Привет! Меня зовут Михаил Васягин, я занимаюсь веб-аналитикой в M2. М2 — это онлайн-платформа для решения вопросов с недвижимостью. Сервисами М2 пользуются как частные лица, так и профессиональные участники рынка — риелторы, застройщики, банки. Мы помогаем тысячам людей экономить время, нервы и деньги. Сейчас каждая пятая квартира в новостройке покупается через нашу платформу.

В 2024 году маркетинг запустил большую медийную кампанию, которая откручивалась с помощью рекламных систем Яндекс Директ и AdRiver. Мы в веб-аналитике не захотели останавливаться на метриках охвата и решили попробовать оценить влияние показов медийной рекламы на дальнейшие источники трафика на сайте. 

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

Для решения первичной задачи мы использовали сырые данные AdRiver (post-view) и Яндекс Метрики (post-click), а также базу данных ClickHouse и Apache Superset для визуализации.

Материал будет полезен веб и маркетинговым аналитикам, а также маркетологам, кто хочет самостоятельно разобраться в сырых данных AdRiver и Яндекс Метрики. А именно: как их можно связывать между собой, какие отчеты построить на этих данных и какие полезные выводы для бизнеса можно сделать.

Синхронизация AdRiver и Яндекс Метрики: план, которого мы придерживались

Основная проблема состоит в том, что данные лежат в разных аналитических системах. Непонятно, насколько корректно сработает синхронизация cookie между AdRiver и Яндекс Метрикой — какой именно будет процент совпадений между двумя разными аналитическими системами. Еще мы не смогли найти примеры практических материалов с похожими кейсами, поэтому и решились написать эту статью.

После продолжительного ресерча мы сделали вывод, что наиболее понятная и детальная теория есть в материалах ресурса CyberBrain. Затем мы решили двигаться по шагам из этой статьи. Вот наш план:

1. Устанавливаем на сайт счетчик AdRiver*

2. Получаем сырые данные от AdRiver и загружаем их в ClickHouse

3. Сверяем синхронизацию cookie между AdRiver и Яндекс Метрикой

4. Пишем витрину данных, определяемся с типом атрибуции, а также выбираем необходимое окно атрибуции

5. Строим дашборд в Superset, в котором базово будет видно.

   • какой процент пользователей из тех, кто видел объявление медийной рекламы, посещал сайт;

   • какой процент пользователей из тех, кто посещал сайт, до этого видел объявление медийной рекламы. 

6. Анализируем дашборд и ищем инсайты

*Медийные кампании должны размещаться именно в Adtracker-системах или быть размечены специальными метками на показ и клик этих систем. Причина в том, что, например, Яндекс Метрика для медийной рекламы не дает возможность выгрузить сырые данные по показам объявлений, поэтому про оценку кампаний, запущенных с помощью Яндекс Директ, в статье речь не пойдет.

Здесь стоит добавить, что для текущей задачи необходимым также является экспорт сырых данных Яндекс Метрики в ClickHouse, стандартизированная UTM-разметка, а также наличие в Яндекс Метрике ключевых размеченных событий на сайте (про дальнейшее склеивание Яндекс Метрики с CRM системой в статье речь не пойдет).

Как мы установили счетчик AdRiver и выгрузили данные в ClickHouse

Устанавливаем на сайт счетчик AdRiver

На первый взгляд все просто: нам нужен счетчик counter, получить который можно по инструкции. Его необходимо установить на все страницы сайта. Если возникнут проблемы, например, в процессе установки счетчика через Google Tag Manager, он может не пропустить стандартный js-код, можно написать в поддержку AdRiver с просьбой предоставить счетчик для сайта. С помощью него можно будет связать данные post-view и post-click.

Важно отметить, что счетчик AdRiver забирает first-party cookie Яндекс Метрики, поэтому он должен срабатывать именно после инициализации счетчика Яндекс Метрики. Это можно, например, настроить с помощью последовательности активации тегов в Google Tag Manager. В случае успешной установки при загрузке страницы сайта в консоли разработчика на вкладке Network должна появиться такая строчка:

