Публичный набор данных BigQuery для Google Analytics 4

15 июля, 2023

Хотите начать изучение облачного хранилища Google BigQuery и написание SQL-команд для Google Analytics 4, но не имеете под рукой статистики, для которой можно было бы выполнять запросы? Не беда! Разработчики Google для всех желающих открыли публичный набор данных BigQuery для GA4.

Теперь вы можете просматривать тестовые данные, экспериментировать с функциями и изучать возможности работы Google Analytics 4 в BigQuery. Все, что для этого нужно - стремление к изучению нового и время на тренировки!

Данные, с которыми вы будете работать, принадлежат демонстрационному интернет-магазину Google Merchandise Store с настроенной электронной торговлей и активированной улучшенной статистикой. Однако этот публичный датасет следует отличать от тестового аккаунта GA4 для Google Merchandise Store, поскольку это другие данные. Публичный набор данных в BigQuery содержит обфусцированные данные за три месяца (с 1 ноября 2020 года по 31 января 2021 года), то есть немного искаженные и запутанные для анализа. Некоторые поля содержат плейсхолдеры, в том числе <Other>, NULL и '' ". Из-за обфускации внутренняя согласованность набора данных может быть несколько ограничена.

Сам набор данных в BigQuery называется ga4_obfuscated_sample_ecommerce. Для его использования вам понадобится доступ к проекту Google Cloud c включенным BigQuery API.

Содержание

Создание проекта в Google Cloud

Чтобы создать новый проект, используйте любую учетную запись Gmail и перейдите в Google Gloud по ссылке. Если вы работаете с облаком Google впервые, то вас попросят указать страну и согласиться с условиями использованиями Google Cloud Platform:

Начало работы с Google Cloud

В связи с текущими событиями в мире Google приостановил работу для пользователей из России. На момент публикации данной статьи вы не сможете привязать свою банковскую карту, выпущенную на территории РФ, чтобы использовать все возможности Google Cloud Platform, а также выбрать страну Россия из списка. Поэтому выберете из списка наиболее подходящую и нажмите Agree and continue.

На открывшейся странице Google Cloud в левом верхнем углу нажмите на Select a project:

Select a project

И создайте проект - NEW PROJECT:

Создание проекта

В поле Project name вы можете оставить автоматически добавленное название проекта, а можете указать свое собственное название, например BigQuery Public Dataset:

Название проекта

В завершение нажмите кнопку Create (Создать). В вашем Google Cloud будет создан новый проект. Об этом будет свидетельствовать уведомление в правом верхнем углу (Notifications):

Уведомление о создании проекта

Включение BigQuery API

Когда вы создали новый проект, API BigQuery включается автоматически. Но я рекомендую перепроверить это. Для этого в Google Cloud выберите свой проект из выпадающего списка:

Выбор проекта в Google Cloud

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

Подтверждение изменений в проекте

Включите BigQuery API:

Включение BigQuery API

На этом предварительная настройка вашего проекта в Google Cloud завершена.

Платное и бесплатное использование

Google Cloud и BigQuery являются платными инструментами инфраструктуры Google Cloud Platform. Но вы можете бесплатно использовать режим тестовой среды BigQuery (она же Песочница или Sandbox) с ограниченным набором функций BigQuery.

Тестовая среда BigQuery

Бесплатного тарифа должно быть достаточно для того, чтобы ознакомиться с набором данных и запустить примеры запросов. Для использования платного функционала вам необходимо активировать Billing, привязав активную банковскую карту к своей учетной записи. С нее в дальнейшем буду списываться деньги.

Использование публичного набора данных

Общедоступные наборы данных BigQuery по умолчанию доступны в консоли Google Cloud. Добавить публичный датасет с данными интернет-магазина Google к себе можно просто перейдя по ссылке.

Вам откроется страница BigQuery, а в ней публичный проект со множеством наборов данных:

Публичные датасеты BigQuery

Один из них - это датасет с данными Google Merchandise Store ga4_obfuscated_sample_ecommerce для Google Analytics 4:

Публичный набор данных BigQuery для Google Analytics 4

