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

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

Чтобы убирать окончания я использую mystem. Это лемматизатор от Яндекса. Он обрабатывает список слов и возвращает нормализованные значения — леммы.
Если система не уверена, какая лемма правильная, то покажет
Определяем важность
Сортировка «по важности» позволит игнорировать порядок слов. При сортировке нормализованных значений фраз по алфавиту мы получим готовые кластеры — группы запросов, схожих по смыслу и набору слов.
Важность слова — вычисляемый параметр для конкретного списка ключевых слов. Он не определяет важность слова в общей картине мира.
Важность слова рассчитывается из частотности и количества упоминаний слов в списке. Рассмотрим на примере.
Берём список запросов с частотностью
- Купить бумеранг — 1000
- Бумеранги цена — 700
- Бумеранги в москве — 750
- Купить классический бумеранг — 450
- Цены на бумеранги в москве — 350
- Купить классический бумеранг в москве — 100
В списке запросов встречаются слова: купить, бумеранг, классический, москва, цена, в, на. Вес слова равен сумме долей частотностей помноженных на количество упоминаний слова.
Считаем доли частотностей
- Купить бумеранг — 1000 = 1000/2 = 500
- Бумеранги цена — 700 = 700/2 = 350
- Бумеранги в москве — 750 = 750/3 = 250
- Купить классический бумеранг — 450 = 450/3 = 150
- Цены на бумеранги в москве — 350 = 350/5 = 70
- Купить классический бумеранг в москве — 100 = 100/5 = 20
Считаем вес слов
- Купить — (500+150+20)*3 = 2010
- Бумеранг — (500+350+250+150+70+20)*6 = 8040
- Классический — (150+20)*2 = 340
- Москва — (250+70)*2 = 640
- Цена — (350+70)*2 = 840
- В — 20
- На — 70
Сортируем по важности
- 8040 — бумеранг
- 2010 — купить
- 840 — цена
- 640 — москва
- 340 — классический
- 70 - на
- 20 - в
Располагаем запросы по важности
- Купить бумеранг — бумеранг | купить
- Бумеранги цена — бумеранг | цена
- Бумеранги в москве — бумеранг | москва
- Купить классический бумеранг — бумеранг | купить | классический
- Цены на бумеранги в москве — бумеранг | цена | москва | на | в
- Купить классический бумеранг в москве — бумеранг | купить | москва | классический | в
Упорядочиваем и чистим
- Бумеранг | купить: купить бумеранг — 1000
- Бумеранг | купить | классический: купить классический бумеранг — 450
- Бумеранг | купить | москва | классический: купить классический бумеранг в москве — 100
- Бумеранг | москва: бумеранги в москве — 750
- Бумеранг | цена: бумеранги цена — 700
- Бумеранг | цена | москва: цены на бумеранги в москве — 350
В итоге получили первые группы объявлений, с которыми можно работать дальше: укрупнять,
объединять,
Реализация в Excel
Выполняем последовательность действий в таблице (XLS, 537 КБ) с формулами. Кластеризация 1000 запросов займет 30 минут.
Алгоритм одной строкой
Собираем СЯ → собираем частотность → разбиваем запросы по словам и вычисляем
доли весов → формируем
Шаг 1. Вычисляем доли весов и разбиваем запросы по словам
Лист «Кластеризация», таблица «Main»
Чтобы избежать правки формул называйте все листы и таблицы аналогично
-
Вычисляем доли весов:
- Доли весов = Частотность /
Кол-во слов . Кол-во слов =LEN ([@Ключ])-LEN (SUBSTITUTE ([@Ключ]," ",""))+1.
Расчёткол-ва слов и доли веса слова - Доли весов = Частотность /
-
Разбиваем слова по фразам функцией «Text to columns»:
Результаты работы функции «Text to columns»
Шаг 2. Формируем таблицу-справочник с весами слов
Лист «Слова — Леммы», таблица «Word»
- Копируем столбцы W1—W7 на новый лист.
- Преобразуем таблицу из формата
[W1] [W2] [W3] [W4] [W5] [W6] [W7] [Доли весов] в формат:
[W1] → [Доли весов]
[W2] → [Доли весов]
[W3] → [Доли весов]
[W4] → [Доли весов]
[W5] → [Доли весов]
[W6] → [Доли весов]
[W7] → [Доли весов]:
Формирование справочника со словами - Удаляем пустые ячейки и считаем
кол-во упоминаний каждого слова.
Шаг 3. Выделяем леммы и дорабатываем справочник со словами
Лист «Слова — Леммы», таблица «Word»
- Копируем полученный на прошлом шаге список слов «как есть».
- Обрабатываем через mystem → получаем леммы для каждого слова.
- Считаем
кол-во упоминаний каждой леммы.
Справочник слов
Шаг 4. Формируем таблицу-справочник с леммами
Лист «Леммы», таблица «Lemmas»
- Копируем полученный список лемм на новый лист и удаляем дубли.
- Из справочника со словами подтягиваем
VLOOKUP-ом кол -во упоминаний каждой леммы. - Считаем
кол-во символов в лемме. - Вычисляем «вес» леммы:
Вес Леммы= [Сумма долей весов слов, входящих в Лемму] * [Кол-во упоминаний Леммы].
Формула:
=(SUMIF (Words[Lemma],[@Лемма], Words[Доли весов]))*[@[Кол-во упоминаний ]]. - Сортируем леммы по столбцу «вес» — от большего к меньшему.
- Проставляем «Статус» для лемм — минимальный для старшей леммы (лучше начать с 1 000),
дальше +1 к следующему статусу:
Справочник лемм
Шаг 5. Делаем первичную кластеризацию
Лист «Кластеризация», таблица «Main»
Для каждого слова в столбцах W1—W7 подтягиваем

