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

Have more questions? Submit a request

92 Comments

  • Rajshekar

    Hi Jacob,

    I have gone through the steps, however for now I am only interested  to know the Ticket created (HH:MM) or Time of Event on Ticket (HH:MM)

    Regards

    Raj

     

    0
  • Jacob J Christensen

    OK, this report measures the duration between two or more events, so there may be a simpler place for you to start. I'm not sure if you can convert the timestamp value from the events you want to capture into something human readable. You probably can, but I have no idea how.

    If you want to know what time of day you get the most tickets you could do something much simpler with standard Insight metrics.

    WHAT Tickets created

    HOW Hour Ticket Created

    If you have a lot of ticket history you may want to add a filter to narrow down the date range.

    I hope that helps you out somewhat.

    0
  • Rajshekar

    Sure Jacob. 

    I searched a lot on google to find Minutes Ticket created/ Time of event on ticket along with Hour , but some how not able to find it.

    The reason for searching minutes option is there is lot of history created in the same hour, & its difficult to capture which among these events occurred first through good data.

    I shall explore few more places . Thank you :)

     

    Regards

    Raj

     

     

     

     

    0
  • Erik Bailey

    I've been working with this, and have found that as long as you are willing to export to Excel, it is easy/straightforward to convert a Timestamp to a real date/time. The notes at the bottom of https://www.epochconverter.com/ show how to convert to Excel date, and it works like charm.

    A metric like this, where "Timestamp: First Ticket Solve" is the timestamp of the earliest ticket solved event, works perfectly:

    SELECT (((Timestamp: First Ticket Solve * 60) - 14400) / 86400) + 25569

    I am in US Eastern time zone, so the -14400 converts from UTC to ET (with some DST discrepancies, but I can live with that). The multiplication by 60 is required because the Zendesk epoch is in minutes rather than seconds.

    Exporting this to Excel, and then manually converting the column to a Date, displays exactly the right data! So although you can't easily convert it inside of a Zendesk report, this gives an alternate mechanism which may be helpful to some...

    0
  • Wendell Wee

    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
  • Jessie Schutz

    @Erik, thanks for sharing!

    0
  • Jessie Schutz

    Hey Wendell! Welcome to the Community.

    Thank you so much for sharing your custom metric! If you're interested, you can post that in our Tips & Tricks forum. You'll be featured in our monthly Community Roundup, and we'll send you swag. :)

    0
  • Mike Althoff

    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
  • Amy Dee

    Hi Mike! I see the issue here. When you select attribute values for your metrics, you need to match them to their attributes.

    In the first metric, you have [Text Field] New ValueLevel 3 and [Text Field] Previous ValueLevel 3. The Level 3 attribute value is (most likely) coming from the Support Level attribute. It matches the Support LevelLevel 3 element you have in the second metric.

    That's where the problem is coming from in the first metric. Insights is trying to find updates where the [Text Field] New Value attribute returns a Support Level value. That can't happen, so Insights can't find any updates.

    Remove the two Level 3 values from that first metric. To replace the first value, go to Attribute Values, then look for [Text Field] New Value. That's the attribute you're using, so those are the values you need to use. They follow the same basic format, so you're looking for [Support Level] Level 3.

    To replace the second value, make sure you use the [Support Level] Level 3 value from [Text Field] Previous Value. It will look the same in the metric editor, but it is connected differently on the back-end.

    Once you update your attribute values, the first metric should start returning useful timestamps.

    I hope this helps! Happy reporting!

    0
  • Bárbara Troncoso Páez

    Hi, 

    I have made the metric "Tiempo de trabajo BI" as you can see in the image 

    And it works perfectly when the atributte that i chose is "Ticket id", but when i want to aggregate ir for "date (ticket solved)", the metric becomes very weird as you can see here: 



    As you can see the avg is 63.11 hr, and the roll up is 7.8 hr and i dont even know why is that number there. 

    How can i make the average to appear in my report, and not that wird number. 

     I have try with these formula, and it doesnt work either =( 

    Thanks! 

     

     

     

    0
  • Bárbara Troncoso Páez
    0
  • Amy Dee

    Hi Bárbara! I think I see what happened here.

    When you removed Ticket Id from your report, the metric found the two highest timestamps out of all your tickets, then found the duration between those two timestamps. The two timestamps may not even be from the same ticket.

    Try something like this instead:

    • SELECT AVG((SELECT Timestamp (ticket resuelto bi) BY Ticket Id) - (SELECT Timestamp (ticket asignado a BI) BY Ticket Id))

    This should find the duration for each individual ticket, then take the average of all those durations.

    I hope this helps! Happy reporting!

    0
  • Bárbara Troncoso Páez

    Hi Amy, 

    Thank you very much for answering! Now it is working perfectly!

    Have a nice day!

     

    0
  • Amy Dee

    Hi Bárbara! That's awesome! There are a lot of little moving parts in this recipe. I'm glad you got everything working.

    Thanks for the update, and have a great day!

    0
  • Bárbara Troncoso Páez

    Hi Again!

    If a want to create a metric of the time between two events, but disaggregating by ticket tags. What considerations or changes do a i have to do in the formula? 

    Thanks!

    0
  • Amy Dee

    Hi Bárbara! Reporting on tags is tricky, because one ticket can have any number of tags at the same time. It's extra tricky in this case, because tags aren't connected to ticket events.

    You can't just add a tag filter to this report. You'll need to make a custom metric for tag filtering, then use that metric to filter your report. (You don't need to display it; you just need to make it for the filter.)

    There are instructions in this recipe article: Reporting on ticket tags

    I hope this helps! Happy reporting!

    0
  • Danae MacLeod

    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
  • Max McDaniel

    Hi, I'm hoping to construct a metric that will show the duration between ticket creation and a tag being added. It seems the second metric to get the time stamp for a tag being added could be based off the above "Agent left private comment" recipe, but I'm not entirely sure how to represent the tag going from not present to present. Can you advise? 

    0
  • Amy Dee

    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
  • Max McDaniel

    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
  • Amy Dee

    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
  • Max McDaniel

    Amy, thanks again for that follow up. This seemed to do the trick, although now I'm having a slightly different issue with a similar metric. The report shows what seems to be accurate metric values when I use "Ticket ID" as my How attribute, but when I change that to "Date (Ticket Solved)" to collect the average "second response" time for a whole day, I get results that appear incorrect. 

     

    Looks good: 

    Looks not good:

    (I would expect the Avg Time to Match Intake Received here for 11/9 to be ~5.50, for example)

     

    Here's the metric, for reference:

    (I then use SELECT AVG( above metric - ticket creation timestamp ) to use as the metric in the What)

    0
  • Amy Dee

    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
  • Suporte Customer Advisor

    Hello everyone,

    I am creating " T1 to T2 Timestamp " metric, because i need to calculate the First Reply for my Tier 2.

    There is a metric that calculates the Timestamp only when my Tickt needs to be altered. But, I have the need to calculate the Timestamp when two fields are alterated AT THE SAME TIME, because that is how our passage from T1 to T2 is organized.

    The fields are called "Responsável do Ticket" and "Group":

    I have tried the following structure:

    SELECT MIN(SELECT(SELECT(SELECT Ticket updated (minutes) BY Ticket Updates) BY Ticket Text Field Change WHERE [Text Field] New Value= [Group] Time Customer Advisor AND [Text Field] Previous Value IN ([Group] Time Customer Heroes, [Group] Time de Customer Success, [Group] Time Financeiro, [Group] Time RD University, [Group] Time Técnico) AND [Text Field] New Value IN([Responsável Do Ticket] CS::Pedro Arantes, [Responsável Do Ticket] CS:: Natasha Nesello, [Responsável Do Ticket] CS::Nicolas Beschoren, [Responsável Do Ticket] CS::Paulo Santos, [Responsável Do Ticket] CS:: Wagner Lima, [Responsável Do Ticket] CS:: Yorran Dias, [Responsável Do Ticket] CS::Lucas Macedo, [Responsável Do Ticket] CS::Mahara Scholz, [Responsável Do Ticket] CS::Brndon Tavares, [Responsável Do Ticket] CS::Inbound Advisors) AND [Text Field] Previous Value NOT IN ([Responsável Do Ticket] CS::Pedro Arantes, [Responsável Do Ticket] CS:: Natasha Nesello, [Responsável Do Ticket] CS::Nicolas Beschoren, [Responsável Do Ticket] CS::Paulo Santos, [Responsável Do Ticket] CS:: Wagner Lima, [Responsável Do Ticket] CS:: Yorran Dias, [Responsável Do Ticket] CS::Lucas Macedo, [Responsável Do Ticket] CS::Mahara Scholz, [Responsável Do Ticket] CS::Brndon Tavares, [Responsável Do Ticket] CS::Inbound Advisors)) BY Ticket Id

    Link

    Unfurtunetly, this structure did not work :(

    Have you any suggestion to help me with this code?

    Kind regards,

    Natasha Nesello

    0
  • Jacob J Christensen

    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
  • Barbara Bonneau

    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
  • Jessie Schutz

    Hi Barbara! Welcome to the Community!

    I would recommend checking out our SLA feature. I think this might be what you're looking for!

    0
  • Viorica Pop

    Hi Amy,

    I need your advice on a metric/report that has to do with events and I'm currently strugguling on how to do it. The final goal is to determine the time it took to Solve a ticket for the first time without Pending (I hope this is dooable). 

    My direction is to substract from First Resolution Time - Total Time Spent in Pending until first resolution time. So I started with the Timestamp when the ticket went for the first time to Solved:

    First change to Solved (T1):
    SELECT MIN(select(select(select Ticket updated (minutes) by Ticket Updates) by Ticket Text Field Change where Text Field = Status and [Text Field] New Value = [Status] solved))
     
    then the Timestamps when the ticket changed to Pending similar to the one above
    Change to Pending (T2):
    SELECT (select(select Ticket updated (minutes) by Ticket Updates) by Ticket Text Field Change where Text Field = Status and [Text Field] New Value = [Status] pending)
     
     and then to aggregate the [Biz Hrs] Agent Wait Time(hrs) where the Change to Pending (T2) < First change to Solved (T1)
    Finally I would need to substract this value from First Resol. Time . Now my report is a bit messy, so maybe you could offer some advice.
     
    Do you think my approach could lead to something? If I'm missing something here can you please let me know.
     
    Thanks in advance!
    0
  • Jessie Schutz

    Hi Viorica!

    Let me check with some of our Community Moderators on this one...we have a couple Insights gurus who might be able to help. :)

    0
  • Viorica Pop

    Thanks Jessie !

    0

Please sign in to leave a comment.

Powered by Zendesk