Авторская методика сборки джема в Excel (ВПР+сводные), чистка большой РК, ER от маржи, ассоциированные конверсии

Формат: один большой блок методологии в самом начале (60 мин) + 4 живых кейса. Главное открытие — впервые в курсе разобран полный pipeline сборки джемовского отчёта в осмысленный xlsx через пустой аукцион + ВПР + сводные таблицы. Это решение убирает главную боль предыдущих уроков — невозможность точно сопоставить данные джема с реальной РК.

Главный посыл: «вечное» обучение, которое работает всегда — это база (CR / CTR / стоимость клика / частотность). Что бы ни придумал WB, базовые показатели останутся. Шаблонов не существует — учусь мыслить, а не повторять чек-листы.

TL;DR

1. Главное открытие курса — pipeline кластеризации джема через пустой аукцион + ВПР + сводные: - Создаём пустой аукцион → загоняем туда фразы из джема как фикс-фразы → WB сам проставляет номер кластера → экспортируем → через ВПР по фразе подтягиваем кластер к джему → строим сводную таблицу (кластер → фразы → CR) → видим все кластеры с CR по каждой фразе в одном xlsx.
2. Дополнение — сопоставление с реальной РК. Экспортируем рабочую РК без кластеризации → вставляем рядом в xlsx → через ВПР по фразе подтягиваем CPC из РК → Стоимость заказа = CPC ÷ CR%. Всё на одном листе, никаких переключений между файлами.
3. Для тех, у кого нет джема — отчёт сравнения. В нём может быть 200-2000 ключей (vs 30 в джеме). Кривое форматирование — данные не ищутся, надо пересоздать в новом xlsx через «вставить только значения». Логика та же: пустой аукцион → кластеры → сводная → CR на месте через =конверсия_корзина × конверсия_заказ ÷ 100.
4. Чистка большой РК — лимит 1 000 минус-фраз. Если в РК 2 500 кластеров — отминусовать всё сомнительное напрямую не получится. Стратегия: создавать списки через основозадающие слова (рубашка / блузка) → в каждом списке оставлять целевое → отключать отображение → видеть «мусор» по убыванию частотности и затрат.
5. Метод «новой компании с нуля для экономии». Запустить параллельную чистую РК → сначала через MarketGuru выгрузить кластеры конкурентов где у нас дальше 200-й позиции → массово добавить в минус-фразы → потом точечно прокачивать целевые.
6. Из общей выгрузки РК — фильтр через 0.1 показов. Если ввести 1 — попадут кластеры с одним кликом тоже. Через 0.1 — только те, у которых меньше одного клика. У Павла так нашлись 2 338 кластеров без кликов, которые жгли 500 ₽ за 15 дней.
7. ER = DRR от маржи. Если маржа 40% и потратили 8% на рекламу — это 8 ÷ 40 = 20% маржи спустили. Полезный индикатор для оценки автокомпаний без знания юнит-экономики клиента.
8. Кейс Павла (рубашка изи-тренд): 2 500 кластеров, 70% полки, средний CTR 5%. Минусуются через списки «рубашка»/«блузка» — всё, что не подходит ни под один список, отметается. Для сомнительного — кластеризация конкурента через сравнение карточек и поиск по номеру кластера, а не по фразе.
9. Кейс Шухрата (детский рюкзак): CTR 7% в топ-3 при равноценном окружении = карточка некликабельна. Дизайн (глянцевая вода внизу, серый фон, рюкзак выглядит «по-взрослому») снижает CTR. Метод оценки CTR: смотрим окружение в топе, а не абсолютное значение.
10. Кейс Ирины (18+ мастурбатор): ассоциированные конверсии 1:1 к основным заказам → склейка размывает CR в 2 раза. Сначала переcклеить, оставить рабочую пару, оценить контент, и только потом анализировать ключи. Также: выкупы по фразам видны в джеме как 500% / 400% конверсии.

1. Pipeline кластеризации джема через пустой аукцион (главное открытие урока)

[02:00] Контекст: за выходные ведущий придумал способ превратить кривой джемовский отчёт в нормальный аналитический инструмент. До этого все уроки страдали тем, что данные джема (30 фраз без номеров кластеров) и данные нейромаркета (с кластерами) приходилось сводить вручную.

Главная проблема джема: в выгрузке нет номера кластера. Поэтому фразу из джема невозможно сопоставить с кластером в РК — приходится гадать.

Пошаговая методика:

Шаг 1. В джеме выгружаем стандартный отчёт «Поисковые запросы» по нужному товару за месяц (или квартал). Получаем 30 фраз с конверсиями.

Шаг 2. В нейромаркете создаём пустой аукцион (на любой товар — даже неактивный, сойдёт). Открываем его на zelenoy klavishe (он может выдать ошибку — игнорируем).

Шаг 3. В этот пустой аукцион через «массовое добавление фикс-фраз» вставляем список фраз из джема. WB автоматически сопоставляет каждой фразе номер кластера.

Шаг 4. Не кластеризуем! Это критично — если кластеризовать, экспорт даст кластеры, а нам нужны исходные фразы. Экспортируем «как есть».

Шаг 5. Из выгрузки нейромаркета берём только 2 колонки: фразы + номер кластера. Копируем в буфер.

Шаг 6. Возвращаемся в джемовский xlsx. В конце добавляем колонку «Кластера». Через «Вставить только значения» вставляем рядом фразы+кластеры из нейромаркета.

Шаг 7. ВПР по фразе. В колонке «Кластера»:

=ВПР(ячейка_фразы_джема; диапазон_фразы+кластеры_из_нейромаркета; 2; 0)

Размножаем формулу — каждой фразе джема теперь сопоставлен номер кластера.

Шаг 8. Сводная таблица. Вставка → Сводная таблица. В строки тянем «Кластер», под него тянем «Поисковой запрос». В значения: конверсия в корзину, конверсия в заказ. Сразу видим группировку фраз по кластерам.

Шаг 9. CR в сводной. Особенность — в сводной формулу размножить двойным кликом нельзя. Прописываем =B5*C5/100, перетягиваем за угол.

[10:00] Результат: xlsx, где каждая фраза джема сгруппирована по кластеру с готовым CR. Видно, какие фразы внутри одного кластера и сколько по каждой реально приходит. Зеро-кластеры (без номера) можно свернуть.

Для контроля: фразы с конверсией 100% — это, скорее всего, низкочастотники (3-5 запросов за период). Чтобы это понимать, в сводную дополнительно тянем «Количество запросов» (частотность за период).

2. Сопоставление джема с рабочей РК через второй ВПР

[12:00] Просто иметь CR по фразам мало — надо понять, по какой именно фразе из кластера идут клики в реальной РК и сколько эти клики стоят.

Шаг 1. Идём в свою рабочую РК (не пустой аукцион). Через нейромаркет экспортируем статистику без кластеризации (чтобы видеть фразы, а не кластеры).

Шаг 2. В нашем xlsx, где уже есть кластеризованные данные джема, вставляем эту выгрузку рядом — через «Вставить только значения» для чистого форматирования.

Шаг 3. ВПР по фразе → CPC. Рядом со сводной таблицей джема прописываем:

=ВПР(фраза_из_джема; диапазон_выгрузки_РК; номер_столбца_с_CPC; 0)

Размножаем — для каждой фразы из джема, по которой шли клики в РК, теперь видна стоимость клика. Все фразы без кликов остаются с #Н/Д — это нормально, они в РК просто не работают.

Шаг 4. Стоимость заказа по фразе. Рядом:

=CPC ÷ CR%

Знак % обязателен (иначе посчитает в 100 раз меньше).

[14:00] Финал: одна таблица, где: - слева сводная по кластерам с CR - справа выгрузка из РК с CPC - между ними — стоимость заказа по каждой фразе

Преимущество перед всеми предыдущими попытками: теперь мы не подставляем CR из «первой попавшейся фразы кластера». Мы точно знаем, по какой фразе из кластера WB фиксирует клики, и берём CR именно для неё. Это самый точный CR из доступных без джем-pro.

