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
This is a great article, can you do the same thing with average 1st time resolution without pending time ?
Hi Steven,
Yes! You could use this same framework to find average first resolution time minus pending time. Great idea!
I have it setup but I have encountered an issue.
a ticket goes through the normal process and gets marked "solved", but the customer comes back and it reopens now the initial time of 1st resolution has stopped.
The ticket then spends some more time in pending before getting resolved.
The issue I am now having is I can have a ticket that is getting a - (minus) hrs time as it has stopped counting the time to 1st resolution but pending time still includes anymore pending time after it has been reopened.
Any ideas how to avoid this? ideally needs to stop calculating pending time once the ticket was marked solved in the 1st instance?
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 Steven,
Are you calculating time to full resolution or first resolution?
Hi Fernando
I am calculating first resolution
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?
I created the Full Resolution minus Pending time and some of my tickets (grouped by severity) are showing a negative value. How could that be?
Are you using business hours Irene?
Nope, I followed this exact set of instructions. Strangely when I change the "how" to include ticket ID, I can export all tickets and see none with a negative count. that is the only change between the two reports and I get wildly different results on the same "what".
Hey Irene! I'm going to check with some of our Insights experts to see if I can get some information on this for you!
Hey Irene,
In order for us to dig into your question, we'd need to create a Ticket for you and investigate your set up. I'll be contacting you through email and we'll get this straightened out.
Thanks!
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 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 Elena,
Here's the navigation path you'll want to follow in order to find the "deleted" value.
First, select "Attribute Values":
Next, search for "Ticket Status" and then select it:
After selecting Ticket Status, you should see the "Deleted" status:
Please let me know if you run into any issues!
Why would the ticket status need to be deleted and not solved?
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.
Hi,
I'd like to do a MEDIAN version of this report, instead of AVERAGE.
I used the default metric, instead of creating an AVG version: Full Resolution Time (hrs) [Mdn]
I then created the Pending MEDIAN: SELECT MEDIAN (Agent wait time in minutes/60) WHERE Ticket Status <> Deleted
Next, I created the report as Full Resolution Time (hrs) [Mdn] - Pending [Mdn]:
I ended up with a couple of points that looked okay but then one month stuck out above the rest with no reason that I can think of.
Anyone know why this would be?
Hi Kevin! Welcome to the Community!
I'm going to see if I can find someone to help you with this. Stand by!
@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])
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?
Hey Darren,
In order to troubleshoot this i've created a ticket for you and will be reaching out to you soon. There are many factors that could influence how a report renders so to really troubleshoot why you're consistently getting 0 values i'd like to take a look at your Insights report. You'll receive an email from me soon!
Thank you very much for this article. It's soooo helpful. I have the metrics set up, and now I am putting together a report. I am a bit confused about the impact of using dates of ticket creation vs. dates of ticket solved. What I want to see is the average time that it takes to fully resolve tickets that are created by day of week (do tickets created on Sunday have a longer resolve time, for example).
I have chosen:
What - Full - Pending (Average)
How - Ticket Assignee, and Day of Week Ticket Created
Filter - Ticket Assignee and Week (ticket solved)
My results don't look right. Is it possible to report on tickets that have been resolved/solved by the date that they are created?
Thank you!
You should filter for Status = Solved, Closed
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!
Guys i have a question, If I write my formula like this
Select Full Resolution Time (hrs) [AVG] - (Ticket On Hold Time (hrs) [Avg] + Ticket Pending Time (hrs) [Avg])
Will I get the average time for a ticket to be solved/closed ignoring the time when the ticket is in pending, on hold or solved status?
To add to my question above I put "Ticket Assignee" and "Week ticket created" as the HOW. As for the filter we use "Ticket created within 30 days". This is the result.
What I'm asking is, is it correct that the data that I'm getting is the average time an agent take to change a ticket status to solved minus the time the ticket is in pending or on hold for all tickets that is created within a certain week?
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!
How does this function differ from "requester wait time"?
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.
Please sign in to leave a comment.