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

WITH user_funnel AS (
  SELECT
    user_id,
    MIN(CASE WHEN event_name = 'signup_completed'
      THEN timestamp_utc END) AS signed_up_at,
    MIN(CASE WHEN event_name = 'onboarding_completed'
      THEN timestamp_utc END) AS onboarded_at,
    MIN(CASE WHEN event_name = 'integration_connected'
      THEN timestamp_utc END) AS integrated_at,
    MIN(CASE WHEN event_name = 'first_value_completed'
      THEN timestamp_utc END) AS activated_at
  FROM product_events
  WHERE timestamp_utc >= now() - INTERVAL '30 days'
  GROUP BY user_id
),
stage_counts AS (
  SELECT 1 AS step_order, 'Signed up' AS step, COUNT(*) AS users
  FROM user_funnel
  WHERE signed_up_at IS NOT NULL
  UNION ALL
  SELECT 2, 'Onboarded', COUNT(*)
  FROM user_funnel
  WHERE onboarded_at >= signed_up_at
  UNION ALL
  SELECT 3, 'Integrated', COUNT(*)
  FROM user_funnel
  WHERE integrated_at >= onboarded_at
  UNION ALL
  SELECT 4, 'Activated', COUNT(*)
  FROM user_funnel
  WHERE activated_at >= integrated_at
)
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;