Time Tracking app: metrics you need to be measuring

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. Using 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:

• Choose the approach
• Create the metrics
  • Metrics creation steps
  • Metrics configuration
• Example 1: Ticket handling time by ticket ID
• Example 2: Ticket handling time by month and organization
• Example 3: Update handling time by agent

Choose the approach

As with any other Support ticket data, there are two approaches for analysing 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 analyse agent performance when the ticket is handled mostly by the same agent and for reporting on time your team spent on supporting 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. We will be using 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)

Create the metrics

If it is the first time you are creating a calculated metric, see Creating standard calculated metrics and attributes.

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.

Metrics creation steps

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.

1. In Explore, click the query ( ) icon.
2. In the Queries Library, click New query.
3. On the Select a dataset page, depending on the metric, select the Support: Tickets or Support: Ticket updates dataset. If you don't see the necessary dataset, see Which datasets use the latest data schema? to identify which dataset to use.
4. In Query Builder, open the calculations menu (), then click Standard calculated metric.
5. In the Standard calculated metric menu, enter a name for your custom metric from the list below, for example Update handling time (min).
6. In the formula box, copy and paste the formula you want from the metrics configuration sections below. If you were creating the Update handling time (min) metric, the Standard calculated metric menu would look like this:

   

7. 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:
   
8. When you are finished, click Save.
9. Open the data structure menu (), locate the metric in the Calculated metrics folder and set the metric's default and visible aggregators. If you were creating the Update handling time (min) metric the aggregator menu would look like this:
   
10. Continue creating the calculated metrics as shown below.

Tip: After you create the metrics, you can change their display format in the data structure menu (). To do so, locate the metric in the Calculated metrics folder, click the arrow pointing down beside the name of the metric, click Edit display format and choose Custom. For example, you could add the suffix "min" to the Update handling time (min) metric, or increase the number of decimal places on any metric.

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) 
 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) 
 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.

1. In Zendesk Explore, click the query ( ) icon.
2. In the Queries Library, click New Query.
3. On the Select a dataset page, select the Support: Tickets dataset.
4. Now, add your metrics. In the Metrics panel, click Add.
5. From the list of metrics, expand Calculated Metrics and choose the Ticket handling time (min) metric.
6. In the Rows panel, add the attribute you want to report against the metrics. Click Add.
7. From the list of attributes, expand Ticket, and then choose Ticket ID.
8. Expand Assignee, and choose Assignee name.
9. Expand Requester, and choose Requester name.
10. Expand Time - Ticket solved, and choose Ticket solved - Date.
11. Click on one of the Time - Ticket solved, then Date range and select the time period you wish.
12. From the visualizations menu (), choose Table.

You'll end up with a table that looks something like the following:

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.

1. In Zendesk Explore, click the query ( ) icon.
2. In the Queries Library, click New query.
3. On the Select a dataset page, select the Support: Tickets dataset.
4. Now, add your metrics. In the Metrics panel, click Add.
5. From the list of metrics, expand Calculated metrics, then choose SUM(Ticket handling Time (hrs)) and AVG(Ticket handling time (min)).
6. Click the AVG(Ticket handling time (min)) and select Dual.
7. In the Columns panel, add the attribute you want to report against the metrics. Click Add.
8. From the list of attributes, expand Time - Ticket created, and choose Ticket created - Year and Ticket created - Month.
9. Click the Ticket Created - Month, then Date range and select the time period you wish.
10. In the Rows panel, add the attribute you want to have in the row selector. Click Add.
11. From the list of attributes, expand Ticket organization, and choose Ticket organization name. 
12. Click the Ticket organization name, and choose the organizations you want to look at. 
13. From the visualizations menu (), choose Line.
14. Adjust clolors, labels and other customizations 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.

1. In Zendesk 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.
4. Now, add your metrics. In the Metrics panel, click Add.
5. From the list of metrics, expand Calculated metrics, then choose AVG(Update handling time (min)).
6. In the Columns panel, add the attribute you want to report against the metric. Click Add.
7. From the list of attributes, expand Updater, and then choose Updater name.
8. In the Filters panel, add the attributes that will refine the results. Click Add.
9. From the list of attributes, expand Updater, and then choose Updater role.
10. Click the Updater role and select Admin and Agent from the menu.
11. In the Filters panel, click Add, expand Time - Ticket update and then choose Update - Date.
12. Click the Update - Date, then Date range and select the time period you wish.
13. From the visualizations menu (), choose Bar.
14. 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.
15. Adjust clolors, labels and other customizations in the chart configuration menu ().  

You'll end up with a chart that looks something like the following: