The client — a Shopify-based e-commerce business selling across European markets — was managing Google Ads, SEO, and conversion rate optimisation entirely through manual processes spread across four disconnected tools. Revenue data lived in Shopify. Ad spend lived in Google Ads. Behavioural funnel data sat in GA4. UX signals were buried in Microsoft Clarity. None of these platforms talked to each other.
Without a unified data layer, the team had no way to answer the most important question in performance marketing: which campaigns, keywords, and landing pages are actually driving profitable revenue — and which are silently burning budget?
Four Siloed Data Sources
No Unified ROAS View
Reactive, Weekly Reporting
No Data Infrastructure for AI
Shopify · Ads · GA4 · Clarity
8+ Sync Workflows
5-Layer Warehouse
Real-Time Reports
Claude + OpenAI (Ph.2)
Google Cloud
Shopify
Laravel
Google Analytics
Microsoft
n8n
Five structured datasets: Raw (exact API responses, never modified), Core (cleaned, deduplicated, standardised with tenant IDs), Reporting (pre-aggregated daily views by campaign, keyword, product), Config (one row per client), and Logs (full pipeline audit trail).
8+ automated workflows handling scheduled sync, idempotent upserts, 12-month historical backfill on setup, data quality checks (missing values, duplicates, anomalies), and full error logging per run. Shopify syncs every 1–3 hours; Ads, GA4, and Clarity sync daily.
Real-time reporting UI showing revenue by campaign, ROAS by ad group, GA4 funnel performance (sessions → add_to_cart → checkout → purchase), and Microsoft Clarity UX signals (scroll depth, rage clicks, quick backs) — all in one interface with up to 1-hour data freshness.
A single managed_entities table drives the entire system: timezone, currency, API credentials, active status, and all tenant IDs in one row per company. New client onboarding requires one INSERT — the pipelines, transforms, and dashboard configure themselves automatically.
Claude will analyse patterns across the unified dataset — underperforming campaigns, wasted budget, growth opportunities — while OpenAI translates insights into executable actions. A Notion-based approval panel gives the client full review before any change goes live.
Data Sources
BigQuery Layers
Sync Pipelines
Target ROAS
