Модель атрибуции на основе онлайн/офлайн данных в Google BigQuery

Как на основе онлайн- и офлайн-данных, собранных в GBQ, “решить” атрибуцию.

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

Краткое содержание:

1. Защита информации, использованной для примеров
2. Зачем выдумывать очередную модель атрибуции?
3. Вопросы от бизнеса и команды маркетинга
4. Исходные данные
5. Решение
6. Результат (плюсы/минусы, необходимые допущения)
7. А что еще можно сделать?

Защита информации

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

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

Зачем выдумывать очередную модель?

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

Что не так со “стандартными” моделями атрибуции

Общеизвестные модели (last non-direct, first/last paid click etc.) имеют значительные, на мой взгляд, недостатки, которые не позволяют обосновано оценить вклад каждого источника в конверсию. К тому же, выбор модели очень часто зависит от типа бизнеса, а также от целевой метрики.

Все приведенные выше модели в той или иной степени могут быть использованы для оценки (например, first click для cost per acquisition). Но если пользователь взаимодействовал с несколькими источниками перед первым заказом, как максимально объективно подойти к распределению ценности в этом случае?

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

Вопросы от бизнеса и команды маркетинга

От бизнеса (руководителей корпоративного блока) в этом случае был один простой вопрос:

Сколько юр. лиц открыли один или более расчетных счетов в Банке в рамках проведенной рекламной кампании?

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

От коллег из команды маркетинга задачи были следующие:

Оценить вклад каждого источника в привлечение клиентов (юр. лиц).

Оценить вклад каждого источника в открытие одного или нескольких счетов в Банке в рамках одного юр. лица

Пользователь мог оставить заявку на открытие счета, успешно его открыть, затем оставить заявку на открытие второго, третьего, n-ого счета, использовав при этом совершенно разные рекламные источники.

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

Исходные данные

После необходимых вводных перейдем, наконец, к сути статьи! Итак, что имеем в Google BigQuery.

1. Таблица с данными об открытии счетов, автоматически обновляемая каждый день. Условно обозначим ее T1.

AccountNumber — номер открытого счета

INN — ИНН заявителя

DateOpen — дата открытия счета

2. Таблица, содержащая ID заявки, ИНН и текущий статус заявки с временем апдейта в базе, и обновляемая два раза в день. Условно обозначим ее T2.

id — номер заявки на сайте

inn — ИНН заявителя

status — текущий статус заявки по открытию счета

lastStatusFrom — время апдейта статуса

3. Стриминговые таблицы, содержащие “сырые” данные о поведении пользователей на сайте

Решение

Шаг 1 — объединяем онлайн- и офлайн-данные

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

SELECT
  id,
  a.inn,
  rko.sql_get_app_status(status) status,
  EXTRACT(date FROM lastStatusFrom) statusUpdateDate,
  b.AccountNumber,
  b.DateOpen
FROM
  `project.dataset.prod_opps_info` a
LEFT JOIN(
  SELECT *
  FROM `project.dataset.rko_open_account`
) b ON a.inn = b.INN

В запросе используется user-defined function (rko.sql_get_app_status(status)), которая имеет вид:

CASE
  WHEN x = 'system_status_1' THEN 'читабельный статус 1'
  WHEN x = 'system_status_2' THEN 'читабельный статус 2'
  WHEN x = 'system_status_3' THEN 'читабельный статус 3'
  ...
  
  ELSE 'неизвестно'
END

Это необходимо для приведения статусов в нормальный читабельный вид и дальнейшего использования в отчетах.

Что происходит в этом запросе:

У читателя может возникнуть вопрос:

Зачем соединять эти таблицы? Ведь в таблице T2 уже есть статусы по каждой заявке. Можно соединить эту таблицу с данными об источниках.

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

Ситуация из будней банковского аналитика:

1 марта пользователь оставляет заявку. Ввиду некоторых технических особенностей, а также обстоятельств, на которые банк не может повлиять (зависящих напрямую от клиента и связанных с процессом открытия счета), на отклик со стороны банка может уйти от 1 до 3 дней. 3 марта пользователь “для уверенности”, что его нужды точно найдут отклик у Банка, заводит еще одну заявку. Далее банк связывается с пользователем, скажем, в тот же день, 3 марта, и подтверждает обращение, по которому в дальнейшем 4 марта открывается счет.

