Case study — ecommerce data warehouse

Ecommerce Data Warehouse

Marketing attribution is usually a black box you rent: a vendor hands you one number for what each channel is worth and you trust it. I've worked inside that box — my agency years included a proprietary ad server, DMP, and customer graph built in house — so I rebuilt it in SQL. This warehouse models the full customer journey and computes attribution five different ways (including Markov removal-effect and Shapley values), then shows them side by side, so the answer to 'what is Google actually worth' comes with its own audit trail.

dbt models

attribution models compared

Markov + Shapley

in pure SQL

glass-box

owned, not rented

in practice

What it's actually like to use

The recurring question in ecommerce is which channels actually drive revenue, and the honest answer is "it depends how you count." A last-touch model and a first-touch model can disagree by a factor of two on what a channel is worth. Most teams get one model from a vendor tool and never see the assumptions underneath it.

This warehouse ingests the store's orders and the ad-platform journey data, reconstructs each customer's full path, and attributes revenue five ways: first-touch, last-touch, linear, a Markov removal-effect model, and Shapley values. The marts put them next to each other, per channel and per attribution window. So instead of one number to trust, you see the range and the reason for it. That's the difference between a budget decision made on faith and one made with the model laid bare.

It's the data layer behind the growth dashboard. The interesting engineering isn't the charts, it's that the two hard attribution models are computed from scratch in SQL, no black box and no Python ML dependency.

The practical outcome: budget decisions at a brand that grew ~10x are made against five attribution models instead of one vendor's number.

architecture

How it's put together

code-first warehouse — sources to answers

sources

raw data

Shopify — orders, customers, refundsad platform — spend + journey touchpoints

ETL — Cloud Run jobs, orchestrated nightly by GCP Workflows

staging

cleaned views

stg_shopify__*stg_triple_whale__*

Thin, typed views over the raw extracts — one model per source table.

intermediate

the attribution math — pure SQL

Markov: transition matrix → removal effectShapley: permutations → marginal contribution

Journeys parsed from touchpoint arrays. Shapley permutes channel orderings — exhaustive at ≤5 channels, Monte Carlo (100 samples) beyond.

marts

tables the business reads

5-model attribution comparisoncohort LTV + retentionCAC / ROAS / payback by channel

First-touch · last-touch · linear · Markov removal · Shapley — side by side, per channel and per window.

served via FastAPI

Powers the growth dashboard — the operating picture, backed by attribution you can open up and audit.

▪ ~54 dbt models▪ staging = views, marts = tables▪ BigQuery

Code-first, layered in dbt

Every transformation is version-controlled SQL, layered the dbt way: staging models are thin typed views over the raw Shopify and ad-platform extracts, intermediate models hold the attribution math, and marts materialize as tables the API reads. ~54 models, each testable in isolation.

Markov removal-effect, in SQL

Customer journeys are parsed from touchpoint arrays into a transition matrix: the probability of moving from one channel to the next. Each channel's worth is its removal effect: how much total conversion probability drops when you take it out of the graph, translated into revenue at risk.

Shapley values, exhaustive or sampled

The Shapley model averages each channel's marginal contribution across every ordering of a journey's channels. Journeys with ≤5 channels get all permutations exactly; larger ones fall back to Monte Carlo sampling (100 random permutations): accuracy where it's cheap, an estimate where exhaustive would explode.

Five models, one comparison table

A comparison mart lines up first-touch, last-touch, linear, Markov, and Shapley per channel. Seeing them together is the actual product. It turns "the tool says X" into "here's how much X depends on the model, and which channels hold up across all of them."

Cohorts, LTV, and acquisition economics

Beyond attribution: cohort retention and LTV curves (daily, weekly, yearly), and acquisition metrics (CAC, ROAS, payback period by channel) joining ad spend to first-touch revenue. Cohort-maturity bias is handled explicitly, not swept under a blended average.

Orchestrated and served

ETL runs as Cloud Run jobs orchestrated nightly by GCP Workflows; dbt rebuilds the marts; a FastAPI service exposes them to the dashboard. The whole path from raw order to attributed dollar runs unattended.

engineering highlights

The parts I'm proud of

Shapley values in SQL: exhaustive where it's cheap, sampled where it isn't

Shapley is the rigorous way to split credit across channels, but the number of orderings explodes with journey length. The model handles that with a branch built into the permutation step: compute every ordering when there are few channels, and switch to Monte Carlo sampling when there are many, so it stays exact where it can and bounded where it can't.

Fig. — int_shapley__permutations (strategy)

-- Journeys with ≤5 unique channels: generate ALL permutations (exhaustive)
-- Journeys with >5 unique channels: Monte Carlo sampling (100 random permutations)
--
-- Journey [google, facebook, klaviyo] → 6 orderings; for each, measure what
-- each channel ADDS when it joins the coalition → average = its Shapley value

Removal effect from a transition matrix

The Markov model treats the journey as a graph and asks a counterfactual: if this channel vanished, how much conversion probability goes with it? That removal effect, scaled by total conversion value, becomes the channel's revenue at risk: a credit assignment grounded in the actual path structure, not a fixed rule like "last click wins."

Fig. — int_markov_chains__transition_matrix (shape)

-- parse each order's touchpoint sequence (chronological) from the journey array
unnest(json_extract_array(attribution_linear_all)) as touchpoint with offset
-- → P(channel_a → channel_b) transition matrix
-- → remove a channel, recompute conversion probability, diff = its importance

It disagrees with itself on purpose

Running five attribution models isn't indecision. It's the honest representation of an unknowable ground truth. A channel that looks strong under last-touch but weak under Shapley is telling you something real about where it sits in the funnel. The comparison mart makes that visible instead of hiding it behind one confident number.

Honest about cohort maturity

LTV by cohort is easy to get wrong: young cohorts haven't had time to spend, so a naive average makes recent months look worse than they are. The cohort models account for maturity explicitly rather than blending immature and mature cohorts into a misleading single curve.

stack

Built with

BigQuery · dbt (~54 models: staging views, intermediate math, marts as tables) · GCP Workflows + Cloud Run for orchestrated ETL · FastAPI serving the marts · sources: Shopify (orders, customers, refunds) and an ad-platform journey feed (spend + touchpoints).

Built for a DTC apparel brand; the client and its figures are abstracted here. The modeling approach, attribution methods, and pipeline are shown as built. This is the warehouse behind the DTC growth dashboard demo; the business it instruments is the flagship case study.

← All projects