Использование сырых данных Яндекс.Метрики для вычисления ассоциированных конверсий

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

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

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

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

В самом простом подходе ассоциированные конверсии можно посчитать, используя сырые данные Яндекс.Метрики о визитах. Их можно получить используя Data Streaming Яндекс.Метрики Про или же через Logs API бесплатного тарифа Яндекс.Метрики.

Data Streaming - это интеграция Яндекс.Метрики Про с Яндекс.Облаком, которая позволяет получать сырые данные Яндекс.Метрики в развернутый в Яндекс.Облаке управляемый кластер ClickHouse.

Logs API - это интерфейс для получения неагрегированных данных Яндекс.Метрики. Он доступен, в том числе, для бесплатной версии Яндекс.Метрики. Инструкцию по выгрузке и работе с данными можно найти в инструкции от Яндекс.Облака.

Data Streaming позволяет получать расширенные наборы данные в реальном времени, но не позволяет собирать исторические данные. Logs API можно использовать для частичного сбора исторических данных (часть полей из Data Streaming в Logs API недоступна), а также для знакомства с тем с неагреированными данными Яндекс.Метрики.

В качестве тренировочного примера данных можно использовать открытую песочницу ClickHouse. Этот сервис позволяет выполнять SQL запросы к открытому кластеру с определенным готовым наборам данных. Среди них есть таблица metrica.visits_v1, в которой лежит образец данных, поставляемых Data Streaming (в настоящем наборе данных набор полей может немного отличаться). Именно его мы и будем использовать в качестве примера.

Актуальный список полей Data Streaming можно найти здесь. Список полей Logs API доступен в его документации.

Перед формированием SQL запроса для построения отчёта по ассоциированным конверсиям нужно определиться с:

В примере мы будем использовать цель с идентификатором 3021434 в качестве конверсионного действия, возьмем окно атрибуции 30 дней, для простоты будем определять касания по полю TraficSourceID (соответствует группировке "Источник трафика" в интерфейсе Яндекс.Метрики) и дедуплицируем одинаковые касания.

Запрос состоит из агрегирования и подзапроса, разберём его по частям.

SELECT c.UserID, c.VisitID "ConversionVisitID", toDate(c.StartTime) "ConversionDate", length(arrayFilter(x -> x = 3021434, c.Goals.ID)) "ConversionsCount", a.VisitID "AssistedVisitID", a.TraficSourceID "AssistedTrafficSourceID", row_number() OVER (PARTITION BY (c.UserID, c.VisitID, a.TraficSourceID) ORDER BY a.StartTime) "touchNum" FROM metrica.visits_v1 c JOIN metrica.visits_v1 a ON c.UserID = a.UserID AND c.StartTime > a.StartTime AND c.StartTime - a.StartTime < INTERVAL 30 DAY WHERE has(c.Goals.ID, 3021434)

В подзапросе мы строим цепочки касаний - фильтруем целевые визиты для выбранной цели и объединяем их со всеми визитами пользователей в рамках окна атрибуции. Ключом объединения является UserID, но здесь же мы можем ограничить визиты пользователей окном атрибуции: во-первых "ассоциированный" визит должен быть раньше целевого визита, а во-вторых, разница между "ассоциированным" и целевым визитами должна быть меньше выбранного интервала, в нашем случае это 30 дней.

При таком объединении одному целевому визиту соответствует один или несколько предшествующих визитов. Мы используем здесь INNER JOIN, поэтому целевые визиты, которые являются единственным касанием в цепочке до конверсии не попадут в нашу выборку, как и "ассоциированные" цепочки визитов, которые не привели к конверсии.

Целевые визиты фильтруются функцией для массивов has. Столбец-массив c.GoalID, в котором хранятся идентификаторы целей, достигнутых пользователем в рамках визита проверяется на наличие нужного нам идентификатора.

Для подсчёта количества достижений нужной нам цели массив c.GoalID фильтруется функцией arrayFilter, оставляя только нужные нам цели, а затем вычисляется длина отфильтрованного массива функцией length.

Для дедупликации используется оконная функция row_number, которая нумерует визиты в группах по ID пользователя, ID целевого визита и источника ассоциированного визита. Сама дедупликация произойдёт в агрегирующем запросе.

Агрегирование

SELECT sub.ConversionDate, sub.AssistedTrafficSourceID, count(sub.ConversionVisitID) "ConversionVisits", sum(sub.ConversionsCount) "ConversionReaches" FROM (...) sub WHERE sub.touchNum = 1 GROUP BY sub.ConversionDate, sub.AssistedTrafficSourceID

Агрегирующий запрос довольно типичен. Для итогового отчёта мы хотим вычислить количество ассоциированных конверсий и конверсионных (целевых) визитов в срезе по датам и источникам данных, именно это мы и делаем.

В условиях нашего примера мы должны дедуплицировать касания одинаковых источников. Это можно сделать фильтрацией по результату оконной функции из подзапроса touchNum. Номер касания 1 в нашем примере означает, что мы оставляем самый ранний ассоциированный визит пользователя из ассоциированных визитов с одинаковыми источниками.

Целиком наш запрос выглядит следующим образом:

SELECT sub.ConversionDate, sub.AssistedTrafficSourceID, count(sub.ConversionVisitID) "ConversionVisits", sum(sub.ConversionsCount) "ConversionReaches"FROM ( SELECT c.UserID, c.VisitID "ConversionVisitID", toDate(c.StartTime) "ConversionDate", length(arrayFilter(x -> x = 3021434, c.Goals.ID)) "ConversionsCount", a.VisitID "AssistedVisitID", a.TraficSourceID "AssistedTrafficSourceID", row_number() OVER (PARTITION BY (c.UserID, c.VisitID, a.TraficSourceID) ORDER BY a.StartTime) "touchNum" FROM metrica.visits_v1 c JOIN metrica.visits_v1 a ON c.UserID = a.UserID AND c.StartTime > a.StartTime AND c.StartTime - a.StartTime < INTERVAL 30 DAY WHERE has(c.Goals.ID, 3021434)) sub WHERE sub.touchNum = 1 GROUP BY sub.ConversionDate, sub.AssistedTrafficSourceID

Что нужно знать при подготовке отчёта по ассоциированным конверсиями и интерпретации результатов?

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

Ассоциированные конверсии полезно использовать в рамках одного типа касания (источника трафика, рекламной кампании и т.д.) для сравнения с конверсиями по классической модели атрибуции, например, "Последний непрямой клик". Таким образом можно определить недооцененные источники по относительно низкому количеству конверсий по последнему клику и значительно большему количеству ассоциированных конверсий.

Ассоциированные конверсии - это дополнительный инструмент в руках маркетолога, который позволяет расширить взгляд на эффективность омниканальной коммуникации с целевой аудиторией. Возможно, это не самый точный из возможных подходов, но определенно один из самых простых.