Eventos clave (conversiones) atribuidos a orgánico

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

-- Eventos clave (conversiones) atribuidos a tráfico orgánico
-- Filtra eventos marcados como clave en la configuración de GA4
SELECT
  event_name AS evento,
  COUNT(*) AS total_conversiones,
  COUNT(DISTINCT user_pseudo_id) AS usuarios_que_convirtieron,
  COUNT(
    DISTINCT CONCAT(
      user_pseudo_id, '.',
      CAST(
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')
      AS STRING)
    )
  ) AS sesiones_con_conversion
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'
  -- En exports recientes de GA4, is_key_event indica eventos clave
  AND COALESCE(
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'is_key_event'),
    'false'
  ) = 'true'
GROUP BY
  evento
ORDER BY
  total_conversiones DESC