Хватит чистить руками: автоматизируем минусовку в Яндекс Директе через Excel и Python

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

Выгружаем данные из Мастера отчетов. Видим простыню на 15 000 строк. Начинаем скроллить. Глаза замыливаются уже на второй сотне, мозг перестает воспринимать текст. А ведь где-то там прячутся фразы, которые тихо и методично выедают дневной бюджет. Делать это построчно руками — непозволительная роскошь.

Метод первый: Excel для тех, кто не любит кодить

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

Как выглядит процесс сборки частотного словаря в Excel:

• Выгружаем отчет по поисковым запросам за нужный период из Директа. Берем столбцы «Поисковый запрос», «Показы», «Клики», «Расход» и данные по отказам из Яндекс Метрики.
• Используем функцию разделения текста по столбцам, указав пробел в качестве разделителя. Получаем широкую таблицу, где каждое слово разбито по ячейкам.
• Собираем все получившиеся слова в один длинный столбец с помощью надстройки Power Query (функция «Отменить свертывание столбцов»).
• Строим Сводную таблицу, где в строках будут наши слова, а в значениях — количество их упоминаний или суммарный расход по всем фразам, содержащим это конкретное слово.

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

Метод второй: Python для суровых объемов

Если кампаний много, семантика широкая, а бюджеты приличные, Excel начнет безбожно виснуть. Здесь на сцену выходит Python. Не пугайтесь, писать сложную архитектуру не придется. Простой скрипт пишется один раз, а экономит десятки часов ежемесячно. Нам понадобятся библиотеки pandas для работы с таблицами и pymorphy2 для русской морфологии.

Логика работы скрипта:

• Считывание файла выгрузки из Яндекс Директа.
• Очистка от спецсимволов, цифр и стоп-слов (предлоги, междометия).
• Лемматизация — скрипт прогоняет каждое слово через pymorphy2 и возвращает его в именительный падеж единственного числа (слова «купили», «покупкой», «купить» сольются в один кластер «купить»).
• Подсчет метрик по каждому корню. Скрипт группирует показы, клики и потраченные рубли не по целым длинным фразам, а по отдельным леммам.

На выходе вы получаете аккуратный датафрейм, где черным по белому написано: корень «своими руками» встречается в 150 разных поисковых запросах, суммарно сожрал 5000 рублей, а конверсий — ноль. Вы просто копируете это слово в список минус-фраз на уровне кампании.

Что это дает на практике

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

Оставьте рутину алгоритмам, а освободившееся время потратьте на аналитику посадочных страниц и работу с конверсией. В конце концов, наша задача — приносить лиды, а не играть в гляделки с бесконечными таблицами.

Бонус: готовый скрипт для поиска минус-слов

Чтобы вы не писали код с нуля, мы подготовили базовый скрипт. Его можно запустить прямо в браузере, используя бесплатный сервис Google Colab.

Вам понадобится выгрузка из Мастера отчетов в формате .xlsx, в которой есть столбцы: «Поисковый запрос», «Показы», «Клики» и «Расход».

Важное правило перед запуском: Откройте скачанный из Директа файл и убедитесь, что самая первая строка — это заголовки столбцов («Поисковый запрос», «Показы» и т.д.). Если Яндекс добавил сверху пару строк с названием отчета и датами — просто удалите их.

Для начала установите библиотеку для распознавания русского языка (в Google Colab это делается отдельной ячейкой с восклицательным знаком в начале):

!pip install pymorphy2

А вот и сам скрипт. Скопируйте его в следующую ячейку, загрузите ваш файл с отчетом и запустите:

import pandas as pd
import pymorphy2
import re

# 1. Инициализируем морфологический анализатор
morph = pymorphy2.MorphAnalyzer()

def lemmatize_query(text):
    # Защита от пустых строк (NaN)
    if pd.isna(text):
        return []
        
    # Вытаскиваем только слова (убираем цифры и спецсимволы)
    words = re.findall(r'[а-яёa-z]+', str(text).lower())
    
    # Приводим к начальной форме
    lemmas = [morph.parse(word)[0].normal_form for word in words]
    
    # Отсеиваем короткие предлоги и союзы (короче 3 букв)
    valid_lemmas = [word for word in lemmas if len(word) > 2]
    
    # ВАЖНО: set() убирает дубликаты внутри одного запроса, чтобы не задваивать расход
    return list(set(valid_lemmas))

# 2. Читаем файл (в Google Colab файл нужно предварительно загрузить)
print("Читаем файл...")
df = pd.read_excel('direct_report.xlsx')

# --- ЗАЩИТА ОТ МУСОРА ИЗ ДИРЕКТА ---
# Отсекаем пустые строки, если Директ выгрузил кривую таблицу
df = df.dropna(subset=['Поисковый запрос'])
# Убиваем финальную строку "Итого", чтобы она не исказила статистику
df = df[~df['Поисковый запрос'].astype(str).str.contains('Итого', case=False)]
# ----------------------------------

# 3. Очищаем финансовые метрики от неразрывных пробелов и запятых
print("Очищаем метрики...")
metrics = ['Показы', 'Клики', 'Расход']
for m in metrics:
    if m in df.columns:
        # Убираем пробелы, меняем запятую на точку и превращаем в числа
        df[m] = df[m].astype(str).str.replace(r'\s+', '', regex=True).str.replace(',', '.')
        df[m] = pd.to_numeric(df[m], errors='coerce').fillna(0)

# 4. Применяем лемматизацию
print("Разбираем слова...")
df['Леммы'] = df['Поисковый запрос'].apply(lemmatize_query)

# 5. Разворачиваем списки в строки (каждое слово на новую строку со статистикой фразы)
df_exploded = df.explode('Леммы').dropna(subset=['Леммы'])

# 6. Группируем данные по отдельным словам и суммируем метрики
print("Считаем расходы...")
result = df_exploded.groupby('Леммы').agg({
    'Показы': 'sum',
    'Клики': 'sum',
    'Расход': 'sum'
}).reset_index()

# 7. Сортируем слова по убыванию потраченного бюджета
result = result.sort_values(by='Расход', ascending=False)

# 8. Сохраняем результат
result.to_excel('negative_keywords_result.xlsx', index=False)
print("Готово! Статистика собрана кристально чисто. Скачайте файл negative_keywords_result.xlsx")

Как это работает: Если у вас было два запроса — «купить айфон недорого» (расход 500 руб) и «покупка айфона б/у» (расход 300 руб), скрипт поймет, что слова «купить» и «покупка» — это один смысл, а «айфон» и «айфона» — одно слово.

В итоговом файле вы увидите сводную статистику:

• айфон: расход 800 руб.
• купить: расход 800 руб.
• недорого: расход 500 руб.
• б/у: расход 300 руб.

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