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

Have more questions? Submit a request

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.

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)

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?

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!

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

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!

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)?

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!

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.

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.

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.

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?

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?

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.

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?

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.

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?

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.

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!

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.

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.

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

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!

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.

Thanks for jumping in to help, Matt!

Yes, thanks!

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!

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!

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?

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.