В итоге имеем две заявки, и один открытий счет. Вопрос — к какой заявке отнести открытие?

На самом деле, причин для второй, третьей, n-ой заявки может быть сколько угодно много. Это не так важно. По итогам мы имеем множество a(кол-во заявок) и множество b(кол-во открытых счетов), которые как-то связаны между собой.

Наша задача — определить функцию (алгоритм, систему), которая, приняв на вход множества a и b, а также (!) определенные допущения и правила, на выходе дает множество c, представляющее собой полученную на основе принятых допущений/правил оценку связи i-ных элементов a и b

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

Надеюсь, мы разобрались с этим принципом. Идем дальше! Обратно к SQL.

Несмотря на то, что в запросе используется LEFT JOIN, по сути, это CROSS JOIN, так как мы объединяем данные таблиц Т1 и Т2 по неуникальному ключу (в нашем случае, ИНН).

Почему ИНН не уникален?

1. В таблице Т1 (открытые счета) у одного ИНН может быть несколько открытых счетов.
2. В таблице T2 (заявки на открытие) у одного ИНН может быть несколько заявок.

Шаг 2 — подставляем источники заявок

В результате запроса из шага 1 мы получили вот такую таблицу:

Как видим, к одной заявке (например, 274818), было отнесено несколько открытых счетов. А у ИНН 54023… могло быть несколько заявок (множества a и b).

Далее присоединим данные по источникам заявок из сессионного стриминга к результатам джойна таблиц T1, Т2

WITH applications AS
(
  SELECT
    *,
    CAST(date AS DATETIME) appdate
  FROM
    `project.rko.applications`
),
crm AS
(
  SELECT
    id,
    a.inn,
    -- переводим статусы в читабельный вид с помощью UDF
    rko.sql_get_app_status(status) status,
    EXTRACT(date FROM lastStatusFrom) statusUpdateDate,
    b.AccountNumber,
    b.DateOpen
  FROM
    `project.dataset.prod_opps_info` a
  LEFT JOIN(
    SELECT
      AccountNumber,
      INN,
      DateOpen
    FROM
      `project.dataset.rko_open_account`
  ) b ON a.inn = b.INN
)
SELECT
  CAST(EXTRACT(date FROM appdate) AS STRING) date,
  CAST(EXTRACT(month FROM appdate) AS STRING) month,
  apps.id,
  source,
  medium,
  campaign,
  IFNULL(crm.status, 'нет информации') connect_status,
  crm.inn,
  IF(DATETIME_DIFF(crm.DateOpen, appdate, DAY) BETWEEN 0 AND 30, AccountNumber, NULL) AccountNumber
FROM
  applications AS apps
LEFT JOIN crm AS crm USING(id)

Что происходит в этом запросе:

Для того, чтобы не перегружать запрос, а также для улучшения его читабельности, я использую конструкцию WITH … AS . Данные по заявкам хранятся в отдельной таблице, которая обновляется каждый день. Это позволяет экономить ресурсы для регулярно исполняемых запросов вроде этого. Данные по заявкам достаются так:

SELECT
  date,
  SPLIT(date,'-')[OFFSET(1)] month,
  trafficSource.source,
  trafficSource.medium,
  trafficSource.campaign,
  SPLIT(hits.eventInfo.eventLabel, ' - ')[OFFSET(1)] id
FROM
  `project.dataset.sessions_2020*`, UNNEST(hits) hits
WHERE
  hits.eventInfo.eventCategory = 'резервирование счета (малый бизнес)'
  AND hits.eventInfo.eventAction = 'отправка заявки'
  AND page.hostname = "www.bank.ru"
  AND _TABLE_SUFFIX BETWEEN FORMAT_DATE("%m%d",DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY))
  AND FORMAT_DATE("%m%d",DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY))

Разберем более детально часть запроса, где происходит join.

