С переходом с 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, где жирным выделена сессия с конверсией:
- yandex/cpc > google/organic > yandex/cpc > vk/cpc (2 конверсии на 1 юзера)
- yandex/cpc > yandex/organic > yandex/organic > vk/cpc > direct/none > direct/none
- yandex/cpc > yandex/organic > google/organic > yandex/cpc
- direct/none
В итоге должна получиться таблица:
|
Источник (source / medium) |
Прямые конверсии (модель LNDC) |
Ассоциированные конверсии |
% ассоциированных |
|
yandex/cpc |
1 |
3 |
3/(1+3) = 75% |
|
google/organic |
1 |
1 |
1/(1+1) = 50% |
|
yandex/organic |
0 |
2 |
2/(0+2) = 100% |
|
vk/cpc |
2 |
0 |
0/(2+0) = 0% |
|
direct/none |
1 |
1 |
1/(1+1) = 50% |
К слову, % ассоциированных конверсий является вычисляемой метрикой и может легко считаться на стороне того же Looker или вашей BI-системы. То есть на стороне Google BQ этот столбец вычислять необязательно.
Этапы реализации
1. Из таблиц GA4 создаем плоскую таблицу содержащую события конверсий (call, email, chat, form и тп.) и события session_start. Столбцы таблицы:
-
- event_timestamp
- user_pseudo_id
- ga_session_id
- source
- medium
2. Добавляем вспомогательные столбцы:
-
- session_id — контакенация user_pseudo_id и ga_session_id для дальнейшего подсчета уникальных событий
- point — 0 (ноль) для события session_start и 1 для события конверсии
- sm — контакенация вида source / medium
- source_nd — копия столбца source, но с присвоенным NULL если source = (direct)
- 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:
- source / medium — уникальное значение source / medium
- direct_conv — прямые конверсии по модели LND = число уникальных конверсий (session_id), атрибуцированных данному source/medium по модели LND
- assoc_conv — ассоциированные конвесии – число уникальных session_id, у которых в цепочке ассоциированных источников встречается данный source / medium
Итоговый код Google Big Query для получения таблицы атрибуцированных конверсий на примере тестовых данных
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 |
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 и даже их комбинаций.
Полезные материалы
