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
Trying to create a report for the last 30 days showing the number of solved tickets per timeframe. (ie 10 tickets solved within 1 hour, 3 tickets solved within 2 hours, etc).
Can anyone help??
Hi Christa,
Insights has some pre-built metrics for three such time frames:
You could either use those or create duplicates of them and modify them for your preferred time frames.
Hope that helps you out, let me know if you need more details to move forward.
Thanks Jacob! That's really helpful!
How do I get the actual number of tickets to show next to each section like you have listed?
Also how can I modify this to get my preferred time frame?
Hello!! First time to create a new metric. :) I was told by zendesk support to replicate this metric for a report I'm building.
However, this is what I came up with because there is no 'ticket text field change" and "status". Just "Records of Ticket Text Field Change" and "Ticket Status".
So I continued with that metric. I was also told to input the following in the HOW Tab and Filters
The report is supposed to look like this:
However,when I followed everything (except for the custom metric), no data will show. It says no data match the filtering criteria. I think it might be the custom metric because that's the only one that's not exactly the same as what I was told to do. Am I missing something?
I appreciate the help!
@Christa
Great! You can select Data labels from the Show configuration menu (as shown below).
@Montoya-Maita-L
It looks like you are very close, seems you have selected Ticket status from the Attributes folder, it should be found in the Attribute values folder instead - the text should be orange.
See if that does the trick.
@Christa, missed the part about modifying the time frame.
There is a great guide for creating custom metrics here, but the metrics you need shouldn't be too difficult to create, so here's a quick guide.
Now we have a copy of the original metric, click the Edit button to make changes to it.
That's one new metric, you can create as many as you like. I recommend you use the Fast- and Slow Resolution time metrics to model the upper and lower end of your time frames. Hope that makes sense.
Hey @Maita -
Hopefully one of our awesome community members or moderators will have an answer for you soon! IN the meantime,I encourage you to head over to the Welcome Thread in The Lounge to introduce yourself.
Welcome! We look forward to seeing you around the Community. Happy Zendesking!
I am trying to complete the above Example #1; Number of times a ticket gets reopened. However, I cannot for the life of me find the Status attribute...only Ticket Status.
Has this field been updated? I can't get this to work.
Hi Jason,
I think I see what you're struggling with and it has tripped me up a number of times too.
To get to the Status attribute value in the Text Field = Status part of the metric statement, you first go to the Attribute Values folder and select Text Field:
How can I use Prev/New value twice in the same statement? E.g.
Prev value (status) = open
AND
New value (status) = on hold
AND
Prev value (group = L1)
AND
New value (group = L2)
Not a real example but hope it gets the point across. Is the above possible?
Hi Jacky! You can only capture one text field change at a time in a single metric, since each field change has its own ID. However, you can nest a filtering metric within a larger one. This works because one update may include any number of individual field changes, and you can work with updates and field changes separately.
In this case, you would start by finding one of the field changes in its own metric:
Once you have that, you can create a second metric to find the other field change. When you do, you can add a numeric filter with the first metric:
(The # Ticket Updates at the start is under Metrics, while the Ticket Updates at the end is under Attributes.)
This metric counts ticket updates with a text field change from Group L1 to Group L2, where the update as a whole also includes a status change from open to on-hold. The result should be updates with both types of field change.
Please note - these examples are provided for demonstration purposes only. I can't guarantee them for all use cases, and I strongly recommend that you audit your results.
I hope this helps! Happy reporting!
Thank you so much! I was trying to figure out how to filter a metric by a metric for ages but couldn't.
Perhaps a good opportunity for a new help centre article? Thanks!! :-)
Follow up question I have is: how do I express that the group has not changed in a query? I want to see ticket updates where there has been an update, but the group has not changed.
My initial thought was to use WHERE Ticket Group (historic) = L1 AND Text Field = Group AND [Text Field] New Value = <empty>.
Reason for not using Ticket Group = L1 is because if it gets escalated in the future then this event would no longer count towards the report.
Hi Jacky,
So what are you looking to do is to report on how many Updates has been made on the ticket while it was in one group?
Hi Jacky! If you have Text Field = Group AND [Text Field] New Value = (empty value) in your metric, it will find updates where the group is changed to empty. That isn't quite what you need.
It's difficult to filter for cases where something didn't happen, but you could make it work with another numeric range filter. You'd start with a simple metric that just looks for group changes:
Then, you'd use IFNULL logic when you nest that into a larger metric:
This finds ticket updates with some other text field change, where the ticket was in the "L1" group at the start of the update, and the update as a whole is not counted in the group change metric.
For more information, you can check out GoodData's documentation on IFNULL Best Practices. It's not directly related to this use case, but we also have similar numeric range filtering in our recipe for Reporting on ticket tags.
As before, this metric is just to give you a starting point and some ideas. I can't guarantee this for all use cases, and I strongly recommend that you test and audit the results. There are a lot of moving parts here, so you'll want to make sure they fit together as you intend.
I hope this helps! Happy reporting!
Thank you for this wonderful post!
I tried a lot to create, but unsuccessful in creating a report on all the category (text field) change events.
For example, I have a ticket (ID 178868); wherein the user (agent) changed the category three times until the ticket changed the status from 'open' to 'closed'.
Hence, I am looking to create a report wherein, I can list these category changes in a table. Just like the one below.
I created two metrics:
Count Cat Status: To count the total number of changes in the text field 'Category' wherein the Ticket Status is 'Closed'
SELECT COUNT(Ticket Updates, Ticket Text Field Change) WHERE Text Field = Category AND Ticket Status <> Deleted AND Ticket Status = Closed
Date Created on or after 01 Jan 2019: To report only the ticket created on or after January 1, 2019
Punit
The 'Category' field included under your HOW section shows the current value of the field. That is why it shows the same value against each date.
To show the new value set on each date:
HOW
To reduce your output to your original table:
...you can also add:
FILTER
You can then drop your custom metric 'Count Cat Status'.
Thank you so much Graeme! It worked like a charm.
You are a rockstar. My million thanks to you.
Hi Guys, after some guidance. I must be doing something wrong and am just missing it.....
I am trying to report on tickets that have changed severity, (which is a custom field but shows in the ticket event history when it is changed etc) and found this article which pointed me in what I hoped was the right direction. So I took the example that was used to show tickets being re-opened etc and tried to create a metric for my severity report. So what I ended up doing was this....
No matter what I do it says it has an unexpected ']' and is expecting a ')'.
I re-did the example provided in the article, namely:
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
and that too complains about the ']'.
I edited all the way back to the first value after the IN statement and it looks like it is having an issue with the first attribute listed having the [ ] around it after the IN, however that is what the example shows and I'm assuming this works for others, so I must be doing something wrong.......
Any help or guidance gratefully received.
Hi Peter,
It seems like you are using the attributes instead of attribute values. Please refer to the below elements.
Ticket Updates >> Attributes
Ticket Text Field Change >> Attributes
Text Field >> Attributes
Severity >> Attribute values of Text Field
[Text Field] New Value >> Attributes
[Severity] 2-Urgent, [Severity] 3-Important, [Severity] 4-Moderate, [Severity] 5-Minor >> Attribute values of [Text Field] New Value
[Text Field] Previous Value >> Attributes
[Severity] 1-Critical >> Attribute Values of [Text Field] Previous Value
Ticket Status >> Attributes
Deleted >> Attribute Values of Ticket Status
Also, I can see that [Severity] 4-Moderate starts with a Curly Brackets, like {Severity] 4-Moderate. I am not sure, if it is pasted from somewhere.
Please select the right elements from the list (as mentioned above) and see if it works.
Hi Punit
Many thanks for your input. You were correct re the typo on the Moderate Severity bracket, that indeed was showing as a curly bracket.
So I think I understand what you were saying re the attribute vs the attribute values, that makes sense.
However when I add the attribute value for severity it brings up the list of values and I have to select one or all of them, in my case 1 through 5 from Critical to Minor.
I cannot add just the one value in orange like you have in your example.
And then again when I select the values for the text field New Value and Previous Value etc it adds the values as 1-Critical, 2-Urgent, etc, etc, but they do not have the Severity part first in the square brackets, [Severity] 1-Critical, etc.
So have I somehow got the add of the attribute value incorrect?
So for example if I add the attribute values for Severity my Metric looks like this:
Hi Peter,
What is the name of the custom field, that contains the drop-down values, i.e., Critical, Urgent, Moderate, Minor?
For example, in the syntax shared by me (above) the name of the field was 'Priority', just like the screenshot below.
So, there must be a custom field, with the name 'Severity'.
If you have a field with the name 'Severity' you must see it as 'Severity' in the attibute values of the attribute 'Text Field'.
Trust, this helps!
Many thanks Punit,
All sorted and working now. I was confusing the attribute values of the actual attribute I had called 'Severity' with the attribute values for the [Text Field] New Value etc, which has the severity values included.Got my head around it now. Thanks for your assistance, very much appreciated.
Hi,
Is there any way to get replies data in the report based on metrics with Text Field changes and attributes? I have a report with Priority (Historic) attribute and a custom metric for counting customers' comments (using Text Field changes attribute), but can not build a proper metric for counting ALL agents' replies (because not all events with replies have text field change there). I know the comments are not connected with any field changes but maybe there is some workaround?
Hi Andrei,
I don't think you would need a custom metric if it is just about showing the number of public comments by agents on the tickets. You may try exploring this.
WHAT:
# Ticket Events
HOW:
Public Comment and apply the filter Public Comment is True
[Text Field] New Value
FILTER:
[Text Field] New Value is [Assignee], [Assignee],...
You will get the following report.
Please let me know if this is not the one you were looking for. So I may dig more to find a solution for you.
Hi Punit,
Thank you, but I know how to build this type of report. I have a bit more complex case where historic attribute is involved.
Hi Andrei,
While I'm rather limited on what I can assist with when it comes to custom metrics, it may be helpful for other users to jump in if we had a screenshot of the custom metric you've set up. Any additional information you can provide may beneficial :)
Thanks!
Hi Andrei, try creating a new # Ticket Updates metric.
Instead of:
SELECT COUNT(Ticket Updates, Ticket Text Field Change)
Make the new one as:
SELECT COUNT(Ticket Updates)
I believe this should work for your purposes.
I'm not sure if there are any other implications to duplicating the metric with this slight edit other than the fact that it will begin to include Ticket Updates even if there were no Text Field changes. If anyone knows more about this I would be interested to know as well.
Thanks for sharing Jacky :)
Hi guys,
Thank you but removing Text Field Change attribute causes an error. This is the challenge here. I will try submitting a ticket on this.
Please sign in to leave a comment.