Сортировка ключевых слов excel
Вручную группировать запросы не всегда эффективно: перебрать 200–300 запросов можно за час, на 20–30 тысячуйдет неделя. Автоматическим сервисам группировку я не доверю, так как она определяет структуру и управляемость кампании.
Поэтому придумал свой метод, который ускоряет кластеризацию и даёт осознанный результат. Облегчает жизнь при работе с СЯ от 2–3 тысяч ключевых слов. Пробовал работать с 45 000 — Excel начинал умирать. Список из 200–300 запросов быстрее перебрать руками.
Далее расскажу про свой метод кластеризации в теории, а затем — как реализую его в Excel. Дам ссылку на готовый Excel-кластеризатор. Но чтобы им пользоваться, нужно хорошо понимать метод.
Метод
Кластеризация — распределение запросов по кластерам. Кластер — это группа запросов, схожих по смыслу и набору слов. Чтобы выделить такие запросы и объединить их в кластер, нужен признак.
Используем для этого нормализованную форму запроса — уберём окончания и выстроим слова в порядке важности:
Удаление окончаний позволит охватить все возможные словоформы для конкретного слова, а сортировка «по важности» — игнорировать порядок слов.
Убираем окончания
Слово без окончания — это признак, который объединяет разные словоформы:
Чтобы убирать окончания я использую mystem . Это лемматизатор от Яндекса. Он обрабатывает список слов и возвращает нормализованные значения — леммы.
Если система не уверена, какая лемма правильная, то покажет 2–3 варианта. Например, для слова «банку» 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
Считаем вес слов
Сортируем по важности
- 8040 — бумеранг
- 2010 — купить
- 840 — цена
- 640 — москва
- 340 — классический
- 70 - на
- 20 - в
Располагаем запросы по важности
- Купить бумеранг — бумеранг | купить
- Бумеранги цена — бумеранг | цена
- Бумеранги в москве — бумеранг | москва
- Купить классический бумеранг — бумеранг | купить | классический
- Цены на бумеранги в москве — бумеранг | цена | москва | на | в
- Купить классический бумеранг в москве — бумеранг | купить | москва | классический | в
Упорядочиваем и чистим
- Бумеранг | купить: купить бумеранг — 1000
- Бумеранг | купить | классический: купить классический бумеранг — 450
- Бумеранг | купить | москва | классический: купить классический бумеранг в москве — 100
- Бумеранг | москва: бумеранги в москве — 750
- Бумеранг | цена: бумеранги цена — 700
- Бумеранг | цена | москва: цены на бумеранги в москве — 350
В итоге получили первые группы объявлений, с которыми можно работать дальше: укрупнять, объединять, кросс-минусовать. Для этого используем Excel.
Реализация в Excel
Выполняем последовательность действий в таблице (XLS, 537 КБ) с формулами. Кластеризация 1000 запросов займет 30 минут.
Алгоритм одной строкой
Собираем СЯ → собираем частотность → разбиваем запросы по словам и вычисляем доли весов → формируем таблицу-справочник с весами слов → выделяем леммы для слов → вычисляем «вес» леммы → формируем таблицу-справочник с леммами → делаем первичную кластеризацию → укрупняем полученные группы.
Шаг 1. Вычисляем доли весов и разбиваем запросы по словам
Лист «Кластеризация», таблица «Main»
Чтобы избежать правки формул называйте все листы и таблицы аналогично таблице-примеру
- Вычисляем доли весов:
- Доли весов = Частотность / Кол-во слов.
- Кол-во слов =LEN ([@Ключ])-LEN (SUBSTITUTE ([@Ключ]," ",""))+1.
Шаг 2. Формируем таблицу-справочник с весами слов
Лист «Слова — Леммы», таблица «Word»
Преобразуем таблицу из формата [W1] [W2] [W3] [W4] [W5] [W6] [W7] [Доли весов] в формат: [W1] → [Доли весов] [W2] → [Доли весов] [W3] → [Доли весов] [W4] → [Доли весов] [W5] → [Доли весов] [W6] → [Доли весов] [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 к следующему статусу: Справочник лемм
Проставляем «Статус» для лемм — минимальный для старшей леммы (лучше начать с 1 000), дальше +1 к следующему статусу: Справочник лемм
Шаг 5. Делаем первичную кластеризацию
Лист «Кластеризация», таблица «Main»
Для каждого слова в столбцах W1—W7 подтягиваем VLOOKP-ом значения «Статус» → записываем их столбцыL1 – L7 :
Итак, что мы сделали. Разбили запросы по словам. Для каждого слова выделили лемму — можем объединить запросы по общим словам. Для каждой леммы посчитали вес. Остаётся выстроить слова в запросе в порядке важности. Тогда при сортировке по алфавиту запросы сами объединятся в группы объявлений.
Выстраиваем слова в порядке важности функцией SMALL. В диапазоне статусов L1 – L7 ищем самый маленький статус — это самое важное слово во фразе. Затем, ищем второй самый маленький статус — это второе по важности слово во фразе. И так еще пять раз — проверяем оставшиеся столбцы L3 – L7.
Получаем последовательность статусов. Например, 37 → 100 → 200 → 700. Для каждого статуса подтягиваем VLOOKP-ом соответствующую Лемму из справочника Лемм. Соединяем Леммы CONCATENATE-ом и получаемнормализованное значение фразы. Я использую его как название группы объявлений.
Сортируем по алфавиту:
Полная рабочая формула в файле-примере.
Шаг 6. Укрупняем полученные группы
Игнорируя окончания и порядок слов, мы объединили запросы с одинаковым набором слов. Количество групп стремится к количеству слов — это 100 % точность инструмента. Можно использовать, если вы предпочитаете работать с запросами в точном соответствии.
Чтобы укрупнить группы, нужно уменьшить точность — снизить количество лемм, которые составляют «нормализованную форму».
Что можно удалить:
- одинокие буквы, цифры, предлоги, доменные зоны. Леммы длиной 1–3 символа;
- редкие леммы — кол-во упоминаний меньше среднего по списку;
- леммы с малым весом — недостаточно «важные»;
- в редких случаях — топонимы.
Важно: лемму не удаляем, только её «Статус» — этого достаточно, чтобы лемма не попала в «нормализованную форму»:
До какой степени укрупнять: я стремлюсь к среднему показателю 2–3 запроса в одной группе объявлений и слежу за максимальным количеством фраз (помним про ограничения систем контекстной рекламы).
Резюме
Полученный список групп удобно кросс-минусовать и двигать между кампаниями. Название группы поможет писать объявления — вы сами определяете важные слова в названии группы.
Ещё раз алгоритм: собираем СЯ → собираем частотность → разбиваем запросы по словам и вычисляем доли весов → формируем таблицу-справочник с весами слов → выделяем леммы для слов → вычисляем «вес» леммы → формируем таблицу-справочник с леммами → делаем первичную кластеризацию → укрупняем полученные группы.
Отзывы джедаев о кластеризаторе
«Я помогал Роме с созданием инструмента на ранних этапах. Всем рекомендую попробовать кластеризатор для ядра от 2000 ключевых слов → сэкономит время.
Инструмент можно улучшить и превратить в автоматический сервис. Также можно дорабатывать формулы определения веса лемм. Но и в текущем виде он поможет специалистам по контексту, которые работают с большой семантикой.»
Егор Холов, ведущий джедай:
«С помощью кластеризатора сильно удобнее и быстрее сгруппировать фразы и потом писать объявления для них. Из недостатков — первый раз кажется, что это сложновато. Но когда попробуешь, то всё довольно понятно. Но эту штуку лучше автоматизировать.»
«Методику пробовал, но не использую в работе, потому что нечасто собираю контекст в больших объемах.
Хорошо подойдет для работы с большой семантикой, особенно в свете последних нововведений яндекса по низкочастотным запросам. Группировки помогут сэкономить много времени при подготовке ключевых фраз.
Методика на первый взгляд кажется сложной и громоздкой, но если разобраться, то процесс становится понятным и удобным.»
«Кластеризация от Ромы просто находка! Методом пользуюсь каждый раз когда работаю с семантикой — собираю или корректирую кампании.
Больше всего мне нравятся три вещи:
- я регулирую какие фразы попадут в группу. Если вес фразы небольшой, то объединяю с похожими. Не придерживаюсь принципа «один ключ — одна группа», иначе управлять кампанией сложно;
- понимаю механику и вижу какие фразы должны быть в заголовке. Конечно, важно делать полное вхождение ключевого слова. Часто оно не вмещается полностью и я строю заголовок из фраз с бо́льшим весом;
- это Excel, который всем знаком. Не нужно устанавливать дополнительные программы и платить за сервис. Если разобраться в формулах, то уже немного прокачаешься.
Из минусов: все формулы я копирую из готового шаблона и переключаться между окнами одной программы неудобно. Я бы хотела иметь формулы под рукой, а может сделать в будущем какой-нибудь шаблон, чтобы сократить количество копирований. Ещё хотелось бы сократить время группировки, но пока не нашла способ.
В целом, способ мне нравится тем, что механика простая и понятная, её легко внедрить и потом управлять кампаниями.»
Что дальше
Если у вас СЯ от 2–3 тысяч ключевых слов, используйте этот алгоритм. Прогоните алгоритм 2–3 раза, чтобы «впитать».
Если у вас список из 200–300 запросов, переберите руками — так быстрее.
Если хотите готовое решение — попросите программистов написать скрипт.
Я постоянно дорабатываю кластеризатор. В следующих итерациях хочу проработать кросс-минусовку групп, добавить справочники минус-слов и максимально автоматизировать кластеризатор на Power Query. Следите за обновлениями!
Сортировка списка - это выстраивание строк или столбцов в определенном порядке.
MS Excel предоставляет две возможности для сортировки списков. Можно сортировать строки или столбцы в возрастающем или убывающем порядке значения какого -либо атрибута (ключа) или нескольких атрибутов. При сортировке строк изменяется порядок их следования, а порядок следования столбцов остается неизменным. При сортировке столбцов изменяется порядок следования столбцов, а строки остаются на своих местах.
Заголовок обычно не сортируется.
Можно сортировать весь список или его часть.
Для сортировки строк всего списка можно использовать два варианта: сортировка по одному ключу и сортировка по нескольким ключам.
Для сортировки по одному ключу нужно:
- выделить любую ячейку (но не диапазон) в столбце, который содержит ключ сортировки;
- на ленте Данные в группе Сортировка и фильтр кликнуть на пиктограмме Сортировка от А до Я или Сортировка от Я до А.
Для сортировки по нескольким ключам:
- выделить любую ячейку (но не диапазон) списка;
- на ленте Данные в группе Сортировка и фильтр кликнуть на пиктограмме Сортировка. MS Excel проведет анализ списка для определения размера списка, определит строку заголовка, исключаемую из сортировки, и выведет на экран диалоговое окно Сортировка (рис. 1).
- кликнуть на кнопке Параметры диалогового окна;
- в диалоговом окне Параметры сортировки (рис. 2) установить опцию Строки диапазона и , если нужно, опцию Учитывать регистр;
- установить порядок сортировки, добавляя уровни.
Сортировка производится по нескольким ключам (уровням): сначала по первому, указанному пользователем в поле Сортировать по , затем в пределах строк, у которых эти поля совпадают, будет произведена сортировка по второму ключу (уровню), установленному в поле Затем по и т. д.
Ключи для сортировки выбираются из раскрывающихся списков.
В каждом ключе можно установить Порядок сортировки По возрастанию или По убыванию .
Чтобы заголовки списков не включались в сортировку нужно установить опцию Мои данные содержат заголовки .
После щелчка на кнопке ОК будет выполнена сортировка списка.
Если выделить не одну ячейку, а несколько строк и столбцов, то будет сортироваться только выделенная часть списка.
Сортировка столбцов списка
Для сортировки по столбцам нужно:
- установить курсор в какую-либо ячейку списка;
- на ленте Данные в группе Сортировка и фильтр кликнуть на пиктограмме Сортировка.
- кликнуть в диалоговом окне Сортировка кнопку Параметры . Откроется диалоговое окно Параметры сортировки (рис. 2), в котором нужно активизировать переключатель Столбцы диапазона и кликнуть на ОК .
Пользовательский порядок сортировки (настраиваемый)
Иногда возникает необходимость применения нестандартного порядка сортировки. Например, сортировку не в алфавитном порядке, а в виде списка: № счета, Приход, Расход, Дата операции, № п.п . Для создания пользовательского порядка сортировки нужно выполнить следующие действия:
- на ленте Данные в группе Сортировка и фильтр кликнуть на пиктограмме Сортировка;
- в списке Порядок диалогового окна Сортировка выбрать пункт Настраиваемый список;
- в появившемся диалоговом окне Списки области Списки выделить Новый Список;
- В области Элементы списк а ввести, разделяя запятыми, или на новых строках каждый элемент пользовательского списка (рис. 3).
- кликнуть на кнопке Добавить;
- п осле появления пользовательского списка в области Списки кликнуть на кнопке ОК;
- выделить ячейку или диапазон списка для сортировки, на ленте Данные в группе Сортировка и фильтр кликнуть на пиктограмме Сортировка; - в открывшемся диалоговом окне Сортировка кликнуть на кнопке Параметры и в открывшемся диалоговом окне Параметры сортировки установить опцию По столбцам, кликнуть на ОК ;
- в диалоговом окне Сортировка в списке Порядок выбрать необходимый пользовательский порядок сортировки и кликнуть на ОК (рис. 4).
Пошаговая инструкция на пальцах о том, как собрать ключевые слова.
Прочитав статью вы сможете подобрать ключевые слова для систем Яндекс Директ и Google Adwords на базовом уровне.
Какой раздел знаний охватывает статья:
Сбор информации → Стратегия → Первичная настройка аналитики → Подбор ключевых слов → Сортировка ключевых слов → Разработка объявлений → Разработка рекламных кампаний → Анализ данных → Оптимизация → Масштабирование → Поддержка
Вы могли заметить, что перед подбором ключевых фраз для сайта стоит еще 3 пункта, так как материалов по ним у нас еще нету, то изложим их в тезисах:
- Вы должны понимать, кто ваш покупатель и какие запросы он будет вводить в поиск.
- Общие концепции стратегии можно почерпнуть из первой части статьи “Как бесплатно сделать лендинг за вечер?”
- Установите код Яндекс Метрики и Google Analytics себе на сайт, настройте цели
Статья подразумевает, что вы уже разбираетесь в понятиях: вордстат, показы, клики, минус-слова, кросс-минусовка, типы соответствий ключевых слова, частота слов, релевантность. А если и не разбираетесь, то cможете найти их в интернете.
1) Создаем таблицу Excel (советую использовать Google Docs, вот наш шаблон ) и делаем в ней матрицу, как на скрине:
2) Думаем, по каким бы запросам нас могли искать наши потенциальные клиенты, слова берем из головы и распределяем их по столбикам:
Яндекс различает части речи, поэтому «математика» и «математический», это 2 разных ключевых слова, но в то же время Яндекс не различает склонения и числа.
Яндекс различает части речи, поэтому «математика» и «математический», это 2 разных ключевых слова, но в то же время Яндекс не различает склонения и числа.
Расширяем матрицу новыми словами:
Теоретический блок
Мы знаем, что такое ключевая фраза в wordstat, она имеет частоту (показы в месяц), это среднее количество запросов за последние 3 месяца и 1 месяц год назад. Все фразы ниже, это входящие запросы, то есть запросы, которые содержат фразу, которую мы ввели, в разных склонениях и числах. Так же вы можете изучить частоту вашего запроса в разрезе регионов и сезонности во вкладках «По регионам» и «История запросов».
Рассматривайте ключевые слова, как сегменты одного бесконечного массива, из которого мы хотим выделить те сегменты, которые будут релевантны нашему сайту. Например, на картинке ниже, пересечение красного цвета содержит самые целевые запросы «математическая летняя школа» , а зеленое пересечение «математическая школа» содержит запросы более широкого вида, а еще есть запрос «школа» , который содержит 99% не релевантных для нас запросов.
Так вот к чему я веду? Мы возьмем для ручной сортировки все входящие запросы фраз «математическая летняя школа» и «математическая школа» , а «школа» не возьмем, так как мы не хотим сортировать 99% запросов, чтобы найти 1% своих.
Матрица нужна, как материал, для составления ключевых фраз (сегментов). Не стоит добавлять в матрицу все слова, которые только возможны. Например, мы не будем добавлять слова «2016», «купить», «погода», так как мы не берем для ручной сортировки сегмент «математическая летняя школа купить» , он уже будет содержаться в сегменте «математическая летняя школа» , как входящий запрос. Чтобы ориентировать в том, какие слова стоит добавлять в матрицу, а какие нет, нужен опыт.
Так же вы могли заметить, что сегменты «математическая летняя школа» и «математическая школа» пересекаются, следовательно, мы должны вычесть из запроса «математическая школа» слово «летний» . Для этого существуют минус-слова, но об этом позже.
Я не могу описать весь процесс, так как нюансов бесконечно много, но могу подсказать вам ключевые точки, следуя которым вы будете двигаться в верном направлении.
Я не могу описать весь процесс, так как нюансов бесконечно много, но могу подсказать вам ключевые точки, следуя которым вы будете двигаться в верном направлении.
Как определить ключевые слова, другими словами, что ищут пользователи, когда вводят конкретный запрос? Вбейте в Яндекс и посмотрите поисковую выдачу, как правило, на первых позициях содержится именно та информация, которая интересна пользователям.
Как определить ключевые слова, другими словами, что ищут пользователи, когда вводят конкретный запрос? Вбейте в Яндекс и посмотрите поисковую выдачу, как правило, на первых позициях содержится именно та информация, которая интересна пользователям.
2) Вы скажете, что аудитория, которая вводит «детский лагерь», могла бы заинтересоваться нашим предложением, но нет, потенциальных клиентов в этом сегменте не более 5%, следовательно, стоимость клиента будет такой высокой, что бы не сможем конкурировать с обычными детскими лагерями.
3) Так как по запросу «лагерь» более 70% не релевантных ключевых слов, то мы начинаем усекать аудиторию, добавляя уточнения из других столбиков, например во фразе «лагерь математический» почти все внутренние запросы нам подходят, а значит добавляем их в сегменты для последующей обработки, такую операцию нам нужно проделать с каждым ключевым словом, идя от общего «лагерь» и сужая сегменты к частному «детский», «болгария».
Как правило в каждом сегменте есть слова, которые нам не подходят, например:
Мы добавляем эти слова в строку поиска с символом минус и снова нажимаем «Подобрать», чтобы получить тот же список ключевых слов, но уже без содержания минус-слов.
4) Чтобы не запутаться, мы по-очередности ищем и добавляем сегменты ключевых слов в отдельное место справа от матрицы, как в шаблоне. К каждому сегменту добавляем минус-слова.
Помните про пересечение сегментов, на данном этапе нам нужно провести кросс-минусовку между сегментами.
Таким образом, получается карта охвата, смотря на нее, мы четко представляем по каким ключевым словам будут показываться наши объявления.
В поле шаблон у нас сразу есть 4 переменные, если мы хотим перемножить 2 столбика, то нам нужно оставить только переменные %(a)s и %(b)s, а %(c)s и %(d)s удалить. Если мы хотим перемножить 3 столбика, то нужно удалить только %(d)s - количество переменных равно количество столбиков.
Вручную группировать запросы не всегда эффективно: перебрать запросов можно за час, уйдет неделя. Автоматическим сервисам группировку я не доверю, так как она определяет структуру и управляемость кампании.
Поэтому придумал свой метод, который ускоряет кластеризацию и даёт осознанный результат. Облегчает жизнь при работе ключевых слов. Пробовал работать с 45 000 — Excel начинал умирать. Список запросов быстрее перебрать руками.
Далее расскажу про свой метод кластеризации в теории, а затем — как реализую его в Excel. Дам ссылку на готовый . Но чтобы им пользоваться, нужно хорошо понимать метод.
Метод
Кластеризация — распределение запросов по кластерам. Кластер — это группа запросов, схожих по смыслу и набору слов. Чтобы выделить такие запросы и объединить их в кластер, нужен признак.
Используем для этого нормализованную форму запроса — уберём окончания и выстроим слова в порядке важности:
Удаление окончаний позволит охватить все возможные словоформы для конкретного слова, а сортировка «по важности» — игнорировать порядок слов.
Убираем окончания
Слово без окончания — это признак, который объединяет разные словоформы:
Чтобы убирать окончания я использую mystem. Это лемматизатор от Яндекса. Он обрабатывает список слов и возвращает нормализованные значения — леммы.
Если система не уверена, какая лемма правильная, то покажет . Например, для слова «банку» 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
Считаем вес слов
Сортируем по важности
- 8040 — бумеранг
- 2010 — купить
- 840 — цена
- 640 — москва
- 340 — классический
- 70 - на
- 20 - в
Располагаем запросы по важности
- Купить бумеранг — бумеранг | купить
- Бумеранги цена — бумеранг | цена
- Бумеранги в москве — бумеранг | москва
- Купить классический бумеранг — бумеранг | купить | классический
- Цены на бумеранги в москве — бумеранг | цена | москва | на | в
- Купить классический бумеранг в москве — бумеранг | купить | москва | классический | в
Упорядочиваем и чистим
- Бумеранг | купить: купить бумеранг — 1000
- Бумеранг | купить | классический: купить классический бумеранг — 450
- Бумеранг | купить | москва | классический: купить классический бумеранг в москве — 100
- Бумеранг | москва: бумеранги в москве — 750
- Бумеранг | цена: бумеранги цена — 700
- Бумеранг | цена | москва: цены на бумеранги в москве — 350
В итоге получили первые группы объявлений, с которыми можно работать дальше: укрупнять, объединять, . Для этого используем Excel.
Реализация в Excel
Выполняем последовательность действий в таблице (XLS, 537 КБ) с формулами. Кластеризация 1000 запросов займет 30 минут.
Алгоритм одной строкой
Собираем СЯ → собираем частотность → разбиваем запросы по словам и вычисляем доли весов → формируем с весами слов → выделяем леммы для слов → вычисляем «вес» леммы → формируем с леммами → делаем первичную кластеризацию → укрупняем полученные группы.
Шаг 1. Вычисляем доли весов и разбиваем запросы по словам
Лист «Кластеризация», таблица «Main»
Чтобы избежать правки формул называйте все листы и таблицы аналогично
- Вычисляем доли весов:
- Доли весов = Частотность / .
- =LEN ([@Ключ])-LEN (SUBSTITUTE ([@Ключ]," ",""))+1.
Шаг 2. Формируем с весами слов
Лист «Слова — Леммы», таблица «Word»
- Копируем столбцы W1—W7 на новый лист.
- Преобразуем таблицу из формата
[W1] [W2] [W3] [W4] [W5] [W6] [W7] [Доли весов] в формат:
[W1] → [Доли весов]
[W2] → [Доли весов]
[W3] → [Доли весов]
[W4] → [Доли весов]
[W5] → [Доли весов]
[W6] → [Доли весов]
[W7] → [Доли весов]:
Шаг 3. Выделяем леммы и дорабатываем справочник со словами
Лист «Слова — Леммы», таблица «Word»
- Копируем полученный на прошлом шаге список слов «как есть».
- Обрабатываем через mystem → получаем леммы для каждого слова.
- Считаем каждой леммы.
Шаг 4. Формируем с леммами
Лист «Леммы», таблица «Lemmas»
- Копируем полученный список лемм на новый лист и удаляем дубли.
- Из справочника со словами подтягиваем -во упоминаний каждой леммы.
- Считаем в лемме.
- Вычисляем «вес» леммы:
Вес Леммы= [Сумма долей весов слов, входящих в Лемму] * [ Леммы].
Формула:
=(SUMIF (Words[Lemma],[@Лемма], Words[Доли весов]))*[@[]]. - Сортируем леммы по столбцу «вес» — от большего к меньшему.
- Проставляем «Статус» для лемм — минимальный для старшей леммы (лучше начать с 1 000), дальше +1 к следующему статусу:
Шаг 5. Делаем первичную кластеризацию
Лист «Кластеризация», таблица «Main»
Для каждого слова в столбцах W1—W7 подтягиваем «Статус» → записываем их столбцы :
Итак, что мы сделали. Разбили запросы по словам. Для каждого слова выделили лемму — можем объединить запросы по общим словам. Для каждой леммы посчитали вес. Остаётся выстроить слова в запросе в порядке важности. Тогда при сортировке по алфавиту запросы сами объединятся в группы объявлений.
Выстраиваем слова в порядке важности функцией SMALL. В диапазоне статусов L1 – L7 ищем самый маленький статус — это самое важное слово во фразе. Затем, ищем второй самый маленький статус — это второе по важности слово во фразе. И так еще пять раз — проверяем оставшиеся столбцы L3 – L7.
Получаем последовательность статусов. Например, 37 → 100 → 200 → 700. Для каждого статуса подтягиваем Лемму из справочника Лемм. Соединяем Леммы нормализованное значение фразы. Я использую его как название группы объявлений.
Сортируем по алфавиту:
Полная рабочая формула .
Шаг 6. Укрупняем полученные группы
Игнорируя окончания и порядок слов, мы объединили запросы с одинаковым набором слов. Количество групп стремится к количеству слов — это 100 % точность инструмента. Можно использовать, если вы предпочитаете работать с запросами в точном соответствии.
Чтобы укрупнить группы, нужно уменьшить точность — снизить количество лемм, которые составляют «нормализованную форму».
Что можно удалить:
- одинокие буквы, цифры, предлоги, доменные зоны. Леммы длиной ;
- редкие леммы — меньше среднего по списку;
- леммы с малым весом — недостаточно «важные»;
- в редких случаях — топонимы.
Важно: лемму не удаляем, только её «Статус» — этого достаточно, чтобы лемма не попала в «нормализованную форму»:
До какой степени укрупнять: я стремлюсь к среднему показателю в одной группе объявлений и слежу за максимальным количеством фраз (помним про ограничения систем контекстной рекламы).
Резюме
Полученный список групп удобно и двигать между кампаниями. Название группы поможет писать объявления — вы сами определяете важные слова в названии группы.
Ещё раз алгоритм: собираем СЯ → собираем частотность → разбиваем запросы по словам и вычисляем доли весов → формируем с весами слов → выделяем леммы для слов → вычисляем «вес» леммы → формируем с леммами → делаем первичную кластеризацию → укрупняем полученные группы.
Отзывы джедаев о кластеризаторе
«Я помогал Роме с созданием инструмента на ранних этапах. Всем рекомендую попробовать кластеризатор для ядра от 2000 ключевых слов → сэкономит время.
Инструмент можно улучшить и превратить в автоматический сервис. Также можно дорабатывать формулы определения веса лемм. Но и в текущем виде он поможет специалистам по контексту, которые работают с большой семантикой.»
«С помощью кластеризатора сильно удобнее и быстрее сгруппировать фразы и потом писать объявления для них. Из недостатков — первый раз кажется, что это сложновато. Но когда попробуешь, то всё довольно понятно. Но эту штуку лучше автоматизировать.»
«Методику пробовал, но не использую в работе, потому что нечасто собираю контекст в больших объемах.
Хорошо подойдет для работы с большой семантикой, особенно в свете последних нововведений яндекса по низкочастотным запросам. Группировки помогут сэкономить много времени при подготовке ключевых фраз.
Методика на первый взгляд кажется сложной и громоздкой, но если разобраться, то процесс становится понятным и удобным.»
«Кластеризация от Ромы просто находка! Методом пользуюсь каждый раз когда работаю с семантикой — собираю или корректирую кампании.
Больше всего мне нравятся три вещи:
- я регулирую какие фразы попадут в группу. Если вес фразы небольшой, то объединяю с похожими. Не придерживаюсь принципа «один ключ — одна группа», иначе управлять кампанией сложно;
- понимаю механику и вижу какие фразы должны быть в заголовке. Конечно, важно делать полное вхождение ключевого слова. Часто оно не вмещается полностью и я строю заголовок из фраз с бо́льшим весом;
- это Excel, который всем знаком. Не нужно устанавливать дополнительные программы и платить за сервис. Если разобраться в формулах, то уже немного прокачаешься.
Из минусов: все формулы я копирую из готового шаблона и переключаться между окнами одной программы неудобно. Я бы хотела иметь формулы под рукой, а может сделать в будущем шаблон, чтобы сократить количество копирований. Ещё хотелось бы сократить время группировки, но пока не нашла способ.
В целом, способ мне нравится тем, что механика простая и понятная, её легко внедрить и потом управлять кампаниями.»
Что дальше
Если у вас ключевых слов, используйте этот алгоритм. Прогоните алгоритм , чтобы «впитать».
Если у вас список запросов, переберите руками — так быстрее.
Если хотите готовое решение — попросите программистов написать скрипт.
Я постоянно дорабатываю кластеризатор. В следующих итерациях хочу проработать групп, добавить справочники и максимально автоматизировать кластеризатор на Power Query. Следите за обновлениями!
Елена Рабкина Редакция «Текстерры»
Работа контекстологов связана с большим объемом информации, статистических данных, текстами. И все это, как правило, в режиме многозадачности. Чтобы не допустить ошибки и грамотно решить поставленные задачи, можно воспользоваться Excel. Программа значительно упростит вашу жизнь и сама справится со многими вопросами, которые отбирают кучу времени, если делать это вручную.
Для чего вообще использовать Excel? Как минимум для трех основных задач:
- Создание рекламной кампании. Начинается она с XLS-шаблона. И вот как раз при помощи Excel можно загрузить уже готовые шаблоны в Google AdWords и «Яндекс.Директ». Что во многом облегчает работу директолога.
- Редактирование рекламной кампании. Довольно часто объявление нужно дополнить или скорректировать. Большое количество изменений можно сделать в таких программах, как, например, AdWords Editor или «Директ Коммандер». Но зачем лишние ресурсы, если все можно сделать в одном Excel при помощи нехитрых формул?
- Аналитика и отчеты. В Excel есть достаточно сортировок и фильтров, которые позволяют быстро и эффективно делать структурированные отчеты.
Ну а теперь ближе к делу. Вот главные трюки Excel, которые помогут вам вздохнуть свободно!
Полезные трюки Excel для контекстолога
1. Удаляем минус-слова
При работе с текущими рекламными кампаниями может возникнуть необходимость удалить минус-слова. С помощью «Экселя» это можно сделать очень быстро.
Как это сделать? Выделяем ключевые слова. Выбираем инструмент поиска и замены. Для этого зажимаем клавиши CTRL+H. Далее в появившемся поле поиска вводим такую комбинацию « –*». В поле «Заменить» ничего не вводим. Готово, все минус-слова удалились!
2. Удаляем слова-повторы
Когда нужно собрать много ключевых фраз, мы частенько можем упустить дубли. Обычно такие повторы приходится удалять через специальные программы, но и Excel отлично с этим справляется.
Как это сделать? Выделяем необходимую область, в меню выбираем раздел «Данные» и там кликаем на «Удалить дубликаты».
3. Добавление знака рубля
Сокращенное «руб.» смотрится так, будто сложно дописать слово до конца, а полное «рублей» иногда отнимает слишком много символов. Тогда оптимальный вариант – поставить знак ₽.
Как это сделать? Функция «Рубль».
Заходим в «Формулы», «Текстовые», кликаем на «Рубль». В моем случае валюта отображается в белорусских рублях. Теперь выбираем нужную ячейку, в которой указана цена, и программа автоматически добавляет символ рубля.
4. Фильтруем по тексту
Особые текстовые фильтры позволяют выделить те ячейки, в которых встречаются нужные слова. Например, нам нужно из всех ключевых запросов выбрать те, в которых есть слово «сайт», но нет слова «раскрутка», чтобы увидеть статистику.
Как это сделать? Выделяем интересующий столбец, переходим в раздел «Данные». Там жмем на кнопку «Фильтр» и жмем на стрелочку, появившуюся возле столбца. Далее прописываем, что нужно найти (пункт «содержит»), а что нужно исключить (пункт «не содержит»). Сохраняем эти условия и получаем нужный результат.
5. Фильтруем по числам
В работе со статистикой рекламных кампаний иногда нужно отобрать те данные, где есть определенные числовые значения. Например, из всех собранных ключевых фраз найти те, у которых CTR>1, а число показов больше 100.
Как это сделать? Действуем по тому же алгоритму, что и в предыдущем пункте. Выделяем интересующий блок, идем в раздел «Данные», жмем «Фильтр». Нажимая на появившуюся стрелочку в каждой колонке, выставляем нужные показатели и получаем результат.
6. Удаляем лишние пробелы
По невнимательности двойные пробелы допускает практически каждый работающий с текстом человек. И это может быть проблемой при создании объявлений для «Яндекс.Директа» или Google Ads, потому что каждый лишний пробел крадет драгоценные символы. Двойные пробелы нужно убирать.
Как это сделать? Функция «СЖПРОБЕЛЫ».
Заходим во вкладку «Формулы», «Текстовые», кликаем на «СЖПРОБЕЛЫ». Затем выбираем ячейки с нужным текстом и применяем к ним функцию. Двойные пробелы убираются автоматически.
7. Делаем первую букву в строке заглавной
Как это сделать? Формула «=ЗАМЕНИТЬ(A1;1;1;ПРОПИСН(ЛЕВСИМВ(A1;1)))».
Разберем формулу подробнее. Часть «=ЗАМЕНИТЬ(A1;1;1;»:
- А1 – ячейка с текстом, который нужно отредактировать.
- 1 – порядковый номер буквы, которую нужно заменить на заглавную.
- 1 – количество букв, которые нужно заменить.
- А1 – ячейка с текстом, который заменяем.
- 1 – количество символов, которые нужно вставить.
Отсчет символов, как мы и привыкли, начинается слева.
8. Делаем все буквы строчными
Обратная ситуация: по ошибке поставили в тексте заглавные буквы там, где они не нужны, или клиент передумал и решил писать название своей компании с маленькой буквы. Нужно быстро заменить все заглавные буквы на строчные.
Как это сделать? Формула «СТРОЧН». Выбираем ячейку с текстом, который нужно изменить, и прописываем формулу. Готово!
9. Считаем количество знаков в строке
Важная функция, которая поможет уложиться в определенный лимит по знакам. Это позволит не делать лишнюю работу, а также знать, сколько еще необходимо добавить до нужного объема.
Как это сделать? Функция «ДЛСТР».
Заходим в «Формулы», находим «Текстовые» и выбираем «ДЛСТР». Обратите внимание, что символы считаются вместе с пробелами.
10. Убираем лишний текст с конца
Если вы уже знаете, что превысили лимит, и нужно сократить текст, Excel может сделать это за вас автоматически. Например, есть большой текст и его нужно адаптировать под рекламное объявление с определенным количеством знаков.
Как это сделать? Формула «ЛЕВСИМВ».
Заходим во вкладку «Формулы», «Текстовые», выбираем «ЛЕВСИМВ» и вводим то количество символов, которое нам необходимо получить. Программа оставит нужное число знаков с начала строки (слева).
11. Выбираем нужное количество символов из текста
Эта функция похожа на предыдущую, но она позволяет сократить текст в любой его части: в начале, середине или в конце. Допустим, нам нужно пропустить одинаковое вступление в рекламных объявлениях.
Как это сделать? Формула «ПСТР».
Заходим в «Формулы», «Текстовые», выбираем «ПСТР». Выбираем ячейку с текстом, прописываем количество символов, которое нужно оставить, и порядковый номер символа, с которого начинать отсчет.
12. Объединяем в одну ячейку тексты из нескольких колонок
Для рекламных объявлений написали много вариантов заголовков, а теперь нужно ко всем добавить общую концовку, например адрес, дату и т. д. Снова прибегаем к помощи Excel.
Как это сделать? Функция «Сцепить».
Заходим в «Формулы», «Текстовые», «Сцепить». В появившемся окне выбираем ячейку с «Текстом 1» и ячейку с «Текстом 2», которые вы хотите объединить в одну. Главное – соблюсти все пробелы и регистр, о которых говорилось выше.
13. Объединение посложнее
Функция очень похожа на предыдущую, только с более серьезным инструментарием: сама добавит пробел и пропустит строку, если это необходимо.
Как это сделать? Функция «ОБЪЕДИНИТЬ».
Все те же «Функции», «Текстовые», «ОБЪЕДИНИТЬ». Сначала добавляем необходимый разделитель между текстами из двух ячеек: союз, пробел, запятую и т. д. Затем в графе «ИСТИНА/ЛОЖЬ» прописываем «истина». Так автоматически будут добавляться пустые строчки, чтобы избежать нескольких разделителей подряд. Затем выбираем те ячейки, текст которых нужно объединить. Готово.
14. Поиск текста в других строках
Иногда нужно быстро найти информацию, не перечитывая при этом все написанное.
Как это сделать? Функция «ПОИСК».
Открываем «Формулы», «Текстовые», выбираем «ПОИСК». В открывшемся окошке выбираем ячейку с текстом, где нужно найти информацию. Затем вводим слово или предложение, которые нас интересуют. Если знаем, можно указать порядковый номер символа, с которого программа начнет отсчет. Если это пропустить, Excel будет считать с самого начала. Программа отобразит порядковый номер знака, с которого начинается то, что вы ищете.
Кстати, эта формула не учитывает регистр. Если вам нужно найти слово конкретно с заглавной буквы, то используйте формулу «НАЙТИ».
15. Ставим кавычки
Иногда надо поставить кавычки на названия, например.
Как это сделать? Формула «=СИМВОЛ(34)&СЖПРОБЕЛЫ(С1)&СИМВОЛ(34)».
Выделяем нужные ячейки, затем выбираем формулу СИМВОЛ и в скобках указываем «34» (это кавычки). Далее после знака «&» добавляем формулу СЖПРОБЕЛЫ и добавляем номер ячейки (С1). В конце выбираем снова СИМВОЛ, чтобы закрыть кавычки, и готово.
16. Приводим ключ к модифицированному широкому соответствию Google AdWords
Из большого количества фраз необходимо уточнить конкретные ключевые слова.
По сути, нам нужно просто к каждому слову из фразы подставить знак «+». Для этого мы выделяем нужные ячейки, далее прописываем «+», после значка «&» добавляем «ПОДСТАВИТЬ», затем функция СЖПРОБЕЛЫ (сжатые пробелы), номер ячейки, знак «;» и снова «+». Вот и все!
17. Создаем заголовки для «Яндекс.Директа»
К ключевым фразам нам нужно добавить продающее окончание.
Выглядит, конечно, страшновато, но сейчас разберемся. Главное – сразу в первом столбце (начало наших заголовков) поставьте в конце нужные знаки препинания. Далее выбираем длину строки при помощи формулы ДЛСТР. Слишком длинные названия система не допускает.
По этой же формуле можно создать и полноценное рекламное объявление, выбирая нужные ячейки с частями текста.
Надеемся, что эти функции Excel упростят вам жизнь и помогут сократить усилия, которые вы тратите на рутинную работу: проверку текста, поиск нужной информации, подсчет символов, создание рекламных кампаний. Теперь те задачи, которые раньше отнимали так много времени, будут выполняться автоматически за вас. Только не забывайте «растягивать» (зажать зеленый квадратик и тянуть) формулы, применяя их сразу на все нужные строчки.
Если вы хотите ускорить все процессы, связанные с запуском рекламных кампаний, и быстрее начать получать результат, мы готовы взять на себя всю работу.
В Google и «Яндексе», соцсетях, рассылках, на видеоплатформах, у блогеров
Читайте также: