Explore Recipe: Breaking down tickets created by time period

In this Explore recipe, you'll learn how to create a query that breaks down how many tickets are being created in certain time periods. In this example, you'll be able to report on tickets created over three periods - 9am-5pm, 5pm-1am, 1am-9am. 

What you'll need

Skill level: Advanced

Time Required: 25 minutes

• Zendesk Explore Professional or Enterprise
• Editor or Admin permissions (see Giving agents access to Explore)
• Ticket data in Zendesk Support

How to create the report in Explore

 To create the query

1. In Explore, click the query () icon.
2. In the Queries library, click New query.
3. On the Choose a dataset page, click Support > Ticket updates > Support: Ticket updates. Query builder opens.
   
4. Now, create a custom Group attribute to group the hours of ticket creation together. 
   From the Calculations () menu, click Group.
5. On the Group page, name the group Daily shifts.
6.  From the Ticket created - Hour drop-down, choose Computed from.
   
7. Create three groups, one for each time period. To create a group, click the + icon and then select the hours you want to add. You can then use the arrows to add the values to the group. (Check out Organizing values by groups and sets for more information about setting up groups.)
   • Day shift - 9am-5pm: Hours 9-16
   • Night shift - 5pm-1am: Hours 17-0
   • Early shift - 1am-9am: Hours 1-8
     
     Ensure that Remove left values is chosen from the drop-down at the bottom, and tick Sort attribute members like a time attribute.
8. When you are finished, click Save.
9.  In the Metrics panel, click Add.
10. From the list of metrics, choose Tickets > Tickets created, then click Apply.
11. In the Columns panel, click Add.
12. From the list of attributes, expand Calculated attributes, click your custom attribute Daily shifts, then click Apply.
13. From the Visualization type ( ) menu, choose Bar.
    
    The query is complete. See the screenshot below for an example of how it will look.
    Keep in mind that Explore uses the current user's time zone when displaying queries, so the ticket created hour will be based on that time zone. For that reason, you might want to consider altering the hours to utilize UTC time instead of using your own time zone if you have a global team.