How to report on agent updates in Explore (when Agents have been downgraded to End users)?

The issue

The standard recommended approach when an agent leaves your Zendesk, is to downgrade them to the End-user role and suspend that user. 

This has an impact on historical data in Explore, however, since that user is no longer an agent, any query that reports on updates (like comments) by agents/end-users will be inaccurate. 

The former-agent updates will count as end-user comments.

Above you can see two updaters (Updater name)

• "Sample customer" (end-user) 
• "Tom WAS an agent" (former agent, now an end-user)

You'll notice that all updates show up as End-user updates, even though Tom was an agent when he made those updates.

A solution (workaround)

One possible way to get accurate counts of updates is to create an Organization that all agents (and former agents) are part of and use this as a way to filter updates properly.

 

So when you downgrade and suspend an agent, that user remains part of The Agent Org and their updates are counted as such.

The metrics

I created two new calculated metrics to precisely capture updates from users as they were at the time their updates were made.

Note: Eugene Orman recommends using the base attributes when defining the custom metrics for speedier calculations. See his example in the comment below.

Agent Org updates

IF ([Updater organization name] = "The Agent Org")

THEN [Update ID] 

ENDIF

 

Non-Agent Org updates

IF ([Updater organization name] != "The Agent Org"

AND [Updater role] = "End-user")

THEN [Update ID] 

ENDIF

 

Creating a Query that does a distinct count on those metrics lets you accurately report on end-users vs. agents updates.