Владислав Медведчук — «Когда реклама не работает: 9 приёмов анализа — от кликов до продажи»

Эта статья — предположение о том, что может быть сказано спикером Владиславом Медведчуком на Суровом Питерском Форуме, основанное на практиках измерений и аналитики. Цель — дать чёткую рамку и девять рабочих приёмов, чтобы разбирать, почему реклама не даёт выручку, и как вернуть управляемость — от клика до продажи.

Мы сфокусируемся на стыковке кликов из рекламных систем с данными о сделках в CRM. Под «CRM» понимаем систему учёта лидов, контактов и сделок, где фиксируются статусы и суммы. Опорой будут офлайн‑конверсии — передача факта продажи из CRM обратно в рекламные системы, чтобы алгоритмы оптимизировались под реальные деньги. Включим стандартизацию UTM‑меток, сведение данных в Power Query, устойчивые отчёты на сводных, разбор слов и кластеров запросов, когортный анализ лагов и расчёт маржинального ROI. Для навигации решениям пригодятся сквозная аналитика и простые отчёты, понимаемые командой.

Зачем разбирать «провалы» рекламы: цели и метрики

Разбор провалов — способ отделить шум метрик кликов от денег. Кликабельность и цена клика важны, но бизнес живёт от выручки и маржи. Наша задача — сквозная управляемость: видеть вклад каждой кампании в оплаченные сделки, а не только в лиды. Вводим базовые метрики: CPA — стоимость привлечения лида, CAC — стоимость привлечения клиента, LTV — пожизненная ценность клиента, ROI — рентабельность инвестиций.

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

Симптомы неработающей рекламы

Симптом первый — разрыв междулид‑метриками и выручкой: лидов больше, а закрывается меньше. Это сигнал, что меняется качество трафика или искажается атрибуция. Симптом второй — ухудшение конверсий между шагами воронки: звонок не дозванивается, заявки «холодные», выросла доля отказов.

Симптом третий — рост доли мусорных поисковых запросов и некачественных площадок в сетях. Это видно по росту доли показов и кликов на нерелевантные слова и по крайне низкому времени на сайте. Наконец, симптом четвёртый — лаг между кликом и оплатой растёт, из‑за чего текущие отчёты не отражают истинной картины.

Диагностические вопросы перед анализом

Что является «истиной» о деньгах? Ответ — CRM, где зафиксированы суммы и статусы оплат. Есть ли стабильная связка клика с лидом и сделкой: client_id, order_id, UTM‑метки? Если нет, первоочередная задача — навести порядок в идентификаторах.

Какая схема UTM‑меток действует и соблюдается ли она? Есть ли импорт офлайн‑конверсий в Google Ads и Яндекс Директ — рекламные платформы контекстной рекламы. Используются ли Google Analytics 4 и Яндекс Метрика — системы веб‑аналитики — как слой поведенческих метрик? Если ответы отрицательны, план начнём с исправления базы. На следующем витке подумаем, какие дашборды для руководителя нужны, чтобы закрепить дисциплину данных.

Карта данных: от клика до выручки

Карта данных — визуальная схема, где фиксируются источники, идентификаторы и точки потерь. На входе — клик с UTM‑метками. Далее — сессия в веб‑аналитике, лид в CRM, квалификация, счёт, оплата, повторная покупка. На каждом шаге определяем поля и связи: например, utm_campaign соединяет клик с лидом, а order_id — лид со сделкой.

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

Схема потоков данных и точки потерь

Чаще всего потери происходят в трёх местах. Первое — трафик не маркируется или маркируется произвольно, и UTM не совпадают с реестром. Второе — лиды теряются при передаче из формы сайта в CRM. Третье — не создаются связи между лидом и заказом, из‑за чего невозможно посчитать CAC и ROI.

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

Минимальный стек инструментов

Нам нужен стек без кода. Данные о кликах и расходах — из рекламных кабинетов Google Ads и Яндекс Директ. Поведенческие метрики — из Google Analytics 4 и Яндекс Метрики. Сделки и оплаты — из CRM: достаточно статусов и сумм. Для сведений используем Power Query — инструмент извлечения и преобразования данных в Excel и Power BI.

