Consultas tabla events:
Consultas tabla events:
Consulta una fila de event_params:
SELECT
(SELECT value.int_value FROM UNNEST(event_params) WHERE KEY = 'ga_session_id') AS sesion_id
FROM
`prueba-blogger-439506.analytics_420521341.events_*`
Cambia el formato a fecha y extrae la hora:
SELECT
PARSE_DATE('%Y%m%d', event_date) AS date,
EXTRACT(HOUR FROM TIMESTAMP_MICROS(event_timestamp) AT TIME ZONE 'America/Lima') AS event_hour,
FROM
`prueba-blogger-439506.analytics_420521341.events_*`
Consulta a una sola fecha de la tabla events:
SELECT
*
FROM
`prueba-blogger-439506.analytics_420521341.events_*`
WHERE
_TABLE_SUFFIX = '20241025'
Consulta un rango de fechas dentro de la tabla events:
SELECT
*
FROM
`prueba-blogger-439506.analytics_420521341.events_*`
WHERE
REGEXP_EXTRACT(_TABLE_SUFFIX, r"[0-9]+") BETWEEN
FORMAT_DATE("%Y%m%d", "2024-10-22") and
FORMAT_DATE("%Y%m%d", current_date())
Consulta un rango de fechas contando hacia atrás desde hoy, sin incluir el día actual.
SELECT
*
FROM
`prueba-blogger-439506.analytics_420521341.events_*`
WHERE
_TABLE_SUFFIX BETWEEN
FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 5 DAY))
AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
Recuento de sesiones, versión compleja (unificando el user_pseudo_id con el ga_session_id)
SELECT
COUNT( DISTINCT( CONCAT( user_pseudo_id,(
SELECT value.int_value
FROM UNNEST(event_params)
WHERE KEY = 'ga_session_id')) )) AS sessions_session_and_user_id,
FROM
`prueba-blogger-439506.analytics_420521341.events_*`
GROUP BY
event_date
Recuento de sesiones, versión simple
SELECT
COUNT(*) AS sesiones
FROM
`prueba-blogger-439506.analytics_420521341.events_*`
WHERE
event_name = 'session_start'