Admins commonly use custom fields to collect information that Zendesk Support doesn't collect by default. For example, you might want to add the office location to a ticket or the product type a ticket is about. Explore collects custom field information to enable you to add it to your reports.
In this Explore recipe, you'll learn a useful method to compare the number of tickets created between two time periods. Then, you'll break this number down by the values in a custom field.
This recipe contains the following sections:
What you'll need
Skill level: Easy
Time Required: 10 minutes
- Zendesk Explore Professional or Enterprise
- Editor or Admin permissions (see Adding users to Explore)
- Ticket data in Zendesk Support
How to create the report in Explore
Use the following steps to create this report in Explore.
- In Zendesk Explore, click the query (
) icon. - In the Queries library, click New query.
- On the Choose a dataset page, click Support > Tickets > Support: Tickets, then click Apply. Query builder opens.
- Next, you'll create two date range calculated metrics. The first will display the tickets created this month and the second will display tickets created last month. In the Calculations menu (
), click Date range calculated metric.
-
On the Date range calculated metric page, configure the following values:
- Name: Tickets created last month
- Original metric: Tickets
- Defined on: Ticket created
- Date range: Simple > Last month
When you are finished, click Save.
-
Using the same steps, create a second date range calculated metric with the following settings:
- Name: Tickets created this month
- Original metric: Tickets
- Defined on: Ticket created
- Date range: Simple > This month
When you are finished, click Save.
- In the Metrics panel of query builder, click Add.
-
From the list of metrics, expand Calculated metrics and choose the two metrics you just created, Tickets created this month and tickets created last month. Make sure to change the aggregator for both metrics to COUNT. Your query will look similar to this example:
Tip: You can change the two date range calculated metrics to cover any time period you want, for example, this week and last week or this year and last year. - Next, create a result metric calculation. This will show the percentage difference between last months tickets and this months tickets. For more help with result metric calculations, see Using result metric calculations. From the result manipulation menu (
), click Result metric calculation. -
On the Result metric calculation page, click Add a new metric, then configure the following values:
- Name: % Change month
- Formula: (COUNT(Tickets created - This month)-COUNT(Tickets created - Last month))/(COUNT(Tickets created - This month))
- Disable the Clear used metrics check box
Tip: If you're working in a language other than English, read this article to help you enter Explore formulas in your language.
- When you are finished, click Add. Your calculation will look like this example:
- Now, you need to change the display format of the result metric calculation to display as a percentage. In query builder, click the chart configuration (
) menu, then click Display format. - On the Display format page, expand the SUM(% Change month) dropdown, then click %.
- Close the Display format page. Your query will now look similar to this example:
- Finally, we want to break the results down by a custom field. This example uses a custom field named favorite fruit. Custom fields are available as an attribute in Explore. In the Rows panel, click Add.
- From the list of attributes, expand Ticket custom fields, click favorite fruit (or your own custom field attribute), then click Apply. Your query will look similar to the following example:
15 Comments
Thanks Katie ! You save my day 😄
This is great, however, Last Month captures the full previous month whereas This Month stops at the current date (for obvious reasons). This means this is not a direct comparison (ex. 31 days compared to say, 15 days). How can I filter Last Month to show just the first 15 days of the month so that the results of Tickets Created This Month vs Tickets Created Last Month comes from a more comparable time frame?
Hello Coco Freling,
I would create a time calculated metric to accomplish this report. It should allow you to make the comparison as you described and I've linked below an article that goes into detail on this topic.
Adding time and date calculated metrics
Best regards.
How would I create a custom metric of measuring the % change in Agent Engagement (Guide dataset - Knowledge Capture) of month over month and year over year?
Hey Ryan,
It looks like you haven't received a response on this post. Were you able to get the metric set up that you were looking for?
Thanks for the follow-up, Brett Bowser! No, I wasn't able to get this set up. To be honest, I'm rather new to the Explore product, so there may be a better way of getting this information than creating a standard calculated metric. I'm just not sure if there's an easy way to put together a query or a way to display percentage change of a metric over time. Any help would be appreciated! Thanks.
Hey Ryan,
I will follow up in our ticket so we can troubleshoot this custom metric!
Best,
You all need to fix the screenshot on action item 11 as it doesn't match the query in action item 10 and can be confusing as it would give you a different result.
Thanks for mentioning this, Jon. We've flagged it for our documentation team to correct!
Hi Jon Coon, thanks so much for pointing this out! I've now corrected the screenshot above. Thanks again!
So how would I calculate the percentage for each row in a table based on the sum of the overall table. For instance, I have a report that shows the SUM of tickets by each Inquiry Type (custom field), along with a grand total at the bottom.
So how would I add a percentage or each row relative to the total to another column in the report?
Hi pstrauss,
You can duplicate the default COUNT(Tickets) metric and add it to your report. Then you can go to result manipulation>>result path calculation and select Pattern = % of total on rows for the duplicate metric as shown below:
Hope this helps!
Ahh, thanks, Taylor. That's helpful!
Hi!
I followed this recipe and, unfortunately, I found an error!
When there's nothing to count (eg. no tickets created last month), it won't return with '0' or NULL, and the calculations will be wrong or empty.
Try to look at this:
Have anyone encountered this issue and solved it?
Thanks! :-)
Hi Louise, thanks for the question. I've opened a ticket on your behalf so we can investigate what's going on. I'll them make any necessary updates to the article. You should hear from someone soon. Thanks!
Please sign in to leave a comment.