Insights Reports - Duration between two or more ticket events in minutes

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 timestamps for specific ticket updates by using the Updated at 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 your older event timestamp
• Retrieving your more recent event timestamp

Retrieving your older event timestamp (Event 1)

This section will instruct you on how to retrieve the timestamp of your older ticket event. This section also 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). 

1. Click on the (advanced) link next to Add new metric. 
2. Select Custom metric. 
3. In the Editor pane enter your formula. For information on how to create custom metrics using ticket events, see Building metrics for the Events model in Insights.
   Note: Any data elements you add must be selected in the Elements window. Your calculation will not process typed data elements. 
4. When you are finished adding your elements and typing your formula, click Add. 

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

• Ticket created timestamp

  SELECT MIN( SELECT( SELECT( SELECT 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)

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

Retrieving your most recent event timestamp (Event 2)

This section will instruct you on how to retrieve the timestamp for your most recent 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). 

To retrieve your most recent event timestamp

1. Click the Reporting icon (), then open the Insights tab.
2. Click the GoodData link in the upper right corner. 
3. Click Reports, then Create report. 
4. In the What menu, click the (advanced) link next to Add new metric. 

   

5. Select Custom metric. 
6. In the Editor pane enter your formula. For information on how to create custom metrics using ticket events, see Building metrics for the Events model in Insights. 
   Note: Any data elements you add must be selected in the Elements window. You calculation will not process typed data elements. 
7. When you are finished entering your formula, click Add. 

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

• T2 leaves a public comment

  SELECT MIN( SELECT( SELECT( SELECT Ticket updated (minutes) BY Ticket Updates) WHERE Public Comment = true AND Comment Present = true AND Ticket Group (Historic) = Tier 2) BY Ticket Id)

• Agent left 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. 

To find the duration between ticket event metrics

1. Click on the advanced link next to Add a new metric. 
2. Select Custom metric.
3. In the Editor pane, type SELECT and your aggregation. 
4. Inside of parentheses, select your second event metric from the Metrics folder in the Elements window. 
5. Enter the subtraction (-) sign. 
6. Select your first event metric. 
7. Close your parentheses. 
8. Click the Add button.

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

• T2 first reply time

  SELECT AVG(Time stamp - T2 first reply - Time stamp - T1 to T2)

• First internal note reply time

  SELECT AVG(Time stamp - First private comment - Time stamp - Ticket Created)

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.