Лучше протестировать сбор данных в различных браузерах в режиме инкогнито. В Request URL обязательно должен быть параметр yid1, значение которого мы увидим в выгрузке AdRiver по сайту в следующем шаге.

Получаем сырые данные от AdRiver и загружаем их в ClickHouse

Выгрузка логов у AdRiver платная, но стоимость небольшая и рассчитывается по фиксированной ставке за CPM. Данные выгружаются ежедневно за вчерашний день на выделенный вам FTP-сервер и хранятся 7 дней. Можно также попросить выгрузить исторические данные за нужный диапазон дат. Забрать эти данные к себе локально можно с помощью библиотеки requests на python, подставив свои урлы, даты и креды:

import pandas as pd
import requests
from requests.auth import HTTPBasicAuth
import gzip
from io import BytesIO

generate_dates = pd.date_range('2024-01-01', periods=30).tolist()

datelist = []
for date in generate_dates:
    date = date.strftime('%Y-%m-%d')
    datelist.append(date)
print(datelist)

for date in datelist:
    res = requests.get(f"https://files.adriver.ru/your_login.ftp/{date}.logsite.csv.gz", 
                       auth=HTTPBasicAuth('your_login', 'your_password'))
    compressed_content = res.content
    compressed_data = BytesIO(compressed_content)

    with gzip.GzipFile(fileobj=compressed_data, mode='rb') as decompressed_data:
        decompressed_content = decompressed_data.read()

    with open('files/example.csv', 'wb') as f: 
        f.write(decompressed_content)

Полученные файлы загружаем в ClickHouse с помощью python, например, используя библиотеку SQLAlchemy. При больших объемах можно разбивать данные на чанки.

Получаем две таблицы – ADRIVER_AD, взаимодействия пользователей с рекламными объявлениями – показы, клики, etc., и ADRIVER_SITE, посещения пользователями сайта, на который мы установили счетчик в первом шаге. Есть подробное описание полей в документации. Их достаточно много и все можно использовать для различных срезов, но необходимым минимумом для нашей задачи из таблицы ADRIVER_AD будут: 

• user_id

• user_ip

• user_agent

• datetime

• type

• banner_id

• domain

• utm_source / utm_medium / utm_campaign / utm_content 

Из таблицы ADRVIER_SITE:

• user_id

• yandex_id_first  

• datetime

Сверяем синхронизацию cookie между AdRiver и Яндекс Метрикой

Так как мэтчинг данных в финальной витрине будет происходить по полю yandex_id_first из таблицы ADRIVER_SITE и полю ym:s:clientID (ниже client_id_ym) из таблицы визитов Logs API Яндекс Метрики, нам нужно понять примерный процент потерь first-party cookie, которые записывает Яндекс Метрика, но по каким-то причинам не записывает AdRiver. На текущий момент у нас процент совпадений составляет в среднем 90% за день. Но начинали мы с 60%... Для себя мы выявили несколько причин, почему могут быть большие расхождения:

• Счетчик AdRiver срабатывает раньше, чем инициализируется счетчик Яндекс Метрики. В таком случае в поле yandex_id_first таблицы ADRIVER_SITE будут пустые значения, решение проблемы описано в первом шаге

• На сайте установлено много пикселей других рекламных или аналитических систем на один и тот же триггер в Google Tag Manager. Они могут мешать корректному сбору данных счетчиком AdRiver, поэтому можно попробовать подвинуть активацию других тегов на 1-2 секунды

• Различные ограничения браузеров, отсюда потери в 10%, они приемлемы

Также мы заметили, что большие потери присутствуют в сегменте именно новых пользователей Яндекс Метрики, а по устройствам и браузерам выбросов у нас не было.

Как мы написали витрину данных: атрибуция и моделирование

У медийной кампании было несколько этапов, первый был одинаковый для всех пользователей. Нам было интересно посмотреть, после показа какого этапа пользователи совершают сессии на сайте, поэтому мы выбрали атрибуцию по last-view, последнему показанному объявлению, и окно атрибуции в 90 дней. Таким образом, показ объявления будет отнесен к сессии, а также ко всем последующим сессиям пользователя на сайте только в том случае, если между ними не прошло 90 календарных дней.

Финальная витрина имеет следующую модель данных. Ниже будут примеры кода для формирования промежуточных таблиц.

Начинаем работу над витриной с данных ADRIVER_AD и сформируем таблицу “ad_group_table” с уникальными user_id и отсортированным массивом событий для дальнейшей атрибуции сессий пользователя к последнему показанному объявлению по event_datetime. Здесь мы объявляем уникальную комбинацию event_date, banner_id, domain (то есть в один день уникальный баннер мог показываться на одной площадке несколько раз). Также для user_id = 0, таких в наших данных было около 30% строк, мы проставляем искусственный идентификатор, который представляет собой хэш-функцию user_ip и user_agent, чтобы попробовать идентифицировать ненайденных пользователей для более точного дальнейшего анализа:

with ad_group_table as (
select user_id_ad_group,
       groupArray(event_datetime) as group_event_datetime,
       groupArray(event_date) as group_event_date,
       groupArray(banner_id) as group_banner_id,
       groupArray(domain) as group_domain
from (
    select if(user_id = '0', hex(SHA1(concat(user_ip, user_agent))), user_id) as user_id_ad_group,
           datetime as event_datetime,
           date as event_date,
           banner_id,
           domain
    from ADRIVER_AD
    order by all)
group by user_id_ad_group),

Далее сформируем таблицу “ad_banner_table” для подсчета показов и кликов по баннерам у всех пользователей за день. За показ и клик считаем количество datetime в зависимости от значения поля type, а в случае, если кликов больше, чем показов, то проставляем показам значение кликов. Также добавляем три необходимые utm-метки (utm_medium добавим в финальной витрине) для более удобной идентификации объявлений на дашборде, так как в нашем случае одному banner_id всегда соответствует уникальный набор трех utm-меток:

ad_banner_table as (
select if(user_id = '0', hex(SHA1(concat(user_ip, user_agent))), user_id) as user_id_ad_banner,
       date as event_date,
       banner_id,
       domain,
       utm_source as media_utm_source,
       utm_campaign as media_utm_campaign,
       utm_content as media_utm_content,
       countIf(datetime, type = 1) as clicks,
       if(countIf(datetime, type = 0) < clicks, clicks, countIf(datetime, type = 0)) as views
from ADRIVER_AD
group by all),

Далее из таблицы ADRIVER_SITE сформируем промежуточную таблицу “key_table” с уникальными first-party cookie Яндекс Метрики (ниже client_id_ym) для исключения будущей связи «многие ко многим». Дело в том, что в таблице ADRIVER_SITE у одного user_id может быть несколько client_id_ym, а также у одного client_id_ym может быть несколько user_id. Вот этим мы пренебрегаем и для каждого client_id_ym  берем последний по datetime user_id. Перед этим мы проверили, что из-за этого допущения мы теряем показы не более, чем у 1% пользователей в финальной витрине:

key_table as (
select yandex_id_first as client_id_ym,
       argMax(user_id, datetime) as user_id_ad_group
from ADRIVER_SITE
where user_id <> '0' and yandex_id_first <> ''
group by client_id_ym),

Делаем финальную таблицу “adriver_table”, в которой мы к таблице “ad_group_table” подтягиваем поле client_id_ym для дальнейшей атрибуции сессий пользователя к последнему показанному объявлению:

adriver_table as (
select *
from ad_group_table
join key_table
using user_id_ad_group),

Переходим к данным Яндекс Метрики и для примера формируем таблицу с сессиями пользователей по модели атрибуции “Последний значимый переход”, а также некоторыми атрибутами и метриками сессий:

ym_table as (
select date,
       dateHourMinute as session_dt,
       session_id,
       client_id_ym,
       deviceCategory,
       landingpagepath,
       role,
       utm_source,
       utm_medium,
       utm_campaign,
       utm_content,
       sessionDuration,
       bounce,
       pageviews,
       conversions
from YM_DATA),