Итак, что мы сделали. Разбили запросы по словам. Для каждого слова выделили лемму — можем объединить запросы по общим словам. Для каждой леммы посчитали вес. Остаётся выстроить слова в запросе в порядке важности. Тогда при сортировке по алфавиту запросы сами объединятся в группы объявлений.
Выстраиваем слова в порядке важности функцией SMALL. В диапазоне статусов L1 – L7 ищем самый маленький статус — это самое важное слово во фразе. Затем, ищем второй самый маленький статус — это второе по важности слово во фразе. И так еще пять раз — проверяем оставшиеся столбцы L3 – L7.
Получаем последовательность статусов. Например, 37 → 100 → 200 → 700. Для каждого
статуса подтягиваем
Сортируем по алфавиту:

Полная рабочая формула
Шаг 6. Укрупняем полученные группы
Игнорируя окончания и порядок слов, мы объединили запросы с одинаковым набором слов. Количество групп стремится к количеству слов — это 100 % точность инструмента. Можно использовать, если вы предпочитаете работать с запросами в точном соответствии.
Чтобы укрупнить группы, нужно уменьшить точность — снизить количество лемм, которые составляют «нормализованную форму».
Что можно удалить:
- одинокие буквы, цифры, предлоги, доменные зоны. Леммы длиной
1–3 символа ; - редкие леммы —
кол-во упоминаний меньше среднего по списку; - леммы с малым весом — недостаточно «важные»;
- в редких случаях — топонимы.
Важно: лемму не удаляем, только её «Статус» — этого достаточно, чтобы лемма не попала в «нормализованную форму»:

В основной таблице ничего править не надо — результат обновится самостоятельно.
До какой степени укрупнять: я стремлюсь к среднему показателю

Резюме
Полученный список групп удобно
Ещё раз алгоритм: собираем СЯ → собираем частотность → разбиваем запросы по словам
и вычисляем доли весов → формируем
Отзывы джедаев о кластеризаторе
Что дальше
Если у вас
Если у вас список
Если хотите готовое решение — попросите программистов написать скрипт.
Я постоянно дорабатываю кластеризатор. В следующих итерациях хочу проработать
Будут вопросы — пишите: igoshinrmn@