Строим аналитику для data-driven маркетинга сложных услуг

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

Задачи

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

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

Решение

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

Мы выбрали линейную модель атрибуции по двум причинам:

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

Чтобы построить модель атрибуции, изначально необходимо выбрать хранилище данных, в котором будут собраны сырые данные из системы веб-аналитики, CRM-системы и статистика из рекламных кабинетов.

Инструменты

Самое важное — подобрать удобный набор инструментов. Чтобы построить data-driven аналитику, необходимо три типа инструментов:

хранилище данных;
ETL;
визуализация (построение отчетности).

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

Выбор хранилища данных

Важно помнить, что нам необходима аналитическая база данных. Мы выбирали между BigQuery и ClickHouse. Например, Azure SQL Database для таких задач не подходит.

Остановились на ClickHouse, потому что:

Дружелюбный синтаксис запросов, который адаптирован под работу с веб-логами.
Работает быстрее BigQuery.
Удобно отдает данные в Power BI: доступен по ссылке и обновляется в облаке без дополнительных коннекторов.
Данные могут храниться на корпоративном сервере, что обеспечивает конфиденциальность и сохранность данных.

ClickHouse быстро устанавливается на ОС Ubuntu. Специально для SEMconf подготовили видеоинструкцию.

ETL

ETL — это продукты, которые содержат коннекторы к различным сервисам, базам данных и прочим системам. Это некий Zapier, но заточенный на работу с базами данных.

В этом проекте мы строили аналитику вокруг данных Google Analytics, а в качестве CRM-системы у клиента был внедрен Salesforce.

Мы использовали Renta Marketing ETL для данных, но вы можете найти множество альтернативных решений, достаточно погуглить запросом ETL tools. Например, есть еще Fivetran или Alooma.

Renta автоматически обновляет данные в ClickHouse и отвечает за актуальность данных двух таблиц:

Таблицу со статусами лидов из Salesforce. Она содержит clientId клиента, статус заявки, дату создания и дату конвертации лида.
Стриминг сырых данных Google Analytics с автоматическим объединением данных из рекламных систем, так называемая raw data. Со структурой таблицы можно в документе.

Теперь, когда в рамках одной базы данных у нас хранятся данные Google Analytics и Salesforce, мы можем их объединить при помощи SQL-запроса и рассчитать линейную модель атрибуции.

Расчет линейной модели атрибуции в ClickHouse

При построении атрибуции мы выдвинули несколько требований:

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

Итоговый Select-запрос с пояснением логики работы доступен в отдельном документе.

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

В результате каждый канал получил по 0,33 ценности.

В итоге, кроме результатов атрибуции, запрос возвращает следующие данные:

данные по всем сессиям из Google Analytics, в том числе без конверсий, включая IP-адреса пользователей, регион или тип устройства;
расходы по всем рекламным каналам;
количество качественных лидов в срезе по рекламным каналам с учетом линейной модели атрибуции.

Это удобно, так как в Power BI не нужно подтягивать дополнительные таблицы и строить связи. Весь датасет собран в рамках одной таблицы.

Если вы ранее не работали с базами данных, то я рекомендую найти человека, который бы отвечал на ваши вопросы во время обучения SQL. Обычно обучения в течение месяца хватает, чтобы самостоятельно приступить к написанию простых запросов. Мне повезло встретить в сообществе ClickHouse в Telegram Владимира Мюге из Яндекса.Маркета.

Последним шагом — нужно подтянуть данные из ClickHouse в Power BI и построить отчеты.

Интеграция с Power BI

Помним, что ClickHouse может отдавать данные по ссылке. Для этого в Power BI необходимо в качестве источника выбрать Web и указать путь к вашему серверу ClickHouse. Хорошая инструкция есть в блоге Renta.

Из фишек: если к ClickHouse подвязать домен, то для обновления отчетов в онлайне не потребуется установка gateway.

В итоге мы разработали отчеты на двух уровнях.

На уровне рекламных кабинетов

Определяется эффективность рекламных инструментов: поисковая реклама, РСЯ, ремаркетинг.

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

На уровне рекламных кампаний

С отчетами работают PPC-специалисты, которым доступны эффективность всех рекламных кампаний вплоть до ключевых слов.

Несколько советов

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

Если у вас остались вопросы, то с удовольствием отвечу на них в комментариях.