Затем формируем предфинальную таблицу “pre_table”, где ко всем сессиям Яндекс Метрики по client_id_ym атрибуцируем последнее по event_datetime рекламное объявление с окном 90 дней, а также с помощью функции indexOf подтягиваем уникальную комбинацию объявления, определенную в таблице “ad_group_table”:

indexOf(group_event_datetime, last_group_event_datetime) as index,
pre_table as (
select *,
       arrayMax(arrayFilter(x -> session_dt >= x and session_dt - x <= 86400*90, group_event_datetime)) as last_group_event_datetime,
       arrayElement(group_event_date, index) as last_group_event_date,
       arrayElement(group_banner_id, index) as last_group_banner_id,
       arrayElement(group_domain, index) as last_group_domain
from ym_table
left join adriver_table
using client_id_ym)

В финальной витрине ко всем объявлениям проставляются необходимые атрибуты (media_utm_medium у таких строк будет иметь значение ‘cpm’), показы и клики из таблицы “ad_banner_table”. А также с помощью full join мы подтягиваем показы и клики по всем остальным ненайденным пользователям. Так как показ одного объявления может быть отнесен ко многим сессиям, то применим оконную функцию rn_ad_banner для корректного подсчета на дашборде показов и кликов по объявлениям:

select * except (group_event_datetime, group_event_date, group_banner_id, group_domain)
from (
    select *,
	       if(media_utm_source <> '', 'cpm', 'not_cpm') as media_utm_medium,
           if(user_id_ad_banner <> '', row_number() over (partition by user_id_ad_banner, event_date, banner_id, domain), 0) as rn_ad_banner
    from pre_table
    full join ad_banner_table
    on pre_table.user_id_ad_group = ad_banner_table.user_id_ad_banner and
       pre_table.last_group_event_date = ad_banner_table.event_date and
       pre_table.last_group_banner_id = ad_banner_table.banner_id and
       pre_table.last_group_domain = ad_banner_table.domain)

with ad_group_table as (
select user_id_ad_group,
       groupArray(event_datetime) as group_event_datetime,
       groupArray(event_date) as group_event_date,
       groupArray(banner_id) as group_banner_id,
       groupArray(domain) as group_domain
from (
    select if(user_id = '0', hex(SHA1(concat(user_ip, user_agent))), user_id) as user_id_ad_group,
           datetime as event_datetime,
           date as event_date,
           banner_id,
           domain
    from ADRIVER_AD
    order by all)
group by user_id_ad_group),

ad_banner_table as (
select if(user_id = '0', hex(SHA1(concat(user_ip, user_agent))), user_id) as user_id_ad_banner,
       date as event_date,
       banner_id,
       domain,
       utm_source as media_utm_source,
       utm_campaign as media_utm_campaign,
       utm_content as media_utm_content,
       countIf(datetime, type = 1) as clicks,
       if(countIf(datetime, type = 0) < clicks, clicks, countIf(datetime, type = 0)) as views
from ADRIVER_AD
group by all),

key_table as (
select yandex_id_first as client_id_ym,
       argMax(user_id, datetime) as user_id_ad_group
from ADRIVER_SITE
where user_id <> '0' and yandex_id_first <> ''
group by client_id_ym),

adriver_table as (
select *
from ad_group_table
join key_table
using user_id_ad_group),

ym_table as (
select date,
       dateHourMinute as session_dt,
       session_id,
       client_id_ym,
       deviceCategory,
       landingpagepath,
       role,
       utm_source,
       utm_medium,
       utm_campaign,
       utm_content,
       sessionDuration,
       bounce,
       pageviews,
       conversions
from YM_DATA),

indexOf(group_event_datetime, last_group_event_datetime) as index,
pre_table as (
select *,
       arrayMax(arrayFilter(x -> session_dt >= x and session_dt - x <= 86400*90, group_event_datetime)) as last_group_event_datetime,
       arrayElement(group_event_date, index) as last_group_event_date,
       arrayElement(group_banner_id, index) as last_group_banner_id,
       arrayElement(group_domain, index) as last_group_domain
from ym_table
left join adriver_table
using client_id_ym)

