The Zendesk Time Tracking app helps support managers gain visibility into the actual time spent across all your customer service interactions within a ticket. The app tracks the time spent on each ticket update, and stores this with the ticket. With Explore, you can create calculated metrics that contain this information, and use them to produce reports. Use this article to learn how to set up custom time tracking metrics and produce three sample reports.
In this article:
Choosing the approach
As with any other Support ticket data, there are two approaches for analyzing the time tracking app data.
Ticket handling time
The first approach is to look at the handling time per ticket. It can be used to analyze agent performance when the ticket is handled mostly by the same agent and for reporting on time your team spent supporting a specific end-user or organization.
If you choose to go with this approach the best place to create your queries is the Support: Tickets dataset. To do so you can create two simple calculated metrics based on the Total time spent (sec) metric and use them to report on the handling time per ticket alongside the native time metrics.
Metrics that can be created to support this approach are:
- Ticket handling time (min)
- Ticket handling time (hrs)
Update handling time
The second approach is to look at the handling time per update. It is the best way to report on the agent performance and it is suitable for the complex workflows where the same ticket is handled by multiple agents.
For this approach, the calculated metrics and queries should be created in the Support: Updates history dataset based on the data stored in the Changes - Previous value and Changes - New value attributes. You can use the NUMBER() function to transform the text attribute values into numbers.
Metrics that can be created to support this approach are:
- Update handling time (min)
- Update handling time (hrs)
Creating the metrics
If this is the first time you've created a calculated metric, see Creating standard calculated metrics and attributes.
To create the metric
Repeat the following general procedure to create each custom metric. Use the metrics configuration sections below to find the information you'll need to create each one.
- In Explore, click the query (
) icon.
- In the Queries library, click New query.
- On the Choose a dataset page, click Support > Tickets > Support: Updates history, then click New query. Query builder opens.
- From the calculations menu (
), click Standard calculated metric.
- On the Standard calculated metric page, enter a name for your calculated metric from the list below, for example Update handling time (min).
- In the Formula field, enter or paste the formula you want from the metrics configuration sections below:
Note: The formulas in this article won't work if the user profile is set to a non-English language. If your user profile in Explore isn't in English, follow the instructions in this article to make the formula work: How can I convert Explore formulas from English into another language?IF ([Changes - Field name] = "Total time spent (sec)")
If you are creating the Update handling time (min) metric, the Standard calculated metric menu would look like this:
THEN
IF ([Changes - Previous value]=NULL OR [Changes - Previous value]="")
THEN NUMBER([Changes - New value])/60
ELIF (REGEXP_MATCH([Changes - New value], "[0-9]+") AND REGEXP_MATCH([Changes - Previous value], "[0-9]+"))
THEN(NUMBER([Changes - New value])-NUMBER([Changes - Previous value]))/60
ENDIF
ENDIF - If your user profile language is not in English replace the attribute names in the formula with attributes in your language. For example, in German the formula will look like this:
- When you are finished, click Save.
- In the Metrics panel, click Add.
- From the list of attributes, expand the Calculated metrics folder, find your calculated metric, then click the pen icon next to it.
- From the Options menu of the Standard calculated metrics page, choose Edit aggregators.
- Set the metric's default and visible aggregators. If you are creating the Update handling time (min) metric the aggregator menu looks like this:
- Continue creating the calculated metrics as shown below.
Metrics configuration
Metric name: Ticket handling time (min)
This metric should be created in the Support: Tickets dataset. It returns the time spent by agents per ticket, calculated in minutes.
Copy and paste this formula into the formula field of your standard calculated metric:
VALUE(Total time spent (sec))/60
Supported aggregators when you use this metric in a query: SUM, AVG, MIN, MAX, MED
Metric name: Ticket handling time (hrs)
This metric should be created in the Support: Tickets dataset. It returns the time spent by agents per ticket, calculated in hours.
Copy and paste this formula into the formula field of your standard calculated metric:
VALUE(Total time spent (sec))/60/60
Supported aggregators when you use this metric in a query: SUM, AVG, MIN, MAX, MED
Metric name: Update handling time (min)
This metric should be created in the Support: Updates history dataset. It returns the time spent by agents per ticket update, calculated in minutes.
Copy and paste this formula into the formula field of your standard calculated metric:
IF ([Changes - Field name] = "Total time spent (sec)")
THEN
IF ([Changes - Previous value]=NULL OR [Changes - Previous value]="")
THEN NUMBER([Changes - New value])/60
ELIF (REGEXP_MATCH([Changes - New value], "[0-9]+") AND REGEXP_MATCH([Changes - Previous value], "[0-9]+"))
THEN(NUMBER([Changes - New value])-NUMBER([Changes - Previous value]))/60
ENDIF
ENDIF
Supported aggregators when you use this metric in a query: SUM, AVG, MIN, MAX, MED
Metric name: Update handling time (hrs)
This metric should be created in the Support: Updates history dataset. It returns the time spent by agents per ticket update, calculated in hours.
Copy and paste this formula into the formula field of your standard calculated metric:
IF ([Changes - Field name] = "Total time spent (sec)")
THEN
IF ([Changes - Previous value]=NULL OR [Changes - Previous value]="")
THEN NUMBER([Changes - New value])/60/60
ELIF (REGEXP_MATCH([Changes - New value], "[0-9]+") AND REGEXP_MATCH([Changes - Previous value], "[0-9]+"))
THEN(NUMBER([Changes - New value])-NUMBER([Changes - Previous value]))/60/60
ENDIF
ENDIF
Supported aggregators when you use this metric in a query: SUM, AVG, MIN, MAX, MED
Example 1: Ticket handling time by ticket ID
In this example, you'll produce a list of tickets with their handling time and some additional attributes.
Before you start, ensure you've created the custom metrics shown previously.
- In 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, add your metrics. In the Metrics panel, click Add.
- From the list of metrics, choose Calculated Metrics > Ticket handling time (min) ,then click Apply.
- In the Rows panel, add the attribute you want to report against the metrics. Click Add.
- From the list of attributes, expand Ticket, and then choose Ticket ID.
- Expand Assignee, and choose Assignee name.
- Expand Requester, and choose Requester name.
- Expand Time - Ticket solved, and choose Ticket solved - Date.
- Click on one of the Time - Ticket solved, then Date range and select the time period you wish.
- From the visualizations menu (
), choose Table.
Example 2: Ticket handling time by month and organization
In this example, you'll produce a chart showing total ticket handling time and average ticket handling time by month and organization.
Before you start, ensure you've created the custom metrics shown previously.
- 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, add your metrics. In the Metrics panel, click Add.
- From the list of metrics, choose Calculated metrics > SUM(Ticket handling Time (hrs)) and AVG(Ticket handling time (min)), then click Apply.
- From the visualizations menu (
), choose Line.
- Click the AVG(Ticket handling time (min)) metric you just added, then select Dual.
- In the Columns panel, add the attribute you want to report against the metrics. Click Add.
- From the list of attributes, choose Time - Ticket created > Ticket created - Year and Ticket created - Month, then click Apply.
- Click the Ticket Created - Month, then Date range and select the time period you want.
- In the Rows panel, add the attribute you want to have in the row selector. Click Add.
- From the list of attributes, choose Ticket organization > Ticket organization name, then click Apply.
- Click the Ticket organization name, and choose the organizations you want to look at.
- You can adjust the chart colors, labels and more in the chart configuration menu (
).
You'll end up with a chart that looks like this:
Example 3: Update handling time by agent
In this example, you'll generate a graph showing agents with the top ten average handling time.
Before you start, ensure you've created the custom metrics shown previously.
- In Zendesk Explore, click the query (
) icon.
- In the Queries Library, click New query.
- On the Choose a dataset page, click Support > Tickets > Support: Updates history, then click New query. Query builder opens.
- Now, add your metrics. In the Metrics panel, click Add.
- From the list of metrics, choose Calculated metrics > AVG(Update handling time (min)), then click Apply.
- In the Columns panel, add the attribute you want to report against the metric. Click Add.
- From the list of attributes, choose Updater > Updater name, then click Apply.
- In the Filters panel, add the attributes that will refine the results. Click Add.
- From the list of attributes, choose Updater > Updater role, then click Apply.
- Click the Updater role attribute you just added and select Admin and Agent from the menu.
- In the Filters panel, click Add
- From the list of attributes, choose Time - Ticket update > Update - Date, then click Apply.
- Click the Update - Date filter you just added, then click Date range and select the time period you want to show.
- From the visualizations menu (
), choose Bar.
- To show only the top ten users, in the result manipulation menu (
), click Top/bottom. On the Top/bottom page, select Top, then set the number to 10.
- Adjust colors, labels and other customizations in the chart configuration menu (
).
You'll end up with a chart that looks something like the following:
82 Comments
Hi -- I just started using the ticket tracking app, so i'm not anticipating to see a large amount of data but I do have time logs visible in tickets and see the app is tracking time. When I then go to explore and and run the queries as outlined above, my graphs are not populated with any data. Why would this be happening?
So the awesome Nhia resolved my query, if you are looking to see how much time agents spent on tickets then use this: Update handling time (hrs)
IF ([Changes - Field name] = "Total time spent (sec)")
THEN
IF ([Changes - Previous value]=NULL OR [Changes - Previous value]="")
THEN NUMBER([Changes - New value])/60/60
ELIF (REGEXP_MATCH([Changes - New value], "[0-9]+") AND REGEXP_MATCH([Changes - Previous value], "[0-9]+"))
THEN(NUMBER([Changes - New value])-NUMBER([Changes - Previous value]))/60/60
ENDIF
ENDIF
That will then allow you to report on all sorts of time analysis of your tickets and mirrors anytime that is captured in the Time Tracker widget / plugin.
hi i have a question why calculated update and time tracking dont match?
ex:
but when i pull data using this and i drill the data :
the problem is on example ticket i screenshoot agent rahma got total time spent is 450
and it must if follow time tracking total is 82
I set up the formula in Support: Ticket Updates data set with no problem. However, when I come back to Support: Tickets and enter the formula VALUE(Total time spent (sec))/60, I get an error. See image below. What am I doing wrong?
Based on the the way the color changes and part of the formula is underlined, I think there was something that happened when it was trying to autofill or when you were typing.
It should look like this instead:
I'd suggest deleting the formula text and starting over -- once you type "Value", you should get a popup menu of autofill options. If you keep typing a few more characters (e.g., "Value(tot"), then the list should narrow down to VALUE(Total time spent (sec)) -- hit return so that gets entered into the formula field, and you should see the green checkmark. Then just type the "/60" to add that to the end, and the green checkmark should reappear, and you should be good to go. Hope that helps!
Thanks... When I type it in, though, "Total time spent" isn't an option in the drop-down. Only "Total followers". I must have done something wrong setting up the query in the "Support: Ticket Updates" calculation.
Hi! I followed the instructions to set up the Update Handling Time (min) metric. However, the metric only shows up for some agents, with most showing up blank even though they completed updates in the specified timeframe. Any idea what I am missing?
Looks like we need more details to troubleshoot this. Let me create a private ticket on your behalf. Thanks!
Hey,
I noticed that this tutorial doesn't take into consideration the tickets that are automatically updated by the system.
For instance, an agent can be absent on Tuesday, but if some pending tickets are set to solve by the system (internal rules), then it is counted as "ticket updated by the agent". But, the action was just an automatic rule. Can we overcome that?
The Time tracking app should only run when an agent opens a ticket in Zendesk and it will only record the time spent when an agent submits an update. The data measured by the Time tracking metrics should only come from actual updates made by agents.
Hi Gab Guinto
Thanks for your reply. Then, I am experiencing the opposing of what you're saying. :(
I'll open a ticket to see that.
I litteraly copied the formula:
VALUE(Total time spent (sec))/60
But I am receiving this error:
Can anyone help me to understand why? what am I doing wrong?
Just FYI,
1. I've set my user language to English (United Kingdom)
2. I am in the "Support: Tickets" data set
Hi Nelson Garcia, have you already followed the steps to set up the Time Tracking app? If not, the Total time spent metric won't exist in your environment yet, which could be why you're seeing that error.
Hi, I already created Update handling time (min) metric in Support: Updates history dataset. Now I am trying to create Ticket handling time (min) or Ticket handling time (hrs) in Support: Tickets dataset, but I am getting this error: There's an issue with the formula. Check your calculation syntax and try again.
I am copy-pasting the formula from this article.
I created a ticket on your behalf so that we can check on this further. Thanks!
Hello!!! I need to do this without this app. I need to know all the time an agents have a ticket even if they are not into.
Can you help me please?
I don't have the app, and I wouldn't like to add another app.
I appreciate your answer!! I have many days on this.
The only way to get this functionality is via the app.
No matter I select Total Time Spent(sec) or update handling time, the result is null.
Could you help check and fix?
Hi! Anyone else encountered issues with data pulled from the Time Tracking app? We have 0 data about Status time, etc since Apr 26, 2022. Any dates prior to Apr 26 have complete populated data.
Worse than you. Lost the data from long long time ago.
Hello, so, is not a way to measure the time a ticket was assigned to each group when my ticket could be assignee to several and differents groups?
Please sign in to leave a comment.