Tiempo medio en página por URL orgánica

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

-- Tiempo medio de engagement por página en sesiones orgánicas
-- Usa engagement_time_msec acumulado por cada evento de la página
SELECT
  (SELECT value.string_value FROM UNNEST(event_params)
    WHERE key = 'page_location') AS pagina,
  COUNT(*) AS page_views,
  ROUND(
    AVG(
      (SELECT value.int_value FROM UNNEST(event_params)
        WHERE key = 'engagement_time_msec')
    ) / 1000, 2
  ) AS tiempo_medio_seg,
  ROUND(
    SUM(
      (SELECT value.int_value FROM UNNEST(event_params)
        WHERE key = 'engagement_time_msec')
    ) / 1000 / 60, 2
  ) AS tiempo_total_min
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 = 'user_engagement'
GROUP BY
  pagina
HAVING
  page_views >= 10
ORDER BY
  tiempo_medio_seg DESC
LIMIT 50