Excel для маркетингового анализа: сводные таблицы и формулы

Выгрузка из Яндекс Директа за месяц — это, как правило, файл на 3–8 тысяч строк с показами, кликами, расходом и конверсиями по каждой группе объявлений. Открыть его и «на глаз» понять, какая кампания съедает бюджет впустую, а какая приносит клиентов по 340 ₽ вместо 900 ₽ — невозможно. Именно для этого нужен Excel для маркетингового анализа: не как замена Яндекс Метрике или DataLens, а как рабочий инструмент, которым можно свести разрозненные цифры в один отчёт за 20–40 минут.

Я веду контекстную рекламу и настраиваю сквозную аналитику для клиентов, и почти каждую неделю пересчитываю ROMI и CPA по кампаниям именно в Excel — потому что для быстрой проверки гипотезы разворачивать полноценный дашборд в DataLens избыточно, а Excel открывается за три секунды и даёт результат сразу. В этой статье — рабочий набор инструментов: сводные таблицы, формулы для расчёта эффективности рекламы, ВПР и СУММЕСЛИМН для объединения данных из разных источников, и честный разговор о том, где Excel уже не справляется.

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

Excel для маркетингового анализа: что это и когда без него не обойтись

Excel для маркетингового анализа — это использование сводных таблиц, формул и условного форматирования для обработки данных рекламных кампаний: расчёта CTR, CPC, CPA, ROMI, сравнения площадок и периодов, поиска аномалий в расходе бюджета. По сути, это промежуточный слой между «сырой» выгрузкой из рекламного кабинета и готовым решением — продолжать кампанию, остановить объявление, перераспределить бюджет.

На практике Excel закрывает три задачи, которые плохо решаются напрямую в интерфейсе Директа или Метрики:

• Свод данных из нескольких источников — Директ, Метрика, CRM, звонки — в одну таблицу для расчёта сквозных метрик.
• Быстрые «what-if» расчёты: что будет с ROMI, если снизить CPC на 15%, или как изменится конверсия при повышении цены заявки.
• Разовые срезы, под которые не стоит настраивать отдельный дашборд — например, сравнение эффективности двух акций за квартал.

Если задача повторяется из месяца в месяц и данных становится больше — это сигнал переходить на автоматизированную аналитику, об этом отдельно ниже. Но для 80% рабочих задач маркетолога связка «выгрузка + сводная таблица + пара формул» решает вопрос быстрее любого BI-инструмента.

Какие данные из Директа и Метрики стоит выгружать в Excel

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

Базовый набор колонок, который я использую в 90% отчётов:

• Дата (или неделя/месяц — зависит от глубины анализа);
• Название кампании и группы объявлений;
• Показы, клики, расход;
• Конверсии — из Метрики или CRM, по целям;
• UTM-метки — source, medium, campaign — если данные сводятся из нескольких систем.

Данные по расходу и кликам выгружаются напрямую из статистики Директа в формате XLSX или CSV. Данные по конверсиям — из Яндекс Метрики для анализа рекламы: отчёт «Мастер отчётов» с разбивкой по UTM-метке campaign даёт ровно то, что нужно для склейки с Директом. Если рекламных источников несколько и данные собираются регулярно, есть смысл настроить выгрузку через API Яндекс Метрики — тогда Excel получает готовый файл без ручного экспорта каждый раз.

Отдельный момент — колл-трекинг. Если значимая доля заявок приходит по телефону, без данных о звонках расчёт CPA будет занижать эффективность кампаний, где клиенты предпочитают звонить, а не заполнять форму. Здесь пригодится связка с коллтрекингом и сквозной аналитикой — звонки экспортируются отдельным файлом и подключаются к общей таблице через ВПР по UTM-метке или номеру заявки.

Сводные таблицы: как за 20 минут собрать отчёт по рекламным кампаниям

Сводная таблица — основной инструмент маркетингового анализа в Excel. Она превращает выгрузку из 4000 строк в компактный отчёт по 8–12 кампаниям без единой формулы. Порядок действий стандартный: выделяем диапазон с данными, «Вставка» → «Сводная таблица», в область строк тащим «Кампания», в область значений — «Расход», «Клики», «Конверсии».

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

Формулы для расчёта CTR, CPA, ROMI и других метрик рекламы

Сводная таблица показывает сырые цифры — показы, клики, расход, конверсии. Дальше нужны формулы, чтобы получить относительные метрики, по которым принимаются решения. Вот набор, которым я пользуюсь чаще всего:

Обратите внимание на формулу CPA: без обёртки в ЕСЛИ она возвращает ошибку деления на ноль в строках без конверсий, и вся сводная таблица «сыпется». Эта мелочь занимает секунду, но экономит время на отладке отчёта позже.

Полный список метрик, которые стоит считать регулярно — включая LTV, ARPU и долю повторных продаж — я разбирал отдельно в статье про маркетинговые метрики. Для расчёта ROMI по формуле выше нужны данные о доходе с заявки, которые обычно берутся из CRM — как их корректно связать с рекламными расходами, разберём в следующем разделе.

Как связать расходы на рекламу с доходом из CRM

Расход и конверсии из Директа — это только половина картины. Заявка ещё не деньги: часть лидов отваливается на этапе звонка, часть превращается в реальные сделки с разной суммой чека. Чтобы посчитать ROMI честно, нужно подтянуть в ту же таблицу данные о продажах из CRM.

Практическая схема, которая работает почти в любой CRM (amoCRM, Bitrix24, свой Excel-реестр сделок):

1. Выгрузить из CRM таблицу сделок с полями: дата, источник/utm_campaign, статус, сумма сделки.
2. В отчёте по рекламе и в выгрузке из CRM должен быть общий ключ — чаще всего это значение utm_campaign или ID кампании, переданный через параметр отслеживания.
3. Свести обе таблицы через ВПР или сводную таблицу по этому ключу, получив для каждой кампании сумму дохода из закрытых сделок.
4. Подставить полученный доход в формулу ROMI из таблицы выше.

Главная сложность здесь не формулы, а дисциплина в разметке UTM-меток на старте — если кампании называются по-разному в Директе и в CRM, автоматическая сверка не сложится, и придётся сопоставлять кампании вручную каждую неделю. Разметку ссылок и то, какие параметры обязательны для сквозной аналитики, я подробно разбирал в статье про UTM-метки — рекомендую свериться с ней перед тем, как выстраивать связку с CRM, чтобы не переделывать отчёт заново через месяц.

Если сделок мало и сводить их вручную — не проблема, отдельный ВПР не нужен: достаточно раз в неделю дополнить таблицу расходов колонкой «доход» вручную по данным из CRM. Автоматизация через ВПР оправдана, когда счёт сделок идёт на десятки в неделю и ручная сверка занимает больше часа.

Типичные ошибки при построении отчёта в Excel

За несколько лет работы с такими отчётами я собрал список ошибок, которые встречаются почти у всех, кто делает это впервые:

Отдельно стоит сказать про автоматизацию: если отчёт нужен ежедневно и вручную скачивать данные из API Директа надоело, можно настроить автоматическую выгрузку через Google Таблицы и коннекторы — это снимает половину рутины, описанной выше. Но даже при автоматической выгрузке логика метрик и формул остаётся той же, что описана в этой статье.