Объединение ежедневного экспорта и потоковой передачи данных Google Analytics 4 в BigQuery

Наверняка вы знаете, что в интерфейсе Google Analytics 4 данные в отчетах и Исследованиях недоступны за сегодняшний день. Но что делать, если в вашей компании требуется анализировать статистику по событиям практически в режиме реального времени? Тогда вам необходимо установить связь с Google BigQuery, настроив при этом ежедневный экспорт и потоковую передачу данных, а затем объединить данные из этих таблиц с помощью SQL.

Задержка сбора данных

Уверен, что если вы читаете этот материал, то уже слышали о задержке данных, когда информация о ваших событиях/событиях-конверсиях появляется в отчетах Google Analytics 4 в течение 24 часов с момента их активации или настройки. Иногда задержка может составлять до 48 часов. Но это еще не все.

А поскольку в Google Analytics 4 вы можете отправлять свои собственные данные извне (офлайн-события) с помощью Firebase SDK или Measurement Protocol, то для обработки такого рода событий Google нужно еще некоторое время. В таком запросе вы можете использовать временную метку timestamp_micros, которая должна находиться в пределах 72 часов с момента регистрации события. Если ее не указывать, то отправляемое офлайн-событие будет записано в момент его загрузки. Таким образом, из-за этого окна в 72 часа Google может обновлять и пересчитывать свои табличные данные в интерфейсе.

Примечание: подробнее о том, как GA4 собирает, обрабатывает и отображает данные в вашем ресурсе, читайте в этом материале.

24 - 72 часа? Да еще и нельзя посмотреть статистику за сегодняшний день? Что же делать? Самое простое и очевидное решение - это использовать связь Google Analytics 4 - BigQuery и потоковую передачу данных.

Типы экспорта

Но перед тем, как мы выполним сам запрос, давайте вспомним какие существуют типы экспорта при установлении связи вашего ресурса Google Analytics 4 с BigQuery.

Выбрав проект в Google Cloud и задав местоположение (регион) данных, Google попросит вас указать тип экспорта. На момент написания этого материала существует два типа экспорта данных о событиях:

1. ежедневный (daily) - полная выгрузка данных за прошлый день (раз в день);
2. потоковый (streaming) - выгрузка данных в режиме реального времени (постоянно), по мере совершения пользователями событий (доступна после добавления платежного аккаунта).

Если в Google Cloud вы добавили платежный аккаунт, то вы можете использовать оба типа экспорта.

В связи с текущими событиями в мире Google приостановил работу для пользователей из России. На момент публикации этого материала вы не сможете привязать свою банковскую карту, выпущенную на территории РФ. Наиболее простое и эффективное решение - выпустить карту другой страны (Казахстан, Киргизия, Армения и т.д.), чтобы иметь возможность пользоваться Google Cloud и оплачивать счета.

Помимо стандартных типов экспорта, которые были добавлены в Google Analytics 4 достаточно давно, в 2023 году появился еще один тип экспорта - пользовательские данные (user-data):

При экспорте пользовательских данных GA4 создает в вашем проекте BigQuery две новые таблицы:

1. pseudonymous_users_ - ежедневная таблица экспорта пользовательских данных по идентификатору user_pseudo_id (по сути, client_id);
2. users_ - ежедневная таблица экспорта пользовательских данных по идентификатору user_id.

Однако сейчас нас интересуют не пользовательские данные, а таблицы ежедневного и потокового экспорта данных.

Ежедневный экспорт данных (Daily)

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

Например, events_20220322 - это таблица с данными о событиях за 22 марта 2022 года. И так далее.

Напротив таблицы events вы можете видеть число в скобках:

• events_(1) означает, что в этой таблице доступны все данные о событиях Google Analytics 4 за предыдущий день;
• events_(2) означает, что в этой таблице доступны все данные о событиях Google Analytics 4 за предыдущие два дня;
• events_(51) означает, что в этой таблице доступны все данные о событиях Google Analytics 4 за предыдущие 51 день.

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

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

Общий объем ежедневных событий для экспорта составляет 1 000 000. Если объем экспорта значительно превысит один миллион событий в день, то экспорт может быть приостановлен незамедлительно, а повторных обновлений за предыдущие дни не будет.

Потоковый экспорт данных (Streaming)

Этот тип экспорта доступен только тогда, когда вы добавили платежный аккаунт и привязали активную банковскую карту в своем проекте Google Cloud.

Помимо основных/ежедневных таблиц events_, в которых хранится информация обо всех событиях вашего ресурса Google Analytics 4 за разные дни, в BigQuery создается еще таблица с именем events_intraday_ГГГГММДД:

Все данные, поступающие в реальном времени, хранятся в этой таблице и постоянно обновляются на текущую дату по мере поступления новых событий. Обратите внимание, что таблица events_intraday_ГГГГММДД также может содержать события предыдущего дня, если они еще не попали в таблицу events_ГГГГММДД. Как только эта дата будет обработана при ежедневном экспорте, вся информация будет удалена из таблицы events_intraday_ГГГГММДД.

