Атрибуция и правильное определение каналов в Google Analytics 4 и BigQuery

Одной из самых значимых возможностей Google Analytics 4 (GA App+Web) является передача сырых несэмплированных данных в BigQuery, что позволяет делать любые запросы и любые выборки данных для анализа. Мы часто используем эту возможность и работаем с данными напрямую в BigQuery даже чаще, чем с обычным интерфейсом новой Google Аналитики.

Однако, как и в любой сложной системе, здесь не обходится без ряда особенностей. О такой особенности определения источников и каналов я расскажу в этой статье.

Определение источника и канала в Google Analytics

Источник и канал — это очень важные параметры в аналитике и маркетинге. Они показывают откуда и как пришел к нам пользователь, то есть, с какого сайта и каким был этот переход: органическим, рекламным, реферальным и т.п. Существует несколько способов определения источника и канала:

• Принудительное, с помощью UTM-меток
• Автоматическое, по ряду правил

На самом деле при использовании UTM-меток мы получаем не только источник и канал, но и другие параметры, которые нам очень нужны для анализа:

• source — источник перехода.
• medium — канал перехода, то есть тип трафика.
• campaign — рекламная кампания.
• content — например, рекламное объявление, если их несколько в кампании.
• term — ключевые слова.

Обычно эти параметры определяются для первого хита сессии и характеризуют именно сессию пользователя, а не отдельный хит.

Что такое атрибуция каналов и модели атрибуции

В современных условиях пользователи могут совершать несколько взаимодействий с сайтом и по разным каналам. Например, пользователь увидел рекламное объявление и перешел на сайт (канал рекламы), через день он вспомнил об этом и вбил в поиске название компании и снова перешел на сайт (органический канал), а еще через день он просто открыл в браузер, выбрал адрес из истории ранее посещенных, опять перешел на сайт (прямой переход) и, наконец, что-то купил.

Вопрос: какому из этих каналов (реклама, органика, прямой) следует приписать эту конверсию?

Ответ на этот вопрос и является определением модели атрибуции канала. То есть, атрибуция — это просто приписывание какой либо активности пользователя, например, к каналу, то есть типу трафика. То есть, ответ на вопрос: как взаимодействовал пользователь перед тем, как что-то произошло.

Модель атрибуции — это способ учета этих взаимодействий, в нашем случае, этих каналов, если их несколько. Например, равномерное распределение самого факта конверсии на все каналы, которые использовались этим пользователем.

Существует множество моделей атрибуции: последний клик (учитываем только последний канал), первый клик (учитываем только первый канал), равномерное распределение, линейная модель, модель с учетом взаимодействий, цепи Маркова и другие более экзотические способы.

Всё это нам часто нужно определять при анализе трафика.

Google Analytics 4 при выгрузке данных в BigQuery определяет три параметра: источник, канал и кампанию в своих таблицах в виде структуры traffic_source:

• traffic_source.source — источник перехода
• traffic_source.medium — канал перехода
• traffic_source.name — кампания перехода

Однако часто нам этих данных не хватает. Вот несколько “проблемных” моментов:

1. Зачастую требуется определение еще и content и term, а их в таблице нет.
2. Переходы с yandex органики определяются неверно, как канал referral. В настоящий момент времени исправить это штатными способами в Google Analytics 4 невозможно.
3. Несмотря на то, что Google явном виде пишет, что используется “модель Последний клик (несколько каналов)”, на самом деле атрибуция происходит не совсем так, как хотелось бы.

О последнем случае следует сказать отдельно. Google в своей документации пишет:

