Five data sources, one reporting system

The knot

The operation ran on five systems that didn’t share data: the CRM (Kustomer), the phone platform (Amazon Connect), the QA tool, the WFM tool and an internal roster. The main weekly report was built by hand, cell by cell, agent by agent, copying numbers out of the CRM. Several assistant managers and I spent hours on it. It arrived late, it was error-prone, and it described a week that was already over.

What I did

• Wrote ETL pipelines in Google Apps Script that pull from every source and land the data in one shared layer. The internal roster acted as the hub: every other source joins to it on a shared key, so an agent’s CRM activity, call data, QA scores and schedule all line up.
• The first working version took about two weeks. I kept improving it for years after that, and by the end it handled 200,000+ rows a week.
• Rebuilt the reporting as 30+ Looker Studio dashboards reading from that layer, with CSAT, FCR, AHT, TTFR, SLA, occupancy, adherence and volume visible in near-real time.
• Added 60+ low-code automations in Apps Script and Slack Workflow Builder for the repetitive work around it.
• Later added LLM tooling for reporting narratives and forecast anomaly checks, with me reviewing the output before it went to anyone.

What changed

Reporting latency fell by half and admin workload by 40%. The WFM reporting infrastructure was adopted across the company.

The dashboard I’m proudest of showed attendance not as a count of infractions but as time actually lost from the queue. Once we could see it that way, we rewrote the attendance policy around queue impact instead of “X strikes”, which is both fairer to the team and more useful to the operation.

What I’d tell someone doing the same

Build the data layer before the dashboards, and pick one source to be the hub everything else joins to. When two reports read from different exports they will eventually disagree, and the time spent arguing about which one is right costs more than building the layer did.