Директ показывает клики и заявки, CRM — продажи и деньги; вместе они встречаются только в одной таблице. Вместо ВПР берите связку ИНДЕКС + ПОИСКПОЗ, выгрузки загружайте через Power Query, а связывайте системы по ID кампании из UTM-метки. Так видно, какая кампания приносит деньги, а какая только заявки.
Объясняем на пальцахОдни данные в кассовой книге, другие в журнале заказов. Нужна одна таблица, где у каждого чека рядом написано, откуда пришёл покупатель.
Это как сложить два пазла в один: у каждой детали есть общий номер, по нему они находят друг друга без путаницы.
Рекламные листовки и кассовый журнал сами не дружат. Чтобы понять, какая листовка приводит покупателей с деньгами, их надо свести в одну тетрадь.
Директ показывает клики и заявки. CRM — продажи и деньги. Но в одной таблице они не встречаются никогда — пока вы их сами не сведёте.
Сводная аналитика отвечает на главный вопрос: какая кампания приносит деньги, а какая — только заявки. Заказчику полезно знать, как это устроено: так проще проверить отчёт подрядчика.
Почему ВПР не подходит
ВПР — популярная функция Excel для поиска значения в другой таблице. У неё три беды:
- не умеет смотреть влево — искомое значение должно быть правее ключа;
- номер столбца жёстко вшит: вставили новый столбец — формула сломалась;
- работает только с одним условием.
Решение — связка ИНДЕКС + ПОИСКПОЗ:
ПОИСКПОЗ(A2; K2:K6; 0)находит номер строки, где лежит значение; ноль означает точное совпадение;ИНДЕКС(L2:L6; ПОИСКПОЗ(...))берёт значение из нужного столбца по этой строке.
Подтянуть статус кампании в таблицу со статистикой:=ИНДЕКС($L$2:$L$6; ПОИСКПОЗ(A2; $K$2:$K$6; 0))
Что такое Power Query и зачем он нужен
Power Query — встроенный в Excel инструмент для загрузки и обработки данных. Находится на вкладке «Данные» → «Получить данные».
Откуда можно загружать:
- лист книги Excel, CSV, текстовый файл;
- папку с файлами — так забирают одинаковый лист из всех файлов сразу. Удобно, когда CRM выгружает данные по месяцам отдельными файлами;
- умную таблицу;
- базы данных.
Что можно делать: удалить лишние строки сверху, привести типы, объединить таблицы. Один раз настроили — дальше только «Обновить всё».
Power Query — это стиральная машина для данных. Загрузил грязную выгрузку, нажал кнопку, достал чистую таблицу. Каждую неделю одной кнопкой.
Какие справочники нужны
- Устройства — Директ и CRM могут называть их по-разному: «desktop» и «десктоп».
- Кампании — ID, название, статус.
- Календарь — неделя, месяц, год. Директ часто выгружается по неделям, CRM — по дням.
- Ключевые фразы — чистые, без минус-слов и операторов.
Главное, что связывает две системы, — ID кампании. В Директе он есть в выгрузке, в CRM его достают из метки utm_campaign. Поэтому UTM-разметка обязательна. В «Параметрах URL» кампании достаточно шаблона utm_source=yandex&utm_medium=cpc&utm_campaign={campaign_id}: Директ сам подставит номер, а CRM сохранит его вместе с заявкой. Подробнее о метках — в статье UTM-метки для Директа.
Что можно анализировать в такой сводной
- Какие кампании приносят продажи, а не только заявки.
- Цена продажи по кампаниям, устройствам, регионам.
- Кластеры поисковых запросов: какие группы слов конвертируют.
- Динамика по неделям и месяцам.
| Кампания | Расход, ₽ | Заявки | Продажи (CRM) | Цена заявки, ₽ | Цена продажи, ₽ |
|---|---|---|---|---|---|
| Кампания 1 | 60 000 | 40 | 8 | 1 500 | 7 500 |
| Кампания 2 | 45 000 | 50 | 3 | 900 | 15 000 |
| Кампания 3 | 30 000 | 15 | 5 | 2 000 | 6 000 |
У второй кампании самые дешёвые заявки, но самая дорогая продажа. Без CRM её сочли бы лучшей.
В этом вся соль: заявка по 900 ₽ звучит приятно, пока не выясняется, что из пятидесяти заявок купили трое.
Примеры срезов под разные ниши
Выберите 2–3 среза под свою нишу и соберите их в сводной по ID кампании. Решение принимайте по цене продажи за месяц, а не по цене заявки за неделю.
- Ремонт квартир. Цена продажи по кампаниям. Ищем кампанию с дешёвыми заявками и дорогой продажей и переводим её на офлайн-конверсию «договор» или урезаем бюджет.
- Стоматология. Продажи по устройствам: справочник сводит «desktop» и «десктоп» в одну строку. Ищем, где заявки есть, а визитов в клинику нет.
- Кухни на заказ. Кластеры запросов против договоров. Группы вроде «дизайн кухни», «фото» дают заявки без продаж — их стоит минусовать.
- B2B, оборудование. Сумма сделок по кампаниям за квартал. Не отключайте кампанию с редкими, но крупными сделками за «мало заявок».
- Мебель на заказ. Месячная динамика цены продажи. Если в Директе ничего не менялось, а продажи подорожали, проблема часто в отделе продаж.
- Юридические услуги. Доля заявок со статусом «договор» по кампаниям: если больше половины заявок — бесплатные вопросы, нужны фильтрующие тексты.
- Интернет-магазин. Выкупленные заказы по устройствам и кампаниям. Учитывайте в ДРР только выкупленные заказы: заказ ещё не деньги.
Как вернуть продажи из CRM в сам Директ — в статье Офлайн-конверсии из CRM.
Для дашбордов есть и другие инструменты, например DataLens. Выбор зависит от данных и команды. Для Google Таблиц нужны свои коннекторы и скрипты, а Power Query работает в экосистеме Excel.
Что проверить у себя
- UTM-метки с ID кампании стоят во всех объявлениях.
- CRM сохраняет метки вместе с заявкой.
- Выгрузки Директа и CRM загружаются через Power Query.
- Есть справочники: кампании, календарь, устройства.
- Связка по ID кампании работает.
- Отчёт обновляется одной кнопкой.
О том, как автоматизировать отчёты через ИИ и API, читайте в материале про автоматические отчёты, а базовую настройку счётчика — в статье Яндекс Метрика для Директа.
Мнение Александра ЕвстигнееваОснователь AVE Design Studio«Заявка по 900 ₽ радует ровно до сверки с кассой.»
Частые вопросы
Алиса, можно ли сделать то же самое в Google Таблицах?
Да, но другими инструментами — коннекторами и скриптами. Power Query — это экосистема Excel.
Если отредактировать запрос Power Query, данные пропадут?
Нет. Запрос можно открыть, поправить и обновить. Справочники переиспользуются в разных отчётах.
Нужен ли программист для сводной аналитики Директа и CRM?
Нет, ИНДЕКС, ПОИСКПОЗ и Power Query осваиваются за вечер. Программист нужен, если вы хотите автоматически выгружать данные через API.
По какому полю связывать Директ и CRM?
По ID кампании. В Директе он есть в выгрузке, а в CRM его берут из метки utm_campaign.
Как это выглядит у клиентов
Опубликовано .