Внутри датасета отображается 92 таблицы с названием events_ Это и есть статистика за три месяца (с 1 ноября 2020 года по 31 января 2021 года), где каждая таблица с events_ - конкретный день:

Статистика в публичном датасете BigQuery для GA4 за три месяца

Теперь вы можете выполнять запросы к этому набору данных.

Примеры запросов

Перед тем, как вы начнете писать SQL-команды для публичного набора данных BigQuery для Google Analytics 4, я рекомендую ознакомиться с другими материалами моего блога:

Если вы не знаете, как правильно составить запрос или не уверены в корректности его написания, попробуйте инструмент ga4sql.com.

Чтобы создать свой первый запрос для публичных данных BigQuery, нажмите на Query и выберите один из доступных способов:

In new tab (В новой вкладке);
In split tab (На вкладке рядом).

Создание запроса

Вам откроется окно запроса, в котором вы будете писать SQL-команды.

Рассмотрим несколько примеров запросов.

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

Скопируйте нижеприведенный код и вставьте его на вкладку запроса:

SELECT
  COUNT(*) AS event_count,
  COUNT(DISTINCT user_pseudo_id) AS user_count,
  COUNT(DISTINCT event_date) AS day_count
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`

SELECT

COUNT(*) AS event_count,

COUNT(DISTINCT user_pseudo_id) AS user_count,

COUNT(DISTINCT event_date) AS day_count

FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`

В BigQuery это будет выглядеть так:

Запрос по количеству уникальных событий, пользователей и дней

Рядом с этим запросом появится флажок, свидетельствующей о его правильном написании. Дополнительно также указывается, сколько данных будет обработано:

Размер запроса

В этом примере при выполнении запрос будет обрабатывать 125.66 MB. Этот показатель помогает вам определить, целесообразно ли выполнять запрос. Как вы уже знаете, BigQuery является платным инструментом со своей тарификацией. На стоимость этого запроса влияют три фактора:

количество и размер столбцов, включенных в запрос, либо в результате, либо в операторе WHERE;
диапазон дат (количество ежедневных таблиц, используемых запросом);
количество событий в день, определяющее количество строк в каждой таблице.

Запросы с более длительными интервалами дат стоят значительно дороже. При прочих равных, запрос на год в 12 раз дороже запроса на месяц. Плата за использование по требованию зависит от количества байтов, обработанных каждым запросом, и составляет 5 долларов за 1ТБ. Первый ТБ в месяц пользователям предоставляется бесплатно. Поскольку вы используйте тестовую среду BigQuery при привязанного платежного аккаунта, вы можете не переживать за стоимость запросов.

Но как только вы начинаете работать с данными Google Analytics 4 вашего проекта и выполнять запросы к своей базе данных BigQuery, обязательно следите за своими лимитами. В моем блоге есть перевод статьи с сайта analyticscanvas.com, в которой описывается дорогостоящая ошибка подключения к облачному хранилищу данных в Looker Studio (ранее Google Data Studio). Незнание некоторых моментов оптимизации не освобождает вас от ответственности и может привести к серьезным материальным последствиям.

Для выполнения запроса нажмите кнопку Run (Выполнить):

Выполнение запроса

Под окном запроса появятся результаты его выполнения:

Количество событий (event_count) - 4295584
Количество пользователей (user_count) - 270154
Количество дней в наборе данных (day_count) - 92

Результат выполнения запроса

Именно так аналитик данных и работает со статистикой своего Google Analytics 4, экспортированной в Google BigQuery.

В официальной документации Google приведены несколько примеров базовых запросов.

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

Чтобы запросить определенный диапазон дат из набора данных экспорта событий BigQuery, используйте псевдостолбец _TABLE_SUFFIX в предложении WHERE вашего запроса. Подробное составление запросов с несколькими таблицами рассмотрено в этой статье.

Нижеприведенный SQL-запрос подсчитывает уникальные события по дате и имени события за определенный период дней и выбранные события в публичном наборе данных:

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;

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;

, где:

BETWEEN '20201201' - начальная дата 1 декабря 2020 года;
AND '20201202' - конечная дата 2 декабря 2020 года.

