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] ENDIF
IF (([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.