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: Ticket Updates 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: Ticket updates, 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: Ticket updates 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: Ticket updates 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: Ticket updates, 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:
101 Comments
We can not choose the dataset "Support: Ticket Updates". The "Zendesk support" does not have this option too. We can only choose between "Support: Ticket Events" and "Support: Tickets, Users, Organizations". How can we get the right dataset?
Hi Lennart,
Support: Ticket Events is the legacy name of the Support: Ticket Updates dataset. Its content is exactly the same. So, in your case, you can create the metrics listed above in the Ticket Events.
In future "Ticket Events" will be renamed into "Ticket Updates".
Hi Eugene,
then we chose the right dataset. If we configure it as you say in your instruction, we got this exception:
It means that there is an issue with the syntax of the metric. Probably you used different metric names but forgot to update the related metric formula according to the names you had set. I will create a ticket for you to check this.
We have an german client and need to take take the german expressions... Now it works. Thanks for your help.
Hello, can you tell me how we could round the time tracking values to the nearest 15 minutes?
Hey Phil!
For this scenario you would actually need to create a calculated attribute using one of your Time Tracking - min metrics. It would look something like this:
IF (VALUE(Time Tracking Metric- min) <= 15)
THEN “0-15 min”
ELIF (VALUE(Time Tracking Metric- min)>15 AND VALUE(Time Tracking Metric- min) <= 30)
THEN “15- 30 min”
ELIF (VALUE(Time Tracking Metric- min)>30 AND VALUE(Time Tracking Metric- min) <= 45)
THEN “30-45 min”
ELIF (VALUE(Time Tracking Metric- min) > 45)
THEN “>45 min”
ELSE “No update”
ENDIF
Where (Time Tracking Metric- min) is a placeholder for whichever metric in minute dimensions you want to use. I'm not sure if this is exactly what you're looking for but this is about as close as we can get.
More on calculated attributes here:
Hope this helps!
Dear Eugene Orman ,
Can you please advise if there is a way for me to create a Time tracking by Group report?
For Example: When a ticket is received in "group A" they make some changes for 3h and assign it to "group B" for 2h, then they assign it to "group C" for 1h and they close it.
So the report should look like:
Ticket id Group A Group B Group C Total resolution Time
123345 3h 2h 1h 6h
132665 1h 2h 3h 6h
Please help me to achieve this.
Thank in advance.
Hey Eugene and the ZD Team,
I am struggling to get the time tracking to work at all. I have had the time tracking app active since the beginning of February, but I am not getting any tracked tickets with these queries.
How can I get this working? I need it for reporting asap. Thanks!
Hey Boyan!
You can achieve this Time Tracking by Group report with the following:
Metrics: Sum(Update Handling Time - min)
Columns: Update Ticket Group
Rows: Ticket ID
A Simple Total result manipulation for the Sum of columns will allow you to include your Total resolution time (based on the time tracking data).
(please note that the Update Ticket Group filter in my example is only included for clarity and may not be necessary for your purposes)
Does handle time start when the ticket changes from New to Open or when the first agent comment occurs?
These metrics are displaying aggregated time. How do we go about calculating a metric we can use per agent to show during a specific time period how much time they spent on a ticket? What I mean is, the update handling time may show you the last update this week, but then the total time is showing not the total time this week, but the total time - from all time. How do you fix this? I'm trying to create an agent productivity report showing how much time each agent spent on specific tickets during a week
@Whitney, the handling time is recorded at each agent comment submission. The timer starts counting once the agent accesses the ticket and ends when the update is submitted. So, it is independent of the ticket status field.
@Mark, you can simply slice the SUM(Update handling time) metric by the Updater Name and filter it by the specific time range. So, the report will be very similar to the Example 3 from this article, but you will use the SUM aggregator instead of AVG.
Hi Whitney, hi Mark,
Eugene's right! @Whitney, the handling time is measured between the time the agent opens a ticket, and clicks on submit.
@Mark, just follow Eugene's instructions and you'll get the data that you need on agent handling time :-)
Thank you, Eugene!
Hi guys,
when i copy and paste the above i get an error message and i am therefore not able to get the AHT displayed.
Any tipps?
Thanks
Paul
@Paul, If your user profile language is not in English replace the attribute names in the formula with attributes in your language. As well, check if the attributes you need exist in the dataset you are using.
With regards to Example 1 - we can have sometimes up to 3 people working on different activities in a single ticket for a customer.
What do I need to do in order to show for a particular ticket which of my team members worked on a the ticket and the time they each expended?
Hi guys!
Thank you for the recipes! Im wondering if there's a way to know how many time a ticket spent in a status?
i.e. How many time our tickets spent in hold or pending?
And in which tickets a trigger made changes?
Thank you in advance :)
Hey Team,
Thanks for creating so many great recipes for us to use! I did want to ask if you could direct/help create a recipe I am looking for. I am interested in Public Comments per Ticket for a specific channel. I would be interested in seeing the average across the channel. Would love to hear if this is possible.
Thanks!
Hi,
Is it possible to calculate the ticket handling time from a report like this, per quartiles?
Thanks!
@Johann!
It looks like you're trying to use Example 1 for a use case that it is not intended for. Example 3 is more in line with what you are looking to report on to expose the update handling time spent per Agent.
@Segio
The Ticket Updates dataset includes metrics for Duration - Field changes you can use for this purpose.
If you wish to see which Tickets a Trigger fired on you would need to update the Trigger to add a specific data point (such as a Tag) and then report on which Tickets contained that Tag. Trigger usage cannot be reported on in Explore without these changes.
@Harvey
You can report on Public Comments per Ticket by Channel with this configuration using the Ticket Updates Dataset.
You would then need to add a Result Manipulation for Totals with the Average aggregator to achieve your desired results.
Hi,
Is Tymeshift integrated with Zendesk Explore?
Does anyone know if it is possible to get agents' working time in Zendesk Explore? I mean All of the time logged for the agent, by excluding Lunch from occupancy?
Thanks!
Hi Carla,
Tymeshift is a 3rd party integration so you'll actually want to reach out to them directly to discuss whether it's compatible with Explore or not. You can contact them at the following email address for further assistance: support@tymeshift.com
Hope this helps!
Thanks Brett!
What about my previous inquiry?
For "Ticket Handling Time (min)" how do I get it to show actual time in minutes and seconds? I don't want it to round
@Mistyamber, simply use the SUM aggregator instead of AVG. See Choosing metric aggregators for more details. For minutes you can use Ticket handling time (min) calculated metric and Total time spent (sec) custom field metric for seconds.
Hi All-
Just activated the Time Tracking app hours ago. Trying to show the "Ticket Handling Time" per ticket by assignee name but no success. Please help
I just want to get the min spent per ticket.
I am trying to do that also, instead of generic time but finding out actual values using the time tracking app.
Hello,
We have Support Professional, which entitles us to Explore Lite.
We want to know if it is possible to get metrics from the Time Tracking app on Explore Lite. For instance, time per ticket, total time accrued by tickets from a particular organisation, etc.
Any info?
TIA guys!
Rolly Valenzuela, the Time tracking app data will start flowing into Explore once the app is used on some of the tickets. Tickets that were closed before you installed the app will not have any ticket handling data.
Jared Allenbrand, If you want to see values submitted per ticket you can use the same approach as Rolly. Simply create a query in the Tickets datasets. You can even use the original metric to see the raw data in seconds recorded by the app. Here is a demo - https://cl.ly/e38494a0ccfb
Colum Wedel, Since Explore Lite doesn't provide the ability to create custom reports displaying the Time tracking app data is not possible. You need to be on Explore Pro to access this data.
Please sign in to leave a comment.