Explore recipe: Daily ticket activity in the last 30 days

In this Explore recipe, you'll learn how to create a query that shows your daily ticket activity over the last 30 days. This includes tickets created, reopened, solved and deleted. This helps you get an indication of ticket volume.

What you'll need

Skill level: Moderate

Time Required: 25 minutes

• Zendesk Explore Professional
• Editor or Admin permissions (see Adding users to Explore)
• Ticket data in Zendesk Support

Creating the query

To create the query

1. In Zendesk Explore, click the query () icon.
2. In the Queries Library, click New Query.
3. Select a dataset containing the ticket data you'll need to build the query. Select Zendesk: Ticket Updates. Query Builder opens.
   
   Now, set up some custom Standard calculated metrics to show the solved and deleted ticket updates as negative results. This makes the query easier to picture in terms of tickets in (created and reopened) and tickets out (solves and deletions).
4. Click Calculations (), and then click Standard calculated metric. 
5. Create two standard calculated metrics by copying the formulas below into each metric. Name the metrics - Tickets solved and - Deletions. They will look like the screenshots below. Click Save after you create each metric.
   

   0 - COUNT(Tickets solved)

   0 - COUNT(Deletions)

   
   For the - Deletions metric, ensure you also check the box to Compute separately.
   
6. In the Metrics panel, click Add.
7. From the list of metrics, expand Tickets, select Tickets created and Tickets reopened, then click Apply.
8. From the same list of metrics, expand Calculated metrics, click the two custom metrics you created, - Tickets solved and - Deletions, then click Apply.
9. Ensure the metric aggregators for your custom metrics are set to COUNT. For more information, see Choosing metric aggregators.
10. In the Columns panel, click Add.
11. In the attributes list expand Time - Ticket update, click Update - Date, then click Apply.
12. Under Filters, click Add. Expand Time - Ticket update, then click Update - Date.
13. Open the filter you just added, click Edit date ranges, and open the Advanced tab. Set the date range to the last 30 days.
    
14. Click Visualization type (), then select Column.
15. Click Chart configuration (), then click Displayed values and change Show value to Show. Change Position to Inside.
    
16. Click Chart configuration (), then click Chart and tick Stacked. Ensure that every other option is unticked.
    

Then the query is complete! See an example below in the screenshot. (Note that in the example the date filter is showing the results by month rather than the last 30 days. Check out Editing dates and date ranges for more information on changing the date filter on this report.)