Submitted Ticket
Customer submitted a new support ticket (via Zendesk)
Type: Activity Transformation
SELECT
t.id AS "activity_id"
, t.created_at AS "ts"
, 'zendesk' AS "source"
, t.submitter_id AS "source_id"
, u.email AS "customer"
, 'submitted_ticket' as "activity"
, t.subject ::varchar(250) AS "feature_1" -- subject
, t.id as feature_2 -- ticket_id
, a.email as feature_3 -- assigned_to
, NULL AS "revenue_impact"
, 'https://SUPPORTDOMAIN.zendesk.com/agent/tickets/' || t.id AS "link"
FROM zendesk.ticket AS t
JOIN zendesk.user AS u
ON ( u.id = t.submitter_id )
JOIN zendesk.user AS a
ON ( a.id = t.assignee_id )
WHERE t.subject <> 'SCRUBBED'
Ticket Interactions
Creates:
- Commented Ticket
- Reopened Ticket
- Closed Ticket
- Solved Ticket
Type: Activity Transformation
SELECT
e.id AS "activity_id"
, e."timestamp" AS "ts"
, 'zendesk' AS "source"
, t.submitter_id AS "source_id"
, u.email AS "customer"
, CASE
WHEN e.event_type = 'Comment' THEN 'commented_ticket'
WHEN e.event_type = 'Change' and e.status = 'open' and e.previous_value in ('closed', 'solved') THEN 'reopened_ticket'
WHEN e.event_type = 'Change' and e.status in ('closed', 'solved') THEN e.status || '_ticket'
END as "activity"
, t.subject ::varchar(250) AS "feature_1"
, e.ticket_event_via AS "feature_2"
, a.email AS "feature_3"
, NULL AS "revenue_impact"
, 'https://SUPPORTDOMAIN.zendesk.com/agent/tickets/' || t.id AS "link"
FROM zendesk.ticket_events AS e
JOIN zendesk.tickets AS t
ON ( e.ticket_id = t.id and t.subject <> 'SCRUBBED' )
JOIN zendesk.users AS u
ON ( u.id = t.submitter_id )
JOIN zendesk.users AS a
ON ( a.id = t.assignee_id )
WHERE
e.event_type = 'Comment' or
(e.event_type= 'Change' and e.status in ('closed', 'solved')) or
(e.event_type= 'Change' and e.status = 'open' and previous_value in ('closed', 'solved') )
Rated Support Experience
Type: Activity Transformation
select
s.id as activity_id
, s.created_at as ts
, 'zendesk' as source
, t.submitter_id as source_id
, u.email as customer
, 'rated_support_experience' as activity
, s.score as feature_1 --rating
, s.reason as feature_2 -- reason
, NULL as feature_3
, NULL as revenue_impact
-- link to the ticket
, 'https://COMPANYDOMAIN.zendesk.com/agent/tickets/' || t.id AS "link"
from zendesk.satisfaction_rating s
inner join zendesk.ticket t
on t.id = s.ticket_id
JOIN zendesk.user AS u
ON ( u.id = t.submitter_id )
where
score <> 'offered'
Contribute to the Narrator Community
Help the Narrator community by suggesting edits or contributing SQL transformations. Use the "Suggest Edits" button at the top right or visit our Contributors page for more info on how to get involved.
Have questions?
Our data team is here to help! Here are a couple ways to get in touch...
š¬ Chat us from within Narrator
š Email us at [email protected]
Updated 6 months ago
What's Next
Don't see what you're looking for? Browse the Activity Index for more examples...
Activity Index |