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

Zendesk Explore is our new analytics software. In the future, we’ll be discontinuing Insights and you’ll need to start using Explore. To make the move now, see Moving to Explore from Insights.

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.