§1 The bottleneck
Before automation, producing the weekly cross-domain performance report meant pulling exports from more than 40 separate properties, normalising column names that were never quite consistent, and stitching them into a single view by hand. It took the better part of a working day, and it was exactly the kind of repetitive task that introduces silent errors — a missed export, a renamed column, a paste into the wrong row.
§2 Designing for "one pipeline, many domains"
The redesign started from a constraint: every domain's raw data had to land in the same shape before any analysis touched it. Rather than writing 40 slightly different extraction scripts, I wrote one parameterised pipeline driven by a configuration table — domain ID, source type, currency, timezone — so adding a new domain meant adding a row, not writing new code.
§3 The BigQuery schema
Raw exports land in domain-specific staging tables, then a scheduled SQL transformation normalises currency, timezone, and naming into a single fct_domain_performance table partitioned by date and clustered by domain ID. That structure keeps query costs predictable even as the number of domains grows.
CREATE OR REPLACE TABLE reporting.fct_domain_performance
PARTITION BY event_date
CLUSTER BY domain_id AS
SELECT
domain_id,
event_date,
SAFE_DIVIDE(revenue_local, fx_rate) AS revenue_eur,
sessions,
conversions
FROM staging.domain_raw_*§4 Scheduling, validation, and alerting
A Python orchestration layer (Cloud Functions + Cloud Scheduler) triggers the extraction nightly, runs row-count and null-rate checks against each domain's expected ranges, and posts a summary to Slack. If a domain's data looks anomalous — a sudden zero, a currency mismatch — the pipeline flags it before it reaches the report rather than after.
- Row-count deltas vs. the trailing 7-day average
- Null-rate thresholds per critical column
- Currency and timezone sanity checks against the config table
§5 What changed
Weekly report preparation dropped from roughly six hours of manual assembly to under fifteen minutes of review — and the report itself became more trustworthy, because the validation layer surfaces problems that used to slip through unnoticed in a spreadsheet.