select * except (group_event_datetime, group_event_date, group_banner_id, group_domain)
from (
    select *,
	       if(media_utm_source <> '', 'cpm', 'not_cpm') as media_utm_medium,
           if(user_id_ad_banner <> '', row_number() over (partition by user_id_ad_banner, event_date, banner_id, domain), 0) as rn_ad_banner
    from pre_table
    full join ad_banner_table
    on pre_table.user_id_ad_group = ad_banner_table.user_id_ad_banner and
       pre_table.last_group_event_date = ad_banner_table.event_date and
       pre_table.last_group_banner_id = ad_banner_table.banner_id and
       pre_table.last_group_domain = ad_banner_table.domain)

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

Строим дашборд в Superset: конверсии и инсайты

Напомним, что изначально мы хотели собрать дашборд в Superset, в котором базово будет видно:

• какой процент пользователей из тех, кто видел объявление медийной рекламы, посещал сайт;

• какой процент пользователей из тех, кто посещал сайт, до этого видел объявление медийной рекламы. 

Начнем с первого пункта и построим визуализацию, на которой по выбранной группировке объявлений медийной рекламы (media_utm_source / media_utm_medium / media_utm_campaign / media_utm_content) будут видны показы, клики, CTR и уникальные пользователи, у которых показ данного объявления был последним, а также те пользователи, которые после показа данного объявления медийной рекламы в течение 90 дней посетили сайт. В итоге получили первую искомую конверсию:

Во втором пункте мы хотим оценить влияние показов медийной рекламы на конкретные post-click источники трафика на сайте, а также понять, какой объем полезных действий на сайте (например, регистрации и заявки) совершают «медийные» пользователи. Для начала построим визуализацию, где все пользователи будут разделяться на “cpm” (контактировали с медийной рекламой) и “not_cpm” (не контактировали с медийной рекламой) и посмотрим их соотношение сессий и полезных действий:

Затем добавим в визуализацию группировку по utm-метке источника трафика (в данном кейсе используем атрибуцию «Последний значимый переход») и получим искомый ответ на второй вопрос:

Финально мы решили сравнить различные показатели, сессии и полезные действия, у «медийных» пользователей («Метрика CPM» на дашборде) и всех пользователей сайта (просто «Метрика» на дашборде) в различных срезах: по типу устройства, роли и странице входа, а также оценить динамику. На скриншоте ниже видим достаточно сильные отличия между этими двумя сегментами:

Примеры инсайтов при анализе дашборда

• Конверсия из показа медийной рекламы в post-view сессии по пользователям невысокая, однако качество такого трафика на порядок выше: такие пользователи генерируют достаточно большой объем полезных действий 

• Относительные показатели (время сессии, страниц за сессию, показатель отказов) также в несколько раз лучше у «медийных» пользователей

• В отличие от общего органического трафика в сегменте «медийных» пользователей страницами входа этого трафика преимущественно являются целевые лендинги продуктов, а не новостные разделы сайта

Что в итоге 

Подводя итог по дашборду, могу сказать, что это лишь малая часть того, что мы можем проанализировать по финальной витрине. Например, здесь также напрашивается:

• Расчёт uplift, как разницы conversion rate в полезные действия между контактировавшими и не контактировавшими с медийной рекламой пользователями

• Расчет медианного количества времени от показа объявления медийной рекламы до совершения сессии или полезного действия на сайте

Я считаю, что построение такой финальной витрины — хороший старт для общего ознакомления с post-view данными. Следующими шагами можно добавлять показы объявлений медийной рекламы в сквозную аналитику как отдельные этапы мультиканальной атрибуции.

Надеюсь, данная статья поможет открыть завесу тайны по вопросу работы с сырыми post-view данными. Текущий вариант — лишь одна из интерпретаций, которых может быть множество, и это замечательно. Буду рад обсудить детали реализации и ответить на ваши вопросы в комментариях. Расскажите, приходилось ли вам самостоятельно работать с сырыми post-view данными? Какие специалисты принимали участие в реализации и каких результатов удалось достичь? С какими трудностями столкнулись?