Tip: Utilizing a variety of calculations in an Explore Query to understand escalated tickets

Community Moderator

Posted Jan 18, 2021

The Goal

One of the most difficult transitions for me from Insights to Explore was understanding how all of the "calculated" and "calculation" options work and where to find them. In this example, I am comparing our escalated tickets to our total tickets to understand the trends year over year.

Note: there are even more ways to get to this information, this is just a jumping off point.

Prerequisites

Zendesk Explore Professional

Instructions

Step 1

Create a new Explore query using the Support: Tickets dataset
Name your query

Step 2

Select your metrics
Start with D_COUNT(Tickets) to get your total ticket volume
Next you will need to create a Standard Calculated Metric to find your escalated tickets

Step 3

Click on Calculations in the right menu
Select "Standard Calculated Metric"
We escalate our tickets through the JIRA app, which tags our tickets with the jira_escalated tag, so I'm going to use Tag Reporting to create the following metric:
```
IF (INCLUDES_ANY([Ticket tags], "jira_escalated")) THEN [Ticket ID] ENDIF
```

Click Save

Step 4

Now you should have a table that shows your total ticket volume and your escalated ticket volume.
I recommend setting the Visualization type to Table so that it doesn't change as we work, and you may want to change the Header background color to improve contrast for accessibility. I set mine to #d7dadb

Step 5

Now you get to create your first calculation! What I want to understand first is what percentage of our total tickets are escalated tickets. This is a simple calculation in which you divide the number of escalated tickets by the total number of tickets. Explore has a handy way to do these simple calculations called Result Metric Calculation.
First select Result Manipulation from the right menu
From the options there, select "Result Metric Calculation"
Click on Add a New Metric to expand the editor
Name your calculation and then enter the parameters
```
D_COUNT(JIRA Tickets)/D_COUNT(Tickets)
```
You can either select these from the dropdown menu next to the word Insert, or just start typing it in
You can see here, I'm simply dividing our escalated tickets by total tickets
Click the Add button to add it to your metrics
In order for this number to show up correctly, you will need to set it's Display Format to %. The default sets 2 decimal points for %. You can use the Custom option to set 0 decimal points if you'd like.

Note: If this is all you need, you have the option to select the check box next to "Clear used metrics" toward the bottom of the window. This will leave you with a single column in your table that includes the percentage of escalated tickets. From there you can include attributes and/or filters, and select a different visualization type. For example, you could compare years in a column chart or bar chart.

Step 6

Now that you have your basic metrics covered, you can begin to add attributes. I selected the following:
Columns: Ticket created - Year
I used the Advanced date range selector to filter this to the current year, plus the two previous years

Rows: Ticket created - Month
If you'd like, you can click on the Row Header, to shorten Ticket created - Month to just Month
You can also add filters at this point if you'd like. For example, you could narrow it to a specific group, brand, or a custom ticket field.

Step 7

This is a great start to help me understand our escalated ticket behavior, but I'd also like to compare our total ticket volume change year over year, so I can see if that had any impact on how many JIRA tickets we filed.
To start this process, I'm going to use the Result Path Calculation.

Tip: The Result Path Calculation will replace the metric it's calculating. To show it side by side with it's attendant metric, simply create a duplicate Standard Calculated Metric for the metric you'd like to use.

First I create a Standard Calculated Metric that is a duplicate of D_COUNT(Tickets). I name this: Result Path Ticket DCount
The calculation is simply:
```
D_COUNT(Tickets)
```
I position this new metric between my two existing metrics

My table now looks like this:

You can see that the duplicate metric gives the same numbers as the original.
Now that I have the duplicate, I can go to Result Manipulation and select Result Path Calculation. The advantage to using this option, is that you don't have to try and create complex calculations on your own via Result Metric Calculation.
For the new metric, I'm going to select the following:

Note: the first year, will not have any result because there is no previous element to compare it to

Now that I have all of the calculations I want, I'm going to clean this table up, and use some styling to understand what it's telling me.

Step 8

First, I'm going to rename that Result Path Calculation column to something more understandable: "% Difference"
Next, I'm going to click on Chart and then Columns, and I'm going to hide the 3rd column that has the blank % Difference from our Result Path Calculation.

Because I really want the % Difference to stand out, I'm going to use the Advanced Display Formatting to designate green font color for increases, and red for decreases in volume year over year. For that, I'll use the following:

IF (SUM(Result Path Ticket DCount) > 0) THEN
    {
        "precision": 0,
        "scale": 1,
        "prefix": "",
        "decimalSeparator": ".",
        "italic": FALSE,
        "bold": TRUE,
        "suffix": "%",
        "fontColor": "#00FF00",
        "thousandsSeparator": " "
    }

ELSE
    {
        "precision": 0,
        "scale": 1,
        "prefix": "",
        "decimalSeparator": ".",
        "italic": FALSE,
        "bold": TRUE,
        "suffix": "%",
        "fontColor": "#EB4962",
        "thousandsSeparator": " "
    }
ENDIF

Don't forget to click Apply!
Finally, I'd like to set and format the Averages for the Rows.
First, click on Result Manipulation and select Totals
Click on Rows under "Grand totals on" and select the aggregator to AVG

Note: Currently, you can select only one aggregator for Totals, so you couldn't, for example set SUM for the absolute values and AVG for the percentage values. In these cases, I err on the side of the percentage values.

To set the formatting for the Totals, click on Chart configuration and then Chart. For "Total style" you can select the formatting of your choice. I have set mine to Bold and Italic.