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