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

In this article I will go over how to create metrics and reports using the Ticket Events datasets.

Ticket Events tracks all events of a ticket. The Ticket Updates attribute is the center of all our Ticket Events, and it's broken into three types of events. When looking at events you will create a count metric with Ticket Updates and one of these three events and use the other attributes to filter for what you are looking for.

Understanding the Events model

Before the Ticket Events model was added to Insights, reporting was ticket-centric, and we could only tell what your helpdesk looked like *now* (for example: current assignee of a ticket, how many tickets are currently open, and so on). Ticket Events tracks all events of a ticket over time.

Here is the model of every Insights project.

The Ticket Updates attribute is the center of all our Ticket Events, and it's broken into three types of events: text field changes, numeric changes, and satisfaction score changes. Let's us define each of these data sets in the events model.

Ticket updates

The “Ticket Updates” is the key to the events model and is an attribute which is represented as a unique ID for each time the Submit button is clicked on a ticket. Within one ticket update you can have multiple child events like this:

An important thing to note is the Ticket Update ID is randomly generated and does not represent the order in which events happen.

There are three different types of Ticket Updates:
  • Ticket Text Field Change
  • Ticket Numeric Change
  • Satisfaction Survey Change

Ticket field change

This attribute captures a change to any standard and custom text field within a ticket. In this dataset we track how a text field changes within a ticket because we store the previous value and the new value.

For example, if a ticket status changes from Closed to Open, the “[Text Field] Previous Value” will be “Closed” and the “[Text Field] New Value” will be “Open”. This type of tracking enables us to see how many times a ticket status changed.

Ticket numeric change

Very similar to the Ticket Field attribute, the Ticket Numeric Change dataset will capture a change to any standard and custom numerical field within a ticket. We track the previous and new numerical values of a ticket which allows us to create time tracking reports.

Satisfaction Survey Change

The last thing that is tracked are changes made to satisfaction surveys. You can tell the new and old value of a satisfaction score and how long (in minutes) it took the customer to take the satisfaction survey or to change his response.

Creating metrics using the ticket events

Now that we’ve gone over the building blocks of the Ticket Events model, the next step is to learn how to create metrics using these different attributes. As previously mentioned, typically, if you want to look at the number events, your metric will look like this:

SELECT COUNT(Ticket Update,<second parameter>)

For more information on COUNT metrics, see Working with COUNT metrics on the GoodData website.

Example 1: How many times did a ticket get re-opened?

Continuing the example we used above, if you are trying to measure how many times a ticket gets re-opened, you can create a metric like this:

SELECT COUNT (Ticket Update, Ticket Text Field Change) WHERE [Text Field] Previous 
Value=[Status] solved AND[Text Field] New Value NOT IN ([Status] closed, [Status] deleted)

The way to interpret this metric in plain English is:

“Count the number of updates where there was a field change where the ticket went from the solve status to any other status that is not closed or deleted”

If you want to slice the report by the lowest grain (Ticket Update) it will look like this:

As you can see ticket ID 226 and 3 re-opens.

In Insights there is a metric called “# Reopens” that computes the same number, but using the metric above allows us to slice by “Date (Event)” so you can see when the event actually took place.

Example 2: How many times was the ticket updated with a comment?

In the past, the Ticket Comments dataset lived independently from the Ticket Updates dataset. In the new data model, Ticket Comments is included within Ticket Updates. For more information on the new data model see, Enhancing Insights- New data model changes that make reporting simpler.

You can now evaluate the number of tickets updated with a comment without using a second parameter. The "Comment Present" attribute inside the Ticket Updates dataset is used instead.

This metric would look like:

SELECT COUNT (Ticket Update) WHERE Comment Present = true AND Ticket Status <> Deleted.

Have more questions? Submit a request

Comments

  • 1

    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 

     

  • 0

    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.

  • 0

    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?

  • 0

    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.

  • 0

    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.

  • 0

    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

    Edited by Jacob J Christensen
  • 0

    @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.

  • 0

    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!

  • 0

    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?

  • 0

    @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.

  • 0

    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.

  • 0

    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.

    Edited by Jacob J Christensen
  • 0

    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!

  • 0

    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?

  • 0

    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.

  • 0
    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

  • 0

    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.

  • 0

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

  • 0

    @Michael Craddock

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

  • 0

    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!

  • 0

    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)

Please sign in to leave a comment.

Powered by Zendesk