Insights recipe: Duration between two or more ticket events in minutes

Tip: This recipe enables you to find the duration between two different types of ticket event, like a group change and a comment. For durations within a single ticket field, like how long a ticket was in a certain group before escalating, use this recipe instead: Insights recipe: Reporting on duration of a text field.

Zendesk Insights captures and enables you to report on pre-built timestamp metrics, such as First Reply Time and First Resolution Time. 

To report on other times, you need to create your own custom metric in Insights (see Creating custom metrics in Insights). You can use custom metrics to retrieve the epoch timestamps (in minutes) for specific ticket updates by using the Ticket updated (minutes) fact. You can then create a third custom metric to subtract your most recent occurring event (Event 2) from the older event (Event 1), and receive the duration between your two events. 

This recipe will instruct you on how to create these custom metrics. If you want a more thorough guide on creating custom metrics, see Creating custom metrics in Insights and Building custom metrics for the Events model in Insights. For information on creating reports, see Building custom reports in Insights. 

This recipe contains the following sections:

• Retrieving timestamps using custom metrics
• Finding the duration between ticket events
• Creating your report 

Retrieving timestamps using custom metrics

This recipe will includes two different examples, Tier 2 first reply time and First internal note reply time. You can follow this recipe and substitute the formula for the example's metrics with your own.  The formula of your metric will depend on the report you are creating. 

• Retrieving the first event timestamp
• Retrieving the second event timestamp

Retrieving the first event timestamp (Event 1)

This section will instruct you on how to retrieve the timestamp of the first ticket event for the duration. This section contains example formulas for retrieving the timestamp for when a ticket is sent from Tier 1 to Tier 2 (Tier 2 first reply time) and when a ticket is created (First internal note reply time). 

These recipes use the Advanced Metric Editor in Insights. For details on how to work with the metric editor, please refer to this guide: Creating custom metrics in Insights.

The metrics for this recipe are as follows:

• T1 to T2 timestamp

  SELECT MIN( SELECT( SELECT( SELECT Ticket updated (minutes) BY Ticket Updates) BY Ticket Text Field Change WHERE [Text Field] New Value = [Group] Tier 2 AND [Text Field] Previous Value = [Group] Tier 1) BY Ticket Id)

• Ticket created timestamp

  SELECT MIN( SELECT( SELECT( SELECT MIN (Ticket updated (minutes)) BY Ticket Updates) BY Ticket Text Field Change WHERE Text Field = Status AND [Text Field] Previous Value = (empty value)) BY Ticket Id) 

Please note, you can't copy and paste in the metric editor. Color-coded items need to be selected from the "Elements" list on the right. 

• Blue elements are under Facts 
• Purple elements are under Attributes 
• Orange elements are under Attribute Values > Attribute. For example, Status is under Attribute Values > Text Field.
• Green elements are under Metrics

Retrieving the second event timestamp (Event 2)

This section will instruct you on how to retrieve the timestamp for the second ticket event. This section will show formulas for retrieving the timestamp for when Tier 2 leaves a public comment (Tier 2 first reply time) and when an agent left a private comment (First internal note reply time). 

The formulas for the first event examples in this tip are as follows: 

• First public agent comment in T2

  SELECT MIN( SELECT ( SELECT( SELECT Ticket updated (minutes) BY Ticket Updates) WHERE Public Comment = true AND Comment Present = true AND Updater Role IN (Agent, Admin) AND Ticket updated (minutes) > (SELECT T1 to T2 Timestamp BY Ticket Id)) BY Ticket Id)

• First private comment

  SELECT MIN( SELECT( SELECT( SELECT Ticket updated (minutes) BY Ticket Updates) WHERE Public Comment = false AND Comment Present = true) BY Ticket Id)

Finding the duration between ticket events

After you have created your custom metrics, you can find the duration between the two metrics. You can also change the aggregation, to alter how your duration is measured. For example, you can use the AVG aggregator to retrieve the average duration. 

The formulas for the examples in this tip are as follows: 

• T2 first reply time

  SELECT AVG((SELECT First public agent comment in T2 BY Ticket Id) - (SELECT T1 to T2 timestamp BY Ticket Id)

• First internal note reply time

  SELECT AVG((SELECT First private comment BY Ticket Id) - (SELECT Ticket created timestamp BY Ticket Id))

Creating your report

After you have created your third metric, you can then add it to your report. This section will instruct you on how to add your metrics and create your report. For more information on creating, editing, and customizing reports, see Building custom reports in Insights. 

1. In the What panel, select your third metric. 
2. In the How panel, select any attributes you would like to include in your report. For example, you could use the Ticket ID attribute. 
3. Click Done.
4. Click on the Filters button to add any filters to your report. For example, you can add the Numeric range filter, to filter out all tickets that do not have any duration (Filtering your report). 

   This filter would resemble the image below: 

   

5. After you finish customizing your report, click Create  at the top left corner.