If you're using the Time Tracking app, and have Insights, you can track the time your support team spent on every Zendesk ticket.
With the app, support managers gain visibility into the actual time spent across all your customer service interactions. While the app tracks time automatically, you’ll need to build a few custom metrics and reports in Insights to analyze your team’s time logs. We’ve created a four-part series to walk you through best practices on how to best report on time spent:
- The time-tracking metrics you need to be measuring (this article)
- Leaderboards, time logs, top customers
- Advanced time-tracking report: A look inside your agents’ day-to-day productivity
- Advanced time-tracking report: The most costly support issues for your organization
This first article in the series walks you through setting up three essential metrics in your Insights account. These metrics are the building blocks that you will use to create reports in our other recipes and an be used in any custom report you want to make later.
Note: If you recently installed the Time Tracking app, the custom fields required to build these metrics might not yet appear in Insights. The fields become available after your ticket data sync with Insights. This sync occurs once an hour for Enterprise and once a day for Professional.
For general information on setting up the Time Tracking app, see Setting up Time Tracking.
Metric 1: Total time spent
This metric will measure the total time spent on each of your tickets through the Time Tracking app. This metric is used as part of the other two metrics in this recipe, so be sure to follow the instructions carefully.
Skill Level: Basic
Time Required: 10 minutes
Instructions
- Click Reporting (
) in the sidebar, then click the Insights tab. Click the GoodData link in the top right corner of the panel.
- In Insights, in the top menu, click Manage > Metrics > Create Metric > Custom metric.
- In the Name your metric field, enter Total time spent (min).
- Click into the metric editor and enter SELECT SUM(IFNULL(
- Add an element by going to the menu on the right side of the screen and clicking Facts , then select [Numeric Field] New value and click Add Selected to add the fact to your metric.
- Back in the Metric editor, add ,0) - IFNULL(
- Add another element to your metric by clicking Facts , then then select [Numeric Field] Old value and click Add Selected to add the fact to your metric. This fact should be right next to the last one you added.
- Back in the metric editor, enter ,0)) / 60 WHERE
- Add another element to your metric by clicking Attributes > Numeric Field. Once Numeric Field is selected, add it to your metric by clicking Add Selected.
- Back in the metric editor, enter an equals sign (=).
- Click Attribute Values. You will see a list of attributes again. After you select the attribute you're using for your metric, you can then select the attribute value as your final element.
- Select the Numeric field attribute, then the Total time spent (sec) attribute value. Your attribute value will appear in orange.
- Add the element by clicking Add Selected.
- Your final Metric should appear as follows: SELECT SUM(IFNULL([Numeric Field] New value,0) - IFNULL([Numeric Field] Old value,0)) / 60 WHERE Numeric Field = Total time spent (sec)
- Click Add at the bottom right.
- On the next screen, under Metric Format , click on #,##0.00 and change it to #,##0.0 min
Metric 2: Avg time spent per ticket (min)
This metric will measure the average time spent on each individual ticket. This will use the metric we previously created in this recipe, so be sure not to skip around. We’ll use this metric later in some of our reports to get a birds eye view on how long tickets are taking your team to complete.
Skill Level: Basic
Time Required: 5 minutes
Instructions
-
Click the Reporting (
) in the sidebar, then click the Insights tab. Click the GoodData link in the top right corner of the panel.
-
In Insights, in the top menu, click Manage > Create Metric > Custom metric .
-
In the Name your metric field, enter Avg time spent per ticket (min).
-
Click into the metric editor and enter SELECT AVG(SELECT.
-
Add an element by going to the menu on the right side of the screen and clicking Metrics > Total time spent (min) and click Add Selected to add the metric to your metric. Note that this is the metric we created earlier in the recipe.
-
Click in to the metric editor again and enter BY.
-
Add another element by going to the menu on the right side of the screen and clicking Attributes, then Tickets, then select Ticket Id and click + Add Selected to add the Attribute to your metric
-
Click in to the metric editor again and enter ).
-
Your final metric should read SELECT AVG(SELECT Total time spent (min) BY Ticket Id).
-
Click Add at the bottom right.
-
On the next screen, under Metric Format , click on #,##0.00 and change it to #,##0.0 min
Metric 3: Avg time spent per update (min)
This metric will measure the average time spent on each update of a ticket. This will use the first metric we created in this recipe, so be sure not to skip around. We’ll use this metric later in some of our reports to get an up close look at your agent activity to help you tighten up efficiency.
Skill Level: Basic
Time Required: 5 minutes
Instructions
-
Click the Reporting (
) in the sidebar, then click the Insights tab. Click the GoodData link in the top right corner of the panel.
-
In Insights, in the top menu, click Manage > Create Metric > Custom metric .
-
In the Name your metric field, enter Avg time spent per update (min).
-
Click into the metric editor and enter SELECT AVG(SELECT.
-
Add an element by going to the menu on the right side of the screen and clicking Metrics > Total time spent (min) and click Add Selected to add the metric to your metric. Note that this is the metric we created in the first section of the recipe.
-
Click in to the metric editor again and enter BY.
-
Add another element by going to the menu on the right side of the screen and clicking Attributes, then Ticket Updates, then select Ticket Updates and click + Add Selected to add the Attribute to your metric
-
Click in to the metric editor again and enter ).
-
Your final metric should read SELECT AVG(SELECT Total time spent (min) BY Ticket Updates).
-
Click Add at the bottom right.
- On the next screen, under Metric Format , click on #,##0.00 and change it to #,##0.0 min
169 Comments
Is there a way that the total time spent could be in hours as minutes is useful but when running a report i don't want to have to transfer everything from minutes to hours.
@Sam - yes! All you need to do is divide your metric by 60 another time to go from minutes to hours. For example, the MAQL will look like this:
SELECT SUM(IFNULL([Numeric Field] New value,0) - IFNULL([Numeric Field] Old value,0)) / 3600 WHERE Numeric Field = Total time spent (sec)
@brent Thank you! Legend!
This was a very helpful article. I used all 3 reports. Thanks!!
I keep getting this error with the 2nd metric: Unexpected 'BY'. Expecting: )
I have tried it with 1-4 ) brackets with no luck. Please let me know what I am doing wrong.
@Zachary it looks like you're using the "Total time spent (sec)" attribute value instead of the "Total time spent" metric in step one. Attribute values appear in orange text, and metrics appear in green text.
In the end, it should look like this:
I must be missing something as I do not have that metric in my list. Hmm
@Zachary that metric is not included in Insights by default because it uses fields that only exist once the time tracking app is installed. You'll have to build it by following the steps in the "Metric 1: Total time spent" section of this article.
When using these metrics and app, we found two issues:
We created a metric to handle values that are out of range and resolve negative updater values.
This replaces values greater than 8 hours with 5 minutes (simpler than try to test for ticket age at the time of update) and works at the update level to remove the influence of the previous time spent by other agents. Then we just need to pull the average.
Hi There:
We have just recently started using Time Tracker and as such, there are many 'solved' tickets that simply don't have times associated with them.
I believe this is skewing our ticket times. I am only speculating, but would it average 'zero values' with other tickets that actually do have times? I tried to create a filter that only pulls from tickets that actually have times associated with them, however, the 'Total Time Spent' metrics don't seem to be available for use in Filters.
Any advice?
Thanks.
Hi Chris!
These recipes should keep that from happening. If a ticket has no time tracking data, it should return a null value, not a 0. That means those ticket should be excluded automatically.
If you're seeing something else, double check your metrics and make sure they match the recipes above. The most likely issue would be using IFNULL in the wrong place.
As for your second question, you can't use metrics in a "List of Values" filter. That only works for Attributes. Instead, add a Numeric Range Filter:
This should limit your report to tickets that have a value in the Total Time Spent (min) metric.
Happy reporting!
Here is what I have entered into the editor:
[Numeric Field]SELECT SUM(IFNULL(*[Numeric Field] New Value*,0) - IFNULL(*[Numeric Field] Old Value*,0)) / 60 WHERE *Numeric Field* = *Total time spent (sec)*
I get this error when trying to 'Add' it:
Unexpected '[Numeric Field]'. Expecting: SELECT
**EDIT**: Nevermind, solved it! Had that random [Numeric Field] in the beginning. Sorry-- thanks!
Hey Hannah! Glad you got it working. :)
Hello,
I am having trouble finding the Attribute Value "Total time spent (sec)" for the first metric. When I click Attribute Value on the Elements menu, it just opens up Attributes, instead of Attribute Values. And I am not able to find the "Total time spent (sec)" under Attributes.
Thanks!
Hi Rosalie! Attribute values are listed under their attributes. That keeps the list organized.
To find Total time spent (sec), go to Attribute Values, then select Numeric Field. This should open a new list with all the available values for that attribute, including Total time spent (sec).
I hope this helps! Happy reporting!
I'm not seeing Total time spent (sec) under Attribute Values. Do I need to add the app first?
@ Amy_Dee @Kristina - I'm missing Total time spent (sec) under Attribute Values
too - and we've had it installed for a year or more.
Any ideas?
I found a total time spent (sec) under metrics... are the instructions wrong?
Hi Andrew! For the recipes in this article, you need to use the Total time spent (sec) under Attribute Values. The recipes are correct. In your case, you may have created a metric with the same name at some point.
I recorded a short, animated GIF showing where to find Total time spent (sec) in the Metric Editor. (You must install and use the Time Tracking app before it populates in Insights.)
You can see that the "Attribute Values" option takes me to the "Attributes" list. I select Numeric Field first, since that is what I'm using in my metric. Then I can choose a value under Numeric Field. When it is selected from the right place, Total time spent (sec) will appear orange.
I hope this helps! Happy reporting!
I went through and deleted the last attempt I had made, then it worked.
I'm curious about the logic behind the formula for Metric 1: Total time spent.
Is there a specific reason you recommend doing a sum of "new value - old value" in that manner? Unless I'm missing something it seems like a sum of "Time Spent Since Last Update" or even just grabbing the max/final amount for "Total time spent (sec)" would both give you the same value, and would be much simpler.
Hi Joshua! I know it looks complicated, but this version of the recipe is a bit more reliable than simpler options.
The final value of the Total time spent (sec) field would give you the total time for the ticket as a whole, but it wouldn't allow you to see the handle time for individual updates.
The Time spent last update (sec) field has an edge case exception that we want to avoid. These metrics are based on numeric field changes. If you post two updates in a row with the exact same handle time, then there isn't technically a change to that field. The second update won't have a value for Time spent last update (sec) that can be captured in a metric. However, the Total time spent (sec) value will still increase.
When the Time Tracking app is on its default settings, these duplicate updates are rare. There's a simplified mode, though, which is far more likely to repeat values.
I hope this helps. Happy reporting!
Hey Amy,
Ahhh, I didn't consider that edge case for Time spent last update - that makes perfect sense. Thanks for the quick response!
Heya,
I am trying to build a metric similar to the Average time spent per update (min). Similar as in I am trying to break it down even more (into Avg time per public comment and Avg time per private comment) and I am running into some issues.
When trying to replace SELECT AVG(SELECT Total time spent (min) BY Ticket Updates) with SELECT AVG(SELECT Total time spent (min) BY Public Comments) it sort of works, except the resulting numbers are really far off.
And I can't seem to find a way to utilize Private Comments either.
Any ideas?
Hi Andreas! This sort of report should be possible. It's a bit different from your approach, though, due to how comments are recorded in the data model.
There are three relevant attributes here:
The Ticket Updates attribute is the base for the dataset, so it can anchor a metric. The other two can be used to filter your results.
In your case, I recommend creating the Avg time spent per update (min) metric described in the article above, where you use BY Ticket Updates. Then you can make two customized versions for comments:
Avg time spent on public comments
SELECT Avg time spent per update (min) WHERE Comment Present = true AND Public Comment = true
Avg time spent on private comments
SELECT Avg time spent per update (min) WHERE Comment Present = true AND Public Comment = false
These metrics should find the average handle time in minutes, but only for updates which include a public or private comment, respectively.
I hope this helps! Happy reporting!
Thanks! That seems to do the trick. I appreciate the help.
When trying to follow the first step, I get the following error:
What am I doing wrong?
Hi Martin,
It look like you're hitting Add. In this case, use +Add Selected in the top right.
Yikes, don't know how I missed that. Thanks Chad!
I'm experiencing the same issue as Kristina and Andrew. I've ensured that the app is installed andI can see that the Ticket Fields are added in Zendesk, but I still don't see the Total time spent (sec) in Attribute Values or anywhere else.
Please sign in to leave a comment.