Approach: compute per-user 14-day attributed revenue (sum charge_amount within 14 days after assignment), include users with zero purchases, mark censored users (assignment + 14d > data_cutoff) and exclude or use survival-aware imputation; here we exclude censored users for unbiased ARPU uplift. Then bootstrap users (resample users with replacement within each arm) to get distribution of mean per-user revenue and 95% CI for uplift (treatment_mean - control_mean).SQL to build per-user revenue and censor flag:sql
-- params: assignment_table(events) has user_id, assignment_date, arm ('treatment'/'control')
WITH events AS (
SELECT user_id, charge_amount, event_time
FROM raw_events
),
assign AS (
SELECT user_id, MIN(assignment_date) AS assign_date, arm
FROM assignments
GROUP BY user_id, arm
),
user_window AS (
SELECT a.user_id, a.arm, a.assign_date,
DATE_ADD(a.assign_date, INTERVAL 14 DAY) AS window_end
FROM assign a
),
user_revenue AS (
SELECT u.user_id, u.arm, u.assign_date, u.window_end,
COALESCE(SUM(e.charge_amount), 0) AS revenue_14d,
CASE WHEN u.window_end <= (SELECT MAX(event_time) FROM events) THEN 0 ELSE 1 END AS censored
FROM user_window u
LEFT JOIN events e
ON e.user_id = u.user_id
AND e.event_time >= u.assign_date
AND e.event_time < u.window_end
GROUP BY u.user_id, u.arm, u.assign_date, u.window_end
)
SELECT * FROM user_revenue;
python
import numpy as np
import pandas as pd
# user_revenue_df: columns ['user_id','arm','revenue_14d','censored']
df = user_revenue_df[df['censored']==0] # exclude censored users
control = df[df.arm=='control']
treat = df[df.arm=='treatment']
def bootstrap_mean_diff(ctrl, trt, n_boot=10000, seed=0):
rng = np.random.default_rng(seed)
diffs = np.empty(n_boot)
for i in range(n_boot):
sample_c = rng.choice(ctrl.revenue_14d.values, size=len(ctrl), replace=True)
sample_t = rng.choice(trt.revenue_14d.values, size=len(trt), replace=True)
diffs[i] = sample_t.mean() - sample_c.mean()
return np.percentile(diffs, [2.5,50,97.5]), diffs
ci, diffs = bootstrap_mean_diff(control, treat)
print("ARPU uplift median and 95% CI:", ci)
