Every worked example in the rest of this book rests on three tables: who was assigned to which arm, who was actually exposed to the treatment, and the metric events joined against both. Conflating any two of them is where most SQL experiment-analysis bugs actually come from — not from getting a formula wrong, but from joining the wrong table to the wrong thing.
Three Tables, Not One
An assignment table records, for every user who entered an experiment, which variant they were assigned to and when. This is the table randomization actually wrote to. Every user who was ever assigned belongs in it — including ones who never came back, never triggered the feature, or churned the next day.
An exposure table records who was actually exposed to whatever the treatment changes — the moment a feature flag was evaluated for a user, or the moment a user actually reached the code path a variant controls. Assignment and exposure are not the same event. A user can be assigned to the treatment arm and never actually see the treatment, because they never opened the screen it lives on.
A metric fact table is the ordinary event or transaction data an experiment measures against — orders, sessions, revenue events — indexed by user and timestamp, with no knowledge of experiments at all. It's the same fact table product analytics already runs on; an experiment analysis just joins it against the two tables above.
| Table | Grain | Key columns |
|---|---|---|
| experiment_assignment | One row per user per experiment | experiment_id, user_id, variant, assigned_at |
| experiment_exposure | One row per user's first qualifying exposure | experiment_id, user_id, first_exposed_at |
| fact_orders (a metric fact table) | One row per order event | user_id, event_time, metric_value |
Why the Distinction Actually Matters
It's tempting to analyze only the users who show up in the exposure table — after all, why include users the treatment could never have touched? This is exactly the mistake covered in Chapter 1 of the causal inference book: filtering down to "exposed" users breaks randomization, because whatever determines exposure (opening a particular screen, triggering a particular code path) is rarely itself randomized. The assignment table is what preserves the randomization guarantee. The exposure table is diagnostic information about compliance — useful for understanding intent-to-treat versus treatment-on-the-treated gaps, never a filter to apply before computing the headline effect.
The practical rule: the primary analysis always joins the metric fact table to the assignment table, keyed on every assigned user, regardless of whether they were ever exposed. The exposure table is a secondary join, used only to answer secondary questions like exposure rate or compliance — never to decide who's in the denominator of the main result.
Building the User-Level Table
Every later chapter in this book starts from the same shape: one row per assigned user, with their variant and their aggregated metric value over the experiment window. Here's the SQL that builds it, for a three-arm coupon experiment measuring revenue:
with assignment as (
select experiment_id, user_id, variant, assigned_at
from experiment_assignment
where experiment_id = 'coupon_test_2026_06'
),
metrics as (
select user_id, event_time, metric_value
from fact_orders
where metric_name = 'revenue'
),
user_metrics as (
select
a.user_id,
a.variant,
coalesce(sum(m.metric_value), 0) as revenue
from assignment a
left join metrics m
on m.user_id = a.user_id
and m.event_time >= a.assigned_at
and m.event_time < a.assigned_at + interval '14 days'
group by a.user_id, a.variant
)
select
variant,
count(*) as n_users,
avg(revenue) as avg_revenue
from user_metrics
group by variant
order by variant;Two details in this query matter more than they look. First, the join is a left join from assignment — not an inner join from metrics — so a user who was assigned but placed zero orders still gets a row, with revenue coalesced to zero. Dropping them via an inner join would silently exclude exactly the users whose true metric value is zero, biasing every arm's average upward by an amount that depends on each arm's non-purchase rate.
Second, the metric window is bounded on both sides — event_time >= assigned_at and event_time < assigned_at + interval '14 days'. Without the lower bound, a user's pre-assignment purchase history leaks into their "treatment effect." Without the upper bound, users assigned earlier accumulate more calendar time to purchase than users assigned later in the enrollment period, which biases the comparison in favor of whichever arm happens to have earlier assignments.
Running this query against a real three-arm coupon test — the same experiment used throughout this book — returns:
| Variant | n_users | avg_revenue |
|---|---|---|
| control | 30,050 | $252.00 |
| discount_10pct | 30,120 | $281.00 |
| free_shipping | 29,980 | $270.00 |
This is the table every later chapter builds on: Chapter 2 turns these three rows into a proper t-test, Chapter 3 handles the case where the metric is a ratio instead of a sum, and Chapter 4 checks whether the 30,050 / 30,120 / 29,980 split is actually consistent with a fair random assignment, or whether it's an SRM in disguise.