- 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 Updates history dataset which stores details of updates and events that happen in Zendesk Support.
This article contains the following topics:
- What you'll need
- Calculating the average time a ticket is in each status through its lifecycle
- Calculating how long tickets sit with a support group
- Calculating how long tickets sit with multiple support groups
- Calculating how long tickets sit with their last support group
- Calculating duration of time for custom ticket fields
- Next steps
What you'll need
Skill level: Intermediate
Time required: 20 minutes
- Zendesk Explore Professional or Enterprise
- Editor or Admin permissions (see Giving users 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, click New report.
- On the Select a dataset page, click Support > Support - Updates history, then click Start report. The report 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 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 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 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 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 reports icon (
) .
- In the Reports library, click New report.
- On the Select a dataset page, click Support > Support - Updates history, then click Start report. The report 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 report 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 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 the Support 1 group. Perform the following steps in the report you created previously, or in a new report that uses the Support: Updates history dataset.
To use the custom metric in a report
- 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 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-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 reports icon (
) .
- In the Reports library, click New report.
- On the Select a dataset page, click Support > Support - Updates history, then click Start report. The report 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 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 the Support: Updates history dataset.
To create the report
- 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:
Calculating how long tickets sit with their last support group
To calculate how long a ticket sits with its last group, create a standard calculated attribute to identify the time of the last assignment to a group. Then, create a standard calculated metric using the DATE_DIFF() function to calculate it.
Creating the attribute to capture the last group assignment
- In your report, click the Calculations menu (
) and select Standard calculated attribute.
- Give the attribute a descriptive name, such as Last group assignment.
- In the Formula field, enter the following:
IF ([Changes - Field name]="group_id")
THEN DATE_LAST_FIX([Update - Timestamp], [Update ticket ID], [Changes - Field name]) ENDIF
Creating the metric to calculate how long the ticket was assigned to the group
- In your report, click the Calculations menu (
) and select Standard calculated metric.
- Give the metric a descriptive name, such as Last group assignment to resolution.
- In the Formula field, enter the following:
DATE_DIFF([Ticket solved - Timestamp], [Last group assignment], "nb_of_minutes" )
Adding the attribute and metric to your report
- In the Metrics panel, click Add and select the metric you created above (Last group assignment to resolution, in the example).
-
Change the metric aggregator to AVG.
- In the Rows panel, click Add and select Ticket ID and Ticket group.
- In the Filters panel, click Add and select the attribute you created above (Last group assignment, in the example).
- (Optional) For a cleaner-looking report, click the Capturing the last update attribute and then exclude NULL values and include only Solved and Closed statuses. For help, see Filtering by an attribute in the Filters panel.
Calculating duration of time for custom ticket fields
Reporting on the duration of custom ticket field values is very similar in concept to Calculating how long tickets sit with a support group. The important difference here is that you’ll need 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 in Discovering 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
- In Admin Center, click the Objects and rules icon (
) in the sidebar, then select Tickets > Fields.
- In the Field ID column, 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/<ticket field ID>
(replace subdomain with the name of your Zendesk subdomain, and replace <ticket field ID> with 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, see Creating the custom metric earlier in this article.
The last step is to create a report using your custom metric. For instructions, see Using the custom metric in a report.
Next steps
For more information about the metrics and attributes you can use with the Updates history dataset, see Metrics and attributes for Zendesk Support.
55 comments
Tobias Rohling
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)
0
Carlos Santos
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.
1
Dane
It's not readily available. However, I'm thinking that it's possible through some form of customization with the Time Tracking App. Unfortunately, this is outside our scope and let's hope someone found a way to go about this.
-1
Tobias Rohling
Hey Dane,
any thought on my issue? =)
0
Dane
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.
0
Tobias Rohling
Hey Dane,
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)
1
Martin Cubitt
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?
0
Zsa Trias
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?
0
Bill Cao
This "VALUE(Field changes time (hrs))" suddently does NOT work for me. Am I doing something wrong, or does it have a replacement? Thanks
0
Deiaa Eid
Hi Dane
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.
0
Elaine
That's because you are using the VALUE aggregator. It was mentioned in this article that VALUE 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/24
because the database-level metric is in minutes. This adjustment should provide more accurate results.Hope this clarification is helpful!
1
Carmelo Rigatuso
I can get the duration of the custom fields to work, but now I need to also know who the assignee was during that time. I have customer status for an order flow that goes through a few stages. What I can't seem to work out is who specifically worked that ticket during that status. Since the status field changes and the ticket assignee changes are in the same event, the next agent gets hit with the previous custom status duration. Ex.
I tried getting the previous value of the assignee, (which is a pain itself) but the ticket doesn't always change hands when the custom status changes.
Any help or tips would be appreciated.
0
Carmelo Rigatuso
For those of you that were looking for measuring time in business hours, I created a feedback post here. Please comment and upvote!
1
Rosie
Thank you so much for your comprehensive feedback and reasoning. Also for taking your time to share all this information with us. Our product team have been informed and they will continue monitoring any feedback in this area. If more people share similar feedback like you, there's a possibility that it will be added to later updates.
Thank you!
0
Abdelhameed Khaled
Regarding this comment of Dayana and answer of @Gab guinto
Comment :
You mentioned: '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.'
Is there a way to adjust the formula to include also the time spent in the last group that solves it? For our reporting, it is important to calculate all the time by group in the lifecycle of the ticket and I'm missing the last one (the time of the group that solved the ticket).
Answer:
Since the recipe uses the field changes attributes, it's not possible to measure the time spent with the current or final group with the same metric/formula.
You may try to build separate attributes to get the timestamp of the last group assignment (Working with earliest and latest date functions) and create a metric to calculate the duration between that timestamp and resolution.
Now I tried to do this Metric to get the duration between last group assignment and solve date to add it on the calculation of counting how long ticket stay with each group but it keep showing me the last update timestamp whatever it is not the group assignment timestamp and there are some corner cases such as if the ticket reopened and solved again or the group is changed after the ticket is solved
IF ([Changes - Field name]="group_id")
THEN DATE_DIFF(DATE_LAST([Ticket solved - Timestamp]) ,[Update - Timestamp] ,"nb_of_minutes")
ENDIF
The metric gives different results when I change the aggregator function . I want to get the average for each group but when I choose the average it give the average for the ticket itself from first group to solve timestamp then 2nd group to solve timestamp then 3rd and etc ..
I could use any help on that
0
Abdelhameed Khaled
In this Point :
Calculating how long tickets sit with their last support group
How could I hide calculate the average and hide all rows to show just the average of them which is the last row
0
Alex Zheng
If you are looking to hide rows or columns in your report I would take a look at the article here which goes over a few options that you have for doing so.
Let me know if you have any further questions.
0
D.Fitz
Is there a way to distinguish between when a ticket has been 'Solved' and when a ticket has been 'Closed'?
I'm also finding that the 'Capturing the last update' attribute only returns one value and can't be split by Ticket ID
0
Jonathan R Bast
How would you modify the formula to show business hours and not the total time spent between changes?
0
Alex Zheng
Unfortunately, business hours calculations are limited to the default ones available. Any custom formulas will not take into account business hours, you can read the article here for further information.
1
Kelly Janssen
I followed the steps for “Calculating how long tickets sit with their last support group” but would like to remove any time the ticket was “pending” or “on-hold” from the resolution time as our agents for time spent waiting for others. Is there a way to update the calculation to handle this?
0
Alex Zimmer
Hello,
I am trying to determine the duration during which a text field was NULL. I have tried both of the following formulas as attributes and as metrics on the “Updates History” dataset, and no combination has returned anything. Text field name is “Jira ID”.
IF ([Changes - Field name]="Jira ID")
AND ([Changes - Previous value] = "NULL") AND ([Changes - New value] != "NULL")
THEN VALUE(Field changes time (min))
ENDIF
IF ([Changes - Field name]="Jira ID" )
AND ([Changes - Previous value] = “NULL”)
THEN VALUE(Field changes time (min))
ENDIF
Can anyone advise? Thank you in advance.
Alex
0
Yvonne-Sabine Udoh
the formula is not working at all, No matter what I do I get “There's an issue with the formula…”
0
James Peterson - Eastern Logic
I saw several comments trying to get the time in each group including the last group, but still no solutions. We are looking for the same but with assignee. Hoping for a solution in 2025.
0
James Skene
Hi
We have a custom ticket field for Jira Status which updates as the Jira ticket moves through it's lifecycle.
I am trying to build a report that shows me the average amount of time tickets spend in each Jira Status to help me to identify any areas to improve. Does anybody have any suggestions on how I can achieve this? Reporting on the field change time does not work because that is excluding tickets that have not changed status, i.e. have been in Jira Status = Backlog since being escalated.
Thanks
James
0