Metrics Selection and Diagnostic Interpretation Questions

WITH user_day AS (
  -- For each user and date, mark whether they did each event
  SELECT
    CAST(event_time AT TIME ZONE 'UTC' AS DATE) AS dt,
    user_id,
    MAX(CASE WHEN event_name = 'view_product' THEN 1 ELSE 0 END) AS viewed,
    MAX(CASE WHEN event_name = 'add_to_cart' THEN 1 ELSE 0 END) AS added,
    MAX(CASE WHEN event_name = 'begin_checkout' THEN 1 ELSE 0 END) AS began,
    MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS purchased
  FROM events
  WHERE event_time >= (CURRENT_DATE AT TIME ZONE 'UTC') - INTERVAL '13 days'
    AND event_time < (CURRENT_DATE AT TIME ZONE 'UTC') + INTERVAL '1 day'
    AND event_name IN ('view_product','add_to_cart','begin_checkout','purchase')
  GROUP BY 1, user_id
),
daily_counts AS (
  -- Aggregate unique users per step per day
  SELECT
    dt,
    SUM(viewed)    AS unique_users_view,
    SUM(added)     AS unique_users_add,
    SUM(began)     AS unique_users_begin_checkout,
    SUM(purchased) AS unique_users_purchase
  FROM user_day
  GROUP BY dt
)
SELECT
  dt AS date,
  unique_users_view,
  unique_users_add,
  unique_users_begin_checkout,
  unique_users_purchase,
  -- conversion rate view -> purchase (null-safe)
  CASE WHEN unique_users_view = 0 THEN 0.0
       ELSE ROUND(100.0 * unique_users_purchase::numeric / unique_users_view, 2)
  END AS conversion_rate_view_to_purchase_pct,
  -- step drop-offs as percent dropped from previous step
  CASE WHEN unique_users_view = 0 THEN 0.0
       ELSE ROUND(100.0 * (1 - unique_users_add::numeric / unique_users_view), 2)
  END AS drop_view_to_add_pct,
  CASE WHEN unique_users_add = 0 THEN 0.0
       ELSE ROUND(100.0 * (1 - unique_users_begin_checkout::numeric / unique_users_add), 2)
  END AS drop_add_to_begin_pct,
  CASE WHEN unique_users_begin_checkout = 0 THEN 0.0
       ELSE ROUND(100.0 * (1 - unique_users_purchase::numeric / unique_users_begin_checkout), 2)
  END AS drop_begin_to_purchase_pct
FROM daily_counts
ORDER BY dt DESC
LIMIT 14;

-- core metric SQL (example)
SELECT COUNT(DISTINCT user_id) AS daily_paying_users, payment_date
FROM prod.payments
WHERE status = 'completed'
  AND payment_date = current_date
GROUP BY payment_date;

-- dbt custom test: relative change from yesterday
WITH today AS (
  SELECT COUNT(DISTINCT user_id) AS v FROM {{ ref('payments_metric') }} WHERE date = current_date
), yesterday AS (
  SELECT COUNT(DISTINCT user_id) AS v FROM {{ ref('payments_metric') }} WHERE date = current_date - 1
)
SELECT CASE WHEN ABS(today.v - yesterday.v) / GREATEST(yesterday.v,1) <= 0.5 THEN TRUE ELSE FALSE END AS pass
FROM today, yesterday;

-- assumes table metrics(date, dau)
WITH features AS (
  SELECT
    date,
    EXTRACT(DOW FROM date) AS dow,
    dau
  FROM metrics
),
historic AS (
  SELECT
    cur.date,
    cur.dau,
    AVG(prev.dau) AS exp_mean,
    STDDEV_SAMP(prev.dau) AS exp_stddev
  FROM features cur
  JOIN features prev
    ON cur.dow = prev.dow
    AND prev.date BETWEEN cur.date - INTERVAL '56 days' AND cur.date - INTERVAL '7 days'
  GROUP BY cur.date, cur.dau
)
SELECT
  date, dau, exp_mean, exp_stddev,
  (dau - exp_mean) / NULLIF(exp_stddev,0) AS z,
  CASE WHEN ABS((dau - exp_mean) / NULLIF(exp_stddev,0)) > 3 THEN 'anomaly' ELSE 'ok' END AS flag
FROM historic;

# metrics: DataFrame with 'date' index and 'dau'
metrics['dow'] = metrics.index.dayofweek
def rolling_like_for_like(df, weeks=8):
    results=[]
    for i,row in df.iterrows():
        same_dow = df[(df.index.dayofweek==row.dayofweek) & (df.index < i) & (df.index >= i - pd.Timedelta(days=7*weeks))]
        mean = same_dow['dau'].mean()
        std = same_dow['dau'].std()
        results.append((mean,std))
    df['exp_mean'], df['exp_std'] = zip(*results)
    df['z'] = (df['dau'] - df['exp_mean']) / df['exp_std']
    df['flag'] = df['z'].abs() > 3
    return df