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
- Click the Reporting icon (
) in the agent interface, then open the Insights tab.
- Click the GoodData link in the upper right corner.
- Click Manage>Metrics.
- Select the Full Resolution Time (hrs) [Mdn] metric.
- Click the Duplicate button.
- Click the Edit button.
- In the Metric Editor, delete MEDIAN and enter AVG.
- Enter a new name for your metric. This recipe uses Full Resolution Time (hrs) [Avg].
- 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
- In the What panel, click the (advanced) link next to Add new metric.
- Select Custom metric.
- Enter a name for your metric in the Name your metric text box. This example uses the name Pending Time (hrs) [Avg].
- 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.
- 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
- In the What panel, click the (advanced) link next to Add new metric.
- Select Custom metric.
- Enter a name for your metric in the Name your metric text box. This example uses the name Full Resolution Time - Pending time.
- 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.
- Click Add to add your metric.
To create the full resolution time without On-hold time
- In the What panel, click the (advanced) link next to Add new metric.
- Select Custom metric.
- Enter a name for your metric in the Name your metric text box. This example uses the name Full Resolution Time-On-hold
- 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.
- 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.
55 Comments
Awesome article thank you!
Hello Renee,
Requester wait time is the combined time a ticket spends in the New, Open, and On-Hold statuses. This will also be a median if you use our default metric.
This metric is an average of full resolution time without Pending or On-Hold. So the main differences are the fact that its an average and it is also excluding on-hold time.
I hope that helps clarify things.
Hi Fernando
I am calculating first resolution
Hi everyone, this report looks great. We are trying to create an MTTR report/KPI for our agents - however - we are using Explore and not insights. Does anyone know how to achieve this type of report/metrics within Explore?
Thank you
Hey Nicole,
Sure I can do that. :) Might be a bit since I'm leaving for PTO.
Cheers
Jasmina
Hey Steven!
I'm going to see if any of the Insights gurus in amongst our Community Moderators are able to answer this for you. Stand by!
Hey Jesse,
Have you had a chance to view the list of available Explore Recipes which I've linked for you? This will provide a list of useful reports and how to create them.
Let me know if that's not what you're looking for!
Thanks for the question, Leigh. It seems that Fernando beat me to the punch. If you want to report on tickets that are only completely done, you will want to implement the filter described above. Thanks Fernando!
Hey Ahamed,
I was able to work with one of our Insights experts regarding this query. Could you try using the following metric I've listed below?
This report gets a bit more complicated once you start adding/subtracting averages so we believe once you pull the average resolution time by Ticket ID and then take the Average of those numbers you'll see more accurate results.
The above should also help with reducing the number of columns in your report as well making it easier for managers to read.
Cheers!
Hi Jasmina,
I agree, I learned most of what I needed to know about Zendesk from the Community!
I would defer to others that might see an error I'm not catching but I think your metrics look spot on.
I think you're right to suspect the date (timeline) filter, particularly because your report has a non-timeline filter on it. I haven't had good luck with the timeline filters and I know the logic and my understanding if it is just not clicking so I usually go for something solid like date ticket created. I hope others respond in case I'm off base on this one.
Why would the ticket status need to be deleted and not solved?
Try this formula:
VALUE(Full Resolution Time - min)-VALUE(On-hold Time - min)
If you want the value in hours you will select
(VALUE(Full Resolution Time - min)-VALUE(On-hold Time - min))/60
Good morning,
I have been replicating this recipe on Explore (article is closed for comments so I can't consult the community)
https://support.zendesk.com/hc/en-us/articles/360043021313-Explore-recipe-Average-ticket-resolution-time-without-pending-or-on-hold-time
However data does not look consistent. The median (or average) resolution is way too high (even if I exclude some support groups whose statistics could affect the others).
Would someone be able to tell me if this recipe takes into account business hours (that is the only reason I can come with) ? What is the formula for the calculated metric if we want to focus on Business Hours resolution time only ?
Many thanks for your help.
Delphine
How does this function differ from "requester wait time"?
So the issue was with the argument "# Reopens <= 0", although I'm not sure why. Removing it makes it works
Hey, Leonardo!
You are getting the correct data that you are looking for! The metric is giving Full Resolution Time minus Pending Time and On-Hold Time as you expect, and the attributes you've selected breaks that down per agent per week. The filter makes sure that it is only reporting weeks within the last 30 days. Great job!
@Kevin,
It is hard to tell without looking at the underlying data. Try adding the 2 components of your metric as series to see the differences. (Full Resolution Time (hrs) [Mdn] and Pending [Mdn])
Hi Guys,
I am trying to generate a report to calculate Average resolution per agent excluding on hold and pending time. I followed the above method but the output wasn't something what I expected. Can someone please help me !
Thanks in advance.
Hi,
Just want to say this is incredibly helpful, and I have learned pretty much everything I know so far from this community. :)
I am trying to use this within business hours. This is what I have created:
SELECT [Biz Hrs] Full Resolution Time (hrs) [AVG] - ([BizHrs] On-Hold Time (hrs) [Avg] + [BizHrs] Pending Time (hrs) [Avg])
[Biz Hrs] Full Resolution Time (hrs) [AVG] --> SELECT AVG (Full resolution time in minutes within business hours/60) WHERE Ticket Status <> Deleted
[BizHrs] On-Hold Time (hrs) [Avg] --> SELECT AVG (On hold time in minutes within business hours/60) WHERE Ticket Status <> Deleted
[BizHrs] Pending Time (hrs) [Avg] --> SELECT AVG (Agent wait time in minutes within business hours/60) WHERE Ticket Status <> Deleted
BUT my data seems inconsistent... My bizz hrs AVG with pending and on hold seems sometimes lower than bizz Hrs AVG without on hold and pending.
I am using date (timeline) filter for this on a dashboard. I wonder if that's the issue.
The other filter that I'm using within the actual report is Date (Ticket Solved) where _Filter Ticket Solved Date is greater than 0 Delete
Any help would be very much appreciated, because at this time I can't really see where did I go wrong.
Hey Jasmina -
So glad you got it working! Thank you for coming back and sharing this with everyone.
Would you be interested in writing up what you've put together here as a tip and posting it in the community? Here's how to write a community tip.
Hi Brett,
Thanks for this. I am a bit confused here, where do I select this from or do I have to edit the metric I have created ? I am unable to find Average on hold time metric and Average pending time metric.
Thank you
Hi Nicole,
Thanks for your prompt response.
Please see the attached image.
The numbers don't add up. Also is there a way I can just get the Full Resolution time without the On hold and pending without too many tables so it will be easy for other managers to understand?
Thanks in advance.
Hello! I can't seem to find the "deleted" tag where the recipe says it is. When I select "Attribute Values", it sends me to "Attributes". Am I doing something wrong?
Hi Steven,
Yes! You could use this same framework to find average first resolution time minus pending time. Great idea!
Hi Elena! Welcome to the Community!
I'm checking with some of our Insights experts to find someone who can help you out here. Stand by!
Hi Lindsey!
In this case it would be to filter deleted tickets out of the reporting. This can help to prevent reporting from bring skewed.
I want to calculate this in Business hours. I see that there is already a metric available for Avg Full Res time [Biz hrs] in Hours available. Using this, can I create the Pending and On hold metrics as described above and subtract to get the Avg Full resolution time in business hours?
Hi Ahamed -
Can you tell us more about the results you got that weren't what you were expecting?
I'm having some issues where it won't let me select all my agents if I filter it using Valid values rather than All.
I have this for my end metric:
SELECT Full Resolution Time (hrs) AVG - (Pending Time (hrs) AVG + On Hold Time (hrs) AVG)
All the others one have this:
SELECT AVG (Full resolution time in minutes within business hours/60) WHERE Ticket Status <> Deleted AND # Reopens <= 0
(difference is that my pending and on hold are using agent wait times within business hours and on hold time within business hours).
Anyone have any
I've included the "[Biz Hrs] On Hold Time (hrs) [Mdn]" which is
SELECT MEDIAN(On hold time in minutes within business hours/60) WHERE Ticket Status <> Deleted
However the stats are always 0. Doesn't seem to be capturing any of the tickets we have moved into a hold status.
Basically we would like to see a total amount of time our tickets spent on-hold as a whole and not per ticket.
Any ideas?
Please sign in to leave a comment.