- What's the average time a ticket sits in each status through its lifecycle?
- How long do tickets sit with each of my support groups?
The examples in this article use the Ticket updates dataset which stores details of updates and events that happen in Zendesk Support.
This article contains the following topics:
What you'll need
Skill level: Intermediate
Time required: 20 minutes
- Zendesk Explore Professional or Enterprise
- Editor or Admin permissions (see Giving agents access to Explore)
- Ticket data in Zendesk Support
Calculating the average time a ticket is in each status through its lifecycle
In this query, you'll discover the average time your solved tickets spent in each ticket status throughout their lifecycle.
To create the report
- In Explore, click the query icon (
) .
- In the Queries library, click New query.
- On the Choose a dataset page, click Support > Ticket updates > Support: Ticket updates, then click New query. Query builder opens.
- In the Metrics panel, click Add.
- From the list of metrics, expand Duration - Field changes (hrs), and then choose the following metrics:
- New status time (hrs)
- Open status time (hrs)
- Pending status time (hrs)
After you add each one, click the added metric and changed its aggregator to AVG. This will display the average of all returned values. When you are finished, click Apply.
Tip: If you have enabled the On-hold ticket status, you can also add the metric On-hold status time (hrs). - From the Visualization type (
) menu, choose Table.
Explore displays the finished query.
Calculating how long tickets sit with a support group
In this query, you’ll create a calculated metric that shows how long your tickets spend in your support group named Support Group 1. This metric can be used on its own or with any other default or calculated metrics. This can be challenging as group names are stored as an ID in the Explore Field changes attributes, but you'll learn how to discover the ID of your groups.
This query measures the time a ticket spent in a group before being moved to another group; it does not include the time a ticket spent in a group before it was solved.
Discovering your group ID numbers
Before you can create the metrics for the query, you need to find the group ID for the Support Group 1 group in Zendesk Support.
To discover your group IDs
- In your web browser, visit the following URL: subdomain.zendesk.com/api/v2/groups.json
(replace subdomain with the name of your Zendesk subdomain)
You'll see a page showing information about all of your groups, including the group ID in JSON format. You can make the results easier to read by installing a JSON Viewer extension in your browser (for example, with Chrome, search the Chrome web store for JSON viewer).
Creating the custom metric
Now, you'll create a custom metric that displays the duration that a ticket was assigned to a group that you specify. Before you start, make sure you've noted down the group ID using the previous procedure.
To create the custom metric
- In Explore, click the query icon (
) .
- In the Queries library, click New query.
- On the Choose a dataset page, click Support > Ticket updates > Support: Ticket updates, then click New query. Query builder opens.
- From the Calculations menu (
), choose Standard calculated metric.
- On the Standard calculated metric page, give the metric a name, for example Time spent in group.
- In the Formula field, copy and paste the following formula. Replace the text Group ID number with the group ID number you noted previously.
IF ([Changes - Field name]="group_id") AND ([Changes - Previous value]="Group ID number") THEN VALUE(Field changes time (min))/60 ENDIF
Tip: If you're working in a language other than English, read this article to help you enter Explore formulas in your language.Supported aggregators when you use this metric in a query are SUM, AVG, MIN, MAX, and MED
You'll end up with a page that looks similar to this:
- When you are finished, click Save. Leave the query open and continue to the next procedure.
Using the custom metric in a query
Now, you'll create a query that uses the custom metric you just created to report on the amount of time tickets spent in the Support 1 group. Perform the following steps in the query you created previously, or in a new query that uses the Support: Ticket updates dataset.
To use the custom metric in a query
- In the Metrics panel, click Add.
- From the list of metrics, choose Calculated metrics > Time spent in group, then click Apply. Explore displays the total time that tickets spent in the Support 1 group. Set the metric aggregator to MED, or any other supported value you want.
- In the Columns panel, click Add.
- From the list of attributes, choose Time - Ticket update > Update - Year and Time - Ticket update > Update - Month.
- Set the visualization type (
) to Column. Explore displays a chart showing how long each of your tickets spent in the Support 1 group.
- When you are finished, click Save.
Calculating how long tickets sit with multiple support groups
In this query, you’ll create a calculated attribute that shows previous groups the ticket was assigned to. You can then use this attribute with the pre-built Field changes time metric to calculate how long the ticket was in each group.
Before you start, you'll need the ID numbers of the groups you want to query. To get the ID numbers, see Discovering your group ID numbers in this article.
Creating the custom attribute
First, you'll create a custom attribute that checks whether the ticket has been assigned to the groups you list. If it has, the group name is returned. Before you start, make sure you've noted down the group ID using the previous procedure.
To create the custom attribute
- In Explore, click the query icon (
) .
- In the Queries library, click New query.
- On the Choose a dataset page, click Support > Ticket updates > Support: Ticket updates, then click New query. Query builder opens.
- From the Calculations menu (
), choose Standard calculated attribute.
- On the Standard calculated attribute page, give the metric a name, for example Previous group.
- In the Formula field, copy and paste the following formula. Replace the example group IDs with the group ID numbers you noted previously. Then, replace the example group names with the names that correspond to your group IDs.
IF ([Changes - Previous value]="24120932") THEN "Dev Team" ELIF ([Changes - Previous value]="24935531") THEN "Sales" ELIF ([Changes - Previous value]="24072451") THEN "Support" ENDIF
If you want to check for more groups than this example, add a new ELIF line for each group.
You'll end up with a page that looks similar to this:
- When you are finished, click Save.
Using the custom attribute in a query
Now, you'll create a query that uses the custom attribute you just created to report on the amount of time tickets spent in each group. Perform the following steps in a new query that uses the Support: Tickets updates dataset.
To create the query
- In the Metrics panel, click Add.
- From the list of metrics, choose Duration - Field changes (hrs) > Field changes time (hrs), then click Apply.
- In the Metrics panel, click the metric you just added, and change its' aggregator to AVG.
- In the Columns panel, click Add.
- From the list of attributes, choose Time - Ticket update > Update - Month, then click Apply.
- In the Rows panel, click Add.
- From the list of attributes, choose Calculated attributes > Previous group.
- From the Visualization type menu (
), choose Column.
- From the Chart configuration menu (
), choose Chart.
- In the Chart menu, check Stacked. This ensures that results for each group you select are shown in the same chart column.
The final result will look similar to the example below:
Next steps
For more information about the metrics and attributes you can use with the Ticket updates dataset, see Metrics and attributes for Zendesk Support.
26 Comments
Hello,
We are currently looking for a way to combine these two metrics, so that we can understand how long a ticket is in the Open status for each ticket group. We have run into an issue in that we can only slide this by updated group, meaning if a ticket is opened in tier 1, and moves to tier 2, then the entire open time is reported for tier 2.
Is there any way to slice the open time by previous group/current group instead of updated group?
Thanks,
Jason
@Jason,
You can use the Ticket group attribute to return the current ticket group or Update ticket group to return the group of the ticket set at each update. Here is an example:
How would we go about doing this for a custom drop down field?
For example we have a field called "Custom field" with Value1, Value 2, Value 3, Value 4
Im trying to figure out the calculated metric for the time spent in each of those values.
Hell Wesley Bowles,
This article goes into detail on how to set up your reporting with custom drop fields that I've linked below. If you have any trouble setting this up, please let us know, and we'd be happy to troubleshoot it.
Reporting with custom fields
Best regards.
Is there a way to exclude any time outside of business hours?
Hey Bryan,
You should be able to filter your queries by business hours using the following recipe: Explore recipe: Filtering queries by business hours
I hope this helps!
Bryan Telfer, the recipe that Brett provided allows filtering a report based on the ticket creation time or any other time attribute, but it will not help you to exclude the time ticket spent outside of the business hours in the specific field value.
Eugene Orman Is there a formula that could allow me to exclude the time that the ticket spent outside of working hours? Tickets that sit in an open, pending, etc. status over the weekend are greatly offsetting my query.
Bryan Telfer Unfortunately, there is no formula that would allow you to create such clever logic.
Is it a correct formula to measure the time between ticket creation & ticket update to a certain value (Yes)?
I want to make a custom SLA-related metric for each of the restoration steps (finer steps than the available SLA metrics)
IF ([Changes - Field name]="certain field name in multi-select" )
AND ([Changes - Previous value] = NULL)
AND ([Changes - New value] = "Yes")
THEN VALUE(Field changes time (min))
ENDIF
Thanks!
Hey Miyahara,
If you're trying to measure the duration between ticket field changes then the above formula should work for you. Are you running into any issues with the metric you provided?
Let me know!
The avg here is is calculated every time the ticket moved to another group. meaning it the avg time per 'visit'
But if I want to know the avg time that group spent per ticket and not per visit - how do I do that?
for example - ticket 1234
group A 12 min
Group B 15 min
Group A 10 min
ticket 1122
Group A 20 min
Group B 5 min
Group A 30 min
Group B 25 min
in the above example the average will be:
Group A: (12+10+20+30)/4 = 18
Group B: (15 + 25 + 5)/3= 15
Per ticket Should be:
Ticket 1234:
Group A 12+10 = 22
Group B 15
ticket 1122:
Group A: 20 + 30 = 50
Group B: 25 + 5 = 45
Avg:
Group A (22 + 50) /2 = 36
Group B: (45 + 15) / 2 = 30
As you can see the avg are different
Hello Shelly Fleishaker,
At this time, there is no way to accomplish this in Explore natively. I would recommend sharing your use case with product feedback so our developers can consider implementing this change for future updates.
Best regards.
Hello Devan - Community Manager,
Is there a way for calculating how long tickets sit with a support group in a specific status (open) ?
Thanks!
Hi Edouard,
Thanks for reaching out! You can pull this type of report using the Support: Ticket Updates dataset as Eugene mentioned earlier in this thread. I recommend using these metrics and attributes if you'd like to break down this report by individual tickets:
Metrics: Open Status Time (min/hrs/days)
Rows: Ticket ID, Update Ticket Group
If this isn't quite what you're looking for let us know and we'd be happy to take another look! :)
Hi Zendesk team.
When I approach a customer of ours, I set a reminder (trigger) for a few days later in case the customer hasn't responded yet - in this case the ticket changes form Pending to Open.
It seems that when the remainder scenario takes place, Zendesk attributes the time from Pending to Open to my team - distorting the actual amount of time which the ticket was under my team's care.
Could you think of a solution to the problem?
Please take under consideration that we want to see data for all groups who touched the ticket and only for the current one.
Hi Amit,
Thanks for reaching out! I'd like to get a better understanding of your workflow for this trigger. Is the trigger configured to open the ticket so that the agent sees it has not been responded to? Once this ticket is re-opened, does the agent solve it out or send another response to the end user?
It may be worth checking out this article that walks you through how to set up automated reminders for end users that have not responded to their ticket. This configuration does not move these tickets to open status preventing them from skewing with your data.
Hope this helps!
Hi Taylor,
Thank you very much for your answer.
I'm aware of the method you've shared in article above and using it for some cases.
Other cases requires a representative discretion regarding how to move forward with the case in hand - hence the status change to open and the resurfacing of the ticket.
So I'm afraid that won't solve the issue.
If we've already touched Zendesk's timing mechanism, is there a way to set up a definite time for action?
For example, instead of configuring 96 hours, can one set up an event to take place at 2:57 PM 19/10/2020?
Hi Amit,
Thanks for clarifying! It isn't possible to do this natively within Support but you could utilize third party apps to set reminders for specific times and dates without changing the status of the ticket. Here are a couple apps you may find useful for your workflow:
I am trying to create a calculated metrics using "[Changes- field name]", however the field list does not display an option that starts with the key word "[Change". Is there something I am missing here?
I even tried copy pasting the formula given in this article, but it is not accepting any field that starts from the key word [Change".
Could you please help how can I add this field to the calculated metrics.
Thanks
Eshmeet
Hi Eshmeet Singh, thanks for the question!
If the field isn't appearing, it's possible you're not using the correct dataset. You must use the Support: Ticket updates dataset to see that metric. I hope this helps!
Thanks Rob, right I was using incorrect dataset. It fixed my problem!
Eshmeet
Excellent, thanks Eshmeet Singh I'm glad it's working for you now :-)
Hello Rob, I tried applying the same metrics to calculate duration between two custom field values. However, it's not throwing any results.
Below is the calculated metrics I applied:
Metrics used
IF ([Changes - Field name]="Calendar Status")
AND ([Changes - Previous value] ="Build Assigned")
AND ([Changes - New value] = "Build Complete")
THEN VALUE(Field changes time (days))
ENDIF
Is there something I am not doing right in this query? thanks
Hi Eshmeet Singh, while I'm unable to try this with your particular custom field, your formula looks correct.
What I like to do is try breaking down the formula into smaller sections for testing. Here's a suggested order:
Confirm that the Calendar status field has data and that you can report on it. To do that, you could try adding the Calendar status attribute to the rows column of a query.
Then, try taking one or both of the AND statements out. Does it still fail?
If you're still having problems, I'd recommend posting this question over in the Explore Q and A section at https://support.zendesk.com/hc/en-us/community/topics/360001200893 - there's a bunch of query experts over there who I'm sure will help you out. Thanks!
Hi,
It seems like this solution also calculates the solved time for a Ticket in Group. Is there a way to only look at New, Open & Pending time for a Ticket which moves between Group and excluding the Solved Time?
Please sign in to leave a comment.