SQL でサインアップからアクティベーションまでのファネルを構築する

WITH events AS (
  SELECT user_id, event_name, timestamp_utc
  FROM product_events
  WHERE timestamp_utc >= now() - INTERVAL '30 days'
),
signups AS (
  SELECT user_id, MIN(timestamp_utc) AS signed_up_at
  FROM events WHERE event_name = 'signup_completed'
  GROUP BY user_id
),
onboarding AS (
  SELECT s.user_id, s.signed_up_at, MIN(e.timestamp_utc) AS onboarded_at
  FROM signups AS s
  LEFT JOIN events AS e ON e.user_id = s.user_id
    AND e.event_name = 'onboarding_completed'
    AND e.timestamp_utc >= s.signed_up_at
  GROUP BY s.user_id, s.signed_up_at
),
integrations AS (
  SELECT o.user_id, o.signed_up_at, o.onboarded_at,
    MIN(e.timestamp_utc) AS integrated_at
  FROM onboarding AS o
  LEFT JOIN events AS e ON e.user_id = o.user_id
    AND e.event_name = 'integration_connected'
    AND e.timestamp_utc >= o.onboarded_at
  GROUP BY o.user_id, o.signed_up_at, o.onboarded_at
),
user_funnel AS (
  SELECT i.user_id, i.signed_up_at, i.onboarded_at, i.integrated_at,
    MIN(e.timestamp_utc) AS activated_at
  FROM integrations AS i
  LEFT JOIN events AS e ON e.user_id = i.user_id
    AND e.event_name = 'first_value_completed'
    AND e.timestamp_utc >= i.integrated_at
  GROUP BY i.user_id, i.signed_up_at, i.onboarded_at, i.integrated_at
),
stage_counts AS (
  SELECT 1 AS step_order, 'Signed up' AS step, COUNT(signed_up_at) AS users
  FROM user_funnel
  UNION ALL
  SELECT 2, 'Onboarded', COUNT(onboarded_at) FROM user_funnel
  UNION ALL
  SELECT 3, 'Integrated', COUNT(integrated_at) FROM user_funnel
  UNION ALL
  SELECT 4, 'Activated', COUNT(activated_at) FROM user_funnel
)
SELECT
  step_order,
  step,
  users,
  100.0 * users
    / NULLIF(MAX(CASE WHEN step_order = 1 THEN users END) OVER (), 0)
    AS conversion_from_signup_pct,
  100.0 * users
    / NULLIF(LAG(users) OVER (ORDER BY step_order), 0)
    AS conversion_from_previous_pct
FROM stage_counts
ORDER BY step_order;