Explore recipe: Time over time percentage change for a custom dropdown ticket field

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
• How to create the report in Explore
• Further reading

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.

1. In Zendesk Explore, click the query () icon.
2. In the Queries library, click New query.
3. On the Choose a dataset page, click Support > Tickets > Support: Tickets, then click Apply. Query builder opens.
4. 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.
5. 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.
6. 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.
7. In the Metrics panel of query builder, click Add.
8. 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.
9. 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.
10. 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.
11. When you are finished, click Add. Your calculation will look like this example:
    
    
12. 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.
13. On the Display format page, expand the SUM(% Change month) dropdown, then click %.
    
    
14. Close the Display format page. Your query will now look similar to this example:
    
    
15. 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.
16. 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:
    
    

Further reading

• Reporting with custom fields
• The 'About' field