Funnel de conversión desde landing orgánica hasta evento clave

-- Funnel de conversión en sesiones orgánicas
-- Etapas: session_start > page_view > scroll > evento clave
WITH sesiones_organicas AS (
  SELECT
    CONCAT(
      user_pseudo_id, '.',
      CAST(
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')
      AS STRING)
    ) AS session_id,
    event_name
  FROM
    `your-project.analytics_XXXXXXXXX.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
      AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
    AND traffic_source.medium = 'organic'
)
SELECT
  COUNT(DISTINCT IF(event_name = 'session_start', session_id, NULL)) AS paso_1_sesiones,
  COUNT(DISTINCT IF(event_name = 'page_view', session_id, NULL)) AS paso_2_page_view,
  COUNT(DISTINCT IF(event_name = 'scroll', session_id, NULL)) AS paso_3_scroll,
  COUNT(DISTINCT IF(event_name = 'generate_lead', session_id, NULL)) AS paso_4_conversion,
  -- Tasas de avance entre pasos
  ROUND(SAFE_DIVIDE(
    COUNT(DISTINCT IF(event_name = 'page_view', session_id, NULL)),
    COUNT(DISTINCT IF(event_name = 'session_start', session_id, NULL))
  ) * 100, 2) AS tasa_1_a_2,
  ROUND(SAFE_DIVIDE(
    COUNT(DISTINCT IF(event_name = 'scroll', session_id, NULL)),
    COUNT(DISTINCT IF(event_name = 'page_view', session_id, NULL))
  ) * 100, 2) AS tasa_2_a_3,
  ROUND(SAFE_DIVIDE(
    COUNT(DISTINCT IF(event_name = 'generate_lead', session_id, NULL)),
    COUNT(DISTINCT IF(event_name = 'scroll', session_id, NULL))
  ) * 100, 2) AS tasa_3_a_4
FROM
  sesiones_organicas