Ассоциированные конверсии на базе Google Analytics 4 и BigQuery

С переходом с UA на GA4 пропал функционал анализа ассоциированных конверсий. А инструмент важный. Да, можно косвенно скомпенсировать его моделью атрибуции “на основе данных” или подобной распределенной, считающей вклад каждого из источников трафика в цепочке касаний. Но все же хотелось бы иметь под рукой понятным образом рассчитываемую таблицу ассоциированных конверсий, чтобы можно было оценить косвенный вклад того или иного источника трафика в копилку конверсий. Сделать нужно на базе актуального Google Analytics 4 в связке с Google BigQuery, позволяющим гибко оперировать собранными GA4 данными. В статье вы найдете не только код Google BQ для получения таблицы ассоциированных конверсий, но и пошаговое описание логики кода со скриншотами, что получается по ходу реализации.

Задача – сделать таблицу прямых и ассоциированных конверсий по source/medium и campaign, а также вычислить долю ассоциированных

Условия и допущения:

• Прямые конверсии по источнику — кол-во конверсий, совершенных в сессию этого источника. Используемая модель атрибуции конверсий — Last-Not-Direct (LND)
• Ассоциированные конверсии по источнику — кол-во конверсий, у которых данный источник присутствовал, но не был последним в цепочке
• По 1 юзеру может быть несколько цепочек событий вида s–s–…–s–c (где s – сессия, c – конверсия), если у него несколько конверсий. Каждая цепочка должна просчитываться отдельно.
• Одна конверсия по одному источнику трафика может быть либо в прямых, либо в ассоциированных, но не в обоих сразу. Так сумма прямых и ассоциированных конверсий по какому-либо источнику даст общее число уникальных конверсий с участием этого источника.

Дополнительные вводные данные:

• Для реализации подобного вам потребуется иметь предварительно настроенный импорт данных из GA4 в Google BQ. Песочницы Google BQ с ее ограничением в 90 дней хранения данных в таблицах вполне хватит для большинства проектов, где цикл привлечения клиента (конверсии) вполне укладывается в этот срок.
• Эта статья не про атрибуцирование событий — подразумевается, что вы знаете, откуда взять информацию об источнике трафика (source и medium). Здесь хороший материал про атрибуцирование конверсий.
• Приведенный ниже код является некоторой моделью, поясняющей логику решения задачи, нежели готовым решением, которую можно скопировать и запустить без изменений:) код потребует некоторой доработки под ваш проект (напр., названия событий и атрибуция неопределенного трафика) + формирование начальной плоской таблицы init на базе таблиц GA4
• Возможно, код Google BQ можно было написать оптимальней. Если у вас есть предложения как его улучшить или оптимизировать — добро пожаловать в комментарии!
• Реализация требует определенных знаний в области программирования и продуктов Google. Вы можете обратиться к автору статьи за платной реализацией под ваш проект.

Тестовые данные: 4 пользователя и 5 цепочек

Для упрощения понимания механизма, рассмотрим реализацию на неком небольшом тестовом массиве данных. Он охватывает несколько разных кейсов – 2 конверсии у 1 пользователя, несколько сессий с источником direct/none перед конверсией, повторяющиеся источники в цепочке, а также цепочка из 1 касания с неопределенным источником трафика direct/none.

Итого, есть 4 юзера с такими цепочками source/medium, где жирным выделена сессия с конверсией:

1. yandex/cpc > google/organic > yandex/cpc > vk/cpc (2 конверсии на 1 юзера)
2. yandex/cpc > yandex/organic > yandex/organic > vk/cpc > direct/none > direct/none
3. yandex/cpc > yandex/organic > google/organic > yandex/cpc
4. direct/none

В итоге должна получиться таблица:

К слову, % ассоциированных конверсий является вычисляемой метрикой и может легко считаться на стороне того же Looker или вашей BI-системы. То есть на стороне Google BQ этот столбец вычислять необязательно.

Этапы реализации

1. Из таблиц GA4 создаем плоскую таблицу содержащую события конверсий (call, email, chat, form и тп.) и события session_start. Столбцы таблицы:

1. 1. event_timestamp
   2. user_pseudo_id
   3. ga_session_id
   4. source
   5. medium

2. Добавляем вспомогательные столбцы:

