In this Explore recipe, you'll learn how to create a query that shows the number of tickets being created by agents and end-users.
What you'll need
Skill level: Moderate
Time Required: 20 minutes
- Zendesk Explore Professional or Enterprise
- Editor or Admin permissions (see Giving agents access to Explore)
- Ticket data in Zendesk Support
Creating the query
To create the query
- In Zendesk 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, then click New query. Query builder opens.
- Now, create Standard calculated metrics to count the number of agent created tickets and the number of end-user created tickets. Click Calculations (
), then click Standard calculated metric.
- Copy the formulas below and paste each into the metric editor. Add a title for each metric like Tickets created by agents and Tickets created by end-users. The completed metrics will look like the screenshots below. Ensure you click Save each time you create a metric.
IF (([Changes - Field name]="status" AND [Changes - Previous value]=NULL)
AND ([Updater role] = "Admin" OR [Updater role] = "Agent"))
THEN [Update ID] ENDIFIF (([Changes - Field name]="status" AND [Changes - Previous value]=NULL) AND
([Updater role] = "End-user")) THEN [Update ID] ENDIF
Tip: If you're working in a language other than English, read this article to help you enter Explore formulas in your language. - In the Metrics panel, click Add.
- From the list of metrics, expand Calculated metrics, click the custom metrics you created, Tickets created by agents and Tickets created by end-users, then click Apply.
- Ensure the metric aggregators for your custom metrics are set to COUNT. For more information, see Choosing metric aggregators.
- In the Columns panel, click Add.
- From the list of attributes, choose Time - Ticket created > Ticket created - Month, then click Apply.
-
Click the Ticket created - Month attribute and set the date range to This year. See Editing dates and date ranges for help setting a different date range.
- Click Visualization type (
), then select Column.
- Click Chart configuration (
), then click Chart.
-
On the Chart page, enable Stacked and Stacked: Percentage. Ensure that no other settings are ticked.
- Click Chart configuration (
), then click Displayed values.
- On the Displayed values page, change Show value to Show. Change Position to Outside.
The query is now complete. See the screenshot below as an example.
9 Comments
I receive this error message for both Tickets created by Agents and Tickets created by End-Users when using the calculated metrics described above. I have tried with both SUM and COUNT. Is there a new attribute for this value?
Ha! Need to use the Zendesk: Ticket Updates dataset, not the Zendesk: Ticket dataset. The way step 3 wraps around, it's easy to miss that last word.
Upon tinkering with the calculations, it looks like all of the fields in the provided calculations are no longer exist. Assuming [Requester role] would sub for [Updater role] and [Ticket ID] would sub for [Update ID], it's unclear what should now be subbed for [Changes - Field name] and [Changes - Previous value].
Hey Alessia,
It looks like you haven't received a response on this post yet. Are you still running into issues getting the above report working properly for you? If so, I'll need to create a ticket on your behalf so our Customer Care team can look at the query you've created.
Let me know!
Hey Brett Bowser
on my end, it is saying the data set is too large, even though, it literally cant be (looking at yesterdays data, there should only be a handful) and it looks kind of funny. Any advice?
All set! Thanks Brendon!
Same here.
Thanks for taking the time to share this with us Brendon :)
Glad you were able to get the above recipe working!
I created this query as described and using the Zendesk: Ticket Updates dataset but the results are not correct.
To double check, I used the Decompose function and the tickets created by agents are tickets created by end-users; moreover, I found also inbound phone calls tickets in the decompose (which is not supposed to happen as I am using the Talk dataset).
I have the need to have a clear overview (month over month/year over year) of how many tickets are created by agents/admins and not their replies to end-users.
Is there a way to find these data?
Please sign in to leave a comment.