SELECT
  CAST(EXTRACT(date FROM appdate) AS STRING) date,
  CAST(EXTRACT(month FROM appdate) AS STRING) month,
  apps.id,
  source,
  medium,
  campaign,
  IFNULL(crm.status, 'нет информации') connect_status,
  crm.inn,
  IF(DATETIME_DIFF(crm.DateOpen, appdate, DAY) BETWEEN 0 AND 30, AccountNumber, NULL) AccountNumber
FROM
  applications AS apps
LEFT JOIN crm AS crm USING(id)

Здесь есть два ключевых момента.

Первый

IF(DATETIME_DIFF(crm.DateOpen, appdate, DAY) BETWEEN 0 AND 30, AccountNumber, NULL) AccountNumber

Фактически, для обработчика запросов здесь написано:

Если разница между датой заявки и датой открытия счета не превышает 30 дней (включительно), подставь подходящий по ИНН номер счета, в противном случае поставь NULL

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

Кстати, это условие можно было выставить и на первом шаге, когда мы объединяли ID заявок и открытые счета. Но мы сознательно этого не делаем. Объяснение — в конце статьи.

Второй

IFNULL(crm.status, 'нет информации') connect_status,

Если нам не удалось найти ни одного открытого счета по заявке, поставь ей статус нет информации.Таким образом, каждая заявка получит какой-то статус.

По итогам выполнения запроса из шага 2 мы получаем вот такую таблицу:

Последние два столбца — ИНН и номер счета. Здесь ничего сложного. Просто у заявок появились источники (source, medium, campaign).

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

Здесь начинается самое интересное!

Шаг 3 — решаем атрибуцию

Чтобы лучше понять, что за данные перед нами, посмотрим на несколько примеров с использованием определенных ИНН. Выполним еще раз запрос из шага 2, но с условием WHERE INN = ‘номер ИНН’

Первый

Видим пример ситуации, ранее описанной на шаге 1 — пользователь оставил 2 заявки в один день, и ему в итоге открыли 1 счет. Непонятно, как распределить ценность между контекстом yandex и google.

Посмотрим другой пример:

Второй

Еще интереснее ситуация! 4 заявки с промежутком в 3 дня (1+3) с 4х разных источников, 2 открытых счета. При этом первой заявке может быть атрибутирован только 1 счет.

Наконец, посмотрим еще один примечательный случай:

Третий

3 заявки с двух источников и 2 открытых счета. При этом первая заявка вовсе не получила открытых счетов (так как с ее даты и до первого открытого счета у этого ИНН прошло более 30 дней).

И это не все “интересные” случаи.

Так как же при этих условиях решить атрибуцию?

Для этого вернемся на шаг 1 и посмотрим на задачу. Нам надо каким-то образом оценить взаимосвязь источников и открытий счетов при определенных допущениях и правилах. О них далее и пойдет речь.

На мой взгляд, нельзя рассматривать эффективность какой бы то ни было атрибуции в отрыве от целевой метрики.

В нашем конкретном случае мы рассматриваем две метрики:

1. количество ИНН с открытыми счетами по заявкам с сайта
2. количество открытых счетов на один ИНН

Правильный и закономерный вопрос: почему именно эти метрики?

Ответ:

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

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

Таким образом, наши правила следующие:

- ценность заявки (источника) пропорциональна порядку ее появления в истории заявок пользователя (ИНН). Иными словами, первая заявка получит бОльшую ценность, чем вторая заявка. Та, в свою очередь, бОльшую ценность, чем третья и так далее

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

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

WITH applications AS
(
  SELECT
    *,
    CAST(date AS DATETIME) appdate
  FROM
    `project.rko.applications`
),
crm AS
(
  SELECT
    id,
    a.inn,
    -- переводим статусы в читабельный вид с помощью UDF
    rko.sql_get_app_status(status) status,
    EXTRACT(date FROM lastStatusFrom) statusUpdateDate,
    b.AccountNumber,
    b.DateOpen
  FROM
    `project.dataset.prod_opps_info` a
  LEFT JOIN(
    SELECT
      AccountNumber,
      INN,
      DateOpen
    FROM
      `project.dataset.rko_open_account`
  ) b ON a.inn = b.INN
)
/*
здесь расчитываем вес каждого канала для отдельно взятого ИНН
и вычисляем коэфициент качества (вклад каждого источиник в открытый счет)
*/
SELECT DISTINCT
  * except(AccountNumber, connect_status),
  ROUND(CUME_DIST() OVER(PARTITION BY inn ORDER BY date DESC, id DESC),1) weight,
