Выполнение запросов нескольких таблиц GA4 events_ с _TABLE_SUFFIX в Google BigQuery

05 января, 2023

После того, как вы связали Google Analytics 4 и Google BigQuery, вы сможете выполнять SQL-запросы к своей таблице с данными events_. Но делать это можно по-разному. Как? Постараемся разобраться в этой статье.

Каждый день данные Google Analytics 4 в BigQuery хранятся в отдельной таблице. Она имеет название events_ГГГГММДД, где ГГГГММДД - год, месяц и день отчета:

Таблица с данными за каждый день в BigQuery

Таблица ежедневного экспорта (events_ГГГГММДД) создается после того, как в Google Analytics 4 соберутся данные обо всех событиях за день. Google обновляет данные о событиях в ежедневной таблице в течение 72 часов после даты фиксации этих событий, например когда набор событий поступает позже из Measurement Protocol или Firebase SDK. Так, если таблица относится к дате 20230101 (1 января 2023 года), Google Analytics будет обновлять в ней данные о событиях с временной меткой 20230101 (1 января 2023 года) до даты 20230104 (4 января 2023 года). Если возникнет необходимость повторно обработать данные, например для исправления ошибки, то ежедневная таблица может быть обновлена и по прошествии 72 часов.

Помимо основной таблицы, в которой хранится полный объем данных обо всех событиях дня, в BigQuery создается еще внутренняя промежуточная таблица с именем events_intraday_ГГГГММДД:

Промежуточная таблица events_intraday_

Она содержит записи сеансов для всех типов активности, зарегистрированной в течение дня. Эта таблица удаляется после того, как все данные загружаются в таблицу events_ГГГГММДД. Таким образом, в BigQuery вы работаете именно с набором таблиц events_:

Набор таблиц с данными за разные дни

Таблица потокового экспорта (events_intraday_ГГГГММДД) обновляется постоянно в пределах суток, например с 00:00:00 до 23:59:59 в часовом поясе ресурса. При смене даты данные о событиях начинают вноситься в новую таблицу.

Запрос к одному дню

Если вам нужны данные только за один день (например, 22 марта 2022 года), SQL-запрос будет выглядеть примерно так:

, где строчка osipenkovru.analytics_206854065.events_20220322 означает путь к вашей таблице BigQuery за этот день:

  • osipenkovru - ваш проект в Google Cloud;
  • analytics_206854065 - ваш набор данных (датасет);
  • events_20220322 - название таблицы с данными за конкретный день.

В Google BigQuery сам запрос и результат будут выглядеть так:

Запрос в BigQuery для всех данных GA4, но конкретной даты

Чтобы не ошибиться в написании пути, вы можете перейти в Google BigQuery, выбрать свой проект с экспортированными данными из Google Analytics 4 и на вкладке DETAILS скопировать значение из поля Table ID:

Table ID

Все таблицы в наборе данных

Запрос данных только за один день - это хорошо. Но в большинстве случаев вам потребуется запрашивать больший период времени и несколько таблиц из набора данных. Для этого используется _ TABLE _ SUFFIX и подстановочный знак со звездочкой * вместо таблицы к конкретным названием. Например, вот так:

, где event_* как раз свидетельствует о том, что нам необходимо запросить данные во всех таблицах датасета, а не конкретной одной, как это было в примере выше с 22 мартом 2022 года и таблицей events_20220322. Но такой запрос еще будет включать данные промежуточной таблицы с названием events_intraday_20220322. Об этом подробнее будет написано ниже.

В Google BigQuery сам запрос и результат будут выглядеть так:

Запрос в BigQuery для всех таблиц и всех данных GA4

Примечание: имейте в виду, что выбор всех столбцов (*) всех таблиц в вашем наборе данных не является хорошей практикой. В зависимости от количества собранных событий запрос большого количества данных может привести к значительным затратам.

Запрос к диапазону дат (статический)

При выполнении анализа статистики Google Analytics 4 в BigQuery для определенного диапазона дат следует использовать фиксированные даты начала и окончания периода. Например, с 1 марта по 22 марта 2022 года:

, где:

  • .events_* - конструкция с подстановочным знаком, означающая выбор всех таблиц в наборе данных;
  • _TABLE_SUFFIX BETWEEN '20220301' AND '20220322' - диапазон дат от 1 марта 2022 года (в формате ГГГГММДД) до 22 марта (в формате ГГГГММДД), который задаются с использованием _TABLE_SUFFIX, BETWEEN (между) и AND (и).

В Google BigQuery сам запрос и результат будут выглядеть так:

Запрос в BigQuery для всех данных GA4 и выбранного диапазона дат с указанием статических значений

Особенность этого запроса именно в подстановочном знаке в конце названия таблицы event_*, который означает выбор всех таблиц, а далее с помощью _TABLE_SUFFIX в следующей строке SQL-запроса вы уже задаете статический интервал дат (от и до).

Запрос к диапазону дат (динамический)

Диапазон дат можно задавать не только указав конкретные даты начала и конца периода, но и динамические. Например, скользящий период за последние 30 дней - сегодняшняя дата минус 30 дней до вчерашнего дня включительно:

, где:

  • FORMAT_DATE - функция форматирования даты в нужном виде;
  • DATE_SUB - функция, которая вычитает указанный интервал времени из даты. В нашем случае, из другой функции CURRENT_DATE - текущей даты;
  • CURRENT_DATE - функция возвращает текущую дату для указанного часового пояса или часового пояса по умолчанию;
  • INTERVAL 30 DAY и INTERVAL 1 DAY - поддерживаемые аргументы функции, позволяющие задать указанный интервал времени к дате.

Таким образом, первая дата после BETWEEN будет равняться значению, полученному из текущей даты минус 30 дней, а второй интервал после AND - текущая дата минус 1 день, то есть до вчера.

Примечание: подробнее об описанных функциях читайте в официальном справочнике Google BigQuery.

В Google BigQuery сам запрос и результат будут выглядеть так:

Запрос в BigQuery для всех данных GA4 и выбранного диапазона дат с указанием динамических значений

Запрос к диапазону дат (статический и динамический)

В BigQuery вы можете задать комбинацию фиксированной даты начала и динамической даты окончания. Например, 1 марта 2022 года минус 1 день от сегодняшней даты, то есть до вчерашнего дня включительно:

В Google BigQuery сам запрос и результат будут выглядеть так:

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

Промежуточные таблицы

Как вы уже знаете, при экспорте данных из GA4 в BigQuery в течение дня создается промежуточная таблица с названием events_intraday_ГГГГММДД. Именно часть _intraday_ отличает промежуточные таблицы от итоговых в вашем проекте:

Обычные

Промежуточные

Промежуточная таблица удаляется ежедневно в следующих случаях:

  • если помимо потоковой передачи вы используете ежедневный экспорт;
  • если создание дневной таблицы завершено.

Для новых пользователей не включаются данные о кампанииисточнике и канале.

Самый простой способ включить в свой запрос обе таблицы - использовать подстановочный знак в сочетании с более широким _TABLE_SUFFIX фильтром. С этим условием он будет включать в набор данных любую таблицу, которая начинается с events_даты и содержит дату в формате ГГГГММДД.

, где REGEXP_EXTRACT - функция, возвращающая подстроку, соответствующую регулярному выражению.

Примечание: убедитесь, что ваш набор данных не содержит других таблиц с названием, начинающимся с events_

В Google BigQuery сам запрос и результат будут выглядеть так:

Запрос с промежуточными таблицами

Материал основан на статье ga4bigquery.com

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

По контекстной, таргетированной рекламе и аналитике