In this Explore recipe, you'll learn how to create a report that compares your performance to specific SLA targets. This query is specific to the first reply time SLA target but you can modify it to suit any SLA metric.
What you'll need
Skill level: Moderate
Time Required: 20 Minutes
- Zendesk Explore Professional or Enterprise
- Editor or Admin permissions (see Giving agents access to Explore)
- Ticket data in Zendesk Support
Creating the query
To create the query
- In Explore, click the query (
) icon. - In the Queries library, click New query.
- On the Choose a dataset page, click Support > SLAs > Support: SLAs, then click New query. Query builder opens.
- Now, create a Standard calculated attribute to calculate the different intervals of your targets. You can modify the values to suit the intervals you want to report on. From the Calculations (
) menu, click Standard calculated attribute. - On the Standard calculated attribute page, enter the name SLA Metric Value Interval for the attribute.
- Copy the formula below and paste it into the Formula editor.
IF (VALUE(SLA metric completion time (min))<120) THEN "0-2 hours" ELSE
See the screenshot below for an example of how the completed attribute will look.
IF (VALUE(SLA metric completion time (min))<240) THEN "2-4 hours" ELSE
IF (VALUE(SLA metric completion time (min))<480) THEN "4-8 hours" ELSE "Over 8 hours"
ENDIF ENDIF ENDIF
Tip: If you're working in a language other than English, read this article to help you enter Explore formulas in your language. - In the Metrics panel, click Add.
- From the list of metrics, choose SLA Tickets > SLA Tickets, then click Apply.
- In the Columns panel, click Add.
- From the list of attributes, choose Calculated attributes and then click the custom attribute you created earlier. In the example above it is called SLA Metric Value Interval. Then click Apply.
- Now, add a filter to show the metric value for first reply time only. In the Filters panel, click Add.
- From the list of attributes, choose Ticket SLA > SLA metric, then click Add.
- Click the filter you just added and change the filter to First Reply Time.
- From the visualization type menu (
), click Column.
- From the chart configuration menu (
), click Displayed values, then change Show value to Show.
The query is complete. Here's an example of how the completed report might look.
Next steps
If you want to show the query in percentage format, click Visualization Type ( ) and choose Pie.
16 Comments
Hi!
Thanks for this explanation. But when I take all the steps, I get this error (sorry for it being in dutch):
I get an error saying there's a fault in line -1 and column -1. These are, of course, out of reach in my window. When I put some enters before the IF's, and put them on different lines, the fault on line -1 shifts to 0, 1 and 2, dependable on the amount of enters.
What am I doing wrong, or what is going wrong! Help! Haha
Hi Nick,
Thanks for letting us know!
One of our moderators were able to replicate this on their end as well. I'm going to create a ticket on your behalf so we can dig into this further.
Cheers!
I don't see the option to select a calculated metric within the columns when I am trying to build this query.
Hey James,
Any chance you could provide a screenshot of what you see on your end? Definitely odd that you don't see this option.
Let me know!
Brett Bowser
I found my error. I originally created a calculated metric instead of an attribute. Thanks for the response.
Thanks for the heads up, James!
However the metric doesn't tell if the SLA was breached or not which is the most important.
Do you have a graphic for that?
Hey Francesco,
There are a couple of default metrics that should capture breached SLA tickets. You can use the Breached active SLA tickets or the Breached SLA tickets metrics which are mentioned in the following article: Metrics and attributes for Zendesk Support
I hope this helps!
How do I set this up to recognise the business hours rather than calendar hours?
Hey Haleema,
There should be separate metrics you can use that will account for the business hours you've set up. For example, there's the First reply time - Business hours (min) metric created for you. You can see a full list of available metrics here: Metrics and attributes for Zendesk Support
I hope this helps!
For anyone else getting the format error when copy pasting the attribution calculation, I think we need to change "SLA metric value time" to "SLA metric completion time" as per:
"The metric was renamed from SLA metric value time to SLA metric completion time."
- https://support.zendesk.com/hc/en-us/articles/360041814933-Explore-dataset-updates-January-2020
Also, in the screenshot of the attribution calculation there seem to be 2 extra "END IF"s that shouldn't be present.
Hi Cint thanks very much for this feedback. You are indeed correct. The metric was renamed to SLA metric completion time. I've updated the article to reflect this.
I did double-check and the three END IFs are required in this example.
Another way of writing the formula to remove those would be to use:
IF (VALUE(SLA metric completion time (min))<120) THEN "0-2 hours" ELIF
(VALUE(SLA metric completion time (min))<240) THEN "2-4 hours" ELIF
(VALUE(SLA metric completion time (min))<480) THEN "4-8 hours" ELSE "Over 8 hours"
ENDIF
But I think both should work just fine. Thanks again for pointing this out!
Hello
It seems that an error occurs regardless of which code is written in patterns 1 and 2. Can you understand the cause?
■Pattern1
IF (VALUE(SLA metric completion time (min))<120) THEN "0-2 hours" ELSE
IF (VALUE(SLA metric completion time (min))<240) THEN "2-4 hours" ELSE
IF (VALUE(SLA metric completion time (min))<480) THEN "4-8 hours" ELSE "Over 8 hours"
ENDIF ENDIF ENDIF
■Pattern2
IF (VALUE(SLA metric completion time (min))<120) THEN "0-2 hours" ELIF
(VALUE(SLA metric completion time (min))<240) THEN "2-4 hours" ELIF
(VALUE(SLA metric completion time (min))<480) THEN "4-8 hours" ELSE "Over 8 hours"
ENDIF
Attached capture error message
"There is a problem with the formula. Please check the syntax of the formula and try again."
Hi SATORI株式会社,
Based on your screenshot, it seems like you're setting this up under another dataset. The metric SLA metric completion time is under the SLAs dataset, so it can only be referenced in a custom attributes and metrics when working with that specific dataset. I tested with the second one – this formula:
– and I was able to save it without errors.
Can you try setting this up again under the Support: SLAs dataset?
Hope this helps. Thank you!
Hi - can you please give an example of how you would add this formula for Response based SLAs?
Please sign in to leave a comment.