😅 Автоматизация обработки поисковых запросов в Power Query.

Сделал в Power Query автоматический быстрый шаблон для получения из Google Ads новых поисковых запросов, которые ранее не встречались в статистике аккаунта.

Шаблон можно адаптировать и к точно такому же анализу поисковых запросов из Яндекс Директ в том числе. Разница будет лишь в названии столбцов и ещё паре незначительных моментов.

Что выполняется автоматически за 150 шагов преобразований данных:

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

2️⃣ Автоматическое удаление из получаемого списка фраз:

✅ Неявных дублей ранее несогласованных заказчиком фраз на всех предыдущих этапах согласований семантики с его стороны.
✅ Неявных дублей остановленных и активных ключевых фраз в аккаунте.
✅ Неявных дублей уже исключённых мусорных фраз.

Теперь получение не входящих ни в один из выше перечисленных трёх списков новых поисковых запросов вместе с их статистикой, занимает меньше минуты.

3️⃣ Автоматическое назначение всем словам их смысловых ярлыков и групп ярлыков.

4️⃣ Автоматическая группировка семантики по смысловым ярлыкам слов и укрупнение группировки до групп ярлыков.

5️⃣ Порядок группировки учитывает важность каждого кластера (показатели статистики) и всех вложенных в него более мелких кластеров.

6️⃣ Возможность выбора, учитывать ли при группировке фраз все виды добавок (горячие, гео, информационные, ранние этапы спроса и т.д.) или пренебречь ими вовсе.

7️⃣ Автоматическая консолидация конверсий, кликов, показов, расходов и вычисление CPA, CR, CTR, CPC на любых уровнях группировки семантики:

✅ На уровне групп ярлыков.
✅ На уровне смысловых ярлыков категорийных слов.
✅ На уровне смысловых ярлыков добавок.
✅ На уровне поисковых запросов.

8️⃣ Консолидация показателей на уровне одной самой частотной словоформы каждой леммы (начальной словарной формы) запроса с учётом стоп-слов, остальные неявные дубли игнорируются.

————

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

Спойлер:

Я не программист, и владею языком M и DAX в Power Query на супербазовом уровне чайника. Сам ежедневно смотрю на ютубе обучающие ролики. Для оптимизации скорости отработки эти 150 шагов можно сократить раза в два, если убить на это ещё несколько дней.

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

Рассмотрим сегодня на стриме темы:

⚡ Группировка семантики на английском языке в Power Query и сводных по модели данных.

⚡ Покажу новый автоматический шаблон с результатами обработки и группировки новых поисковых запросов. Объяснять его создание с нуля не буду, сейчас нет цели разжёвывать все 150 операций преобразований данных, но покажу вкратце, что он делает. Получилась пушка.

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

Я не собираюсь сейчас продавать вам никакие файлы-шаблоны, а лишь покажу, как можно делать, для чего всё это делается, как это поможет в дальнейшем, и сколько времени вы можете экономить ежемесячно, в том числе все ваши сотрудники и коллеги.

————

Дата: сегодня, 23 мая.
Время: через час в 19:00 по МСК.
Место: турбочятик.
Памписка: 10 000 ₽/мес.
Запись: будет.

————

Сорямба за столь поздний анонс, только закончил всё это делать. До встречи.

via @ppc_bigbrain

😎 (1/3) Автогруппировка 35 тысяч фраз за 2 минуты в Power Query.

Доделал в PQ автоматический группировщик семантики по смыслу и намерениям, к которому постепенно шли на стримах весь май.

Получилась бомбическая бомба пушка торпеда взрывчатка. Не прочитав при группировке ни единой фразы, настолько детально сгруппировать семантику за 2 минуты, вообще в неё не глядя — это ультрапобеда. В следующем посте прикрепил скрины с результатами отработки запроса.

Группообразующими критериями, которые динамически пересчитываются сквозным образом через все этапы обработки данных в шаблоне, являются:

1️⃣ Смысловые ярлыки слов.

2️⃣ Группы ярлыков (ещё более укрупнённые смысловые группировки).

3️⃣ Общая частотность всей группы ярлыков в 1-gram анализе.

4️⃣ Общая частотность каждого отдельного ярлыка в 1-gram анализе.

5️⃣ Общая частотность каждой категории независимо от порядка и морфологии её написания.

6️⃣ Количество категорийных слов во фразе.

Например, категорийных слов во фразе — 2, из них одно — главное категорийное, другое — второстепенное подкатегорийное, а также во фразе: горячих добавок — 2, геодобавка — 1.

Суммарно слов во фразе: 2+2+1 = 5.

Но первостепенным критерием группировки выступает сначала главная категория, затем — подкатегория в порядке её общего охвата в отрасли, а добавки — по остаточному принципу, но тоже в порядке приоритета в отрасли.

7️⃣ Комбинации категорийных смысловых ярлыков в каждой строке:

а) Без учёта порядка слов, морфологии и стоп-слов.
б) С учётом порядка слов, морфологии и стоп-слов.

8️⃣ Комбинации ярлыков горячих и гео добавок в каждой строке.

9️⃣ Для универсализации единой схемы нейминга (порядка и набора слов в названиях) всех кластеров извлечённые в отдельный столбец категорийные слова расположены в строке в том же самом порядке, в котором упорядочены по важности все кластеры лексики.

————

Приведу пример.

По убыванию общего объёма показов упорядочены следующие кластеры:

1. Предмет.
2. Места установки.
3. Конструкции движения.
4. Материалы.
5. Конструкции створчатости.
6. Стиль.
7. Разные наборы характеристик.
8. Пожаропрочность.
9. Конструкции строения.
10. Цвета.
11. Конструкции покрытий.
12. Описание мест установки.
13. Покрытия.
14. Количественные характеристики.
15. Характеристики размеров.
16. Габариты.
17. Единицы измерения.

В каждом кластере свой набор уникальных слов.

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

————

Отсюда автоматически рождается универсальная единая схема нейминга и категорийных кластеров, и групп объявлений для рекламного аккаунта:

Группа ярлыков — ярлык — название предмета рекламы — самая важная его характеристика — чуть менее важная — ещё менее важная — ещё менее важная — и т.д.

————

Тем самым:

⚡️ Схлопывается в единое написание огромное количество неявных дублей с разным порядком слов.

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

⚡️ При обычной сортировке категорийных слов в строке от А до Я потерялся бы смысл фразы, какое из слов — главное, а какое — зависимое.

⚡️ Сохранение такого же порядка извлечённых категорийных слов, какова и доля охвата их смысловых ярлыков в отрасли, позволяет в списке категорий видеть:

🔣 Упорядоченные последовательности лексических паттернов.
🔣 Какие характеристики более и менее популярны.
🔣 Какие именно характеристики являются основными, а какие — вложенными (второстепенными).

————

Такая приоритизация в 1 клик позволяет увидеть, с каких категорий и подкатегорий начинать:

✅ Запуск рекламы.
✅ Работу с разными типами посадочных страниц.
✅ Изменение текущих и изготовление новых посадочных страниц в порядке приоритета интересов ЦА, а не вслепую наугад.
✅ Изменение порядка секций и кнопок навигационного меню сайта.

Скриншоты группировки фраз и десятки сценариев применения этого автошаблона — в следующих постах.

🔻 Читать дальше продолжение.