- 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 theUpdates historydataset 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 (seeGiving agents access to Explore)
- Ticket data in Zendesk Support
Calculating the average time a ticket is in each status through its lifecycle
In this report, 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 reports icon (
) . - In the Reports library, clickNew report．
- On theSelecta datasetpage, clickSupport>Support - Updates history, then clickStart report．The report builder opens.
- In theMetricspanel, clickAdd．
- From the list of metrics, expandDuration - 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 toAVG．This will display the average of all returned values. When you are finished, clickApply．
Tip:If you have enabled the On-hold ticket status, you can also add the metricOn-hold status time (hrs)． - From the Visualization type (
) menu, chooseTable．
Explore displays the finished report.
Calculating how long tickets sit with a support group
In this report, you’ll create a calculated metric that shows how long your tickets spend in your support group namedSupport 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 ExploreField changesattributes, but you'll learn how to discover the ID of your groups.
This report 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 report, you need to find the group ID for theSupport Group 1group in Zendesk Support.
To discover your group IDs
- In your web browser, visit the following URL:subdomain．zendesk.com/api/v2/groups.json
(replacesubdomainwith 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 reports icon () .
- In the Reports library, clickNew report．
- On theSelecta datasetpage, clickSupport>Support - Updates history, then clickStart report．The report builder opens.
- From theCalculationsmenu (
), chooseStandard calculated metric． - On theStandard calculated metricpage, give the metric a name, for exampleTime spent in group．
- In theFormulafield, copy and paste the following formula. Replace the textGroup ID numberwith 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 ENDIFTip:If you're working in a language other than English,read this articleto help you enter Explore formulas in your language.Supported aggregators when you use this metric in a report are SUM, AVG, MIN, MAX, and MED
You'll end up with a page that looks similar to this:
- When you are finished, clickSave．Leave the report open and continue to the next procedure.
Using the custom metric in a report
Now, you'll create a report that uses the custom metric you just created to report on the amount of time tickets spent in theSupport 1 group．Perform the following steps in the report you created previously, or in a new report that uses theSupport: Updates historydataset.
To use the custom metric in a report
- In theMetricspanel, clickAdd．
- From the list of metrics, chooseCalculated metrics>Time spent in group, then clickApply．Explore displays the total time that tickets spent in theSupport 1group. Set the metric aggregator to MED, or any other supported value you want.
- In theColumnspanel, clickAdd．
- From the list of attributes, chooseTime - Ticket update>Update - YearandTime - Ticket update>Update - Month．
- Set the visualization type () toColumn．Explore displays a chart showing how long each of your tickets spent in theSupport 1 group．
- When you are finished, clickSave．
Calculating how long tickets sit with multiple support groups
In this report, you’ll create a calculated attribute that shows previous groups the ticket was assigned to. You can then use this attribute with the pre-builtField changes timemetric 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, seeDiscovering your group ID numbersin 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 reports icon () .
- In the Reports library, clickNew report．
- On theSelecta datasetpage, clickSupport>Support - Updates history, then clickStart report．The report builder opens.
- From theCalculationsmenu (), chooseStandard calculated attribute．
- On theStandard calculated attributepage, give the metric a name, for examplePrevious group．
- In theFormulafield, 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.
如果([变化——前一个值]=“24120932”)然后“Dev Team" ELIF ([Changes - Previous value]="24935531") THEN "Sales" ELIF ([Changes - Previous value]="24072451") THEN "Support" ENDIFIf 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, clickSave．
Using the custom attribute in a report
Now, you'll create a report 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 report that uses theSupport: Tickets updatesdataset.
To create the report
- In theMetricspanel, clickAdd．
- From the list of metrics, chooseDuration - Field changes (hrs)>Field changes time (hrs), then clickApply．
- In theMetricspanel, click the metric you just added, and change its' aggregator toAVG．
- In theColumnspanel, clickAdd．
- From the list of attributes, chooseTime - Ticket update>Update - Month, then clickApply．
- In theRowspanel, clickAdd．
- From the list of attributes, chooseCalculated attributes>Previous group．
- From the Visualization type menu (), chooseColumn．
- From the Chart configuration menu (
), chooseChart． - In theChartmenu, checkStacked．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:
Calculating duration of time for custom ticket fields
Reporting on the duration ofcustom ticket field valuesis very similar in concept toCalculating how long tickets sit with a support group．重要的区别在于,你娘家姓的d to find a specific value to use in your custom metric formula in order to return the appropriate results.
For example, let’s say you’ve created a custom ticket field called Type of Support, with possible values of Beginner Support, Intermediate Support, and Advanced Support. You want to report on how long tickets spend with the Advanced Support value applied.
To get started, find the ID of the Type of Support field, and then use that ID to find the value of the Advanced Support value, which you can use in your custom metric formula.
Finding the specific ticket field value
These steps are similar to those inDiscovering your group ID numbers．Except here, you’re not going to find the ID of a group; you’re going to find the ID of a custom ticket field, and then find the specific value of the ticket field value that you want to report on.
To find the ticket field ID
- InAdmin Center, click theObjects and rulesicon (
) in the sidebar, then selectTickets > Fields． - In theField IDcolumn, find the ID number of your custom ticket field (in this example, Type of Support). Make a note of this number, which you’ll need in the following step.
To find the value of the ticket field value
- In your web browser, visit the following URL:subdomain．zendesk.com/api/v2/ticket_fields/
(replacesubdomainwith the name of your Zendesk subdomain, and replacewith the ID you noted previously)
In the example, you would see something like the following:
- “id”: 360048826392,
- “name”: “Advanced Support”,
- “raw_name”: “Advanced”,
- “value”: “advanced_support”,
- “default”: false
The value to use in the custom metric formula is “advanced_support”.
If you were to use “Advanced Support” instead, the formula would technically be valid, but it wouldn’t return any results.
Creating the custom metric and using it in a report
Following the example, you want to see how long a ticket spends with the Advanced Support value set. The formula for that would look like the following:
IF ([Changes - Field name]="Type of Support")
AND ([Changes - Previous value] = "advanced_support")
THEN VALUE(Field changes time (min))
ENDIF
For more detailed instructions, seeCreating the custom metricearlier in this article.
The last step is to create a report using your custom metric. For instructions, seeUsing the custom metric in a report．
Next steps
For more information about the metrics and attributes you can use with the Updates history dataset, seeMetrics and attributes for Zendesk Support．
41 Comments
so this would look right for me - am I correct?
(In my example I need the time per month, this isn't important)
1. Calculate the total on-hold time for each ticket (red)
2. Calculate the average of these values (blue)
Hi.
From the comments, I gather the answer to this question is still NO but just in case something changed recently here goes:
Is it possible to generate a report that shows how much time a ticket has been assigned to each of my teams from creation to closure?
Thank you.
It's not readily available. However, I'm thinking that it's possible through some form of customization with theTime Tracking App．Unfortunately, this is outside our scope and let's hope someone found a way to go about this.
HeyDane,
any thought on my issue? =)
When it comes to calculating the AVE your approach is correct. It will add up all the values in the columns and get the average of it.
HeyDane,
thanks a lot for confirming! :)
I would suggest to change the article - cause it says:
"This gives you the tools to answer some important business questions like:
What's the average time a ticket sits in each status through its lifecycle?"
So everyone using this article is getting the wrong results for this question mentioned (what the whole article is about)
I am really happy to see that Zendesk allow custom statuses (albeit linked to one of the core statuses).
However, I am unable to find a way to report on the time that a ticket is at a custom status, since in order to calculate time between events I need to use the Updates History dataset, yet custom statuses are not available in the dataset, only in the Support Tickets dataset.
How do Zendesk suggest getting this information?
Hello Martin,
Unfortunately, at this time, custom statuses can only be reported in the Tickets dataset.
For reference:How can I report on tickets with a custom ticket status?
This "VALUE(Field changes time (hrs))" suddently does NOT work for me. Am I doing something wrong, or does it have a replacement? Thanks
HiDane
I have a question regarding the duration report. I would like to create a report that calculates the time spent from when a ticket is created until it reaches the 'On-Hold' status. This report should only include tickets that are in the 'On-Hold' status. It should analyze the history of all 'On-Hold' tickets and calculate the time from ticket creation to 'On-Hold' status.
Also need to calculate the time duration between ticket created to change the ticket type to Problem.
That's because you are using the VALUE aggregator. It was mentioned in thisarticlethatVALUE aggregator can still be used with the database-level metrics and it is not allowed to be used with the calculated metrics, which was always the case as explained in the article you mentioned. See Troubleshooting errors in Explore formulas we have listed a few different ways to avoid the syntax error with the VALUE aggregator.
Instead of using
VALUE(Field changes time (hrs))it's recommended to utilizeVALUE(Field changes time (min))/60/24because the database-level metric is in minutes. This adjustment should provide more accurate results.Hope this clarification is helpful!
Pleasesign into leave a comment.