Explore recipe: Calculating estimated cost of service

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.

Important: Values produced by this calculation are estimated and are intended only to give you an indication of support charges. This article is provided for instructional purposes only. Please post any issues you have in the comments section below.

This article contains the following topics:

• What you'll need
• Creating a total time spent metric
• Creating an hourly rate metric
• Creating the cost of service metric
• Using the cost of service metric
• Next steps

What you'll need

Skill level: Medium

Time required: 20 minutes

• Zendesk Explore Professional
• Editor or Admin permissions (see Adding users 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 a total time spent 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.

1. In Explore, click the query ( ) icon.
2. In the Queries Library, click New query.
3. On the Select a dataset page, select the Support: Ticket Updates dataset. This dataset is where the historic custom field data from the Time Tracking app is stored.
4. In Query Builder, open the calculations menu (), then click Standard calculated metric.
5. Name the new metric Total time spent (hours).
6. In the formula window, enter the following formula:

   SUM(Total time spent (sec))/3600

7. When you are finished, click Save.

Creating an hourly rate metric

Now, create a metric that details how much agents are paid each hour. This example assumes that you have one tier of support and all of your agents are paid the same amount.

1. In Explore, click the query ( ) icon.
2. In the Queries Library, click New Query.
3. On the Select a dataset page, select the Support: Ticket Updates dataset. This dataset is where the historic custom field data from the Time Tracking app is stored. If you don't see the Ticket Updates dataset, you can create it using the procedure in Working with datasets.
4. In Query Builder, open the calculations menu (), then click Standard calculated metric.
5. Name the new metric Hourly rate.
6. In the formula window, enter the hourly rate for your agents. for example:

   15.99

7. When you are finished, click Save.

Creating the cost of service metric

The final metric you need to create multiples the time spent and hourly rate metrics you just created to calculate the actual cost of service.

1. In Explore, click the query ( ) icon.
2. In the Queries Library, click New Query.
3. On the Select a dataset page, select the Support: Ticket Updates dataset. This dataset is where the historic custom field data from the Time Tracking app is stored. If you don't see the Ticket Updates dataset, you can create it using the procedure in Working with datasets.
4. In Query Builder, open the calculations menu (), then click Standard calculated metric.
5. Name the new metric Cost of service.
6. In the formula window, enter the following formula:

   SUM(Hourly rate)*SUM(Total time spent (hours))

7. 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: Ticket Updates dataset.

To use the cost of service metric in a report

1. In Explore, open a new query using the Support: Ticket Updates dataset.
2. In the Metrics panel, click Add.
3. From the list of metrics, choose Calculated metrics > Cost of service, 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.