🛒 Ecommerce · Marketing Analytics

Unified Ecommerce + Marketing Analytics Platform

Client: Hoist · Stack: Shopify · Airbyte · Supermetrics · BigQuery · Power BI

Data Sources Unified

Daily

Automated Refresh

Dashboards Delivered

Single Source of Truth

01 / Project Overview

What We Built & Why

For Hoist, we implemented a centralized analytics setup that brings ecommerce, paid marketing, and website analytics into one reporting layer. The core goal was to eliminate siloed reporting and give stakeholders a single, consistent view of performance across every channel.

📦

Daily Sales Performance

Sales, orders, AOV, and product performance from Shopify as the revenue source of truth.

🌐

Website Traffic Behavior

Sessions, users, engagement rate, landing pages, and funnel insights via GA4.

📣

Paid Media Performance

Google, Microsoft & Meta Ads performance with spend, clicks, conversions in one place.

📊

Cross-Channel Efficiency

Blended ROAS, CPA, and cross-platform contribution trends for smarter budget decisions.

02 / Data Sources & Integrations

5 Sources, One Warehouse

Data Source	Integration Method	Refresh	Purpose
🛍️ Shopify Orders, customers, products, refunds	Airbyte → BigQuery	Daily	Revenue + order + customer analytics
📈 Google Analytics (GA4) Traffic, engagement, funnels	Google Native Connector	Daily	Traffic, engagement, user behavior, funnels
🔍 Google Ads Campaigns, spend, conversions	Google Native Connector	Daily	Paid search performance + spend + conversions
🪟 Microsoft Ads Bing paid search	Supermetrics	Daily	Paid search performance + spend + conversions
📱 Meta Ads Facebook + Instagram	Supermetrics	Daily	Paid social performance + spend + conversions

Key design principle: Centralize everything in BigQuery with the same grain and same metric definitions — so dashboards never conflict across teams. Shopify is the source of truth for revenue/orders. Ad platforms own spend/impressions/clicks. GA4 owns on-site behavior and session funneling.

03 / Pipeline Architecture

End-to-End Data Flow

🛍️

Shopify

Airbyte sync

→

📣

Ad Platforms

Supermetrics

→

📥

Staging

BQ raw tables

→

⚙️

Modeling

Fact/Dim layer

→

📊

Power BI

Dashboards

🛍️ Shopify Ingestion via Airbyte

·Daily extraction of orders, line items, customers, products/variants, refunds & discounts

·Reliable incremental sync with schema evolution handling

·Easy monitoring and re-sync capability

·Normalized staging tables → analytics-ready views

📣 Marketing Ingestion via Supermetrics

·GA4 traffic, engagement & conversions (daily)

·Google & Microsoft Ads: campaign → ad-group → ad level

·Meta Ads: campaign / ad set / ad — spend, reach, conversions

·Standardized date keys, UTM mapping & normalized metrics

04 / Data Warehouse

BigQuery Data Modeling

🛍️ Ecommerce Facts

fct_orders

fct_order_items

fct_refunds

📣 Marketing Facts

fct_paid_google_ads

fct_paid_microsoft_ads

fct_paid_meta_ads

🌐 Web Analytics Facts

fct_ga4_acquisition

fct_ga4_engagement

📐 Dimensions

dim_date

dim_campaign

dim_product

dim_geo

🛍️ Shopify is the source of truth for revenue & orders — not ad platform conversion values.

📣 Ad Platforms are the source of truth for spend, impressions & clicks — reported natively per platform.

🌐 GA4 is the source of truth for on-site behavior and session-based funnel analysis.

05 / Dashboards Delivered

4 Reporting Views for Stakeholders

Shopify Ecommerce

✓ Gross & net sales

✓ Orders, AOV, items/order

✓ Discounts & refunds trend

✓ Top products by revenue & qty

✓ New vs returning customers

GA4 Website Performance

✓ Users, sessions, engagement rate

✓ Source / medium & channel

✓ Landing page performance

✓ Mobile vs desktop conversion

✓ Conversion journey indicators

Paid Media (Per Platform)

✓ Google Ads: spend, CPC, ROAS

✓ Microsoft Ads: same KPI structure

✓ Meta: reach, CTR, CPA, creatives

✓ CPA & conversion tracking

✓ Campaign → ad-level drill-down

Blended Cross-Channel

✓ Total spend (all paid channels)

✓ Total conversions (platform-reported)

✓ Blended CPA & blended ROAS

✓ Channel comparison over time

✓ Contribution trend analysis

06 / Business Impact

Measurable Value Delivered

🤖

Automated daily pipeline eliminated all manual export & reporting work across 5 platforms.

🎯

Single trusted view of ecommerce + marketing performance — no more conflicting numbers across teams.

⚡

Faster weekly budget allocation decisions with clear ROAS & CPA comparisons by channel.

🔍

Improved visibility into campaign inefficiencies, product-level winners & traffic quality by source.

🔧

Modular architecture — adding a new channel is a repeatable pattern with minimal engineering effort.

07 / Tech Stack

Technologies Used

🛍️

Shopify

Ecommerce data source

🔄

Airbyte

Shopify → BigQuery sync

📡

Supermetrics

Ads + GA4 ingestion

🗃️

BigQuery

Central data warehouse

📊