Eventos personalizados disparados en sesiones orgánicas

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 30 31 32

-- Eventos personalizados en sesiones orgánicas
-- Excluye eventos automáticos y de enhanced measurement de GA4
SELECT
  event_name AS evento,
  COUNT(*) AS total_disparos,
  COUNT(DISTINCT user_pseudo_id) AS usuarios_unicos,
  COUNT(
    DISTINCT CONCAT(
      user_pseudo_id, '.',
      CAST(
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')
      AS STRING)
    )
  ) AS sesiones
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'
  -- Excluir eventos estándar de GA4
  AND event_name NOT IN (
    'session_start', 'first_visit', 'page_view',
    'scroll', 'click', 'view_search_results',
    'file_download', 'video_start', 'video_progress',
    'video_complete', 'user_engagement'
  )
GROUP BY
  evento
ORDER BY
  total_disparos DESC
LIMIT 30