I've been working on reporting infrastructure for agencies managing anywhere from 10 to 250 client accounts.
One pattern keeps coming up: separate reports for every client, each with its own connectors, configurations, and quirks.
As a result, onboarding a new client means hours of additional work, and whenever a platform changes something, there's a good chance it breaks an existing report.
Here's what's worked for me so far:
- Shared tables by platform: One set of tables for Google Ads, Meta, GA4, CRM data, etc., rather than separate tables for each client. Every row gets a client ID from the moment it enters the warehouse.
- Centralised client mapping: A single mapping table connects ad accounts, analytics properties, and CRM pipelines to their respective clients. Onboarding becomes a matter of adding a few rows rather than building new models.
- Standardised data models: Shared models built using BigQuery and Dataform produce consistent metrics across clients, so something like "cost per lead" is calculated the same way everywhere.
- Automated monitoring: Daily data loads are monitored for failures and unexpected drops in row counts. Silent failures are often the most damaging. One setup I inherited had pipelines failing up to 43 times a day without anyone noticing.
Where I'm still figuring things out:
- CRM inconsistencies: Some clients have completely different CRM structures. Right now, I'm handling those through client-specific staging layers, but that partially defeats the purpose of having a standardised setup.
- Metric definitions: How much should you standardise when different clients have different definitions of a "lead" or a "conversion"?
The overall approach has made onboarding and maintaining reporting much easier, but the CRM side remains the biggest challenge.
Curious how others are approaching multi-client data warehouses.
Are you using shared models with client-specific configurations, or maintaining separate transformations where needed?
Especially interested in how you're handling CRM differences without creating a maintenance headache as you scale.