1. 1. session_id — контакенация user_pseudo_id и ga_session_id для дальнейшего подсчета уникальных событий
   2. point — 0 (ноль) для события session_start и 1 для события конверсии
   3. sm — контакенация вида source / medium
   4. source_nd — копия столбца source, но с присвоенным NULL если source = (direct)
   5. medium_nd — копия столбца medium, но с присвоенным NULL если source = (direct) (обнуляем источник direct/none чтобы далее атрибуцировать конверсии по LND)

3. Промаркируем отдельные цепочки в рамках каждого юзера:

1. С помощью оконной функции (партиции по user_pseudo_id, упорядоченные по возрастанию event_timestamp) для каждого события посчитаем кумулятивную сумму point и запишем в отдельный столбец (назовем его cjm_no). Считаем со смещением на 1 строчку назад (ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), чтобы конверсионное событие попадало в предыдущую цепочку. А также добавляем условие IFNULL, поскольку первое событие в цепочке считаться со смещением не будет и будет выдавать NULL (заменим его на 0)

2. Так мы получим столбец, у которого будет уникальное число для каждой цепочки пользователя. Далее мы сможем производить операции для каждой отдельной цепочки пользователя с помощью оконной функции и партиционировании по user_pseudo_id и cjm_no одновременно.

3. Для каждого события добавляем полный массив с цепочкой source/medium без учета модели атрибуции. Так для цепочки 3 юзера мы увидим таблицу вида yandex/cpc > yandex/organic > google/organic > yandex/cpc

4. Добавляем столбец с цепочкой ассоциированных источников = полная цепочка за вычетом всех повторений источника LND (может оказаться пустым массивом, если касание было одно). Плюс на этом этапе, поскольку мы работаем уже с готовыми цепочками конверсий, можно избавиться от событий session_start, облегчив тем самым работу с запросом. Из цепочки 3 юзера останется yandex/organic > google/organic

5. Сделать финальную таблицу для Source / Medium:

1. source / medium — уникальное значение source / medium
2. direct_conv — прямые конверсии по модели LND = число уникальных конверсий (session_id), атрибуцированных данному source/medium по модели LND
3. assoc_conv — ассоциированные конвесии – число уникальных session_id, у которых в цепочке ассоциированных источников встречается данный source / medium

Итоговый код Google Big Query для получения таблицы атрибуцированных конверсий на примере тестовых данных

