Zendesk – First Response Time

Question

What is my team’s First Response Time?

First Response Time is the time it takes for your team to answer to a new ticket or customer question. This can be measured as the minutes or hours elapsed between the creation of a new ticket from a customer and the first message that a customer support representative sent as a reply.

A few things that you should keep in mind when working with the First Response Time metric.

As a metric, it can easily be manipulated or misleading. For example, your ticketing system, like Zendesk, might automatically respond whenever a new ticket is created. This automatic response shouldn’t be counted towards the First Response Time.

You should consider your working hours, if you do not offer 24/7 support then a ticket that is created outside your working hours, shouldn’t be measured towards the overall First Response Time, you could offset the creation time to the beginning of your working day.

As in other similar customer support metrics, you should consider different views of it, more specifically, you should take into consideration the average, median, minimum and maximum first response time.

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 are tickets and ticket_metric_events that we are going to use bellow.

The tickets table has the following structure

Zendesk tickets table

The ticket_metrics_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 Response Time

SELECT avg(firstResponse) from  
  (SELECT min(tmp2.ftr-tmp2.created_at) AS firstResponse
   FROM
     (SELECT a.time AS ftr, tmp1.created_at, a.ticket_id, a.metric
      FROM cont_ticket_metric_events AS a,
        (SELECT created_at, id
         FROM cont_tickets)tmp1
      WHERE (a.ticket_id=tmp1.id)
        AND (a.time>tmp1.created_at)
        AND a.metric = 'reply_time')tmp2
   GROUP BY tmp2.ticket_id)tmp3

Minimum First Response Time

SELECT min(tmp2.ftr-tmp2.created_at) AS MinfirstResponse  
FROM  
  (SELECT a.time AS ftr,
          tmp1.created_at,
          a.ticket_id,
          a.metric
   FROM cont_ticket_metric_events AS a,
     (SELECT created_at, id
      FROM cont_tickets)tmp1
   WHERE (a.ticket_id=tmp1.id)
     AND (a.time>tmp1.created_at)
     AND a.metric = 'reply_time')tmp2

Maximum First Response Time

SELECT max(firstResponse) from  
  (SELECT min(tmp2.ftr-tmp2.created_at) AS firstResponse
   FROM
     (SELECT a.time AS ftr, tmp1.created_at, a.ticket_id, a.metric
      FROM cont_ticket_metric_events AS a,
        (SELECT created_at, id
         FROM cont_tickets)tmp1
      WHERE (a.ticket_id=tmp1.id)
        AND (a.time!=tmp1.created_at)
        AND a.metric = 'reply_time')tmp2
   GROUP BY tmp2.ticket_id)tmp3

Median First Response Time

SELECT median(extract(epoch FROM firstResponse)/60)  
FROM  
  (SELECT min(tmp2.ftr-tmp2.created_at) AS firstResponse
   FROM
     (SELECT a.time AS ftr,
             tmp1.created_at,
             a.ticket_id,
             a.metric
      FROM cont_ticket_metric_events AS a,
        (SELECT created_at, id
         FROM cont_tickets)tmp1
      WHERE (a.ticket_id=tmp1.id)
        AND (a.time!=tmp1.created_at)
        AND a.metric = 'reply_time')tmp2
   GROUP BY tmp2.ticket_id)tmp3

 

in Customer Support