Five platforms unified.
Ten customer cohorts surfaced.
Dreamiere had revenue data in Shopify, traffic in GA4, and paid campaigns spread across Google, Meta, and Bing — with no way to connect them into a single view. We built a centralised BigQuery warehouse fed by Airbyte and Supermetrics, delivered six Looker Studio dashboards covering ecommerce performance, paid media efficiency, and user behaviour — and added an advanced RFM customer segmentation analysis that maps the entire customer base into ten actionable loyalty cohorts.
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 three ad accounts. There was no way to answer a simple question like 'which channel drives the best customers' — 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, Meta Ads, and Bing Ads 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 it was never reliable.
No customer lifecycle visibility — can't identify who's about to churn
Shopify held all the order data needed to understand customer health, but no lifecycle analysis had been built on top of it. The business had no visibility into which customers were loyal, which were at risk of churning, and which had already gone quiet. Retention and win-back campaigns were being sent without any segmentation logic.
Cross-channel paid efficiency was impossible to compare
Google Ads, Meta Ads, and Bing Ads each reported their own ROAS, CPA, and conversion counts — using different attribution windows, different conversion definitions, and different date ranges. There was no single view showing which channel was actually delivering the best return, making budget allocation decisions arbitrary.
Manual reporting — time-consuming, error-prone, always stale
Producing a weekly performance report required someone to manually export data from five different platforms, paste them into a spreadsheet, reconcile the numbers, and format the output. Reports were always at least a week behind, inconsistent week to week, and consumed hours of analyst time that should have been spent on actual analysis.
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, RFM scoring |
| Google Analytics 4 (GA4) | Native Looker Studio connector | Traffic, engagement, ecommerce funnel (View → Cart → Checkout → Purchase), device + age breakdowns |
| Google Ads | Native Looker Studio connector | Paid search — spend, clicks, CPC, ROAS, campaign + region + age performance |
| Meta Ads (Facebook + Instagram) | Supermetrics → Looker Studio | Paid social — spend, reach, CTR, conversions, platform + placement + audience breakdowns |
| Bing Ads | Supermetrics → Looker Studio | Paid search + display on Bing — same KPI structure as Google Ads for direct channel comparison |
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, RFM scoring — 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. Meta Ads and Bing Ads 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.
RFM scoring in BigQuery
SQL-based RFM model runs daily over the Shopify customer and order data — scoring every customer on Recency, Frequency, and Monetary dimensions and assigning a cohort.
BigQuery → Looker Studio
Six dashboards connect directly to curated views and the RFM output table. One metric definition change propagates everywhere simultaneously.
-
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, Meta Ads, and Bing Ads 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.
- 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, Meta Ads, and Bing Ads
- fct_paid_google / fct_paid_meta / fct_paid_bing — separate but structurally identical
-
03
→ RFM scoring logic
Every customer scored daily on three dimensions — cohort assigned automatically.
The RFM model runs as a scheduled SQL query over fct_customers and fct_orders. Each customer receives a score for Recency (days since last order), Frequency (total number of orders), and Monetary (total lifetime spend). Scores are combined into a composite RFM score and mapped to one of ten named cohorts. The output is a daily-refreshed table that feeds directly into the RFM dashboard in Looker Studio — enabling customer-level drilldowns and cohort-level exports for CRM campaigns.
Shopify is the source of truth.
All revenue, order, AOV, and customer lifetime value 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. Meta spend from Meta. Bing spend from Bing. Platform-reported figures are taken at face value and clearly attributed. The blended dashboard aggregates these without applying cross-platform attribution, which would introduce false precision.
RFM analysis — every customer scored, every cohort actionable.
Three scores. One customer health picture.
RFM analysis scores every customer on three dimensions drawn directly from their order history. Recency measures how long it's been since their last purchase — recent buyers are more likely to convert again. Frequency measures how many times they've purchased — frequent buyers are more loyal and more valuable. Monetary measures total lifetime spend — it identifies the customers who drive disproportionate revenue.
Each dimension is scored on a scale, and the combined RFM score determines which of ten cohorts a customer belongs to. Cohorts are recalculated daily as new orders arrive, so the segmentation is always current — not a static snapshot from last quarter.
- Recency — days since last order (lower = better; recent buyers score high)
- Frequency — total number of orders placed (higher = more loyal)
- Monetary — total lifetime spend across all orders (higher = more valuable)
- Combined RFM score → cohort assignment, recalculated daily
- Customer-level drilldown — exportable list per cohort for CRM targeting
- Revenue contribution — what share of total revenue each cohort generates
- Customer count per cohort — how many customers are in each segment
- Revenue contribution per cohort — which segments drive the most value
- AOV and orders per customer — loyalty metrics by cohort
- Recency explorer — average days since last order by cohort
- Exportable customer lists — ready for CRM win-back and retention campaigns
- Cohort trend over time — are Champions growing or shrinking month over month?
Knowing your cohorts is only useful if you act on them differently.
The RFM output was designed from the start to be CRM-ready. Each cohort has an exportable customer list that can be uploaded directly into email, SMS, or paid retargeting tools. Champions get loyalty and early-access messaging. At Risk customers get win-back offers. Hibernating customers get reactivation sequences. Lost customers get a final attempt or are suppressed from campaigns entirely.
This is the layer that moves the needle on retention and customer lifetime value — and it's the part that was entirely invisible before the warehouse was built. The data was always there in Shopify. The analysis just hadn't been built on top of it.
Champions
Bought recently, buy often, and spend the most. The most valuable segment. Reward and retain.
Loyal
Buy regularly and spend well. Less recent than Champions but highly reliable. Keep engaged with loyalty offers.
Potential Loyalists
Recent buyers with above-average frequency. Strong signs of becoming loyal. Nurture with targeted follow-up.
Promising
Recent but infrequent. Bought recently but haven't returned yet. Early-stage loyalty building required.
Recent
Just purchased for the first time. High recency score but no frequency history yet. Onboarding sequences are critical here.
Needs Attention
Were once frequent buyers but recency is dropping. Engagement is cooling. Re-engagement campaigns needed before they slide further.
About to Sleep
Low recency, below average frequency and spend. Showing early churn signals. A targeted offer now may pull them back.
At Risk
Were once valuable customers — good frequency and spend — but haven't purchased in a long time. Win-back campaigns required urgently.
Hibernating
Low scores across all three dimensions. Last purchase was long ago. Minimal recent activity. Final re-engagement attempt or suppression from active campaigns.
Lost
Lowest scores across all dimensions. Have not responded to re-engagement. Typically removed from active marketing to protect deliverability and reduce spend.
Six dashboards — ecommerce, web, paid media, and customer intelligence.
Shopify Ecommerce Overview
The ecommerce team's daily view — revenue, orders, and customer behaviour grounded entirely in Shopify data.
- Gross revenue, net revenue (after refunds), total discounts
- AOV, average orders per customer, total orders
- Revenue over time — daily, weekly, monthly trend
- New vs repeat customer distribution + revenue split
- Top products by revenue and quantity
- Region-wise revenue and order distribution
GA4 Website Performance
Traffic quality, engagement behaviour, and ecommerce funnel — what the website is doing for revenue.
- 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
- Age-wise engagement and conversion analysis
Google Ads Performance
Paid search efficiency — from campaign level to individual ad, with ROAS and age/region breakdowns.
- Cost, impressions, clicks, CTR, CPC, ROAS
- Campaign-wise performance and trend analysis
- Cost vs impressions + clicks vs cost over time
- Region-wise and age-wise performance breakdown
Meta Ads Performance
Paid social across Facebook and Instagram — spend, reach, and conversion efficiency by platform and placement.
- Ad spend, impressions, reach, CTR, conversions, cost per conversion
- Campaign-wise performance breakdown
- Platform comparison: Facebook vs Instagram
- Placement analysis: Feed, Stories, Reels, Marketplace
- Audience breakdown: age, gender, region
Blended Paid Media — Cross-Channel
Unified paid view across Google Ads, Meta Ads, and Bing Ads — total spend, total conversions, blended efficiency.
- Total spend across all three paid channels
- Total clicks and total conversions (consistent definition)
- Blended ROAS and blended cost per conversion
- Channel contribution — Google vs Meta vs Bing share of spend and conversions
- Spend vs revenue trend for budget shift signals
RFM Customer Segmentation
Advanced customer intelligence — ten loyalty cohorts, daily-refreshed, exportable for CRM and retention campaigns.
- Customer distribution across all ten RFM cohorts
- Revenue contribution per cohort — which segments drive the most value
- Orders, AOV, and customer value metrics by cohort
- Recency explorer — average days since last order per cohort
- Exportable customer-level lists for targeted CRM activation
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 Meta Ads and Bing Ads. 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 plus the RFM scoring output. Fact tables modelled with shared dimensions. The RFM SQL model runs as a scheduled query — daily, automated, no manual trigger. Curated views materialise all metric definitions in one place.
Looker Studio
Six 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 you can't compare across channels.
We unify your ecommerce, paid media, and customer data into one warehouse — and build the RFM segmentation that tells you exactly which customers need what, today.
Book a 30-min call →US · UK · EU · APAC · Billing in USD.