Multi-client marketing analytics warehouse and report generator

Problem

A marketing team reported monthly to many client accounts through dashboards with broken data connectors and hand-assembled numbers. The data lived in many separate tools, and mistakes in a dashboard's hand-built configuration went unnoticed until someone spotted a wrong figure.

Goal

One scheduled platform that pulls every source for every client, models consistent monthly metrics, generates each client's monthly deck automatically, and proves that the dashboard is still built the way it was specified.

My role

Designed and built it end to end: ingest modules, job scheduling, warehouse modelling, data-quality gates, the AI-visibility measurement method, the deck generator, custom dashboard components and the verification tool.

Solution

Scheduled container jobs feed a cloud data warehouse through per-source ingest modules. SQL models in layers turn raw rows into monthly metrics. The same models drive both a BI dashboard and a per-client slide-deck generator, and a verification tool checks the live dashboard against its build specification.

How it works

Scheduled container jobs run in daily, monthly and backfill modes, each running per-source modules across web and search analytics, social, advertising, call and form leads, search visibility and AI-answer visibility.

Each module reloads a client's data window only after a successful fetch, so a failing source never wipes a client's history, and every run goes to a run log that alerting reads.

Authentication is keyless in the cloud: the job's identity impersonates a reader identity and signs short-lived tokens. Locally, credentials come from the operating system keychain. The one feed that reads a mailbox runs as its own scheduled job under its own identity, so mailbox access is isolated from every other ingest at the job level.

SQL layers build staging views, a monthly mart in which every session lands in exactly one channel bucket, and presentation views.

Data-quality gates are queries that return rows only when something is impossible (negative metrics, clicks above impressions, call components that do not add up, out-of-range positions or scores), on the principle that a wrong number is worse than a missing one. Freshness gates flag a feed only when a client that normally has data has gone quiet, so a naturally low-volume source is not treated as a fault, and a duplicate-month gate catches a doubled month, which would double every downstream figure.

AI-answer visibility is recorded as rows, one per client, engine, prompt and run, with whether the brand was mentioned and whether its site was cited. A raw count of AI citations was rejected because it has no denominator (it rises whenever prompts or engines are added) and its month-to-month change is mostly model randomness. Instead the prompt set is frozen and versioned, and the report shows a rate with a confidence interval, saying "no change" when this month's interval overlaps last month's. Each paid query run checks the remaining data-provider balance, skips calls that would take it below a floor and records the skips in the run log.

Reporting is one dashboard with a client selector over a client dimension rather than one copy per client: the BI tool offers no API to create or edit reports, so per-client copies would turn every template change into many manual edits.

A batch generator writes a branded slide deck per client per month with fixed metric definitions and placeholders for human commentary, and custom visualisation components render signed-off layouts from live data.

A verification tool builds the expected report from the specification, compares it read-only with the live report's saved configuration and produces a punch list of differences (including a date setting that silently resets itself). It only compares; a person makes every fix.

Also scoped, as design only: a recurring website health audit that renders each monitored site's homepage in a real headless browser, checks structured data, titles, headings, contact links, alt text and contact-detail consistency, and tracks every issue from found to fixed in the same reporting, without letting a failed scan clear an open issue.

Technical challenges

A wide range of APIs with different authentication, quotas and response shapes.

Partial failure that never erases history (reload only after a successful fetch).

A metric for AI-answer visibility that stays honest under run-to-run randomness: frozen, versioned prompts, a rate with confidence intervals, and "no change" when the intervals overlap.

Gates that catch impossible, stale or duplicated data without flagging normally quiet sources.

A BI tool with no create or edit API: one multi-client dashboard, plus a specification-drift verifier for settings that silently reset.

Keyless cloud authentication, bounded spend on paid data APIs and least privilege between feeds.

Multi-client modelling where every metric must reconcile.

Capabilities

Multi-source, multi-client ingest
History-preserving reloads
Run logging for alerting
Layered warehouse models
Automated data-quality, volume-aware freshness and duplicate-month gates
One multi-client dashboard with a client selector
Spend floor on paid data calls
Job-level isolation of mailbox access
AI-answer visibility with confidence intervals
Automated monthly decks per client
Custom dashboard components
Dashboard-versus-specification verification

Technology

Impact

Moved monthly client reporting from manual assembly to a scheduled pipeline producing consistent per-client decks, with quality gates and specification checks catching errors before a client sees them. No metrics are claimed.

Skills demonstrated

Data engineering and warehouse modelling
ELT reliability
API integration at breadth
Statistical reporting design
Report automation
Verification tooling with tests
Cloud security (keyless authentication, least privilege)