In this Explore recipe, you'll learn how to create a query that reports the number of times your agents were assigned to a ticket, even if they are no longer the assignee on the ticket.
Note: If a user is assigned to a ticket more than once, it will count the assignment twice (for example if a previously assigned ticket is assigned again to the same agent).
What you'll need
Skill level: Moderate
Time Required: 25 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 you'll create standard calculated metrics to calculate initial assignments and reassignments. Click Calculations (
), and then Standard calculated metric.
- On the Standard calculated metric page, enter a name for your metric, in this case New ticket assigns.
- In the Formula field, enter or paste the New ticket assigns formula:
IF ([Changes - Field name] = "assignee_id"
AND [Changes - Previous value]=NULL
AND [Changes - New value]!="0")
THEN [Update ID]
ENDIFTip: If you're working in a language other than English, read this article to help you enter Explore formulas in your language. - Enable Compute separately.
- When you are finished, click Save.
-
Repeat steps 4 to 8 to create a second calculated metric named Assignee reassignments (custom). Use the following formula for this metric.
IF ([Changes - Field name] = "assignee_id"
Your metrics will look like the following screenshots:
AND [Changes - Previous value]!=NULL
AND [Changes - New value]!="0")
THEN [Update ID]
ENDIF
- In the Metrics panel, click Add.
- From the list of metrics, choose Calculated metrics > New ticket assigns and Calculated metrics > Assignee reassignments (custom), then click Apply.
- Ensure the metric aggregators for your custom metrics are set to COUNT. For more information, see Choosing metric aggregators.
- In the Rows panel, click Add.
- From the list of attributes, choose Ticket field update values > Update ticket assignee, then click Apply.
- To get the sum of the two metrics, click Result manipulation (
), then click Result metric calculation.
- On the Result metric calculation page, click Add a new metric.
- Enter the name of your metric, in this case Total assignments.
- Enter or paste the formula below. You must select your metrics and functions from the drop-down next to Insert.
COUNT(New ticket assigns)+COUNT(Assignee reassignments (custom))
- Click + Add to add the calculation.
Your query is complete! See the screenshot below for an example.
9 Comments
Hello,
I do have an strange behavior when I am doing that.
I want to know for one group from where are coming the ticket, which group have assign a ticket to this group and count. Only the ongoing ticket (not the close and solved)
So I have done basicly that :
IF ([Changes - Field name] = "group_id"
AND [Changes - Previous value]!=NULL
AND [Changes - New value] = "Id of me group" )
THEN [Update ID]
ENDIF
For me, all ticket with group ID not Null change to my group ID should do +1 but I only have 0 in my COUNT. What I have miss ?
I try a different way
And I have add a filter on the group I want to focus.
Now I have count but in some case my COUNT staid at 0 and other case it is working.
Looking the ticket, both have been escalated using the same way so I do not understand.
So in my count I am loosing escalated ticket from group.
What I have miss ?
Thank you for your lihgt,
Best regard
Hello,
yes except the ticket coming to this group can come from different group
thank you
JEL
Hello
Any help ?
Thank you
How can we put a date limit on this, I only want to see the assignments for a certain time period?
Hi Tina,
You should be able to add a Time - Ticket update attribute to filter by a certain time period.
Check out Editing dates and date ranges for more information on filtering with date attributes.
Thanks!
Hey Jessica Blake
I tried recreating this exactly, but I see data discrepancies.
We have external inboxes which are associated to each user (i.e. bobby.koch@zendesk.com and jessica.blake@zendesk.com) and the goal is to see how many emails were assigned/received at each email address over a date period.
When I try to use the default, my own custom formulas, and yours, I see more data than I should (it is pretty close, but off by a couple).
I tried to add the channel "email" to this query, but then I lose the majority of the data (shows 0s for most).
Is there any way to accomplish what I am looking for here? Would really appreciate any help.
Thank you for that information, Jean-Edouard,
I'm going to go ahead and pull this issue into a ticket so I can get more account-specific details to troubleshoot this issue. You should see an email shortly with the ticket information.
Hello Jean-Edouard Leblond,
For your formula, have you looked up the group id of the group that you're tracking to use in your formula, as is explained in this recipe: Tracking ticket assigns across groups?
Hello Bobby Koch,
So let's see if we can troubleshoot this report for you. If you could share a screenshot of how you set up this report we can being seeing where the issue is coming from.
Best regards.
Please sign in to leave a comment.