In this Explore recipe, you'll learn how to create a query that helps report on your agents' adherence to due dates on average at a group level.
What you'll need
Skill level: Moderate
Time Required: 20 minutes
- Zendesk Explore Professional or Enterprise
- Editor or Admin permissions (see Giving agents access to Explore)
- Ticket data in Zendesk Support
Creating the query
To create the query
- In Zendesk Explore, click the query (
) icon.
- In the Queries library, click New query.
- On the Choose a dataset page, click Support > Tickets > Support: Tickets, then click New query. Query Builder opens.
- Now, create a Standard calculated metric to show the number of days before or after a due date that a ticket was solved. Click Calculations (
), and then Standard calculated metric.
- On the Standard calculated metric page, copy the formula below and paste it into the metric editor. Add a title for the metric like Due Date Delivery. It will look like the screenshot below:
DATE_DIFF([Ticket solved - Timestamp],[Ticket due - Timestamp],"nb_of_days")
Tip: If you're working in a language other than English, read this article to help you enter Explore formulas in your language. - In the Metrics panel, click Add.
- From the list of metrics, choose Calculated metrics > Due Date Delivery, then click Apply.
- Ensure the metric aggregator for Due Date Delivery is set to AVG. For more information, see Choosing metric aggregators.
- In the Rows panel, click Add.
- From the list of attributes, choose Ticket > Ticket group, then click Apply.
- Click Chart configuration (
), then Display format. Change AVG(Due Date Delivery) to Advanced. Then copy the formatting below and replace the text in the Display format editor.
IF (AVG(Due Date Delivery) < 0) THEN
{
"backgroundColor": "",
"precision": 0,
"scale": 1,
"prefix": "",
"decimalSeparator": ".",
"italic": FALSE,
"bold": FALSE,
"suffix": "",
"fontColor": "#228B22",
"thousandsSeparator": " "
}
ELIF (AVG(Due Date Delivery) > 1) THEN
{
"fontColor": "#ff0000"
}
ELSE
{
"fontColor": "#000000"
}
ENDIF
This will change the color of the values based on whether your agents are solving a ticket ahead of the due date, or exceeding the due date.
The query is complete! See the screenshot below as an example.
7 Comments
Hi, I am trying to calculate the number of days from Ticket creation to when it was updated last, I have created the following metric
DATE_DIFF([Ticket updated - Timestamp],[Ticket created - Timestamp],"Last Updated (days)"
But the query displays no result
Can you help?
Alessandra
Try this:
I'm wanting a report to track my due date metrics and be sure it's based off of the first solved instance. Sometimes the customers are responding back with a simple note of appreciation and the ticket may linger for a day or two before it's solved out again.
Is there a way to be sure I'm accurately tracking the due date delivery based off of the first solve time?
I am trying to display today's date using now() or today() but when I am trying to add these metrics it forcefully applies sum or avg aggregator to it resulting blank display of the date.
Kris Parker,
So you will want to use this rebuild metric on first resolution time to accomplish this, I've gone ahead a linked below an article that goes into detail on how to implement this.
Explore recipe: Reporting on full resolution time
Best regards.
Mrunal Dadhi,
This is because metrics only measure numbers, so you would need to accomplish this using an attribute. I've linked an article below that goes into detail on the differences and implementation of metrics and attributes for your reference.
Metrics and attributes for Zendesk Support
Best regards.
I’ve been able to pull ticket-by-ticket info on whether the due date was hit, but I now need to pull an overall percentage of tickets that were solved by the due date. How do I look at an overall percentage of tickets that achieve the due date delivery?
Please sign in to leave a comment.