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

• 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

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.

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.

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.

1. In Explore, click the query ( ) icon.
2. In the Queries library, click New query.
3. On the Choose a dataset page, click Support > Tickets > Support: Ticket updates, then click New query. Query builder opens. 
4. From the calculations menu (), click Standard calculated metric.
5. On the Standard calculated metric page, enter a name for your calculated metric from the list below, for example Update handling time (min).
6. 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)") 
   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 you are 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. In the Metrics panel, click Add.
10. From the list of attributes, expand the Calculated metrics folder, find your calculated metric, then click the pen icon next to it.
11. From the Options menu of the Standard calculated metrics page, choose Edit aggregators.
12. Set the metric's default and visible aggregators. If you are creating the Update handling time (min) metric the aggregator menu looks like this:
    
13. Continue creating the calculated metrics as shown below.

Tip: After you create the metrics, you can change their display format on the Standard calculated metrics page for each metric. To do so, locate the metric in the Calculated metrics folder and click the pen icon next to it. On the Standard calculated metrics page, click Options > 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 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.

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

1. In Zendesk Explore, click the query ( ) icon.
2. In the Queries Library, click New query.
3. On the Choose a dataset page, click Support > Tickets > Support: Ticket updates, then click New query. Query builder opens.
4. Now, add your metrics. In the Metrics panel, click Add.
5. From the list of metrics, choose Calculated metrics > AVG(Update handling time (min)), then click Apply.
6. In the Columns panel, add the attribute you want to report against the metric. Click Add.
7. From the list of attributes, choose Updater > Updater name, then click Apply.
8. In the Filters panel, add the attributes that will refine the results. Click Add.
9. From the list of attributes, choose Updater > Updater role, then click Apply.
10. Click the Updater role attribute you just added and select Admin and Agent from the menu.
11. In the Filters panel, click Add
12. From the list of attributes, choose Time - Ticket update > Update - Date, then click Apply.
13. Click the Update - Date filter you just added, then click Date range and select the time period you want to show.
14. From the visualizations menu (), choose Bar.
15. 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.
16. Adjust colors, labels and other customizations in the chart configuration menu ().  

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