Назад к проектам

Как я автоматизировал SEO-отчёт

Google Search Console · Яндекс Вебмастер · Python · PostgreSQL · Power BI

Однажды ко мне пришёл наш SEO-специалист и показал слайд, который он каждый месяц готовил для клиентского отчёта.

На слайде ничего сложного: показы, клики, CTR и средняя позиция. Нужно было показать динамику и сравнить Google с Яндексом за один и тот же период.

Проблема была в подготовке. Данные приходилось выгружать из разных систем, сводить, строить графики, приводить всё к нужным цветам и размеру, а потом вставлять в презентацию.

Он спросил, можно ли это автоматизировать.

Можно.

В результате получился автоматизированный SEO-отчёт: данные из Google Search Console и Яндекс Вебмастера загружались через Python в PostgreSQL, а в Power BI можно было сравнивать поисковые системы, периоды и устройства без ручной подготовки графиков.

Посмотреть интерактивный дашборд ↓

Сначала хотелось сделать гораздо больше

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

Но это уже требовало совсем другого объёма данных: их нужно регулярно собирать, хранить историю и поддерживать отдельную инфраструктуру. Ресурсов команды на такую систему тогда не было.

Поэтому решили не усложнять и начать с конкретной задачи: автоматически собирать основные показатели из Google и Яндекса и показывать их в одном отчёте.

Что получилось

Я остановился на довольно простой архитектуре.

Всё работало локально на Windows-компьютере. Отдельный сервер для такого объёма данных был просто не нужен.

Python забирал данные из API Google Search Console и Яндекс Вебмастера и приводил их к общей структуре. История сохранялась в PostgreSQL, а Power BI Desktop подключался непосредственно к базе.

Почему PostgreSQL

Можно было обойтись CSV-файлами, но я сразу хотел хранить достаточно длинную историю.

По мере накопления данных постоянно растущий CSV становился бы неудобным промежуточным хранилищем. PostgreSQL эту проблему снимал: данные лежат отдельно от отчёта, их можно обновлять и запрашивать независимо от Power BI.

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

Google и Яндекс в одной модели

У Google Search Console и Яндекс Вебмастера разные API и разная структура ответов. Поэтому перед загрузкой данные нужно было привести к общей модели.

В результате для Power BI источник уже выглядел одинаково независимо от поисковой системы:

Это позволило строить одни и те же графики для Google и Яндекса и сравнивать их за одинаковые периоды.

Здесь же я добавил разрез по типам устройств. Динамику можно было смотреть отдельно для компьютеров, смартфонов и планшетов. Это немного углубило анализ: например, стало можно быстро проверить, одинаково ли изменение проявляется на разных устройствах.

SEO-специалисту эта возможность понравилась и стала одним из дополнительных сценариев работы с отчётом.

Немного о расчётах

После объединения источников данные дополнительно агрегировались перед загрузкой в отчёт.

Из технических деталей здесь интереснее всего средняя позиция. При объединении строк её нельзя считать обычным средним: строка с тысячей показов должна влиять на итоговый показатель сильнее строки с десятью.

Поэтому средняя позиция рассчитывалась с учётом количества показов.

transforms/common.pypython
combined["weighted_position"] = combined["position"] * combined["impressions"]
grouped = (
    combined.groupby(GRAIN, as_index=False, observed=True)
    .agg(
        clicks=("clicks", "sum"),
        impressions=("impressions", "sum"),
        weighted_position=("weighted_position", "sum"),
        loaded_at=("loaded_at", "max"),
    )
)
grouped["position"] = grouped["weighted_position"] / grouped["impressions"]

Остальная обработка была довольно стандартной. Мне вообще нравится эта часть работы: написать загрузку на Python, привести данные в порядок и немного покопаться в SQL.

Что можно было смотреть в отчёте

Сам по себе график трафика редко объясняет, почему что-то изменилось. Поэтому мне хотелось видеть основные показатели рядом.

Например, если трафик из Яндекса падает заметно сильнее, чем из Google, можно посмотреть, что происходит с показами и средней позицией.

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

Дашборд не объяснял причину автоматически. Его задача была проще — быстро показать, где именно изменилась картина и что имеет смысл исследовать дальше.

Из отчёта для презентации — в рабочий инструмент

Изначальная задача была довольно утилитарной: раз в месяц быстро подготовить визуализацию для клиентской презентации.

Раньше процесс выглядел примерно так:

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

Но в итоге SEO-специалист стал открывать Power BI гораздо чаще, чем раз в месяц. Он менял периоды, сравнивал Google и Яндекс, переключал устройства и следил за динамикой показателей.

То есть начинали мы с автоматизации одного регулярного отчёта, а получился небольшой рабочий инструмент для SEO-аналитики.

Дашборд

Ниже можно посмотреть публичную демонстрационную версию отчёта.

Если отчёт не загрузился, Открыть дашборд на весь экран.

Для демонстрации на Leadmeter я использую отдельный обезличенный CSV-набор данных. Он не содержит исходных данных проекта или доступов к API. В рабочей версии источником для Power BI был PostgreSQL.

Код проекта на GitHub

Итог

Мне этот проект нравится именно своей простотой. Для решения задачи не понадобилась сложная инфраструктура: хватило Python, PostgreSQL и Power BI.

Главное, что ручная подготовка отчёта исчезла, а SEO-специалист получил инструмент, которым стал пользоваться и для текущего анализа.

Для меня это хороший результат автоматизации: меньше ручной работы и больше времени на сам анализ.