Zendesk – Full Resolution Time

Question

What is my team’s Full Resolution Time?

Full Resolution Time is the time between ticket creation and the timestamp of the latest change of the ticket status into “solved” and works well together with First Resolution Time.

Ideally, we’d like to see the two resolution times to be as close together as possible. A low Full Resolution Time which is close to the First Resolution Time, means that our team is reaching the holy grail of customer support which is to being able to solve the problems of our customers with the first communication that we have.

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 Full 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 max(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 Full 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 max(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 Full 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 max(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 Full 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 max(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