Experiment Analysis & Result Interpretation Questions

WITH first_exposure AS (
  SELECT user_id, MIN(event_date) AS t0, group
  FROM events
  WHERE event = 'feature_exposed'
  GROUP BY user_id, group
),
retained AS (
  SELECT f.user_id, f.group,
    CASE WHEN EXISTS (
      SELECT 1 FROM events e
      WHERE e.user_id = f.user_id
        AND e.event_date BETWEEN f.t0 + INTERVAL '29 day' AND f.t0 + INTERVAL '30 day'
        AND e.event = 'session_start'
    ) THEN 1 ELSE 0 END AS day30_retained
  FROM first_exposure f
)
SELECT group, COUNT(*) AS users, SUM(day30_retained) AS retained, AVG(day30_retained) AS retention_rate
FROM retained
GROUP BY group;

-- 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;

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)