Атрибуция в маркетинге долгое время считалась одной из самых неприятных аналитических задач. Но объединив сырые данные (raw data), SQL и dbt (Data Build Tool) ранее такая сложная задача может стать невероятно простой.
Атрибуция в маркетинге — это по сути ваше понимание механизмов и тактик маркетинга, которые способствуют привлечению новых клиентов в ваш бизнес. Рано или поздно каждому аналитику предстоит углубиться в дремучий лес маркетинговой атрибуции, ведь это критически важная информация, которая необходима каждой маркетинговому отделу в мире. Если вам будет интересно познать степень опечаленности ваших коллег при упоминании маркетинговой атрибуции, просто просмотрите цепочку комментариев под этим твитом (лично я остановился после 50-ого):
В чем проблема с маркетинговой атрибуцией для SaaS?
Смог ли кто-нибудь выработать к ней подход или это гиблое дело?
Что на счет разных моделей: first-touch, last-touch, linear, decay, the works...
И прошу, только не заливайте мне о Google Analytics.
Что вы там найдете:
Так что же вам действительно необходимо для построения модели атрибуции?
Собственно на этом все! Добавив SQL поверх сырых данных, как результат, вы получите следующее:
3. Самую прозрачную модель атрибуции. Вам не нужно полагаться на логику вендоров. Если ваш отдел продаж считает, что ваша атрибуция неверна, покажите им dbt-доки, пройдитесь с ними по логике вашей модели и внесите изменения с помощью одной строки SQL.
Атрибуция в маркетинге подразумевает большие наборы данных, и это действительно интересная инженерно-аналитическая задача. Давайте же рассмотрим, как построить лучшую модель атрибуции, которую когда-либо видел ваш бизнес.
Для этого мы собираемся построить таблицу, содержащую каждое “касание” (touch), которое предшествовало тому, как конкретный человек стал клиентом, и канал, который привел к этому касанию.
Затем мы оцениваем относительный вес каждого касания, которое привело к конверсии. Эта оценка выполняется путем присвоения касаниям «баллов»: каждая конверсия приносит ровно один балл, и этот балл делится между касаниями клиента. Существует четыре основных способа распределить этот балл:
В общем, вот примерно то, с чем мы будем работать (на примере электронной коммерции)
Здесь мы видим, что у клиента 745 было четыре взаимодействия до конверсии: он сначала перешел на ваш сайт по рекламе в Facebook, затем он попал туда через Adwords-рекламу, и, наконец, посетили ваш сайт, введя URL-адрес прямо в браузер (дважды!) перед покупкой.
Если вы сразу готовы погрузиться в dbt-проект, чтобы увидеть, как это все работает, вы можете ознакомиться с нашим примером проекта атрибуции здесь. Если нет, то далее для вас последует подробный разбор.
Необходимая dbt-техника: пакеты
Нам нужна таблица, которая отражает каждый раз, когда клиент взаимодействует с нашим брендом. Для компаний электронной коммерции самое близкое, чем мы можем воспользоваться, — это сессии (sessions, сеансы). (Если же вы работаете в B2B организации, вам скорее всего больше подойдут таблицы взаимодействий между вашим отделом продаж и потенциальным клиентом из вашей CRM).
Сессии — это дискретные периоды активности клиентов на сайте. Отраслевые стандарты определяет сессию как серию действий, за которыми следует 30-минутное окно без какой-либо активности.
Вот пример:
Стоит отметить, что сессии также содержат реферальную информацию, которая помогает нам понять откуда пришел клиент. Эти UTM-теги часто устанавливаются отделом маркетинга, поэтому всегда стоит сначала проверить, есть ли у них какая-либо таблица, определяющая теги, которая использует ваша компания. Точная иерархия для source, medium и campaign часто варьируется от бизнеса к бизнесу. Вот что говорится по этой теме в доках Google Analytics:
После того, как данные о просмотрах страниц уже есть в вашем хранилище, вам нужно будет сделать две вещи:
Такого рода моделирование — достаточно сложная задача, особенно для компаний с тысячами просмотров страниц в день (Спасибо, Господи, за инкрементальные модели). К счастью, кое-кто очень умный написал пакеты, которые сделают за вас всю тяжелую работу, независимо от того, отслеживаются ли просмотры ваших страниц с помощью Snowplow, Segment или Heap. Пользуйтесь этими благами, установив соответствующий пакет для преобразования данных.
Конверсии:
Вам также нужно знать, когда клиент совершил конверсию. Обычно для компании электронной коммерции это факт совершения первого заказа этим клиентом.
Вам может потребоваться преобразовать данные для получения этой формы - для этого используйте dbt.
select
*
from sessions
left join conversion using (customer_id)
where sessions.started_at <= customer_conversions.converted_at
and sessions.started_at >= dateadd(days, -30, customer_conversions.converted_at)
Мы часто ограничиваем сессии, которые засчитываются для атрибуции, только сессиями в течение 30 дней, предшествующих конверсии (что часто называют “окном атрибуции”). Это имеет особый смысл, когда у вас есть четкое представление о пути конверсии, который находится в определенном временном диапазоне; если вы будет засчитывать древние касания, это может привести к получению странных или бесполезных данных.
Когда мы ограничились только сессиями, предшествующими конверсии, нам нужно узнать следующую информацию:
select
*,
count(*) over (
partition by customer_id
) as total_sessions,
row_number() over (
partition by customer_id
order by sessions.started_at
) as session_number
from sessions
left join customer_conversions using (customer_id)
where sessions.started_at <= customer_conversions.converted_at
and sessions.started_at >= dateadd(days, -30, customer_conversions.converted_at)
select
*,
case
when total_sessions = 1 then 1.0
when total_sessions = 2 then 0.5
when session_number = 1 then 0.4
when session_number = total_sessions then 0.4
else 0.2 / (total_sessions - 2)
end as forty_twenty_forty_points,
case
when session_number = 1 then 1.0
else 0.0
end as first_touch_points,
case
when session_number = total_sessions then 1.0
else 0.0
end as last_touch_points,
1.0 / total_sessions as linear_points
from sessions_before_conversion
Наконец-то! Вы построили свою модель атрибуции! По этим баллам вы можете определить, какой канал и кампания привели к наибольшему количеству конверсий с течением времени. Выполнение такого рода агрегирования мы уже оставим нашим бизнес-аналитикам:
-- in your BI tool:
select
date_trunc(week, converted_at) as date_week,
utm_campaign,
sum(first_touch_points) as attribution_points
from attribution
group by 1, 2
В зависимости от вашего инструмента бизнес-аналитики вы также можете подумать над созданием интерфейсов, позволяющих заинтересованным сторонам (стейкхолдерам) переключаться между методологиями атрибуции и уровнем агрегации (отчеты по дням, неделям и месяцам, а также группировка по кампаниям, источникам или средствам).
Но погодите-ка, почему мы отталкиваемся здесь от даты конверсии, а не от даты сессии? Когда происходят сессии, мы не знаем, приведут ли они к конверсиям (и когда, если приведут). В результате, если мы будем группировать все по дате сессии, наши коэффициенты конверсии продолжат расти от недели к неделе.
Для маркетингового отдела это может затруднить принятие решений, поскольку цифры всегда меняются. Поэтому вместо того, чтобы отвечать на вопрос “какое количество сессий Facebook привело к конверсиям на этой неделе?”, мы выбираем ответить на вопрос “сколько конверсий на этой неделе было результатом сессий Facebook?” Теперь, когда ваш отдел маркетинга знает, какие каналы ведут к наибольшему количеству конверсий они могут спросить: “Какой канал приводит к наиболее ценным конверсиям?”
Просто умножьте свои баллы на ценность конверсии:
select
... ,
revenue * first_touch_points as first_touch_revenue,
revenue * last_touch_points as last_touch_revenue,
revenue * forty_twenty_forty_points as forty_twenty_forty_revenue,
revenue * linear_points as linear_revenue
from sessions_before_conversion
Теперь, когда ваш отдел маркетинга знает, какие каналы ведут к наиболее ценным конверсиям, они могут спросить “какой канал обеспечивает максимальную окупаемость наших затрат? "
Для этого вам необходимо поместить в свое хранилище данные с каждой платформы, на которую вы тратите деньги (Adwords, Facebook, Instagram, Bing и т. д.). Мы используем Stitch и Fivetran для доступа к API всех рекламных платформ и загрузки этих данных в наше хранилище. Поскольку эти источники данных загружаются в формате источника (т.е. столбцы и таблицы именуются API платформы, а не нами), вам необходимо преобразовать их, чтобы получить согласованную структуру, а затем объединить их все вместе.
(Профессиональный совет: поищите на dbt package hub пакеты под конкретные рекламные платформы, которые сделают эту тяжелую работу за вас!)
Важно отметить, что эти данные должны иметь те же utm параметры, что и данные вашей сессии. Таким образом, мы сможем объединить два набора данных для расчета:
select * from {{ ref('ad_spend') }}
),
attribution as (
select * from {{ ref('attribution_touches') }}
),
-- aggregate first as this is easier to debug / often leads to fewer fanouts
ad_spend_aggregated as (
select
date_trunc('month', date_day) as date_month,
utm_source,
sum(spend) as total_spend
from ad_spend
group by 1, 2
),
attribution_aggregated as (
select
date_trunc('month', converted_at) as date_month,
utm_source,
sum(linear_points) as attribution_points,
sum(linear_revenue) as attribution_revenue
from attribution
group by 1, 2
),
joined as (
select
*,
1.0 * nullif(total_spend, 0) / attribution_points as cost_per_acquisition,
1.0 * attribution_revenue / nullif(total_spend, 0) as return_on_advertising_spend
from attribution_aggregated
full outer join ad_spend_aggregated
using (date_month, utm_source)
)
select * from joined
order by date_month, utm_source
Это даст нам представление о данных такого рода:
В этом запросе следует обратить внимание на несколько вещей:
Но прежде чем поделиться дашбордами с более широким кругом лиц, протестируйте их со своими стейкхолдерами, чтобы найти возможные проблемы и понять, насколько они важны, и стоит ли их как-нибудь менять. Но не дожидайтесь, пока ваша работа станет идеальной, прежде чем поделиться ею!
В зависимости от вашего инструмента бизнес-аналитики вы также можете подумать на параметрами дашборда, чтобы иметь возможность:
При этом стоит признать, что эту модель лучше всего себя показывает при просмотре данных в совокупности. Мы рекомендуем:
Двое ваших клиентов могут пользоваться одним компьютером, и все их сессии могут быть связаны с недавней покупкой, совершенной кем-то одним из них. Или (что более вероятно) один пользователь может иметь сразу две учетные записи с разных устройств.
Дело в том, что веб-отслеживание ненадежно. Для некоторой части вашей клиентской базы цифры всегда будут немного неточными.
Вам также необходимо учитывать, что именно определяет “конверсию”, особенно когда конверсии происходят на уровне аккаунта, но взаимодействие может происходить на уровне клиента.
Настройте свой SQL, чтобы найти все сессии предшествующие первому заказу, а также сессии, которые были в промежутке между каждым заказом — вам нужно будет использовать оконную функцию в таблице orders, чтобы найти дату предыдущего заказа. Убедитесь, что вы используете order_id вместо вашего customer_id в операторе partition оконной функции.
select
*,
count(*) over (
partition by order_id
) as total_sessions,
row_number() over (
partition by order_id
order by sessions.started_at
) as session_number
from sessions
left join orders
using (customer_id)
where sessions.started_at <= orders.created_at
and sessions.started_at >= dateadd(days, -30, orders.created_at)
-- ensure sessions aren't counted twice
-- use a coalesce to ensure the first order isn't excluded by a NULL join
and coalesce(sessions.started_at > orders.previous_created_at, true)
Для источников, о которых у вас нет записей о взаимодействии (в частности, для OOH-кампаний), вам нужно будет сделать некоторые обоснованные предположения, основанные на относительном росте трафика (это часто называют моделированием всплесков, но это уже выходит за рамки этой статьи!).
Затем вы достигните решающего момента, где вам придется ответить для себя на вопросы:
Вместо этого лучше сделайте шаг назад, поймите, что вы проделали отличную работу, и двигайтесь дальше.
Спасибо Эрин Огилви, Тристану Хэнди и Джанессе Ланц за их неоценимый вклад в это руководство.
habr.com
Атрибуция в маркетинге — это по сути ваше понимание механизмов и тактик маркетинга, которые способствуют привлечению новых клиентов в ваш бизнес. Рано или поздно каждому аналитику предстоит углубиться в дремучий лес маркетинговой атрибуции, ведь это критически важная информация, которая необходима каждой маркетинговому отделу в мире. Если вам будет интересно познать степень опечаленности ваших коллег при упоминании маркетинговой атрибуции, просто просмотрите цепочку комментариев под этим твитом (лично я остановился после 50-ого):
В чем проблема с маркетинговой атрибуцией для SaaS?
Смог ли кто-нибудь выработать к ней подход или это гиблое дело?
Что на счет разных моделей: first-touch, last-touch, linear, decay, the works...
И прошу, только не заливайте мне о Google Analytics.
Что вы там найдете:
- Продукты, которые стоят тысячи долларов в месяц, чаще всего построены по “принципу черного ящика” без какого либо намека на прозрачность.
- “Простые”, “готовые” решения, которые стоят недорого, предлагают анализ только ограниченного объема данных.
- Маркетологов, которые полностью опустили руки (просто действуют наобум) и измотаны обещаниями вендоров.
Так что же вам действительно необходимо для построения модели атрибуции?
- Сырые данные в вашем хранилище (warehouse), которые отражают все многообразие взаимодействия клиентов с вашим брендом. Для электронной коммерции — это будут посещения их веб-сайтов. Для клиентов B2B - это могут быть разговоры с отделами продаж.
Собственно на этом все! Добавив SQL поверх сырых данных, как результат, вы получите следующее:
- Самую дешевую модель атрибуции. В этом руководстве предполагается, что вы работаете с современным стеком данных, и уже обладаете всей необходимой инфраструктурой:
- Данные о событиях вы собираете, например, с помощью инструментов Snowplow или Segment (хотя Segment может быть дороговат);
- Данные с рекламных платформ извлекаете с помощью Stitch или Fivetran;
- Загружаете данные в современное облачное хранилище данных, такое как Snowflake, BigQuery или Redshift;
- Используете dbt, чтобы ваши аналитики могли моделировать данные в SQL.
3. Самую прозрачную модель атрибуции. Вам не нужно полагаться на логику вендоров. Если ваш отдел продаж считает, что ваша атрибуция неверна, покажите им dbt-доки, пройдитесь с ними по логике вашей модели и внесите изменения с помощью одной строки SQL.
Атрибуция в маркетинге подразумевает большие наборы данных, и это действительно интересная инженерно-аналитическая задача. Давайте же рассмотрим, как построить лучшую модель атрибуции, которую когда-либо видел ваш бизнес.
Атрибуционная модель данных
На самом деле невозможно точно сказать, почему тот или иной человек становится вашим клиентом. Лучшее, что мы можем сделать, как аналитики, чтобы подобраться к сути — это сделать довольно хорошее предположение. Для этого мы собираемся использовать подход, называемый позиционной атрибуцией (positional attribution). По сути, это означает, что мы будем взвешивать важность различных касаний (touch — взаимодействие клиента с брендом) в зависимости от их положения (порядка, в котором они происходят на протяжении жизненного цикла клиента).Для этого мы собираемся построить таблицу, содержащую каждое “касание” (touch), которое предшествовало тому, как конкретный человек стал клиентом, и канал, который привел к этому касанию.
Затем мы оцениваем относительный вес каждого касания, которое привело к конверсии. Эта оценка выполняется путем присвоения касаниям «баллов»: каждая конверсия приносит ровно один балл, и этот балл делится между касаниями клиента. Существует четыре основных способа распределить этот балл:
- First touch: приписать заслугу за всю конверсию первому касанию.
- Last touch: приписать всю конверсию последнему касанию.
- Forty-twenty-forty: приписать 40% (то есть 0,4 балла) к первому касанию, 40% к последнему касанию, и оставшиеся 20% разделить между всеми касаниями поровну.
- Linear: разделить балл поровну между всеми касаниями.
В общем, вот примерно то, с чем мы будем работать (на примере электронной коммерции)
| CUSTOMER_ID | SESSION_ID | STARTED_AT | UTM_SOURCE | UTM_MEDIUM | UTM_CAMPAIGN | CONVERTED_AT | FIRST_TOUCH_POINTS | LAST_TOUCH_POINTS | FORTY_TWENTY_FORTY_POINTS | LINEAR_ATTRIBUTION_POINTS |
| 745 | 4 | 2020-02-03 12:29:32 | facebook_ads | paid_social | 10percentpromocode | 2020-02-04 10:05:31 | 1 | 0 | 0.4 | 0.25 |
| 745 | 16 | 2020-02-03 18:50:24 | adwords | paid_search | branded_search | 2020-02-04 10:05:31 | 0 | 0 | 0.1 | 0.25 |
| 745 | 23 | 2020-02-04 04:50:24 | direct | 2020-02-04 10:05:31 | 0 | 0 | 0.1 | 0.25 | ||
| 745 | 32 | 2020-02-04 10:05:21 | direct | 2020-02-04 10:05:31 | 0 | 1 | 0.4 | 0.25 | ||
| 2956 | 1 | 2020-02-01 12:55:16 | facebook_ads | paid_social | 10percentpromocode | 2020-02-10 01:54:54 | 1 | 0 | 0.4 | 0.33 |
| 2956 | 68 | 2020-02-10 00:51:56 | facebook_ads | paid_social | freeshipping | 2020-02-10 01:54:54 | 0 | 0 | 0.2 | 0.33 |
| 2956 | 69 | 2020-02-10 01:53:55 | bing | search | 2020-02-10 01:54:54 | 0 | 1 | 0.4 | 0.33 |
Если вы сразу готовы погрузиться в dbt-проект, чтобы увидеть, как это все работает, вы можете ознакомиться с нашим примером проекта атрибуции здесь. Если нет, то далее для вас последует подробный разбор.
Как построить модель атрибуции
1. Соберите необходимые источники данных
Сессии:Необходимая dbt-техника: пакеты
Нам нужна таблица, которая отражает каждый раз, когда клиент взаимодействует с нашим брендом. Для компаний электронной коммерции самое близкое, чем мы можем воспользоваться, — это сессии (sessions, сеансы). (Если же вы работаете в B2B организации, вам скорее всего больше подойдут таблицы взаимодействий между вашим отделом продаж и потенциальным клиентом из вашей CRM).
Сессии — это дискретные периоды активности клиентов на сайте. Отраслевые стандарты определяет сессию как серию действий, за которыми следует 30-минутное окно без какой-либо активности.
Вот пример:
| SESSION_ID | CUSTOMER_ID | STARTED_AT | ENDED_AT | UTM_SOURCE | UTM_MEDIUM | UTM_CAMPAIGN |
| 1 | 2956 | 2020-02-01 12:55:16 | 2020-02-01 12:55:47 | facebook_ads | paid_social | 10percentpromocode |
| 2 | 4 | 2020-02-02 13:06:47 | 2020-02-02 13:18:56 | facebook_ads | paid_social | freeshipping |
| 3 | 1170 | 2020-02-03 12:15:00 | 2020-02-03 12:15:19 | facebook_ads | paid_social | 10percentpromocode |
- utm_source: рекламодатель, сайт, публикация и т. д., который направляет трафик на ваш ресурс, например: google, newsletter (рассылка по электронной почте), billboard (баннер).
- utm_medium: рекламное или маркетинговое средство, например: cpc, баннер, рассылка по электронной почте.
- utm_campaign: имя кампании, слоган, промокод и т. д. применимые к продукту.
После того, как данные о просмотрах страниц уже есть в вашем хранилище, вам нужно будет сделать две вещи:
- Разбивка на сессии (sessionization): агрегировать эти просмотры страниц в сессии, добавив логику, которая определяет промежутки продолжительностью 30 минут или более.
Такого рода моделирование — достаточно сложная задача, особенно для компаний с тысячами просмотров страниц в день (Спасибо, Господи, за инкрементальные модели). К счастью, кое-кто очень умный написал пакеты, которые сделают за вас всю тяжелую работу, независимо от того, отслеживаются ли просмотры ваших страниц с помощью Snowplow, Segment или Heap. Пользуйтесь этими благами, установив соответствующий пакет для преобразования данных.
Конверсии:
Вам также нужно знать, когда клиент совершил конверсию. Обычно для компании электронной коммерции это факт совершения первого заказа этим клиентом.
| CUSTOMER_ID | CONVERTED_AT |
| 1170 | 2020-02-03 14:20:08 |
| 2014 | 2020-02-04 4:30:21 |
| 2265 | 2020-02-04 9:43:35 |
2. Найдите все сессии предшествующие конверсии
Мы хотим ограничить наш анализ только сессиями, которые произошли до конверсии. Для этого нужно объединить два источника данных:select
*
from sessions
left join conversion using (customer_id)
where sessions.started_at <= customer_conversions.converted_at
and sessions.started_at >= dateadd(days, -30, customer_conversions.converted_at)
Мы часто ограничиваем сессии, которые засчитываются для атрибуции, только сессиями в течение 30 дней, предшествующих конверсии (что часто называют “окном атрибуции”). Это имеет особый смысл, когда у вас есть четкое представление о пути конверсии, который находится в определенном временном диапазоне; если вы будет засчитывать древние касания, это может привести к получению странных или бесполезных данных.
3. Рассчитайте общее количество сессий и их индексы
Необходимая SQL-техника: оконные функции.Когда мы ограничились только сессиями, предшествующими конверсии, нам нужно узнать следующую информацию:
- Сколько сессий было у этого клиента до конверсии? (total_sessions)
- Каков порядковый номер каждой сессии в рамках каждой конкретной группы сессий? (session_index)
select
*,
count(*) over (
partition by customer_id
) as total_sessions,
row_number() over (
partition by customer_id
order by sessions.started_at
) as session_number
from sessions
left join customer_conversions using (customer_id)
where sessions.started_at <= customer_conversions.converted_at
and sessions.started_at >= dateadd(days, -30, customer_conversions.converted_at)
3. Распределите баллы
Теперь, когда у нас есть поля session_index и total_session, мы находимся в пределах всего нескольких case-операторов от наших заветных баллов атрибуции:select
*,
case
when total_sessions = 1 then 1.0
when total_sessions = 2 then 0.5
when session_number = 1 then 0.4
when session_number = total_sessions then 0.4
else 0.2 / (total_sessions - 2)
end as forty_twenty_forty_points,
case
when session_number = 1 then 1.0
else 0.0
end as first_touch_points,
case
when session_number = total_sessions then 1.0
else 0.0
end as last_touch_points,
1.0 / total_sessions as linear_points
from sessions_before_conversion
Наконец-то! Вы построили свою модель атрибуции! По этим баллам вы можете определить, какой канал и кампания привели к наибольшему количеству конверсий с течением времени. Выполнение такого рода агрегирования мы уже оставим нашим бизнес-аналитикам:
-- in your BI tool:
select
date_trunc(week, converted_at) as date_week,
utm_campaign,
sum(first_touch_points) as attribution_points
from attribution
group by 1, 2
В зависимости от вашего инструмента бизнес-аналитики вы также можете подумать над созданием интерфейсов, позволяющих заинтересованным сторонам (стейкхолдерам) переключаться между методологиями атрибуции и уровнем агрегации (отчеты по дням, неделям и месяцам, а также группировка по кампаниям, источникам или средствам).
Но погодите-ка, почему мы отталкиваемся здесь от даты конверсии, а не от даты сессии? Когда происходят сессии, мы не знаем, приведут ли они к конверсиям (и когда, если приведут). В результате, если мы будем группировать все по дате сессии, наши коэффициенты конверсии продолжат расти от недели к неделе.
Для маркетингового отдела это может затруднить принятие решений, поскольку цифры всегда меняются. Поэтому вместо того, чтобы отвечать на вопрос “какое количество сессий Facebook привело к конверсиям на этой неделе?”, мы выбираем ответить на вопрос “сколько конверсий на этой неделе было результатом сессий Facebook?” Теперь, когда ваш отдел маркетинга знает, какие каналы ведут к наибольшему количеству конверсий они могут спросить: “Какой канал приводит к наиболее ценным конверсиям?”
4. [Бонус] Добавьте показатели дохода
Если у вас есть долларовая стоимость конверсии, вам следует присоединить ее к своей модели!Просто умножьте свои баллы на ценность конверсии:
select
... ,
revenue * first_touch_points as first_touch_revenue,
revenue * last_touch_points as last_touch_revenue,
revenue * forty_twenty_forty_points as forty_twenty_forty_revenue,
revenue * linear_points as linear_revenue
from sessions_before_conversion
Теперь, когда ваш отдел маркетинга знает, какие каналы ведут к наиболее ценным конверсиям, они могут спросить “какой канал обеспечивает максимальную окупаемость наших затрат? "
5. [Бонус] Добавьте данные по расходам на рекламу
Для этого в вашем хранилище должны быть максимально прозрачные данные о расходах на рекламу - одна запись на кампанию, на день, с атрибутами кампании (канал, источник) и потраченной суммой.Для этого вам необходимо поместить в свое хранилище данные с каждой платформы, на которую вы тратите деньги (Adwords, Facebook, Instagram, Bing и т. д.). Мы используем Stitch и Fivetran для доступа к API всех рекламных платформ и загрузки этих данных в наше хранилище. Поскольку эти источники данных загружаются в формате источника (т.е. столбцы и таблицы именуются API платформы, а не нами), вам необходимо преобразовать их, чтобы получить согласованную структуру, а затем объединить их все вместе.
| DATE_DAY | UTM_SOURCE | UTM_MEDIUM | UTM_CAMPAIGN | SPEND |
| 2020-02-01 | facebook_ads | paid_social | 10percentpromocode | 13 |
| 2020-02-02 | facebook_ads | paid_social | 10percentpromocode | 15 |
| 2020-02-03 | facebook_ads | paid_social | 10percentpromocode | 13 |
| 2020-02-04 | facebook_ads | paid_social | 10percentpromocode | 10 |
| 2020-02-05 | facebook_ads | paid_social | 10percentpromocode | 13 |
| 2020-02-06 | facebook_ads | paid_social | 10percentpromocode | 13 |
| 2020-02-07 | facebook_ads | paid_social | 10percentpromocode | 12 |
| 2020-02-08 | facebook_ads | paid_social | 10percentpromocode | 11 |
Важно отметить, что эти данные должны иметь те же utm параметры, что и данные вашей сессии. Таким образом, мы сможем объединить два набора данных для расчета:
- Стоимости конверсии: количество рекламных долларов, потраченных на привлечение клиента.
- Рентабельность затрат на рекламу: полученный доход/ рекламные расходы
select * from {{ ref('ad_spend') }}
),
attribution as (
select * from {{ ref('attribution_touches') }}
),
-- aggregate first as this is easier to debug / often leads to fewer fanouts
ad_spend_aggregated as (
select
date_trunc('month', date_day) as date_month,
utm_source,
sum(spend) as total_spend
from ad_spend
group by 1, 2
),
attribution_aggregated as (
select
date_trunc('month', converted_at) as date_month,
utm_source,
sum(linear_points) as attribution_points,
sum(linear_revenue) as attribution_revenue
from attribution
group by 1, 2
),
joined as (
select
*,
1.0 * nullif(total_spend, 0) / attribution_points as cost_per_acquisition,
1.0 * attribution_revenue / nullif(total_spend, 0) as return_on_advertising_spend
from attribution_aggregated
full outer join ad_spend_aggregated
using (date_month, utm_source)
)
select * from joined
order by date_month, utm_source
Это даст нам представление о данных такого рода:
| DATE_MONTH | UTM_MEDIUM | CONVERSIONS | REVENUE | TOTAL_SPEND | COST PER ACQUISTION | RETURN ON AD SPEND |
| 2020-02-01 | adwords | 1.37 | $19.11 | $47.00 | $34.33 | $0.41 |
| 2020-02-01 | bing | 0.48 | $6.43 | |||
| 2020-02-01 | direct | 10.98 | $141.43 | |||
| 2020-02-01 | facebook_ads | 5.14 | $66.43 | $312.00 | $60.67 | $0.21 |
| 2020-02-01 | 3.04 | $41.61 |
- Мы агрегировали в CTE, а затем объединили два CTE вместе - я всякий раз начинаю нервничать, когда join и агрегация происходят в одном и том же запросе (слишком большой риск разветвления!). Разделение логики сохранит ее чистоту, и ваш оптимизатор запросов будет вам благодарен.
- В дикой природе редко можно встретить full outer join! У нас он тут, чтобы гарантировать, что
- Конверсии, не связанные с рекламными расходами, отображаются в нашем результирующем наборе.
- Расходы на рекламу, не приводящие к конверсиям, по-прежнему будут отображаться в нашем результирующем наборе.
- Часто utm-атрибуты в ваших данных о расходах на рекламу не идеально соответствуют utm-параметрам ваших сессий, поэтому вам нужно будет выполнить корректировку исходных данных, чтобы этот join заработал.
- Мы делали join по дате конверсии и дате расходов на рекламу. Как объяснялось выше, это предотвращает изменение цифр после отчетного периода. Именно так большинство маркетинговых отделов решают эту проблему.
6. Предоставьте к ней доступ!
Теперь, когда у вас есть данные и несколько рабочих запросов, закомитте их в свой dbt-проект, смастерите несколько дашбордов и передайте их в руки заинтересованных сторон.Но прежде чем поделиться дашбордами с более широким кругом лиц, протестируйте их со своими стейкхолдерами, чтобы найти возможные проблемы и понять, насколько они важны, и стоит ли их как-нибудь менять. Но не дожидайтесь, пока ваша работа станет идеальной, прежде чем поделиться ею!
В зависимости от вашего инструмента бизнес-аналитики вы также можете подумать на параметрами дашборда, чтобы иметь возможность:
- переключаться между методологиями атрибуции;
- переключаться между уровнями агрегации (источник utm/средство/кампания).
Понимание ограничений этой модели
1. Позиционная атрибуция не является идеальным представлением человеческого процесса принятия решений
Человек очень сложно устроен. Есть целые области, посвященные пониманию процесса принятия решений. Таким образом, вероятно, невозможно точно ответить на вопрос; “Что привело к конверсии?” с любым инструментом, не говоря уже о каком-то SQL! Преимущество реализации этого в SQL заключается в том, что мы можем хотя бы двигаться в правильном направлении, сохраняя полный контроль над моделью.При этом стоит признать, что эту модель лучше всего себя показывает при просмотре данных в совокупности. Мы рекомендуем:
- Ограничится неделями - даже группировка по дням может стать слишком детализированной.
- Избегайте распределения долларовых расходов по клиентам: часто возникает желание начать оперировать выражениями типа “покупатель 123 обошелся мне в 12 долларов”. Но поскольку атрибуция - это несовершенная наука, мы не рекомендуем вам это делать.
2. Просмотры рекламы в этой модели не учитываются
В этом анализе есть одно упущение: просмотры рекламы (например, когда клиент видит одну из ваших реклам в Instagram, но в результате не переходит на ваш сайт). Для большинства компаний невозможно получить записи об этих просмотрах в хранилище данных, поэтому для этого вам придется полагаться на показатели, сообщаемые платформами. Всегда относитесь к ним с недоверием: в интересах рекламной платформы раздувать эти цифры настолько, насколько это возможно.3. Веб-отслеживание ненадежное
У некоторых ваших клиентов веб-отслеживание может быть заблокировано, и вы никогда не узнаете, откуда пришел этот клиент.Двое ваших клиентов могут пользоваться одним компьютером, и все их сессии могут быть связаны с недавней покупкой, совершенной кем-то одним из них. Или (что более вероятно) один пользователь может иметь сразу две учетные записи с разных устройств.
Дело в том, что веб-отслеживание ненадежно. Для некоторой части вашей клиентской базы цифры всегда будут немного неточными.
Делайте ее под себя
Хотя основные идеи моделирования атрибуции одинаковы для всех компаний, нюансы вашего бизнеса могут изменить некоторые детали того, как вы бы ее реализовали.Кастомизация под B2B
Если вы работаете в сфере B2B, использовать взаимодействия с отделом продаж вместо сессий в качестве представления “касаний” может оказаться более результативным. Это все зачастую отражено в инструменте CRM - если вы используете Salesforce, просмотрите таблицы “events” и “tasks”. Многие команды будут регистрировать стихийные звонки в tasks, а запланированные встречи в events - вместе с отделом продаж постарайтесь выяснить, как эти данные принято регистрировать в вашей компании.Вам также необходимо учитывать, что именно определяет “конверсию”, особенно когда конверсии происходят на уровне аккаунта, но взаимодействие может происходить на уровне клиента.
Индивидуальные методологии начисления баллов
Некоторые маркетинговые отделы любят внедрять свои собственные методы начисления баллов. Например, одна команда, с которой мы работали, немного модифицировала логику, отдав приоритет платным каналам:- если цикл взаимодействия с клиентом (customer’s journey) не оплачивается (является органикой), просто применяем к нему стандартные 40-20-40;
- если оплачивался, тогда, как только происходит платная сессия, все последующие неоплачиваемые каналы больше не учитываются.
Атрибуция на уровне заказа
Если вы являетесь компанией электронной коммерции, которая тратит значительную часть своих маркетинговых долларов на ретаргетинг существующих клиентов (более 1 миллиона долларов в год), возможно, имеет смысл инвестировать в создание атрибуции на уровне заказов. Вместо того, чтобы отмечать первый заказ как дату конверсии, у каждого клиента может быть несколько конверсий, и каждая конверсия имеет независимую атрибуцию. Это менее распространенный подход, чем атрибуция на уровне клиента, но мы работали с несколькими компаниями, которые сочли такое представление своих данных куда более полезным. Если вы планируете это сделать, сначала обязательно обсудите это со своим маркетинговым отделом, поскольку важно, чтобы это моделирование максимально соответствовало их представлениям о паттернах покупок клиентов.Настройте свой SQL, чтобы найти все сессии предшествующие первому заказу, а также сессии, которые были в промежутке между каждым заказом — вам нужно будет использовать оконную функцию в таблице orders, чтобы найти дату предыдущего заказа. Убедитесь, что вы используете order_id вместо вашего customer_id в операторе partition оконной функции.
select
*,
count(*) over (
partition by order_id
) as total_sessions,
row_number() over (
partition by order_id
order by sessions.started_at
) as session_number
from sessions
left join orders
using (customer_id)
where sessions.started_at <= orders.created_at
and sessions.started_at >= dateadd(days, -30, orders.created_at)
-- ensure sessions aren't counted twice
-- use a coalesce to ensure the first order isn't excluded by a NULL join
and coalesce(sessions.started_at > orders.previous_created_at, true)
Использование других источников
Сессии — не единственный способ взаимодействия пользователей с вашим брендом. Команды обычно еще добавляют данные:- реферальных опросов: «Как вы узнали о нас?»
- использования промокодов
- OOH(Out of home)-кампаний, например, телевизионная реклама и рекламные билборды.
Для источников, о которых у вас нет записей о взаимодействии (в частности, для OOH-кампаний), вам нужно будет сделать некоторые обоснованные предположения, основанные на относительном росте трафика (это часто называют моделированием всплесков, но это уже выходит за рамки этой статьи!).
Заключение:
Если вы достигнете той точки, когда вы сможете одновременно отчитываться о расходах на рекламу и конверсиях таким образом, чтобы это было полезно для вашего маркетингового отдела, дела у вас уже идут лучше, чем у большинства компаний.Затем вы достигните решающего момента, где вам придется ответить для себя на вопросы:
- Стоит ли мне уделять больше времени этой модели? Должен ли я использовать марковскую модель, смешивать прогнозируемую пожизненную ценность клиента (CLV) и учитывать другие расходы, такие как зарплаты маркетологов?
- Инвестирую ли я время в качество исходных данных? Часто самая сложная часть этого анализа — объединить расходы на рекламу и касания, поскольку UTM-кампании часто обслуживаются достаточно плохо. Могу ли я поработать с отделом маркетинга, чтобы создать более совершенные UTM-схемы?
- Или мне не стоит заостряться на этом и сконцентрировать свои силы на другой задаче? Для многих компаний ресурсы данных весьма ограничены, и инвестирование большего количества времени в эту модель происходит за счет других проектов.
Вместо этого лучше сделайте шаг назад, поймите, что вы проделали отличную работу, и двигайтесь дальше.
Спасибо Эрин Огилви, Тристану Хэнди и Джанессе Ланц за их неоценимый вклад в это руководство.
Моделирование атрибуции в маркетинге
Атрибуция в маркетинге долгое время считалась одной из самых неприятных аналитических задач. Но объединив сырые данные (raw data), SQL и dbt (Data Build Tool) ранее такая сложная задача может стать...
habr.com