Consultas básicas para la exportación de datos de eventos de Google Analytics

Las consultas de ejemplo de esta página se aplican a los datos de exportación de eventos de BigQuery para Google Analytics.

Consulta tu conjunto de datos en lugar del conjunto de datos de muestra

A menos que se indique lo contrario, todas las consultas que se enumeran aquí usan conjuntos de datos de muestra y deberían producir resultados válidos. Para usar los datos de exportación de eventos de BigQuery de tu propia propiedad de Google Analytics, busca el comentario -- Replace table en cada consulta y reemplaza la tabla de muestra. Para copiar el nombre de la tabla de tu conjunto de datos, sigue estos pasos:

  1. Ve a la IU de BigQuery y selecciona el proyecto que contiene tu conjunto de datos.
  2. Ubica la tabla en el Explorador.
  3. Haz clic en los tres puntos verticales que aparecen a la derecha de la tabla y, luego, en Copiar ID.
  4. Pega el nombre de la tabla en lugar de la tabla de muestra en la consulta.
  5. Reemplaza la parte de la fecha de la tabla por *.

Por ejemplo, si Copy ID copió el nombre de la tabla de BigQuery my-first-gcp-project:analytics_28239234.events_20240718, reemplaza lo siguiente:

  -- Replace table
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`

con:

  -- Replace table
  `my-first-gcp-project.analytics_28239234.events_*`

Cómo consultar un período específico

Para consultar un período específico en un conjunto de datos de exportación de eventos de BigQuery, usa la pseudocolumna _TABLE_SUFFIX en la cláusula WHERE de tu consulta. Para obtener más información, consulta Filtra las tablas seleccionadas con _TABLE_SUFFIX.

Por ejemplo, la siguiente consulta cuenta los eventos únicos por fecha y por nombre del evento para un período específico de días y eventos seleccionados:

-- Example: Query a specific date range for selected events.
--
-- Counts unique events by date and by event name for a specifc period of days and
-- selected events(page_view, session_start, and purchase).

SELECT
  event_date,
  event_name,
  COUNT(*) AS event_count
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_name IN ('page_view', 'session_start', 'purchase')
  -- Replace date range.
  AND _TABLE_SUFFIX BETWEEN '20201201' AND '20201202'
GROUP BY 1, 2;

Recuento de usuarios, usuarios nuevos y usuarios recurrentes

  • Para obtener el recuento total de usuarios activos, cuenta la cantidad de valores de user_id distintos. Sin embargo, si tu cliente de Google Analytics no envía un user_id con cada hit o si no tienes certeza, cuenta la cantidad de valores de user_pseudo_id distintos.
  • En el caso de los usuarios nuevos, cuenta la cantidad de usuarios distintos cuyo event_name sea uno de los siguientes:
  • En el caso de los usuarios recurrentes, se cuenta la cantidad de usuarios que tuvieron actividad durante el período, pero que no iniciaron su primera sesión ni lanzaron la app por primera vez durante el período (usuarios activos menos usuarios nuevos).
-- Example: Get 'Total User' (Active User), 'New User', and 'Returning User' counts.

WITH
  UserInfo AS (
    SELECT
      user_pseudo_id,
      MAX(IF(event_name IN ('first_visit', 'first_open'), 1, 0)) AS is_new_user
    -- Replace table name.
    FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    -- Replace date range.
    WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20201130'
    GROUP BY 1
  )
SELECT
  COUNT(*) AS active_users,
  SUM(is_new_user) AS new_users,
  COUNT(*) - SUM(is_new_user) AS returning_users
FROM UserInfo;

Cantidad promedio de transacciones por comprador

La siguiente consulta muestra la cantidad promedio de transacciones por comprador.

-- Example: Average number of transactions per purchaser.

SELECT
  COUNT(*) / COUNT(DISTINCT user_pseudo_id) AS avg_transaction_per_purchaser
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_name IN ('in_app_purchase', 'purchase')
  -- Replace date range.
  AND _TABLE_SUFFIX BETWEEN '20201201' AND '20201231';

Valores para un nombre de evento específico

La siguiente consulta muestra el event_timestamp para todos los eventos purchase y los valores de los parámetros de eventos asociados:

-- Example: Query values for a specific event name.
--
-- Queries the individual timestamps and values for all 'purchase' events.

SELECT
  event_timestamp,
  (
    SELECT COALESCE(value.int_value, value.float_value, value.double_value)
    FROM UNNEST(event_params)
    WHERE key = 'value'
  ) AS event_value
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_name = 'purchase'
  -- Replace date range.
  AND _TABLE_SUFFIX BETWEEN '20201201' AND '20201202';

La consulta anterior se puede modificar para mostrar el total de los valores del parámetro del evento en lugar de una lista:

-- Example: Query total value for a specific event name.
--
-- Queries the total event value for all 'purchase' events.

SELECT
  SUM(
    (
      SELECT COALESCE(value.int_value, value.float_value, value.double_value)
      FROM UNNEST(event_params)
      WHERE key = 'value'
    ))
    AS event_value
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_name = 'purchase'
  -- Replace date range.
  AND _TABLE_SUFFIX BETWEEN '20201201' AND '20201202';

Los 10 artículos más agregados al carrito

La siguiente consulta muestra los 10 artículos principales que agregaron al carrito la mayor cantidad de usuarios.

-- Example: Top 10 items added to cart by most users.

SELECT
  item_id,
  item_name,
  COUNT(DISTINCT user_pseudo_id) AS user_count
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, UNNEST(items)
WHERE
  -- Replace date range.
  _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
  AND event_name IN ('add_to_cart')
GROUP BY
  1, 2
ORDER BY
  user_count DESC
LIMIT 10;

Cantidad promedio de vistas de página por tipo de comprador (compradores en comparación con no compradores)

La siguiente consulta muestra la cantidad promedio de vistas de página por tipo de comprador (compradores en comparación con no compradores) de los usuarios:

-- Example: Average number of pageviews by purchaser type.

WITH
  UserInfo AS (
    SELECT
      user_pseudo_id,
      COUNTIF(event_name = 'page_view') AS page_view_count,
      COUNTIF(event_name IN ('in_app_purchase', 'purchase')) AS purchase_event_count
    -- Replace table name.
    FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    -- Replace date range.
    WHERE _TABLE_SUFFIX BETWEEN '20201201' AND '20201202'
    GROUP BY 1
  )
SELECT
  (purchase_event_count > 0) AS purchaser,
  COUNT(*) AS user_count,
  SUM(page_view_count) AS total_page_views,
  SUM(page_view_count) / COUNT(*) AS avg_page_views,
FROM UserInfo
GROUP BY 1;

Secuencia de vistas de página

Esta consulta muestra la secuencia de vistas de página que realizó cada usuario. La consulta ordena los resultados con los siguientes campos para que los eventos se muestren en el orden en que ocurrieron para el usuario, incluso si se enviaron en el mismo lote:

  • user_pseudo_id
  • user_id
  • batch_page_id
  • batch_ordering_id
  • batch_event_index

Si bien el ejemplo limita los resultados solo a los eventos page_view, puedes usar la misma cláusula ORDER BY para ordenar correctamente todos los eventos quitando la condición de la cláusula WHERE para event_name.

La consulta también muestra cómo usar funciones definidas por el usuario GetParamString y GetParamInt para reducir la duplicación y facilitar la comprensión y el mantenimiento de tus consultas.

-- Example: Sequence of pageviews.

/** Temporary function to retrieve the string_value of an event parameter by event name. */
CREATE TEMP FUNCTION GetParamString(event_params ANY TYPE, param_name STRING)
AS ((SELECT ANY_VALUE(value.string_value) FROM UNNEST(event_params) WHERE key = param_name));

/** Temporary function to retrieve the int_value of an event parameter by event name. */
CREATE TEMP FUNCTION GetParamInt(event_params ANY TYPE, param_name STRING)
AS ((SELECT ANY_VALUE(value.int_value) FROM UNNEST(event_params) WHERE key = param_name));

SELECT
  user_pseudo_id,
  user_id,
  batch_page_id,
  batch_ordering_id,
  batch_event_index,
  event_name,
  GetParamInt(event_params, 'ga_session_id') as ga_session_id,
  GetParamString(event_params, 'page_location') as page_location,
  GetParamString(event_params, 'page_title') as page_title,
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_name = 'page_view'
  -- Replace date range.
  AND _TABLE_SUFFIX BETWEEN '20240718' AND '20240731'
ORDER BY
  user_pseudo_id,
  user_id,
  batch_page_id,
  batch_ordering_id,
  batch_event_index;

Lista de parámetros de eventos

En la siguiente consulta, se enumeran todos los parámetros de eventos que aparecen en tu conjunto de datos:

-- Example: List all available event parameters and count their occurrences.

SELECT
  EP.key AS event_param_key,
  COUNT(*) AS occurrences
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, UNNEST(event_params) AS EP
WHERE
  -- Replace date range.
  _TABLE_SUFFIX BETWEEN '20201201' AND '20201202'
GROUP BY
  event_param_key
ORDER BY
  event_param_key ASC;

Cómo unirse a Google Ads

Para recuperar datos adicionales de Google Ads para tus eventos de Google Analytics, configura el Servicio de transferencia de datos de BigQuery para Google Ads y, luego, une el collected_traffic_source.gclid de los datos de eventos de Google Analytics al campo gclid de ads_ClickStats_customer_id de la transferencia de Google Ads.

Ten en cuenta que la exportación de datos de eventos de Google Analytics crea una tabla para cada día, mientras que la transferencia de Google Ads completa una sola tabla ads_ClickStats_customer_id por cliente.

Anterior
Comenzar
Siguiente
Consultas avanzadas de eventos