Explore recipe: Reporting on the duration of fields

In this Explore recipe, you'll learn 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 Ticket updates dataset which stores details of updates and events that happen in Zendesk Support.

This recipe replaces the Insights recipe Reporting on duration of a text field.

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
• Next steps

What you'll need

Skill level: Intermediate

Time required: 20 minutes

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

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

In this query, 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 query icon () .
2. In the Queries Library, click New query.
3. On the Choose a dataset page, choose the dataset containing the ticket data you'll need to build the query; in this case, Support: Tickets updates.

   Query 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 it's aggregator to AVG. This will display the average of all returned values.

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

Calculating how long tickets sit with a support group

In this query, 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 query 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 query, 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 query icon () .
2. In the Queries Library, click New query.
3. On the Choose a dataset page, choose the dataset containing the ticket data you'll need to build the query; in this case, Support: Ticket updates.

   Query 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

   Supported aggregators when you use this metric in a query 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 query 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 query

Now, you'll create a query 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 query you created previously, or in a new query that uses the Support: Tickets updates dataset.

To use the custom metric in a query

1. In the Metrics panel, click Add.
2. From the list of metrics, expand Calculated metrics, then click your custom metric, Time spent in group.

   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 query, 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.

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 query icon () .
2. In the Queries library, click New query.
3. On the Choose a dataset page, select the dataset containing the ticket data you'll need to build the query; in this case, Support: Ticket updates.

   Query 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 query

Now, you'll create a query 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 query that uses the Support: Tickets updates dataset.

To create the query

1. In the Metrics panel, click Add.
2. From the list of metrics, choose Duration - Field changes (hrs) > Field changes time (hrs).
3. In the metrics panel, click the metric you just added, and change the aggregator to AVG.
4. In the Columns panel, click Add.
5. From the list of attributes, choose Time - Ticket update > Update - Month.
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:

Next steps

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