Explore recipe: Reporting on the duration of fields

In this Explore recipe, you'll discover how to measure how long some of your ticket fields were set to a certain value. This gives you the tools to answer some important business questions like:

• What's the average time a ticket sits in each status through its lifecycle?
• How long do tickets sit with each of my support groups?

The examples in this article use the Updates history dataset which stores details of updates and events that happen in Zendesk Support.

This article contains the following topics:

• What you'll need
• Calculating the average time a ticket is in each status through its lifecycle
• Calculating how long tickets sit with a support group
• Calculating how long tickets sit with multiple support groups
• Calculating how long tickets sit with their last support group
• Calculating duration of time for custom ticket fields
• Next steps

What you'll need

Skill level: Intermediate

Time required: 20 minutes

• Zendesk Explore Professional or Enterprise
• Editor or Admin permissions (see Giving users access to Explore)
• Ticket data in Zendesk Support

Calculating the average time a ticket is in each status through its lifecycle

In this report, you'll discover the average time your solved tickets spent in each ticket status throughout their lifecycle.

To create the report

1. In Explore, click the reports icon () .
2. In the Reports library, click New report.
3. On the Select a dataset page, click Support > Support - Updates history, then click Start report. The report builder opens.
4. In the Metrics panel, click Add.
5. From the list of metrics, expand Duration - Field changes (hrs), and then choose the following metrics:
   • New status time (hrs)
   • Open status time (hrs)
   • Pending status time (hrs)

   After you add each one, click the added metric and changed its aggregator to AVG. This will display the average of all returned values. When you are finished, click Apply.

   Tip: If you have enabled the On-hold ticket status, you can also add the metric On-hold status time (hrs).
6. From the Visualization type () menu, choose Table.

Explore displays the finished report.

Calculating how long tickets sit with a support group

In this report, you’ll create a calculated metric that shows how long your tickets spend in your support group named Support Group 1. This metric can be used on its own or with any other default or calculated metrics. This can be challenging as group names are stored as an ID in the Explore Field changes attributes, but you'll learn how to discover the ID of your groups.

This report measures the time a ticket spent in a group before being moved to another group; it does not include the time a ticket spent in a group before it was solved.

Discovering your group ID numbers

Before you can create the metrics for the report, you need to find the group ID for the Support Group 1 group in Zendesk Support.

To discover your group IDs

• In your web browser, visit the following URL: subdomain.zendesk.com/api/v2/groups.json

  (replace subdomain with the name of your Zendesk subdomain)

You'll see a page showing information about all of your groups, including the group ID in JSON format. You can make the results easier to read by installing a JSON Viewer extension in your browser (for example, with Chrome, search the Chrome web store for JSON viewer).

Creating the custom metric

Now, you'll create a custom metric that displays the duration that a ticket was assigned to a group that you specify. Before you start, make sure you've noted down the group ID using the previous procedure.

To create the custom metric

1. In Explore, click the reports icon () .
2. In the Reports library, click New report.
3. On the Select a dataset page, click Support > Support - Updates history, then click Start report. The report builder opens.
4. From the Calculations menu (), choose Standard calculated metric.
5. On the Standard calculated metric page, give the metric a name, for example Time spent in group.
6. In the Formula field, copy and paste the following formula. Replace the text Group ID number with the group ID number you noted previously.

   IF ([Changes - Field name]="group_id") AND ([Changes - Previous value]="Group ID number") 
   THEN VALUE(Field changes time (min))/60 
   ENDIF

   Tip: If you're working in a language other than English, read this article to help you enter Explore formulas in your language.

   Supported aggregators when you use this metric in a report are SUM, AVG, MIN, MAX, and MED

   You'll end up with a page that looks similar to this:

   

7. When you are finished, click Save. Leave the report open and continue to the next procedure.

Tip: After you create custom 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 "hours", or increase the number of decimal places.

Using the custom metric in a report

Now, you'll create a report that uses the custom metric you just created to report on the amount of time tickets spent in the Support 1 group. Perform the following steps in the report you created previously, or in a new report that uses the Support: Updates history dataset.

To use the custom metric in a report

1. In the Metrics panel, click Add.
2. From the list of metrics, choose Calculated metrics > Time spent in group, then click Apply. Explore displays the total time that tickets spent in the Support 1 group. Set the metric aggregator to MED, or any other supported value you want.
3. In the Columns panel, click Add.
4. From the list of attributes, choose Time - Ticket update > Update - Year and Time - Ticket update > Update - Month.
5. Set the visualization type () to Column. Explore displays a chart showing how long each of your tickets spent in the Support 1 group.

   

6. When you are finished, click Save.

Calculating how long tickets sit with multiple support groups

In this report, you’ll create a calculated attribute that shows previous groups the ticket was assigned to. You can then use this attribute with the pre-built Field changes time metric to calculate how long the ticket was in each group.

Before you start, you'll need the ID numbers of the groups you want to query. To get the ID numbers, see Discovering your group ID numbers in this article.

Note: This section doesn't capture the last group a ticket was assigned to before being solved. For that information, see Calculating how long tickets sit with their last support group.

Creating the custom attribute

First, you'll create a custom attribute that checks whether the ticket has been assigned to the groups you list. If it has, the group name is returned. Before you start, make sure you've noted down the group ID using the previous procedure.

To create the custom attribute

