Мультиканальная аналитика, путь к конверсии на данных Google Analytics 4 в BigQuery

Клиент, которой пришел и моментально купил - редкость, особенно если продукт дорогой. Путь клиента проходит в несколько этапов, пример: клик на рекламу, поиск продукта в google или yandex поиске, прямой заход, почтовая рассылка. Если компания не знает как приходит к ним клиент, то скорее всего бюджет на привлечение клиентов будет использован не оптимально.

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

Эффективно распределить бюджет
Снизить потери денег вложив их в нерабочие каналы
Повысить ROI
В обращении к конкретной группе клиентов посредством персонализации рекламы. Зная как пользователи приходят к покупке, вы с большей вероятностью предложите им то, что они хотят.

Мы подготовили пример работы с сырыми данными Google BigQuery на примере общедоступного датасета Google Analytics

Похожий отчет есть в Google Analytics Universal, но как вы понимаете, возможности кастомизации отчета там отсутствуют, поэтому мы разберем пример на чистом SQL.

Преимущества такого подхода - полная картина пути клиента со всеми ответвлениями и нюансами.

Недостатки - сложность построения такого отчета, так как нужен опытный аналитик и большой объем данных.

В этой статье мы будем использовать следующие функции и методы, по ним у нас есть отдельные статьи.

работа с временными табличными выражениями WITH
функции работы с датами
навигационные функции
строковые функции
агрегационные функции
работа с UNNEST
работа с CASE WHEN
работа с оконными функциями

SQL запрос для публичного датасета BigQuery схемы Google Analytics


-------------------------- 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 = '<Other> / <Other>' THEN 'facebook / cpm'
      WHEN source_medium = '(organic) / organic' THEN 'google / organic'
      WHEN source_medium = '<Other> / 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 – финальная часть запроса
получаем данные о том сколько в процентах конкретный путь пользователя занимает от всех конверсионных путей всех пользователей совершивших покупку.