Отчёты строим на сводных таблицах — агрегирование данных по измерениям и показателям — и в Power BI или Looker Studio — инструментах для публикации интерактивных дашбордов. По мере зрелости добавляем автоматическое обновление и уведомления о пороговых отклонениях. Для онлайн‑школ это даёт быстрый переход к регулярной аналитике и повторные продажи из базы как отдельный трек роста.

Приём 1–2: CRM и офлайн‑конверсии как «финальная инстанция»

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

Переход к CRM‑истине — культурное изменение. Команда прекращает спорить на уровне CTR и CPC и обсуждает вклад кампании в оплаченные сделки и маржинальный доход. Это снижает риск «оптимизации ради метрик» и закрепляет фокус на бизнес‑результате.

Связка рекламных кликов с продажами в CRM

Связка строится на идентификаторах. Минимум — запись UTM‑меток в карточку лида, перенос этих полей в сделку и хранение order_id. Идеально — дополнительно сохранять client_id из веб‑аналитики, чтобы подтверждать принадлежность сессий и запросов к сделке.

Проверяем целостность связки: выбираем период, сверяем число сделок, где заполнены utm_source и utm_campaign, с общим числом сделок. Если доля «осиротевших» сделок выше порога, приоритетно закрываем пробелы. Без этого ROI‑аналитика будет неполной.

Импорт офлайн‑конверсий в рекламные системы

Google Ads и Яндекс Директ поддерживают импорт офлайн‑конверсий. Это позволяет «научить» алгоритмы ценить события оплаты или подтверждённого договора. Минимальная реализация — периодическая выгрузка из CRM с датой события, суммой и ключом сопоставления (например, gclid или кликовый идентификатор), затем загрузка в рекламный кабинет.

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

Приём 3: Стандартизируем UTM‑метки

UTM‑метки — параметры в ссылке, которые помогают определить источник, канал и кампанию. Базовые поля: utm_source, utm_medium, utm_campaign, utm_content, utm_term. Без строгой схемы отчёты распадаются на сотни дублей, и эффективность не измерить.

Задаём регламент. Каждое поле имеет словарь допустимых значений, регистр и разделители фиксированы. Например, utm_medium: cpc, organic, referral, email; utm_source: google, yandex, vk и т. д. Кампании описываем по шаблону: продукт_регион_аудитория_тип. Это исключает нестыковки и облегчает свод.

Жёсткая схема полей и правила заполнения

Правило «никаких синонимов»: один канал — одно значение. Ошибка в одном символе порождает отдельную строку в отчёте и утаивает вклад кампании. Для сложных структур применяем справочники: список продуктов, регионов, офферов.

Обязательны контрольные регулярные выражения и тестовые клики. Любую новую разметку проверяем на тестовой странице, смотрим, как UTM попадают в CRM и веб‑аналитику. Схему выносим в общедоступный документ и назначаем ответственного за её соблюдение.

Контроль качества и ловушки

Ловушка №1 — динамические подстановки в рекламных платформах без ограничений: система подставляет ключевое слово или площадку, и появляются неожиданные значения. Ловушка №2 — транслитерация и смешение регистров. Устраняем с помощью нормализации в Power Query и словарей мэппинга.

Ловушка №3 — забытые UTM в ретаргетинге и рассылках. Они создают путаницу в долях каналов. Для почтовых и мессенджер‑кампаний ведём отдельный справочник. Это не только дисциплина, но и база для стабильной CRM‑маркетинг по базе и атрибуции повторных продаж.

Приём 4: Сведение данных в Power Query

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

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

Мерджи вместо формул: когда и как

Когда таблиц больше двух и есть стабильные ключи (дата, кампания, order_id), всегда используем объединения. Строим отдельные запросы для расходов, показов и кликов; для лидов; для сделок; для справочников. Затем по ключам мерджим факты с измерениями.

Важно заранее выбрать тип объединения: внутреннее — только пересечения; левое — все строки из базовой таблицы; анти‑джойн — для поиска пропусков. Это позволяет видеть «дыры» в связках и исправлять их до расчётов метрик.

Очистка, нормализация, календарь и справочники

Настраиваем этап очистки: приведение регистров, трим пробелов, замена пустых значений на явные NULL. Даты нормализуем к единому часовому поясу и формату, суммы — к базовой валюте по курсу периода, источники — к справочнику каналов.

