Insights Reports - Duration between two or more ticket events in minutes Follow

Comments

37 comments

  • Avatar
    Mo Rastegar-Panah

    This is AWESOME! Thanks for documenting!

  • Avatar
    Corrin Duque

    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?

  • Avatar
    Amy Dee

    @Corrin - Most of this can be done with a few modifications to these metrics. For example, if tickets are routed directly to the right group, you could use the “Timestamp - T1 to T2" metric above, but remove the [Text Field] Previous Value part. That should show you the first time a ticket was assigned to a group, regardless of whether it was assigned before that.

    As for the first agent response, that gets tricky. Filtering by the status or assignee can be complicated, and it depends heavily on your workflow. Instead, you could modify the “Timestamp - First private comment” metric above to find the first agent comment after a ticket is assigned to the group.

    To do this, you can change the Public Comment part to true, or remove it entirely if you want to count any comment. Next, after the comment conditions, you could add "AND Updater Role IN (Agent, Admin)" to limit it to comments made by agents. Finally, after the "BY Ticket Id", you can add "WHERE Ticket updated (minutes) > Timestamp - Assigned to group-name, using the name of your first metric. The final result would look like this:

    SELECT MIN( SELECT( SELECT( SELECT Ticket updated (minutes)
    BY Ticket Updates) WHERE Public Comment = true
    AND Comment Present = true AND Updater Role IN (Agent, Admin)) BY Ticket Id WHERE Ticket updated (minutes) > Timestamp - Assigned to group-name)

    These metrics should allow you to find the amount of time from when a ticket is first sent to a group to the next public comment from an agent. Keep in mind that this is only a starting point. There are edge cases and exceptions that can skew your results. You may need to fine-tune the metrics a bit to better fit your workflow.

  • Avatar
    Jacob J Christensen

    @Robbert: Thanks a bunch! This is awesome, just what I've been looking for!

    @Amy: Thanks for the variations and comments, also very helpful!

  • Avatar
    Sam Dresser

    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!!!

  • Avatar
    Jacob J Christensen (Edited )

    Hi Sam,

    It has been a while since I last messed around with custom metrics, so I could be a bit off, but I think you could create something simpler, like:

    SELECT COUNT(Ticket Updates,Ticket Text Field Change)
    WHERE [Text Field] New Value = [[The value option of your choice]]

    and use that metric as a Numeric filter where:

    1 What do you want to filter?

    Ticket Id

    2 Select metric and range

    where your Metric is greater than 0.

    You still need to specify the report's WHAT and possibly HOW also.

    There is an outstandingly good article on creating custom metrics for the events model here.

     

    Hope that helps!

  • Avatar
    Sam Dresser

    Yes! I actually worked with Zendesk support and came up with something similar.

    Thanks!

  • Avatar
    Keith

    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!

     

  • Avatar
    Eugene Orman

    Hi Keith,

    This is quite strange, based on your metric example the report should display the correct data. In order to investigate this further I'll need specific metric/report examples. I have created a ticket for you, so let's continue communication within the ticket. 

  • Avatar
    Cameron D. (Edited )

    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?

  • Avatar
    Eugene Orman (Edited )

    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.  

  • Avatar
    Cameron D.

    You're a wizard, Eugene! It's working brilliantly. As long as I remember to set the date after July 13th 2016, everything looks good to go.

    Thanks!

  • Avatar
    Andrei Kamarouski (Edited )

    Hi!

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

  • Avatar
    Denielle Barcelona (Edited )

    How should I get the timestamp of the last user (either agent or end-user) update on a ticket? I'm trying to get the full resolution time from the very last update of a person on a ticket instead of when the ticket is in "solved" status.

  • Avatar
    Amy Dee (Edited )

    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!

  • Avatar
    Denielle Barcelona

    Thanks @Amy! I'll definitely give that a try!

  • Avatar
    Matthew Willard

    Hi @Amy! 

    I am trying to get a created_at like timestamp i.e. 

    2016-11-30T19:28:26Z

    However, I can't seem to get it from insights. I tried converting an Epoch timestamp from above in an epoch time converter, and it also did not properly represent the time the ticket was submitted. 

    For example, 24607424 translates to 

    GMT: Mon, 12 Oct 1970 19:23:44 GMT
    Your time zone: 10/12/1970, 12:23:44 PM GMT-7:00 DST

    But when I review the ticket, I get a created at of: 

    2016-10-14T11:44:53Z

    My ultimate goal is to get a created_at timestamp and a assigned_at timestamp, that are formatted in this way: 

    2016-10-14T11:44:53Z

    Any help would be greatly appreciated! 

     

     

  • Avatar
    Amy Dee

    Hi Matthew! The timestamps in Insights are displayed in minutes, while most epoch converters work in seconds. That could cause the discrepancy.

    From your example, 24607424 * 60 is 1476445440. When I put that time in an epoch converter, I get this:  Fri. 14 Oct 2016 11:44:00 GMT.

    There isn't an easy way to convert the epoch timestamp to a standard date/time within Insights. Those timestamps are usually used as intermediate steps in other metrics, not as standalone results.

    You may be able to do some creative math within the metric and/or elaborate functions in the formatting (GoodData has documentation here). It's probably more reliable, though, to convert them outside of Insights. 

    Happy reporting!

  • Avatar
    Manish Khemka

    I am trying to replicate the report explained in this article above and do not seem to get any data.

    I tried opening a ticket but looks like the support team does not help on custom metrics.

    Hoping for some from the community on this :). Below are the metrics:

    Ticket created timestamp

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

    L2 to L3 timestamp

    SELECT MIN(SELECT(SELECT(SELECT Ticket updated (minutes) BY Ticket Updates) BY Ticket Text Field Change WHERE [Text Field] New Value = Kony L3 Product Support AND [Text Field] Previous Value = Kony Product Support) BY Ticket Id)

    L3 leaves first public comment

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

    Time Taken for first Public reply

    SELECT AVG (L3 leaves first public comment - L2 to L3 timestamp)

     

    Appreciate any help

    Thank you

    Manish Khemka.

  • Avatar
    Sam Dresser

    @Manish,

    Could you post a screenshot of your metrics? The color-coding is helpful to ensure that the metrics are being built in the correct way.

  • Avatar
    Manish Khemka (Edited )

    Here you go:



    WHAT: Time Taken for first Public reply

    How: Ticket Id

  • Avatar
    Natalie Miles

    I'm trying to create a filter like Hour Ticket Created, but by half hour intervals instead.  Is there a way to create a custom metric with this by retrieving the time stamp?

  • Avatar
    Eugene Orman

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

  • Avatar
    Samuel Garcia

    Thanks! this is very usefull! 

    If i want to see for example all tickets that yesterday changed from Open to other status, how can i define the date in the report?

    Thanks!

  • Avatar
    Amy Dee

    Hi Samuel! If you just want to count tickets with a certain type of update, you don't need to use Ticket updated (minutes). That fact is useful for durations, but not as much for dates.

    In your case, I would recommend something like this:

    • SELECT COUNT(Ticket IdTicket Text Field Change) WHERE [Text Field] Previous Value[Status] Open AND Ticket Status <> Deleted 

    This should count the number of tickets that had their status changed from Open to something else at least once during the reporting period. You can use Date (Event) to see results for specific days.

    This metric is just a basic starting point for demonstration purposes. I can't guarantee it for all use cases, and I highly recommend auditing your results.

    I hope this helps! Happy reporting!

  • Avatar
    Rajshekar

    Hi All,

    I am new to MAQL and need some help please.

    I am trying to replicate the above query for Ticket created time stamp but I am getting the below error


    Unexpected 's'. Expecting: ELEM_IDENTIFIER, FUNCNAME1, NEXT, OBJECT, STRING, -, RANKFUNCNAME, IF, PREVIOUS, FUNCNAMEN, COUNT, RUNFUNCNAME, REAL, IDENTIFIER, FUNCNAME2, CASE, FUNCNAME12, PERCENTILE, TEXT_ELEM, THIS, NATURAL, (, IFNULL

     

    Regards

    Raj

     

     

     

  • Avatar
    Rajshekar

    I change the status to New I get the below error

     

    Please help

     

    Regards

    Raj

  • Avatar
    Jacob J Christensen

    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.

  • Avatar
    Rajshekar

     

    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

     

  • Avatar
    Jacob J Christensen

    I don't know exactly what you want to do, have you gone through all the steps in the article above?

    I'm guessing you may need to filter out tickets that have no duration. Using a numeric range filter like described at the very end of the article.

Please sign in to leave a comment.

Powered by Zendesk