Чтобы правильно оценить эффективность рекламы, важно понимать, сколько продаж приносит реклама и кампании. Сложность заключается в том, что данные по рекламе и по продажам находятся в разных системах, и их нужно свести. Для одного из своих клиентов, российского интернет-магазина строительных товаров, я построил отчет в 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, откуда забирать данные — например, из папки на вашем компьютере, куда вы будете складывать свежие выгрузки, — и на обновление отчета понадобится около пяти минут.