Группируем 3 000 ключевых слов за 15 минут
У нас был классный инструмент — кластеризатор ключевых слов в Excel, который сильно экономил время. Но у него был серьезный минус: каждый раз приходилось заново создавать таблицы, вставлять и протягивать формулы. На это уходило
С помощью 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.
Прежде чем начинать работу с леммами, обязательно скопируйте их из зелёной таблицы в синюю, чтобы все изменения сохранились
Мы получили исходник справочника лемм. Если мы оставим справочник как есть, кластеризация пройдёт по принципу «одна группа объявлений — одно слово». В порядке исключения в одну группу могут попасть две одинаковые фразы с разным порядком слов.
Чтобы настроить точность кластеризации и объединить фразы в группы, поработайте со статусами лемм:
2. Обновите таблицы на листах PQ 3.1, PQ 3.2 Adgroup и «Структура».
На листах PQ 3.1 и PQ 3.2 формируются промежуточные служебные справочники. Кластеризатор использует их для формирования итоговых результатов. Ничего не трогайте и не меняйте на этих листах, иначе кластеризатор может выдать неправильные результаты.
На листе AdGroup появятся результаты кластеризации. У каждой фразы прописано название группы объявлений, в которую она попадает; фразы отсортированы по алфавиту и образуют единые массивы ключевых слов.
Фразы «exocur ru курсы валют» и «exocur курсы валют» попали в одну
Если вы недовольны результатами кластеризации, укрупните группы объявлений по инструкции в пункте 6. Затем обновите таблицы на листах PQ 3.1, PQ 3.2 и AdGroup. Укрупняйте группы снова и снова, пока не получите желаемый результат.
Лист «Структура» — сводная таблица из результатов кластеризации. Там находятся группы объявлений, сумма весов и количество фраз в группе.
Лист «Структура» помогает понять, надо ли уточнять кластеризацию или, наоборот, укрупнять группы
Кластеризатор на Power Query — не полностью автоматическое решение. Вам придётся самостоятельно чистить леммы и контролировать точность группировки слов. Инструмент ускоряет процесс, но не дает готового решения. Благодаря этому, вы всегда контролируете финальный результат.
20 июля 2023
Записал Сергей Галиуллин, отредактировала Наташа Ганецкая
Инструменты
Инструменты
Шаблон таблицы возможностейУстройство агентства
Оценка 360: взгляд на сотрудника глазами коллегУстройство агентства
Правила выживания для стажёров и сотрудниковУстройство агентства
Презентация подходаУправление
План обучения менеджеровПродолжая пользоваться сайтом, вы принимаете соглашение о передаче данных.