Building a CRO Data Pipeline

Q: Do I need a data warehouse to run good CRO?

Below $3M revenue or 2 tests/month: no. Run on native dashboards. Above that, the cost of fragmented data (analyst time, conflicting numbers, missed insights) exceeds the cost of building the pipeline within 90 days.

Q: BigQuery, Snowflake, or Postgres for CRO data?

BigQuery is the default — GA4 exports natively, costs scale to zero at low volume, and SQL syntax is familiar. Snowflake is a fit if your company already runs on it. Postgres works at the smallest scale but breaks down once events exceed 100M/month.

Q: How long to build a working CRO data pipeline?

Two to four weeks for the first usable version with one SQL-capable engineer. The trap is trying to ship everything before producing any insights — ship a minimal version in week 1, layer on sources in weeks 2–4.

Q: Why not just use GA4 ecommerce data?

GA4 ecommerce undercounts revenue by 5–15% due to ad blockers, consent banners, and cross-device gaps. Use Shopify or Stripe webhooks as the revenue source of truth. Use GA4 for behavior data — what users did, where they came from.

Building a CRO Data Pipeline That Actually Powers Decisions

Most CRO programs run on fragmented data. GA4 lives in one tab, the testing tool in another, Hotjar in a third, Klaviyo behavior in a fourth, Stripe revenue in a fifth. Analysts spend 60% of their week reconciling instead of analyzing.

A real CRO data pipeline collapses all of that into one queryable store. Below is the stack and schema we deploy for clients between $5M and $200M revenue.

1 source Truth — not 5 dashboards

$200–800 Monthly infrastructure cost

2–4 weeks Time to first usable build

60% Analyst time freed up

The Reference Stack

The components that actually matter, with concrete tooling:

Layer	Purpose	Tools
Behavioral source	Site events, sessions, funnels	GA4 with BigQuery export
Transactional source	Revenue, orders, refunds	Shopify webhooks, Stripe webhooks
Engagement source	Email, SMS opens / clicks / conversions	Klaviyo events, Postscript
Qualitative metadata	Recording IDs, heatmap deltas	Hotjar API, FullStory data export
Test source	Assignment + outcomes	VWO / Convert / Statsig API
Warehouse	The truth store	BigQuery (default), Snowflake, Postgres
Transform	Modeling layer	dbt
Activation	Push insights back to tools	Hightouch or Census (reverse ETL)
Reporting	Dashboards	Looker Studio, Hex, Metabase, Mode

For a fuller landscape of what testing tools feed into this, see the A/B testing tools comparison.

Why GA4 Alone Is Not Enough

GA4’s UI is built for surface analysis. The moment you ask “what’s the 90-day repeat purchase rate of users who saw variant B and then opened an email within 7 days?” the UI breaks down. The BigQuery export is the answer — and it’s free up to 1M events/day on the standard tier.

Turn it on inside GA4 Admin → BigQuery Linking. Within 24 hours you have raw event-level data flowing in. Every event, every parameter, every user_pseudo_id. That’s the foundation.

Schema Design: Events vs Entities

The single most common pipeline failure is mixing event tables and entity tables in the same model. Keep them separate.

Event tables (append-only, immutable):

events_raw — every event from every source, untouched
pageviews — derived from events, one row per pageview
add_to_carts
checkouts_started
purchases
email_opens, email_clicks
test_exposures (from your testing tool)

Entity tables (slowly changing):

users — keyed on user_id with merged user_pseudo_id history
sessions — derived from events, one row per session
orders — from Shopify/Stripe
tests — metadata about every running and completed experiment
variants — one row per variant per test

The transform layer (dbt) joins these into reporting models. Common reporting tables:

user_funnel — one row per user, columns for every funnel stage timestamp
test_results — one row per test/variant with conversions, revenue, lift
daily_kpis — one row per day with site-wide CVR, RPV, AOV, sessions

This structure means an analyst can answer “what did users assigned to variant B do across email + site + checkout in the 7 days after exposure?” with one SQL query.

The Truth Source Problem

Every CRO team has a version of this conversation: “Our testing tool says variant B won by 12%, but GA4 shows variant A had higher revenue, and Shopify says total revenue was flat. Which is right?”

All three are right — they’re measuring different things on different attribution windows with different definitions of “conversion.” A pipeline ends this debate by enforcing one definition.

The rule: the data warehouse is the truth. Tool dashboards are previews. Every test result, every KPI, every dashboard reads from the warehouse — never from the source tool directly.

To make this real:

Ingest test assignments from your testing tool via webhook or API.
Ingest orders from Shopify/Stripe (not from GA4 ecommerce events — they undercount by 5–15%).
Join test_exposures to orders in dbt with a defined attribution window (we recommend 7 days for DTC, 30 days for SaaS trials).
Calculate lift, p-value, and CI in the warehouse using a single SQL macro. Every test uses the same math.

This pairs directly with the CRO process framework — without a single source, the “Measure” stage produces conflicting answers.

Bringing in Qualitative Metadata

