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

Return to top
Have more questions? Submit a request

55 Comments

  • Ashleigh Hyde

    Awesome article thank you!

    1
  • Jeff Ma

    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

    0
  • Dan Cooper
    Community Moderator

    Hi Jeff, 

    It's tough to tell without seeing your data.  This is just a guess, but I'm curious if those users have never used the Pending or On Hold status on tickets they were assigned to or maybe never had a ticket assigned to them.  If this is a new instance, sometimes it takes a few days/weeks to get enough data points to have valid data populate.  In short, check your calculations and see if an example exists today in your dataset. 

    Hopefully this helps you lock down what might be going on with those values not showing up as valid.

    0
  • Jeff Ma

    Daniel,

     

    I thought that too, but all users I'm trying to pull from definitely have been in the systems for 6+ months with on hold and pending assigned to them they have solved.

    I'll look over it again but it's racking my brain as I've checked it 10x.

    0
  • Jeff Ma

    So the issue was with the argument "# Reopens <= 0", although I'm not sure why. Removing it makes it works

    0
  • Brett - Community Manager
    Zendesk Community Team

    Thanks for sharing the fix Jeff!

    0
  • Jasmina Arapinac

    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.

     

    0
  • Heather Rommel
    Community Moderator

    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.

    0
  • Jasmina Arapinac

    Never mind :D Found it! :)

    works perfect now, so guys feel free to copy formula if you need AVG full resolution time within business hours that excludes all on hold and pending. It works amazing on a dashboard with date (timeline) filter now.

    My mistake was in my filter. Instead of ticket solved I had ticket created :( But this is the way it's supposed to be:

     

    0
  • Nicole S. - Community Manager
    Zendesk Community Team

    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.

    0
  • Jasmina Arapinac

    Hey Nicole, 

    Sure I can do that. :) Might be a bit since I'm leaving for PTO. 

    Cheers

    Jasmina

    0
  • Ahamed

    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.

    0
  • Nicole S. - Community Manager
    Zendesk Community Team

    Hi Ahamed -

    Can you tell us more about the results you got that weren't what you were expecting?

    0
  • Ahamed

    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.

    0
  • Brett - Community Manager
    Zendesk Community Team

    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!

    0
  • Ahamed

    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

    0
  • Brett - Community Manager
    Zendesk Community Team

    Hey Ahamed,

    You'll need to edit the metrics you created. Those metrics in the screenshot are just the example metrics I created in my test account.

    Hope this helps!

    0
  • Jesse Marion

    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

    0
  • Brett - Community Manager
    Zendesk Community Team

    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!

    0
  • Jesse Marion

    Brett - Community Manager - YES I have been using the recipes for a while now. I might be missing it, I am trying to find info on this exact report just done in Explore rather than Insights as there are some significant differences from what I can see reviewing this.

     

    Thank you

    0
  • Brett - Community Manager
    Zendesk Community Team

    Hey Jesse,

    The only recipe that I could find that was relatively close is the Explore recipe: Reporting on full resolution time article.

    I'll reach out to some of our Community Moderators to see if they have any advice for you regarding this report.

    Cheers!

    0
  • Stassa Miller
    Community Moderator

    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

    0
  • Brandon Tidd
    Community Moderator

    You'll want to also account for things like Call Wait Time.  This article has more information on definitions.

    https://support.zendesk.com/hc/en-us/articles/115000716748-Reporting-on-calls-with-Explore-Talk-Professional-and-Enterprise-

    0
  • Delphine Pougnard

    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

     

    0
  • Chris Stock
    Community Moderator

    Hi Delphine Pougnard - the recipe doesn't take into account business hours, but you can create a version of it that does. When creating the calculated metric look for the fields that contain 'Business hours'. Your new calculated metric will look like:

    VALUE(Full resolution time - Business hours (min))-VALUE(Agent wait time - Business hours (min))-VALUE(On-hold time - Business hours (min))

    Hope this helps.

    0

Please sign in to leave a comment.

Powered by Zendesk