Кейс MediaNation и ВиРент: как внедрить сквозную аналитику и отследить трафик со всех маркетинговых каналов

Клиент

ВиРент — компания по аренде и прокату строительных инструментов для B2C и B2B, которая выросла из компании ВсеИнструменты.ру.

Проблема

Изначально бренд оценивал эффективность рекламы по ДРР — процентному соотношению затрат на рекламу к выручке, полученной от нее. Значение ДРР вычислялось на основании дохода, который передавался в системы веб-аналитики Яндекс.Метрика и Google Analytics с использованием инструмента «Электронная торговля». Эффективность размещения анализировали и по внутренним метрикам, включая CPL, коэффициент конверсии в отгрузку и другие.

ВиРент получает большую часть своих лидов через входящие звонки, которые отслеживаются через Calltouch. Однако маркетинговому отделу компании было сложно отследить связь между входящим звонком в Calltouch, источником или каналом этого звонка (например, рекламной кампании) и выручкой после закрытия заказа внутри клиентской ERP-системы 1С.

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

Все это препятствовало прозрачно оценивать эффективность рекламы. Клиент обратился с этой проблемой в MediaNation в 2022 году.

Решение

Мы предложили клиенту построить систему сквозной аналитики, которая объединила бы данные из всех рекламных каналов с данными по заказам и выручкой из 1C в единый автообновляемый отчет. В таком отчете клиент смог бы увидеть всю воронку продаж: с момента первого взаимодействия пользователя с сайтом до оплаты заказа и возврата арендованного оборудования. Все это можно было бы увидеть в разрезе источников перехода на сайт и рекламных кампаний.

Основные этапы решения задачи:

1. Определение систем из которых необходимо ежедневно забирать данные и места их хранения.
2. Проектирование схемы сквозной аналитики.
3. Завершение настройки веб-аналитики.
4. Получение доступа к базе данных ClickHouse клиента.
5. Настройка передачи сырых неагрегированных данных из необходимых источников в БД клиента через StreamMyData.
6. Фильтрация, обработка и объединение данных всех необходимых систем в единую структуру с помощью языка SQL.
7. Определение ключевых метрик для отслеживания.
8. Создание и согласование прототипа сквозного отчета.
9. Визуализация сквозного отчета в системе DataLens

Далее мы детально расскажем о каждом из них.

1. Подготовка к проекту

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

2. Аудит и донастройка веб-аналитики

Перед построением сквозной аналитики предстояло корректно настроить веб-аналитику. Сначала в качестве ключевой для отчета системы веб-аналитики выбрали Universal Analytics. Однако весной 2023 года Google оповестил пользователей, что с 1 июля 2023 года этот сервис перестанет работать, и его заменит Google Analytics 4 с совершенно другим способом сбора данных. На тот момент новая система была довольно сырой, постоянно изменялась, и не было четкого понимания, насколько надежно она будет работать. Поэтому мы выбрали более надежный вариант — Яндекс.Метрику.

3. Проектирование схемы сквозной аналитики

Далее мы в MediaNation разработали несколько вариантов схем данных для финальной структуры отчета. Первая схема получилась очень запутанной:

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

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

Мы утвердили следующую связку систем для формирования финальных данных:

1. Рекламные кабинеты.
2. Calltouch.
3. CRM.
4. 1C.

В Calltouch при входящем звонке поступал номер телефона пользователя и параметры UTM, которые позволяли определить источник визита пользователя на сайт, с которого он совершил звонок. Следовательно, данные из рекламных кабинетов объединялись с Calltouch по дате и UTM-меткам.

Далее Calltouch объединялся с CRM по дате и номеру телефона пользователя. Если пользователь после звонка оформил заказ, в CRM заводилась сделка и ей присваивался уникальный идентификатор.

Этот же идентификатор присутствовал в ERP-системе (1С). Поэтому следующим шагом данные, полученные на предыдущих шагах, мы объединили с данными 1С по идентификатору сделки. В 1С содержались подробные данные о заказах — наименования и идентификаторы товаров, количество единиц товаров, стоимость товаров, общий статус сделки, тип клиента (B2B/B2C) и пр.