Шаг 5 (опционально). Двойной ВПР наоборот. Если хочется в выгрузке РК тоже видеть CR — добавляем колонку и через ВПР подтягиваем CR обратно из джема:

=ВПР(фраза_из_РК; диапазон_джема_с_CR; столбец_CR; 0)

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

3. Та же методика на отчёте сравнения (для тех, у кого нет джема)

[22:00] Отчёт сравнения круче джема тем, что в нём до 2 000 ключей против 30 в джеме. Но WB его экспортирует в кривом формате — данные не ищутся стандартными формулами.

Что не так: WB вставляет отчёт с особым форматированием, из-за которого ВПР, фильтры и поиск не работают.

Лайфхак: 1. Удалить заголовок в начале файла. 2. Выделить все данные. 3. Создать пустой xlsx. 4. Через «Вставить только значения» вставить туда — форматирование очистится. 5. Теперь всё работает нормально.

Pipeline тот же самый, только две модификации: - CR пишем сразу в столбце xlsx (=конверсия_корзина × конверсия_заказ ÷ 100), не в сводной — потому что отчёт сравнения короткий (всего 4 колонки) и формулу удобно прокинуть сразу. - В отчёте сравнения в каждой строке два товара (наш + конкурент) — нас интересует наш, его данные на «втором товаре».

Шаг 1. Через сравнение карточек создаём отчёт сравнения нашего товара и любого конкурента (для большего пула фраз).

Шаг 2. Скачиваем, чистим форматирование. CR считаем в столбце.

Шаг 3. Те же действия, что с джемом: пустой аукцион → массовое добавление фраз → WB присваивает кластеры → экспорт без кластеризации → ВПР по фразе → сводная по кластерам.

Шаг 4. К сводной по кластерам тянем «количество запросов» (частотность) — особенно важно, потому что в отчёте сравнения много низкочастотников с «фантомно высоким» CR. Если частотность 14 — CR 33% сразу читается как артефакт.

[38:00] Универсальность: способ работает что для авто, что для аукциона, что для джема, что для отчёта сравнения. Логика одна: пустой аукцион присваивает кластеры → ВПР сводит → сводная даёт картину.

Автор подчёркивает: «такого ещё никто не делал». Возможно WB когда-нибудь сами научатся отдавать нормальные отчёты, но пока приходится «извращаться».

4. Чистка большой РК — лимит 1 000 минус-фраз, фильтр 0.1, списки

[46:00] Кейс — кампания Павла (изи-тренд, рубашка). 2 500 кластеров. Минус-фраз уже 1 000 (лимит). CTR низкий, 70% показов в полках.

Главное ограничение WB: в РК максимум 1 000 минус-фраз. Если кластеров 2 500, отминусовать всё сомнительное в одну РК физически нельзя.

Метод 1. Фильтр «0.1» в нейромаркете. Стандартный фильтр «от 1 клика» оставит и кластеры с 1 кликом. Чтобы видеть только нулевые — вводим 0.1 (через точку, ноль целых одна десятая). Тогда показываются только кластеры, у которых меньше одного клика.

Результат у Павла: 2 338 кластеров без кликов за 15 дней, потратили вместе 500 ₽.

Метод 2. Поиск порога затрат через фильтр показов. Фильтр «показы от X»: от 50 — 10 кластеров на 100 ₽. От 30 — 23 кластера на 200 ₽. От 10 — 72 кластера на 300 ₽. От 5 — 136 кластеров на 400 ₽. Подбираем порог так, чтобы охватить максимум трат при минимуме кластеров.

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

Метод 3. Новая чистая РК с нуля. 1. Зайти в свои текущие кластеры через MarketGuru. 2. Выгрузить таблицу позиций конкурентов. 3. Через умную таблицу отсортировать по убыванию позиций. 4. Все кластеры дальше 100-200 позиции (зависит от ниши) — точно не наши. 5. Скопировать их → загнать в новую РК как минус-фразы массово. 6. Дальше точечно прокачивать целевые.

