Расшифровка поля event_timestamp в Google BigQuery

Когда вы экспортируете данные из Google Analytics 4 в BigQuery, в таблице событий создаются определенные параметры события. Среди них - поле event_date и event_timestamp. Если назначение event_date можно догадаться по его названию, то параметр event_timestamp расшифровать не так-то просто. В этом материале я постараюсь это сделать, используя известные функции даты и времени. И, главное, ответить на вопрос - зачем оно нужно и какие секреты таит в себе это поле?

Итак, поле event_date имеет строковый тип данных (STRING) и содержит дату в часовом поясе вашего ресурса Google Analytics 4 в формате ГГГГММДД, когда было зарегистрировано событие.

На скриншоте выше - пример значения event_date - 20240221. Это дата в формате ГГГГММДД, то есть 21 февраля 2024 года. Фактически, поле event_date - это просто столбец с датой зарегистрированного события на основе установленного часового пояса в вашем ресурсе без какой-либо конкретики.

Но ваш сайт могут посещать пользователи из разных городов и стран, и совершать на нем события, имея различные часовые пояса. Эта проблема известна достаточно давно. В свое время в блоге я писал материал по определению местного времени пользователя с помощью Google Tag Manager.

Поэтому если вам нужно провести какой-либо анализ в разных часовых поясах, не забудьте о настройке в интерфейсе Google Analytics 4. Если вы не будете учитывать это, то получите неверные результаты. Например, при построении SQL-запросов по полю event_date может быть искажена статистика по времени и дням недели в разрезе конверсионных действий.

Вот как различается анализ графика по количеству просмотров страниц в час между пользовательским интерфейсом GA4 и BQ. Данные пользовательского интерфейса GA4 основаны на часовом поясе ресурса, а данные BQ - на основе времени UTC. В результате данные из BQ смещаются на 2 часа, что приводит к расхождению между двумя наборами данных. Это связано с тем, что для часового пояса свойства установлено значение UTC+2, а для данных из BQ по умолчанию установлено значение UTC.

Примечание: другую проблему с часовыми поясами описал Симо Ахава (Simo Ahava) в блоге Team Simmer. Если вам интересно, почему временные метки в таблице потокового экспорта данных events_intraday_ устанавливаются в будущем, когда их запрашивают с помощью SQL-запросов, обязательно прочитайте этот материал.

И вот здесь вам может помочь поле event_timestamp. В схеме данных оно расположено рядом с event_date, имеет целочисленный тип данных (INTEGER) и содержит время в микросекундах, когда событие было зарегистрировано в Google Analytics 4.

Значение, хранящееся в этом поле, имеет формат UTC (Coordinated Universal Time, Всемирное координированное время). Это стандарт, по которому общество регулирует часы и время. UTC является основой для определения часовых поясов и времени в различных регионах мира. Большинство стран используют UTC в качестве стандарта для своих национальных часовых поясов.

Временная метка event_timestamp представляет собой абсолютный момент времени, не зависящий от часового пояса. Однако когда отображается значение временной метки, оно обычно преобразуется в удобочитаемый формат, состоящий из обычной даты и времени (ГГГГ-ММ-ДД ЧЧ:ММ:СС) и часового пояса. Это не внутреннее представление event_timestamp, а всего лишь понятный человеку способ описать момент времени, который представляет временная метка. Другими словами, данные, хранящиеся в поле event_timestamp, имеют формат UNIX (количество миллисекунд, прошедших с 1 января 1970 года), и он всегда соответствует времени UTC.

Часовой пояс вашего ресурса (reporting time zone) в GA4 можно посмотреть в разделе Администратор (Admin) - Ресурс (Property) - Информация о ресурсе (Property details):

В моем счетчике часовой пояс отчетов UTC/GMT +3 , что означает, что в BigQuery я всегда буду видеть временные метки UTC+3, то есть со смещением в три часа.

Проверить можно следующим способом. Скопируйте любое значение из поля event_timestamp вашей таблицы BigQuery, перейдите на сайт конвертера времени unixtimestamp.com, вставьте скопированную временную метку в поле Enter a Timestamp и нажмите кнопку Convert.

С помощью такого простого действия вы сможете преобразовать значение временной метки в формат, понятный человеку. Например, метка 1708521989791067 из event_timestamp соответствует дате 21 февраля 2024 года и времени 16:26:29 согласно моей временной зоне Your Time Zone (UTC/GMT +3) и 21 февраля 2024 года 13:26:29 в формате UTC/GMT +0. Время UTC+0 соответствует среднему времени по Гринвичу в Великобритании.

