Time Tracking recipe: Leaderboards, time logs, top customers Follow

Comments

35 comments

  • Avatar
    Ken Shampo

    How do I get the total time on spent on tickets to be hours? I have tried to throw in some /60s in there but it seems I am doing it in the wrong place.

  • Avatar
    Sabrina Nielsen

    Hi Ken,

    Our metric looks like this:

    SELECT SUM([Numeric Field] New value - IFNULL([Numeric Field] Old value, 0)) / 3600 WHERE Numeric Field = Total time spent (sec)

  • Avatar
    Jeff Callahan

    I was able to get the example Leaderboard to work however this does not account for collaboration.  If I add time to a ticket solved by another agent my time will be counted under that agent in the leaderboard.   

    Is there a way to accurately add a count of solved tickets to the report  Sarah Kay  detailed? 

  • Avatar
    Megan Howell

    Jeff,

    You could add # Tickets Solved under the WHAT in the report that Sarah Kay detailed above, and as long as you are slicing by updater, it should capture the tickets where that person (the updater) marked the ticket as solved. You'd want to use the # Solved Tickets that looks like this: 

    Note that this would miss tickets that are solved by the customer, or those solved by business rules, so I don't necessarily recommend this route, if you are doing a lot of collaboration, you might be better off leaving the solved tickets as a separate report and putting them on the same dashboard!

  • Avatar
    Jeff Callahan

    Megan,

    Thanks!   Would you be able to paste that in as text?  I realize that I would need to manually select the details, but it would be a great starting point.  

    We do a lot of collaboration, but unfortunately we also have a lot of business rules that close our tickets.

    Thanks again!
    Jeff

  • Avatar
    Megan Howell

    Jeff,

     

    SELECT IFNULL(COUNT(Ticket Id, Ticket Text Field Change),0) WHERE [Text Field] New Value IN ([Status] solved, [Status] closed) AND [Text Field] Previous Value <> [Status] solved AND Ticket Status IN (Solved, Closed) AND Date (Event) = (SELECT MAX(SELECT Date (Ticket Solved) BY Ticket Id) BY Ticket Id)

     

    Hopefully that helps!

  • Avatar
    Chris Lambert

    Can anyone provide a formula that exclude tickets over x minutes form these calculations?  I have agents that sometime forget to exit out of a ticket when they leave for the day and it is really throwing off my numbers - I have some agents that have time spent on tickets of 12 hours + which I know is not possible.

    Also, when looking at average time per update and average time per ticket, are either of those numbers inclusive of one or the other (i.e the average time per update would also be part of the average time per ticket)?

  • Avatar
    William Macken

    Chris,

    To filter out tickets with Total Time Spent over X, you can include a numeric range filter on your report that looks like the following:

    This will filter out tickets that have a Total Time Spent value higher than the value included in the filter.

    In regards to your questions concerning average time per update and average time per ticket-- not exactly. The sum of individual updates (total time spent) is taken from all tickets and then averaged to find the average time per ticket, whereas the average time per update is an average of the time spent per update across all updates within each ticket.

    Hopefully that helps clear things up!

  • Avatar
    Zoe Winker

    I've gone through this several times, and I get almost all of the time metrics back as 0 or pretty close to 0, which seems highly inaccurate. I double checked all the metrics I created and can't seem to find the issue... I've double checked each step to make sure I did it exactly as laid out in this article.

     

  • Avatar
    Jacob J Christensen

    Hi Zoe,

    I have one possible answer for this. Can you confirm that the time tracking fields are present on ALL your your ticket forms?

    If they are missing on even one they will not measure at all....if my memory serves me right.

    If this is the case it should be an easy fix to add the fields, update some tickets and wait for the data to sync.

  • Avatar
    Daniel Lopez Henao

    Hola a todos , estoy tratando de generar un reporte que me permita ver el tiempo que registra cada agente en los tickets, pues este reporte es necesario ver el ticket id, cuando lo genero no me cuenta el tiempo dedicado en un ticket por varios agentes.

     

    es decir si tengo el ticket 11111 y el agente a y el agente b registran tiempo, me aparece solo el tiempo del agente que lo tiene asignado y el tiempo que cerro.

  • Avatar
    David Birchmier

    Hola Daniel,

    No estoy seguro de cómo hacer esto dentro de los informes nativos, pero tal vez alguien más puede aconsejar.

    Esto es posible dentro de los informes de Tymeshift. Estoy adjuntando una captura de pantalla como un ejemplo de lo que creo que buscas. ¿Es esto lo que estás buscando?

  • Avatar
    Chris Pettigrew

    Hello:

    We are trying to create a report that uses the recipes for Time Tracking. A lot of them require the use of the 'Updater' metric in order to obtain each time entry that occurs on a ticket. This is especially important when the 'assignee' isn't necessarily the person who is doing the update. However, this metric is really ugly when it is placed in a graph (either along an axis or as a series). For example, my 'Updater' ID is: Admin - Chris Pettigrew (15257916187)

    That said, if I try to run the report using 'Ticket Assignee', it will miss the updates that I do on tickets that I wasn't assigned to but I provided updates on.

    Is there some way to aggregate data based on the 'Updater Metric', but group it under 'Ticket Assignee'? Thus, giving me all the updates that I did on tickets, but would display the Agent names more elegantly?

  • Avatar
    Bob Novak

    Hi Chris - you should be able to modify the display label of the Updater attribute:

    If you change that dropdown to just "Updater Name" that should be what you are looking for.

  • Avatar
    Rene de Haas

    Hi all,

    I'm working on the report "Time spent per customer (leaderboard). When I follow the steps above, I will get a huge list of users. It's too big to render a graph.

    So I added the filter "Top 10 User by Total time spent (min)". This works as expected, however, I get agents in the  top 10. Apparently agents can be seen as users too? 

    Can someone explain to me how Zendesk defines "users" and how I can fix this leaderboard so only end-users are shown? 

     

     
  • Avatar
    Jacob J Christensen

    Hi Rene,

    Sounds like you want to filter by the user role or updater role, both are available so you can limit the set of users by their role - End user, Agent or Admin.

  • Avatar
    Ben Greiner

    We want to see the Time Spent per (top 15) Customer over the last 30 days. Using the recipe, Which customers does my support team spend the most time supporting? we were seeing the Top 15 customers of all time. I added an optional Filter Metric By > Date (Assignee Updated) is the last 30 days. This helped eliminate our all-time "winners". However, we are still seeing extreme times that appear to include more than the last 30 days. Any tips on how to really limit this to the last 30 days?

  • Avatar
    Dipesh Dave

     Hey Ben,

    For the filter aspect when you selected the Filter Metric By > Date (Assignee Updated) did you select the range to be from today to 30 days ago?

    By selecting this range it should strictly limit the report to the last 30 days.

  • Avatar
    Amy Dee

    Hi Ben! I think the issue here is your date dimension. Date (Assignee Updated) counts whole tickets based on the date of the assignee's last update.

    In the report you described, Insights will find all tickets that were last updated by their assignee in the past 30 days, then count each ticket's entire handle time. That could be misleading if a ticket had tons of activity 31-60 days ago, then one quick update 29 days ago.

    If you want to find the handle time for updates that were actually made in the past 30 days, you'll need to use Date (Event) instead. That should should show you the handle time from the past 30 days, regardless of when the ticket was created or what happened 31+ days ago.

    I hope this helps! Happy reporting!

  • Avatar
    Matt Winer

    So I did the first one " How much time did my support agents spend working on tickets last week?"

    Prior to putting the filter in I have results

    But then when I put the filter in: Week (Mon-Sun)/Year (Ticket Solved). I get no results.  Thinking we haven't maybe solved any tickets since I've turned on time tracking I changed it to Week (Mon-Sun)/Year (Assignee Updated) but I still get no results.

     

  • Avatar
    Jacob J Christensen

    I think you may be right Matt.

    You could try updating the Week (Mon-Sun)/Year (Ticket Solved) and select This week instead of last week. Or you can wait until Monday and see if the data shows up.

    The Assignee Updated I believe is an update to the user, not their tickets.

  • Avatar
    Matt Winer

    Yeah, that was a stupid mistake.  Of course there's nothing last week, I only turned it on this week.  :)  thanks! 

  • Avatar
    Felix Frenzel

    Hey. 

    How come I do not see the first metrics:

    • Avg time spent per ticket (min)

    • Avg time spent per update (min)

    • Total time spent (min)

    I am on the professional plan, somehow these metrics are not available. 

    Thanks in advance for the help!

  • Avatar
    Matt Winer

    Hey Felix, 

    Did you go through the first step and create those metrics? 

    1) The time-tracking metrics you need to be measuring http://support.zendesk.com/entries/78238928-Time-Tracking-Recipes-Three-essential-metrics

    Gotta start there first then move on to the next pages.

  • Avatar
    Jessie Schutz

    Thanks for jumping in to help, Matt!

  • Avatar
    Felix Frenzel

    Yes, thanks!

  • Avatar
    Adrian Bester

    I'm trying to set up the time-consuming report. If I choose top 10 last week it only displays 3 tickets. If I change that to top 10 this week 1 ticket. 

    I followed and checked all steps (I believe!) I must be missing something!

     

  • Avatar
    Amy Dee

    Hi Adrian! This is most likely an issue with filter order. Filter order doesn't make much of a difference unless you're using ranking filters. That is because Insights processes filters from top to bottom. 

    In this case, Insights is finding the top 10 tickets for all time, then finding which of those tickets had an update last week. That's why only 3 tickets are showing up; the other 7 weren't updated during that time.

    As a best practice, always put the ranking filter last in the list. That way, Insights will filter down to the relevant tickets first, then find the top 10 from those results.

    I hope this helps! Happy reporting!

  • Avatar
    Lucien Stevens

    I finished following the steps for the first report (Last Weeks time log) and all i get is the # of tickets closed for the filtered time. All the other boxes are empty. Anything i am missing? 

  • Avatar
    Jacob J Christensen

    Hi Lucien,

    My guess would be that you either haven't installed the Time tracking app and enabled the fields on your ticket form(s), or you have it on some, but not all forms - the time tracking fields need to be on all your ticket forms for the time to be recorded.

    Hope that helps you out.

Please sign in to leave a comment.

Powered by Zendesk