Marketing Data Platform & Reporting Dashboard
A BigQuery warehouse and reporting dashboard for a US marketing agency, replacing a spreadsheet workflow with one source of truth per client, plus an assistant that answers questions over it in plain language.
A marketing agency needed one place where client performance actually reconciled. The platform pulls analytics, search console, ads, server logs and social exports into a per-client BigQuery dataset, models them into marts, and serves them through a dashboard where each client sees only their own company.
On top of that sits an assistant that answers questions in plain language by writing SQL against that client's marts. Two independent checks stand between a question and the warehouse, one on the query text and one on the tables the query actually resolves to, so a question can never read another client's data.
Case Study
The Problem
The agency reported on client performance by pulling exports from half a dozen places, reconciling them in spreadsheets, and rebuilding the same charts every month. Figures disagreed depending on which tool they came from and nobody could say why, so every report carried a quiet risk of being wrong. Taking on another client meant repeating the whole setup by hand. They also wanted to show clients how often AI assistants were fetching their sites, which nothing off the shelf reports.
The Approach
I built the warehouse in BigQuery with the infrastructure defined in OpenTofu: analytics, search console, ads, server logs and social exports landing in per-client datasets, ingested by scheduled jobs on Cloud Run. The awkward parts are modelled into marts, so a page never does arithmetic of its own, which is what had been producing the disagreements in the first place. Where two sources genuinely count different things, both are shown side by side and never averaged. The dashboard is Next.js, sign-in is by invitation only, and a client user is pinned to their own company by the session rather than by anything in the URL. The assistant answers a question by writing SQL against that client's marts, behind two independent gates: the query text is checked before it runs, and the query is dry-run so the tables it really reads, resolved through every view, are checked against a declared list. Either gate failing refuses the query, and neither one alone is trusted.
The Outcome
Reporting that used to take a day of spreadsheet work now comes off one page, and the figures reconcile against the source systems. Twenty months of social history and three years of analytics were backfilled as far as the APIs allowed, with the limits written down where they did not. The assistant is live and costs a few cents a question. The engagement is ongoing.