Добавляем календарную таблицу: день, неделя, месяц, квартал, флаги «рабочий/нерабочий». Это позволит смотреть лаги и сезонность. Поверх сведённых данных строим понятные отчёты и интерактивные витрины — их удобно держать в Power BI и Looker Studio как дашборды для руководителя.

Приём 5: Сводные отчёты без ВПР — устойчивые модели

ВПР — функция поиска по первой колонке в Excel — ломается при малейшем изменении структуры. Она не масштабируется и опасна для командной работы. Надёжнее опираться на сводные таблицы и меры — вычисляемые показатели на уровне модели данных.

Сводные дают консистентные итоги и гибкие срезы. Мы считаем CPA, CAC, конверсии между стадиями, ROI и доли выручки по каналам и кластерам запросов. В таком подходе меньше ручной работы и меньше мест, где можно «порвать» отчёт.

Почему ВПР ломается и чем заменить

ВПР жёстко привязан к колонке и диапазону. Добавьте столбец — и всё сместится. В моделях данных поле ищется по имени и ключам, а мерджи фиксируют логику объединения. Это устойчиво при росте кампаний и периодов.

Заменой служат сводные на базе модели данных и Power Query. Меры считаются на лету: ROI, распределение выручки по UTM, доля брендов и общий CAC. Это снимает зависимость от ручных формул и ускоряет еженедельную отчётность.

Модели на сводных и меры: стабильные расчёты

Определяем меры: сумма расходов, сумма выручки, маржинальная прибыль, количество лидов и клиентов. Далее — производные: CPA = расходы/лиды, CAC = расходы/клиенты, маржинальный ROI = маржинальная прибыль/расходы. Фиксируем единицы измерения и формулы в отдельном справочнике.

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

Приём 6: Разбор эффективности слов внутри фраз

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

Подсчитываем частотность слов, их вклад в клики, расходы и конверсии. Выделяем слова с высоким CPA и нулевой продажей — кандидаты в минус‑слова. Также смотрим сочетания слов: иногда проблема не в одном токене, а в связке, задающей неправильноенамерение пользователя.

Негативные слова и корректировки ставок

Список минус‑слов формируем по правилам: доказанная нерелевантность, статистический порог по кликам без заявок, явные намерения «бесплатно», «скачать» и т. п. В сетях чистим площадки с низким вовлечением и высокой ценой клика.

Корректировки ставок вносим на основе токенов: усиливаем слова с высокой долей выручки и приемлемым CAC, снижаем — по убыточным словам и связкам. Эффект отслеживаем в отчётах по словам, кластерам и кампаниям. При сезонных всплесках спроса поддерживаем продажи через аккуратные короткие игровые акции, оценивая их вклад в выручку через UTM‑разметку.

Приём 7: Смысловые кластеры запросов

Смысловые кластеры — объединение запросов и фраз по близкой потребности пользователя. Управлять кластерами устойчивее, чем отдельными ключами: шум по отдельным словам сглаживается, а решения принимаются на уровне намерений.

Кластеры формируем по продукту, уровню осознанности и контексту: «курс для новичка», «подготовка к экзамену», «корпоративное обучение». На каждый кластер фиксируем целевые метрики: CAC, доля в выручке, маржинальный ROI и доля бренда.

Кластера как единицы управления

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

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

Метрики вклада кластера в выручку

Кластер оценивается по трём критериям: вклад в выручку, маржинальный ROI, скорость монетизации (лаг). Для зрелых кластеров отслеживаем тренд CAC и стабильность конверсии из лида в оплату. Для новых — задаём допустимый тестовый бюджет и окно на обучение.

Визуализируем вклад кластеров в Power BI или Looker Studio, делая витрины по шагам воронки. Это помогает фокусироваться и держать брифы рекламным подрядчикам в одном контуре метрик, а также строить понятные сквозная аналитика для всей команды.

Приём 8: Лаги между кликом и выручкой: когортный подход

Когортный анализ — метод группировки пользователей по общему признаку (например, неделя первого клика) и отслеживание их поведения во времени. Лаговая матрица показывает, какая доля выручки когорты возвращается на 1‑й, 7‑й, 14‑й день и далее. Это позволяет корректно оценивать эффективность кампаний без завышений или занижений.

