Narrator Documentation

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'
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') )
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'
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]

šŸ—“ Or schedule a 15 minute meeting with our data team

Updated about a month ago


What's Next

Don't see what you're looking for? Browse the Activity Index for more examples...

Activity Index

Zendesk


Suggested Edits are limited on API Reference Pages

You can only suggest edits to Markdown body content, but not to the API spec.