1. In Explore, click the reports icon () .
2. In the Reports library, click New report.
3. On the Select a dataset page, click Support > Support - Updates history, then click Start report. The report builder opens.
4. From the Calculations menu (), choose Standard calculated attribute.
5. On the Standard calculated attribute page, give the metric a name, for example Previous group.
6. In the Formula field, copy and paste the following formula. Replace the example group IDs with the group ID numbers you noted previously. Then, replace the example group names with the names that correspond to your group IDs.

   IF ([Changes - Previous value]="24120932") THEN "Dev Team"
   ELIF ([Changes - Previous value]="24935531") THEN "Sales"
   ELIF ([Changes - Previous value]="24072451") THEN "Support"
   ENDIF

   If you want to check for more groups than this example, add a new ELIF line for each group.

   You'll end up with a page that looks similar to this:

   

7. When you are finished, click Save.

Using the custom attribute in a report

Now, you'll create a report that uses the custom attribute you just created to report on the amount of time tickets spent in each group. Perform the following steps in a new report that uses the Support: Tickets updates dataset.

To create the report

1. In the Metrics panel, click Add.
2. From the list of metrics, choose Duration - Field changes (hrs) > Field changes time (hrs), then click Apply.
3. In the Metrics panel, click the metric you just added, and change its' aggregator to AVG.
4. In the Columns panel, click Add.
5. From the list of attributes, choose Time - Ticket update > Update - Month, then click Apply.
6. In the Rows panel, click Add.
7. From the list of attributes, choose Calculated attributes > Previous group.
8. From the Visualization type menu (), choose Column.
9. From the Chart configuration menu (), choose Chart.
10. In the Chart menu, check Stacked. This ensures that results for each group you select are shown in the same chart column.

The final result will look similar to the example below:

Calculating how long tickets sit with their last support group

To calculate how long a ticket sits with its last group, create a standard calculated attribute to identify the time of the last assignment to a group. Then, create a standard calculated metric using the DATE_DIFF() function to calculate it.

Creating the attribute to capture the last group assignment

1. In your report, click the Calculations menu () and select Standard calculated attribute.
2. Give the attribute a descriptive name, such as Capturing the last update attribute.
3. In the Formula field, enter the following:

   IF ([Changes - Field name]="group_id")
   THEN
   DATE_LAST([Update - Timestamp])
   ENDIF

Creating the metric to calculate how long the ticket was assigned to the group

1. In your report, click the Calculations menu () and select Standard calculated metric. 
2. Give the metric a descriptive name, such as How long before solving.
3. In the Formula field, enter the following:

   DATE_DIFF([capturing last update attribute],[Ticket solved - Timestamp] , "nb_of_minutes" )

Adding the attribute and metric to your report

1. In the Filters panel, click Add and select the attribute you created above (capturing the last update attribute, in the example).
2. In the Metrics panel, click Add and select the metric you created above (How long before solving, in the example).
3. Change the metric aggregator to AVG.
   
   
4. In the Rows panel, click Add and select Ticket ID and Ticket group.
   
   
5. (Optional) For a cleaner-looking report, click the Capturing the last update attribute and then exclude NULL values and include only Solved and Closed statuses. For help, see Filtering by an attribute in the Filters panel. 

Calculating duration of time for custom ticket fields

Reporting on the duration of custom ticket field values is very similar in concept to Calculating how long tickets sit with a support group. The important difference here is that you’ll need to find a specific value to use in your custom metric formula in order to return the appropriate results.

For example, let’s say you’ve created a custom ticket field called Type of Support, with possible values of Beginner Support, Intermediate Support, and Advanced Support. You want to report on how long tickets spend with the Advanced Support value applied.

To get started, find the ID of the Type of Support field, and then use that ID to find the value of the Advanced Support value, which you can use in your custom metric formula.

Finding the specific ticket field value

These steps are similar to those in Discovering your group ID numbers. Except here, you’re not going to find the ID of a group; you’re going to find the ID of a custom ticket field, and then find the specific value of the ticket field value that you want to report on. 

To find the ticket field ID

1. In Admin Center, click the Objects and rules icon () in the sidebar, then select Tickets > Fields.
2. In the Field ID column, find the ID number of your custom ticket field (in this example, Type of Support). Make a note of this number, which you’ll need in the following step.

To find the value of the ticket field value 

• In your web browser, visit the following URL: subdomain.zendesk.com/api/v2/ticket_fields/<ticket field ID>
  
  (replace subdomain with the name of your Zendesk subdomain, and replace <ticket field ID> with the ID you noted previously)

In the example, you would see something like the following:

• “id”: 360048826392,
• “name”: “Advanced Support”,
• “raw_name”: “Advanced”,
• “value”: “advanced_support”,
• “default”: false

The value to use in the custom metric formula is “advanced_support”.

If you were to use “Advanced Support” instead, the formula would technically be valid, but it wouldn’t return any results.

Creating the custom metric and using it in a report

Following the example, you want to see how long a ticket spends with the Advanced Support value set. The formula for that would look like the following:

IF ([Changes - Field name]="Type of Support")
AND ([Changes - Previous value] = "advanced_support")
THEN VALUE(Field changes time (min))
ENDIF

For more detailed instructions, see Creating the custom metric earlier in this article. 

The last step is to create a report using your custom metric. For instructions, see Using the custom metric in a report.

Next steps

For more information about the metrics and attributes you can use with the Updates history dataset, see Metrics and attributes for Zendesk Support.