プロダクトジャーニーのドロップオフをセッションごとに測定する

WITH step_counts AS (
  SELECT
    step_order,
    step_name,
    COUNT(*) AS sessions_reached
  FROM product_journey_events
  WHERE timestamp_utc >= now() - INTERVAL '30 days'
    AND environment = 'production'
  GROUP BY step_order, step_name
)
SELECT
  step_order,
  step_name,
  sessions_reached,
  100.0 * sessions_reached
    / NULLIF(FIRST_VALUE(sessions_reached) OVER (ORDER BY step_order), 0)
    AS journey_completion_pct,
  100.0 * sessions_reached
    / NULLIF(COALESCE(LAG(sessions_reached) OVER (ORDER BY step_order), sessions_reached), 0)
    AS step_to_step_pct
FROM step_counts
ORDER BY step_order;