Zendesk Support - Ticket Metrics Queries

Zendesk Support provides calculated ticket metrics that give insight into the performance of your support organization. These queries recreate each metric.

1. GROUP_STATIONS - Calculate the number of groups each ticket passed through:

   SELECT
       ticket_id,
       COUNT(distinct value) AS group_stations
   FROM zendesk.ticket_field_history
   WHERE field_name = 'group_id' 
   GROUP BY ticket_id
   

2. ASSIGNEE_STATIONS - Calculate the number of assignees each ticket had:

   SELECT
       ticket_id,
       COUNT(distinct value) AS group_stations
   FROM zendesk.ticket_field_history
   WHERE field_name = 'assignee_id' 
   GROUP BY ticket_id;
   

3. REOPENS - Calculate the number of times the ticket was reopened:

   WITH grouped_ticket_status_history AS ( 
   SELECT * 
   FROM zendesk.ticket_field_history
   WHERE field_name = 'status' 
   ORDER BY ticket_id, updated
   ),
   statuses AS ( 
   SELECT 
       ticket_id,
       LAG(ticket_id, 1, 0) OVER(ORDER BY ticket_id, updated) AS prev_ticket_id, 
       value AS status, 
       LAG(value, 1, 'new') OVER(ORDER BY ticket_id, updated) AS prev_status
   FROM grouped_ticket_status_history
   )
   SELECT 
       DISTINCT ticket_id, 
       COUNT(ticket_id) AS reopens 
   FROM statuses 
   WHERE ticket_id = prev_ticket_id AND prev_status = 'solved' AND status = 'open' 
   GROUP BY ticket_id;
   

4. ASSIGNEE_UPDATED_AT - Calculate when the assignee last updated each ticket:

   SELECT
       ticket_id,
       MAX(updated) AS assignee_updated_at
   FROM zendesk.ticket_field_history
   WHERE field_name = 'assignee_id' 
   GROUP BY ticket_id;
   

5. REQUESTER_UPDATED_AT - Calculate when the requester last updated the ticket:

   SELECT
       ticket_id,
       MAX(updated) AS requester_updated_at
   FROM zendesk.ticket_field_history
   WHERE field_name = 'requester_id' 
   GROUP BY ticket_id;
   

6. STATUS_UPDATED_AT - Calculate when the status was last updated on each ticket:

   SELECT
       ticket_id,
       MAX(updated) AS status_updated_at
   FROM zendesk.ticket_field_history
   WHERE field_name = 'status' 
   GROUP BY ticket_id;
   

7. INITIALLY_ASSIGNED_AT - Calculate when each ticket was initially assigned:

   SELECT
       ticket_id,
       MIN(updated) AS initially_assigned_at
   FROM zendesk.ticket_field_history
   WHERE field_name = 'assignee_id' 
   GROUP BY ticket_id;
   

8. ASSIGNED_AT - Calculate when each ticket was last assigned:

   SELECT
       ticket_id,
       MAX(updated) AS initially_assigned_at
   FROM zendesk.ticket_field_history
   WHERE field_name = 'assignee_id' 
   GROUP BY ticket_id;
   

9. SOLVED_AT - Calculate when each ticket was solved:

   SELECT
       ticket_id,
       MAX(updated) AS solved_at
   FROM zendesk.ticket_field_history
   WHERE value = 'solved' 
   GROUP BY ticket_id;
   

10. LATEST_COMMENT_ADDED_AT - Calculate when each ticket was most recently comment on:

    SELECT
        ticket_id,
        MAX(created) AS latest_comment_added_at
    FROM zendesk.ticket_comment 
    GROUP BY ticket_id;
    

11. CREATED_AT - Calculate when each ticket was created:

    SELECT
        id,
        created_at,
    FROM zendesk.ticket;
    

12. UPDATED_AT - Calculate when each record was most recently updated:

    SELECT
        ticket_id,
        MAX(updated) AS updated_at
    FROM zendesk.ticket_field_history     
    GROUP BY ticket_id;