Program Impact and Results Questions

WITH params AS (
  SELECT
    DATE '2024-07-01' AS rollout_date  -- set rollout date here
),
users_cohort AS (
  SELECT
    u.user_id,
    date_trunc('day', u.created_at)::date AS cohort_date,
    CASE WHEN date_trunc('day', u.created_at)::date < p.rollout_date THEN 'before' ELSE 'after' END AS cohort_side
  FROM users u CROSS JOIN params p
),
-- retention: did user have any session within 30 days of signup
user_retention AS (
  SELECT
    uc.cohort_date,
    uc.cohort_side,
    uc.user_id,
    MAX(CASE WHEN s.started_at <= uc.cohort_date + INTERVAL '30 days' THEN 1 ELSE 0 END) AS retained_30d
  FROM users_cohort uc
  LEFT JOIN sessions s
    ON s.user_id = uc.user_id
    AND s.started_at >= uc.cohort_date -- optional: session after signup
  GROUP BY 1,2,3
),
-- purchases within 30 days
user_purchases AS (
  SELECT
    uc.cohort_date,
    uc.cohort_side,
    uc.user_id,
    COALESCE(SUM(CASE WHEN p.purchased_at >= uc.cohort_date AND p.purchased_at < uc.cohort_date + INTERVAL '30 days' THEN p.amount ELSE 0 END),0) AS spend_30d
  FROM users_cohort uc
  LEFT JOIN purchases p ON p.user_id = uc.user_id
  GROUP BY 1,2,3
),
cohort_metrics AS (
  SELECT
    uc.cohort_date,
    uc.cohort_side,
    COUNT(DISTINCT uc.user_id) AS cohort_size,
    SUM(ur.retained_30d) AS retained_users_30d,
    SUM(up.spend_30d) AS total_spend_30d
  FROM users_cohort uc
  LEFT JOIN user_retention ur ON ur.user_id = uc.user_id AND ur.cohort_date = uc.cohort_date
  LEFT JOIN user_purchases up ON up.user_id = uc.user_id AND up.cohort_date = uc.cohort_date
  GROUP BY 1,2
)
SELECT
  cohort_date,
  cohort_side,
  cohort_size,
  retained_users_30d,
  ROUND(100.0 * retained_users_30d / NULLIF(cohort_size,0),2) AS retention_30d_pct,
  ROUND(total_spend_30d::numeric / NULLIF(cohort_size,0),2) AS ltv_30d_per_user
FROM cohort_metrics
ORDER BY cohort_date;

SELECT
  day,
  region,
  dau,
  rolling_mean,
  rolling_stddev,
  CASE
    WHEN rolling_stddev IS NULL OR rolling_stddev = 0 THEN NULL
    ELSE (dau - rolling_mean) / rolling_stddev
  END AS z_score,
  CASE
    WHEN rolling_stddev IS NULL OR rolling_stddev = 0 THEN FALSE
    WHEN ABS((dau - rolling_mean) / rolling_stddev) > 3 THEN TRUE
    ELSE FALSE
  END AS is_anomaly
FROM (
  SELECT
    day,
    region,
    dau,
    AVG(dau) OVER (
      PARTITION BY region
      ORDER BY day
      ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
    ) AS rolling_mean,
    STDDEV_SAMP(dau) OVER (
      PARTITION BY region
      ORDER BY day
      ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
    ) AS rolling_stddev
  FROM daily_metrics
  WHERE dau IS NOT NULL
) t
ORDER BY region, day;