Tráfico orgánico por dispositivo y sistema operativo

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

-- Tráfico orgánico por dispositivo y sistema operativo
SELECT
  device.category AS dispositivo,
  device.operating_system AS sistema_operativo,
  COUNT(
    DISTINCT CONCAT(
      user_pseudo_id, '.',
      CAST(
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')
      AS STRING)
    )
  ) AS sesiones,
  COUNT(DISTINCT user_pseudo_id) AS usuarios,
  ROUND(
    COUNT(
      DISTINCT CONCAT(
        user_pseudo_id, '.',
        CAST(
          (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')
        AS STRING)
      )
    ) * 100.0 / SUM(COUNT(
      DISTINCT CONCAT(
        user_pseudo_id, '.',
        CAST(
          (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')
        AS STRING)
      )
    )) OVER(), 2
  ) AS porcentaje
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
  dispositivo, sistema_operativo
ORDER BY
  sesiones DESC
LIMIT 20