[1:08:00] Метод 4. Списки через основозадающие слова. Если 1 000 свободных минус-фраз → нельзя просто залить туда список из джема. Делаем наоборот через списки: 1. В нейромаркете создаём список «рубашка» → ищем все фразы со словом «рубашка» → массово добавляем в список. 2. Аналогично «блузка», «одежда». 3. Отключаем отображение этих списков — на экране остаются только все, что не «рубашка»/«блузка». Это мусор. 4. Просматриваем мусор по убыванию частотности и затрат.

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

[1:22:00] Метод 5. Сетяр + позиция + клики. Если приближаемся к минусованию пограничных кластеров: - Фильтр буст до топ-20 → отсортировано по частотности. - Должны быть CTR ≥ 7% при сумме частотности 1 200/мес. - Если CTR ниже 6% — кандидаты на минусы. - Если CTR от 6 до 12% — отдельно отсмотреть глазами.

5. Кейс Павла — рубашка изи-тренд, 2 500 кластеров

[1:00:00] Артикул из категории «рубашки», автоматическая кампания. Симптомы: 2 500 кластеров, 70% показов в полках, низкий CTR, при этом много нулевых кластеров.

Шаг 1. Общая статистика. Кликов мало, CTR низкий — большинство показов в полках занижают.

Шаг 2. Фильтр от 1 клика → 147 рабочих кластеров. Остальные 2 350 — балласт.

Шаг 3. Аудит — нет ли переспама в SEO? Иногда 2 500 кластеров появляются из-за того, что в SEO загнали всю мыслимую семантику. У Павла переспама нет, но в принципе это первое подозрение.

Шаг 4. Отчёт сравнения с конкурентом. Отчёт сравнения дал ~200 ключей — против 30 джема. Сразу видно потенциальные точки роста.

Шаг 5. Создание списка «целевые фразы». В нейромаркете создаём отдельный список. Идём по сводной таблице из xlsx (где видим кластеры с CR), и: - Кластер с хорошим CR при адекватной частотности — берём номер кластера, ищем в РК, добавляем в список «целевые». - Если CR 100% при частотности 14 — игнорируем как артефакт. - Если CR 17% при частотности 647 — берём, отличный кандидат.

Шаг 6. Чистка через «всё кроме целевых». В нейромаркете отключаем отображение списка «целевые» → видим только потенциальный мусор. Дальше — методы 4-5 из предыдущего раздела.

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

6. Поиск сомнительного через кластеризацию конкурента

[1:24:00] Финальный шаг для самых сомнительных кластеров — кросс-проверка через конкурента.

Сценарий: в нашей РК есть кластер с 6 ₽ CPC, по которому страшно отминусовать (вдруг полезный), но в нашем джеме его нет, и в отчёте сравнения тоже нет.

Метод: 1. Сделать отчёт сравнения с конкурентом, у которого этот кластер точно есть. 2. Применить ту же методику pipeline: пустой аукцион → присвоить кластеры → ВПР → отчёт по кластерам. 3. Взять номер кластера, который нас интересует. 4. Через ВПР искать по номеру кластера (не по фразе!) в нашей выгрузке РК. 5. Если совпадение нашлось — посмотрели CR у конкурента, приняли решение. 6. Не нашлось — отминусовываем без жалости.

Универсальное правило: если фраза не нашлась по тексту, но кластер тот же — ВПР по номеру кластера, а не по фразе. Кластеры стабильнее формулировок.

7. Кейс Шухрата — рюкзак, окружение и кликабельность

[1:40:00] Артикул — детский рюкзак от 2 до 7 лет, цена ~1 000 ₽. Симптомы: ставка 205 ₽, клик 6 ₽, CTR 2%, 85% показов в полках, средняя позиция 8.

Шаг 1. Авто-РК → доля полок 85% → мониторинг полок. Везде зелёный свет, обороты у конкурентов мелкие (700 К ₽/мес). Гипотеза «не сезон» — пик у детских рюкзаков в августе.

