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, and 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
- In Explore, click the query (
) icon. - In the Queries library, click New query.
- On the Choose a dataset page, click Support > Ticket updates > Support: Ticket updates. Query builder opens.
- Now, create a custom Group attribute to group the hours of ticket creation together.
From the Calculations (
) menu, click Group.
- On the Group page, name the group Daily shifts.
- From the Ticket created - Hour drop-down, choose Computed from.
- 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.
- When you are finished, click Save.
- In the Metrics panel, click Add.
- From the list of metrics, choose Tickets > Tickets created, then click Apply.
- In the Columns panel, click Add.
- From the list of attributes, expand Calculated attributes, click your custom attribute Daily shifts, then click Apply.
- 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.
13 Comments
Any suggestions on what to do when time shifts overlap when trying to gauge volume per timezone? We have GMT, EST and PST shifts and so all three overlap. So far we followed this and created 2 groups - "covered" and "uncovered" for now, but curious of suggestions on how to visualize when timezones overlap.
Hello John,
It looks like the community is having trouble finding a response to your ask. We will go ahead and include this in our Community Roundup to get more eyes on your question.
Best regards.
Thanks Devan - Community Manager! We ended up creating a different shift for each overlapping timezone as to better understand the coverage. For example GMT, GMT/EST, EST/PST, PST, etc.
Here is a simple mock-up of that:
This may not be the final solution, but it did help visualize the overlapping and volume of each timezone that we could later put on a dashboard to see certain times and recent trends.
Thanks for taking the time to share this John!
Hello,
Is there anyway to create a report that shows the number of tickets created each quarter by Customer Organization?
I've been trying to set this up with mediocre luck. So far, I have COUNT(End-user submitted tickets) for Metrics, Requester organization name for Columns, and Ticket created - Quarter for the rows. I thought I was on the right track, but the information shown per quarter is inaccurate. I applied filters to show only Q4 of 2019, but I am seeing a customer organization in the chart, this customer hasn't submitted a ticket since September 30th which is the end of Q3. Another customer shows 18 for Q4 when they've submitted 21 in Q4. It seems like the metrics are all over the place and not sure why.
Best regards,
Ryan
Hello Ryan Frye,
I think we can piece this together for you, but we'd like a little more information regarding the way you were trying to accomplish this. Would you be able to share some screenshots with us of the Explore formula you tried to use so we can take a closer look?
Best regards.
Hi Devan,
The top two filters are for Ticket Created - Q4 and Ticket Created - 2019.
Here is a screenshot of the current query. I was able to figure out everything except for one hiccup. The fourth bar in the graph from the bottom, the ticket it reflects was submitted on September 30th at 11:30PM. My only thought at this time is that there may be a time conversion since we're on MST. Could that be the case in this instance?
Best regards,
Ryan
Hello Ryan Frye,
I would recommend trying the time range filter and seeing if that resolves your issue. I've included an article that goes into detail on how to apply these for your current report below.
Adding interactive dashboard widgets
Best regards.
Hi Devan,
Thank you for your help!
Best regards,
Ryan
Hey John Cason,
could you share how you achieved your time zone graph?
would be much appreciated !
Hi there! I'm also interested in creating overlapping shifts.
For now, I have used an IF/ELIF combination with arrays:
IF (IN([Ticket created - Hour],ARRAY("6","7","8","9","10","11","12","13"))) THEN "Morning" etc
But I noticed that the tickets are counted only once, in the "earliest" shift of two that correspond to that time slot. Yes, I tried both D_COUNT and COUNT.
Any help would be much appreciated.
Hey Alina,
It looks like you were able to get this question answered in a ticket and the solution was to create a separate metric for each of your shifts.
Let us know if you have any other questions :)
Hello team
I think your step 6 instruction has the bold the wrong way round
"From the Ticket created - Hour drop-down, choose Computed from."
Cheers!
Please sign in to leave a comment.