Páginas de salida más frecuentes 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 33 34 35 36

-- Páginas de salida más frecuentes en sesiones orgánicas
-- Identifica la última página vista de cada sesión
WITH ultima_pagina 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,
    ARRAY_AGG(
      (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location')
      ORDER BY event_timestamp DESC
      LIMIT 1
    )[OFFSET(0)] AS pagina_salida
  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'
    AND event_name = 'page_view'
  GROUP BY
    session_id
)
SELECT
  pagina_salida,
  COUNT(*) AS salidas,
  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 2) AS porcentaje_salidas
FROM
  ultima_pagina
GROUP BY
  pagina_salida
ORDER BY
  salidas DESC
LIMIT 30