In this Explore recipe, you'll learn how to use the Updates history dataset to find the first agent a ticket was assigned to. You'll also create a filter that can be used to exclude results where the first assignee is the same as the current assignee.
What you'll need
Skill level: Intermediate
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 report
This report returns the first assignee for each ticket in your instance.
To create the report
- In Explore, click the reports (
) icon.
- In the Reports library, click New report.
- On the Select a dataset page, click Support > Support - Updates history, then click Start report.
- In the Calculations menu (
), click Standard calculated attribute.
- On the Standard calculated attribute page, give your attribute a name like First assignee name.
- In the Formula field, enter or paste the following formula:
IF ([Changes - Field name]="assignee_id" AND [Changes - Previous value]=NULL AND [Changes - New value]!="0" AND [Changes - New value]!=NULL) THEN [Update ticket assignee] ENDIF
Tip: If you're working in a language other than English, read this article to help you enter Explore formulas in your language. - From the Computed from dropdown list, choose Ticket ID.
- When you are finished, click Save.
- Repeat steps 4 to 8 to create a second calculated attribute named Current assignee filter and the following formula:
IF [Update ticket assignee] = [Assignee name] THEN "true" ELSE "false" ENDIF
- Now, you'll construct the report. In the Metrics panel, click Add.
- From the list of metrics, choose Tickets > Tickets updated, then click Apply.
- In the Rows panel, click Add.
- From the list of attributes, choose Calculated attributes > First assignee name, then click Apply.
- In the Filters panel, click Add.
- From the list of attributes, choose Calculated attributes > Current assignee filter, then click Apply.
- Click the Current assignee filter you just added. On the Exclude tab, exclude one of the following values:
- True: If you want to exclude tickets where the current ticket assignee is the same as the first tickets assignee.
- False: If you want to exclude tickets where the current ticket assignee is NOT the same as the first tickets assignee.
Explore displays the finished table showing agents who were both the first and the current assignee for each ticket and the number of tickets for which that was the case. If you want to see the individual ticket IDs for these, add Tickets > Ticket ID to the Rows panel.
Tip: The Updates history dataset contains a large amount of information related to your tickets. If your report is taking too long to process, consider adding another filter to narrow the results. For example, you could add the Ticket created - Date filter to narrow the date range being reported.
5 Comments
Hi team,
Is it possible to find all assignees for a ticket?
Thanks,
Ben
Hey Ben,
You should be able to report on this using the Ticket Updates dataset along with the Update Ticket Assignee attribute. This recipe may help: Explore recipe: Total number of assignments
Hi team,
So our team need the data from the tickets, specifically the first assignee of the tickets to make it easier to see an overview of ticket processing .
Our team using Chatbot named Ivy and if using this query, it would trigger all ticket from live chat with Ivy as first assignee. could we exclude Ivy and utilize the second agent as first assignee?
is there any logic that we can utilized to recognize our second assignee agent to sort out the ticket? some kind of logic that work like this:
Could we utilize that logic?
Thanks,
Kuka
Thanks for reaching out regarding this.
I am not able to test this out as I would not have the same chatbot integration as you, and it looks like you are using a 3rd party bot that actually falls as an assignee. But I have some ideas on this.
First, we may need to actually separate the reporting on live chat tickets from the rest of the tickets. You can do that by filtering the report by ticket channel. Then when creating the report for tickets created from live chat, have you tried editing the formula so that instead of the assignee id being changed from NULL, it is changed from Ivy? Something like this:
IF ([Changes - Field name]="assignee_id" AND [Changes - Previous value]="Ivy Assignee ID"
AND [Changes - New value]!="0" AND [Changes - New value]!=NULL)
THEN [Update ticket assignee]
ENDIF
Of course, you would change the "Ivy Assignee ID" to the actual assignee ID. Check here for more information: https://support.zendesk.com/hc/en-us/articles/4408823393306-How-can-I-locate-an-agent-ID-in-Support-
It may be possible to have this all in a single query, and having a custom attribute using the logic that you provided, but that extent of custom coding would be out of our scope.
Hope this helps! Cheers!
Hello -
I followed the steps here, but I'm not getting 100% the results I'm looking for in in my query results.
I built a custom metric to show me the the number of tickets that are moved from a specific group into another group like this:
In this case, it seems like the "First Assignee" attribute can't be bound to this in a table row - I would like to visually show who the first assignee on the ticket was prior to the ticket changing groups.
In my example here, I isolated to a single ticket that should be included:
So this is working for the "Tickets updated" metric, but I would like to have this also work for my custom "Upgrades" metric.
Please sign in to leave a comment.