Вы можете изменить эти значения в интервале с 1 ноября 2020 года по 31 января 2021 года, поскольку в публичном датасете данные доступны именно за этот период.

Результат выполнения запроса:

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

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

Чтобы получить общее количество пользователей, подсчитайте количество различных user_id . Однако, если ваш клиент Google Analytics не отправляет обратно user_id с каждым обращением или если вы не уверены, подсчитайте количество различных user_pseudo_id (Client ID).

Для новых пользователей вы можете использовать тот же подход, описанный выше, но для следующих значений event_name:

first_visit
first_open

SQL-запрос для публичного набора данных выглядит следующим образом:

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;

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;

Этот запрос не даст никаких результатов, если вы не добавите платежный аккаунт к Google Cloud. Без биллинга вы получите такую ошибку:

Запрос не будет выполнен из-за отсутствия активного платежного аккаунта

После привязки запрос в интерфейсе BigQuery с результатом выполнения за период с 1 ноября по 30 ноября 2020 года и с активным платежным аккаунтом:

Количество пользователей (user_count) - 79421
Количество новых пользователей (new_user_count) - 71734

Запрос с количеством пользователей и новых пользователей

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

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

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

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

Период, используемый в запросе - с 1 по 31 декабря 2020 года. Его можно выполнить в тестовой среде BigQuery. Результат :

Запрос со средним количеством транзакций на одного покупателя

Значения для конкретного имени события

В следующем запросе показана event_timestamp (временная метка/время совершения события в микросекундах) для всех событий purchase и соответствующие значения параметров события:

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

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

Период, используемый в запросе - с 1 по 2 декабря 2020 года. Его можно выполнить в песочнице (тестовой среде). Результат выполнения в BigQuery:

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

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

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

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

Результат выполнения SQL-запроса:

Запрос с общим количеством значений параметров события для purchase

Топ-10 товаров добавлено в корзину

Этот запрос показывает первые 10 товаров, добавленных в корзину наибольшим количеством пользователей:

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;

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;

К сожалению, при выполнении этого запроса выдается следующая ошибка: Access Denied: Table bigquery-public-data:ga4_obfuscated_web_ecommerce.events_*: User does not have permission to query table bigquery-public-data:ga4_obfuscated_web_ecommerce.events_*, or perhaps it does not exist in location US. Биллинг аккаунта на это не влияет.

Ошибка выполнения запроса из документации Google

Но вы можете попробовать использовать данную SQL-команду для данных своего ресурса Google Analytics 4. Например, для моего сайта osipenkov.ru статистика с 1 по 30 июня 2023 года по топ-10 товаров, добавленных в корзину, будет выглядеть так:

Топ-10 товаров добавлено в корзину (osipenkov.ru)

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

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

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;

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;

Период, используемый в запросе - с 1 по 2 декабря 2020 года. Его также можно выполнить в песочнице (тестовой среде). Результат выполнения в BigQuery:

Строка false - статистика по просмотрам страниц непокупателей;
Строка true - статистика по просмотрам страниц покупателей;

Запрос со средним количеством просмотров страниц по типам покупателей

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

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

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;

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;

SQL-запрос выполним в тестовой среде BigQuery. Диапазон дат по умолчанию - 01-02 декабря 2020 года:

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

Список параметров события

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

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;

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;

Даты по умолчанию - с 1 по 2 декабря 2020 года, но вы можете изменить их на другие. Запрос доступен в песочнице BigQuery:

Запрос со списком параметров события

Понравилось изучать BigQuery и SQL-запросы? Приглашаю вас на онлайн-обучение GA4&BigQuery!

Метки: Google Analytics 4, Google BigQuery, Google Gloud, SQL

Публичный набор данных BigQuery для Google Analytics 4

Создание проекта в Google Cloud

Включение BigQuery API

Платное и бесплатное использование

Использование публичного набора данных

Примеры запросов

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

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

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

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

Значения для конкретного имени события

Топ-10 товаров добавлено в корзину

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

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

Список параметров события

Получайте бесплатные уроки и фишки

Еще статьи