Шаг 2. Проверка релевантности. Открываем 3-4 карточки конкурентов, где нас показывают: - 269 ₽ за рюкзак (наш 1 000 ₽) — большой разрыв в цене, конкурент с дешёвым рюкзаком отнимает клики. - Конкуренты в пастельных тонах для девочек 7-8 лет — мы со своим «детским» рюкзаком с машинками выпадаем визуально. - Окружение нерелевантное — это объясняет низкий CTR в полках.

[1:50:00] Шаг 3. CTR в поиске = метрика окружения. Открываем статистику поисковых запросов: - В топ-3 CTR 7% — слишком мало для рюкзака. - В топ-1 — десяточка, выбивается из общего ряда.

Метод оценки CTR через окружение: смотрим окружение в выдаче, а не абсолютное значение CTR. - Если рядом 4 одинаковые карточки по той же цене → даже 7% CTR может быть нормой. - Если рядом 3 карточки заметно хуже и 1 заметно дороже → мы должны иметь 15-20% CTR.

У Шухрата: в окружении 2 карточки откровенно скучные, 1 завышенная цена, 1 «не для ребёнка». Мы должны быть фаворитом — но CTR всего 7%. Значит проблема не в окружении, а в карточке.

Шаг 4. Анализ карточки. Главное фото: - Эффект «глянцевой воды» внизу — низ рюкзака уже тёмный, в отражении сливается в визуальный мусор. - Серый фон оптимизма не добавляет. - Облачки/яркий фон были бы лучше. - Без таблички «2-7 лет» — рюкзак выглядит «для модной девушки», а не для ребёнка.

Шаг 5. Решение по РК: - Авто-РК пока не выключать — не сезон, к августу всё разгонится. - Отселить 3 самых дальних кластера (за топ-10) в аукцион — они жгут бюджет. - Поработать с дизайнером над главным фото. - В межсезонье — самовыкупы по 3-4 главным ключам, чтобы прокачать органику.

8. ER — DRR от маржинальности

[1:46:00] Введён новый показатель, который полезнее обычного DRR для оценки рекламы без знания юнит-экономики клиента.

ER (Effective Rate) = DRR от вашей маржи, не от 100%.

Формула:

ER = DRR ÷ Маржа × 100%

Примеры: - Маржа 40%, DRR 8% → 8 ÷ 40 = 20%. Спустили на рекламу пятую часть прибыли. - Маржа 40%, DRR 20% → 50% прибыли спустили. - Маржа 30%, DRR 17% → ~57% прибыли спустили (плохо). - Маржа 40%, DRR 30% → 75% прибыли в трубу.

Применение: если клиент не озвучивает юнит-экономику, но мы видим DRR, можно оценить «зоны допустимости» — DRR 8% при маржинальности 40% это норма, при маржинальности 25% уже половина прибыли.

Для AI это полезный индикатор в аудитах — если клиент закрыт по теме маржи, можно через бенчмарк по категории прикинуть ER. Особенно полезно для категорий, где маржа известна с погрешностью ±5% (ручной труд, посуда, рюкзаки).

9. Кейс Ирины — 18+ мастурбатор, ассоциированные конверсии 1:1

[2:00:00] Артикул — мужской мастурбатор, ниша 18+, цена 1 187 ₽ (771 ₽ продавец до СПП), ИП Орион. Симптомы: высокий DRR, две одновременные РК (авто+аукцион), большая часть трафика из полок.

Шаг 1. Воронка → общий CR. 7% × 28% ÷ 100 = CR 1,96%. Низковато.

Шаг 2. Авто-РК. Кликов мало, CTR низкий. В поиске всё отминусовано — значит CTR полностью складывается из полок + каталога.

[2:06:00] Шаг 3. Распределение по зонам. Не только полки (70%) — но и каталог 30%.

Каталог в авто-РК — отдельная проблема: на него вообще нельзя влиять. WB сам решает, в какой каталог поставить. Если CTR проседает из-за каталога — единственный выход выключить авто полностью.

В чистых полках без каталога у Ирины CTR неплохой. Низкий общий CTR обеспечен именно каталогом.

Шаг 4. CR полок через портрет покупателя. Заказы из карточек 14 + с главной 0 = 14 на 661 переход = CR полок 2,11% (фактически равен общему CR карточки).

