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.
40 Comments
Hey Brett,
Two questions:
-Is there a way in which I could calculate the time it takes for a case to move from one group to another?
-Is there a way in which I could calculate the time between a case is created and is labeled with a particular label?
Cheers!
Hey Hernan,
If I'm understanding you correctly, you'll want to take a look a look at the following recipe for tracking reporting on the duration of fields: Explore recipe: Reporting on the duration of fields
The above link should help with both your questions but will most likely require some adjustments to your custom metric.
This report is too large to load. Wild that this is not handled yet. Without speed improvements, these recipes are almost impossible to use.
Could this be modified (and if so, how could it be modified) to show a percentage of solved tickets that were never escalated to another Zendesk Group?
Hey Randy,
You can use the above steps as a good starting point and instead set [Changes - New value] to NULL since there won't be a change in the group_id.
Hope that helps!
Hey there!
On our Zendesk, instead of choosing a group to assign a ticket to, the clients are seeing a custom ticket field with categories (in the background, each categories is linked to a group).
I wanted to make the same report as above, but instead of group_id, I was trying to use category_id. The formula is not showing an error, but the report I get are all down to 0, as if ticket's category had never been changed, or I know this is not the case.
Is category_id a wrong name when referring to a custom field? I also tried ticket_fields/ID (with the id of the customized ticket field and the formula is not showing errors there, but the reports are wrong).
Has someone done this before?
Thank you!
Cheers,
Marion
Marion Evrard
Looking at the issue, replacing group _id with category_id is causing your problem. You will need to replace it with the exact name of your custom field, which is case sensitive, so make sure you are typing everything as you have previously set up the custom field when creating the report.
Best regards.
Hey Devan!
Thank you for this, I tried using Category as it is how we called our custom field so I have this:
IF ([Changes - Field type]= "Category_id" AND
[Changes - Previous value] = "360005181497" AND
[Changes - New value] = "360005181477") THEN
[Update ID]
ENDIF
But nothing is happening.
Could it be somewhere else?
Should I create a ticket for this?
Thank you!
Cheers,
Marion
Marion Evrard,
I've gone ahead and created a ticket on your behalf, so one of our Explore experts can troubleshoot this further for you. You should expect an email followed by a response shortly.
Best regards.
I have a report table in Insights with the number of group reassignments by Ticket Group (historic) on the Left and Ticket Group on the top. Reporting group reassignments using the group_id field changes new and previous values will not be useful. How can I create this report using the group names?
Hello Janet Hunter,
So I've created a report that should replicate your previous report from Insights. Ticket group(historic) is now update ticket group, so with this change you should still be able to create your old report now in Explore.
Best regards.
Devan,
Thanks for the guidance, but the numbers don't match what I get in my Insight report. According to the attribute description for Update Ticket Group, the value returned is the value it was updated to, not the value before the update, so it isn't the previous value.
I have also tried using the Group Reassignments metric (modified to include null values) and still can't match my Insights numbers even with the group_id for new and previous values.
Comparison is difficult because the export of the table in Explore comes out in a list format instead of a table. Shouldn't the export format be a table?
Is it possible to have this measure tickets assigns across multiple groups. For example I want to know the # of tickets from support to sales OR marketing?
Would something like this work?
Beau,
The dashboard reports are supposed to be flexible enough to be used by all our groups relying on them to filter based on the groups they are interested in. With 35 groups receiving tickets from multiple other groups, trying to hard code the logic isn't really workable. My other concern is that even using the group_id fields and not knowing what the group names are, my numbers are not matching the insights report. . .
Janet
Hi Janet,
My comment was unrelated to yours, I was asking a question for my own use case.
Beau, I think your metric would work. You can also just create a query using the "Changes" attributes and filter to the groups you want to see in a particular query. That way, you don't have a build out metrics for all the combinations of groups you might need to see.
Hey Beau,
Looks like you may have a syntax error as well in your code. Can you try the following:
Let me know if that doesn't work!
Brett Bowser I'm trying to do the same thing as Beau, but I have 10 groups as options. Does this mean I keep adding parentheses for each value like this:
IF ((((((((((([Changes - Field name] = "group_id" AND
[Changes - Previous value] = "41899508" AND
[Changes - New value] = "36000xxx") OR
[Changes - New value] = "36000xxx")) OR
[Changes - New value] = "36000xxx"))) OR
[Changes - New value] = "42584xxx")))) OR
[Changes - New value] = "36000xxx"))))) OR
[Changes - New value] = "36000xxx")))))) OR
[Changes - New value] = "36000xxx"))))))) OR
[Changes - New value] = "36000xxx")))))))) OR
[Changes - New value] = "36000xxx"))))))))) OR
[Changes - New value] = "36000xxx")))))))))) THEN
[Update ID]
ENDIF
Because that isn't working. Do I have too many groups for this to work?
Brett Bowser just kidding. I futzed around with it and this formula worked:
IF ([Changes - Field name] = "group_id" AND
[Changes - Previous value] = "4189xxx" AND
[Changes - New value] = "36000xxx" OR
[Changes - New value] = "36000xxx" OR
[Changes - New value] = "36000xxx" OR
[Changes - New value] = "4258xxx" OR
[Changes - New value] = "36000xxx" OR
[Changes - New value] = "36000xxx" OR
[Changes - New value] = "36000xxx" OR
[Changes - New value] = "36000xxx" OR
[Changes - New value] = "36000xxx" OR
[Changes - New value] = "36000xxx") THEN
[Update ID]
ENDIF
Glad you were able to get this working Melissa :)
Hello all,
I was able to successfully create the metric, however the business is asking to show ticket attributes of those tickets that were reassigned.
For example the Ticket number, Assignee, Ticket Subject. Adding those to the Rows section is giving me an error.
Thanks in advanced!
Peng
Hello Peng Li,
By what you described, it sounds lie you might be hitting some data limit caps. This can be caused by exceeding the row limit or your reporting timing out due to size. Let us know if this isn't the case and the exact error you are receiving, and we'd be happy to troubleshoot this further.
Best regards.
Is there any way to track how many times an agent reassigns a ticket to someone else?
Hello Melissa Norton,
There is an app that will let you do this relatively seamlessly called Agent Activity Tags. It is easy to configure and would allow you to track reassignment. Let us know if you need help converting this info a report in Explore.
Best regards.
Hi! I was trying to do this for ticket forms to track how often they are changed. Would the logic still be the same?
IF ([Changes - Field name] = "ticket_forms" AND
[Changes - Previous value] = "360002674954" AND
[Changes - New value] = "360003074073") THEN
[Update ID]
ENDIF
Or is this just not possible to track?
Hey there -- I'm trying to see how many tickets were assigned to a group per day. Is there a time attribute that would work for this? If I'm understanding correctly, last assigned date will show me when it was assigned to a specific agent, not to the group overall. I need to see how many were moved groups only per day.
Is it possible to configure drillthrough for this query? At the moment, the drillthrough pulls all ticket updates, rather than just the group reassignments. I tried adding a filter on Update channel = Group change, but most of these group changes are logged under Web form or Web service (probably because we use macros for reassigning tickets). As well as knowing how many assignments are being made, we'd like to then be able to access those specific tickets so we can do checks on whether agents are escalating tickets appropriately. Thanks.
I'm trying to make a query for when one group (Group A) assigns to either of two groups (Group 1 or Group 2) but the result seems different from when I do standalone queries for each (Group A to Group 1, Group A to Group 2).
Group A to Group 1
Group A to Group 2
Group A to Group 1 or 2
Here's the result I'm getting:
Can anyone kindly point me to the right direction? Thanks in advance!
Hello everyone,
I have 2 issues you may help me with.
1. Is there a reference for fields names somewhere ? As used in
If I want to use something else than "Group ID", it's sometimes hard to find the proper syntax (for example you need to use "status" and not "ticket_status"). What's the rule here ?
2. I'm trying to track the time spent in a group, but without the time the ticket spent in Pending status.
Any ideas ?
I built this query to understand whats' measured and when, and added what I'd like to track. I tried different things, but without much success...
The hard part is to base the calculated metrics on 2 different fields that can change (group and status).
I also tried to calculate the time between 2 updates (without any conditions) to see if I could later filter on status and ticket group changes, but no success here either...
@Jean-Sébastien Catier. I usually grab the field name from the "Changes - Field Name" field. That attribute has the field names that you need for your metrics.
Please sign in to leave a comment.