- 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
zAdrian
Hello,
Regarding this mentioned limitation:
'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 group that solves it? For our reporting, it is important to calculate all the time a ticket stays with a group as we have SLAs on the entire period a ticket is assigned, until resolution.
Any advice on how to achieve this is much appreciated :)
9
e-Services
Hi,
I am trying to measure the duration between the ticket creation and the time that the agent really started to work on the ticket (not assigned to the ticket).
I have an alternative status field that my agents are updating depending on the ticket status and to achieve what I want, I need to measure the time it took to set the field value to "In Work"
There are a few options here:
I have tried the following query based on one of the posts above but I can't get any value out of it.
Is the query correct?
Thanks
Yoram
0
Thibaut
Hi Yoram,
I decided to create a ticket for you so I can look in details into your formula and query, you should receive a notification shortly about it.
Thibaut | Customer Advocate | EMEA
0
Simon.T
Hi Explore Community:
Am trying to create a report based on above instructions to determine time spent on a ticket for 2 support groups (A and B). Am facing issue of not able to display time spent in "B" support group. Screenshot for your reference:
Scenario: The first Group A is the group whom received the ticket and escalated to Group B whom work and subsequently closed the case. I've verify the Group ID input are correct. Please advice:
IF ([Changes - Previous value]="123") THEN "A"
ELIF ([Changes - Previous value] ="456") THEN "B"
ENDIF
0
Gab Guinto
Hi Simon,
One limitation of this recipe is that it does not account for the time the ticket is sitting with the current group. It can only calculate the time the ticket was assigned to the previous groups (since the metric can only measure the duration when group_id value is changed). This is why you are not seeing the results for Group B in your graph – if that ticket was closed out while assigned to Group B (the current group), then you won't be able to calculate the time spent under that group using this recipe. You may need to build other custom metrics to measure the time from when a ticket was assigned to the current group until it was resolved. Sorry about this, Simon.
-1
Russ Milton
Has there been any update to allow me to view the field changes time linked to the schedule of the ticket (i.e business hours)?
4
Michael Bui
I have a custom field that is date-based, I want to calculate from the time the ticket was created to when that field has a date entered. Essentially field change.
This is the custom metric I'm using but I get no results
IF ([Changes - Field name]="Installation Completed date" )
AND ([Changes - Previous value] = NULL)
THEN VALUE(Field changes time (min))
ENDIF
0
Darenne
Hi Michael,
Due to the complexity of the issue, I've created a ticket for this concern and will give you an update on the ticket ID too.
Thank you for your kind understanding!
0
Mike Sanchez
Any idea why I can't add Calculated attributes as a row? It appears under Metrics but neither in Columns or Rows.
0
Diogo Maciel
https://support.zendesk.com/hc/en-us/articles/4408824243738-Creating-standard-calculated-metrics-and-attributes
I hope this helps!
0
Prakriti
Hi there,
Is there a way to build these queries in business hours instead of calendar hours?
I'm trying to modify this query "Calculating how long tickets sit with multiple support groups"
5
Gab Guinto
Unfortunately, the calculations from custom metrics can only be in calendar hours. At this time, only the default ticket metrics such as first reply time are available in business hours.
-6
Dayana
Hello there, team!
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).
I appreciate your time and answers / advices to achieve this goal, because I have a lot of time trying to do this... Thanks!!! :)
3
Dayana
Hello there, Comunity!
Any has any comment could help me?
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).
I appreciate your time and answers / advices to achieve this goal, because I have a lot of time trying to do this... Thanks!!! :)
2
PT Dadlani
When will business hours be available or how can one calculate this using business hours? Otherwise, this metric is more or less useless from a customer perspective.
4
Gab Guinto
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.
1
Gab Guinto
I agree that it would be very helpful if the metric is also able to take into account business hours. This one's not yet in the roadmap, but I recommend that you start a thread about this here to engage with more users looking for a similar functionality. Thank you!
1
Rebecca Che
Hi, we have a custom field with multiple custom ticket statuses (example: with the custom status we have Open/pending/hold, and under Open, there is sub category like troubleshooting, Remote session etc...)with a dropdown list, Now I'm trying to calculate the average time in each custom status, for example, how long time ticket sits in Open-troubleshooting. so the formula is created as below:
IF ([Changes - Field name] = "Custom status" AND [Changes - Previous value]= "Open::Troubleshooting")
THEN VALUE(Field changes time (min))/60
ENDIF
but seems not giving correct value? could you help to check and advice?
0
Dave Dyson
I think as the article above states, you need to use the "value" (which in this context mean the tag) associated with your custom field option -- so rather than "Open::Troubleshooting", you'd put the tag association with that option there. Can you give that a try?
-1
Rebecca Che
thanks Dave. Tried below both but didn't give any value.
IF ([Changes - Field name] = "Custom status")
AND ([Changes - Previous value]= "active_troubleshooting")
THEN VALUE(Field changes time (hrs))
ENDIF
Also tried
IF ([Changes - Field name] = "Custom status")
AND ([Changes - Previous value] = Null )
AND ([Changes - New value]= "active_troubleshooting")
THEN VALUE(Field changes time (hrs))
ENDIF
Could you give some advice?Thanks
0
Alex Zheng
Let me open up a ticket with you to dive in further.
Best regards,
0
Andrew Chu
I have a question related to "Calculating how long tickets sit with a support group" - I have tried the formula and it works for a single group, but now I want to calculate for more than one group, when I try to add further steps in the formula doesn't seem to work.
Anyway to enhance this, or in another way, can we change the formula/set up in "Calculating how long tickets sit with multiple support groups" so that instead of a chart the results appear as table?
0
Dane
Upon checking the format of the formula, it will only be possible for a single group. Even if you use "OR" statement it will just return all the values of the groups you will designate. The best option is to create multiple calculated metrics for each group.
Hope this helps.
0
Bill Cao
Hi, Is possible to sum all durations of one field on a status to form a calculated attribute? i.e
I want to have an attribute which can show Field 1 on status A for 20 mintues total. Thanks
0
Gab Guinto
The duration metrics for ticket statuses, like other custom calculations based on the metric Field changes time, are only able to track the time of previous statuses. It won't be able to calculate the time spent in the current status.
We don't an available recipe on this, but if you're comfortable using the different Explore functions (see Explore functions reference) in custom calculations, then may explore other workarounds, maybe try getting the timestamp of the most recent status change and then calculate using date_diff the duration from that timestamp until the current date/time.
0
Sandra de Jong
Hi
I'm trying to create 2 reports and i hope you could give me a suggestion.
1: I want to know the duration of a ticket in the current group (since last change to this group)
2: i want to know how long a custom field was filled. Difference with excamples on this page i do not have a specific data set i'm looking for. I need any data entered.
hope someone could help me.
0
Zaryab Khan
I have been trying to compute Business hours a ticket spends in different/particular ticket group.
I have tried this formula but with very marginal success (Computed only 3 tickets with non zero value and 6 tickets with an incorrect value of 0) the hours were computed in calendar hours which is not fit for reporting (have yet to come across a solution for Business hours)
IF ([Changes - Field name]="group_id") AND ([Changes - Previous value]="44xx704xxxxx")
THEN VALUE(Field changes time (min))/60
ENDIF
1
Jordan Means
I am trying to create a metric that computes the difference between when a ticket is opened and the first time it goes Pending or On-Hold. First Reply Time and Time Pending don't fit that need.
Any advice on how you'd create this?
0
Christophe Tiraboschi
Hi folks!
Sandra de Jong,
1. You could create a calculated attribute that returns the timestamp of when the ticket was assigned to this group:
Then make a time difference between that timestamp and now in a calculated metric:
I would recommend testing the result of this metric on a couple of tickets before trusting it blindly to make sure it works as expected.
You can find more information about the DATE_DIFF functions in this article if you want to adapt it to your needs:
2. You can just use NULL to exclude or include all possible values. For instance:
Basically, we measure the time for which the ticket field did not have a NULL value. Once again, please test this metric for a couple of tickets to make sure it works according to your expectations.
Zaryab Khan, I cannot see a workaround for reporting on this during business hours. The initial metric Field changes time (min)) only uses calendar hours. We cannot even create a calculated metric as a workaround, since the functions that measure time cannot exclude specific ranges of hours:
I am sorry I could not provide better news.
Jordan Means, I think this is doable. You could create a calculated attribute using the DATE_FIRST function to capture the timestamp of the first time the status changes from Open to something else. Then, you could create a calculated metric that measures the time between the ticket creation and the timestamp returned by your attribute. Something like:
You can find more information about the DATE_FIRST function and the DATE_DIFF functions in those articles:
I hope this helps!
-1
Tobias Rohling
Hey,
I have a question regarding the first report:
"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."
The formula e.g. behind the metric "AVG (On-Hold status time (hrs))" is:
IF ([Changes - Field name] = "status" AND [Changes - Previous value]= "hold") THEN VALUE(Field changes time (min))/60 ENDIF
This formular means, that every status change from "on-hold" of all tickets is being used to calculate the metric, right?
Lets assume we have two tickets in total:
#1: was in status on-hold one time for 4h
#2: was in status on-hold two times - first time 4h, second time 2h
With this formular we use each status change on its own:
4h+4h+2h / 3= 3,33h AVG on-hold status
But 3,33h is not the "the average time your solved tickets spent in" status on-hold, what the article says. Because the formular just ignores the "per ticket" and just calculated each status update.
The actual time would be:
#1: 4h
#2: 4h + 2h = 6h
-> 4h+6h / 2= 5h AVG on-hold status per ticket
So imo you need first the SUM "on-hold status time" for each ticket and only then calculate the average.
Do I have a thinking error here or is this article wrong?
Thanks! =)
1