Analytical Background Questions

import numpy as np

def bootstrap_conv_diff(control, treatment, n_resamples=10000, seed=None):
    """
    Returns 95% bootstrap CI for (treatment_rate - control_rate).
    control, treatment: 1D arrays of 0/1 (or booleans).
    n_resamples: number of bootstrap draws (default 10000).
    seed: int or None for reproducibility.
    """
    rng = np.random.default_rng(seed)
    control = np.asarray(control)
    treatment = np.asarray(treatment)
    n_c, n_t = len(control), len(treatment)
    if n_c == 0 or n_t == 0:
        raise ValueError("Both groups must be non-empty.")
    diffs = np.empty(n_resamples)
    for i in range(n_resamples):
        sample_c = rng.choice(control, size=n_c, replace=True)
        sample_t = rng.choice(treatment, size=n_t, replace=True)
        diffs[i] = sample_t.mean() - sample_c.mean()
    lower, upper = np.percentile(diffs, [2.5, 97.5])
    return lower, upper

WITH windowed AS (
  -- restrict to 7-day period and relevant events
  SELECT *
  FROM events
  WHERE event_name IN ('view','add_to_cart','checkout','purchase')
    AND occurred_at >= CURRENT_DATE - INTERVAL '7 day'
),
step_first AS (
  -- first time each step happened per session
  SELECT
    session_id,
    user_id,
    event_name,
    MIN(occurred_at) AS first_at
  FROM windowed
  GROUP BY session_id, user_id, event_name
),
pivot_steps AS (
  -- pivot so each session has timestamps (or NULL) per funnel step
  SELECT
    session_id,
    user_id,
    MAX(CASE WHEN event_name='view' THEN first_at END)       AS view_at,
    MAX(CASE WHEN event_name='add_to_cart' THEN first_at END) AS add_to_cart_at,
    MAX(CASE WHEN event_name='checkout' THEN first_at END)    AS checkout_at,
    MAX(CASE WHEN event_name='purchase' THEN first_at END)    AS purchase_at
  FROM step_first
  GROUP BY session_id, user_id
),
sessions_with_order AS (
  -- ensure steps are in chronological order within session
  SELECT *,
    -- only count a step if it happened after the previous step (or view exists)
    CASE WHEN view_at IS NOT NULL THEN 1 ELSE 0 END AS did_view,
    CASE WHEN add_to_cart_at IS NOT NULL AND add_to_cart_at >= view_at THEN 1 ELSE 0 END AS did_add_to_cart,
    CASE WHEN checkout_at IS NOT NULL AND checkout_at >= add_to_cart_at AND add_to_cart_at IS NOT NULL THEN 1 ELSE 0 END AS did_checkout,
    CASE WHEN purchase_at IS NOT NULL AND purchase_at >= checkout_at AND checkout_at IS NOT NULL THEN 1 ELSE 0 END AS did_purchase
  FROM pivot_steps
),
agg AS (
  SELECT
    SUM(did_view)       AS sessions_view,
    SUM(did_add_to_cart) AS sessions_add_to_cart,
    SUM(did_checkout)    AS sessions_checkout,
    SUM(did_purchase)    AS sessions_purchase
  FROM sessions_with_order
),
rates AS (
  SELECT
    'view' AS step, sessions_view AS count,
    1.0 AS conversion_from_prev,
    1.0 AS conversion_from_start
  FROM agg
  UNION ALL
  SELECT
    'add_to_cart', sessions_add_to_cart,
    CASE WHEN sessions_view=0 THEN 0 ELSE sessions_add_to_cart::float/sessions_view END,
    CASE WHEN sessions_view=0 THEN 0 ELSE sessions_add_to_cart::float/sessions_view END
  FROM agg
  UNION ALL
  SELECT
    'checkout', sessions_checkout,
    CASE WHEN sessions_add_to_cart=0 THEN 0 ELSE sessions_checkout::float/sessions_add_to_cart END,
    CASE WHEN sessions_view=0 THEN 0 ELSE sessions_checkout::float/sessions_view END
  FROM agg
  UNION ALL
  SELECT
    'purchase', sessions_purchase,
    CASE WHEN sessions_checkout=0 THEN 0 ELSE sessions_purchase::float/sessions_checkout END,
    CASE WHEN sessions_view=0 THEN 0 ELSE sessions_purchase::float/sessions_view END
  FROM agg
)
SELECT
  step,
  count,
  ROUND(conversion_from_prev::numeric,4) AS conversion_from_prev,
  ROUND(conversion_from_start::numeric,4) AS conversion_from_start
FROM rates
ORDER BY CASE step WHEN 'view' THEN 1 WHEN 'add_to_cart' THEN 2 WHEN 'checkout' THEN 3 WHEN 'purchase' THEN 4 END;