Building custom metrics for the Events model in Insights (Professional and Enterprise) Follow

Comments

21 comments

  • Avatar
    Michael Craddock

    What I am looking for is how long it takes for a Group to pick up a ticket. i.e. if Tier 1 escalates it to Tier 2, how long did it sit with Tier 2 before it was assigned to an Agent?
    Initial Assign time is easy, and I can determine Total time a ticket was unassigned, but not avg unassigned time by each group.

  • Avatar
    Jessie Schutz

    Hi Michael!

    We have a couple Insights gurus that hang out here in the Community, so hopefully they'll be able to help you! I'd also recommend that you post your question in our new Insights Q&A and report requests forum. We review this forum on a regular basis to see if there are any suggestions that would make for a good Tip of the Week!

  • Avatar
    Jacob J Christensen

    @Michael Craddock

    I believe this article could help you achieve that.
    It helped me out with something similar.

  • Avatar
    Jacob J Christensen (Edited )

    Hi,
    I have made a custom metric to find out how many times tickets have been escalated to our second level support (group), when I try to report on this - ideally a daily score - I don't get any current results.

    Can ticket events only be reported on after a ticket has been solved?
    Is that why I don't get the hits I expect from the past week?

    This is my metric by the way:

    SELECT COUNT(Ticket Updates,Ticket Text Field Change) WHERE [Text Field] New Value = [Group] Level 2 support

  • Avatar
    Joseph Black

    Jacob,
    The metric is built correctly. I would recommend checking to make sure that there is data for what you're looking for. If there is, make sure that none of your report or dashboard level filters are excluding that data. If that all looks good from your end then we can investigate a bit further within a ticket.

  • Avatar
    Jacob J Christensen (Edited )

    Thanks Joseph!
    No filters in my way, I've created a ticket.


    EDIT:
    Turned out I had multiple instances of the same group name and had selected the wrong one for this metric. Silly me.

  • Avatar
    Fernando

    Hi. When a ticket is created, it can be worked by both Tier 1 group and Tier 2 group agents, independently of which group the ticket is in. As each Tier has a different hourly cost for us, I would like to calculate the cost of each ticket for our company. I already implemented Total Time Spent metrics (which work like a charm), and created a couple of variables which hold the hourly cost for Tier 1 and for Tier 2, respectively.

    Now, using the Ticket Group attribute would be useless, since what I actually care about is the group to which the updater belongs. I thought I could maybe stick a tier1 tag to my Tier 1's, and a tier2 tag to my Tier 2's, but I'm really really stuck.

    Thanks for any advise!

  • Avatar
    Angel Altura

    Hi Fernando,

    Regarding your question about calculating the cost of your ticket based on the group which the updater belongs, you can still use the attribute Ticket Group. The field Group on the ticket, which the attribute Ticket Group is based on, obtains the group of the Ticket's Assignee/Updater. So whenever the ticket Assignee was changed, this field also change based on the Assignee's group. Therefore, if you use the Ticket Group attribute on your report along side with the metrics Total Time Spent, you'll be able to get the total time spent on the tickets of all the agent/s that belongs to that certain ticket group. In your case, you'll be able to get the Total Time Spent on the tickets that belong to Tier1 and Tier2 group respectively.

  • Avatar
    Fernando

    Hi Maria. Unfortunatelly using ticket group is not an option for us. A ticket can be in group "Tier 1" from new to resolved, and could be worked by both T1 and T2 agents without changing the group. What I finally did was creating metrics for ticket updater in (user a, user b, user c) for tier 1, and for (user d, e, and f) for tier 2 etc.

  • Avatar
    Sean Cuevas

    I'm looking to report not on the # of reopens we've had among our tickets but instead the # of tickets that have had at least one  reopen. How would I create this metric?

  • Avatar
    Matt Hoffman

    @Sean - the key to counting "# of tickets with at least 1 reopen" rather than "total # of reopens" is to build a metric using SELECT COUNT(Ticket Id, Ticket Text Field Change). Building a metric like this will count up the number of unique ticket IDs that satisfy the metric, meaning that no ticket will get counted more than once.

    With that explanation in mind, I have some great news - the exact metric you're looking for is available by default: # Tickets Reopened.

    Give that a try - if you run into any problems, drop us a line at support@zendesk.com.

  • Avatar
    Brian Lehnen

    Hi All,

    Been struggling the past day or so trying to meet/build a requirement for "restored time"

    Essentially, calculating the duration between the time a service incident ticket was opened and the service restored. The ticket may take longer to fully resolved, but I need to capture outage time for the client.

    My thoughts on this are to to use a custom field (checkbox) that the agent can click upon restore. From there I can calculate the time between the empty box field and its change. I thought I had accomplished this via the GoodData reporting UI, but my values do not seem to correct. Originally, I wanted to use a custom MAQL query, but I could not get the syntax correct.

    I took a stab at it using @Jacob's query above, but I'm obviously missing something.

    If there is a simpler way to calculate this, I'm all ears. Otherwise, I would appreciate any insight on the correct query to pull the duration. In the meantime I will keep plugging away.

    Thanks in advance!

  • Avatar
    Matt Hoffman

    @Brian - Thanks for reaching out! Custom metrics can be tricky, but the key to measuring the difference in time between two ticket events is to use duration. That can be done in one of two ways:

    1. Create custom metrics that will capture the timestamp of each event of interest, and subtract the difference: http://zdsk.co/1VUDEOV

    2. If you're only concerned with a change in one field, you can use a simpler method that looks at [Text Field] Duration in Minuteshttp://zdsk.co/233vAL5

    The links I provided contain instructions for how to measure duration by using some generic examples, but you should just follow the idea & general structure of the metrics. Based on what you described, I would start with #2 (checkboxes count as text fields, if that wasn't clear).

    Hope this helps! If you're stuck somewhere just drop us a line at support@zendesk.com.

  • Avatar
    Brian Lehnen

    Thanks Matt,

    Shortly after posting I found the article from #1 in a previous post by @Jacob, which helped me greatly. I ended up using a custom field (checkbox) to mark the point at which the customer has been restored. This allows us to accurately measure outage time for our SLA's. I'm essentially regurgitating the metrics from your post, but in case somebody finds them helpful, here they are:


    Metric: Timestamp—Restored

    SELECT MIN( SELECT( SELECT( SELECT Ticket updated (minutes) BY Ticket Updates) BY Ticket Text Field Change WHERE [Text Field] New Value = [Restored] 1 AND [Text Field] Previous Value = [Restored] 0) BY Ticket Id)

    Metric: Timestamp—Ticket Created

    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)

    Metric: Restore Time

    SELECT (Timestamp—Restored - Timestamp—Ticket Created)

    I am still working on the calculating the running value if the checkbox were to get unchecked and then checked again at a later time. Would be interested if metrics gurus out there have any ideas on how to tackle that scenario?

    Thanks for the follow-up!

    Brian 

     

  • Avatar
    Fernando
    I installed Time Tracking app, and we're using it successfully within Insights to track time and costs, after creating the metrics as described in one of the "recipes" in the Help Center. (I was finally able to solve the problem I had before by creating variables for costs of tier 1 and tier 2, and associating the cost metrics with the agents in each group).
     
    I have I new challenge now: we have business hours that go from 9:30 to 18:00, Monday through Friday. Our agents some times need to work overtime, not just staying a while longer at the office, but also doing some nightly jobs, or even some tasks during weekends or holidays, and I would like to track those times as well, that is, be able to discriminate work done during office hours from work done during off-hours.
     
    Typical sample scenarios would be:
    1. Agent starts working a case Monday at 4pm, and finishes at 8pm. That's 4 hours total. But it's also 2 hours business time, 2 hours off-time. Metric should return 2 hours (off-time only)
    2. Agent starts working a case on a weekend or a holiday at 10am, and finishes at 12pm. That should count as 2 total hours, 2 off-time hours. Metric should return 2 hours (also worked 2 off time hours)
    3. Agent starts working a case on a Tuesday at 10pm, and finishes at 1am Wednesday. That should count as 3 total hours, 3 off-time hours. Metric should return 3 hours
    4. Agent starts working a case on a Friday at 8:30am and finishes at 11:30am. That should count as 3 total hours, 1 off-time and 2 business. Metric should return 1 hour in this case.

    I really cracked my head on this one, but got nothing. I'll appreciate any ideas!

    Fernando

  • Avatar
    Matt Hoffman

    Hey Fernando -

    Unfortunately, what you're describing is not possible - the simplest way to explain this is that the time value you want to 'split' is attributed to just one ticket event & you cannot break that figure down any more than by that one event.

    Sorry to be the bearer of bad news. The best bet at this point may be to investigate some of our 3rd party time tracking integrations such as Harvest. While I don't have experience with Harvest, a more robust time tracking/billing system may have the solution you're looking for.

  • Avatar
    Jennifer

    I'm looking to build a report that uses the public comment is present event. I'd like to build a report that shows how many "new" tickets an agent takes per day - so number of tickets assigned to them per day and also has it's first public comment present. Since "statuses" can change and tickets that were solved on day one can be re-opened on day 2, using "ticket solved that day" isn't accurate. Is it possible to create a metric like this?

  • Avatar
    Robbert Hink

    Hey Jennifer,

    What you are describing should indeed be possible to do with a custom metric. An easy way to achieve this is by creating a custom field, let's say a checkbox called "picked up". You can then set up a trigger, that checks that box when an agent publicly replies to the ticket and when the group and assignee changes and if that box isn't checked yet. What that allows for is that you can look for that specific event rather than looking for the first reply made by an agent specifically in a custom metric. This is a much cleaner solution.

    Once you have this workflow working, you can build your custom metric which should look like this:

    You can then use this metric in a report that could look like this:

    WHAT: # Tickets picked up
    HOW: Date (Event)

  • Avatar
    Jennifer

    Thanks Robbert! This looks like it'll do just what we need. 

  • Avatar
    Jennifer

    Is there a way to get a real time report based on events? We're using events (see above in this thread) to tell us how many new tickets an agent takes in a day. Gooddata can be an hour behind and I haven't been able to create a view in Zendesk based on when an event happens. Therefor my agents have no real time report on their personal metrics. Is there a solution?

  • Avatar
    Jacob J Christensen

    Hi Jennifer,

    What you are asking, I think, is beyond the features of GoodData.

    But a dashboarding service like Geckoboard could help you get some useful real-time data - I did a bit about operational dashboards you may find helpful.

    I'm not exactly sure what the full range of features will be for the upcoming Zendesk Explore product, but it might also be worth a look - I've signed up for early access myself.

Please sign in to leave a comment.

Powered by Zendesk