Managing 20 customer experience agents without a clear view of what each one is doing is a common problem. You know someone on your team is underperforming, but you're not sure who. You suspect ticket counts are inflated, but you can't prove it. Every Monday, someone spends hours pulling data manually, building a spreadsheet, and sending it around, only for it to be outdated by Tuesday.

That was the situation at a US e-commerce brand I worked with in 2025. Their CX team had grown to 20 agents, but their performance tracking system had not kept pace. The result: leadership was flying blind, agents had no visibility into their own standing, and duplicate tickets were quietly inflating the numbers no one trusted.

I built them a KPI tracking system entirely in Google Sheets and Google Apps Script. It has been running since August 2025. Here is a full breakdown of how it works.

The Brief: What Leadership Needed

Before I built anything, I mapped the gap between what existed and what was needed.

What existed:

What leadership needed:

The instinct in most organizations is to solve this by buying software. There are plenty of CX workforce management platforms out there, and some of them are genuinely good. But at this scale, a $400-500/month SaaS tool is hard to justify, especially when Google Workspace is already paid for and your team is already living in Google Sheets.

The pragmatic answer: build the system in tools the team already uses, automate the intelligence layer with Apps Script, and save the SaaS budget for something that actually requires it.

Architecture Overview

The system has three layers:

  1. Master KPI Tracker (one shared workbook, roughly 50 sheets)
  2. Individual Agent Trackers (one per agent, 12 sheets each)
  3. Google Apps Script automation (the intelligence layer connecting both)

Each layer has a clear job. The Master Tracker gives leadership a single source of truth. Individual Agent Trackers give each agent ownership over their own data and performance visibility. The Apps Script layer ties them together, runs checks automatically, and surfaces information that would otherwise stay hidden.

Layer 1: The Master KPI Tracker

The Master Tracker is the system's command center. It is a single Google Sheets workbook with approximately 50 sheets organized around a simple principle: raw data in, processed metrics out, dashboard on top.

Dashboard sheet: This is the first thing leadership sees. It shows live aggregate metrics across all 20 agents: total tickets resolved, average CSAT score, QA average, first-response time, and the current Top-3 leaderboard. No manual updates needed. Everything refreshes as data syncs from agent sheets.

Per-agent RAW data sheets: Each agent has a dedicated sheet where their daily ticket data is entered. Keeping raw data isolated per agent prevents cross-contamination and makes debugging straightforward. If an agent's numbers look wrong, you know exactly where to look.

Per-agent processed sheets: Alongside each RAW sheet is a processed sheet where formulas do the heavy lifting. Weekly totals, monthly averages, trend comparisons, and performance ratios are all calculated automatically. No one needs to run calculations manually.

Duplicates sheet: This is one of the most valuable sheets in the system and the one that surprised leadership most. Before this system existed, duplicate ticket IDs (the same ticket counted by two agents or entered twice) were invisible. The Duplicates sheet surfaces every suspected duplicate flagged by the automation, showing the ticket ID, both agent names, and the date. Leadership can review and confirm before anything is deleted.

DeletedLog sheet: This is the audit trail. Every time a confirmed duplicate is deleted, the system logs it: what was deleted, from which agent's sheet, and when. No silent data loss. This was a non-negotiable requirement and it turned out to be the feature leadership referenced most in early conversations about trust.

Layer 2: Individual Agent Trackers

Every agent gets their own Google Sheets workbook with 12 sheets. The design goal was simple: make it easy to enter data and make it valuable to look at.

Monthly data entry tab: This is the interface agents interact with daily. It is designed to feel like a form, with clear columns and validation rules to prevent entry errors. Agents log ticket IDs, resolution times, channel (email, chat, phone), and any QA notes.

My Stats dashboard: This is the sheet agents actually care about. It shows their own metrics compared to the team average. CSAT, tickets resolved, QA score, first-response time, all benchmarked against the group. Agents started self-correcting almost immediately once they could see where they stood.

My QA Log: Quality assessment scores tracked over time in a dedicated view. Agents and their team leads can both see the history of QA evaluations, which made performance conversations faster and less adversarial. The data was already in front of both parties before the conversation started.

Supporting tabs: DM rotation schedule (who covers what channel and when), a Links tab with direct links to resources and tools, and a Reminders tab for recurring process notes. These tabs reduced the volume of "where do I find..." messages in team chat.

Layer 3: The Google Apps Script Automation

This is where the system earns its keep. Google Apps Script is a JavaScript-based runtime built directly into Google Workspace. It runs on a schedule or in response to triggers, with no external server needed and no additional cost beyond the Workspace subscription.

Three automations do the real work here.

The Duplicate Detection Engine

On a scheduled trigger, a script cross-references every ticket ID across all 20 agent RAW sheets. If the same ticket ID appears in more than one sheet, or appears twice in the same sheet, the script writes the suspected duplicate to the Duplicates sheet: ticket ID, agent names involved, and the date of the entry.

This runs automatically. Leadership does not need to request it or remember to run it. Duplicates surface passively, and the team reviews them on a regular cadence rather than discovering them by accident months later.

Before this system, duplicate ticket counts were quietly inflating performance metrics. The top agents looked better than they were, and the agents doing honest work looked worse. One audit function changed that.

One-Click Deletion with Audit Trail

When leadership reviews the Duplicates sheet and confirms a record needs to be removed, they click one button. The script handles the rest: it deletes the entry from the relevant agent sheet and immediately logs the deletion to the DeletedLog sheet with a timestamp and the identity of the record.

