取得元ごとのファネル コンバージョンの比較

WITH users AS (
  SELECT
    user_id,
    MIN(acquisition_source) AS acquisition_source,
    MAX(CASE WHEN event_name = 'signup_completed' THEN 1 ELSE 0 END) AS signed_up,
    MAX(CASE WHEN event_name = 'first_meaningful_action_completed' THEN 1 ELSE 0 END) AS activated
  FROM product_events
  WHERE timestamp_utc >= now() - INTERVAL '30 days'
  GROUP BY user_id
)
SELECT
  acquisition_source,
  SUM(signed_up) AS signups,
  SUM(activated) AS activated_users,
  100.0 * SUM(activated) / NULLIF(SUM(signed_up), 0) AS activation_rate_pct
FROM users
WHERE signed_up = 1
GROUP BY acquisition_source
HAVING SUM(signed_up) >= 20
ORDER BY activation_rate_pct DESC;