In this Explore recipe, you'll learn how to create an Explore query to show the number of times tickets were assigned from one group to another. For example, you could report how many tickets are being escalated from the Support group to the Sales group.
What you'll need
Skill level: Moderate
Time Required: 20 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
- In Zendesk Explore, click the query () icon.
- In the Queries Library, click New Query.
- Now, select a dataset containing the ticket update data you'll need to build the query.
Select Support: Ticket Events. Query Builder opens.
- Now, create a Standard calculated metric to show when tickets move from one group to another. Click Calculations (), and then Standard calculated metric.
- Copy the formula below and paste it into the metric editor. Add a title for the metric like Ticket assigns from Support to Sales.
Replace the IDs in the formula with the group IDs of the two groups you want to report about. To get the group IDs of your groups, make a GET request to subdomain.zendesk.com/api/v2/groups.json. Check out our documentation here for more information. In the example below, the first group ID would be for 'Support' as the previous group and the second group ID would be for 'Sales' as the new group the ticket is assigned to.
The completed metric will look like the screenshot below:
IF ([Changes - Field name] = "group_id" AND
[Changes - Previous value] = "36000000xxxx" AND
[Changes - New value] = "3600000xxxxx") THEN
- When you are finished, click Save.
- In the Metrics panel, click Add.
- From the list of metrics, expand Calculated metrics, click the custom metric you created, Ticket assigns from Support to Sales, then click Apply.
- Ensure the metric aggregator for Ticket assigns from Support to Sales is set to COUNT. For more information, see Choosing metric aggregators.
Next stepsIf you want to see the agents who are making the group update, you can add an attribute to show the agent's name.
- In the Rows panel, click Add.
- From the list of attributes, expand Updater, click Updater name, then click Apply.
- Click Result manipulation (), then Metric filter. Set the metric filter so it only shows results greater than 0.
Then the query is complete! Check out the screenshot below as an example.