Five platforms unified.
One view of what's driving revenue.
Country Meats had revenue data in Shopify, traffic in GA4, and paid campaigns running across Google, Bing, and StackAdapt — with no way to connect them into a single view. We built a centralised BigQuery warehouse fed by Airbyte and Supermetrics, and delivered five Looker Studio dashboards covering ecommerce performance, web behaviour, and paid media efficiency across all three ad channels.
Five platforms. No shared view of what was actually driving the business.
"Revenue was in Shopify. Traffic was in GA4. Paid performance was split across Google, Bing, and StackAdapt. There was no way to answer a simple question like 'which channel drives the most orders' — because the data was never in the same room."
Five platforms, five disconnected views of the business
Shopify held revenue and order data. GA4 held traffic and behaviour. Google Ads, Bing Ads, and StackAdapt each held their own spend and conversion data. No two platforms shared a common metric definition or a common date logic. Cross-platform analysis required manual exports, and the results were never reliable.
Three ad channels with no unified view of paid efficiency
Google Ads, Bing Ads, and StackAdapt each reported their own spend, conversions, and ROAS — using different attribution windows and different conversion definitions. There was no single view showing which channel was actually delivering the best return, making budget allocation decisions guesswork rather than data-driven decisions.
No customer purchase history visibility
Shopify held all the order data needed to understand repeat purchase behaviour and customer value, but no analysis had been built on top of it. The business had no visibility into which customers were buying again, what the new-vs-repeat revenue split looked like, or which products were driving the most loyal customers.
Manual reporting — time-consuming, error-prone, always stale
Producing a weekly performance report required someone to manually export data from five different platforms, reconcile the numbers in a spreadsheet, and format the output. Reports were always at least a week behind, inconsistent week to week, and consumed hours of time that should have been spent on actual analysis and decisions.
Five sources, two connectors, one warehouse — refreshed daily.
| Data source | Connector | What it powers |
|---|---|---|
| Shopify (orders, customers, products, refunds) | Airbyte → BigQuery | Revenue, orders, AOV, product performance, new vs repeat customers, refund tracking |
| Google Analytics 4 (GA4) | Native Looker Studio connector | Traffic, engagement, ecommerce funnel (View → Cart → Checkout → Purchase), device breakdowns |
| Google Ads | Native Looker Studio connector | Paid search — spend, clicks, CPC, ROAS, campaign and region performance |
| Bing Ads (Microsoft Advertising) | Supermetrics → Looker Studio | Paid search on Bing — spend, clicks, CTR, conversions, same KPI structure as Google Ads for direct comparison |
| StackAdapt | Supermetrics → Looker Studio | Programmatic display, native, and video — spend, impressions, CTR, conversions, audience and placement breakdowns |
Core design principle: every metric is defined once and applied consistently across all dashboards — so revenue, ROAS, and CPA mean the same thing everywhere, regardless of which platform is the source.
Ingestion, transformation, curated views — all automated, all daily.
Shopify → Airbyte → BigQuery
Incremental daily sync of orders, customers, line items, and refunds into raw staging tables. Schema evolution handled automatically.
Ads + GA4 → Supermetrics / Native
Google Ads and GA4 via native connectors. Bing Ads and StackAdapt via Supermetrics. All channels normalised to consistent date keys and metric definitions.
Staging → fact + dimension tables
Each source transformed into analytics-ready fact tables with shared dimensions: date, product, channel, region, and customer.
Curated views in BigQuery
All metric definitions materialised into curated views. Revenue, ROAS, AOV, and CPA defined once — changes propagate to all five dashboards simultaneously.
BigQuery → Looker Studio
Five dashboards connect directly to curated views. One metric definition change propagates everywhere simultaneously. No manual dashboard updates.
-
01
→ Shopify ingestion via Airbyte
Reliable incremental sync — orders, customers, products, and refunds.
Airbyte was selected for Shopify because of its robust incremental sync logic and native handling of Shopify's schema evolution. Raw records are preserved in staging tables for auditability, then transformed into clean fact tables for reporting. Refunds are modelled separately to ensure gross vs net revenue is always distinguishable.
- fct_orders — order-level revenue, AOV, discount, status, channel attribution
- fct_order_items — line-item level for SKU and product performance
- fct_refunds — refund tracking for accurate net revenue
- fct_customers — first order, last order, total orders, total spend per customer
- Incremental sync — new and updated records only, no full daily reload
-
02
→ Paid media ingestion — three channels, one structure
Google Ads, Bing Ads, and StackAdapt normalised to identical metric definitions.
The biggest challenge in multi-channel paid media is that every platform defines metrics differently — attribution windows differ, conversion events differ, and even "clicks" can mean different things. We standardised the ingestion layer so that spend, clicks, impressions, and conversions are defined identically across all three channels, making the blended view genuinely comparable rather than misleading. StackAdapt's programmatic metrics (view-through, completed views) are mapped to consistent fields alongside search click data from Google and Bing.
- Consistent date keys — no timezone offset differences across platforms
- Campaign → ad group → ad hierarchy preserved for all three channels
- Conversion definitions aligned across Google Ads, Bing Ads, and StackAdapt
- fct_paid_google / fct_paid_bing / fct_paid_stackadapt — separate but structurally identical
-
03
→ Transformation and curated views
All metrics defined once — consistent everywhere across all five dashboards.
The transformation layer converts raw source data into clean fact tables and materialises all key metric definitions into curated BigQuery views. Revenue, AOV, net revenue, ROAS, CPA, and click-through rates are each defined in one place. When a definition needs updating — for example, adjusting the ROAS calculation to exclude a channel — it changes once in BigQuery and propagates to every dashboard that uses it. No per-dashboard fixes, no diverging metric definitions across reports.
Shopify is the source of truth.
All revenue, order, AOV, and customer metrics are grounded in Shopify. Platform-attributed revenue from ad channels is available but clearly labelled as platform-reported — Shopify is what drives financial and ecommerce decisions.
GA4 is the source of truth.
Website traffic, session quality, ecommerce funnel completion, and device behaviour are owned by GA4. The View → Add to Cart → Checkout → Purchase funnel is measured and reported from GA4 data using the native Looker Studio connector.
Each ad platform is the source of truth for its own spend.
Google Ads spend from Google. Bing Ads spend from Microsoft Advertising. StackAdapt spend from StackAdapt. Platform-reported figures are taken at face value and clearly attributed. The blended view aggregates these without applying cross-platform attribution, which would introduce false precision.
Five dashboards — ecommerce, web traffic, and three paid channels.
Everything Shopify knows about your revenue — in one place.
The Sales Overview dashboard is the ecommerce team's daily command centre. It surfaces all revenue, order, and customer metrics grounded entirely in Shopify data — gross and net revenue, AOV trends, new vs repeat customer splits, and top products by both revenue and volume.
With refunds modelled separately, gross vs net revenue is always distinguishable. The new vs repeat revenue split makes it immediately clear whether growth is coming from acquisition or retention — a question that was impossible to answer before the warehouse was built.
- Gross revenue, net revenue (after refunds), total discounts applied
- AOV, average orders per customer, total orders over time
- New vs repeat customer count and revenue contribution
- Top products by revenue and units sold
- Region-wise revenue and order distribution
- Revenue trend — daily, weekly, monthly with period comparison
- Sessions, active users, new users, views, avg session duration
- Ecommerce funnel: View → Add to Cart → Checkout → Purchase
- Top traffic source breakdown — organic, paid, direct, referral, social
- Device comparison: desktop vs mobile vs tablet
- Landing page performance — which pages drive the most conversions
Traffic quality, engagement behaviour, and the ecommerce funnel.
The GA4 dashboard answers the question the sales dashboard can't: what is the website actually doing for revenue? It surfaces funnel drop-off points (View → Add to Cart → Checkout → Purchase), traffic quality by source, and device behaviour — giving the team the context to understand whether a revenue dip is a traffic problem or a conversion problem.
Connected via the native Looker Studio GA4 connector, the dashboard refreshes daily without any manual data extraction or spreadsheet reconciliation.
Google Ads Performance
Paid search efficiency — from campaign level down to individual ad, with ROAS and regional breakdown.
- Cost, impressions, clicks, CTR, CPC, ROAS
- Campaign-wise performance and spend trend
- Cost vs impressions + clicks vs cost over time
- Region-wise and age-wise performance breakdown
Bing Ads Performance
Microsoft Advertising — same KPI structure as Google Ads for direct channel-to-channel comparison.
- Spend, impressions, clicks, CTR, CPC, ROAS
- Campaign and ad group performance breakdown
- Conversion volume and cost per conversion
- Performance trend — day over day, week over week
StackAdapt Performance
Programmatic display, native, and video — spend efficiency, audience reach, and conversion performance.
- Spend, impressions, reach, CTR, conversions, cost per conversion
- Campaign and ad type performance breakdown
- View-through vs click-through conversion attribution
- Audience and placement performance analysis
Airbyte, Supermetrics, BigQuery, Looker Studio — each doing what it does best.
Airbyte
Open-source ELT connector for Shopify. Handles incremental syncs of orders, customers, products, and refunds into BigQuery staging tables. Schema evolution is managed automatically — no manual intervention when Shopify updates its API.
Supermetrics
Managed connector for Bing Ads and StackAdapt. Google Ads and GA4 connect via native Looker Studio connectors. All five sources land at consistent grain and refresh cadence — no platform has a different date or metric definition from the others.
Google BigQuery
Central warehouse for all five sources. Fact tables modelled with shared dimensions. Curated views materialise all metric definitions in one place — revenue, ROAS, AOV, and CPA defined once and consistent across every dashboard that reads from the warehouse.
Looker Studio
Five dashboards connecting directly to BigQuery curated views. Daily refresh. Metric definition changes in BigQuery propagate to all dashboards simultaneously. No manual dashboard updates, no stale numbers, no version control issues across reports.
Shopify data you've never been able to act on. Ad spend spread across channels you can't compare.
We unify your ecommerce and paid media data into one warehouse — and build the dashboards that tell you exactly which channels are driving revenue, today.
Book a 30-min call →US · UK · EU · APAC · Billing in USD.