A KPI dashboard that answers in plain English
A US luxury bathroom brand sells online, through a showroom and to the trade. Deals close weeks after the click, so its Looker report kept arguing about numbers that were not final yet. We built a KPI dashboard on top of the company's own BigQuery that counts only closed CRM revenue, shows what changed in the ad accounts next to the spend, and lets the team ask questions in plain language.
The problem
Ad platforms report the conversions they claim. The business lives on deals that close in the CRM, sometimes months later. A report built on either number alone leads to the wrong call: pausing an ad that is about to pay back, or scaling one that only sold to customers who were already in the pipeline.
What we built
- One definition of revenue. Revenue is closed CRM revenue and ROAS is that revenue over spend. Meta's own claimed purchases sit beside it, flagged, instead of silently replacing it.
- "What changed" next to the spend. Every change made in Google Ads, Meta and Bing, by people or by scripts, is collected into one log and marked on the spend chart, so a jump in cost comes with its likely cause.
- Creative winners and losers on matured money. Lead forms take weeks to turn into estimates and sales take longer, so an ad is judged on closed revenue after the lag, not on day-three numbers.
- Ask AI over your own data. A question like "why did cost per estimate rise last month?" is answered by Claude using a query tool that can only read a curated, read-only dataset, with size and row limits, and a rule to check the change log before explaining a jump.
- Actions with a safety net. An ad can be paused or turned on from the dashboard with a confirmation and a 15-second undo, and every switch is logged.
- Safe by construction. Sign-in is restricted to the company's Google domain, the service account sees only the curated views, and deploys go through GitHub Actions with keyless authentication, preview revisions for every pull request and a one-command rollback.
The result
The team now has one view that says what closed, what each channel and creative cost, and what changed in the accounts, and it answers follow-up questions without waiting for an analyst. We have since reused the same pattern for a second client, and the dashboard is the base of a multi-tenant version that connects GA4, Google Ads and Meta to a canonical set of tables.
See it working
The live demo runs on synthetic data for a fictional showroom. It has a built-in story that the AI can find: a budget increase, a broadened Meta audience that raised cost per lead, an affiliate that started bidding on the brand, and a target-ROAS change.
Want this on your own BigQuery?
We connect your ad accounts, analytics and CRM, define revenue the way your business actually earns it, and add an analyst that answers in plain language.