Data warehouse
Your ads data in a warehouse you own.
The gap
The Google Ads interface shows you what Google wants you to see, for the period you happen to have selected. The Looker Studio template on top of it is the same data with a logo. Neither joins the ads to what the CRM says happened, neither tells you when something has drifted, and neither survives changing agency.
What we build
A BigQuery warehouse with the agreed advertising, analytics, commerce, CRM and lifecycle sources landing on a schedule. It sits in a client-owned Google Cloud project, with retention, access, query cost controls and connector fees documented.
The warehouse is the fundamental plumbing. Easier analysis and dashboards are the compounding benefit once the sources, keys and commercial definitions are in place. See what an ecommerce data warehouse unlocks for that secondary layer.
The ecommerce reference layer
At the centre are separate tables at their real grain: orders or commerce transactions at one row per order, payment and refund events where the source exposes them, and order lines at one row per item sold. Keeping them separate stops a three-line basket becoming three orders.
The commercial tables retain the fields the source can support: transaction and customer keys, timestamps, currency, gross sales, discounts, tax, shipping, refunds, net revenue, product or SKU, quantity, and cost or contribution where it is available. The commerce platform remains the financial record. GA4 purchases and advertising-platform conversions sit beside it as measurement evidence rather than replacing it.
Scheduled loads bring Google Ads, GA4, Microsoft Advertising, Meta Ads and Klaviyo into BigQuery. Each source lands in source-shaped tables before it is transformed, with authentication, field definitions, attribution windows, time zones, lookback limits, costs and failed loads documented.
| Source | What it contributes |
|---|---|
| Ecommerce platform or ERP | Transactions, payment and refund events, order lines, products, discounts, customer keys and available cost data |
| Google Merchant Center | Submitted and processed product data, eligibility, issues and available product-performance evidence |
| Google Ads | Spend, clicks, campaign structure and source-reported conversions |
| Microsoft Advertising | Spend, clicks, campaign structure and source-reported conversions |
| Meta Ads | Paid-social spend, delivery and source-reported results |
| GA4 | Consented web events, purchase events and transaction IDs |
| Klaviyo | Profile, event, campaign and flow evidence available through the agreed API route |
| CRM or call platform | Lead stages, call outcomes and later commercial value where lead generation is also in scope |
Loading Meta Ads or Klaviyo does not turn this into Meta or email management. It means those channels can be read against the same order and customer facts as Google and Microsoft.
Transaction IDs, campaign and product identifiers, dates and eligible advertising identifiers connect the data only at the level each source genuinely supports. Where no stable join exists, the gap stays visible instead of being filled with an invented match.
One reference layer, not one fabricated number
Google Ads, Microsoft Advertising, Meta Ads, GA4 and Klaviyo can all claim the same order under different attribution and timing rules. The warehouse preserves each platform-reported result and keeps it separate from the order-system outcome. It gives the business one documented place to reconcile the claims; it does not force them to agree.
That improves attribution hygiene. It does not prove that a channel caused the sale. Holdout or geo tests answer incrementality when the volume supports them; the predictive model does not.
Weekly variance alerts. n8n runs the checks every Monday: spend against budget, CPA and value against the trailing period, search term mix, impression share, conversion action volumes by type. For ecommerce, the checks also cover source freshness, duplicate transaction IDs, order totals against the store, refund lag, spend, MER, new-customer acquisition cost and realised cohort value. Anything outside its normal range is flagged before anyone opens the account. That is the machine half of the weekly loop. The human half is deciding what to do about it.
The model earns its way in
A model is not useful because BigQuery can train one. It earns a route into the account by beating a simpler rule on customers it has not seen.
The first baselines are deliberately plain: value on the first order, and net value accumulated in the first 90 days. If either predicts the later commercial outcome well enough to support the same decision, the simpler rule wins. It is cheaper to explain, monitor and maintain.
Where that simple relationship does not hold, and the history is large, mature and representative enough, I test whether other facts known by the scoring date add useful signal. First-order product mix, value, discount, acquisition source and new-versus-returning status can be candidates. Later orders, future refunds and subsequent email behaviour are not allowed to leak backwards into an earlier score.
Training and validation are split by time. Later customer cohorts are held out, every label has a complete outcome window, and the model is compared with a plain baseline. I check error, calibration and lift in the segment the business would actually use, then monitor drift as price, range and acquisition mix change. If it does not hold out of sample, it does not become an operational input.
Predictions start in reporting. With the required consent, purpose, platform eligibility and client approval, a validated segment can later inform a Klaviyo lifecycle segment, advertising audience or eligible value feed. Actual and predicted value remain separate fields. No score is sent to a platform, and no bid changes, merely because the model produced it.
Competitor ad review using Google's Ads Transparency Center, within the coverage and search functions Google provides. It shows declared advertiser creative and regions, not a competitor's complete spend or targeting plan.
Dashboards on the warehouse. Your numbers, joined to your outcomes, in a form you can hand to your accountant.
One honest limit, stated plainly: matching CRM outcomes to clicks is attribution hygiene, not proof that the ads caused the job. Some of those customers would have found you anyway. The warehouse is also where that question gets answered properly, with holdout and geo tests, when the volume justifies running them. Most accounts never get the hygiene, let alone the causation test. We do them in that order.
What you get
The source loads and monitored pipelines. Documented transaction, payment and order-line tables. The joined campaign, product, customer-cohort and lifecycle reporting model. Reconciliation and freshness checks. Reusable reporting views, alert rules and the first agreed dashboards.
Where machine learning is justified, you also get the feature and target definitions, validation result, model version, scored output table and monitoring rules. Audience or value-feed exports are added only when the model and the destination are approved.
Sources checked
- Google Cloud: BigQuery pricing
- Google Analytics: BigQuery Export
- Google Ads transfers to BigQuery
- Merchant Center transfers to BigQuery
- Google Merchant API product status and issues
- Google Cloud: evaluating BigQuery ML models
- Microsoft Advertising Reporting API
- Meta Marketing API Insights
- Klaviyo Events API
- Klaviyo Reporting API
- Google Ads Transparency Center
Related: What the warehouse unlocks · Ecommerce management · The weekly loop · Targets from unit economics
Explore the interactive client-owned warehouse diagram alongside the implementation detail above.
FAQ
Is this overkill for £10k a month?
It can be. We scope the decision and alerting need first. An LTV model needs enough representative history, and a smaller account may be better served by a simpler joined report.
Who owns it?
You. It sits in your Google Cloud project. If we part ways, it keeps running.
What does it cost to run?
It depends on storage, query volume, transfer and the connectors used. We estimate those costs from your volumes, set budgets and alerts, and use BigQuery's current free allowances only where they actually apply.
Does this make every platform agree?
No. It gives us one documented place to compare them. The order system remains the commercial record, while each platform's attributed result stays labelled under its own rules. Incrementality needs an experiment.
Does the model change bids automatically?
No. Predictions start in reporting. Only a model that holds up on later customer cohorts may inform an approved audience or value feed, and every outbound route remains monitored and reversible.
What becomes easier once the warehouse exists?
New analysis and dashboards can reuse the same transactions, order lines, product keys, customer definitions and marketing joins. Where the required source is already present, a new question becomes a query or view rather than another export-and-reconciliation project.