COUNT(AccountNumber) OVER (PARTITION BY id) n_accounts_by_id,  COUNT(AccountNumber) OVER (PARTITION BY id) * ROUND(CUME_DIST() OVER(PARTITION BY inn ORDER BY date DESC, id DESC),1) quality_coeff
FROM(
SELECT
  CAST(EXTRACT(date FROM appdate) AS STRING) date,
  CAST(EXTRACT(month FROM appdate) AS STRING) month,
  apps.id,
  source,
  medium,
  campaign,
  IFNULL(crm.status, 'заявки нет в базе коннекта') connect_status,
  crm.inn,
  IF(DATETIME_DIFF(crm.DateOpen, appdate, DAY) BETWEEN 0 AND 30, AccountNumber, NULL) AccountNumber
FROM
  applications AS apps
LEFT JOIN crm AS crm ON apps.id = crm.id
) ORDER BY date, id

Перед тем, как смотреть результаты, давайте разберем запрос. Добавилось 3 новых поля, а именно: weight, n_accounts_by_id, quality_coeff.

Разберем эти поля по порядку.

weight

ROUND(CUME_DIST() OVER(PARTITION BY inn ORDER BY date DESC, id DESC),1) weight

Здесь с помощью функции CUME_DIST() мы ранжируем ценность заявок согласно первой метрике.

Функция возвращает относительный ранг строки, определяемой как NP/NR. NP определяется как количество строк, которые либо предшествуют текущей строке, либо являются равными ей. NR — это количество строк в разделе.

n_accounts_by_id

COUNT(AccountNumber) OVER (PARTITION BY id) n_accounts_by_id,

Здесь мы считаем, какое количество заявок может быть ассоциировано с определенной заявкой. Это поле необходимо в рамках второй метрики.

quality_coeff

Здесь мы, по сути, присваиваем оценку заявке (источнику) исходя из обеих метрик.

weight * n_accounts_by_id

Результаты

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

Первый

2 заявки, 1 открытый счет в рамках 30-дневного окна. У нас был вопрос — как распределить ценность между контекстом google и yandex. Смотрим на поле quality_coeff.

• google был источником первой заявки, поэтому получает вес 1. эта заявка может быть ассоциирована с единственным счетом — 1.
• yandex был источником второй заявки. она так же может быть ассоциирована с открытым счетом — 1. однако из-за того, что это вторая заявка у данного ИНН, она получает меньший вес — 0.5

Таким образом, в рамках этого ИНН ценность google=1, yandex=0.5

Второй

4 заявки, 4 разных источника, 2 открытых счета.

Здесь нам пришлось избавиться от полей AccountNumber и connect_status, так как они мешают “схлопыванию” заявок в рамках DISTINCT.

• первый источник google / organic получил ценность 1, так как только один счет можно ассоциировать с заявкой и он был первым в ряду нескольких источников
• второй источник google / cpc получил ценность 1.6, так как оба открытых счета могут быть привязаны к заявке, и он был вторым в очереди.
• третий источник go.mail.ru / referral получил ценность 1, так как оба открытых счета могут быть привязаны к заявке, и он был третьим в очереди заявок.
• наконец, четвертый источник yandex_serach / cpc получил ценность 0.6, так как оба открытых счета могут быть привязаны к заявке, и он был последним в очереди.

Третий

Три заявки, три источника, два открытых счета.

• первый источник yandex_serach / cpc не получил никакой ценности, (quality_coeff=0) так как, несмотря на то, что он был источником привлечения, к этой заявке нельзя отнести ни один открытый счет в рамках 30-дневного окна атрибуции.
• второй источник google / organic получил ценность 1.4, так, как несмотря на то, что с ним можно ассоциировать обе оффлайн-конверсии, он был вторым в очереди.
• третий источник, все тот же yandex_serach / cpc, получил ценность 0.6, так как был третьим по списку.

Таким образом, мы можем оценить вклад каждого источника в каждую офлайн-конверсию (открытый счет) по каждому ИНН.