Таким образом, во временной метке event_timestamp зашифровано определенное значение в микросекундах, которое сильно детализирует статистику по событиями Google Analytics 4 и позволяет выполнять различные SQL-команды, строя нужные срезы данных. А используя различные функции временных меток BigQuery (timestamp functions), даты и времени (datetime functions) и поле event_timestamp, вы можете "манипулировать" итоговым значением.

В качестве примера давайте выполним простой SQL-запрос, который будет отображать первые 10 записей с полями event_date и event_timestamp за 21 февраля 2024 года:

, где:

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

Результат в Google BigQuery будет выглядеть так:

Скорректируем свой запрос, добавив функцию TIMESTAMP_MICROS к полю event_timestamp.

Она преобразует количество микросекунд с 1 января 1970 г. 00:00:00 в формате UTC и возвращает метку времени:

В результатах запроса вы увидите новый столбец, в котором дата и время из метки event_timestamp будут отображаться в удобочитаемом виде. Как видите, это UTC+0.

Вы можете извлечь из этого значения часть метки времени, например, час. Для этого воспользуйтесь функцией EXTRACT и аргументом HOUR:

Функция EXTRACT возвращает значение, соответствующее указанному аргументу. В нашем случае HOUR. Но вы можете использовать и другие:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR
• DAYOFWEEK
• DAY
• DAYOFYEAR
• WEEK
• MONTH
• QUARTER
• YEAR
• DATE
• DATETIME
• TIME
• и другие

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

А если теперь вы желаете отобразить час совершения события в своем часовом поясе, указанном в настройках Google Analytics 4 (напоминаю, что у меня - это UTC/GMT +3), то вы можете в функции EXTRACT использовать дополнительный параметр AT TIME ZONE:

, где значение "Europe/Moscow" - это название часового пояса из базы данных (сокр. tz или Zoneinfo). Полный список часовых поясов можно посмотреть здесь или здесь.

Если часовой пояс не указан, используется часовой пояс по умолчанию - UTC. Определенные функции даты и времени позволяют переопределить часовой пояс по умолчанию и указать другой. Вы можете указать часовой пояс, задав название часового пояса или смещение часового пояса от UTC. То есть вместо "Europe/Moscow" использовать +03:

Таким образом, пример вашей SQL-команды может выглядеть так:

Query results:

, где поле:

• hour_utc - час в формате UTC+0;
• hour_local1 - час в часовом поясе "Europe/Moscow" +3;
• hour_local2 - час со смещением +3 (аналогично hour_local1).

Есть и другая функция, которую вы можете использовать для перевода значения даты и времени в свой часовой пояс. Это функция DATETIME с соответствующим параметром. Если часовой пояс не указан, используется часовой пояс по умолчанию - UTC.

В результате, наш первоначальный SQL-запрос пополнился еще тремя строчками:

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

Получается, что поле event_timestamp точнее. Оно записывает точное время (в микросекундах), когда произошло событие, а event_date записывает только дату. event_timestamp менее подвержен ошибкам часового пояса и при желании вы можете его задать в явном виде. Поле event_date хранится в часовом поясе вашего ресурса Google Analytics 4, а это значит, что сравнивать события в разных потоках данных или устройствах может быть сложно. Поле event_timestamp хранится в формате UTC, что упрощает сравнение событий из разных источников.

А если выполнить запрос, в котором применить различные варианты извлечения данных из временной метки event_timestamp? Да, это отличное решение, демонстрирующее различия в отображении.

Скопируйте этот SQL-запрос и вставьте его в BigQuery, заменив путь к таблице на свой:

И результат в BigQuery:

С помощью функции EXTRACT и нужного аргумента можно извлечь любое значение из временной метки event_timestamp - дату, только год, только месяц, только день, только час, только минуты, только секунды и т.д.

Функция FORMAT_DATE позволяет преобразовать дату в строку в нужном нам формате. Этот формат задается еще одним аргументом и имеет различные варианты написания. Для данных Google Analytics 4, как правило, используется шаблон %Y%m%d или %d-%m-%Y, где:

• %Y - обозначает год в виде четырехзначного числа;
• %m – обозначает месяц в виде двухзначного числа;
• %d – обозначает день в виде в виде двухзначного числа.

