By Cindy Au, GoodData
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 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 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.
- Ticket text field changes
- Ticket numeric field changes
- Satisfaction Survey Change
Ticket text field changes
To measure ticket text field changes you will use the Records of Ticket Text Field Changes attribute. 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 field changes
Very similar to the Records of Ticket Text Field Changes 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 changes
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, Records of Ticket Text Field Changes) 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.