Zendesk – First Resolution Time

Question

What is my team’s First Resolution Time?

First Resolution Time, is the time it took until the ticket was marked as resolved by the customer support agent for the first time. We say first time because it is not uncommon for a ticket to be reopened by the customer with further questions.

The first resolution time metric is an indication of the efficiency of customer support. Ideally, we’d like none of the tickets to be reopened while reducing this time as much as possible. This time is also perceived by the customer, and thus it affects her experience.

Schema

Blendo will sync all of your Zendesk data into your data warehouse properly prepared to be used for your analytics purposes. Some of the tables we prepare is the ticket_metric_events that we are going to use bellow.

The ticket_metric_events table has the following structure

Zendesk ticket metric events table

Go here to find out the full schema of Zendesk expected data.

Output

SQL Queries

Average First Resolution Time

SELECT avg(first_resolution-first_activation)  
FROM  
  (SELECT min(TIME) AS first_activation,
          ticket_id
   FROM cont_ticket_metric_events
   WHERE (TYPE='activate')
   GROUP BY ticket_id)act
RIGHT JOIN  
  (SELECT min(TIME) AS first_resolution,
          ticket_id
   FROM cont_ticket_metric_events
   WHERE TYPE='fulfill'
   GROUP BY ticket_id)ful ON act.ticket_id=ful.ticket_id

Minimum First Resolution Time

SELECT min(first_resolution-first_activation)  
FROM  
  (SELECT min(TIME) AS first_activation,
          ticket_id
   FROM cont_ticket_metric_events
   WHERE (TYPE='activate')
   GROUP BY ticket_id)act
RIGHT JOIN  
  (SELECT min(TIME) AS first_resolution,
          ticket_id
   FROM cont_ticket_metric_events
   WHERE TYPE='fulfill'
   GROUP BY ticket_id)ful ON act.ticket_id=ful.ticket_id

Maximum First Resolution Time

SELECT max(first_resolution-first_activation)  
FROM  
  (SELECT min(TIME) AS first_activation,
          ticket_id
   FROM cont_ticket_metric_events
   WHERE (TYPE='activate')
   GROUP BY ticket_id)act
RIGHT JOIN  
  (SELECT min(TIME) AS first_resolution,
          ticket_id
   FROM cont_ticket_metric_events
   WHERE TYPE='fulfill'
   GROUP BY ticket_id)ful ON act.ticket_id=ful.ticket_id

Median First Resolution Time

SELECT median(extract(epoch  
                      FROM (first_resolution-first_activation))/60)
FROM  
  (SELECT min(TIME) AS first_activation,
          ticket_id
   FROM cont_ticket_metric_events
   WHERE (TYPE='activate')
   GROUP BY ticket_id)act
RIGHT JOIN  
  (SELECT min(TIME) AS first_resolution,
          ticket_id
   FROM cont_ticket_metric_events
   WHERE TYPE='fulfill'
   GROUP BY ticket_id)ful ON act.ticket_id=ful.ticket_id

 

in Customer Support