Insights recipe: Duration between two or more ticket events in minutes

Have more questions? Submit a request

92 Comments

  • Graeme Carmichael

    Viorica

    I am not sure if what you are looking for can be done. Hopefully, someone can provide a solution.

    I believe the best approach is to create a feature request so that Zendesk calculates the first resolution time excluding pending and includes that within the reporting metrics. I am sure you can make a case for that. Customers that monitor first resolution time may find their metrics unfavourably skewed for tickets that have had high agent wait time.

    0
  • Viorica Pop

    Hi Graeme, 

    Thanks for the suggestion, I will create a feature request but I was hoping to get a hold of one of the Zendesk's experts in analytics until the request will possibly be addressed by Zendesk.

    It happens quite often that our customers re-open tickets just to say a thank you and that adds more time to the RWT until the agent goes back and solves the ticket again. 

    @Jessie, 

    Is there someone available that could help ?

    Thanks!

     

    0
  • Amy Dee

    Hi Viorica! I took a look at the metrics in your previous comment. That approach is combining elements from different parts of the data model that don't connect to each other. That is likely why your results look cluttered.

    The duration metrics described in this recipe do not work with business hours in any way. Insights has no way to see your schedule, and this timestamp comparison approach has no reference to the date or time. It only captures calendar minutes.

    If you're interested in business hours, you must use the default Zendesk metrics. Zendesk calculates those internally and stores them as a completed number on the ticket. Insights has no information about the solve event or schedule; it just sees that Ticket 1234 had a first resolution time of 280 business minutes.

    Let's take a step back and look at your overall goal. It sounds like you want to see the first resolution time without time in pending status, calculated in business hours. That should be possible, since there are default metrics for resolution times and pending times in business hours.

    I recommend starting with something simple like this:

    SELECT MEDIAN(SELECT ([Biz Hrs] First Resolution Time (hrs) [Mdn] - [Biz Hrs] Agent Wait Time (hrs) [Mdn]) BY Ticket Id)

    This takes the business hours first resolution time and subtracts the business hours agent wait time within each ticket, then it takes the median value across tickets. In my tests, this allowed me to see resolution times without the time spent in pending.

    Please note: Zendesk calculates business hours with the schedule at the time of the relevant update. If the schedule changes between the ticket's last time in pending status and the first solve, you could get some odd results with this recipe. 

    I hope this helps! Happy reporting!

    0
  • Morgan King

    I am trying to use this but cannot get the First public agent comment in T2 to pull any data.

    I believe my issue lies within the T1 to T2 timestamp. I have mine setup as follows:

    SELECT MIN( SELECT( SELECT( SELECT Ticket updated (minutes) BY Ticket Updates) BY Ticket Text Field Change WHERE [Text Field] New Value = Tier 2 Technical Support AND [Text Field] Previous Value = Tier 1 Technical Support) BY Ticket Id)

    Tier 2 and Tier 1 are a different attribute than Text Field, the attribute is actually Assigned Tier but, I cannot get my string to appear with [Assigned Tier] Tier 1 Technical Support at the same time I don't see [Assigned Tier] Previous Value as an option, how do I get it to appear appropriately?

     

    0
  • Amy Dee

    Hi Morgan! It looks like you found the attribute values under Ticket Group. That attribute shows the current group on the ticket; it does not work in a ticket update metric like this one.

    Since you're using the [Text Field] New Value and [Text Field] Previous Value attributes, you need to find the matching attribute values:

    • Attribute Values[Text Field] New Value[Group] Tier 2 Technical Support 
    • Attribute Values > [Text Field] Previous Value > [Group] Tier 1 Technical Support 

    The values look the same once they're in the metric editor, so make sure you're using the right attribute for each one.

    I hope this helps! Happy reporting!

    0
  • Steve Filer

    Where a Ticket is assigned to more than 1 Group during its lifetime I'm trying to report on the amount of time it spent assigned to a specific Group. 

    Anyone have some suggestions? I'm not sure that differences between timestamps will help. 

    0
  • Amy Dee

    Hi Steve! For this type of report, I recommend the duration of a text field recipe. That recipe introduces the [Text Field] Duration in minutes fact, which allows you to report on changes and durations within a single field (like the group or status). It's great for finding those durations throughout the life of a ticket.

    Please note - the [Text Field] Duration in minutes fact functions by finding the start and end times at each field change, so it can't capture the duration for the current value. If you want to include the current value, you may need to combine that recipe with the one in this article -- use the fact to get durations during the ticket, then use timestamps to get the final change and a separate "end" event (like the solve).

    I hope this helps! Happy reporting!

     

    0
  • Steve Filer

    Thanks Amy - most helpful!

    0
  • Sergi Planas Alfonso

    First of all, excelent work with that article! 

    I hope you can help me somehow. I want to get from a ticket the total time of each event that a group write an answer. Each ticket can be answered by different groups and I want to get the time spent from a group on that ticket. How could I do that?

    I tried this

    Thanks in advance!

    0
  • Kim Lake

    HI there

    Just set this up and it's working a treat - except for one particular use case... can anyone help?

    I can see in my report that the metric is reading the time from the ticket being assigned to the group, to the time of the next Public Reply. So far so good.

    But what about in cases where the ticket was assigned from T1 to T2 (starting the clock), and T2 transferred it on to a new group, T3. The clock didn't stop until a T3 agent replied - but the ticket wasn't with T2 at that point, so the reply time wasn't theirs.

    How do I adjust the metric so it shows me the Reply Time for comments while still assigned to that group - not the time between being assigned, and the next public comment?

    In the above use-case, I'd want the reply time to go against the group that had the ticket when the reply was made, and a separate metric that counts the "assigned time" to T2 where they didn't make a public comment at all.

    0
  • Amy Dee

    Hi Kim! This is a tricky use case with a lot of little moving parts.

    The are historic attributes that capture the group at the time of any field change. (See: What are the Historic attributes in Insights?) The issue here is that comments do not count as field changes. They're recorded as properties on the ticket update as a whole. This means a comment-only update does not log a historic group value, which means historic groups aren't a reliable way to find this type of information.

    At this point, I'd look into a couple broad approaches:

    Find the timestamp for when the ticket first leaves the T2 group as well, then look for public agent comments between the timestamps to and from the group. This adds a layer of complexity, and it would include non-T2 agents who posted while the ticket was with T2. However, it would limit the report to response times within the group.

    Set up a variable for the T2 updaters, then limit the report to replies made by those agents. This would require some manual maintenance as the group membership changes, and it would exclude non-T2 agents replying to the ticket. However, it would avoid adding new timestamps, and it would capture cases where T2 escalates the ticket and then adds a quick reply. 

    The best option would depend on how you handle group memberships and escalations, and whether you want to include actions from non-T2 agents. 

    As for finding the amount of assigned time in T2, I'd recommend this recipe: Reporting on the duration of a text field. This finds the amount of time a field had a certain value before the value changed. This would allow you to report on how long tickets were assigned to T2 before escalating to T3. It can't capture the current group, but for a step within an escalation workflow, it's the easiest approach.

    I would advise against combining the "duration of a text field" results with the reply time numbers here in the same chart, at least at the start. There are a lot of different events involved that could start conflicting with each other, so you'd want to have a "clean" set of results for each type of metric.

    I hope this helps! Happy reporting!

    0
  • Kim Lake

    HI Amy

    This approach is the one we'd need:

    "Find the timestamp for when the ticket first leaves the T2 group as well, then look for public agent comments between the timestamps to and from the group. This adds a layer of complexity, and it would include non-T2 agents who posted while the ticket was with T2. However, it would limit the report to response times within the group."

    However, now it seems to be blowing the stats out the water - either blank, or many more hours than what I can see on the tickets.

    I have a ticket open with Zendesk for this, wonder if you could help?

    0
  • Amy Dee

    Hi Kim! That use case does make sense. For the odd behavior you're seeing, the most likely issue is a conflict with date filters. If you use a Date (Event) filter, and the metrics aren't locked down, the report will only show results for tickets where all three events (into T2, reply, and out of T2) occurred in the date range. If tickets can move back and forth between groups, it could skew results as well.

    Since you already have a ticket going with our support team, that will be the best place to dig in. They'll be able to help you with specific examples and reviewing your draft reports.

    I hope this helps! Happy reporting!

     

    0
  • Jessie Schutz

    Hi Kim! It looks like Amy is helping you out in your ticket now. Let us know if you need help with anything else!

    0
  • Pablo Jorquera

    Hello everyone,

    I'm trying to get something similar to what Mike Althof commented before, but I'm not sure if I should select the same structure.

    business needs: Our support is given through different groups. We need to measure the average aging of a ticket on each group.

    So, a ticket always has two possible outcomes - it becomes solved by the group or scalated to a different group.

    From the metric perspective it would be something like "Select the average time between a ticket has been assigned to group = XXX until it has been assigned to a group = "any but X" or ticket status = solved"

    Can you help me out with a simple metric to start of? I assume I will have to replicate in the future all these metrics to a rule "1 metric per group"

    0
  • Amy Dee

    Hi Pablo! That type of metric is difficult to set up, because you can't easily combine two different types of field change in the same metric. You would need to lock down the group and status changes separately, then pull them back together with carefully-restricted nested filters.

    Instead, I would start with the duration of a text field recipe. That recipe uses the [Text Field] Duration in minutes fact to find how long a field had a certain value before the value changed. In an escalation workflow, it would allow you to find the amount of time in each intermediate group from a single metric.

    The downside of that recipe is that it can't find the time in the current group. The metric is based on field changes, and the current group has not changed.

    For the current group, you could use the recipe in this article to find the duration between the most recent group change and the solve event. Fortunately, there is a default fact for the ticket solve timestamp - Ticket solved at (minutes) - so you don't need to recreate that from scratch.

    Once you have these building blocks, you can use them to combine the results, so you can see the amount of time spent in a group before an escalation or a solve. You may need to create a separate metric for each group to put everything together, but it would scale more easily for large workflows with a lot of group changes.

    I hope this helps! Happy reporting!

    0
  • Aldrin Hilario

    How to get the timestamp if the group is the same?

    Example:

    SELECT MIN( SELECT( SELECT( SELECT Ticket updated (minutes) BY Ticket Updates) BY Ticket Text Field Change WHERE [Text Field] New Value = [Group] Tier 1 AND [Text Field] Previous Value = [Group] Tier 1) BY Ticket Id)

     

    WHERE [Text Field] New Value = [Group] Tier 1 is a light agent (Project client - Escalation)

    WHERE [Text Field] Previous Value = [Group] Tier 1 is an official agent that's answering the ticket

    0
  • Sílvia Andrés

    Hi all!

    I need help... I'm trying to know the time since the ticket creation to the status "Hold", because this is the time that my support is working on a ticket.

    I created a metric, but I'm lost, I don't know if I need to create another metric for compare, or I can use this metric alone, but it is not working.

    And I put this metric in a report:

    In "HOW" I put Ticket Type.

    But the report is showing me: No data match. Could you tell me what is wrong?

     

    0
  • Nicole - Community Manager

    Hi Silvia -

    I'm sorry no one has gotten back to you here. You could try cross-posting your request to the Insights Q&A topic in the community; it may receive more visibility to other community members there.

    0
  • Jacob J Christensen

    Hi Silvia,

    You should be able to set this up using the 3 custom metrics below (only the last one needs to be in the report).

    Ticket created timestamp

    SELECT MIN( SELECT( SELECT( SELECT MIN (Ticket updated (minutes)) BY Ticket Updates) BY Ticket Text Field Change WHERE Text Field = Status AND [Text Field] Previous Value = (empty value)) BY Ticket Id)

    Ticket changed from NEW to HOLD timestamp

    SELECT MIN( SELECT( SELECT( SELECT MIN (Ticket updated (minutes)) BY Ticket Updates) BY Ticket Text Field Change WHERE Text Field = Status AND [Text Field] Previous Value = [Status] new AND [Text Field] New Value = [Status] hold) BY Ticket Id)

    Created to HOLD time

    SELECT AVG((SELECT Ticket changed from NEW to HOLD timestamp BY Ticket Id) - (SELECT Ticket created timestamp BY Ticket Id))

    For the report

    WHAT

    you keep the Created to HOLD time metric.

    HOW 

    I assume you want to see the time per ticket, so I've added Ticket Id here

    I also added Date (Ticket Created) to group the tickets by the day they were created, you could also do it by Ticket Solved. 

    FILTER

    I added a few filters to 1. remove tickets that have had no on-hold status, and 2. reduce the time period being reported on to keep it focused.

    Hope that makes sense.

    EDIT:

    Forgot to mention, the metric is measured in minutes, see example below:

    1
  • Sílvia Andrés

    Thank you Jacob! I will try it.

    1
  • Tom from dapulse

    How can I get the time stamp of the last public comment or private-comment  made on a ticket.

    Here's the use-case.

    I want to measure "Full resolution time". My team will often set tickets as pending even if the case should be solved and those tickets get automatically solved after 48 hours (a trigger we have).

    I want to calculate the amount of time that passes from ticket creation to last public comment or internal comment (whatever came the latest)

    0
  • Amy Dee

    Hi Tom! You should be able to get this information with a slightly modified version of the recipe: First reply by event (version 2).

    That recipe shows how to identify the very first public agent comment on a ticket and get the duration from ticket creation. In your case, it sounds like you want to see the very last comment, public or private, compared with ticket creation. The overall logic would be the same, with just a couple differences:

    • Replace any SELECT MIN with SELECT MAX. That switches from the first comment timestamp to the last.
    • Replace Public Commenttrue with Comment Presenttrue. That allows private comments as well.

    The recipe is already filtered to agent/admin updaters. If you want to allow end-users as well, you can add End-user to the Updater Role filters.

    I hope this helps! Happy reporting!

    1
  • Tom from dapulse

    Thanks Amy!

    Is there a way to exclude all tickets that were ever on on-hold status?

    0
  • Amy Dee

    Hi Tom! You could create a metric that looks for any update where the status changes to on-hold, then filter the report for tickets that don't have any of those updates.

    This is combining status change updates from our article on building metrics for the events model with report filtering techniques from our article about reporting on ticket tags

    Metric: SELECT IFNULL((SELECT # Ticket Updates WHERE [Text Field] New Value[Status] hold), 0)

    This metric counts updates where the status field is changed to on-hold. If there is no update like that, the metric returns a 0 instead of a null value.

    You can add this in a numeric range filter on the report as a whole. For the attribute, choose Ticket Id. For the metric, choose the custom metric above. For the range, choose "equal to 0." This means the report will only include tickets where the update metric returns a 0, which means the tickets never had an update where the status went to hold.

    It may be possible to combine this type of filtering with other metrics in the report. However, if you're looking at the first reply by event recipe, the report metrics are already complicated and carefully connected. It's far simpler to drop a separate filter on the report than to modify those metrics further.

    Quick disclaimer - these examples are provided for demonstration purposes only. I can't guarantee them for all use cases, and I strongly recommend that you audit your results.

    I hope this helps! Happy reporting!

    0
  • Tom from dapulse

    Thanks a lot Amy!

    You are a superstar!

    0
  • Jitendra

    Hi,

    I want to see if for one particular ticket customer has written two times(number of times doesn't matter, if it makes it easier) but there is no reply from any agents in last 48 hours. The ticket could be opened or newly created. I am completely new to it, any help is highly appreciated.

     

    0
  • Graeme Carmichael

    Jitendra

    I don't have a answer for you, but there is a similar question and response here that I hope is useful.

    0
  • Nicole - Community Manager

    Hi Jitendra - 

    We are experiencing unusually high ticket queues currently. I see that you have now received a response from an agent in your ticket in addition to the potential solution Graeme provided above, but let us know if you have any further questions. Welcome to the Zendesk Community!

    0
  • Elise Fin

    Hi, 

    I'm trying to create a report with average time between ticket creation and a field being updated to a specific option (it's a drop down). I see in previous screenshots and comments that I should find an attribute value called '[bug progress] empty value' or do this need to be set differently if there was no value?

    Thank you in advance!

    0

Please sign in to leave a comment.

Powered by Zendesk