ETL и датавиз для Rusprofile: seo-аналитика на больших данных

1. Вводная задача от заказчика, проблематика, цели

Сложность анализа больших объемов данных для контроля и повышения эффективности поискового продвижения сайта из-за семплирования. Несемплированные данные доступны в интерфейсе Метрики максимум за две недели. Чтобы получить более полный доступ, необходимо подключать Метрику ПРО (от 300 тыс. рублей в месяц).

2. Описание реализации кейса и творческого пути по поиску оптимального решения

ЦЕЛИ ДАШБОРДА

• Мониторинг ключевых метрик SEO: позволяет быстро отслеживать динамику органического трафика и видимость сайта по ключевым показателям (поисковая система, группа запросов, смысл поискового запроса).
• Принятие решений на основе данных: из-за семплирования в Яндекс.Метрике данные часто искажены. Дашборд предоставляет чистые, несемплированные данные для анализа, что упрощает принятие стратегических решений.
• Идентификация точек роста и проблемных зон: дашборд показывает динамику роста по группам запросов и позволяет оперативно выявлять резкие падения позиций по отдельным группам, что дает возможность своевременно принимать меры.

Основная особенность проекта — действительно большой объем данных.

Например, выгрузка данных из Яндекс.Метрики за один месяц содержит около 15 млн строк и весит более 3 ГБ, и это при использовании всего 14 полей из всех доступных в Logs API (https://yandex.ru/dev/metrika/ru/logs/fields/visits).

Чтобы сохранить доступ к данным, мы использовали только российские и опенсорсные решения:

• ClickHouse — для хранения данных, поскольку эта колоночная СУБД разработана специально для работы с большими объемами данных.
• Airflow — для оркестрации потоков данных.
• Yandex DataLens — для визуализации данных, так как инструмент российский, обладает нужным функционалом и активно развивается.
• Jupyter Notebooks (Python) — для удобства работы с кодом (на основе опыта Netflix: https://netflixtechblog.com/notebook-innovation-591ee3221233, https://netflixtechblog.com/scheduling-notebooks-348e6c14cfd6).

Мы спроектировали структуру датасета и его хранение в базе данных, чтобы:

• не выгружать лишние данные, которые не будут использоваться;
• предусмотреть защиту от дубликации данных при загрузке;
• защитить процесс загрузки данных от сбоев соединения.

Также мы написали коннекторы к:

• Logs API Яндекс Метрики;
• API Топвизора.

Подробная схема представлена в блоке с изображениями.

Для обеспечения безопасности и повышения скорости работы дашборда в базе данных создано несколько слоев:

• временные данные (tmp-слой) — сюда данные загружаются на начальном этапе. В случае обрыва соединения затронуты будут только временные таблицы, а основная таблица останется нетронутой;
• сырые, неагрегированные данные (raw-слой) — сюда данные импортируются из временных таблиц. Дополнительно проверяется отсутствие пересечения датасетов, а дата импорта фиксируется для возможности отслеживания изменений;
• витрины данных (dwh-слой) — этот слой формируется на основе raw-слоя и содержит агрегированные данные в нужных срезах. Использование витрин снижает нагрузку на базу и ускоряет загрузку чартов в дашборде.

Такая структура защищает от ошибок и ускоряет работу дашборда.

При загрузке данных мы дополнительно обогащаем их, чтобы получить дополнительные срезы, недоступные в текущих инструментах:

1. Для данных из Яндекс Метрики

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

2. для данных из Топвизора – размечаем запросы по группам и поисковому интенту на основе словаря в Google Sheets, что обеспечивает более гибкую сегментацию в отчетах.

Для обеспечения актуальности данных в отчетах мы разработали пайплайн в Apache Airflow:

• данные из Яндекс.Метрики выгружаются ежедневно за предыдущий день и загружаются в базу;
• по понедельникам пересчитывается агрегация данных о визитах из Яндекс.Метрики;
• также по понедельникам выгружаются данные Топвизора за прошедшую неделю и обновляется агрегация;
• по результатам всех операций в Telegram-чат отправляются уведомления о статусе и обновлении выгрузки, а логи хранятся на сервере в формате Jupyter-ноутбуков, что позволяет легко определить причину сбоя в случае возникновения ошибки.

3. Результаты сотрудничества

Мы создали ETL и систему дашбордов, которые решают проблему анализа больших объемов данных для контроля и повышения эффективности поискового продвижения сайта из-за семплирования.

Как проект изменил жизнь пользователей

Наш клиент – Rusprofile – сервис проверки и анализа контрагентов. На портале Rusprofile размещена подробная актуальная информация о более чем 10 миллионах российских юридических лиц и 12 миллионах индивидуальных предпринимателей. Имея более 500 000 визитов в сутки, наш клиент столкнулся с дичайшим семплированием* в Яндекс Метрике, которые не дают посмотреть точные данные более, чем за 2 дня.

Мы создали ETL и систему дашбордов, которые решают эту проблему (для чего на старте обработали более 120 Гб сырых данных из Яндекс Метрики и Топвизора, и в результате исходная таблица в базе данных содержит более 700 млн строк).

На данный момент:

• затраты на настройку системы визуализации окупились еще в августе;
• мы экономим для клиента по 300 000 рублей ежемесячно, исключив необходимость подключения Метрики ПРО для доступа к несемплированным данным;
• за счет собственной системы хранения и обработки данных обеспечиваем постоянный доступ ко всему массиву информации.

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

Сергей Торкунов

Head of SEO, Rusprofile

Мы долго искали тех, кто сможет нам помочь с разработкой SEO-дашборда и полной автоматизацией процесса обработки и хранения данных. С коллегами из JetStyle познакомились в чате конференции Baltic Digital Days.

В процессе работу у нас сложилась легкая и при этом эффективная коммуникация: ребята всегда были на связи, старались максимально объяснять все детали технической реализации, предлагали варианты архитектурного решения, если выяснялись какие-то нюансы, перепридумывали, как и что лучше сделать. Мы вместе много и усердно дебажили систему. В итоге – довольны результатом и имеем планы на дальнейшее развитие.