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:
102 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?
We have several agents assist in a case. Is there a way we can see how much time each individual agent spends in each case? The Example 1: Ticket handling time by ticket ID report shows an overall time spent in the case for the last agent that accessed it and I'd like to see actual time by agent.
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 Francesco,
It looks like you have a ticket created with our Customer Advocacy team related to this issue. They will follow-up with you on the ticket since they may need to look at account-specific information to troubleshoot further.
Cheers!
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.
Casey Moore
You'll need to first add **/60** to your metric formula so the minutes are converted to hours.
You can then edit the display format of your query as shown in the screenshot below:
Mistyamber Reynolds can you provide a screenshot of the query you're trying to set up so I can take a look at your metrics/attributes?
Hello, Here is where I am stuck. I report back on how many hours we spend on each client a month. I have no issue getting those hours to show up. My issue is that an agent can spend an hour in April on an issue and another hour in May. Especially as the month closes. When I pull May's report, the ticket doesn't show up because it was created in April. I also track total time spent per agent. Same issue. The hours get logged in the month the ticket was created, not based on when they spent the time working on them. Example: If I create a ticket April 30th and spend 1 minute on it, and spend 10 hours on May 1, the report says I spent 10 hours on that ticket in April because thats when it was created. Makes tracking billable hours impossible. Maybe I am using the wrong data or metric for the date ranges?
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 Eugene,
then we chose the right dataset. If we configure it as you say in your instruction, we got this exception:
Hi Makmur,
If you're using Explore Professional, your data syncs one hour after the most recent sync ended. The sync time is randomized within the hour.
Currently, there's no realtime syncing with Explore but something the team is looking into.
Cheers!
Chris Bulin
Thanks will give it a try
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 :)
Hi Eugene Orman,
Our Zendesk has custom ticket fields and needs to calculate the time spent on the ticket updates when those fields had specific historical values (they change over time), not just for the current value of the fields. How do we get this?
@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.
I'm trying to build a report that identifies the amount of time spent per update by the group the ticket was assigned two. Two years ago, a Zendesk rep posted this solution:
However, I do not believe this is accurate. The definition of Update Ticket Group is:
The group to which a ticket was assigned at the end of an update. For example, if a ticket was reassigned from Tier 1 to Tier 2, the attribute returns Tier 2.
What we want to capture is the group at the *beginning* of the update, because the agent who performs the update is likely a member of *that* group, not the group the ticket is *handed off to*.
How can we report using the group the ticket was assigned to *before* the update was completed?
Ideally we would instead use the fact "Updater group", but that doesn't seem to be a fact that's available in the update dataset.
Hi team,
Just want to ask, for time tracking data is it can only shown by D+1 or real time? We are using Zendesk support professional and Zendesk explore professional.
Thank you
Hey Phil,
This cannot be done within Explore. If you're tracking this information in an organization field, you would need to use automations as I mentioned above but it may be a bit tricky getting the time tracking data from the ticket over to the organization field. I'm thinking you can use our API to pull this information from a ticket field and populating it into an org field using a custom script. However, that may be more trouble than it's worth.
I wish I was able to provide an alternative solution for you here. It may be beneficial to create a separate post under our Support Troubleshooting and Q&A topic to help provide visibility to other users in the forum. Once you've created a post, I'm happy to get it added to our monthly Community Roundup and get your question out there :)
Hello Chris, Yes, I can confirm I am using Support: Tickets (default) dataset.
Is there something I an import into explore without having to manually create the metrics to then have the dashboard?
Hi,
Is there a way to format the time metric as HH:MM:SS? We use this in insights, but I cant seem to understand how the display format works in explore.
Edit: Nvm. Found out the metric needs to be in seconds and the format used is duration.
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)
Hey Phil,
Have you had a chance to review our Reporting with custom fields article for Explore? You can pull these numbers from your Support account an email sent over to the manager from Explore if necessary. The only downside is that you can set up scheduled emails in Explore once that number has reached a certain threshold.
Let me know if the above isn't what you're looking for.
Cheers!
Hi! I've created the "Handling time per agent" bar chart as per the instructions. I was wondering if there's a way to overlay the average handling time for the team over the individual average handling times to compare how each agent performs compared to the average handling time across the whole team?
Hi,
Is it possible to calculate the ticket handling time from a report like this, per quartiles?
Thanks!
I am trying to find a way to relate time tracking to ticket tags.
This way we can match agent efficiency, IE Time spent on specific subjects/tags.
Does anyone have a way to do that?
Hi Jim -
You would need to rebuild the reports in Insights; there's not functionality to do a direct export/import between the two systems.
Hey Mistyamber,
I believe what Devan is referring to is adding the Updater attribute to your report which should get you the individual agent contributions. More information in our Metrics and attributes for Zendesk Support article which I've linked for you.
When using the Updater attribute, you'll want to make sure you add the Updater Role > is not > End-user filter to your report.
Let me know if you run into any issues setting this up :)
Hey Jason
I eventually got help from @... on a separate ticket and ended up with a query I think is getting us what we want. No complaints so far.
So we set up the query like this:
- Created Update handling time (min) metric
- Create new query - Choose a dataset: Support -> Ticket Updates -> Support: Ticket updates
- Use Update handling time (min) in Metrics
- Use Updater name in Rows
- Use Update - Timestamp in Filters (example: 'this week')
- Use Updater name in Filters (this was easiest for me to pick out my team from all potential updaters)
- On the right choose Visualization and pick Table
Now to see more detail and see time logged for each ticket that week you can:
- Click on the minutes of an agent and choose Decompose
- Then choose Ticket ID
What I've seen is that using this approach will give me time logged by a agent for each ticket for this week, regardless who is assigned the ticket and regardless of ticket status.
Hope that helps!
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!
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!
Please sign in to leave a comment.