Usuarios nuevos vs recurrentes desde orgánico
Compara el volumen de usuarios nuevos frente a recurrentes en tráfico orgánico. Permite evaluar si el contenido atrae visitantes frescos o fideliza a los existentes.
-- Usuarios nuevos vs recurrentes en tráfico orgánico
-- ga_session_number = 1 indica primera sesión del usuario
SELECT
CASE
WHEN (SELECT value.int_value FROM UNNEST(event_params)
WHERE key = 'ga_session_number') = 1 THEN 'Nuevo'
ELSE 'Recurrente'
END AS tipo_usuario,
COUNT(DISTINCT user_pseudo_id) AS usuarios,
COUNT(
DISTINCT CONCAT(
user_pseudo_id, '.',
CAST(
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')
AS STRING)
)
) AS sesiones
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
tipo_usuario
ORDER BY
usuarios DESCExplicación paso a paso
- 4 Clasifica cada sesión como 'Nuevo' o 'Recurrente' según ga_session_number. El valor 1 indica la primera sesión del usuario.
- 9 Cuenta usuarios únicos por tipo usando user_pseudo_id.
- 10 También cuenta sesiones únicas para cada tipo, ya que un usuario recurrente puede tener múltiples sesiones.
- 24 Filtra por session_start para tener una fila por sesión y evitar contar múltiples eventos dentro de la misma sesión.
- 26 Agrupa por tipo_usuario para obtener las dos filas de resultado: Nuevo y Recurrente.
Ejemplo de resultado esperado
| tipo_usuario | usuarios | sesiones |
|---|---|---|
| Nuevo | 8432 | 8432 |
| Recurrente | 3210 | 5847 |
Variaciones y adaptaciones
Para ver la evolución diaria, añadir PARSE_DATE('%Y%m%d', event_date) AS fecha al SELECT y al GROUP BY. Para calcular el porcentaje de cada tipo, envolver la query en un CTE y usar usuarios / SUM(usuarios) OVER() * 100. Para desglosar por landing page, añadir page_location al SELECT y GROUP BY.