1. При атрибуции в поле traffic_source используется модель Последний клик (несколько каналов). Значения traffic_source не изменяются, если пользователь после установки взаимодействует с последующими кампаниями.
2. Cross-channel last click (Последний клик (несколько каналов). Google Analytics будет анализировать клики от всех источников и выполнять атрибуцию по последнему клику. Прямой трафик исключается, то есть все модели атрибуции в Аналитике не назначают долю ценности прямым переходам, за исключением случаев, когда конверсию невозможно связать с кампанией.

Фактически это хорошо нам знакомая модель “Последний непрямой клик”, но мы неоднократно сталкивались с ситуаций, когда в этой модели, например, игнорировались UTM-метки для второй и далее сессий.

Поэтому мы решили сделать свой собственный расчет определения и атрибуции каналов на базе сырых данных Аналитики.

Расчет источников и каналов в BigQuery

Я написал механизм определения всех пяти параметров для каждой сессии в отдельности. При этом определении используются следующие правила:

1. Если первый хит сессии был с UTM-метками, данные берутся исключительно из UTM-меток, даже если это был внутренний переход. Это правило для тех малограмотных маркетологов, которые умудряются делать внутренние ссылки с UTM-метками.
2. Если переход был с поисковиков Google, Яндекс, Рамблер, Yahoo, Bing (список настраивается), то это однозначно органика.
3. Если переход был из соц.сетей Facebook, VK, Одноклассники, Instagram, Twitter (список настраивается), то это канал social. Google это делает по-другому, но мне проще их соотнести с отдельным выраженным каналом.
4. Прямой переход фиксируется так, как принято у Google Analytics, то есть, значениями (direct)/(none).
5. Для каждой сессии в отдельности считаются следующие модели атрибуции:
   1. Последний клик (считается для источника, канала и кампании)
   2. Первый клик (считается для источника, канала и кампании)
6. Для каждой сессии в отдельности считаются шесть путей
   1. Путь каналов, например, “yandex > google > (direct)”
   2. Путь каналов, например, “cpc > organic > (none)”
   3. Путь кампаний
   4. Путь объявлений
   5. Путь ключевых слов
   6. Путь “источник/канал”, например, “ yandex/cpc > m.facebook.com/social

Вот запрос SQL, который это все делает. Это больше “болванка” запроса, на основе которой далее делаются свои запросы. Обязательно замените в нем исходный набор данных на ваш собственный.

WITH
	raw_data AS (
		-- Черновая выборка данных и UNNEST в 1НФ
		SELECT 
            PARSE_DATE('%Y%m%d',  event_date) AS event_date,
            event_timestamp,	
            event_name,
            user_pseudo_id,
            -- Параметры сессии
            (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
            (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
            (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_referrer') AS page_referrer,
        FROM `project_id.analytics_**************` -- УКАЖИТЕ ЗДЕСЬ СВОЙ ИСТОЧНИК ДАННЫХ
        WHERE event_name IN ('page_view', 'session_start')	
	),
    entry_points AS (
		-- Определяем точки входа в сессию
		SELECT
            raw_data.*,
            CONCAT(user_pseudo_id, '.', ga_session_id) AS session_id,
            FIRST_VALUE(page_location) OVER (PARTITION BY ga_session_id ORDER BY event_timestamp) AS first_page,
            FIRST_VALUE(page_referrer) OVER (PARTITION BY ga_session_id ORDER BY event_timestamp) AS referrer,
        FROM raw_data
    ),
    hits AS (
        -- Таблица хитов и определение источника, канала, кампании для Last Click
        SELECT
            entry_points.*,
            -- Определение источника
            CASE
                -- Если указана UTM метка, дерем данные из нее
                WHEN REGEXP_CONTAINS(first_page, 'utm_source.*') 
                    THEN REGEXP_EXTRACT(first_page, 'utm_source=([\\w\\d-_%.]+)(?:&|$)')
                -- Если это переход из поисковиков, берем домен без TLD
                WHEN REGEXP_CONTAINS(referrer, '^https?://(?:www\\.)?(yandex|google|rambler|yahoo|bing).*') 
                    THEN REGEXP_EXTRACT(referrer, '^https?://(?:www\\.)?(yandex|google|rambler|yahoo|bing).*')
                -- Если referrer отсутствует, возвращаем (direct)
                WHEN referrer IS NULL 
                    THEN '(direct)'
                -- Иначе берем домен перехода без www
                ELSE REGEXP_EXTRACT(referrer, '^https?://(?:www\\.)?([a-zA-Z0-9_\\-.]+)/.*')
            END AS hit_source,
            -- Определение канала
            CASE
                -- Если указана UTM метка, дерем данные из нее
                WHEN REGEXP_CONTAINS(first_page, 'utm_medium.*') 
                    THEN REGEXP_EXTRACT(first_page, 'utm_medium=([\\w\\d-_%.]+)(?:&|$)')
                -- Если referrer отсутствует, возвращаем (none)
                WHEN referrer IS NULL THEN '(none)'
                -- Если это переход из поисковиков, возвращаем organic
                WHEN REGEXP_CONTAINS(referrer, '^https?://(?:www\\.)?(yandex|google|rambler|yahoo|bing).*') 
                    THEN 'organic'
                -- Если это переход из соц.сетей, возвращаем social
                WHEN REGEXP_CONTAINS(referrer, '^https?://(?:.*\\.)?(facebook|fb|vk|ok|instagram|twitter)\\..*') 
                    THEN 'social'                
                -- Иначе возвращаем referral
                ELSE 'referral'
            END AS hit_medium,
            -- Определение кампании
            CASE
                -- Если указана UTM метка, дерем данные из нее
                WHEN REGEXP_CONTAINS(first_page, 'utm_campaign.*') 
                    THEN REGEXP_EXTRACT(first_page, 'utm_campaign=([\\w\\d-_%.]+)(?:&|$)')              
                -- Иначе возвращаем NULL
                ELSE NULL
            END AS hit_campaign,
            -- Определение объявления
            CASE
                -- Если указана UTM метка, дерем данные из нее
                WHEN REGEXP_CONTAINS(first_page, 'utm_content.*') 
                    THEN REGEXP_EXTRACT(first_page, 'utm_content=([\\w\\d-_%.]+)(?:&|$)')              
                -- Иначе возвращаем NULL
                ELSE NULL
            END AS hit_content,
            -- Определение ключевых слов
            CASE
                -- Если указана UTM метка, дерем данные из нее
                WHEN REGEXP_CONTAINS(first_page, 'utm_term.*') 
                    THEN REGEXP_EXTRACT(first_page, 'utm_term=([\\w\\d-_%.]+)(?:&|$)')              
                -- Иначе возвращаем NULL
                ELSE NULL
            END AS hit_term,
        FROM entry_points
    ),
    attribution AS (
        -- Атрибуция источников, каналов и т.п. по сессиям
        SELECT
            hits.*,
            FIRST_VALUE(hit_source) OVER (PARTITION BY session_id ORDER BY event_timestamp) AS source_last_click,
            FIRST_VALUE(hit_medium) OVER (PARTITION BY session_id ORDER BY event_timestamp) AS medium_last_click,
            FIRST_VALUE(hit_campaign) OVER (PARTITION BY session_id ORDER BY event_timestamp) AS campaign_last_click,
            FIRST_VALUE(hit_source) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS source_first_click,
            FIRST_VALUE(hit_medium) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS medium_first_click,
            FIRST_VALUE(hit_campaign) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS campaign_first_click,
            -- Сессионные пути
            STRING_AGG (CASE WHEN event_name='session_start' THEN hit_source ELSE NULL END, ' > ') OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS source_path,
            STRING_AGG (CASE WHEN event_name='session_start' THEN hit_medium ELSE NULL END, ' > ') OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS medium_path,
            STRING_AGG (CASE WHEN event_name='session_start' THEN hit_campaign ELSE NULL END, ' > ') OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS campaign_path,
            STRING_AGG (CASE WHEN event_name='session_start' THEN hit_content ELSE NULL END, ' > ') OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS content_path,
            STRING_AGG (CASE WHEN event_name='session_start' THEN hit_term ELSE NULL END, ' > ') OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS term_path,
            STRING_AGG (CASE WHEN event_name='session_start' THEN CONCAT(hit_source, '/', hit_medium) ELSE NULL END, ' > ') OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS source_medium_path,
            -- Число сессий пользователя
            COUNT (DISTINCT session_id) OVER (PARTITION BY user_pseudo_id) AS session_count,
        FROM hits
    )
SELECT * FROM attribution

Важное замечание: этот запрос возвращает ТАБЛИЦУ ХИТОВ, то есть, ее можно использовать далее в своих расчетах. Если вам нужна таблица сессий, то самый простой способ не группировка, а просто добавить в конец (в последний SELECT) выражение

WHERE event_name = 'session_start'

(Спасибо Надежде Афоничевой за эту простую, но очень продуктивную идею).

Пояснения по запросу SQL

Этот запрос состоит из нескольких подзапросов:

• raw_data — просто формирует данные в первой нормальной форме и приводит их к нужному типу
• entry_points — определяет точки входа в сессию и формирует правильный идентификатор сессии для каждого пользователя
• hits — строит таблицу хитов и для каждого хита считает параметры канала, источника, кампании, объявления и ключевых слов по правилам, приведенным выше
• attribution — оконными функциями выполняет расчет модели “Последний клик” для сессии, “Первый клик” для пользователя и шесть путей, описанных выше

Поскольку этот запрос возвращает таблицу хитов, можно на основе полученных данных реализовать расчет любой другой модели атрибуции.

Буду благодарен за комментарии и уточнения к этой статье.