Time Tracking recipe: The metrics you need to be measuring

Have more questions? Submit a request

167 Comments

  • Chad Dunbar

    Hi Erica,

     

    It looks like what you want is going to be based on the ticket updates directly. Give this a try:

    Metric 1: SELECT SUM( [Numeric Field] New value /60 ) WHERE[Numeric Field] New value=Time spent last update (sec)

    It's built in the same fashion as the Total Time Spent metric above. That will get the time spent on the updates without interacting with the total time spent values.

    From there, build the report with that metric using Ticket ID, and Updater. If you need the individual update times, then add Ticket Updates as well. Keep in mind, the Ticket Update IDs don't seem to be sequential (can't remember the article for it)

    Filters used are Date (Event) and Updater Role = Agent. To break out individual dates, also add Date (Event) to the report fields.




    0
  • Erica Rodney

    Thank you for the help and details, Chad!

     

    I think I am only couple clicks away from the having the final report!!  But the click (or two) I am missing are the ones that capture the actual collective times spent on a ticket on any given day per agent.  here is an example of a report with the new metric:

    Here is the recipe for the total time per update metric I used: 

    Thank you again for ALLLL of your time and explanations!


    Erica

    0
  • Amy Dee

    Hi Erica! I would advise against using the Time spent last update (sec) field for this type of metric. If you have two updates in a row with the same handle time, then there won't be a change to that field. That means Zendesk won't record a new field value for that update, and you'd lose it in this metric.

    I recommend creating the Total time spent (min) metric from this recipe article. Then, when you construct your report, use Updater and Date (Event) under HOW. That should show you the total time spent by each agent each day, regardless of who is assigned to the ticket now.

    For more information about user attributes, check out this guide: Which user attribute should I use in Insights?

    I hope this helps! Happy reporting!

    0
  • Michael H.

    I've tried creating the metrics, but it seems like they dont get any data.

     

    Any idea?

     

    My metrics look like this,

     



    0
  • Amy Dee

    Hi Michael! I think I see the issue here.

    In the Total time spent (min) metric, you have the wrong attribute. Instead of Ticket Numeric Change, you should have Numeric Field. Since the attribute and value don't line up right now, the metric can't return any valid data.

    Update the Total time spent (min) metric first. Then, remove and replace it in the Avg time spent per ticket (min) metric (and any others where it is nested within a larger metric). That will ensure that the larger metrics are using the latest version of the Total time spent (min) metric.

    Once you get that attribute fixed and all the other metrics updated, you should start seeing data in the report.

    I hope this helps! Happy reporting!

    0
  • Michael H.

    Thank you Amy, it works flawlessly now! I actually saw the error myself going over the data :)

     

    Another question for you.

    I have different tags - "Apples", "Oranges", "Bananas" and "Waterbottle".

    Since Apples, oranges and bananas goes under the same category, I want it to count them together.

    So when I make a report, I can make it show like this.

    Michael has sold 34 fruits (combining total amount of the three) and 8 waterbottles.

    How do I create a metric that counts the sum of specific tags under one name?

    0
  • Amy Dee

    Hi Michael! That's great! 

    For tags, you need to take some extra steps. Tags are unusual in the data model, since one ticket can have any number of tags at the same time, and you may need to report on any one of those tags.

    To make that possible, tag data is stored in a separate dataset. You need to create metrics that connect to the tag data, then use those metrics to filter your report. We have recipes in this article: Reporting on ticket tags

    The recipe article shows how to find tickets with (or without) a specific tag. That would work for "waterbottle" in your example. For the "fruit" tags, you would combine them with "IN" -

    • SELECT IFNULL((SELECT # Tickets WHERE Ticket Tag IN (applesorangesbananas)),0)

    This finds tickets with at least one of the three fruit tags. If none of the three are present, it will return a 0 instead of a null. (Watch out for those parentheses!)

    Once you have your tag filtering metrics, you can use them to filter your whole report with a Numeric Range Filter. You can also use them to filter individual metrics by nesting them within a larger metric. The details are in the recipe article.

    I hope this helps! Happy reporting!

    0
  • Michael H.

    Amy, you are truely a master in GoodData!

     

    Is it possible to track the average time spent active on at ticket, where the agent have the ticket open in the browser?

     

    The average time we made on tickets earlier, is from create to solve, but since we have 10 tickets that have 10 days solve time, it kinda ruins the whole statistics out of like 200.

    0
  • Helvijs Vigners

    Looks like total amount for a ticket gets allocated to assignee... How do I exclude any time other agents logged on the ticket...

     

    Example:Those are all agents each row. One of the agents suddenly had 3000 minutes.

    This screws up reporting as most of the time on one of the tickets was logged by someone else, but allocated to the assignee.

    Anybody able to help?

    0
  • Amy Dee

    Hi Helvijs! Which attribute are you using for the agents in this report? Also, how is the Time (min) metric set up?

    If you are using the Ticket Assignee attribute, Insights will combine all time under the current assignee. That's expected.

    However, if you use the Updater attribute (and a metric that works with updates), then Insights should show you the total time for each agent, regardless of who is assigned to the ticket.

    We have a quick summary of user attributes in this article: Which user attribute should I use in my report?

    I hope this helps! Happy reporting!

    1
  • Helvijs Vigners

    Thanks Amy! That was helpful. What about Date filter? which one do I select to get all time logged for an agent on the date.

    I have tried Date(Assignee Updated) and Updater as to which agent.

    My metric in minutes is:SELECT SUM(IFNULL([Numeric Field] New value,0)-IFNULL([Numeric Field] Old value,0))/60 WHERE Numeric Field=Total time spent (sec)

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

     

    0
  • Amy Dee

    Hi Helvijs! For a report like this, I recommend Date (Event). That captures the date of any given update. Since your metrics are looking at changes to the Time Tracking field, Date (Event) will show you the time spent each day.

    Date (Assignee Updated) is the date of the most recent update by the ticket's assignee, and it identifies whole tickets. For example, say a ticket was created on December 1st and last updated by the assignee on December 6th. With the filter in your screenshot, Insights would find that ticket, then show you all the combined time from the 1st through the 6th.

    For more information about dates, I recommend this article: Insights date dimensions.

    I hope this helps! Happy reporting!

    0
  • Helvijs Vigners

    Perfect! :) 

    0
  • Sore

    Hello

    I've come across a little issue that is quite critical in order to measure my agents time spent.

    When a "Follow up" ticket is created, it inherits the old tickets total time spent.

    That means on the follow up ticket, when it is created it has the value of the original ticket. However when the first agent submits, it will overwrite this value which is great.

    What is problematic is that the agent submitting the first time tracked on the follow up ticket, will have the old value withdrawn from his "Total Time spent metric" in Insights!

    So If I spend 1 hour on the original ticket, and 1 minut on the follow up, I will end up with -59 minutes on the follow up ticket in the Total Time spent metric.

    How can I avoid negative values in my metric?

    1
  • Michael Ichioka

    Is this recipe up-to-date? I'm getting this error message at Step 5 for Metric 1:

     Edit:PEBKAC - thanks Bryan!

    0
  • Bryan Flaherty

    Hello Michael!  It looks like you might be hitting the green add under the element selector.  In order to put an element into a metric you are building you will want to double click on that element.

    The add button is for you to add your newly created metric to a report when you are all done with it.

    Hope this helps!

     

    1
  • James P

    Hi,

    Can anyone help me on this.? 

    I want to get the total time spent or the resolution time of the Tier2 Agent. that is less pending status and less on-hold status. 

    means. the time which the ticket set to pending and on-hold should not be counted.

    need to get this one:

    total time spent or the resolution time

    if possible it can get the time base on the Assignee. where ever the ticket came from (tier1 or other department)

     

     

    *** tried the existing metrics in zendesk and coded it but unfortunately can get the exact time for the tier2 agent.****

    Please help me. 

     

    Thanks in Advance. 

    0
  • Jessie Schutz

    Hey Sore! Sorry for the delayed response! I'm going to check with some Insights and Time Tracking experts to see if we can get some info on what you're experiencing here. Stand by!

    0
  • Stephen Fusco

    Hey Sore, 

    This is actually a known issue that our development team is actively working on fixing. In the meantime they have suggested this workaround:

    "This particular issue only affects follow-up tickets. An unintended consequence of the way Insights computes for the Total Time Spent is causing follow-up tickets in particular to inherit the time tracking ticket field values from the original ticket. 

    In the meantime, I can give you some recipes to exclude follow-up tickets from your main reports (and report on them separately). It's not ideal, and it shouldn't be a long term fix. However, it should allow you to better assess the impact of these affected tickets in your reports for now.

    To get started, follow this recipe to create a metric for creation events: Insights recipe: Ticket creation events.

    Next, create a second metric to find follow-up tickets. (It will use the metric from that first step.)

    • SELECT IFNULL((SELECT(SELECT Ticket Creation Events WHERE Ticket Update Via=Closed Ticket) BY Ticket Id ALL OTHER WITHOUT PF), 0)


    Once you have this metric, you can use it in a Numeric Range filter at the report level:

    • For the Attribute, choose Ticket Id
    • For the Metric, choose the Follow-Up Tickets metric above
    • For the Range, you have two options:
      • "Is equal to 0" means you will only see tickets that are NOT follow-up tickets
      • "Is equal to 1" means you will only see tickets that ARE follow-up tickets"

    We hope to have it fixed soon but hopefully that information helps in the interim. 

    0
  • Kyle Clark

    Any solution to Sore's issue with follow-up tickets? I think this works as a workaround also.

    0
  • Cherise Russell

    Hi,

    This solution is great but I need a further step and not sure how to work it out. The scenario is this:

    We have about 30clients, each client's support package started on a different date, some of these packages over lap the year, so for instance one started on 11 November 2017 and runs until 10 October 2018.

    Ideally I want to be able to have a report that shows the organisation name and total time spent on tickets from the start of their support package. And even better if I can also display how many hours they have left.

    I understand to work this out I need to calculate the hours between current date and Support Package Start Date - but a bit lost how to do this. Anyone that can help or point me in the right direction please?

    0
  • Jessie Schutz

    Thanks for sharing that, Kyle! 

    0
  • Sebastian Kochanowski

    Referring to comment https://support.zendesk.com/hc/en-us/articles/203664236/comments/208311427 

    Wouldn't usage of SELECT ABS help here to eliminate negative values?

    0
  • Andrea McMahon

    Hi - I am getting an error when trying to add the first metric. 

     

    0
  • Sebastian Kochanowski

    Quick look via smartphone - looks like you didn't put first  ifnull function body in brackets ()

    0
  • Andrea McMahon

    Sebastian - you were right! Thank you!

    0
  • Jessie Schutz

    Good lookin' out, Sebastian! Thanks!

    0
  • Rob Muise

    I am getting this error when trying t build this report.

     

    0
  • Sebastian Kochanowski

    Rob,

    Are you sure you tried to add FACT in right place? Your cursor seems to be in wrong place....

    0
  • Rob Muise

    Yes, it was after the last open bracket.  I was just checking my spacing.

     

    Rob

     

    0

Please sign in to leave a comment.

Powered by Zendesk