Insights recipe: First reply time by event (version 2)

Have more questions? Submit a request

19 Comments

  • Selçuk Mıynat
    Comment actions Permalink

    Hi,

    I need to generate a weekly report for the first reply times of each agent.

    I've generated a report for each ticket with their first reply times. But I want to sort them from longer to the shorter, and put the agent names right next to each of them.

    How can I do that, please?

    0
  • Amy Dee
    Comment actions Permalink

    Hi Selçuk! If you want to see which agent made the first reply, use the Updater attribute. This recipe finds the event of the first reply, so the Updater is the person who submitted it. We have more information about the various user attributes here: Which user attribute should I use in my report?

    As for displaying results from highest to lowest, you can do that in the report. Hover the cursor over the metric name. That should make a pair of arrows appear. Those arrows sort the column in ascending or descending order:

    I hope this helps! Happy reporting!

    0
  • Felipe Fernandes
    Comment actions Permalink

    This is great. Thanks for sharing. Getting closer to finding a real FRT. In another article, I found a suggestion that I should add an "agent_created" tag to tickets created by an agent and then exclude them from the FRT calculation. How do I modify the recipe(s) to not count FRT for tickets that DO NOT contain the "agent_created" tag? I am happy to entertain another solution if possible too that doesn't involve tags. Our agents often create a lot of tickets which keep the FRT timer ticking so I don't want to count those.

    0
  • Amy Dee
    Comment actions Permalink

    Hi Felipe! Tags are great on the workflow side, but they get tricky on the reporting side. It's especially tricky in cases like this, since tags are not connected to ticket events.

    Tags and events are both connected to tickets, though, so you should still be able to add a tag filter. It will just take a few extra steps. We have details in this recipe: Reporting on ticket tags.

    Basically, you'll need to make a tag-filtering metric, then use a numeric range filter on the report as a whole. That way, the report will only run the first reply metrics on tickets that have (or don't have) your chosen tag.

    As long as you keep the tag elements in a dedicated report filter and your event metrics in the report itself, they should work alongside each other.

    I hope this helps! Happy reporting!

    0
  • Lisbeth Vinueza
    Comment actions Permalink

    Hey @Amy Dee. Is there anyway to tweak this to show the time between Ticket creation and Last Public Comment? 

    0
  • Amy Dee
    Comment actions Permalink

    Hi Lisbeth! That should be possible by switching MIN to MAX throughout the metrics. That would find the highest timestamp of a public agent comment, then subtract the ticket creation timestamp. The result should be the duration between ticket creation and the most recent reply.

    Side note - for the SELECT MIN(Ticket created at (minutes)) elements, it doesn't really matter whether you use MIN or MAX. There is only one creation timestamp per ticket, so it should return the same number either way.

    I hope this helps! Happy reporting! 

    0
  • Lisbeth Vinueza
    Comment actions Permalink

    Hey @Amy Dee

    I don't think it worked as expected:

    My metrics are:

    Last reply time by event (hrs) [Avg]

    Timestamp: Last agent reply

    I am getting the same results as First Time reply.

    0
  • Amy Dee
    Comment actions Permalink

    Hi Lisbeth! There are a couple issues here.

    First, in your "Timestamp: Last agent reply" metric, you replaced the Ticket created at (minutes) fact with Ticket updated (minutes). The end of that metric should be ...Ticket updated (minutes) > (SELECT MAX(Ticket created at (minutes)) BY Ticket Id)... That piece means it only looks for updates that happened after ticket creation, so the metric doesn't record a 0 for agent-created tickets.

    Second, your "Last reply time by event (hrs) [Avg]" metric still has the Timestamp: First agent reply metric nested in it. That means it's still looking for an update that matches the first reply timestamp. You need to put the creation timestamp back in the "Last agent reply" metric, then replace the nested metric here.

    Once you update the timestamps, you should get the duration from creation to the last agent reply instead of creation to the first agent reply.

    I hope this helps! Happy reporting!

    0
  • Lisbeth Vinueza
    Comment actions Permalink

    Thanks Amy Dee! It worked. I looked through it like 10 times but I missed those little details :(

    You are a genius. Thanks for putting this together. 

    1
  • Jim Keller
    Comment actions Permalink

    Hello Amy,

    I am unable to find 'agent' or 'admin' under any of the elements. I can do a general search and find nothing, I can drill down to where it says above it should be listed. And even in a manual search I cannot find just 'agent' or 'admin'. 
    I see now that I only have 'Attributes' if I click on Attribute Values or Attribute Labels, within the element window it just says 'Attribute'.
    Is there a different service/license level that unlocks the other two elements?

    0
  • Brett - Community Manager
    Comment actions Permalink

    Hey Jim,

    I'm going to generate a ticket on your behalf so our Advocacy team can dig into this further. You'll receive a follow-up email shortly stating your ticket has been created so feel free to reply back to that email if you have any additional information to provide.

    Thanks!

    1
  • Jim Keller
    Comment actions Permalink

    Thanks Brett. I have the email and ticket number.

    0
  • Katie Saddlemire
    Comment actions Permalink

    Is there a version of this for explore? 

     

    Thanks! 

    0
  • Carlos Navarro
    Comment actions Permalink

    Hi, Katie. I believe this Explore article could be what you're looking for:

    Please let me know if you have any further questions.

    Thanks!

    0
  • Katie Saddlemire
    Comment actions Permalink

    Thanks Carlos. That's close, but I wanted to do it based on the date the first reply occurred instead of the date the ticket was created. 

    0
  • Amanda Gunn
    Comment actions Permalink

    Hello,

    Do you know how to adjust these queries or how to write queries to instead show the DATE of the first reply time? not the minutes or hours.


    Thank you,

    Amanda

    0
  • Tom Rogers
    Comment actions Permalink

    Hi,

    Is there a way to change the number to HH:MM?

    For example 16.31 = 16:18

    Thanks in advance

    Tom

    0
  • Amy Dee
    Comment actions Permalink

    Hi Tom! Yes, you can change the display formatting to show HH:MM. To start, you'll need to remove the /60 from the end of the metric. That way, the results will be in minutes.

    From there, you can use arithmetic format blocks to change the display. These should be entered in the metric format box:

    Here are a couple options that you can copy and paste:

    • {{{60||00}}}:{{{|60|00}}} hrs
    • {{{1440||[>1]# days\, ; [>0]# day\, ;#}}}{{{60|24|0}}}:{{{|60|00}}} hours

    The first simply displays hours and minutes, while the second will also display days if the number gets that high. For an idea of what this looks like in a chart, here are three example tickets displayed in minutes, HH:MM, and DD HH:MM:

    GoodData has more tips and tricks for the metric format box in their article on Formatting Numbers in Reports.

    I hope this helps! Happy reporting!

    0
  • Lisbeth Vinueza
    Comment actions Permalink

    Hey Amy, 

    I am trying to create a "Last reply time by event (hrs) [Avg]" in Explore. My current recipe in Insights is:

    SELECT AVG(( SELECT MAX(Ticket updated (minutes)) BY Ticket Updates WHERE Updater Role IN (AgentAdmin) AND Public Comment = true AND Ticket updated (minutes) = (SELECT Timestamp: Last agent reply BY Ticket Id) ) - (SELECT MAX(Ticket created at (minutes)) BY Ticket Id)) /60
     
    How can I create this in Explore?
    0

Please sign in to leave a comment.

Powered by Zendesk