Базовые запросы для экспорта данных о событиях Google Аналитики 4 event data export

Примеры запросов на этой странице относятся к данным экспорта событий BigQuery для Google Аналитики 4. Информацию о таких запросах для событий Universal Analytics можно найти в справочном руководстве BigQuery для Universal Analytics.

Во всех указанных здесь запросах используются примеры наборов данных, поэтому результаты должны быть положительными. Вы можете использовать собственные данные экспорта событий BigQuery для ресурса Google Аналитики. Для этого найдите в любом запросе комментарий "-- Replace table" и замените ИД примера набора данных на ИД своего набора.Его можно узнать в интерфейсе BigQuery на странице проекта экспорта событий. Например, если для вашего набора данных экспорта BigQuery задан ИД my- first-gcp-project:analytics_28239234, замените:

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

на:

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

Запрос определенного диапазона дат

Чтобы создать запрос на определенный диапазон дат из набора данных для экспорта события в BigQuery, используйте в условии WHERE псевдостолбец _TABLE_SUFFIX. Дополнительную информацию можно найти в разделе Фильтрация в таблицах с помощью _TABLE_SUFFIX.

В следующем запросе подсчитываются уникальные события по дате и названию события за определенное количество дней и среди выбранных событий:

-- 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;

Количество новых и текущих пользователей

  • Чтобы узнать общее количество пользователей, необходимо посчитать количество уникальных идентификаторов user_id. Однако если ваш клиент Google Аналитики не отправляет идентификатор user_id с каждым обращением или вы не уверены в доступности этих данных, выполните подсчет уникальных псевдоидентификаторов user_pseudo_id.
  • Чтобы посчитать количество новых пользователей, примените тот же подход, только для следующих значений атрибута event_name:
-- Example: Get 'Total User' count and 'New User' count.

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 user_count,
  SUM(is_new_user) AS new_user_count
FROM UserInfo;

Среднее число транзакций на покупателя

По запросу, указанному ниже, можно получить среднее число транзакций на покупателя.

-- 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';

Значения для определенного названия события

По следующему запросу можно получить значение event_timestamp для всех событий purchase и связанные значения параметров событий:

-- 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';

Предыдущий запрос можно изменить таким образом, чтобы получить общие значения параметров событий вместо списка:

-- 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';

10 самых популярных товаров, добавленных в корзину

Указанный ниже запрос показывает 10 самых популярных товаров, добавленных пользователями в корзину.

-- 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_web_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;

Среднее количество просмотров страниц по типу пользователей (покупатели и остальные посетители)

По следующему запросу можно получить среднее количество просмотров страниц по типу пользователей (покупатели и остальные посетители):

-- 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;

Последовательность просмотров страниц

Следующий запрос помогает узнать, в какой последовательности пользователи просматривали страницы за один сеанс:

-- Example: Sequence of pageviews.

SELECT
  user_pseudo_id,
  event_timestamp,
  (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location')
    AS page_location,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = '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 '20201201' AND '20201202'
ORDER BY
  user_pseudo_id,
  ga_session_id,
  event_timestamp ASC;