Сквозная отчетность рекламы до продаж в Битриксе

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

Задача

Запущена контекстная реклама в Гугл Эдс, ведущая на сайт компании. Заявки с форм, почты и телефонные звонки падают в CRM Битрикса с различными статусами.

Необходимо оценивать эффективность кампаний до конечных статусов в Битриксе.

Чтобы реализовать данный отчёт, будем использовать Excel с надстройкой Power Query и Power Pivot.

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

Итак, у нас имеется:

• реклама в Гугл;
• CRM Битрикс 24

Я специально максимально упростил кол-во источников данных, чтобы вам было более понятно. По хорошему обычно источников больше, например Директ, Майтаргет, Google Analytics, Склад.

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

Что происходит в Битриксе

Как я уже говорил, заявки с форм, корзины, почты и телефонные звонки падают в CRM Битрикса с различными статусами. CRM в свою очередь связана с 1С и когда происходит оплата товара, в Битриксе сделка приобретает статус «Оплачено».

Перед финальным статусом с оплатой, есть ещё один конечный статус «Подтверждено», говорящий о том, что клиент согласовал оплату, договор подписан, но деньги пока не поступили.

Выгружаем данные

Из Google Ads нам нужен отчёт с кампаниями, их ID и суммами затрат. Чтобы не строить новый отчёт, просто возьмём стандартный по кампаниям и добавим идентификаторы. (по хорошему, нам не надо брать ничего лишнего, чтобы не задействовать лишние вычислительные мощности)

И выгружаем его в .csv

Из Битрикса выгружаем данные по сделкам с полями UTM-меток, это utm_source, utm_medium, utm_campaign

Таким образом мы получили два файла Excel.

Загрузка в Excel

Теперь необходимо загрузить их в Ексель. Открываем файл из CRM и преобразуем данные в табличный вид нажатием ctrl+T

Подготовив таблицу, переносим её в Power Query, для этого выделяем её и на листе и жмём загрузить данные из таблицы или диапазона

Открывается окно редактора Power Query, где мы жмём «Закрыть и загрузить», но при этом снимаем галки с создания дополнительного листа и ставим на добавление в модель данных.

То же самое делаем с данными из второго файла Excel:

• копируем из файла и вставляем отдельным листом в файле с первой таблицей из CRM;
• преобразуем их в таблицу;
• загружаем в редактор Power Query (тут может потребоваться проверка формата столбцов, например столбцы со значениями могут иметь текстовый формат. Проверяем, чтобы такого не было, иначе в дальнейшем возникнет ошибка);
• Сохраняем и загружаем с галкой на добавление в модель данных.

Схематично, это выглядит так.

Сам Excel выглядит следующим образом

Связка данных

На данном этапе, необходимо связать две таблицы. Поскольку в данных из Гугл Эдс мы не используем даты и список кампаний у нас идёт без повторений, связывать таблицы можно без каких-либо справочников кампаний.

Для этого идём в модель данных

И выбираем связи таблиц

Сейчас таблицы не связаны, поэтому мы делаем связку по ID кампаний. Просто перетягиваем одно поле на другое.

Этого достаточно, ничего не нужно сохранять и можно выходить из этого окна простым нажатием на крестик.

Сводный отчёт

Осталось немного и мы уже на финишной прямой. Создаём отдельный лист в Excel и вставляем сводную таблицу через меню Вставка → Сводная таблица.

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

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

Кастомные значения

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

Всё это вычисляемые значения. Делаются они в модели данных.

Заключение

Для тех, кто не знаком с Power Query, данный отчёт может показаться довольно сложным. На самом деле это не так, делается он минут за 10-15 первый раз.

В последующие разы достаточно сказать Power Query откуда забирать данные, например из папки на вашем компьютере, куда вы будете складывать свежие выгрузки и обновление отчёта вовсе займёт минут 5.