In this Explore recipe, you'll learn how to use the更新历史记录dataset to find thefirstagent 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.
You can also modify this report to find the first assigned group instead of agent.
This recipe contains the following topics:
What you'll need
Skill level:Intermediate
Time required:20 minutes
- Zendesk Explore Professional or Enterprise
- Editor or Admin permissions (seeGiving 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, clickNew report.
- On theSelect a datasetpage, clickSupport>Support -更新历史记录, then clickStart report.
- In the Calculations menu (
), clickStandard calculated attribute. - On theStandard calculated attributepage, give your attribute a name likeFirst assignee name.
- In theFormulafield, enter or paste the following formula:
如果([常es - 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 articleto help you enter Explore formulas in your language. - From theComputed fromdropdown list, chooseTicket ID.
- When you are finished, clickSave.
- Repeat steps 4 to 8 to create a second calculated attribute named当前的受让人filterand the following formula:
IF [Update ticket assignee] = [Assignee name] THEN "true" ELSE "false" ENDIF
- Now, you'll construct the report. In theMetricspanel, clickAdd.
- From the list of metrics, chooseTickets>Tickets updated, then clickApply.
- In theRowspanel, clickAdd.
- From the list of attributes, chooseCalculated attributes>First assignee name, then clickApply.
- In theFilterspanel, clickAdd.
- From the list of attributes, chooseCalculated attributes>当前的受让人filter, then clickApply.
- Click the当前的受让人filter you just added. On theExcludetab, 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, addTickets>Ticket IDto theRows面板。
Modifying the report to find the first assigned group
To find the first assigned group on a ticket, rather than the first assigned agent, follow the steps above but substitute the following formulas:
- In step 6, use this formula instead:
如果([常es - Field name]="group_id" AND [Changes - Previous value]=NULL AND [Changes - New value]!="0" AND [Changes - New value]!=NULL) THEN [Update ticket group] ENDIF
- In step 9, use this formula instead:
IF [Update ticket group] = [Ticket group] THEN "true" ELSE "false" ENDIF
18 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 theUpdate Ticket Assigneeattribute. 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 withIvy 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:
如果([常es - 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-
它可能会有这一切在一个曲ery, 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.
I utilized this Standard Calculated Metric a while ago to help me identify tickets moving from group to group. I now have a need for it to try to find some other information. It seems that the formula is now failing.
If I run the original queries/reports that I initially used this metric in they seem to run fine. However, when I now select it from the list of metrics under columns or rows, I get the error below.
Can anybody shed some light on this?
You are probably in the wrong dataset, make sure you are in the Updates History dataset and the formula should work.
Let me know if you have any further questions.
Is it possible to get the same information, first assignee name and hopefully group in the ticket and SLA dataset?
Unfortunately not, as the changes - field name attribute only exists in the Updates History dataset.
Best regards,
Hi team,
Is it possible to find all assignees for a ticket?
or
get first assignee for a tickets in one column and get second assignee for a tickets in second column.
I am New to Zendesk Can any one help me with this?
Thanks,
Omkar
HiRachayeeta Dutta, it is possible but it will require some work. Here's an example of how to do this.
1. Create a new report under theSupport - Updates historydataset
2. Add the attribute [Changes - Field name] to Filters, and select "assignee_id"
3.1. Create astandard calculated attribute, let's name it "Ticket Assignee • Previous", for example:
⚠️Don't forget to replace "yoursubdomain" with your actual Zendesk account subdomain.
3.2. Create a second standard calculated attribute, "Ticket Assignee • New":
4. Add theAssignee reassignmentsmetric to the report
5. Add the two new standard attributes to the report, exclude "NULL" values on both:
The only downside is thatthis will return the user ID instead of the name, so we'd have to edit the previous standard attributes in order to obtain the agent's name.
This will be a manual task, however, as we'll have to use aSWITCH function. Exemplifying for the "Ticket Assignee • Previous" attribute:
⚠️ You should add a "CASE" value for each agent in your account. This makes the whole report more difficult to maintain, of course, depending on how many agents you have.
Result:
In the example image above, we can see that there are CASE conditions for two agent IDs, but we'd need to edit our two attributes and add more CASE conditions for the other user IDs.
Hope this helps!
Thanks for the responsePedro Rodrigues. it helped a lot.
can you help me with one one more thing like how to just get first assignee name of a ticket and name of last agent who have updated the ticket .
apologies for the late response and again thanks for the response .
Omkar Salyan
HiRachayeeta Dutta, you can follow the steps shared on this page to report on first assignee. As for the last assignee, you can try the following standard calculated attribute (also in the Updates history dataset):
Hope this helps!
I'm using the "Bad initial satisfaction ratings" metric to find negative C-SAT data based on the first assignee and this is not working for us. All of the numbers are associated with a blank value under "First Assignee Name".
If you're using the Bad initial satisfaction ratings in your report and you need to see which agent was assigned at that time of the rating, you can try slicing the data by the native Update ticket assignee attribute instead of the custom one discussed above. This should show the assignee at the time the bad rating was added to the ticket.
Hi,
The first reply metric is attributed to the current assignee / group even if it wasn't them who did that first answer (we actually got a separate group that triages most tickets).
Is there a way to see how long it took to send the first reply and attribute it to the agent who actually wrote that first reply?
Thanks
This may be achievable through the Updates history dataset. You can try building a custom attribute using the earliest date functions to get the timestamp of the first agent reply – seeWorking with earliest and latest date functions– and then use the Updater name attribute to see the agent who performed the update.
Hey all,
How can I see the first reply time - business hours by the first assignee agent?
请sign into leave a comment.