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

Have more questions? Submit a request

96 Comments

  • Amy Dee
    Comment actions Permalink

    To Andrei - These recipes will not be able to show results in business hours, unfortunately. GoodData cannot access your schedule, so it has no way to know what your business hours are. Also, these recipes are based on epoch timestamps, which do not show the date or time of day without extra conversion steps.

     

    To Denielle - Looks like you changed the question in your post after I wrote a reply. Whoops!

    If you're looking for the timestamp of the last user update on a ticket, you could try something like this:

    • SELECT MAX( SELECT( SELECT( SELECT Ticket updated (minutes) BY Ticket Updates) WHERE Updater Role IN (End-User, Agent, Admin)) BY Ticket Id)

    You will certainly want to test that out in your account with your workflows before using it in a report. However, it looks promising in my own tests.

     

    Happy reporting!

    3
  • Eugene Orman
    Comment actions Permalink

    Hi Cameron,

    Timestamp--Ticket Reopened

    If this metric should capture the timestamp of the ticket reopening event, then [Text Field] Previous Value must be [Status] Solved:

    Timestamp--Ticket Responded To

    I don't think that the metric configuration will help you to track the right ticket event, it just returns timestamp of the first public comment on the ticket. But in fact you'll need to capture first public comment submitted by agent after ticket got reopened. To do so you could set few triggers which will help you to track this event. Here is an example of the set-up:

    • Create a Custom checkbox field and place it on you ticket form, let's call it "Ticket reopened". Check and uncheck the field box on few tickets, in order to pass this information to GoodData quicker (don't forget data sync occurs once per  hour).  

     

    • Set the first trigger which will mark the ticket as reopened                                 

     

    • Create the second trigger which will un-check the box when Agents replies publicly on the ticket, in other words, upon first reply after reopening event.                                  

     

    • The last step will be to modify your "Timestamp--Ticket Responded To" metric,  you can use following configuration.                                                                                        

     

    Average Re-reply Time 

    This metric is set correctly, so you don't need to modify it.  

    3
  • Andrei Kamarouski
    Comment actions Permalink

    Hi!

    Do you know any way to catch up business hours inside these metrics? 

    2
  • Amy Dee
    Comment actions Permalink

    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
  • Sílvia Andrés
    Comment actions Permalink

    Thank you Jacob! I will try it.

    1
  • Amy Dee
    Comment actions Permalink

    Hi Max! At this time, tag data is not connected to event data. This is because one ticket can have any number of tags at the same time, which makes things difficult to manage on the backend.

    You can't report on when a tag was added or removed, only whether it has ever been on a ticket and whether it is still present on the ticket.

    If this type of report is important for your workflow, I recommend associating the tag with a field, like a checkbox or dropdown. Then you can report on when the field changes instead. (Fields only have one value at a time, which makes them much easier to use for this sort of report.)

    I hope this helps! Happy reporting!

    1
  • Amy Dee
    Comment actions Permalink

    Hi Max! Checkboxes actually have three values in Insights: true, false, and null. They're null on tickets that don't have access to the checkbox field. Once the field becomes available on a ticket, it has the usual options of true (1, or checked) and false (0, or unchecked).

    Insights reports on field changes based on your ticket data. If this is a brand new field, then it's possible you haven't synced any data of the field changing from unchecked to checked on a ticket yet. 

    Try checking and unchecking the box a few times on a ticket (make sure to submit each update), then wait for the ticket to sync. Do you see more options for the previous value now?

    Also, you don't need to include a previous value if it isn't relevant. If you're mainly focused on when the box is checked, you can focus on the new value of 1 and not include the previous value in the metric.

    I hope this helps! Happy reporting!

    1
  • Wendell Wee
    Comment actions Permalink

    I don't understand why we can't have a in-built ticket time reporting metric. I had to build my own metric based on this article.

     

    If anyone is interested here's how i obtain the ticket created time in HH:MM format in excel.

     

    First you need to follow the article to create a metric for: Ticket Created: Timestamp

    SELECT MIN( SELECT( SELECT( SELECT 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)

     

    Secondly you then need to convert the timestamp into a human readable HH:MM

    This can be done by creating another metric to make use of first one that was created to obtain the epoch timestamp and format it. This is based on the formula provided by Erik in the previous comment.

    SELECT (((Ticket Created: Timestamp * 60) + 28800) / 86400) + 25569

    You will need to amend "28800" according to your timezone, mine is UTC+8 thus 28800 is 8 hrs in seconds.

     

    Once done, you can export into excel and change the column format to time and you should get the correct ticket create time in HH:MM.

    Hope this helps.

     

    1
  • Jacob J Christensen
    Comment actions Permalink

    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
  • Amy Dee
    Comment actions Permalink

    Hi Max! You're getting close! My main question is how you're getting the ticket creation timestamp in the average duration metric.

    This type of recipe is highly susceptible to aggregation issues. It's possible that the metric is comparing the lowest two timestamps for each report slice, not each ticket. That could be why the results look okay when you slice by Ticket Id, but not when you slice by Date (Ticket Created)

    Try something like this instead:

    SELECT AVG(SELECT(Checkbox timestampCreation timestamp) BY Ticket Id)

    That should find the difference between those timestamps within each ticket, then take the average across tickets.

    I hope this helps! Happy reporting!

    0
  • Barbara Bonneau
    Comment actions Permalink

    Thanks for the article and all the useful comments.

    I am still struggling with one remaining need.

    I want to make sure my agents do not let clients without information even though solving a ticket might take some time.

    I would like them to check tickets that have not been updated for more than 2 days and created the related view.

    Now I would like to create a KPI associated to this need that could be for example the number of tickets with delay between two updates greater than two days for one agent.

    Any idea if it is feasible? I could not figure out how to manage it!

    0
  • Jacob J Christensen
    Comment actions Permalink

    Hi Natasha,

    I would recommend that you take some of the complexity out of the reporting by creating a custom ticket field and have a trigger set the value for this field when the conditions for the T1 to T2 handover are met.

    This should make it simpler to create a time stamp, and also make it more robust to changes in your agent setup.

    For example:

    You could create a checkbox field on your ticket form(s) called Tier 2

    Have one (or more) triggers check the field when Assignee and Group are changed to Tier 2.

    This should give you a simpler way to formulate the time stamp metric. Hope that helps.

     

    0
  • Amy Dee
    Comment actions Permalink

    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
  • Viorica Pop
    Comment actions Permalink

    Thanks Jessie !

    0
  • Rajshekar
    Comment actions Permalink

    I change the status to New I get the below error

     

    Please help

     

    Regards

    Raj

    0
  • Sam Dresser
    Comment actions Permalink

    I'm such a fan of this, I have a follow-up question.

    What I'm trying to do, is get a number of how many tickets were ever in a given state. The state I'm looking for is based on a custom drop-down field.

    For example. Say I have a custom drop-down field that have the following options:

    • Option 1
    • Option 2
    • Option 3
    • Option 4

    I'm trying to see how many tickets were ever saved set to "Option 3."

    I was thinking that I might be able to accomplish this using timestamps. If I could record a timestamp of when the ticket was saved at "Option 3," I should be able to then create a metric of how many tickets have a timestamp. Right?

    Well, I'm having trouble. I don't see a way to build a metric that counts the number of tickets that have a timestamp, and not to count the ones that don't.

    Am I thinking about this the right way? Is there a better way for me to get this number? THANKS!!!

    0
  • Viorica Pop
    Comment actions Permalink

    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
  • Max McDaniel
    Comment actions Permalink

    Hi Amy, 

    Thanks for the response and confirmation. New plan us to use a checkbox to get to that timestamp. If I'm understanding this correctly, the idea is to find the event where the [Text Field] Previous Value is where that box is NOT checked and the [Text Field] New Value IS checked, which are indicated by the Attribute Values showing a 1 and a 0. It seems to me that the "previous value" should be the "0" and the "new value" should be the "1" but there was not the [Rehoming Plans Established] 0 option under the [Text Field] Previous Value Attribute Value category. Can you confirm if this is correct?

    Here's what I have:

    0
  • Eugene Orman
    Comment actions Permalink

    @ Natalie,  It is not possible to report on half hour intervals in Insights.

    0
  • Sergi Planas Alfonso
    Comment actions Permalink

    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
  • Jacob J Christensen
    Comment actions Permalink

    Hi Raj,

    Looks like your attribute values are not connected (the orange parts) - they are text only. Also it doesn't appear to be exactly set up like in your first screenshot.

    0
  • Amy Dee
    Comment actions Permalink

    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
  • Keith
    Comment actions Permalink

    This article is great, thank you! 

    I have everything working perfectly, but now I'm trying to display the average reply time for all tickets per month. I thought this would work:

    Average T2 Reply Time Metric:
    SELECT AVG (T2 first reply time)

    Then I make that metric my WHAT and set Month/Year (Ticket Created) as my how, but the information being return isn't correct.

    Can anyone help me figure this out?

    Cheers!

     

    0
  • Brett - Community Manager
    Comment actions Permalink

    Hi Iryna,

    I reached out to one of our Insights product experts and it looks like this kind of report is not possible. There's no metric available that will report on the interaction between two entirely independent field durations. Your closest option would be to use the Requester wait time metric as you previously mentioned.

    Let me know if you have any other questions.

    Cheers!

    0
  • Danae MacLeod
    Comment actions Permalink

    It doesn't look like the exact ticket status can be selected as an attribute. Trying to come up with a formula that will count the average amount of time it takes tickets to move from open to on hold - this doesn't look like it will work.

    0
  • Mike Althoff
    Comment actions Permalink

    Hey folks - I've been using this article to help me calculate the time that passes between a ticket being escalated from Level 1 to Level 3 and a Level 3 agent 'taking' the ticket's assignment.

    For some reason, I am not having any luck getting data for the time stamp of when a ticket is assigned a Level 3 Support Level.  Here's what I currently have:

    Any ideas?

    I am then using below to get the time stamp of when a Level 3 agent 'takes' the ticket - in our paradigm, changing the Support Level triggers an event to change the Group to the corresponding Level:

    Finally - if I get these right, my intent is to subtract these two and get a time duration between the two events (am hoping to manage to a 'ticket read' of within 1 hour of escalation, measuring the 'read' moment as when a Level 3 agent 'takes' the ticket).

    0
  • Corrin Duque
    Comment actions Permalink

    Would like to use this but I created triggers to auto assign all our tickets to their appropriate Tier groups ( we call them Pods). How would I create a time stamp to get first response agent update/comment when ticket is in open status and assigned?

    0
  • Rajshekar
    Comment actions Permalink

     

    Thanks Jacob :)

    Got it!! 

    I created a test report & the values looks something as below

    Could you please help, how do I fix this

     

    Regards

    Raj

     

    0
  • Tom from dapulse
    Comment actions Permalink

    Thanks a lot Amy!

    You are a superstar!

    0
  • Cameron D.
    Comment actions Permalink

    I'm trying to calculate response times beyond the first reply time. I thought this set would work:

    Timestamp--Ticket Reopened

    SELECT MIN( SELECT( SELECT( SELECT Ticket updated (minutes) BY Ticket Updates) BY Ticket Text Field ChangeWHERE Text Field = Status AND Updater Role = End-user AND [Text Field] New Value = [Status] open AND [Text Field] Previous Value = [Status] new OR [Text Field] Previous Value = [Status] pending) BY Ticket Id)

     

    Timestamp--Ticket Responded To

    SELECT MIN( SELECT( SELECT( SELECT Ticket updated (minutes) BY Ticket Updates) WHERE Public Comment = true AND Comment Present = true) BY Ticket Id)

     

    Average Re-reply Time

    SELECT AVG(Timestamp--Ticket Responded To - Timestamp--Ticket Reopened)

     

    I'm getting negative numbers in the report, which doesn't seem right -- could it be that I've got the final metric's arithmetic in the wrong order?

    0

Please sign in to leave a comment.

Powered by Zendesk