The design principle here was that no data should disappear without a trace. If someone later questions why a number changed, there is a full record of what was removed and when. This matters for performance reviews, payroll disputes, and team trust in the system overall.

The Live Top-3 Leaderboard Marquee

The Dashboard sheet includes a scrolling ticker showing the current top 3 agents by CSAT score. It updates in real time as data syncs. This was a deliberate choice. Visibility into rankings drives behavior, and a scrolling marquee on the shared dashboard made performance visible in a way that a static table in a weekly report never does.

It also made the system feel alive rather than like another static document. The ticker was the feature agents noticed first, and the My Stats dashboard was the one they kept going back to.

The Results

Before: Weekly KPI reviews took 3 to 4 hours of manual work, including data collection, formula-checking, leaderboard updates, and report assembly.

After: Weekly review time dropped to roughly 20 minutes. The data is already compiled. The duplicates are already flagged. Leadership reviews, makes decisions, and moves on.

Duplicate visibility: For the first time, the team had a clear view of how often tickets were being counted more than once. The number was higher than anyone expected. Catching and removing duplicates brought the performance numbers closer to reality and gave high-performing agents more accurate recognition.

Agent accountability: Once agents could see their own stats against the team average in real time, behavior shifted. Agents raised concerns about their own numbers before team leads did. Performance conversations became more collaborative and less corrective.

Leadership visibility: One dashboard, all 20 agents, updated daily. No waiting until Monday. No emailing around for data. No reconciling conflicting spreadsheets.

Why Google Sheets (and Not a $500/Month SaaS Tool)

I get asked this question a lot. There are purpose-built CX workforce management platforms, and some of them are excellent. But they come with real costs beyond the subscription fee.

Every new SaaS tool is a migration project. It requires onboarding, training, change management, and integration work. For a 20-person team that already works in Google Sheets every day, the friction of switching to a new platform is real and often underestimated.

Google Sheets is not the right tool for everything. But for a team of this size, with this level of data complexity, it is entirely capable of delivering what a $500/month tool would deliver, at zero additional cost, with full customization, and with no vendor dependency.

The Apps Script layer is free. The Workspace subscription was already paid. The only investment was the build time, and the system has paid that back many times over.

If you are already using Google Workspace, the marginal cost of this kind of system is essentially zero. That is a hard case to argue against.

What This Unlocks

A functioning KPI system is not just a reporting tool. It changes how you manage.

Data-driven coaching: When you sit down with an agent for a performance review, you are not relying on memory or anecdote. You have their QA history, their ticket trend, their CSAT average over time, and how they compare to the team. The conversation is anchored in fact rather than impression.

Fair performance reviews: When everyone is measured on the same metrics, calculated the same way, with duplicates removed, the playing field levels. Top performers get accurate recognition. Underperformers cannot hide behind inflated counts.

Management without micromanagement: When agents can see their own stats, they manage themselves more actively. When leadership has a live dashboard, they do not need to check in constantly to know what is happening. The system creates visibility that replaces the need for surveillance.

How This Scales

The architecture I built for 20 agents works identically for 5 agents or for 100. The Master Tracker scales by adding agent sheets. The duplicate detection engine processes whatever it finds. Individual Agent Trackers are templated so adding a new agent takes minutes.

The limiting factor at larger scale is Google Sheets performance, not the system design. Very large teams (50 or more agents) would benefit from connecting this to a proper database backend, using Sheets purely as the interface. But for most small-to-midsize CX teams, this setup handles everything without modification.

The same principles apply regardless of scale: raw data isolated per agent, processed metrics calculated automatically, a central dashboard for leadership, individual dashboards for agents, and an automation layer that does the cross-referencing no human should be doing manually.

This project is one example of what I cover in depth in my AI Operations System case study for a fitness coaching business, where the same build-it-in-the-tools-you-have approach produced similar efficiency gains in a completely different context.

If you are evaluating whether AI and automation tools are right for your business, my guide on what actually works in AI automation for consultants in 2026 covers the decision framework in detail.

If you are managing a CX team and want something like this built, reach out here.

FAQ

How long did this take to build? The initial build took approximately three weeks, including architecture design, sheet templating, Apps Script development, testing with live data, and agent onboarding. The Apps Script duplicate detection engine was the most complex piece.

Do agents need to know how to use Google Sheets? Basic familiarity is enough. The data entry tabs are designed to be simple and guided. Agents interact mainly with their own Individual Tracker, which has a straightforward layout. The complex formulas and automation are in the background.

What KPIs does the system track? The system is built around: CSAT (Customer Satisfaction Score), tickets resolved, first-response time, resolution time, QA assessment scores, and channel distribution (email, chat, phone). The specific metrics can be adjusted for any CX team's reporting requirements.

Can this connect to a ticketing platform like Zendesk or Freshdesk? Yes. Google Apps Script supports API calls, so the system can be extended to pull ticket data directly from a ticketing platform rather than relying on manual entry. That is a more complex build and typically the right next step once the manual version is proven and trusted.

What happens when someone tries to delete a record manually? The DeletedLog captures script-triggered deletions. For manual edits, Google Sheets' built-in version history provides a backup record. For teams that need tighter controls, Apps Script can be extended to add edit-level logging, though most teams at this scale find version history sufficient.

Does this work if the team is spread across time zones? Yes. Google Sheets is cloud-native and agents can update their trackers from anywhere. The scheduled scripts run on a set timezone and the Dashboard refreshes globally. Time zone handling for timestamp fields requires a small configuration, but is straightforward.

Sources

Need AI automation for your business?

I build custom AI systems that save 20+ hours/week. Book a free discovery call to see what's possible.

Book a Discovery Call