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
- 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
- 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.
25 Comments
In step 1 it said: "Select Support: Ticket Events."
There's no such dataset
Hey Francesco,
This article has been updated with the correct dataset which is Support: Ticket Updates.
Let me know if you run into any issues locating this dataset.
Cheers!
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 - Community Manager 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 - Community Manager 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.
On Wednesday, April 8, from 8:00am-10:00pm CDT, we'll have a special guest team of experts on hand to answer your questions about Explore: Reporting & Insights Migration in your Zendesk Explore instance for an AMA-style conversation!
Just click the "new post" button and write up your question. Be sure to be as detailed as possible. The best questions include:
Questions posted to this topic in advance of the start time will be answered first thing on the 8th, so if you've got something you want our experts to look at, post it today!
Please sign in to leave a comment.