My Role

I owned it end to end. That covered the measurement framework and KPIs, the data model, the pipeline, the data-quality fixes, and the executive dashboards.

What the CRM Reporting Pipeline Does

The Hard Part: Making Platforms Talk

Each platform described the same campaign differently. Email, in-game, the companion app, Airtable, and Looker each had their own naming, granularity, and IDs. However, no shared key connected them. So before any reporting could happen, the CRM reporting pipeline had to trace every record back to its campaign.

A Common Denominator

I built a shared campaign taxonomy as the “language” every platform translates into. It condensed 99 inconsistent planning labels into 9 standardized beats and 6 program pillars. As a result, a send, an in-game placement, and a planning record can all sit under the same moment.

A Matching Ladder, Not a Single Join

Where a clean key existed, I used it. Where it didn’t, the pipeline steps down:

1. 1Exact match: campaign UID joined to the Airtable plan
2. 2Contextual match: sales window and date logic when no UID exists
3. 3Keyword match: custom overlap scoring links in-game placements to campaigns by name
4. 4Default bucket: anything unresolved is isolated and flagged, never silently miscounted

Together, these steps resolved the large majority of unclassified records.

Custom Code Where Tools Fell Short

Off-the-shelf connectors covered the easy paths. For the rest, I wrote custom logic in Apps Script and n8n. However, large in-game datasets kept timing out the automation runner. So I moved the matching engine entirely into Apps Script, where it runs reliably at volume.

A Unique ID for What Comes Next

To prevent this problem going forward, I designed a campaign UID convention: Title + Content Name + Phase + Channel. It fits a 15–20 character slot inside a 50-character field that another team extends downstream. Every new request now carries a key. Because of that key, future parameters merge cleanly into the CRM reporting pipeline, with no matching ladder needed.

Results

The biggest wins were time and clarity.

• ~6 hrs → under 30 min Estimated weekly reporting time. Manual, channel-by-channel pulls became an automated refresh.
• 3 channels, 1 view Email, in-game, and the companion app are now measured against the same beats.
• A clear read on lift Leadership can see the lift each new campaign, effort, and channel adds. As a result, they know where extra support pays off.

Tech Stack

The CRM reporting pipeline runs in three layers:

• Airtable
• Looker
• Google Sheets
• n8n
• Google Apps Script
• JavaScript
• SQL
• PostgreSQL
• Grafana

Next, see how the same thinking powers live football data triggers for EA SPORTS FC, or browse all projects.

Hero photo: Luke Chesser / Unsplash.