With thanks to Taylor Bowser from Zendesk customer advocates.
A common request from Explore admins is to compare the volume of tickets (or any other metric) between two date ranges. In this Explore recipe, you'll find out how to use date range calculated metrics to create a report showing the percentage variation in tickets created between yesterday and today. You can modify this query to suit your own business needs.
What you'll need
Skill level: Easy
Time required: 10 minutes
- Zendesk Explore Professional or Enterprise
- Editor or Admin permissions (see Giving agents access to Explore)
- Ticket data in Zendesk Support
Creating the first metric
In this first procedure, you'll create a date range calculated metric that displays the number of tickets that were created today.
To create the metric
- In Explore, click the query (
) icon.
- In the Queries library, click New query.
- On the Choose a dataset page, click Support > Tickets > Support: Tickets [default], then click New query. Query builder opens.
- In the Calculations menu (
), click Date range calculated metric.
- On the Date range calculated metric page, configure the following options:
- Name: Tickets created today
- Original metric: Tickets
- Defined on: Ticket created
- Date range: Simple > Today
- When you are finished, click Save.
Creating the second metric
In this procedure, you'll create a date range calculated metric that displays the number of tickets that were created yesterday.
To create the metric
- In Explore, click the query (
) icon.
- In the Queries library, click New query.
- On the Choose a dataset page, click Support > Tickets > Support: Tickets [default], then click New query. Query builder opens.
- In the Calculations menu (
), click Date range calculated metric.
- On the Date range calculated metric page, configure the following options:
- Name: Tickets created yesterday
- Original metric: Tickets
- Defined on: Ticket created
- Date range: Simple > Yesterday
Tip: You can use these metrics as the basis for your own queries. For example, you could report on Talk or Chat volume changes, or solved tickets instead of created tickets. For help with the available metrics and attributes, see the building queries section of the Explore documentation. - When you are finished, click Save.
Creating the query
In this final procedure, you'll add the two date range calculated metrics you created and format them to create the final query.
To create the query
- In the Metrics panel of query builder, click Add.
- From the list of metrics, expand Calculated metrics and choose the two date range calculated metrics you created, Tickets created today and Tickets created yesterday. When you are finished, click Apply.
- Ensure that the Tickets created today metric appears first in the Metrics panel. If it doesn't, you can drag and drop it into the correct position.
- Click each metric you just added and change the aggregator to COUNT.
Your query will look like the following screenshot:
- In the visualization type menu (
), click KPI.
- In the chart configuration menu (
), click Chart.
- On the Chart page, configure the following settings:
- Label: Enter Tickets created today (and change since yesterday)
- Hide label: Uncheck this setting
- Show variation: Enable this setting
- Variation type: Set to Percentage
The query is now complete and will look similar to the screenshot below:
4 Comments
Hey Rob, hope your weekend went well. So for some reason today the calculation is not adding up correctly....I did create a filter to exclude a primary category so it was not part of the tickets created...should I exclude one of the primary categories a different way?
Total created tickets today (48) after filter
Total created tickets yesterday (13)
New variance 500% increase
Hi James, my weekend was good and I hope yours was too! it sounds like what you are doing should work, but it might need a deeper investigation to see what's wrong. I've opened up a ticket on your behalf so we can dig into the problem. Thanks!
Hello All,
Can you please help me do it more like in "Insights":
Hello Boyan Parshorov,
Can you please share more detail on what you are looking to accomplish in Explore?
Best regards.
Please sign in to leave a comment.