Сквозная отчётность рекламы до продаж в Битрикс
Битрикс предлагает широкие возможности для управления бизнес-процессами, а наличие интегрированной отчётности позволяет объединить данные о рекламных активностях с показателями продаж.
В статье покажу, как может быть построена сквозная отчётность рекламы до продаж в Битрикс, а также как связать рекламные кампании с заявками из CRM.
Задача
Запущена контекстная реклама в Гугл Эдс, ведущая на сайт компании. Заявки с форм, почты и телефонные звонки падают в CRM Битрикса с различными статусами.
Необходимо оценивать эффективность кампаний до конечных статусов в Битриксе.
Чтобы реализовать данный отчёт, будем использовать Excel с надстройкой Power Query и Power Pivot.
Исходные данные
Итак, у нас имеется:
- реклама в Гугл;
- CRM Битрикс 24
Я специально максимально упростил кол-во источников данных, чтобы вам было более понятно. По-хорошему обычно источников больше. Например, Директ, Майтаргет, Google Analytics, Склад и т.д.
Что происходит в Битриксе
Как я писал выше, заявки с форм, корзины, почты и телефонные звонки падают в CRM Битрикса с различными статусами. CRM связана с 1С. Когда происходит оплата товара, сделка получает статус «Оплачено».
Перед финальным статусом, есть ещё один статус «Подтверждено». Он говорит о том, что клиент согласовал оплату, договор подписан, но деньги пока не поступили.
Выгружаем данные
Из Google Ads нам нужен отчёт с кампаниями, их ID и суммами затрат. Чтобы не строить новый отчёт, просто возьмём стандартный по кампаниям и добавим идентификаторы.
Выбираем отчёт в Google Ads в формате .csv.
Из Битрикса выгружаем данные по сделкам с полями UTM-меток, это utm_source, utm_medium, utm_campaign.
Таким образом, мы получили два файла Excel.
Загружаем отчёты в Excel
Теперь необходимо загрузить их в Excel. Открываем файл из CRM и преобразуем данные в табличный вид. Для этого используем формулы ctrl+A и ctrl+T.
Подготовленную таблицу, переносим в Power Query. Для этого выделяем таблицу и жмём загрузить данные из таблицы или диапазона.
Открывается окно редактора Power Query, где мы жмём «Закрыть и загрузить», но при этом снимаем галки с создания дополнительного листа и ставим на добавление в модель данных.
То же самое делаем с данными из второго файла Excel:
- копируем из файла и вставляем отдельным листом в файле с первой таблицей из CRM;
- преобразуем их в таблицу;
- загружаем в редактор Power Query (тут может потребоваться проверка формата столбцов, например, столбцы со значениями могут иметь текстовый формат. Проверяем, чтобы такого не было, иначе в дальнейшем возникнет ошибка);
- Сохраняем и загружаем с галочкой на добавление в модель данных.
Схематично это выглядит так:
Сам Excel выглядит следующим образом:
Связка данных
Теперь нужно связать две таблицы. Поскольку в данных из Гугл Эдс мы не используем даты и список кампаний у нас идёт без повторений, связывать таблицы можно без каких-либо справочников кампаний.
Для этого идём в модель данных.
И выбираем связи таблиц.
Сейчас таблицы не связаны, поэтому мы делаем связку по ID кампаний. Просто перетягиваем одно поле на другое.
Этого достаточно, ничего не нужно сохранять и можно выходить из этого окна простым нажатием на крестик.
Сводный отчёт
Создаём отдельный лист в Excel и вставляем сводную таблицу через меню Вставка → Сводная таблица.
В итоге получаем отчёт по эффективности каждой рекламной кампании до заявок. В зависимости от статуса из таблицы CRM можно сделать фильтр по успешным сделкам или по оплаченным.
Данные можно крутить как угодно. Например, скопировать лист с таблицей и вывести причины отвала заявок и сколько на этом потеряли в деньгах.
Кастомные значения
Вы можете увидеть в сводных таблицах на скрине данные, которых не было в исходных таблицах, это количество заявок, сумма заказа, стоимость заявки.
Всё это вычисляемые значения. Делаются они в модели данных.
Заключение
Таким образом, в статье рассказал, как строится сквозная отчётность рекламы до продаж в Битрикс. Такой отчёт может повторить каждый. А если вы хотите быстрее развиваться в маркетинге, рекомендую ознакомиться с моей статьёй:
А если заинтересованы в услуге по сквозной аналитике, пишите мне:
