Explore is Zendesk’s powerful tool for building reports and dashboards from all of your ticket and user data. As covered in our three-part series on Explore basics, each report comprises metrics and attributes. Explore is full of metrics such as # of tickets, average wait time, SLA adherence rate and much more. But what happens when the metric you’re looking for isn’t there? Thankfully, Zendesk allows us to create our own metrics, known as Standard calculated metrics.

Today, we’ll cover how to create our own metrics to build powerful, customised reports.

Challenge

For the sake of this article, let’s imagine a common use case.

Just about every contact centre under the sun will be measuring the volume of abandoned calls they are receiving each week or month. An abandoned call occurs when a caller gives up waiting on the line before being connected to an agent. Long wait times are inconvenient and annoying for customers, so it makes sense to want to keep an eye on this KPI.

What happens, however, if a user hangs up after just one second in the queue? Many customer service managers would prefer for this call to not count negatively against their KPIs, and it’s arguable that this caller didn’t hang up due to long wait times. In the industry, this is known as a “short abandoned call”. Our challenge today is to produce a new metric that measures inbound calls that were abandoned in the queue, excluding those users who hung up within 10 seconds of waiting. We’ll call these “long abandoned calls”.

Head over to Explore and open up a new report using the Talk - Calls dataset in Explore. 

See my previous article, Building Reports with Zendesk Explore - Part 1 to learn more about creating a new report.

‍

‍

Normally, to add a new metric, we’d click on the “Add >” button under Metrics. Before we can find our custom metric in that list though, we have to make it!

Click on the Calculations icon on the right of the page:

‍

‍

Then go ahead and click on Standard calculated metric - this is what Zendesk calls custom-made metrics.

‍

‍

First things first, let’s give our new metric a name.

‍

‍

Below the Name field, you’ll see a large and empty formula field. This is where the magic happens!

‍

‍

Zendesk Explore uses a language akin to SQL. Even if you have no SQL experience, follow along as you’ll likely recognise many of the functions from tools like Excel or Google Sheets.

In this box, we need to tell Zendesk exactly which calls we want our metric to include. We can begin by asking ourselves, in plain English, what are the conditions that any given ticket must meet to be included in our metric. They are:

• It must be an inbound call
  
• The call must be abandoned in the queue
  
• The caller must have waited at least ten seconds before abandoning the call
  

That’s it! Pretty simple in English, now we just need to convert it into syntax that Explore will understand. Let’s begin.

‍

An IF statement as our base

An “if” statement typically goes like this: If X is true, then do Y. It can also be a bit more convoluted than that. We could, for example, have multiple conditions, as our example does. If A is true, and B is true, and C is true, then do Z.

So we’ll use our IF statement to lay out those three conditions we detailed above. We’ll also use it to give as an output at the end (the “then do Z” part).

Go ahead and write IF on the first line. Don’t worry if you see a red error message after this stage, that will disappear once our formula is complete.

‍

The first condition

Next up, the first of our conditions. To separate each condition from the next, we bookend them with parentheses (brackets).

After that, we need to look for the first field we want to limit the tickets by, Call direction. If you’re not sure what field you’re looking for, the best bet is to use the “select a field” button below, where you can search for fields.

‍

‍

Click Select a field, then search for call direction, then select it from the list, as above.

This will insert the element into your query, as below.

‍

‍

We now have an opportunity to define which calls we want to include, based on their direction. Go ahead and finish the line, so that it reads 

([Call direction]="Inbound")

‍

‍

Congratulations, you’ve just created your first condition! We’ve told Zendesk to only include calls where the call direction was inbound, and we’ve wrapped up the line with a closing bracket to tell Zendesk that that’s the end of that condition.

If we were to wrap things up here (including closing the IF statement), then our new metric would only include inbound calls.

‍

The AND operator

If we jumped straight ahead to the next condition, Zendesk wouldn’t know how our conditions relate to each other. Think about the difference between writing 4 and 4, compared to simply writing 4,4, or 4 * 4. Similarly, in Zendesk, we need to tell it that we need the first condition to be true, and the second, and the third. To do this, unsurprisingly, we can use the AND function.

‍

‍

The second condition

Looking at our plain English conditions, above, we see that we now want to tell Zendesk to only include tickets that were abandoned in the queue. To achieve this, we’ll use the exact same structure as before: a field inserted from the Select a field list, followed by the value we require that field to have.

In this case, our field is Call completions status, and the value we want for this attribute is Abandoned in queue. Let’s add that to the next line:

([Call completion status]="Abandoned in queue")

‍

‍

We’re getting there! We’re now telling Zendesk to give us all inbound calls that were abandoned in the queue. Now we just need to tell it to only count those calls where the caller waited ten seconds or more.

‍

The final condition

As before, go ahead and throw in another AND between your previous condition and the next.

‍

‍

Now we have a line waiting for our final condition. We’re going to use the Call wait time (sec) metric for this one.

We need to add “VALUE” before our metric that we’ll be pulling from (Call wait time), in order to tell Zendesk to extract the value of that metric.

Go ahead and add the line:

(VALUE(Call wait time (sec)) >= 10)

This tells Zendesk to only include calls in which the wait time was greater than or equal to ten seconds.

‍

‍

Super! You’ve now got all three of your conditions in there. All we need to do is wrap up the formula.

Finishing the IF statement

Now we’ve told Zendesk what to look for, we need to tell it what to do when it finds tickets that match all of these conditions. To do this, we use the THEN operator. Imagine this as a question. “Then what”? Well, following from that, we can tell Zendesk exactly what we want to output. We can actually keep it really simple here and tell Zendesk to simply return the Call ID of each call that meets the above conditions.

Later on, we’ll use a separate button to tell Zendesk to count all of these Call IDs.

‍

‍

Finally, as we started this formula with an IF operator, we must always end it with a simple ENDIF. This tells Zendesk that this IF statement ends here.

This is what your formula should now look like, and you should have the green tick at this stage! If you don’t, go through and check that you didn’t miss a character, or paste it in from below.

‍

‍

Final formula:

IF

([Call direction]="Inbound")

AND

([Call completion status]="Abandoned in queue")

AND

(VALUE(Call wait time (sec)) >= 10)

THEN [Call ID]

ENDIF
‍

Go ahead and click the white Save button at the bottom of the formula.

Congratulations, you just created your first Standard calculated metric, or custom metric in common parlance.

Next week we will talk about putting your new custom metric to good use and building on it even further.