Zendesk Explore and CSAT: reporting on the correct Assignee

A ticket’s last assignee might be same the person who was assigned to a ticket when the requester rated it. But who gets the CSAT credit when multiple agents were assigned to a ticket? This post will show you how to answer this question in Zendesk Explore.

The challenge

A scenario where the same person handles the ticket from start to finish may be common in small businesses, but that may not be the case for large teams with dozens of agents with multiple shifts, and thousands of tickets coming in on a daily basis.

Therefore, looking at the current/latest assignee might lead to the wrong conclusions. This means it’s crucial to have ratings attributed to the correct support agent in your reports.

Heads-up! Assignee ≠ responsible

The closest we’ll ever get to a more just CSAT attribution is to see who was assigned to the ticket when the end-user rated it. Although closer to the truth, this may never be entirely accurate: just because we found the assignee at the time the ticket was rated, it does not mean that person was the main responsible for the positive/negative CSAT.

The following situation could happen, for example:

• Agent John (who’s really nice to customers) handled and solved the ticket
• While CSAT was still unoffered or offered, agent Hans (who’s a very shady fellow) reassigned it to himself
• The requester responds to the CSAT survey and rates the ticket

In this case, the last assignee is Hans, and the assignee at the time the CSAT rating was given is also Hans. If Hans does his evil deed after CSAT is given, however, our method will be closer to the truth (because Hans will still be the last assignee, but John will be the assignee at the time CSAT was given).

There is also the chance that an agent receives a bad rating and reassigns the ticket to a colleague. This is what our fictional support agent, “Space Pineapple” (because why not?), did:

Querying the ticket updates dataset in Zendesk Explore

Because we need to play around with ticket events in order to find the assignee at the time of the CSAT event, we’ll need to create our query in the Updates History ticket dataset in Zendesk Explore.

In this example, I want to create a table where I see several things: ticket ID, who was assigned when the ticket got the CSAT rating and who is assigned now.

For starters, we’ll create a custom, standard calculated attribute which we’ll name ‘CSAT :: End-user rating’ and that will help us identify valid CSAT cases. Here’s my formula:

IF [Updater role] = "End-user" 
AND [Changes - Field name] = "satisfaction_score"
AND [Changes - Previous value] != [Changes - New value]
THEN "Valid"
ELSE "-"
ENDIF

We then add this attribute to our query filters and select ‘Valid’.

We’ll create a table with the following attributes (in rows):

• Ticket ID
• Update ticket assignee
• Assignee name
• Ticket satisfaction rating
• Ticket satisfaction reason
• Ticket satisfaction comment

Because we need at least one metric, I’ll add COUNT(Satisfaction updates).

The resulting table:

Multiple CSAT ratings, same ticket ID

Another situation comes up: the customer decided to change their rating to Good. Being the sneaky little villain that he is, agent Space Pineapple reassigned the ticket back to himself:

Good thing he isn’t aware that we are already acquainted with his mischievous M.O… This is how our Zendesk Explore table would look like after this unexpected update:

Although we can now see two different CSAT updates, the CSAT attribute shows ‘Good’ on both because that is the current value. Same goes for Ticket satisfaction reason and CSAT comment. This is because the Zendesk dataset doesn’t currently store these values during the update. Consequently, these three fields are now secondary (regarding our table).

Whose CSAT is it, anyway?

Let’s make a few adjustments to our table by adding the same ‘Changes’ attributes we used in the custom attribute formula, and seeing more tickets where the attribute is valid:

It’s looking more complete but still rather confusing: by looking at ticket 73, we confirm the first update row (from good to bad CSAT) is actually an event that follows the second update row (from offered to good). This means that we sent the survey link, the requester rated as a positive experience, then changed the rating to negative. In any case, it’s still hard to read.

One way to have things in order is by adding the Update – Timestamp attribute to the table. In our example, ticket #73 now shows its CSAT events in the correct order:

At this point you can already imagine many ways to play around with this kind of data and possible presentations. Everything depends on what you need to see.

What are we trying to CSAT?

Pun intended… There are many ways to present data in Zendesk Explore. And like most things data-related, what’s important is the question we need answered: what do we want to see?

Focus on agents

Here’s a different visualization example more focused on agents – we’ve Update ticket assignee to Explosions, Changes – Previous value to columns and Changes – New value to rows:

The primary separation of this bar chart is the assignee value (by using the Explosions feature). This kind of chart would answer a need such as: ‘I want to see, for each agent, how many ratings they received, separated by rating scenario.’

Please note that the metric is not showing the number of tickets, but the number of ratings. Therefore, this chart shows us the following for each assignee (at the moment the CSAT was given):

• Pedro had:
  • 1 ratings from no CSAT offered to good
  • 1 rating from good to bad
  • 2 ratings from offered to bad
  • 3 ratings from offered to good
• Space Lemon had:
  • 1 rating from bad to good (that’s ticket #61)
• Space Pineapple had:
  • 1 rating from offered to bad (that’s also ticket #61!)

Extra! A simpler attribute

We can improve our data presentation by creating another standard calculated attribute to sum up the multiple CSAT scenarios.

Let’s name it CSAT Scenario:

IF (([Changes - Previous value] = NULL OR [Changes - Previous value] = "offered")
AND [Changes - New value] = "good")
THEN "1. None to Good"
ELIF (([Changes - Previous value] = NULL OR [Changes - Previous value] = "offered")
AND [Changes - New value] = "bad")
THEN "2. None to Bad"
ELIF ([Changes - Previous value] = "bad" AND [Changes - New value] = "good")
THEN "3. Bad to Good"
ELIF ([Changes - Previous value] = "good" AND [Changes - New value] = "bad")
THEN "4. Good to Bad"
ELSE "?"
ENDIF

This will allow us to have all scenarios under a single attribute. Let’s validate:

So now we can play around with this attribute and the first agent-driven report, globally, or both.

That’s it, happy Zendeskin’ 🙌

Featured image: Didssph via Unsplash 🔗