Таблица потокового экспорта (events_intraday_ГГГГММДД) обновляется постоянно в пределах суток, например с 00:00:00 до 23:59:59 в часовом поясе ресурса. При смене даты данные о событиях начинают вноситься в новую таблицу. Другими словами, потоковый экспорт данных в BigQuery - это и есть мониторинг данных в режиме реального времени для Google Analytics 4. Чуть ниже мы с вами выполним SQL-запрос, объединяющий ежедневный экспорт и потоковую передачу данных.

Если вы используете тестовую среду BigQuery (sandbox, песочница), передача данных о событиях в течение дня не производится (недоступна потоковая передача данных), а также на ваш проект BigQuery накладываются дополнительные ограничения. Наиболее чувствительное - это срок хранения данных в таблицах, который по умолчанию составляет 60 дней.

За использование потокового экспорта взимается плата - 0,05$ за 1 гибибайт данных (это соответствует ~600 000 событий Google Analytics 4, хотя это число будет зависеть от размера событий). В BigQuery отдельные строки рассчитываются с использованием минимального размера 1 КБ для любого события. То есть если ваш ресурс GA4 получает в среднем 200 000 событий в день, а их общей объем не превышает 200 мегабайт, то планируйте 0,01$ в день на потоковый экспорт. В эти затраты не входят сами SQL-запросы (объем обрабатываемых данных).

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

• traffic_source.name (параметр в отчете Кампания пользователя);
• traffic_source.source (параметр в отчете Источник пользователя);
• traffic_source.medium (параметр в отчете Канал пользователя).

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

SQL-запрос для ежедневного экспорта

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

, где:

• osipenkovru-373609 - ваш проект в Google Cloud;
• analytics_206854065 - ваш набор данных (датасет);

А используя подстановочный знак * в названии таблицы, изменяя в ней день, месяц или год, вы можете обращаться к нужному диапазону дат. Для этого вместе с подстановочным знаком и _TABLE _SUFFIX используются операторы BETWEEN (между) и AND (и). В данном примере - это диапазон дат с 1 февраля по 11 февраля 2024 года. GROUP BY - это группировка по полю event_date, а ORDER BY - группировка по дате по возрастанию (ASC).

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

Этот запрос я выполнил 11 февраля, поэтому данных за текущий день в таблицах events_ еще нет, поскольку они еще не обработаны. Для получения результатов за текущий день, необходимо использовать таблицы потокового экспорта.

SQL-запрос для потокового экспорта

Чтобы получить доступ только к данным в реальном времени, мы можете использовать тот же код, изменив только имя таблицы на events_intraday_, поскольку имена всех столбцов точно такие же, как при ежедневном экспорте:

Результат в Google BigQuery:

Этот запрос отображает количество пользователей за сегодняшний день. Однако не забывайте, что поскольку в таблице потоковой передачи данных отображаются данные в реальном времени и обновляются по мере регистрации новых событий, ваш запрос сейчас может отличаться от точно такого же запроса, но который вы выполните чуть позже, например, через 30 минут или час.

Комбинированный SQL-запрос

Как быть, если необходимо помимо исторических данных и статистики за вчерашний день в таблице events_ отобразить результаты запроса из таблицы потоковой передачи данных events_intraday_? То есть фактически объединить данные из таблицы events_ и events_intraday_?

Самый простой способ объединить ежедневный экспорт и потоковую передачи данных Google Analytics 4 в BigQuery - это использовать подстановочный знак в сочетании с более широким фильтром _TABLE_SUFFIX и функцию регулярных выражений REGEXP_EXTRACT. Ее необходимо добавить после оператора WHERE, заключив _TABLE_SUFFIX в круглые скобки (потому что это функция) и задав аргумент в виде конструкции регулярного выражения '[0-9]+', чтобы получилось так:

При таком условии в набор данных будет включена любая таблица, которая начинается с events_ в формате ГГГГММДД. Перед выполнением запроса убедитесь, что ваш набор данных не содержит других таблиц с заголовком, начинающимся с event_, чтобы они не были включены в результаты.

Результат выполнения SQL-запроса будет с данными за сегодняшнюю дату, то есть произойдет объединение таблиц events_ и events_intraday_:

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

Объединение с помощью UNION ALL

Есть и другой способ объединения нескольких таблиц в одну - это использовать оператор UNION ALL для объединения результатов двух или более запросов. Он возвращает все строки из всех запросов, включая дубликаты.

А поскольку наши вышеприведенные SQL-запросы одинаковы и их схема данных не отличается, итоговая конструкция будет выглядеть так:

, где вместо osipenkovru-373609.analytics_206854065.events_* и osipenkovru-373609.analytics_206854065.events_intraday_* вам следует задать свои пути к таблицам.

Query results в BigQuery: