Zendesk Explore - CSAT and ticket assignee report

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? Here’s how to make this clear in Zendesk Explore.

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, it’s crucial to avoid having ratings attributed to the wrong support agent in your reports.

This means that looking at the last assignee might lead to wrong conclusions. The closest we’ll ever get to a just CSAT attribution is to see who was assigned to the ticket when the end-user rated it.

Although closer to the truth, please note that this may never be entirely accurate. 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 reassign the ticket to a colleague. This is what my fictional support agent, Space Pineapple, usually does:

Support agent Space Pineapple got a bad rating, so they went ahead and reassigned the ticket to their friendly coworker, Space Lemon. Too bad I caught it!

Querying the ticket updates dataset

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 ticket updates dataset.

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 "-"

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:

Gotcha, Space Pineapple!

Multiple CSAT ratings, same ticket ID

Having identified who was assigned to the ticket when CSAT was given, another situation came up: for some reason, the customer decided to change their rating to Good.

This is fine… But being the sneaky little villain that he is, agent Space Pineapple reassigned the ticket back to himself.

Space Pineapple strikes again

Good thing he isn’t aware that we are already acquainted with his mischievous M.O.. This is how our 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:

Tickets 61 and 73 have multiple CSAT updates.

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:

Reordered table with Update – Timestamp

At this point you can already imagine many ways to play around with this kind of data and possible presentations.

Everything depends on what we want to see.

What are we trying to C(SAT)?

Pun intended… There are many ways to present these values. Like all things data-related, what’s important is the question or need.

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:

Zendesk Explore :: CSAT example (bar chart)

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, for each assignee (at the moment the CSAT was given), this chart shows us the following:

  • 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 "?"

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. Here’s hoping this was of some use to you. If I missed anything, or if something is unclear, please feel free to let me know in the comment section below.

Header photo: Ioana Cristiana via Unsplash

Need Zendesk assistance?

At Opservator.com 🔗 we provide audit, implementation, and optimization services for multiple Zendesk products and account sizes.

2 thoughts on “Zendesk Explore and CSAT: reporting on the correct Assignee”

  1. I receive this back

    The set of calculations isn’t valid. The calculated attribute “csatenduserrating-e67bc6a22c” references “Changes – Field name” which is as an unknown attribute. Adjust the calculation and try again. (Error code: a27aaa32)

    1. Hi Matt, Zendesk recently renamed the “Ticket updates” dataset to “Updates history”, so please make sure you’re creating the query under that dataset (https://i.imgur.com/iseT0Br.png). I’ve just double-checked my queries and everything looks fine. If you’re on the correct dataset and still having problems, feel free to drop me an email with more details, I’ll be happy to help you troubleshoot.

Leave a Reply

Your email address will not be published. Required fields are marked *