Metric Frameworks and Goal Alignment Questions

WITH cohorts AS (
  SELECT user_id, signup_date::date AS cohort_date
  FROM users
),
days AS (
  SELECT generate_series(1,7) AS day_offset
),
-- For deduplication: mark whether a user had any event in a given day offset
user_activity AS (
  SELECT
    c.cohort_date,
    c.user_id,
    d.day_offset,
    -- flag if user had any event in the day window (deduped to 1)
    CASE WHEN COUNT(e.event_id) > 0 THEN 1 ELSE 0 END AS active_flag
  FROM cohorts c
  CROSS JOIN days d
  LEFT JOIN events e
    ON e.user_id = c.user_id
    AND e.occurred_at >= (c.cohort_date + (d.day_offset) * INTERVAL '1 day')
    AND e.occurred_at <  (c.cohort_date + (d.day_offset + 1) * INTERVAL '1 day')
  GROUP BY c.cohort_date, c.user_id, d.day_offset
),
cohort_sizes AS (
  SELECT cohort_date, COUNT(*) AS cohort_users
  FROM cohorts
  GROUP BY cohort_date
),
retention AS (
  SELECT
    ua.cohort_date,
    ua.day_offset,
    SUM(ua.active_flag) AS active_users
  FROM user_activity ua
  GROUP BY ua.cohort_date, ua.day_offset
)
SELECT
  r.cohort_date,
  cs.cohort_users,
  -- retention percentages for day 1..7 as decimals (multiply by 100 if you want %)
  ROUND(100.0 * MAX(CASE WHEN r.day_offset = 1 THEN r.active_users::numeric / cs.cohort_users ELSE NULL END),2) AS day_1_pct,
  ROUND(100.0 * MAX(CASE WHEN r.day_offset = 2 THEN r.active_users::numeric / cs.cohort_users ELSE NULL END),2) AS day_2_pct,
  ROUND(100.0 * MAX(CASE WHEN r.day_offset = 3 THEN r.active_users::numeric / cs.cohort_users ELSE NULL END),2) AS day_3_pct,
  ROUND(100.0 * MAX(CASE WHEN r.day_offset = 4 THEN r.active_users::numeric / cs.cohort_users ELSE NULL END),2) AS day_4_pct,
  ROUND(100.0 * MAX(CASE WHEN r.day_offset = 5 THEN r.active_users::numeric / cs.cohort_users ELSE NULL END),2) AS day_5_pct,
  ROUND(100.0 * MAX(CASE WHEN r.day_offset = 6 THEN r.active_users::numeric / cs.cohort_users ELSE NULL END),2) AS day_6_pct,
  ROUND(100.0 * MAX(CASE WHEN r.day_offset = 7 THEN r.active_users::numeric / cs.cohort_users ELSE NULL END),2) AS day_7_pct
FROM retention r
JOIN cohort_sizes cs ON cs.cohort_date = r.cohort_date
GROUP BY r.cohort_date, cs.cohort_users
ORDER BY r.cohort_date;

WITH first_views AS (
  -- first view_product per user (defines funnel start and acquisition channel)
  SELECT
    user_id,
    MIN(occurred_at) AS view_at,
    -- take acquisition_channel as of the first view (assumes channel attached to view)
    (ARRAY_AGG(acquisition_channel ORDER BY occurred_at ASC))[1] AS acquisition_channel
  FROM events
  WHERE event_name = 'view_product'
  GROUP BY user_id
),
steps AS (
  SELECT
    fv.user_id,
    fv.acquisition_channel,
    fv.view_at,
    -- earliest add_to_cart within 7 days of view
    (SELECT MIN(e.occurred_at)
     FROM events e
     WHERE e.user_id = fv.user_id
       AND e.event_name = 'add_to_cart'
       AND e.occurred_at >= fv.view_at
       AND e.occurred_at <= fv.view_at + INTERVAL '7 days'
    ) AS add_at,
    -- earliest purchase within 7 days of view
    (SELECT MIN(e2.occurred_at)
     FROM events e2
     WHERE e2.user_id = fv.user_id
       AND e2.event_name = 'purchase'
       AND e2.occurred_at >= fv.view_at
       AND e2.occurred_at <= fv.view_at + INTERVAL '7 days'
    ) AS purchase_at
  FROM first_views fv
)
SELECT
  acquisition_channel,
  COUNT(*) AS users_started,
  ROUND(100.0 * SUM(CASE WHEN add_at IS NOT NULL THEN 1 ELSE 0 END) / NULLIF(COUNT(*),0), 2) AS added_to_cart_rate,
  ROUND(100.0 * SUM(CASE WHEN purchase_at IS NOT NULL THEN 1 ELSE 0 END) / NULLIF(COUNT(*),0), 2) AS purchase_rate
FROM steps
GROUP BY acquisition_channel
ORDER BY acquisition_channel;