Coco & Cici sells bedding, home textiles and nightwear online. Their data lived in five places: Shopify, GA4, Google Ads, Meta and Klaviyo. Each showed a different revenue, a different ROAS and a different story, so every number had to be checked against Shopify first, and marketing budget was split on figures that didn't agree.
With a CFO steering on margin, that wasn't good enough. The question was how to get one version of the numbers, from ad spend to margin, that the team trusts and can ask questions of without waiting for an analyst.
We built it in four layers, working directly with the marketing team and the CFO. They bring the business questions; we turn them into definitions in the model, so "margin" or "new customer" means the same thing in every report.
First the tracking: server-side GTM for the Shopify store, sending conversions to GA4, Google Ads and Meta.
Then the warehouse in BigQuery. Airbyte loads Shopify, Google Ads, Meta and Klaviyo every night, next to the GA4 export. Dataform turns that into one model: revenue excluding VAT, discounts, returns, and margin based on Shopify cost prices. Automated checks reconcile the totals every night, and alerts go to a Slack channel the team shares with us, so they see the same warnings we do.
On top of that sits a Looker Studio dashboard with a management summary, margin, channels and categories, always compared with last year.
Last, we added a semantic layer to the warehouse: tables that describe what every metric means, how it is calculated and which business rules apply, including events like promotions. That layer is what lets Claude answer questions from the warehouse. Access is read-only, without personal data, and every user logs in with their own account. Every week, a set of questions with known answers checks that Claude still reads the numbers correctly.
Coco & Cici now has one source for its numbers, from ad spend to margin, and the CFO steers on margin per category, with returns counted in the month they happen.
The checks have already proven their worth. In August, Shopify stopped recording where about half of the orders paid through Shopify Payments came from. Without the checks, Google Ads would have looked about three times less profitable than it really was. The warehouse flagged it, and we corrected the numbers before they ended up in a budget decision.
The dashboard and Claude read the same model and the same definitions, so a question in Claude is answered from the same numbers as the dashboard. We maintain the warehouse and the checks, and add new questions and rules as the business changes.
Running into the same thing?
We love the work others find too technical. A trigger from 2019 that skews your revenue? We'll find it. And we'll explain it to you without the jargon.
