The problem
The agency's reporting process was entirely manual. After a campaign ran, an analyst would open each ad platform, pull metrics by hand, copy figures into a spreadsheet, and assemble an HTML one-pager — a process that ran roughly an hour per client per reporting period. The method was a ceiling: time-intensive, error-prone, and impossible to scale across a growing client roster.
The deeper problem was architectural. With no single place where ad-platform data lived, there was no fast way to answer in-flight questions: Is a campaign pacing to hit its budget? Is impression volume outrunning spend, or the reverse? Which creative is driving conversions? Each of those questions required logging back into a platform rather than looking at one screen.
The agency needed the data to live somewhere it could be queried at any time, and the assembly work — the number-pulling, the metric math, the table-building — automated, so a human analyst could spend time on what machines cannot do: strategic interpretation.
The approach
The core architectural decision was to build on Cloudflare Workers and D1 rather than a managed SaaS or hosted database. Workers run at the edge with no cold-start overhead; D1 is a serverless SQLite instance that binds directly to the Worker — no network hop to a separate database. For an agency that bills on execution and margin, zero ongoing compute cost and a zero-dependency Worker bundle was a natural fit.
The second decision was to define the data model before writing any connector. Advertising data across platforms is a mess of overlapping metric names: what one platform calls "completions," another calls "video views through 100%." Rather than gluing platform-specific shapes together at the display layer, the build started with a canonical metric registry — one definition per KPI (CTR, CPM, CPC, CPA, VCR, frequency, and others), with direction, unit, and funnel stage encoded alongside the formula. Every downstream consumer — the SQL aggregation layer, the delivery engine, the render templates — reads from that registry, so a metric's definition exists in exactly one place.
The third decision was explicit about the human/machine split. The dashboard automates the mechanical work: pulling raw figures, running the math, flagging pacing status, drafting brief factual interpretations ("76% of budget spent, 88% of impression goal delivered — ahead of pace"). The strategic interpretation — the headline thesis, the recommended next actions — remains a human-authored slot, editable before a report ships. The deliverable is not a machine report; it is an analyst-reviewed report that the machine made possible in five minutes instead of an hour.
The build
The Worker entry handles HTTP routing via Hono. Cloudflare D1 holds a normalized fact table — one row per campaign, per date, per platform — populated by a nightly cron trigger that calls platform APIs, normalizes responses into a common RawInsight shape, and upserts. Secrets are stored via wrangler secret put, never in the repository; a gitleaks pre-commit hook and GitHub secret-scanning backstop at the repo level.
The metric registry (src/metrics.ts) and SQL projection layer (src/sql.ts) are the spine of the math. KPIs are computed once at query time from raw fact rows; the React web layer reads server values rather than re-deriving metrics in JavaScript. A pace.ts module computes spend-against-flight-elapsed pacing; a delivery.ts engine runs direction-aware goal comparison (a rate metric being "below plan" reads differently than a cost metric being "below plan").
The test suite covers every module with smoke scripts: metric registry equivalence, pacing-contract truth tables, naming-convention matching, ingest validation, and delivery logic. The suite runs deterministically with no network calls, no randomness, and no time-of-day dependencies. As of the June 2026 QA pass, all suite scripts were green, including new scripts covering the metric registry, volume-pacing contracts, and campaign-name matcher.
A connector to a second major ad platform was built and tested — it emits the same RawInsight shape as the Google Ads connector, so nothing downstream changes when it activates. It is currently awaiting ad-account access to go live.
Outcomes
The Google Ads connector is live and pulling real campaign data on a nightly cron. The dashboard shows spend pacing, impression delivery against goals, touchpoint breakdown, and per-campaign drill-ins — all without logging into the platform. A print-to-PDF one-pager exists for client delivery.
A structured QA review in June 2026 confirmed the build's security posture is clean: no secrets in the diff, no new runtime dependencies, Worker and web typechecks passing. The review also surfaced one live bug — a partial reach-aggregation fix that left two code paths inconsistent — and blocked the PR on it. The QA surface identified the defect before it compounded, which is the process working correctly.
Since that review the surface has matured into a working analyst tool. A second ingestion path now accepts platform exports as spreadsheets — a programmatic-DSP report drops in as an Excel file and lands as touchpoints on live campaigns, so a platform without an open API is no longer a blind spot. Above the connectors sits a cross-platform matching layer that rolls the same logical campaign up across sources at read time, giving one combined view of a buy that runs on several platforms at once. The interface was rebuilt around a left sidebar with a two-view split — a clean client-facing report and a richer internal view with a data-quality panel — and a full light/dark theme. The reading surface itself was validated against the agency's own reference report so the numbers sit where a reviewer expects them.
The analytical gap the earlier pass called out — period-over-period comparison — is now closed: every performance metric shows pace against goal and movement versus the prior period, with spend-and-delivery trend lines and a Leads → Add-to-Cart → Purchases conversion funnel on the campaign drill-in.
Report assembly now takes minutes rather than an hour per client. As additional connectors and exports come online, the same architecture covers every media account without per-client build work — the client is a config row and a theme, not a new codebase.
What's next
Immediate next steps: activate the pending API connector once ad-account access is confirmed, deepen the cross-platform matching across more campaign shapes, and bring the branded one-pager export fully forward — most of the infrastructure already exists. The same Worker-plus-D1 stack and metric registry are reused by the agency's audit and lead-enrichment tools, so every core improvement compounds across the product suite.