Подбор минус-слов в Яндекс Директ с помощью макроса Microsoft Excel

В начале сентября Яндекс порадовал нас новым отчетом по поисковым запросам, который теперь показывает не только запросы, по которым были клики, а и те, по которым мы получили показы. Это дает нам потенциальную возможность избавиться от лишних показов и, как следствие, поднять CTR, а также сократить количество нежелательных кликов в будущем.

В этой статье мы поделимся нашим скриптом для Microsoft Excel, который позволяет вычленять из поисковых запросов будущие минус-слова быстро и эффективно, а также значительно сократить время на работу с отчетом, т.к. мы не будем тратить время на просмотр запросов, которые уже одобрены в виде фраз.

Этап №1. Сохранение отчета по поисковым запросам

В данной статье, для простоты, мы будем показывать работу на примере одной РК.

Итак, первое, что мы делаем — выбираем нужную РК и переходим в раздел статистики.

to_statistics_yad

Выбираем вкладку «Поисковые запросы». При желании можно сразу отфильтровать по количеству конверсий, чтобы исключить из отчета те запросы, что дали нам конверсии – их минусовать смысла нет. Нажимаем Экспорт в xlsx, т.к. работать дальше будем в Microsoft Excel.

export_search_terms_yd

Этап №2. Выгрузка ключевых фраз из соответствующей РК

Суть работы скрипта – оставить из всех запросов только те слова, что мы предварительно не одобрили как «плюс-слова». То есть те, что встречаются в наших ключевых словах (логично, что если они есть в ключевых словах, значит, мы дали добро на их открутку), любые другие списки, которые мы не хотим минусовать — например, бренды, города и т.п.

Делаем выгрузку фраз. Можно это сделать как с помощью веб-интерфейса:

vygruzka_web_interface1

vygruzka_web_interface2

Так и через Директ Коммандер

export_direct_commander

Этап №3. Сводим все вместе

Итоговый файл должен иметь примерно такую структуру:

Ключевая фраза | Поисковый запрос | Показатели

За базовый берем отчет по поисковым запросам, в который просто переносим столбец с ключами. Это, собственно, единственная необходимая манипуляция на этом этапе. Также чисто из эстетических соображений удаляем ненужные строки и столбцы.

Итоговая таблица примет приблизительно такой вид:

table_sample

Этап №4. Установка и запуск макроса

Для запуска открываем редактор Visual Basic. Делается это или через интерфейс Excel:

visual_basic_editor

Если у вас этой вкладки нет, ее нужно добавить. Для этого заходим в настройки (в английском Excel: File > Options > Customize Ribbon > Main Tabs > ставим галочку на Developer).

excel_developer

Или же просто используем сочетание клавиш ALT + F11.

В открывшемся редакторе создаем модуль:

new_module

В который вставляем текст скрипта:

module_script

 

disclaimer: Скрипты написаны не профи-программистом: пожелания, замечания и изменения кода приветствуются

Запускаем скрипт, нажав зеленый значок Play (или клавишу F5). Выскакивает диалоговое окно с просьбой выделить колонки «поисковый запрос» + «показы» + «клики» + «стоимость». В моем случае, это столбцы B-E.

play_select

По следующему запросу выбираем столбец с ключевыми словами:

select_keywords

И, наконец, указываем ячейку-ориентир для вывода информации:

output_cell

Получаем итоговый список потенциальных минус-слов:

potential_negatives

Работа со списком потенциальных минус-слов

Конечно, в идеале нужно полученные слова прогнать через нормализатор, который поможет свести вместе статистику слов в разных формах (например, «красный», «красная», «красными»). Существует несколько сервисов, которые помогут это сделать. Один из них — http://py7.ru/tools/norm/.

Далее сведенную информацию мы можем отфильтровать по различным критериям.

Например:

— запросы с большим количеством показов и малым количеством кликов для оптимизации CTR;

— самые дорогие запросы;

— запросы с наибольшим количеством кликов.

Тюнинг скрипта

В нижней части кода находится функция, которая «чистит» слова от лишних символов. С ее помощью можно исключить из нашего отчета цифры, английские буквы, специальные символы и т.п.

Править нужно строчку:

Сейчас она учитывает пробелы, цифры, английские и русские буквы.

Если эта строка будет выглядеть так:

В отчете останутся только кириллические символы.

Вот и все. Успешной борьбы с нежелательным трафиком!