Как перемножить слова в excel для директа
Прочитав статью вы сможете подобрать ключевые слова для систем Яндекс Директ и Google Adwords на базовом уровне. К данному подходу мы пришли через сотни рекламных кампаний.
Прочитав статью вы сможете подобрать ключевые слова для систем Яндекс Директ и Google Adwords на базовом уровне.
Какой раздел знаний охватывает статья:
Сбор информации → Стратегия → Первичная настройка аналитики → Сбор ключевых слов → Сортировка ключевых слов → Разработка объявлений → Разработка рекламных кампаний → Анализ данных → Оптимизация → Масштабирование → Поддержка
Вы могли заметить, что перед сбором ключевых слов стоит еще 3 пункта, так как материалов по ним у нас еще нету, то изложим их в тезисах:
- Вы должны понимать, кто ваш покупатель и какие запросы он будет вводить в поиск.
- Общие концепции стратегии можно почерпнуть из первой части статьи “Как бесплатно сделать лендинг за вечер?”
- Установите код Яндекс Метрики и Google Analytics себе на сайт, настройте цели
Статья подразумевает, что вы уже разбираетесь в понятиях: вордстат, показы, клики, минус-слова, кросс-минусовка, типы соответствий ключевых слова, частота слов, релевантность. А если и не разбираетесь, то cможете найти их в интернете.
Алгоритм
Теоретический блок
Мы знаем, что такое ключевая фраза в wordstat, она имеет частоту (показы в месяц), это среднее количество запросов за последние 3 месяца и 1 месяц год назад. Все фразы ниже, это входящие запросы, то есть запросы, которые содержат фразу, которую мы ввели, в разных склонениях и числах. Так же вы можете изучить частоту вашего запроса в разрезе регионов и сезонности во вкладках «По регионам» и «История запросов».
Рассматривайте ключевые слова, как сегменты одного бесконечного массива, из которого мы хотим выделить те сегменты, которые будут релевантны нашему сайту. Например, на картинке ниже, пересечение красного цвета содержит самые целевые запросы «математическая летняя школа», а зеленое пересечение «математическая школа» содержит запросы более широкого вида, а еще есть запрос «школа», который содержит 99% не релевантных для нас запросов.
Так вот к чему я веду? Мы возьмем для ручной сортировки все входящие запросы фраз «математическая летняя школа» и «математическая школа», а «школа» не возьмем, так как мы не хотим сортировать 99% запросов, чтобы найти 1% своих.
Матрица нужна, как материал, для составления ключевых фраз (сегментов). Не стоит добавлять в матрицу все слова, которые только возможны. Например, мы не будем добавлять слова «2016», «купить», «погода», так как мы не берем для ручной сортировки сегмент «математическая летняя школа купить», он уже будет содержаться в сегменте «математическая летняя школа», как входящий запрос. Чтобы ориентировать в том, какие слова стоит добавлять в матрицу, а какие нет, нужен опыт.
Так же вы могли заметить, что сегменты «математическая летняя школа» и «математическая школа» пересекаются, следовательно, мы должны вычесть из запроса «математическая школа» слово «летний». Для этого существуют минус-слова, но об этом позже.
Я не могу описать весь процесс, так как нюансов бесконечно много, но могу подсказать вам ключевые точки, следуя которым вы будете двигаться в верном направлении.
Все ли вам понятно? Есть вопросы? Стоит ли нам запилить видео на эту тему?
Лемматизация — это преобразование слова в его начальную форму (лемму).
Для разных частей речи определена своя лемма, для глаголов это инфинитив, для существительных — именительный падеж единственного числа, для прилагательных — именительный падеж единственного числа мужского рода.
Для чего нужна лемматизация
Лемматизация применяется во множестве известных областей (список не исчерпывающий):
- Поисковые системы используют лемматизацию, чтобы связывать поисковый запрос пользователя и контент страниц в интернете.
- Веб-мастера используют лемматизацию, чтобы определить и разместить на странице все запросы во всех словоформах по одной их нормализованной форме. Если не сделать лемматизацию семантического ядра, есть риск не учесть фразы в других менее популярных словоформах помимо очевидных сочетаний.
- Социологи лемматизируют транскрипты важных речей политиков (например, послание президента к федеральному собранию), чтобы наиболее качественно посчитать тональность текста и встречаемость уникальных важных терминов.
- Инструменты статистики поисковых запросов (например, Вордстат) группируют поисковые запросы пользователей по лемматизированным словам, чтобы при проработке ключевых слов рекламодатель не пропустил важные для его бизнеса ключевые слова.
- Специалисты по контекстной рекламе лемматизируют семантическое ядро перед тем, как составить его частотный словарь, чтобы быстро проанализировать самые встречаемые понятия в тексте и подобрать релевантные посадочные страницы.
Морфологические библиотеки
Помимо лемматизации, морфологические библиотеки позволяют производить другие операции со словоформами:
- Получение всех форм слова
- Получение грамматической информации для слова (часть речи, падеж, спряжение и т.д.)
- Изменение формы слова в соответствии с заданными грамматическими характеристиками
PhpMorphy
phpMorphy — библиотека морфологического анализа, реализованная на php
PyMorphy2
pymorphy2 — Аналогичная по сути библиотека для языка Python. Проект использует регулярно обновляемый открытый корпус и словарь OpenCorpora.
MyStem
Первая версия mystem была создана при активном участии одного из основателей Яндекса Ильи Сегаловича в 1996 году. Алгоритм лемматизации был внедрен в поисковый движок и стала одним из ключевых факторов коммерческого успеха Яндекса. Проект продолжает развитие, есть версии для нескольких языков и консольная утилита для Windows.
!SEMTools дает возможность использовать утилиту прямо в Excel:
Лемматизация в Excel необходимости использования консоли с помощью EXE-файла mystem.exe и надстройки для Excel !SEMTools
Лемматизация в Excel
Для простого маркетолога без знаний программирования использование консольных утилит и тем более языков программирования — скорее хобби, подкрепляемое энтузиазмом, нежели прямая обязанность и часть ежедневного процесса работы.
Однако вместе с этим лемматизация находит применение во многих процессах, связанных с семантическим ядром — его кластеризацией, составлением частотного словаря, n-gram анализом, удалением неявных дублей и т.д.
Поэтому я решил создать инструмент, который бы решал эту задачу и сделал ее доступной людям без навыков программирования.
Так родился инструмент-лемматизатор в надстройке !SEMTools. С ним можно в пару кликов произвести лемматизацию семантического ядра любого размера.
Пример лемматизации поисковых запросов в SEO / контекстной рекламе
Для лемматизации в !SEMTools используется готовый словарь популярных пар слово-лемма. Словарь составлен мной и регулярно пополняется новыми парами. Поскольку он довольно увесистый (10 Мб), он не вшит в саму надстройку, а скачивается с этого сайта при запуске процесса, если уже не открыт локально.
Лемматизация в Excel при помощи !SEMTools
Если не хотите скачивать файл каждый раз при запуске — сохраните его на компьютере и открывайте заранее перед запуском процедуры лемматизации. Макрос убедится, что словарь открыт, и не будет обращаться на сайт. Так можно использовать функции лемматизации в оффлайне и не беспокоиться об отсутствии интернета.
Функция работает очень быстро, обрабатывая десятки тысяч строк текста в секунду. Это позволяет не беспокоиться и обрабатывать огромные семантические ядра даже на не самых мощных ПК.
Но есть и небольшие ограничения — слова приводятся к их леммам только будучи в нижнем регистре, поэтому рекомендуется предварительно изменить их регистр. Пунктуация тоже не игнорируется и рассматривается как часть слов. Поэтому перед лемматизацией нужно удалить все символы кроме букв.
Если не скачивается словарь лемм
Иногда у некоторых пользователей надстройки при запуске макроса возникают ошибки, т.к. автоматически не скачивается словарь лемм.
В таких случаях рекомендуется скачать его самостоятельно (не меняя название), открыть файл параллельно в фоне и уже после этого производить процедуру лемматизации.
С лемматизацией тесно связаны удаление стоп-слов и упорядочивание слов по алфавиту внутри ячейки для удаления неявных дублей. В примере выше видно, что все 4 фразы по сути идентичны, разница лишь в словоформах, порядке слов и наличии предлога «в» в половине фраз. Все эти процедуры также выполнимы в Excel с помощью моей надстройки.
Нужно преобразовать слова в их изначальную форму?
!SEMTools поможет решить задачу за пару кликов!
В этой статье я кратко расскажу о том, как создал неплохой бесплатный генератор линейных комбинаций фраз из слов в Excel, и покажу, как им пользоваться.
Предыстория
Периодически возникает потребность произвести операцию комбинирования различных слов и фраз друг с другом.
Сфер применения несколько:
- PPC-специалисты перемножают списки, чтобы составить всевозможные ключевые слова для Яндекс.Директа или Google Ads
- Вебмастера генерируют искусственные тексты для однотипных страниц, используя инструменты как составитель предложений.
- А кто-то использует перемножение списков для генерации бредовых фраз для дурашливых сайтов.
Онлайн-«перемножатели» и их недостатки
Среди специалистов популярны онлайн-генераторы фраз. Их задача – «перемножать» списки заданных слов друг на друга, сохраняя последовательность слов. Пользовался ими и я.
Однако у онлайн-инструментов есть свои минусы:
- Для них нужен интернет – а он все еще не всегда и везде доступен. А кому-то, наоборот, от него хочется сбежать :)
- Зачастую количество столбцов для ввода ограничено и нельзя добавить самостоятельно столбец для ввода
- Не всегда доступна возможность использовать собственный разделитель
- Вывод финален и его нельзя изменить.
- Если вы допустили ошибку, зачастую все списки придется вводить заново, т.к. они не сохраняются
Перемножение списков слов в Excel
Поскольку не хотелось становиться заложником проблем, связанных с онлайн-перемножалками слов, я решил сделать собственный генератор словосочетаний. Он доступен бесплатно как часть моей надстройки для работы с семантическим ядром — !SEMTools для Excel.
Группа инструментов по объединению данных в !SEMTools для Excel Инструмент находится в меню «Комбинации» -> Значения ячеек
Алгоритмически процедура довольно проста — нужно просто составить все комбинации сочетаний всех значений в столбцах.
Вы не ограничены количеством столбцов для комбинации и выбором разделителей. Единственная ваша задача – чтобы количество комбинаций не превысило максимум, который можно вывести на лист – 1048576 строк.
Для корректной работы макроса (без неожиданных результатов) есть определенные легко выполнимые требования:
- Данные нужно вводить всегда с первой строки (A1, B1, C1)
- В столбцах не должно быть полностью пустых ячеек — инструмент остановится на последней непустой ячейке в столбце
- Если среди столбцов будут пустые, макрос возьмет на вход данные до первого пустого столбца
Примеры составления комбинаций
Перемножить слова через единый разделитель
Перемножить слова через собственные разделители
Чтобы использовать собственные разные разделители между словами, нужно их просто вставить в ячейку между основными комбинируемыми сущностями. Префикс и суффикс также можно вставлять — в первый и последний столбец соответственно.
Вы наверняка заметили, что после комбинирования значения остаются в разных столбцах. Это легко поправляется инструментом «объединить диапазон по горизонтали». Смотрите пример:
Привет! Сегодня хочу поделиться с вами способом сбора минус слов для Директа используя только Эксель, на мой взгляд метод получился очень даже рабочим и интересным, и я ни в коем случае не претендую на авторство, возможно я открыл Америку, но перейдем к делу.
Данный способ возможно не подойдет для совсем уж масштабных семантик, но для 2к запросов вполне сгодился, на больших не пробовал.
Для примера у меня это "металлический декор", прям берем и выгружаем кейколлекотором (ну или собираем ручками, тут как вам удобнее) все что есть по этому запросу
Допустим я собирал вот так:
Вот она наша выгрузка, запросов получилось не много, 92 штуки всего)
Далее для удобства убираю формат, и оставляю только запросы, частота и прочее нам сейчас не нужно. и разбиваем наши фразы на отдельные слова в разные ячейки с помощью вот этой функции, выделив нужный диапазон.
Наш список фраз превращается вот в такой вот набор отдельных слов в ячейках.
Далее мы ищем и заменяем те слова которые сто процентов нам нужны, в данном случае у меня металлический декор и я убираю такие слова как: "металлический" "металлических" "металла" "металл". Лучше начать со словоформы подлиннее, что бы не наоставлять обрубков слов в ячейках.
Вот такая теперь у нас получилась табличка:
Теперь я схлопну все столбцы, что бы не было пустых ячеек, и было удобнее собрать все в кучу, это можно делать сортировкой и наверняка еще тысячей методов, но я делаю вот так: F5, у нас появляется окошко, в котором мы выделяем, выбираем что выделить, выделяем все пустые ячейки и удаляем их.
После этого собираем все в один столбец и я на всякий случай накинул условное форматирование что бы подсветить повторяющиеся ячейки
Далее выделяем и идем на вкладку "Данные" и "удалить дубликаты". В итоге получаем список уникальных слов + предлоги, которые встречаются в наших запросах. Остается только вручную пробежаться и проанализировать слова которые вы будете минусовать, и не убрать случайно что-то нужное у меня осталось всего 40 слов на минусацию, заняло минут 15.
При удалении дубликатов у вас все равно останется много словоформ других слов например: море, моря, морем и тд. Если вы уверены, что у вас не специфическая тематика, то можно воспользоваться лемматизатором слов.
Я использую для этого инструмент Арсёнкина, если не ошибаюсь он бесплатный. туда можно закинуть и значительно сократить кол-во запросов.
В итоге на составление минус фраз для семантики из 2 тысяч запросов у меня ушло пол часа, без использования каких либо сервисов и ручного копания запросов.
Напишите мне если у вас есть более элегантные методы, возьму на заметку)
P.s. Лень было читать инфу в интернете как правильно составлять минуса так, что собрал методу на колене)
Недавно ко мне обратился один знакомый с просьбой помочь с генерацией всех возможных фраз, состоящих из набора заданных слов. Подобного рода задачи могут возникать при составлении списков ключевых слов и фраз для интернет-рекламы и SEO-продвижения, когда нужно перебрать все возможные варианты перестановок слов в поисковом запросе:
В математике такая операция называется декартовым произведением. Официальное определение звучит так: декартовым произведением множеств А и В называется множество всех пар, первая компонента которых принадлежит множеству А, а вторая компонента принадлежит множеству В. Причем элементами множеств могут быть как числа, так и текст.
В переводе на человеческий язык, это означает, что если в множестве А у нас, например, слова «белый» и «красный», а в множестве В «БМВ» и «Мерседес», то после декартова произведения этих двух наборов мы получим на выходе совокупность всех возможных вариантов фраз, составленных из слов обоих списков:
- белый БМВ
- красный БМВ
- белый Мерседес
- красный Мерседес
. т.е. как раз то, что нам нужно. Давайте рассмотрим пару способов решения этой задачи в Excel.
Способ 1. Формулы
Начнём с формул. Предположим, что в качестве исходных данных мы имеем три списка исходных слов в столбцах A, B и C, соответственно, причем количество элементов в каждом списке может меняться:
Сначала сделаем три столбца с индексами, т.е. порядковыми номерами слов из каждого списка во всех возможных сочетаниях. Первый ряд единичек (E2:G2) введём вручную, а для остальных используем следующую формулу:
Логика здесь простая: если индекс в вышестоящей предыдущей ячейке уже дошёл до конца списка, т.е. равен количеству элементов в списке вычисленному функцией СЧЁТЗ (COUNTA) , то мы запускаем нумерацию заново. В противном случае - увеличиваем индекс на 1. Обратите особое внимание на хитрое закрепление диапазонов знаками доллара ($), чтобы можно было скопировать формулу вниз и вправо.
Теперь, когда у нас есть порядковые номера нужных нам слов из каждого списка, можно извлечь и сами слова с помощью функции ИНДЕКС (INDEX) в три отдельных столбца:
Если вы раньше не сталкивались в своей работе с этой функцией, то очень советую хотя бы по диагонали её изучить - она выручает в очень многих ситуациях и полезна ничуть не меньше (и даже больше!), чем популярная ВПР (VLOOKUP) .
Ну, а после останется только склеить построчно получившиеся фрагменты с помощью символа конкатенации (&):
. или (если у вас последняя версия Excel) с помощью удобной функции ОБЪЕДИНИТЬ (TEXTJOIN) , умеющей склеивать всё содержимое указанных ячеек через заданный символ-разделитель (пробел):
Способ 2. Через Power Query
Power Query - это мощная надстройка для Microsoft Excel, выполняющая две основные задачи: 1. загрузку данных в Excel из почти любых внешних источников и 2. всяческие трансформации загруженных таблиц. Power Query уже встроена в Excel 2016-2019, а для Excel 2010-2013 она устанавливается как отдельная надстройка (скачать можно с официального сайта Microsoft совершенно бесплатно). Если вы ещё не начали использовать Power Query в своей работе, то самое время об этом подумать, ибо преобразования типа вышеописанных делаются там легко и непринужденно, буквально за пару движений.
Сначала загрузим исходные списки как отдельные запросы в Power Query. Для этого для каждой таблицы выполним следующие действия:
- Превратим таблицы в "умные" кнопкой Форматировать как таблицу на вкладке Главная (Home - Format as Table) или сочетанием клавиш Ctrl + T . Каждой таблице автоматически будет присвоено имя Таблица1,2,3. , которое, впрочем, можно при желании поменять на вкладке Конструктор (Design) .
- Установив активную ячейку в таблицу, нажмем кнопку Из таблицы (From Table) на вкладке Данные (Data) или на вкладке Power Query (если она установлена у вас как отдельная надстройка для Excel 2010-2013).
- В открывшемся окне редактора запросов выберем команду Главная - Закрыть и загрузить - Закрыть и загрузить в. (Home - Close&Load - Close&Load to..) и затем опцию Только создать подключение (Create only connection) . Это оставит загруженную таблицу в памяти и позволит обращаться к ней в будущем.
Если всё сделаете правильно, то на выходе в правой панели должны получиться три запроса в режиме Только подключение с именами наших таблиц:
Теперь щёлкнем правой кнопкой мыши по первому запросу и выберем команду Ссылка (Reference) , чтобы сделать его обновляемую копию, а затем добавим к данным дополнительный столбец через команду Добавление столбца - Настраиваемый столбец (Add Column - Custom Column) . В окне ввода формулы введём имя нового столбца (например, Фрагмент2) и предельно простое выражение в качестве формулы:
. т.е., другими словами, название второго запроса:
После нажатия на ОК мы увидим новый столбец, в каждой ячейке которого будет лежать вложенная таблица с фразами из второй таблицы (увидеть содержимое этих таблиц можно, если щёлкнуть мышью в фон ячейки рядом со словом Table):
Останется развернуть всё содержимое этих вложенных таблиц с помощью кнопки с двойными стрелками в заголовке полученного столбца и сняв при этом флажок Использовать исходное имя столбца как префикс (Use original column name as prefix) :
. и мы получим все возможные сочетания элементов из первых двух наборов:
Дальше всё аналогично. Добавляем еще один вычисляемый столбец с формулой:
…, а затем ещё раз разворачиваем вложенные таблицы – и вот у нас уже все возможные варианты перестановок слов из трёх наборов, соответственно:
Осталось выделить все три столбца слева-направо, удерживая Ctrl , и сцепить их содержимое через пробел, используя команду Объединить столбцы (Merge Columns) с вкладки Преобразование (Transform) :
Получившиеся результаты можно выгрузить обратно на лист знакомой уже командой Главная - Закрыть и загрузить - Закрыть и загрузить в. (Home - Close&Load - Close&Load to..) :
Если в будущем что-то изменится в наших исходных таблицах с фрагментами, то достаточно будет просто обновить созданный запрос, щёлкнув по результирующей таблице правой кнопкой мыши и выбрав команду Обновить (Refresh) или нажав сочетание клавиш Ctrl + Alt + F5 .
Читайте также: