Клиент, которой пришел и моментально купил - редкость, особенно если продукт дорогой. Путь клиента проходит в несколько этапов,
пример: клик на рекламу, поиск продукта в google или yandex поиске, прямой заход, почтовая рассылка. Если компания не знает как приходит к ним клиент, то скорее всего бюджет на привлечение клиентов будет использован не оптимально. Правильное определение цепочки каналов помогает бизнесу: Эффективно распределить бюджет Снизить потери денег вложив их в нерабочие каналы Повысить ROI В обращении к конкретной группе клиентов посредством персонализации рекламы. Зная как пользователи приходят к покупке, вы с большей вероятностью предложите им то, что они хотят. Мы подготовили пример работы с сырыми данными Google BigQuery на примере общедоступного датасета Google Analytics Похожий отчет есть в Google Analytics Universal, но как вы понимаете, возможности кастомизации отчета там отсутствуют, поэтому мы разберем пример на чистом SQL. Преимущества такого подхода - полная картина пути клиента со всеми ответвлениями и нюансами. Недостатки - сложность построения такого отчета, так как нужен опытный аналитик и большой объем данных. В этой статье мы будем использовать следующие функции и методы, по ним у нас есть отдельные статьи. работа с временными табличными выражениями WITH функции работы с датами навигационные функции строковые функции агрегационные функции работа с UNNEST работа с CASE WHEN работа с оконными функциями
SQL запрос для публичного датасета BigQuery схемы Google Analytics
Ниже — SQL для публичного датасета `bigquery-public-data.ga4_obfuscated_sample_ecommerce`: пути к конверсии и доля каждого мультиканального пути в покупках.
-------------------------- I —----------------------------------------
-- вытаскиваем все касания пользователя, маркируем касания которые закончились покупкой, находим минимальный ивент и берем из него источник
WITH ecom_tab_raw AS (
SELECT
DISTINCT PARSE_DATE('%Y%m%d', event_date) AS date,
user_pseudo_id AS clientId,
MIN(event_timestamp) AS min_timestamp_hit,
-- получаем id сессии
param.value.int_value as session_id,
-- получаем самый ранний timestamp внутри каждой сессии
FIRST_VALUE(event_timestamp) OVER(partition by param.value.int_value ORDER BY event_timestamp ASC) first_ts,
CASE
WHEN event_name = 'purchase' THEN 1
ELSE 0 END
purchase,
CONCAT( traffic_source.name, ' / ', traffic_source.medium ) AS source_medium,
geo.country
FROM
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`,
UNNEST(event_params) as param
WHERE _TABLE_SUFFIX BETWEEN
'20210115' AND '20210131' AND geo.country = 'United States' AND param.key = 'ga_session_id'
GROUP BY
event_date, user_pseudo_id, event_name,traffic_source.name,traffic_source.medium,geo.country,param.value.int_value,event_timestamp
ORDER BY
date,clientId,session_id,min_timestamp_hit ASC
),
-------------------------- I end —-------------------------------------
--------------------------- II —----------------------------------------
ecom_tab AS (
SELECT
date,country,clientId,session_id,
CASE
WHEN source_medium = '(referral) / referral' THEN 'popular-blog.com / referral'
WHEN source_medium = ' / ' THEN 'facebook / cpm'
WHEN source_medium = '(organic) / organic' THEN 'google / organic'
WHEN source_medium = ' / cpc' THEN 'bing / cpc'
WHEN source_medium = '(data deleted) / (data deleted)' THEN 'twitter / cpa'
ELSE source_medium END source_medium,
SUM(purchase) as purchase,first_ts
FROM ecom_tab_raw
GROUP BY
date,country,clientId,session_id,source_medium,first_ts
),
--------------------------- II end —-------------------------------------
----------------------------- III —----------------------------------------
-- вытаскиваем timestamp из unix
-- маркируем источники где была покупка дополнительным ярлыком
goal_markup_tab AS (
SELECT
date,clientId,
TIMESTAMP_MICROS(first_ts) AS timestamp_min,
CASE
WHEN purchase = 1 THEN CONCAT('GOAL_source = ', source_medium)
ELSE source_medium END
event_source_medium,purchase,source_medium,country
FROM ecom_tab
ORDER BY
-- сортируем все касания от раннего к позднему
timestamp_min asc ),
---------------------------- III end —------------------------------------
----------------------------- IV —-----------------------------------------
-- собираем массив из последовательных касаний для каждого clientId
ARRAY_tab AS (
SELECT
country,clientId,
FORMAT( '%T', ARRAY_AGG(event_source_medium) OVER (PARTITION BY clientId) ) AS array_agg
FROM
goal_markup_tab ),
-------------------------- IV end —-------------------------------------
----------------------------- V —------------------------------------------
-- агрегируем результат в пути к конверсии и сумму таких путей
GOAL_path AS (
SELECT
country, array_agg,
SUM(conversions) AS conversions,
FROM (
SELECT
country, array_agg,
CASE
WHEN array_agg LIKE '%GOAL%' THEN 1
ELSE 0 END
conversions
FROM ARRAY_tab )
GROUP BY country,array_agg )
---------------------------- V end —--------------------------------------
---------------------------- VI —-----------------------------------------
SELECT
*, ROUND(conversions / SUM(conversions) OVER() * 100, 1) as from_total
FROM
GOAL_path
ORDER BY
conversions DESC
Пояснения к bigquery SQL запросу
Разобьем запрос на этапы: I – Создаем временную таблицу с названием "ecom_tab_raw" Парсим дату в нужный нам формат, меняем название столбца с "user_id" для удобства; Минимальное время совершения события будем использовать для сортировки; Достаем из набора данных id сессии; Достаем самое первое время совершения события; Дальше если название события равно "purchase" тогда выводим в поле "1", это нужно для подсчета совершенных покупок в дальнейшем; Соединяем название источника трафика с названием канала трафика ; Также фильтруем (если нужно) по дате, стране и по визитам которые имеют "ga_session_id"; II – Создаем временную таблицу, называем "ecom_tab" На втором этапе мы просто исправляем источники трафика, которые отображаются неправильно (в публичном датасете от Google реальные источники переименованы в белиберду, поэтому мы их причесываем для красоты) и суммируем поле "purchase", которое на прошлом этапе мы сделали равным 1, если такое событие было совершено пользователем. III – Создаем временную таблицу "goal_markup_tab" Исправляем отображение timestamp. Если пользователь совершил покупку, то источник помечаем как целевой IV – временная таблица "ARRAY_tab" Собираем в группу сессии одного пользователя по источникам трафика V – временная таблица "GOAL_path" При условии что наша группа из временной таблицы "ARRAY_tab" содержит покупку, выводим результат " 1" для подсчета суммы таких конверсий VI – финальная часть запроса получаем данные о том сколько в процентах конкретный путь пользователя занимает от всех конверсионных путей всех пользователей совершивших покупку.