18+ карточки на главной странице WB вообще не показываются (из этических соображений) — это объясняет 0 переходов с главной.

Шаг 5. Стоимость заказа из полок. - 83 клика × 2% CR = 1,66 заказа. - 311 ₽ затрат ÷ 1,66 = 187 ₽ за заказ из полок. - Цена товара 1 187 ₽, после СПП ~771 ₽ селлеру, маржа максимум 350 ₽. - Полки оставляют ~150-200 ₽ прибыли — на грани.

Шаг 6. Аукцион — поиск. Тут начинается интересное: - В статистике поиска по фразе «вагина (с английской D)» — CR 500% / 400%. Это выкупы Ирины — она сама себе подвыкупается, чтобы поднять органику. - Подтверждено в эфире: Ирина рассказала, что это правда самовыкуп. - Все остальные конверсии (которые не выкупы) — около 1-2%, как и общий CR.

Метод: фраза с CR 500% в джеме — это маркер самовыкупа. Не путать с реальной конверсией.

[2:22:00] Шаг 7. Ассоциированные конверсии 1:1. Открываем РК → метрика «Ассоциированные конверсии»: - По основному артикулу 16 заказов + 16 ассоциированных за период. - Соотношение 1:1 — критическая аномалия.

Что это значит: клиент перешёл на наш товар по рекламе → выбрал внутри склейки другой. Реальный CR этого артикула в 2 раза выше, чем фиксирует WB — потому что часть конверсий ушла «не на ту строчку».

[2:24:00] Шаг 8. Анализ склейки: - В склейке 5+ артикулов. - Активно заказывают 2. - Остальные — балласт, размывают конверсии всего. - В аукционной воронке соотношение здоровее (140 на 37) — потому что аукцион ловит более релевантные ключи, а не «всех подряд».

Решение по Ирине: 1. Сначала расклеить — оставить рабочую пару, остальные отселить в другую склейку или в отдельные карточки. 2. Подождать 1-2 недели свежей статистики на разделённой склейке. 3. Только потом анализировать ключи — сейчас данные искажены размытием. 4. Контент карточки требует переделки — главное фото не убеждает, инфографика «многострадальная», курьер с посылкой выглядит несерьёзно. «Коэффициент жёсткости 10D» ни о чём не говорит покупателю. 5. Точки роста по семантике — два больших кластера у конкурентов, по которым Ирина вообще не работает. Суммарная частотность 280 K показов/мес — больше, чем весь её текущий пул. 6. Стратегия: самовыкупы по двум упущенным ключам → рост органики → подхват полок (которые сейчас не работают из-за низкого оборота).

Универсальное правило для склеек: если в склейке N товаров, а заказывают по 1-2 + есть ассоциированные конверсии ≥ 30% — переcклейка обязательна. Размытые CR искажают всю аналитику и решения по ключам.

10. Стратегия «средние позиции» — почти не работает

[2:38:00] Частый вопрос: «мне топ не нужен, средних позиций хватит, перегретые ставки не для меня». участница подробно объясняет, почему это в большинстве ниш не работает.

Когда стратегия «средние позиции» возможна: 1. Очень маленькая ниша — переизбытка игроков нет, средняя ставка низкая, средние позиции дают выдачу без перегрева. 2. Хорошо работающие полки при маленьком обороте — если ваши 30% оборота от топа достаточно для активации полок, можно жить.

Когда стратегия НЕ работает (большинство ниш): 1. Полки требуют оборота — без оборота они не стрелят, дешёвого трафика не будет. 2. CTR в середине страницы проседает — пользователь утомился, листает. До середины долистает только тот, кто не нашёл что искал в топе. 3. Уникального товара нет — если ваш товар не выделяется, пользователь не будет выбирать вас на 30-й позиции. 4. Низкий CTR в полках = беда — лечится либо ростом оборота, либо никак.

Правило: «Если посредственный товар + средние позиции — не получится. Если уникальный товар + средние позиции — может получиться, но проверять.»

Глоссарий