Tráfico orgánico segmentado por tipo de contenido

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 37 38 39 40 41 42 43 44

-- Tráfico orgánico segmentado por tipo de contenido según URL
-- Ajustar los patrones REGEXP a la estructura de URLs del sitio
WITH sesiones AS (
  SELECT
    (SELECT value.string_value FROM UNNEST(event_params)
      WHERE key = 'page_location') AS landing_page,
    CONCAT(
      user_pseudo_id, '.',
      CAST(
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')
      AS STRING)
    ) AS session_id,
    MAX(
      (SELECT value.string_value FROM UNNEST(event_params)
        WHERE key = 'session_engaged')
    ) AS session_engaged
  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 = 'session_start'
  GROUP BY
    landing_page, session_id
)
SELECT
  CASE
    WHEN landing_page LIKE '%/blog/%' THEN 'Blog'
    WHEN landing_page LIKE '%/producto/%' OR landing_page LIKE '%/product/%' THEN 'Producto'
    WHEN landing_page LIKE '%/categoria/%' OR landing_page LIKE '%/category/%' THEN 'Categoria'
    WHEN landing_page LIKE '%/servicio/%' OR landing_page LIKE '%/service/%' THEN 'Servicio'
    WHEN REGEXP_CONTAINS(landing_page, r'/$') AND NOT REGEXP_CONTAINS(landing_page, r'/[^/]+/[^/]+') THEN 'Home'
    ELSE 'Otro'
  END AS tipo_contenido,
  COUNT(*) AS sesiones,
  COUNTIF(session_engaged = '1') AS sesiones_engaged,
  ROUND(COUNTIF(session_engaged = '1') / COUNT(*) * 100, 2) AS engagement_rate
FROM
  sesiones
GROUP BY
  tipo_contenido
ORDER BY
  sesiones DESC