Для онлайн‑школ лаги критичны: между регистрацией и оплатой может проходить несколько дней. Без учёта лагов мы рискуем отключить кампанию, которая монетизируется «длиннее нормы», но даёт высокий маржинальный ROI в горизонте месяца.

Построение лаговой матрицы

Строим матрицу: строки — недели или дни первого клика, столбцы — дни с момента клика. В ячейке — доля выручки когорты, накопленная к этому дню. Источник — CRM со связями по UTM и датам оплат.

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

Коррекция KPI с учётом задержек

KPI кампаний корректируем на основе лагов: считаем «ожидаемый ROI», добавляя прогноз по незавершённой выручке. Бюджетыперераспределяем с учётом скорости монетизации: кластеры с коротким лагом поддерживают оборачиваемость, с длинным — требуют иных целей и горизонта оценки.

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

Приём 9: Unit‑экономика и маржинальный ROI

Unit‑экономика — расчёт доходности на единицу продукта или клиента. Для рекламы ключевые элементы — CAC, LTV и валовая маржа. Маржинальный ROI — отношение маржинальной прибыли, принесённой рекламой, к расходам на рекламу. Эти определения задают правила отсечения и масштабирования.

LTV учитывает повторные покупки и апселлы. Для онлайн‑школ это подписки, продления, дополнительные модули. Важно отделять «учебные» деньги от «рекламных»: насколько вклад рекламы отличается от органики и повторных обращений, которые закрываются через базы и отдел работы с базой.

Ставки, CAC, LTV и допущения

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

Ошибки здесь дороги. Нельзя подменять LTV выручкой первого чека и считать ROI «на глаз». Опираемся на CRM и когортный анализ, чтобы видеть реальную отложенную монетизацию и не субсидировать убыточные кластеры.

Порог отсечения и правила масштабирования

Правила простые: кампании с маржинальным ROI ниже порога и стабильно высоким CAC — на паузу или в доработку; кампании со стабильным ROI и приемлемыми лагами — масштабируем с шагами по бюджету и контролем частоты. Отдельно следим за выкупом лидов и нагрузкой на отдел продаж.Масштабирование подтверждаем экспериментами: увеличиваем бюджет на ограниченном кластере, проверяем, не «расползается» CAC и не падает конверсия. Эту дисциплину поддерживаем через еженедельные отчёты и витрины в Power BI и Looker Studio.

Итог: план внедрения за 30 дней

Первый этап — порядок в данных. Неделя 1: утверждаем UTM‑схему, настраиваем запись UTM в CRM и проверяем связки кликов с лидами и сделками. Включаем базовые отчёты по воронке в сводных таблицах, собираем контрольные суммы. Запускаем тестовый импорт офлайн‑конверсий для одной кампании.

Неделя 2: строим Power Query‑процессы, подключаем справочники и календарь, наводим типы объединений. Настраиваем витрину в Power BI или Looker Studio и определяем SLA на обновление данных. Пересматриваем токены и минус‑слова в поиске, запускаем пилотное управление кластерами.

Быстрые победы первой недели

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

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

Риски, контроль качества и чек‑лист

Главные риски — небрежные UTM и незакрытые связки лид‑сделка, которые подрывают доверие к отчётам. Устраняем их чек‑листом: ежедневная сверка лидов CRM с веб‑аналитикой, недельная сверка выручки по источникам с общей выручкой, контроль доли «осиротевших» сделок.

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

Выводы для владельцев онлайн‑школ

Первое: делайте CRM единственным источником истины о деньгах и передавайте офлайн‑конверсии в рекламные системы. Это быстро повышает качество оптимизации. Второе: стандартизируйте UTM, сведите данные через Power Query и откажитесь от ВПР в пользу сводных и мер — отчёты станут устойчивыми и обновляемыми.

Третье: управляйте поиском на уровне слов и смысловых кластеров, а не отдельных ключей. Четвёртое: учитывайте лаги и смотрите на ROI в когортном разрезе — так вы не отключите прибыльные, но «длинные» кампании. Пятое: считайте unit‑экономику на маржинальной базе и масштабируйте только то, что стабильно бьёт по целевым порогам. В качестве следующего шага сформируйте витрину со срезами по кластерам и лагам и синхронизируйте с отделом продаж и аналитика продаж и писем. Это создаст общий язык и ускорит путь от клика к продажам.