Сводим данные по рекламе и продажам из CRM Битрикс24 в Excel

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

С чем работаем

У заказчика работает контекстная реклама в Google Ads и ведет на сайт компании. Заявки с форм, почты и телефонные звонки падают в CRM «Битрикс24» с различными статусами. Чтобы свести данные, будем использовать Microsoft Excel с надстройками Power Query и Power Pivot.

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

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

Как это выглядит в «Битрикс24»

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

Выгрузка данных

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

Перейдите в раздел «Отчеты», затем — «Стандартные отчеты», кликните на вкладку «Основной» и выберите «Кампании».

Нажмите на кнопку «Скачать». Вы увидите список из нескольких форматов — выберите вариант Excel .csv.

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

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

Загрузка в Excel

Теперь необходимо загрузить эти документы в Microsoft Excel. Открываем файл из CRM и преобразуем данные в табличный вид с помощью сочетания клавиш Ctrl+T.

После этого переносим таблицу в Power Query. Для этого выделяем ее и на листе и выбираем загрузку данных из таблицы или диапазона.

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

Настройки окна редактора Power Query по умолчанию

Необходимые настройки окна редактора Power Query

После этого нажимаем на кнопку «Закрыть и загрузить».

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

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

Схематично все эти операции выглядят так.

Данные в Excel будут отображаться следующим образом.

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

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

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

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

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

Создание сводного отчета

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

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

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

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

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

Заключение

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

Позже достаточно будет указать Power Query, откуда забирать данные — например, из папки на вашем компьютере, куда вы будете складывать свежие выгрузки, — и на обновление отчета понадобится около пяти минут.