Zendesk – Requester Wait Time

Question

What is my team’s Requester Wait Time?

Another important metric of customer experience is the time the client has to wait for a response from an agent. The total amount of this time is something that affects the overall experience that a customer has and thus it is important to keep track of it.

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

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

Output

SQL Queries

Average Requester Wait Time

SELECT avg(tmp2.activations-tmp2.fulfillments) AS RequesterWaitingTime  
FROM  
  (SELECT ticket_id, sum(CASE
                  WHEN tmp.type='activate' THEN minutes
                  ELSE NULL
              END) AS activations,
          sum(CASE
                  WHEN (tmp.type='fulfill' or tmp.type='paused') THEN minutes
                  ELSE NULL
              END) AS fulfillments
   FROM
     (SELECT ticket_id,
             metric,
             TIME,
             TYPE,
             now()-TIME AS minutes
      FROM cont_ticket_metric_events
      WHERE metric = 'requester_wait_time'
        AND TYPE IN('activate',
                    'paused',
                    'fulfill') )tmp
  group by ticket_id )tmp2

Minimum Requester Wait Time

SELECT min(tmp2.activations-tmp2.fulfillments) AS RequesterWaitingTime  
FROM  
  (SELECT ticket_id, sum(CASE
                  WHEN tmp.type='activate' THEN minutes
                  ELSE NULL
              END) AS activations,
          sum(CASE
                  WHEN (tmp.type='fulfill' or tmp.type='paused') THEN minutes
                  ELSE NULL
              END) AS fulfillments
   FROM
     (SELECT ticket_id,
             metric,
             TIME,
             TYPE,
             now()-TIME AS minutes
      FROM cont_ticket_metric_events
      WHERE metric = 'requester_wait_time'
        AND TYPE IN('activate',
                    'paused',
                    'fulfill') )tmp
  group by ticket_id )tmp2

Maximum Requester Wait Time

SELECT max(tmp2.activations-tmp2.fulfillments) AS RequesterWaitingTime  
FROM  
  (SELECT ticket_id, sum(CASE
                  WHEN tmp.type='activate' THEN minutes
                  ELSE NULL
              END) AS activations,
          sum(CASE
                  WHEN (tmp.type='fulfill' or tmp.type='paused') THEN minutes
                  ELSE NULL
              END) AS fulfillments
   FROM
     (SELECT ticket_id,
             metric,
             TIME,
             TYPE,
             now()-TIME AS minutes
      FROM cont_ticket_metric_events
      WHERE metric = 'requester_wait_time'
        AND TYPE IN('activate',
                    'paused',
                    'fulfill') )tmp
  group by ticket_id )tmp2

Median Requester Wait Time

SELECT median(extract(epoch  
                      FROM (tmp2.activations-tmp2.fulfillments))/60)
FROM  
  (SELECT ticket_id, sum(CASE
                  WHEN tmp.type='activate' THEN minutes
                  ELSE NULL
              END) AS activations,
          sum(CASE
                  WHEN (tmp.type='fulfill' or tmp.type='paused') THEN minutes
                  ELSE NULL
              END) AS fulfillments
   FROM
     (SELECT ticket_id,
             metric,
             TIME,
             TYPE,
             now()-TIME AS minutes
      FROM cont_ticket_metric_events
      WHERE metric = 'requester_wait_time'
        AND TYPE IN('activate',
                    'paused',
                    'fulfill') )tmp
  group by ticket_id )tmp2