You don’t ingest raw session recordings into BigQuery — they’re videos. You ingest the metadata: recording_id, rage_click_count, dead_click_count, scroll_depth, page_path, user_pseudo_id.

Both Hotjar (Business plan and up) and FullStory expose this via API. Sync nightly into a session_quality table. Now you can write a query like:

SELECT recording_url
FROM session_quality sq
JOIN test_exposures te USING (user_pseudo_id)
WHERE te.test_id = 'pdp_layout_v3'
  AND te.variant = 'B'
  AND sq.rage_click_count > 0
ORDER BY sq.rage_click_count DESC
LIMIT 20

That returns 20 recordings of frustrated users in variant B. That’s how qualitative meets quantitative — driven from one query, not from manually scrolling Hotjar.

Reverse ETL: Pushing Insights Back to Tools

The pipeline isn’t only inbound. The highest-leverage move is pushing computed audiences back out:

“Users who saw test X variant B and converted” → push to Klaviyo as a segment for win-back email.
“Sessions with rage clicks in checkout” → push to Sentry / Linear as a bug ticket.
“Top 5% RPV users in last 90 days” → push to Meta as a custom audience for lookalike modeling.
“Users assigned to losing variant” → push to your testing tool to exclude from follow-up tests.

Hightouch and Census handle this. Pricing starts around $500/mo and pays for itself fast — a single high-quality lookalike audience usually beats your house Meta CAC by 20–30%.

Costs at Different Scales

Realistic monthly infrastructure cost. Excludes tool subscriptions you already have (GA4, Shopify, Klaviyo).

Stage	Monthly orders	BigQuery	dbt	Reverse ETL	Reporting	Total
Starter	< 5K	$20–80	dbt Core (free)	Hightouch starter $0	Looker Studio (free)	$20–80
Growth	5K–50K	$100–300	dbt Cloud $100	Hightouch $500	Metabase $85	$785–985
Scale	50K–500K	$400–1,200	dbt Cloud $400	Census $800	Hex $400	$2,000–2,800

The starter setup is genuinely viable for early-stage DTC brands. dbt Core + Looker Studio + the free BigQuery tier covers most needs. We’ve shipped this for clients spending $40/mo total.

A Realistic Build Sequence

Two to four weeks for a working pipeline if your team has one decent SQL person. The order matters — don’t try to do all of it at once.

Week 1: Turn on GA4 → BigQuery export. Set up Shopify webhooks landing in BigQuery. Define partition + cluster. Build the first 5 dbt models (events_raw, sessions, users, orders, daily_kpis).

Week 2: Add Klaviyo and your testing tool. Build test_exposures and test_results models. Build the first reporting dashboard against daily_kpis.

Week 3: Add session metadata (Hotjar/FullStory). Build the joined query workflow. Migrate at least one running test to warehouse-based measurement.

Week 4: Set up reverse ETL. Push the first audience to Klaviyo or Meta. Document everything. Train the team on the SQL patterns.

After this, your automated CRO reporting builds on top — every dashboard, every weekly report, every test write-up reads from the same store.

When You Don’t Need This

A data pipeline is overkill if:

You’re running fewer than 2 tests per month (you don’t have enough surface area).
Revenue is under $1M (the time spent building exceeds the analyst hours saved).
You don’t have a single person who can write SQL (the pipeline becomes a graveyard).

In those cases, run on the native dashboards and revisit at $3M+. The CRO maturity model post maps where a data pipeline fits in the team’s evolution.

For agencies, owning the data pipeline is non-negotiable — it’s the deliverable that compounds beyond the engagement. See CRO agency vs in-house for how that affects the build/buy decision.

Frequently Asked Questions

Do I need a data warehouse to run good CRO?

Below $3M revenue or 2 tests/month: no. Run on native dashboards. Above that, the cost of fragmented data (analyst time, conflicting numbers, missed insights) exceeds the cost of building the pipeline within 90 days.

BigQuery, Snowflake, or Postgres for CRO data?

BigQuery is the default — GA4 exports natively, costs scale to zero at low volume, and SQL syntax is familiar. Snowflake is a fit if your company already runs on it. Postgres works at the smallest scale but breaks down once events exceed 100M/month.

How long to build a working CRO data pipeline?

Two to four weeks for the first usable version with one SQL-capable engineer. The trap is trying to ship everything before producing any insights — ship a minimal version in week 1, layer on sources in weeks 2–4.

Why not just use GA4 ecommerce data?

GA4 ecommerce undercounts revenue by 5–15% due to ad blockers, consent banners, and cross-device gaps. Use Shopify or Stripe webhooks as the revenue source of truth. Use GA4 for behavior data — what users did, where they came from.

Real-World Pipeline Examples

Example 1: $10M eCommerce Brand

Stack:

GA4 → BigQuery (free tier, 1M events/day)
Shopify webhooks → BigQuery
Klaviyo events → BigQuery (via Fivetran)
VWO testing → BigQuery (via API)
Looker Studio dashboards

Cost: $300/month (BigQuery $80, Fivetran $200, other free) Build time: 2 weeks Output: Daily dashboard showing CVR by test variant + email segment + traffic source

Example 2: $50M SaaS