WITH
-- Получаем из таблиц GA4 изначальную плоскую таблицу с данными. Для упрощения я создам отдельную тестовую таблицу, соответствующую цепочкам в примере
init AS (
SELECT
1 AS event_timestamp, -- обозначим просто числами
'session_start' AS event_name, -- событие начала сессии
1 AS user_pseudo_id, -- допустим у нас будет 3 пользователя с ID = 1, 2, 3
1 AS ga_session_id, -- обозначим просто числами
'yandex' AS source, -- источник трафика
'cpc' AS medium, -- канал трафика
UNION ALL SELECT 2, 'call', 1, 2, 'google', 'organic' -- 1 событие конверсии
UNION ALL SELECT 3, 'session_start', 1, 3, 'yandex', 'cpc'
UNION ALL SELECT 4, 'call', 1, 4, 'vk', 'cpc' --2
UNION ALL SELECT 3, 'session_start', 2, 3, 'yandex', 'cpc'
UNION ALL SELECT 4, 'session_start', 2, 4, 'yandex', 'organic'
UNION ALL SELECT 5, 'session_start', 2, 5, 'yandex', 'organic'
UNION ALL SELECT 6, 'session_start', 2, 6, 'vk', 'cpc'
UNION ALL SELECT 7, 'session_start', 2, 7, '(direct)', '(none)'
UNION ALL SELECT 8, 'call', 2, 8, '(direct)', '(none)' --3
UNION ALL SELECT 8, 'session_start', 3, 11, 'yandex', 'cpc'
UNION ALL SELECT 9, 'session_start', 3, 12, 'yandex', 'organic'
UNION ALL SELECT 10, 'session_start', 3, 13, 'google', 'organic'
UNION ALL SELECT 11, 'call', 3, 14, 'yandex', 'cpc' --4
UNION ALL SELECT 12, 'call', 3, 15, '(direct)', '(none)' --5
),
-- Добавляем вспомогательные столбцы
additional_fields AS (
SELECT
*,
CONCAT(user_pseudo_id, '.', ga_session_id) AS session_id, -- комбинация ID пользователя и сессии позволит нам посчитать уникальные события
IF(event_name = 'session_start', 0, 1) AS point, -- Ставим баллы каждому событию: 0 событию session_start и 1 конверсионному событию
CONCAT(source, ' / ', medium) AS sm, -- Добавляем столбец source/medium
-- Добавляем столбцы с об-NULL-енными (direct) / (none) для дальнейшего определения LND атрибуции
IF(source = '(direct)', NULL, source) AS source_nd,
IF(source = '(direct)', NULL, medium) AS medium_nd
FROM init
),
-- Нумеруем отдельные цепочки для юзеров с >1 конверсией
cjm_numbering AS (
SELECT
*,
-- Считаем кумулятивную сумму баллов событий со сдвигом на 1 строку назад, чтобы получить одинаковое число для всех сессий + конверсии цепочки
-- Добавляем IFNULL с присваиваем нуля в качестве суммы, т.к. для первого события цепочки сумма со сдвигом назад считаться не будет
IFNULL(SUM(point) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0) AS cjm_no
FROM additional_fields
),
-- Атрибуцирование событий по модели LNDC
attribution_lnd AS (
-- Если в цепочке только 1 событие, атрибуцированное NULL (об-NULL-или в таблице additional_fields), возвращаем для него (direct)/(none)
WITH smc AS (
SELECT
*,
IFNULL(LAST_VALUE(source_nd IGNORE NULLS) OVER (PARTITION BY user_pseudo_id, cjm_no ORDER BY event_timestamp), '(direct)') AS s_lnd,
IFNULL(LAST_VALUE(medium_nd IGNORE NULLS) OVER (PARTITION BY user_pseudo_id, cjm_no ORDER BY event_timestamp), '(none)') AS m_lnd
FROM cjm_numbering
)
-- Плюс добавляем объединенный столбец source/medium атрибуции по LND
SELECT *, CONCAT(s_lnd, ' / ', m_lnd) AS sm_lnd FROM smc
),
-- Для каждого события добавляем полный массив с цепочкой трафика без учета модели атрибуции
cjm_arrays AS (
SELECT
*,
ARRAY_AGG (CONCAT(source, ' / ', medium)) OVER (PARTITION BY user_pseudo_id, cjm_no ORDER BY event_timestamp) AS sm_path,
FROM attribution_lnd
),
-- Делаем цепочку ассоциированных касаний = полная цепочка за вычетом всех повторений источника, присвоенный событию по LND атрибуции (удаляем источник прямой конверсии)
-- Плюс, поскольку массивы цепочек уже сформированы, мы можем отбросить события session_start и далее работать только с событиями конверсий, что облегчит запрос
associative AS (
SELECT
*,
ARRAY(SELECT x FROM UNNEST(sm_path) AS x WHERE x != sm_lnd) AS sm_path_assoc
FROM cjm_arrays WHERE event_name != 'session_start'
),

-- Получаем финальную таблицу
final AS (
SELECT
DISTINCT(additional_fields.sm), -- уникальные значения source/medium
(SELECT COUNT(DISTINCT(associative.session_id)) FROM associative WHERE associative.sm_lnd = additional_fields.sm) AS direct_conv, -- прямые конверсии
(SELECT COUNT(DISTINCT(associative.session_id)) FROM associative WHERE additional_fields.sm IN UNNEST(associative.sm_path_assoc)) AS assoc_conv -- ассоциированные конверсии
FROM additional_fields, associative
)

SELECT * FROM final ORDER BY direct_conv DESC, assoc_conv DESC

Заключение

Задача получения таблицы ассоциированных конверсий по source/medium решена. Долю ассоциированных конверсий можно посчитать в виде вычисляемой метрики уже на стороне BI-системы. При необходимости аналогичным образом можно построить таблицу ассоциированных конверсий для Campaign, Keyword, Landing page и даже их комбинаций.

Полезные материалы

• Ассоциированные конверсии в Universal Analytics
• Google BQ – Документация по оконным функциям (OVER, PARTITIONED BY)
• Атрибуция и правильное определение каналов в Google Analytics 4 и BigQuery