А функция FORMAT_TIME используется для форматирования времени. Используя аргумент TIME, BigQuery преобразует микросекунды в тип данных TIME, который представляет собой только время без даты. А шаблон %T обозначает время в формате %H:%M:%S (часы:минуты:секунды). Таким образом, данное выражение возвращает точное время совершения события в формате 'часы:минуты:секунды' из поля event_timestamp в BigQuery.

Несмотря на то, что временная метка event_timestamp гораздо точнее, чем event_date, ей тоже нельзя доверять на 100% "из коробки" BigQuery. Почему? Все дело в том, в Google Analytics 4 большинство событий, которые активируются пользователями на сайте или в приложении на стороне клиента, отправляются не по одному, а группой (пакетом). Если вы раньше слышали про группировку событий GA4 перед отправкой (event grouping), то это оно и есть.

А это означает, что когда событие происходит, оно не отправляется сразу в Google Analytics 4. Вместо этого клиентская библиотека ожидает возникновения других событий в течение нескольких секунд, после чего отправляются все события, произошедшие во время этого пакетного окна.

Группировка событий и отправка их пакетом - не такая уж редкость для аналитических инструментов. Она особенно распространена на мобильных устройствах, где, например, постоянные сетевые запросы могут быстро разрядить батарею устройства. Из-за этого у Google Analytics 4 есть существенный недостаток - отдельные события не имеют временных меток и индикатора последовательности их отправки. То есть нельзя точно установить, когда произошло определенное событие или какова была последовательность событий в каждом конкретном пакете, поскольку все события имеют общую временную метку самого пакетного запроса.

В этом легко убедиться, выполнив такой SQL-запрос в BigQuery:

, где вместо osipenkovru-373609.analytics_206854065.events_20240221 используйте путь к своим данным.

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

Как видите на скриншоте выше, несколько строк имеют общий user_pseudo_id (уникальный идентификатор пользователя, Client ID) и объединены одним и тем же event_timestamp. Сама временная метка ничего не можем нам ничего сказать, поскольку имеет одинаковое для всех трех событий значение. И поле event_bundle_sequence_id тоже одинаково для всех записей, так как именно оно отвечает за порядковый идентификатор пакета, в котором были загружены события.

И как же быть? Что делать? Как проводить анализ последовательности, когда есть несколько событий, которые были совершены одним и тем же пользователем и имеют одну и ту же временную метку. Какое событие было первым, какое последним, а какие расположились посередине? Именно Симо Ахава обратил внимание на такой недостаток и сам же предложил решение - добавить к каждому событию специальный параметр, чтобы с ним была связана временная метка.

Если в своем проекте вы используете Google Tag Manager, то сделать это можно с помощью пользовательской переменной Собственный код JavaScript, добавив такой код, который генерирует временную метку:

В Google Tag Manager это выглядит так:

Примечание: вы также можете использовать шаблон переменной от luratic.

После создания переменной вам необходимо добавить ее во все теги событий GA4:

А чтобы упростить добавление параметра события во все теги используйте пользовательскую переменную типа Тег Google: настройки события.

Если вы не работаете с диспетчером тегов Google, а используете библиотеку gtag.js, процесс добавления временной метки очень похож - вам просто нужно будет сгенерировать ее с помощью JavaScript. Пример:

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

И результат от проделанной работы:

Видно, как время регистрации событий в строках 4-8 чуточку, но различаются. Теперь вполне четко можно определить последовательность срабатывания событий. В этом примере они идут в правильном порядке (по возрастанию), хотя Симо специально не упорядочивал их. Если вы посмотрите на строки 11–13 , то увидите, что три события в этом конкретном пакете расположены в неправильном порядке. В зависимости от параметра custom_timestamp сначала идет page_view, затем view_item, и, наконец, fetch_user_data.

Интересно? Безусловно! Но и в такой настройке есть свои особенности. Специальный параметр не собирается для событий, которые GA4 генерирует независимо от ваших тегов событий. Автоматически регистрируемые события session_start, first_visit и user_engagement являются такими событиями. Но если session_start и first_visit можно связать с первым событием просмотра страницы page_view, то событие user_engagement является исключением, которое невозможно отследить с собственной временной меткой. Будут небольшие соответствия, но, по крайней мере, для всех важных событий теперь у вас есть способ правильно упорядочить их в вашем хранилище данных BigQuery. И спасибо за такой прекрасный и простой способ Симо!

Но в то же время он сам не рекомендует создавать в GA4 специальный параметр для временной метки, поскольку это может привести к серьезным проблемам с количеством уникальных элементов (кардинальности). Используйте детальный анализ в самом Google BigQuery, используя приведенные выше функции временных меток, даты и времени.