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

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

Зачем оценивать эффективность рекламы до продаж

За тем, чтобы понимать какая фраза, группа или кампания приводит наибольшее количество продаж крепежа. Обладая такими знаниями можно управлять их количеством и качеством.

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

Варианты оценки эффективности рекламы до продаж

Есть разные способы, наилучший из них — это выгрузка данных из CRM систем, рекламных каналов, 1С, систем коллтрекинга и прочих источников в единую базу данных и последующее построение на их основе отчётов. Для визуализации можно использовать самые популярные инструменты вроде Power BI, Google Data Studio или всем привычный Excel с надстройкой Power Query.

Power BI, Google Data Studio — это комплексные программные системы анализа данных и их визуализации.

Описанный вариант относительно дорогой и требует наличие подрядчика, либо специалиста в штате.

В данной статье я хочу описать ручной способ построения такой отчетности в Excel без сторонней помощи.

Подготовительная работа

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

Во-первых, все рекламные объявления должны быть размечены UTM метками. Как минимум, такие параметры, как utm_source, utm_campaign с уникальным названием кампании, campaign_id, utm_medium, utm_keyword. По одной из них мы будем связывать результаты в рекламном кабинете с данными о продажах из CRM, в нашем примере это ID рекламных кампаний.

UTM-метка — специализированный параметр в URL, используемый маркетологами для отслеживания рекламных кампаний в сети Интернет.

Во-вторых, заявки сайта при попадании в вашу CRM, должны сохранять utm метки в поле заявки. В нашем случае это ID рекламных кампаний.

В-третьих ваш Excel должен иметь надстройку Power Query. 

Power Query — это технология подключения к данным в Excel, с помощью которой можно обнаруживать, подключать, объединять и уточнять данные из различных источников для последующего анализа.

Это необходимый минимум.

Подготовка данных

Создаём обычный документ Excel и в него на разные листы вставляем данные из рекламных кабинетов и CRM. Типы данных можно выбирать в соответствии со скриншотом.

Рис. Данные из Мастера отчётов в Яндекс Директе

Рис. Данные из CRM

Помимо данных из рекламы нужно создать справочник id кампаний и их названий, по которому выгрузки из CRM и рекламных кабинетов будут связаны в единый отчёт. И календарь (делается встроенным функционалом Excel)

Рис. Справочник названий и ID кампаний

Преобразуем каждую таблицу данных в тип “Таблицы”.

Обработка данных

Теперь необходимо каждую из таблиц добавить в Power Query для последующего анализа данных. Выделяем таблицу и на вкладке Данные выбираем пункт “Получить данные из таблицы”. Проверяем формат каждого столбца, например зачастую валюта выводится обычным текстом. Поэтому меняем его на ₽ и добавляем все данные в модель данных.

Рис. Импорт данных

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

Осталось не так много. Необходимо связать данные из рекламных кабинетов и из CRM. Связывать их мы будем с помощью справочника названий кампаний и ID созданного раньше и Календаря.

Рис. Связь данных по Дате и ID кампаний

Визуализация данных

Теперь достаточно в Excel добавить сводную таблицу на основе модели данных. Просто жмем вставить “Сводную таблицу”. В строки выводим даты (числа, недели, месяца), статусы из CRM и названия кампаний, в значения показы, клики, расходы и прочие рекламные показатели по необходимости.

Или можно провалится до названий кампаний и посмотреть эффективность каждой из них до продаж.

Рис. Результаты по рекламным кампаниям до продаж

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

Учёт зарплаты

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

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

Заключение

Коллеги, если вы используете рекламу в интернете, не спешите делать поверхностные выводы об её эффективности. Современные инструменты позволяют подводить итоги наиболее точно.