Жмем синюю кнопку «Связать» (можно создать только одну связь, после чего кнопка станет неактивной). Далее выбираем имя проекта BigQuery, в который хотим стримить данные, а также частоту экспорта. Проверив все настройки, нажимаем «Отправить» и получаем статус «Связь создана». Более подробно процесс описан, например, здесь, мы не будем на нем останавливаться, а перейдем к работе с данными в BQ.
Вычисляем источник сессии
В результате стриминга в указанном проекте BigQuery появится датасет с именем вида analytics_123456789, а в нем два набора таблиц: events_ГГГГММДД и events_intraday_ГГГГММДД, содержащих данные за указанный в их имени день. Таблица events_intraday_ обычно всего одна, она содержит еще не обработанные полноценно Гуглом данные GA4, то есть, поступающие в реальном времени. После окончания некоей фоновой обработки данные станут более полными, и таблица переместится в группу таблиц events_.
Схемы таблиц одинаковы и содержат довольно много полей, но мы в рамках статьи ограничимся лишь задачей определения источников трафика, поэтому рассмотрим только некоторые из них.
Поле event_params имеет тип RECORD, то есть содержит в себе несколько строк, как бы являясь таблицей внутри таблицы. В состав этой «вложенной таблицы» входят столбец key, содержащий название параметра события, и ряд столбцов <type>_value, где <type> — обозначение типа значения параметра. Если значение параметра события целочисленное, оно хранится в поле event_params.int_value, а если строковое, то в event_params.string_value.
Из параметров событий нас интересуют следующие.
Вытащить отдельные «строки» из поля типа RECORD можно, просто обратившись к нему как к таблице. Так что запрос для выбора всего необходимого нам выглядит так:
WITH all_hits AS
(
SELECT event_timestamp,
TRIM(user_pseudo_id, '"') AS client_id,
(SELECT value.int_value FROM e.event_params WHERE key='ga_session_id') AS session_id,
(SELECT value.int_value FROM e.event_params WHERE key='ga_session_number') AS session_number,
(SELECT value.string_value FROM e.event_params WHERE key='source') AS hit_source,
(SELECT value.string_value FROM e.event_params WHERE key='medium') AS hit_medium,
(SELECT value.string_value FROM e.event_params WHERE key='campaign') AS hit_campaign,
(SELECT value.string_value FROM e.event_params WHERE key='content') AS hit_content,
(SELECT value.string_value FROM e.event_params WHERE key='term') AS hit_term,
traffic_source.source AS traffic_source,
traffic_source.medium AS traffic_medium,
traffic_source.name AS traffic_campaign,
event_name,
(SELECT value.string_value FROM e.event_params WHERE key='page_location') AS page_location,
(SELECT value.string_value FROM e.event_params WHERE key='page_referrer') AS page_referrer,
(SELECT value.string_value FROM e.event_params WHERE key='page_title') AS page_title
FROM `project_name.analytics_123456789.events_*` AS e
)
SELECT * FROM all_hits
Теперь мы имеем источник хита hit_source и источник клиента traffic_source. Но нам бы очень пригодился источник сессии, не так ли? Такого готового параметра в данных GA4 почему-то нет, поэтому мы найдем его сами. Для этого просто возьмем первый непустой источник хита в сессии:
sources AS
(
SELECT *
FROM
(
SELECT
MIN(event_timestamp) OVER (PARTITION BY client_id, session_id) AS first_hit,
event_timestamp,
client_id,
traffic_source, traffic_medium, traffic_campaign,
session_id,
-- склеим источник, канал и кампанию, чтобы взять только те тройки параметров,
-- в которых хотя бы один из них не пуст
FIRST_VALUE(CONCAT(hit_source, "/", hit_medium, "/", hit_campaign) IGNORE NULLS)
OVER (PARTITION BY client_id, session_id
ORDER BY event_timestamp
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_channel
FROM all_hits
)
-- нам нужен только первый хит сессии
WHERE event_timestamp = first_hit
)
Эти данные и будем считать источниками сессии. Приклеим их ко всем хитам с событиями:
SELECT h.*,
REGEXP_EXTRACT(first_channel, r"([^/]*)/") AS session_source,
REGEXP_EXTRACT(first_channel, r"([^/]*)/", 1, 2) AS session_medium,
REGEXP_EXTRACT(first_channel, r"[^/]*$") AS session_campaign
FROM all_hits h
LEFT JOIN sources s ON s.session_id = h.session_id AND s.client_id = h.client_id
Источник события
Стандартная задача маркетинговой аналитики: какие источники сколько принесли конверсионных действий. Найдем, например, источники регистраций пользователей в нашем сервисе с атрибуцией по последнему значимому клику.
1. Выберем все хиты с событиями регистраций:
(
SELECT client_id, event_timestamp AS reg_time
FROM all_hits
WHERE event_name = 'registration'
) AS regs
2. Подтянем к ним все предшествующие хиты и хитовые источники:
LEFT JOIN
(
SELECT client_id,
event_timestamp AS event_time,
hit_source AS event_source,
hit_medium AS event_medium,
hit_campaign AS event_campaign,
hit_content AS event_content,
hit_term AS event_term
FROM all_hits
WHERE hit_source IS NOT NULL
) AS ses ON ses.client_id = regs.client_id AND ses.event_time <= regs.reg_time
3. Возьмем последний значимый источник события (хита):
SELECT DISTINCT * EXCEPT (source_time, event_time)
FROM
(
SELECT regs.client_id AS client_id,
CASE -- Несколько преобразований, которые Гугл поленился сделать за нас
WHEN event_source='away.vk.com' THEN 'vk'
WHEN event_source IN ('yandex.ru', 'yandex.kz', 'yandex.by') AND utm_medium='referral'
THEN 'yandex'
ELSE event_source
END AS event_source,
CASE
WHEN event_source IN ('yandex.ru', 'yandex.kz', 'yandex.by') AND utm_medium='referral'
THEN 'organic'
ELSE event_medium
END AS event_medium,
IF(event_campaign IN ('(direct)', '(organic)', '(referral)'),
'(not set)', event_campaign) AS event_campaign,
event_content, event_term,
FIRST_VALUE
( -- Берем последний перед регистрацией значимый источник. Внутренние источники и
-- прямые переходы не учитываем
IF(REGEXP_CONTAINS(event_source,
r'СЮДА МОЖНО ВСТАВИТЬ РЕГУЛЯРНОЕ ВЫРАЖЕНИЕ, ОТРАЖАЮЩЕЕ ВАШИ ДОМЕНЫ, ЧТОБЫ НЕ СЧИТАТЬ ИХ ИСТОЧНИКАМИ|(direct)')
OR utm_source IS NULL,
NULL, event_time) -- берем время последнего события со значимым источником
IGNORE NULLS
) OVER (PARTITION BY ses.clientId, user_id ORDER BY event_time DESC) AS source_time,
event_time
FROM
(
SELECT client_id, event_timestamp AS reg_time
FROM all_hits
WHERE event_name = 'registration'
) AS regs
LEFT JOIN
(
SELECT client_id,
event_timestamp AS event_time,
hit_source AS event_source,
hit_medium AS event_medium,
hit_campaign AS event_campaign,
hit_content AS event_content,
hit_term AS event_term
FROM all_hits
WHERE utm_source IS NOT NULL
) AS ses ON ses.client_id = regs.client_id AND ses.event_time <= regs.reg_time
)
WHERE source_time = event_time
В этом алгоритме рассматривается последовательность хитов, у каждого из которых может быть свой источник. Если же мы решили смотреть не на хиты, а на сессии, достаточно подставить вместо подзапроса выбора источников хитов код вычисления источников сессий из предыдущего раздела этой статьи.
Кстати, рекомендуется каждый подзапрос проверять на дубли. Случается, например, что у одного клиента попадается два хита с одинаковым временем, но с разными источниками. Как это объяснить, не вполне понятно, это можно только принять… К счастью, случай довольно редкий.
Резюме
Можно легко настроить стриминг хитовых данных Google Analytics 4 в BigQuery штатными средствами GA4 + GBQ.
Получить источники сессий можно, взяв источники первых хитов в рамках сессий. Значения source, medium, campaign, content и term хранятся в поле параметров события — event_params.
Чтобы получить последний значимый источник перед событием, нужно взять все хиты с этим событием, присоединить к ним по client_id все предшествующие хиты и выбрать из полученного значимые источники ближайшего к событию хита.
