This article explains 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 Zendesk looked like currently (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 define each of these data sets in the events model.
Ticket updates
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 events, such as the image below:
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 field change:
- Ticket text field changes
- Ticket numeric field changes
- Satisfaction survey changes
Ticket text field changes
To measure ticket text field changes, you will use the Ticket Text Field Change dataset. This dataset captures changes to any system or custom text field within a ticket. In this model, "text field" refers to any qualitative field, including ticket text fields, dropdown fields, and checkboxes.
There are three key attributes for each text field change:
- Text Field: the field that changed
- [Text Field] Previous Value: the value before the change
- [Text Field] New Value: the value after the change
For example, if a ticket status changes from Solved to Open, it will have these three values:
- Text Field: Status
- [Text Field] Previous Value: [Status] solved
- [Text Field] New Value: [Status] open
This type of tracking enables us to see how many times a ticket text field changed.
Ticket numeric field changes
Very similar to ticket text field changes, the Ticket Numeric Change dataset captures changes to any system or custom numeric field within a ticket. In this model, "numeric field" refers to any quantitative field, including ticket numeric and decimal fields.
There are also three key pieces of information for each numeric field change. However, numeric fields behave a bit differently.
- Numeric Field is an attribute, and it captures the field that changed.
- [Numeric Field] Old value and [Numeric Field] New value are both facts, and they capture the values before and after the change.
For examples of how to use numeric field changes in reports, check out this recipe article for the Time Tracking app.
Satisfaction Survey changes
The third type of change that is tracked is satisfaction surveys. Satisfaction surveys are similar to ticket text fields, but they have special rules and restrictions in Zendesk.
The Satisfaction Survey Change dataset has two key attributes:
- Satisfaction Old Value: the value before the change
- Satisfaction New Value: the value after the change
These attributes only have four possible values: Unoffered, Offered, Bad, and Good.
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. Typically, if you want to look at the number of events, your metric will look like this:
- SELECT COUNT(Ticket Updates, <second parameter>)
The default # Ticket Updates metric counts updates with text field changes:
- SELECT COUNT(Ticket Updates, Ticket Text Field Change)
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 Updates, Ticket Text Field Change) WHERE Text Field = Status AND [Text Field] New Value IN ([Status] open, [Status] pending, [Status] hold) AND [Text Field] Previous Value = [Status] solved AND Ticket Status <> Deleted
The way to interpret this metric in plain English is:
“Count the number of updates with at least one ticket text field change, where the status field changed to open, pending, or on-hold status from solved status, and the ticket has not been deleted.”
If you want to slice the report by the lowest grain (Ticket Updates) it will look like this:
As you can see, ticket ID 226 had 3 re-opens.
In Insights there is a default metric called # Reopens that measures the total number of reopens on each ticket. The custom metric above allows you to slice by Date (Event), so you can see when each reopen actually took place.
Example 2: How many times was the ticket updated with a comment?
Ticket comments are not stored in a separate dataset. They are connected directly to the Ticket Updates dataset.
There are three default metrics that count updates with comments: # Total Comments, # Public Comments, and # Private Comments. These metrics use the Comment Present and Public Comment attributes to find updates with public or private comments.
For example, the # Total Comments metric looks like this:
- SELECT COUNT (Ticket Updates) WHERE Comment Present = true AND Ticket Status <> Deleted
Note the metric does not include a second parameter in the COUNT, since comments are not connected with any field changes.
74 Comments
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.
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!
@Michael Craddock
I believe this article could help you achieve that.
It helped me out with something similar.
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
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.
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.
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!
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.
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.
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?
@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.
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!
@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 Minutes. http://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.
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
I really cracked my head on this one, but got nothing. I'll appreciate any ideas!
Fernando
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.
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?
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)
Thanks Robbert! This looks like it'll do just what we need.
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?
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.
I am trying to create the example metric you have at the bottom of the post:
SELECT COUNT (Ticket Update) WHERE Comment Present = true AND Ticket Status <> Deleted.
But I don't see "Ticket Update" as an option; I only see "# Ticket Updates" and that doesn't seem to work as shown here:
Am I missing something?
You need to specify:
SELECT COUNT(# Ticket Updates) WHERE ...
i.e. #Ticket Updates must go enclosed between parenthesis.
Thanks for helping out, Fernando!
Hi, I am having trouble finding one of the Attributes. I cannot for the life of me find "Ticket Text Field Change", all that I can find that is similar is "Records of Ticket Text Field Changes".
I am trying to build a metric where it checks that the "[Text Field] Previous Value" changed in two instances, Assignee and Group, but it doesn't seem to be working, and I am assuming it is due to the improper Attribute.
Hi Paul,
If you go to Attributes and start typing [text the "[Text Field] Previous Value" and "[Text Field] New Value" should appear.
Hi Jacob, I can find those no problem. What I meant is the value within brackets after SELECT COUNT. I cannot find the attribute "Ticket Text Field Change", it only shows me "Records of Ticket Text Field Changes"
Ah, ok.
I think this is a case of the attribute being renamed, for me it is now also called "Records of Ticket Text Field Changes", and the documentation hasn't kept up with that change.
Yeah, I thought that could be it, but I wanted to make sure. I am currently trying to build a metric, and it is not working, so I thought that could be the problem. I am essentially trying to see the amount of tickets where the previous value was different in two fields, but it is failing.
This is what I've tried:
SELECT COUNT(Ticket Id, Records of Ticket Text Field Changes) WHERE ([Text Field] Previous Value = [Assignee] Hind) AND ([Text Field] Previous Value = [Group] Product) AND Ticket Status <> Deleted
AND
SELECT COUNT(Ticket Id, Records of Ticket Text Field Changes) WHERE ([Text Field] Previous Value = [Assignee] Hind) AND [Group] Product) AND Ticket Status <> Deleted
Both return no values, even though it is a fact that tickets had those values at the same time in the past. Does anyone know how to make this work?
P.S. I also had a metric with [Text Field] New Value, and all the new values, but it also wasn't working
Hi Paul! The issue here is that the metrics are looking for two different actions in the same text field change.
The metrics are counting Ticket Id by Records of Ticket Text Field Changes; they're looking at tickets based on individual field changes. This means each metric will only look at one text field change at a time, even if it has multiple [Text Field] Previous Value elements.
That's why these metrics aren't working. No one text field change has two previous values at the same time, so the metric will always be empty.
If you want to combine text field changes, you need to create separate metrics. In this case, you would use three:
Assigned to Hind:
SELECT COUNT(Ticket Id, Records of Ticket Text Field Changes) WHERE [Text Field] Previous Value = [Assignee] Hind AND Ticket Status <> Deleted
Assigned in Product:
SELECT COUNT(Ticket Id, Records of Ticket Text Field Changes) WHERE [Text Field] Previous Value = [Group] Product AND Ticket Status <> Deleted
Assigned to Hind and Product:
SELECT COUNT(Ticket Id, Records of Ticket Text Field Changes) WHERE (SELECT Assigned to Hind BY Ticket Id) = 1 AND (SELECT Assigned in Product BY Ticket Id) = 1
The third metric finds tickets that were previously assigned to Hind and previously in the Product group (but not necessarily at the same time). It can do this, because it figures out the text field changes separately, then combines the results.
If you want to find cases where a ticket was assigned to Hind in the Product group, and the group and assignee changed at the same time, then replace Ticket Id with Ticket Updates in the metrics above. That follows the same logic, but it's looking for the two field changes within the same update.
I hope this helps! Happy reporting!
Please sign in to leave a comment.