How much does it cost your support team to tackle questions about various issue types or how much time do you spend on various customers?
In this report, you'll use data collected by the Zendesk Time Tracking app to create a cost of service metric you can use in your reports.
This article contains the following topics:
What you'll need
Skill level: Medium
Time required: 20 minutes
- Zendesk Explore Professional
- Editor or Admin permissions (see Giving agents access to Explore)
- Ticket data in Zendesk Support
- The Zendesk Time Tracking app must be installed, and must have collected data
Note: The time tracking app stores data in custom numeric fields that are synchronized with Explore. If you have installed the Time Tracking app recently, the data required to build necessary calculated metrics might not yet be synced with Explore. This sync occurs once an hour.
Creating the cost of service metric
The time tracking app tracks how long agents spend working on various tickets. The app reports this in seconds, but for this recipe, you’ll convert the value into hours by creating a new calculated metric.
- In Explore, click the query (
) icon. - In the Queries library, click New query.
- On the Choose a dataset page, click Support > Tickets > Support: Tickets dataset. This dataset is where the historic custom field data from the Time Tracking app is stored.
- In Query builder, open the calculations menu (
), then click Standard calculated metric. - Name the new metric Cost of service metric.
- In the formula window, enter the following formula:
VALUE(Total time spent (sec))/3600*(your hourly rate)
- For example, if your hourly rate is $15.99, enter
VALUE(Total time spent (sec))/3600*15.99
- When you are finished, click Save.
Using the cost of service metric
Now you've created the metric, you can use it in any of your reports that use the Support: Tickets dataset.
To use the cost of service metric in a report
- In Explore, open a new query using the Support: Tickets dataset.
- In the Metrics panel, click Add.
- From the list of metrics, choose Calculated metrics > Cost of service metric, then click Apply. Explore displays the total cost of service for all tracked time spent on tickets.
Now, you can add attributes to the Columns or Rows panel to slice this cost data to your needs. For example, add the Ticket ID attribute to see the cost for each ticket, or the Assignee name attribute to see the cost for each assignee.
Next steps
You can change the display format of this metric to dollar amounts from the display format page of the chart configuration menu ( 
) . By slicing this metric by various attributes, you can produce detailed cost estimates for different parts of your organization.
For more help, see Creating queries.
12 Comments
How do I build the shown Dashboard?
Hi Fernando, most of the queries on that dashboard can be created in a similar way to the recipe in the article. For example, for "Cost of service by group", assuming a ticket costs you $8 to close, you could create a calculated metric containing the formula COUNT(Tickets)*(8).
You'd use that as the metric, and Ticket group as the attribute.
I'll add some more cost example recipes as soon as it's possible.
When I try and copy and paste, or type, the SUM(Hourly rate)*SUM(Total time spent (hours)) formula, it gives me "Error at line -1 and column-1
Hi Jon, I've run through and test this. A few tips:
1. Ensure you are using the Ticket Updates dataset
2. The recipe required you to create three standard calculated metrics. You'll need to create all three, in order, for the recipe formula to work.
3. Example showing it working here. If you continue to have problems, please let us know and I'll open a ticket to investigate.
Thanks Rob, still not working for me. I have followed this to a T. I JUST installed the time tracking App, so perhaps it needs to log some information before this can work.
Thanks for responding Jon, that could be it. I know from my own testing that it takes a while to filter through and the Total time spent (sec) metric won't exist in Explore until that happens. Please do let me know if it continues to fail and I'll help get a ticket opened :-)
@Jon i think there is too many brackets on the example remove the last one
@Rob Im still new to this stuff, ive created the calculations.
How do i put them together to make a report with it?
Yeah, I'm getting either an error message, or a wildly inaccurate calculation.
@Jeremy I've added a new section to the end of the article that explains how to use the calculation in a report. I hope this helps.
@Jon, I'm not sure why your report is failing. I'm going to open a ticket about this so someone can investigate the problem with you. I'll make any necessary doc updates based on their findings.
@Rob I'm facing a very buggy behaviour following your instructions. If I copy/paste your query:
I receive this error: "There's an issue with the formula. Check your calculation syntax and try again."
But there are 3 open brackets and 3 close brackets so there should be no problem.
And if I remove one of them it says is fine:
And it shouldn't because there are now 3 open brackets and just 2 close brackets.
Hi Francesco Mantovani
This article was revised a while back to use a different formula. The formula now used is
VALUE(Total time spent (sec))/3600*(your hourly rate)
Could you try that and see if it works? Bear in mind that you must be using the time tracking app, and the app must have collected data for this to work. Hope this helps!
Please sign in to leave a comment.