Insights recipe: Average resolution time without Pending or On-hold time

Zendesk is removing Insights reporting on February 5th, 2021. If you are using Insights, you need to move to Explore, the latest generation of reporting for Zendesk products, before that time. If you need more time to move to Explore, you can apply for an extension using this form before January 22, 2021.

This recipe will instruct you on how to create a report to show the average ticket resolve time, excluding time spent in the Pending status. This recipe will also show you how to create a second version of the report that excludes time spent in the On-hold status. 

 Skill: Advanced

Time: 30 minutes

Ingredients:

• One duplicated metric
• Four custom metrics 

Creating your average full resolution time metric

The first step in constructing your report is to change your metrics' original calculation to an average. You can do this by duplicating the metric, then editing the metric formula. 

 To change your metrics to an average

1. Click the Reporting icon ( ) in the agent interface, then open the Insights tab.
2. Click the GoodData link in the upper right corner.
3. Click Manage>Metrics.
4. Select the Full Resolution Time (hrs) [Mdn] metric. 
5. Click the Duplicate button. 
6. Click the Edit button.
7. In the Metric Editor, delete MEDIAN and enter AVG. 
8. Enter a new name for your metric. This recipe uses Full Resolution Time (hrs) [Avg].

   

9. Click Save.​

Creating your average Pending and On-hold time metrics

The average Pending and On-hold time metrics are not included in Insights by default, so you will need to create custom metrics. For more information on creating custom metrics, see Creating custom metrics in Insights. 

To create your average Pending time metric

1. In the What panel, click the (advanced) link next to Add new metric. 
2. Select Custom metric. 
3. Enter a name for your metric in the Name your metric text box. This example uses the name Pending Time (hrs) [Avg]. 
4. Enter the MAQL formula into the Editor pane. You cannot type in facts, metrics, or attributes, but instead must select them from the Elements drop-down list.

   SELECT AVG ( Agent wait time in minutes /60) WHERE Ticket Status <> Deleted 

   • Agent wait time in minutes can be found in Facts.
   • Ticket Status can be found in Attributes.
   • Deleted can be found in Attribute values>Ticket Status. 
5. Click Add to add your metric.

To create your average On-hold time metric

• Repeat the same instructions as above, except using On Hold time (hrs) [Avg] and the On hold time in minutes fact. 

Subtracting the Pending and On-hold time from the full resolution

In this section, you will create two final metrics to subtract On-hold time or Pending time from the overall full resolution time. 

 To create the full resolution time without Pending time

1. In the What panel, click the (advanced) link next to Add new metric. 
2. Select Custom metric. 
3. Enter a name for your metric in the Name your metric text box. This example uses the name Full Resolution Time - Pending time. 
4. Enter the MAQL formula into the Editor pane. You cannot type in facts, metrics, or attributes, but instead must select them from the Elements drop-down list.

   SELECT Full Resolution Time (hrs) [Avg] - Pending Time (hrs)[Avg]

   •  Full Resolution Time (hrs) [Avg] and  Pending Time (hrs)[Avg] are your custom metrics created in the section above. They can be found in Metrics.
5. Click Add to add your metric.

To create the full resolution time without On-hold time

1. In the What panel, click the (advanced) link next to Add new metric. 
2. Select Custom metric. 
3. Enter a name for your metric in the Name your metric text box. This example uses the name Full Resolution Time-On-hold
4. Enter the MAQL formula into the Editor pane. You cannot type in facts, metrics, or attributes, but instead must select them from the Elements drop-down list.

   SELECT Full Resolution Time (hrs) [Avg] - On Hold time (hrs)[Avg]

   •  Full Resolution Time (hrs) [Avg] and  On Hold time (hrs)[Avg] are your custom metrics created in the section above. They can be found in Metrics.
5. Click Add to add your metric.

You can now create your reports. You can use the final custom metrics you created as your What, and any attribute as your How. It is recommended to use attributes like Ticket Group and Ticket Assignee.