Чтобы посчитать количество привлеченных ИНН с хотя бы одним открытым счетом в рамках заявок с сайта, нужно выполнить следующий запрос к таблице:

SELECT
  -- distinct нужен для избавления от грануляции по ID заявки, источникам и т.д.
  COUNT(DISTINCT IF(n_accounts_by_id > 0, inn, NULL))
FROM
  result_table
WHERE

Чтобы посчитать эффективность источников, выполняем запрос:

SELECT
  source,
  medium,
  campaign,
  SUM(quality_coeff)
FROM
  result_table
GROUP BY
  1,2,3ORDER BY 4 DESC

Извините, для сохранения конфиденциальности маркетинговых партнеров некоторые источники пришлось скрыть.

В итоге получаем гранулированную по source, medium, campaign таблицу с оценкой эффективности рекламных каналов, которая была получена исходя из основных метрик с 30-дневным окном для оффлайн-конверсии.

Почему мы не выставляем 30-дневное окно атрибуции при соединении онлайн-оффлайн данных (шаг 1)?

Ответ: это нужно для того, чтобы оценить, какое количество оффлайн-конверсий остаются “за бортом” атрибуции.

IF(AccountNumber IS NOT NULL,
'счет открыт',
IF(connect_status = 'счет открыт' AND AccountNumber IS NULL, 'счет открыт, но окно атрибуции не позволяет отнести его к заявке', connect_status)
) status

Добавив вот эту строку в финальный запрос, можем посмотреть, сколько конверсий осталось без оценки из-за окна.

Рассмотрим теперь плюсы и минусы модели, а также необходимые для ее работы допущения.

Плюсы

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

Минусы

• модель не учитывает источники сессий, где не была осуществлена отправка заявки, а это может иметь существенное значение при оценке вклада источника в конверсию
• сильная зависимость результатов от выбранного интервала атрибуции для офлайн-конверсий. если некорректно выбрать этот интервал, ценность расчетов будет нивелирована
• данная модель учитывает только сессии на сайте. для мультиплатформенных расчетов потребуется дополнительные данные/их подготовка
• очевидная зависимость от точности поступающих данных по заявкам (онлайн-конверсиям). расчеты будут неточными, если в GBQ попадают не все заявки

Допущения

Этот параграф — просто необходимое зло, которое, однако, нельзя игнорировать.

• обстоятельства, которые изначально сподвигли пользователя оформить заявку на открытие счета, могли возникнуть за рамками взаимодействия с сайтом. мы допускаем, что в расчетах они никак не учитываются
• между двумя заявками могло произойти что угодно. это “что угодно” могло иметь решающие значение для повторной заявки. мы допускам, что рекламные источники могут быть здесь ни при чем.
• за разными ИНН могут стоят одни и те же физ. лица, которые изначально более лояльны к определенному банку. мы допускаем, что игнорируем это в расчетах.

А что еще можно сделать?

Безусловно, это модель может быть улучшена. Вот возможные варианты:

• не учитывать direct трафик в расчетах. здесь надо быть аккуратным с полем trueDirect . так как, допустим, в сессионных стримингах GA360 и OWOX логика этих полей различается
• для каждого региона — свое окно атрибуции. это позволит более точно оценить эффективность маркетинга для различных отделений банка
• начать учитывать мультиплатформенность пользователей. пользователь мог оставить заявку как на сайте, так и в приложении. это тоже надо учитывать
• добавить источники, в которых не было взаимодействий. они могут иметь существенное влияние на конверсию
• применить понижающий коэффициент для брендового контекста
• добавить косты к оценке источников

Вопросы, отзывы, предложения

Ух, большая получилась статья! Надеюсь, если вы дочитали ее до конца :), она была полезна для вас. Если у вас возникли вопросы, уточнения, или вы заметили где-то явную ошибку, пожалуйста, дайте мне знать об этом. Можно прямо здесь, в комментариях. Или напишите мне в телеграм — @dmitriilin

У меня есть блог, посвященный анализу данных — dmitriilin.com

Там можно найти решения не только для GBQ, но и для GTM, GA, GSheets и других инструментов. See you there!