Five data sources.
One trusted reporting layer.
Hoist had five separate reporting tools — Shopify, GA4, Google Ads, Microsoft Ads, and Meta Ads — each telling a different story. We built a centralised BigQuery warehouse fed by Airbyte and Supermetrics, modelled the data into a single consistent layer, and delivered six dashboards covering ecommerce, website behaviour, paid media performance, and blended cross-channel ROAS.
Five tools. Five different numbers. Nobody trusted any of them.
"Every team had their own dashboard. None of the numbers matched. The first thing we did was agree on what revenue actually means — then build everything on top of that."
Siloed reporting — five tools, five versions of the truth
Ecommerce data lived in Shopify. Web analytics in GA4. Paid search in Google Ads and Microsoft Ads. Paid social in Meta Ads. Each tool had its own metrics, its own date handling, and its own definition of a conversion. Reports never agreed across teams.
No reliable cross-channel efficiency view
ROAS, CPA, and blended paid-media performance couldn't be calculated reliably because spend data (ad platforms) and revenue data (Shopify) lived in separate systems with no common join key. Budget allocation decisions were made on incomplete, platform-specific numbers.
Manual exports — slow, error-prone, and never current
The reporting workflow required someone to manually export CSVs from each platform, reconcile them in a spreadsheet, and update the numbers. This happened weekly at best, was error-prone, and meant decisions were always made on stale data.
Metric definitions drifted across teams
Revenue meant different things to the ecommerce team, the marketing team, and finance. Sessions, conversions, and ROAS were calculated differently in every dashboard. Adding a new data source made the problem worse, not better.
Five sources, two connectors, one warehouse — refreshed daily.
| Data source | Connector | Refresh | What it powers |
|---|---|---|---|
| Shopify (orders, customers, products, refunds) | Airbyte → BigQuery | Daily | Revenue, orders, AOV, product performance, customer analytics |
| Google Analytics 4 (GA4) | Google Native Connector | Daily | Traffic, engagement, user behaviour, funnel insights, session-level attribution |
| Google Ads | Google Native Connector | Daily | Paid search performance — spend, clicks, CPC, conversions, CPA, ROAS |
| Microsoft Ads | Supermetrics → BigQuery | Daily | Paid search performance — same KPI structure as Google for direct comparison |
| Meta Ads (Facebook + Instagram) | Supermetrics → BigQuery | Daily | Paid social — spend, reach, impressions, CTR, conversions, CPA, creative performance |
Core design principle: everything lands in BigQuery at the same grain, with the same metric definitions — so no two dashboards can ever contradict each other.
End-to-end pipeline — ingestion through to analytics-ready tables.
Shopify → Airbyte
Incremental daily sync of orders, line items, customers, products, and refunds into raw BigQuery tables.
Ads + GA4 → Supermetrics / Native
Daily extraction of all paid media and web analytics into standardised staging tables — consistent date keys and normalised metrics.
Staging → fact tables
Each source normalised into analytics-ready fact tables with shared dimensions for date, campaign, product, and geo.
Fact tables → curated views
Reporting views materialise the metric definitions — revenue, ROAS, CPA, AOV — calculated once, consistently, for every dashboard.
BigQuery → dashboards
Six dashboards connect directly to curated views. One change to a metric definition propagates everywhere, instantly.
-
01
→ Shopify ingestion via Airbyte
Reliable incremental sync with schema evolution handling.
Airbyte was chosen for Shopify specifically because of its robust incremental sync patterns and ability to handle Shopify's schema evolution over time. Orders, line items, customers, products, and refunds are extracted daily into raw staging tables — preserving the original payload for auditability.
- fct_orders — order-level revenue, AOV, discount, refund
- fct_order_items — line-item level for SKU and product analysis
- fct_refunds — refund tracking for net revenue calculations
- Incremental sync — no full reload; only new and updated records
- Schema drift handled automatically via Airbyte schema evolution
-
02
→ Marketing ingestion via Supermetrics + native connectors
All three paid channels normalised to the same grain and metric definitions.
The key challenge with multi-platform paid media is that each platform names things differently. We standardised across all three channels so cross-platform comparison is genuinely meaningful:
- Consistent date keys across all platforms — no off-by-one timezone issues
- Campaign → ad group → ad hierarchy preserved at each level
- Spend, clicks, impressions, and conversions defined identically per platform
- UTM mapping applied where available for GA4 → ad platform attribution joins
- fct_paid_google_ads / fct_paid_microsoft_ads / fct_paid_meta_ads — separate, comparable
-
03
→ Source-of-truth rules
One definition per metric — agreed upfront, enforced in the warehouse.
The most important governance decision was agreeing on which system owns which number — and encoding that as a rule in the warehouse rather than leaving it to individual reports. This eliminated the conflicts that had plagued the team's previous reporting setup.
Shopify is the source of truth.
All revenue, order, and customer metrics are grounded in Shopify. Ad platforms report their own attributed revenue but Shopify is what finance and ecommerce teams use to measure the business.
Ad platforms are the source of truth.
Each ad platform reports its own spend and media metrics. These are taken at face value — Google Ads spend from Google, Meta spend from Meta. No cross-platform attribution applied.
GA4 is the source of truth.
Website sessions, users, engagement, and funnel behaviour are owned by GA4. This is the system used for traffic analysis, landing page performance, and conversion journey insights.
Fact tables and shared dimensions — modular by design.
One fact table per source, one shared dimension layer.
Each data source lands in its own fact table. Shared dimensions — date, campaign, product, geo — are defined once and referenced everywhere. This means adding a new data source (a new ad platform, a new ecommerce store) is a repeatable, low-effort pattern: new connector → new staging table → new fact table → inherits all existing dashboards automatically.
The dimension tables are the enforcement layer for consistency. dim_campaign normalises campaign naming across Google, Microsoft, and Meta into a single comparable view. dim_date ensures every table uses the same calendar logic. dim_product maps Shopify SKUs for cross-metric product analysis.
- fct_orders — order-level revenue, AOV, discount, status
- fct_order_items — line-item level for product/SKU analysis
- fct_refunds — refund tracking and net revenue
- fct_paid_google_ads — campaign → ad group → ad
- fct_paid_microsoft_ads — campaign → ad group → ad
- fct_paid_meta_ads — campaign → ad set → ad
- fct_ga4_acquisition — source/medium, channel, campaign
- fct_ga4_engagement — sessions, users, engagement rate
- dim_date — consistent calendar logic across all fact tables
- dim_campaign — normalised naming across all three paid platforms
- dim_product — Shopify product/variant mapping
- dim_geo — country/region where available across sources
Adding a new source changes one table. Nothing else breaks.
The warehouse is built so that every new data source plugs into the same pattern. The dimension tables, the metric definitions, and the dashboard views don't change — only a new fact table is added.
This was a deliberate architectural decision: Hoist's channel mix was likely to evolve, and the warehouse needed to grow without requiring a rebuild each time.
Six dashboards — ecommerce, web, paid media, and blended performance.
Shopify Ecommerce Performance
The ecommerce team's daily view — revenue, orders, and product performance grounded entirely in Shopify.
- Gross and net sales (with refunds modelled)
- Orders, AOV, items per order
- Discounts and refunds trend
- Top products and SKUs by revenue and quantity
- New vs returning customer split
GA4 Website Performance
Traffic quality, engagement behaviour, and the conversion journey — owned by GA4.
- Users, sessions, engagement rate and time on site
- Source/medium and channel group performance
- Landing page performance breakdown
- Device comparison: mobile vs desktop
- Conversion journey indicators
Google Ads Performance
Paid search performance from campaign level through to individual ad — with ROAS where conversion value is available.
- Spend, clicks, CPC, impressions
- Conversions, CPA, ROAS
- Campaign → ad group → ad drilldown
- Performance trend over time
Microsoft Ads Performance
Same KPI structure as Google Ads — designed for direct side-by-side comparison of paid search channels.
- Spend, clicks, CPC, conversions, CPA
- Campaign-level to ad-level breakdown
- Consistent definitions enable Google vs Microsoft comparison
Meta Ads Performance
Paid social performance across Facebook and Instagram — including reach, frequency, and creative-level analysis.
- Spend, reach, impressions, frequency, CTR
- Conversions, CPA, ROAS
- Campaign → ad set → ad breakdown
- Creative performance where available
Blended Cross-Channel Performance
The leadership view — total paid spend, total conversions, and blended efficiency across all three channels in one place.
- Total spend across Google + Microsoft + Meta
- Total conversions (platform-reported, clearly defined)
- Blended CPA and blended ROAS
- Channel contribution and share of spend over time
Airbyte, Supermetrics, BigQuery — the right tool for each job.
Airbyte
Open-source ELT connector platform. Used for Shopify because of its reliable incremental sync, schema evolution handling, and easy monitoring. Orders, customers, products, and refunds extracted daily without custom code.
Supermetrics
Managed connector for Microsoft Ads and Meta Ads. Native Google connectors used for GA4 and Google Ads. All channels land in BigQuery at the same grain and refresh schedule.
Google BigQuery
Centralised warehouse for all five sources. Fact tables modelled with shared dimensions. Curated views materialise metric definitions — one source of truth for every number across all dashboards.
Power BI / Looker Studio
Six dashboards connecting directly to curated BigQuery views. Daily refresh. One metric definition change in BigQuery propagates to every dashboard simultaneously — no manual updates.
Running five separate dashboards that never agree with each other?
We unify your ecommerce, paid media, and web analytics into one warehouse and one reporting layer — so every team works from the same numbers, every day, automatically.
Book a 30-min call →US · UK · EU · APAC · Billing in USD.