In this recipe, you'll learn how to create a query that shows 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 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 a Standard calculated metric to show when tickets move from one group to another. From the Calculations (
) menu, click Standard calculated metric.
-
On the Standard calculated metric page, enter or paste the formula below into the Formula field. 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
[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. - When you are finished, click Save.
- In the Metrics panel, click Add.
- From the list of metrics, choose Calculated metrics > Ticket assigns from Support to Sales (the calculated metric you just created), 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 steps
If 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, choose Updater > Updater name, then click Apply.
-
From the Result manipulation (
) page, click Metric filter.
- On the Metric filter page, set the metric filter so it only shows results greater than 0.
Your query is now complete. See the screenshot below for an example of how it might look.
47 Comments
I've managed to replicate this report and I am wondering if we are able to have it as a percentage.
I looking on for a recipe for escalation rate in general. Thanks in advance.
Hi Everyone,
I'm trying to accomplish something similar but currently i'm only interested in how many ticket have been transferred out of any given group regardless of which other group it has been assigned to e.g:
Is there another way to do this apart from added all the group ID's? Maybe something like:
IF ([Changes - Field name] = "group_id" AND
[Changes - Previous value] = ANY AND
[Changes - New value] = ANY) THEN
[Update ID]
ENDIF
Thanks!
Lizyer Maxim,
You can change your results to a percentage by using a Result path calculation.
Von Au
If you do not want to limit this metric to certain groups, you can omit the lines with [Changes - Previous value] and [Changes - New value]
Hi all,
This works for me to see ticket movements between groups:
But I face a problem when I try to track ticket status.
My goal is to see tickets moved between groups only in case they were reopened.
I've tried to add multiple conditions into one metric:
But it returns 0 values.
Also I tried to filter result of first metric with attribute
But when using with first metric this attribute also returns only 0 values.
What am I doing wrong?
Hey guys.
So I get this error message when I use this code snippet (yes even after changing the id for the groups), does ayone hav any idea of how to fix this issue?
"references "Changes - New value" which is as an unknown attribute. Adjust the calculation and try again. (Error code: 8a623173)"
Fridtjof Hals - are you using the Ticket Updates dataset? This attribute doesn't exist in the Tickets dataset.
Hi Hillary, thanks for pointing that out.
I apperantly need to read the guide when I follow the guide ;-P
Also, it helped to change my language to english as it failed the first time I tried with 'Ticket updates' instead of 'Tickets'.
Thanks for your feedback.
Dear All,
I am trying the following way:
IF ([Changes - Field name] = "group_id"
AND [Changes - Previous value]="22800412"
OR [Changes - Previous value]="23890481"
OR [Changes - Previous value]="360000244479"
AND [Changes - New value]="24854292")
THEN [Update ID]
ENDIF
But it gives me a lot bigger count that it should.
I am trying to achieve the following:
If the ticket was in Group ,1,2,3 or 4 and then reassigned to 5 , to count how much are the tickets reassigned.
OK, I've found it(with some help):
It should have additional () after the first AND and before the last:
IF ([Changes - Field name] = "group_id"
AND
([Changes - Previous value]="22800412"
OR [Changes - Previous value]="23890481"
OR [Changes - Previous value]="360000244479"
OR [Changes - Previous value]="24230382" )
AND
[Changes - New value]="24854292")
THEN [Update ID]
ENDIF
I've followed the steps in this article, including the last section to view which agents have reassigned how many tickets.
The query then displays not only agents & admins, but also end-users. Which makes me doubt the data since they can't reassign tickets?
Also - I then added the filter 'Updater Role' to only Agents & Admins, but I can still see end-users in the query.
Kristin Bouveng, is your metric filter including results greater than 0? For the lines where end users are showing, does your metric show a value of 1?
If my organization has dozens of groups, how can I calculate the total number of tickets that were assigned to a specific group (and solved by any group)? I have tried the following formulas, filtering them to tickets created in 2021 for easier verification.
Formula 1 - Seems like the logical option for organizations with a large number of zendesk groups, but displayed a wildly inaccurate number in the single digits.
Attempt 2 - The outputted value was wildly inflated. I also would prefer not to use this method because it would require adding each group ID individually and it would remain a static list requiring manual updating if new groups are created.
Hi Dustin Suits,
Thanks for reaching out! To confirm, it sounds like you are looking for a sum of solved tickets that are assigned to a specific group, even if they were marked as solved by someone outside of that group? Is that correct?
Looking forward to your reply!
Hello Taylor Bowser, yes that is correct! We're trying to figure out how often tickets are being assigned to a group and someone outside of that group is solving. This would help us identify how often tickets are being assigned to this group incorrectly or how long this group spent on tickets without being the one to solve it. Thank you!
Where do we find an index of Group IDs so we can match them to their names?
Hello James,
Group names and Ids can be obtained through our API's, List Groups endpoint to obtain those references.
Alternatively, through Manage > People in Support, the Groups tab will have each group's name listed. Here, the group name itself is a hyperlink with the group's ID encoded at the end of the URL.
Hi,
Which time filter should I use to see the amount of group assignments for a specific date?
Hi Marcos -
To limit the results to changes on a specific date, you'd want to add a filter using Ticket updated - date
For more information on using filters, see Working with query filters
Please sign in to leave a comment.