Deep dive into ticket reassignment with Explore reports

TL;DR

This post utilises many Explore features to get a detailed report on ticket reassignment. The report will include:

Before and after assignee changes for each ticket.
Workaround for data limitation around previous assignee name.
Filter based on the agent who performed the reassignment.
An example on how the report can be cloned and adapted for different use cases.

We will take an iterative approach with increasing complexity to clarify the logic behind every step. It will only focus on assignee rather than group assignment in order to manage the scope.

What you'll need

Zendesk Explore Professional or Enterprise
Editor or Admin permissions (see Giving agents access to Explore)
Ticket data in Zendesk Support

Creating the report

In Explore, click the reports icon in the left navbar.
In the Reports library, click New report.
On the Select a dataset page, click Support > Support - Updates history, then click Start report.
Enter a user-friendly name for the report (e.g. Ticket reassignment)
In the Metrics panel, click Add.
From the list of metrics, choose Updates > Updates, then click Apply. This metric will display the number of times all tickets have been updated by anyone.
In the Filters panel, click Add.
From the list of attributes, choose Ticket field changes > Changes - Field name, Ticket field changes > Changes - Previous value, and Time - Ticket update > Update - Date filters, then click Apply.
Click the Changes - Field name filter you just added. On the Selected tab, select assignee_id. This filter will narrow down ticket updates to those that include assignee change.
Click the Changes - Previous value filter. On the Excluded tab, select NULL. This filter will exclude new tickets' first assignment.
Click the Update - Date filter. Click Edit date ranges. Select a timeframe (e.g. last month). You will likely override this filter with a dashboard filter at a later stage, but it is necessary to limit the dataset while creating the report to avoid long loading times or even failure to render data.
Click Save.

The report now renders a KPI visualisation of the number of ticket reassignment in the last month. This is different from the metric Assignee reassignment since the later excludes tickets re-assigned to a group.

Data break down

The second iteration of the report will list the old and new assignees in those updates. The attribute names are intuitive and reflect exactly that:

In the Rows panel, click Add.
From the list of attributes, choose Ticket > Ticket ID, Ticket field changes > Changes - Previous value and Ticket field changes > Changes - New value, then click Apply.

Now the visualization changes into a table and breaks down the number of updates (assignee changes) for each ticket updated within the specified time range by all its reassignments combinations.

The ID "0" indicates no assignee. However this still does not include any new tickets. So, tickets that are reassigned from "0" must have gone through a round of assignment and un-assignment.

Improving readability

The third iteration of the report will try to convert those IDs to legible data. Afterall, we would rather know the agent name.

Fortunately, for the new assignee, there is an attribute for the name:

In the Rows panel, remove Changes - New value that you added earlier, then click Add.
From the list of attributes, choose Ticket field update values > Update ticket assignee, then click Apply.

Unfortunately, for the old assignee, the name is not saved in the dataset. However, we can try to cross-reference it. This will work in SOME cases. For example, it is often that the old assignee will perform the reassignment themselves. In this case, we can cross-reference the old assignee_id with the updater_id and display the updater name, which is part of the dataset, whenever there is a match. We can also cross-reference the old assignee_id with the final assignee_id of the ticket, in case the ticket is reassigned back to them. If we get no match still, the least we can do is make the id a hyperlink to the agent profile.

In the Calculations menu, click Standard calculated attribute.
On the Standard calculated attribute page, give your attribute a name such as Unassigned agent.
In the Computed from field, select Update ID.

In the Formula field, enter the following formula:

IF ([Changes - Field name]="assignee_id"
AND [Changes - Previous value]="0")
THEN "Unassigned"
ELIF ([Changes - Field name]="assignee_id"
AND [Changes - Previous value]!=NULL
AND [Changes - Previous value]=STRING([Updater ID]))
THEN [Updater name]
ELIF([Changes - Field name]="assignee_id"
AND [Changes - Previous value]=STRING([Assignee ID]))
THEN STRING([Assignee ID])
ELIF([Changes - Field name]="assignee_id"
AND [Changes - Previous value]!=NULL
AND [Changes - Previous value]!=STRING([Updater ID])
AND [Changes - Previous value]!=STRING([Assignee ID]))
THEN LINK("/agent/users/" + [Changes - Previous value],[Changes - Previous value])
ENDIF

Add the calculated attribute to the report:

In the Rows panel, remove Changes - Old value, then click Add.
From the list of attributes, choose Calculated attributes > Unassigned agent, then click Apply.

Enable hyperlinks in your table:

From the Visualisation type menu in the right navbar, Select Table.
From the Chart configuration menu in the right navbar, Select Chart.
Scroll down to Text interpretation and select HTML.

Add more context to every update:

In the Rows panel, click Add.
From the list of attributes, choose Updater > Updater name, then click Apply.

Now the table shows the new assignee name, some of the old assignee names, and those without names have an ID that is a hyperlink to their profile. Not a 100% solution, but definitely an improvement. You can also see the name of the agent that performed every update.

Create a new filter

The fourth iteration of the report will add a filter to manage large number of updates in your report and group them in logical subsets. You may notice a pattern where tickets are either reassigned by the original assignee, taken by the new assignee, reassigned by the system (trigger or automation), or by another agent. So we will create a filter that does exactly that. This could be helpful while trying to understand why a ticket was reassigned.

In the Calculations menu, click Standard calculated attribute.
On the Standard calculated attribute page, give your attribute a name like Assignee updater's relationship to the ticket (not my best title).
In the Computed from field, select Update ID.

In the Formula field, enter the following formula:

IF ([Changes - Field name]="assignee_id"
AND [Updater ID]=NULL)
THEN "System"
ELIF ([Changes - Field name]="assignee_id"
AND STRING([Updater ID])=[Changes - Previous value])
THEN "Previous assignee"
ELIF ([Changes - Field name]="assignee_id"
AND STRING([Updater ID])=[Changes - New value])
THEN "New assignee"
ELIF ([Changes - Field name]="assignee_id"
AND [Updater ID]!=NULL
AND STRING([Updater ID])!=[Changes - Previous value]
AND STRING([Updater ID])!=[Changes - New value])
THEN "Someone else"
ENDIF

In the Filters panel, click Add. From the list of attributes, choose Calculated attributes > Assignee updater's relationship to the ticket, then click Apply.

You can use the new filter to display a subset in the report itself, create a variation of the report, or include it as a dashboard filter to give viewers the freedom to select the desired subset.

Clone and tweak the report

It's easy to pivot to various use cases using the report we just built. Let's say you wish to monitor tickets where agents unassign themselves and put it back on queue.

Save the last iteration of the report, then from the save menu, select Save as new.

Give the report a descriptive name such as Tickets unassigned back to queue by assignee.
In the Filters panel, click Add. From the list of attributes, choose Ticket field changes > Changes - New value, then click Apply.
Click the Changes - New value filter you just added. On the Selected tab, select 0. This filter will only display tickets where the assignee is removed (assigned to a group).
Click the Assignee updater's relationship to the ticket filter. On the Selected tab, select Previous assignee.
In the Rows panel, remove Update ticket assignee since it will always be empty and Updater name since it will always be equal to the Unassigned agent.
Save the report.

Note that the name of the unassigned agent will always be displayed in this report.

Do you have any improvements to these reports? Questions? Requests for specific use case? Let me know in the comments.