In this Explore recipe, you'll learn how to create a report that displays the average full ticket resolution time in minutes excluding time spent in the pending or on-hold status, or both.
What you'll need
Skill level: Intermediate
Time required: 15 minutes
- Zendesk Explore Professional
- Editor or Admin permissions (see Giving agents access to Explore)
- Ticket data in Zendesk Support
Creating the query
- Full resolution time (min): The duration in minutes from when the ticket was created to its latest resolution.
- Agent wait time (min): The total time in minutes that a ticket was in the pending status.
- On-hold time (min): The total time in minutes that a ticket was in the on-hold status.
To create the query
- In Explore, click the query () icon.
- In the Queries library, click New query.
- On the Choose a dataset page, click Support > Tickets > Support: Tickets, then click New query.
- In the Calculations menu (), click Standard calculated metric.
- In the Name field, give your metric a name like Resolution time without pending or on-hold.
- In the Formula field, enter or paste the following formula:
VALUE(Full resolution time (min))
-VALUE(Agent wait time (min))
-VALUE(On-hold time (min))
Your query will look like the following screenshot:
- When you are finished, click Save.
- In the Metrics panel of Query builder, click Add.
- From the list of metrics, choose Calculated metrics > Resolution time without pending or on-hold, (the metric you just created), then click Apply.
- Now, you need to change the metrics aggregator to AVG to show average values. In the Metrics panel, click the metric you just added.
- On the metrics page, click AVG, then click Apply.
Tip: Instead of using the AVG aggregator consider using MED for the median value instead. When you take the average, any very large or small values (known as "outliers") can skew the results. In these cases, MED can give a more accurate result.
- Explore now displays the average full ticket resolution time in hours excluding time spent in the pending or on-hold status.