Кластеризатор ключевых слов на Power Query
Читать 8 минут
Группируем 3 000 ключевых слов за 15 минут
У нас был классный инструмент — кластеризатор ключевых слов в Excel, который сильно экономил время. Но у него был серьезный минус: каждый раз приходилось заново создавать таблицы, вставлять и протягивать формулы. На это уходило 30–40 минут и можно было легко ошибиться.
С помощью Power Query мы автоматизировали все механические действия: вычисление долей весов, разбивку запросов по словам, формирование справочников слов и лемм, составление названий для групп объявлений и непосредственно саму группировку.
Роман Игошин
коммерческий директор, управляющий партнёр
Всё, что остаётся сделать: подобрать запросы с частотностью → взять шаблон кластеризатора → почистить и отсортировать леммы → получить готовый результат.
Мы написали подробную инструкцию, как пользоваться кластеризатором на Power Query.
Если вы не знаете или не помните, как работает кластеризатор на Excel, прочитайте статью про него. Иначе вы можете запутаться в этой статье.
Скачать Power Query для Excel 2010 и 2013.
В Excel 2016 Power Query уже встроен
Подготовка к кластеризации
1. Запустите кластеризатор.
На каждом листе кластеризатора будет синяя или зеленая таблица с рыбными ключевыми словами и вшитыми формулами. Синие таблицы — это умные таблицы Excel. В них можно удалять всё, кроме первой строки, потому что в ней зашиты формулы для кластеризатора. Зелёные таблицы — результаты работы Power Query. В них ничего нельзя менять или удалять, их можно только обновлять.
Менять или удалять данные можно только в синих таблицах. Если вы измените что-то в зелёной таблице, эти изменения исчезнут после обновления таблицы
2. Вставьте ваш список ключевых слов с частотностью на лист кластеризатора «Исходник».
В списке на листе «Исходник» находятся ключевые слова для будущей рекламной кампании
3. Обновите таблицу на листе PQ 1: кликните правой кнопкой по любой ячейке со значением и выберите «Обновить» (Refresh).
После обновления вы получите справочник слов с долями весов и суммами долей весов.
Если не вы не знаете, что такое доли весов, прочитайте статью про кластеризатор на Excel
В последнем столбце — суммы долей весов. Они зависят от значимости слова и общего количества слов
4. Скопируйте полученную на листе PQ 1 таблицу и вставьте как значения (Paste values) на лист Step 2 - Import Lemmas в столбец Word.
Никогда ничего не исправляйте вручную в зелёных таблицах — эти исправления удалятся после обновления таблицы. Хотите что-то исправить — сначала скопируйте данные в синюю таблицу
5. Лемматизируйте запросы из столбца Word и вставьте результаты в столбец Lemma.
После лемматизации в некоторых леммах останутся запросы со знаками «?» и «|». Лемматизатор добавляет в лемму знак «?», если не распознает слово: например, аббревиатуру «РБК». Знак «|» появляется, если кластеризатор сомневается в форме слова: например, «банк» или «банка». Пропишите правильную лемму вручную или выберите одну из предложенных.
Лемматизатор принял название банка «юнистрим» за глагол и предложил несколько вариантов лемм — просто удалите лишние леммы
6. Обновите таблицу на листе PQ 3.
На этом листе формируется справочник лемм, где для каждой леммы проставлен статус и подсчитаны количество упоминаний, длина и вес. Статус леммы — условный порядковый номер, который используется для сортировки. Лемме с самым большим весом присваивается самый меньший статус, чтобы она попала в начало списка.
Все леммы упорядочены по весу — это произведение суммы весов всех словоформ на их количество
Кластеризация
1. Скопируйте полученную на листе PQ 3 таблицу и вставьте как значения (Paste Values) на лист Step 3 - Status в столбец Lemmas.
Прежде чем начинать работу с леммами, обязательно скопируйте их из зелёной таблицы в синюю, чтобы все изменения сохранились
Мы получили исходник справочника лемм. Если мы оставим справочник как есть, кластеризация пройдёт по принципу «одна группа объявлений — одно слово». В порядке исключения в одну группу могут попасть две одинаковые фразы с разным порядком слов.
Чтобы настроить точность кластеризации и объединить фразы в группы, поработайте со статусами лемм:
- удалите статусы у лемм, для которых не хотите выделять отдельную группу и писать объявление;
- передвиньте более «важные» леммы в начало списка, чтобы они стояли в начале названия группы объявлений. Для этого разделите их статус на 10, 100 или 1000;
- установите статус «123» для минус-слов: все фразы, которые содержат лемму со статусом «123» не попадут в итоговый список.
2. Обновите таблицы на листах PQ 3.1, PQ 3.2 Adgroup и «Структура».
На листах PQ 3.1 и PQ 3.2 формируются промежуточные служебные справочники. Кластеризатор использует их для формирования итоговых результатов. Ничего не трогайте и не меняйте на этих листах, иначе кластеризатор может выдать неправильные результаты.
На листе AdGroup появятся результаты кластеризации. У каждой фразы прописано название группы объявлений, в которую она попадает; фразы отсортированы по алфавиту и образуют единые массивы ключевых слов.
Фразы «exocur ru курсы валют» и «exocur курсы валют» попали в одну группу из-за одинакового набора слов в самих фразах. Мы удалили статус у леммы частицы «ru», поэтому она не повлияла на кластеризацию
Если вы недовольны результатами кластеризации, укрупните группы объявлений по инструкции в пункте 6. Затем обновите таблицы на листах PQ 3.1, PQ 3.2 и AdGroup. Укрупняйте группы снова и снова, пока не получите желаемый результат.
Лист «Структура» — сводная таблица из результатов кластеризации. Там находятся группы объявлений, сумма весов и количество фраз в группе.
Лист «Структура» помогает понять, надо ли уточнять кластеризацию или, наоборот, укрупнять группы
Кластеризатор на Power Query — не полностью автоматическое решение. Вам придётся самостоятельно чистить леммы и контролировать точность группировки слов. Инструмент ускоряет процесс, но не дает готового решения. Благодаря этому, вы всегда контролируете финальный результат.
Обсудим задачу
Мы свяжемся с вами в течение двух часов, чтобы
задать
вопросы
и обсудить, какую пользу можем
принести
Роман Игошин
коммерческий директор, управляющий партнёр