Финальная согласованная c клиентом схема сквозной аналитики с ключами объединения данных выглядела так:

4. Выбор инструментов и технологий

Для хранения сырых данных из всех систем, участвующих в схеме, мы выбрали облачную базу данных ClickHouse, к которой клиент предоставил доступ. В эту базу данных мы с помощью StreamMyData стали ежедневно передавать данные из всех необходимых источников:

1. Яндекс.Директ.
2. MyTarget.
3. VK.
4. Calltouch.

Сейчас в StreamMyData доступно 15 коннекторов для источников данных, и это число постоянно растет:

А также есть возможность передавать их в базы данных ClickHouse или Google BigQuery.

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

5. Переход к основной реализации проекта: объединение данных из систем

После того, как мы удостоверились, что данные в ClickHouse есть в полном объеме, мы приступили к написанию кода на SQL для их обработки и объединения. Код работал в несколько этапов:

1) Объединение данных всех рекламных кабинетов

Мы отфильтровали и обработали статистику из Яндекс.Директ, ВКонтакте, MyTarget, и привели данные к нужным форматам. Затем данные из всех рекламных кабинетов агрегировались по-вертикали в единую структуру. Числовые показатели суммировались на уровне рекламной системы, рекламной кампании и даты.

2) Обработка данных по звонкам из Calltouch и объединение их с рекламными кабинетами

В течение дня пользователь сайта мог позвонить несколько раз, совершив при этом переходы на сайт из разных источников. Поэтому необходимо было решить, какой из звонков считать ключевым, в итоге приводящим к заказу и оплате. Мы решили атрибуцировать все дальнейшие действия пользователя к его первому звонку за день. Данные из рекламных кабинетов объединялись с Calltouch по дате и utm_campaign.

3) Объединение полученных данные с таблицами, содержащими информацию по заказам в CRM

Номер телефона пользователя, помимо Calltouch, хранится в CRM-системе клиента. Это давало нам возможность объединить данные Calltouch и CRM по соответствующему ключу.

4) Объединение с данными о статусах и суммах заказов

В CRM клиента не содержались данные о том, какие товары и на какую сумму были заказаны пользователем. Эта информация хранилась в 1С. Но в CRM, если пользователь после лида совершал заказ, ему присваивался уникальный идентификатор заказа, который существовал также в 1С. По этому идентификатору мы обогащали данные, полученные на предыдущих шагах статусами заказов, их содержанием, ценами и количеством единиц товара и типом клиента (B2B или B2C)

Подобным образом мы составили несколько SQL-запросов для визуализации данных на разных уровнях.

6. Визуализация данных сквозного отчета в DataLens

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

1. Расходы на рекламу.
2. Клики, показы, СРС, CTR.
3. Общее количество звонков, количество звонков уникальных пользователей.
4. Количество лидов.
5. Показатели эффективности — CPA, CPL, CPO, ДРР.
6. Самые популярные и прибыльные позиции товаров.
7. Количество заказов в разрезе типа клиента (B2B или B2C) и его статуса (Оформлен/Отгружен/Отменен/Без статуса).

На стороне клиента информация о заказах регулярно актуализируется, например, корректируется сумма заказов или меняется их статус. Данные в отчете автоматически меняются соответствующим образом.

Все эти данные можно посмотреть как в качестве сводных показателей, так и в динамике по времени или разбивке по интересующим параметрам. А также в разрезе разных уровней:

1. Общая эффективность бизнеса.
2. Эффективность по источникам / рекламным кампаниям / ключевым словам.

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

Лист № 1. Сводка по всем каналам

Лист № 2. Данные по источникам

Лист № 3. Эффективность рекламных кампаний

Лист№ 4. Эффективность товаров и категорий

Лист № 5. Эффективность ключевых слов

Результат

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