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

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



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

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

ETL (Extract, Transform, Load) — это трёхэтапный процесс управления данными, в дословном переводе значит «извлечение, преобразование, загрузка».

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

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

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

Бизнес-задача и её решение

Проблема

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

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

Решение

Создание собственного хранилища данных и визуализация их в дашборде на основе Yandex DataLens.

Цели дашборда:

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

Реализация и технические детали

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

Например, выгрузка данных из Яндекс.Метрики за один месяц содержит около 15 млн строк и весит более 3 ГБ, и это при использовании всего 14 полей из всех доступных в Logs API.

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

• ClickHouse — для хранения данных, поскольку эта колоночная СУБД разработана специально для работы с большими объемами данных.
  
• Airflow — для оркестрации потоков данных.
  
• Yandex DataLens — для визуализации данных, так как инструмент российский, обладает нужным функционалом и активно развивается.
  
• Jupyter Notebooks (Python) — для удобства работы с кодом (на основе опыта Netflix: Beyond Interactive: Notebook Innovation at Netflix и Part 2: Scheduling Notebooks at Netflix).

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

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

Также специалисты агентства написали коннекторы к Logs API Яндекс Метрики и к API Топвизора.



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

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

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

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

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

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

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







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

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

Инсайты, гипотезы, процесс создания и взаимодействия с заказчиком

Сам дашборд JetStyle создали в Yandex DataLens, следуя продуктовой логике:

1. На старте проекта было чёткое понимание ожиданий заказчика, примерное представление о конечном результате и важные для него срезы данных.
   
2. Настроив выгрузку данных из Яндекс.Метрики, специалисты сразу реализовали MVP вкладки с данными о посещаемости. После этого в ходе 3–4 итераций JetStyle доработали дашборд: изменили логику расчёта план-факта, внедрили витрины для повышения скорости, добавили сравнение произвольных периодов.
   
3. К моменту работы над вкладкой с позициями заказчик уже получил рабочий инструмент для отслеживания динамики поискового трафика. В процессе разработки вкладка с позициями также претерпела ряд изменений и улучшений.
   
4. После создания каждой вкладки JetStyle проводили кросс-проверку данных, чтобы исключить ошибки в расчетах.

В процессе настройки дашборда у JetStyle возникали интересные заморочки, например:

1. Раздел «план-факт»

Сначала он рассчитывался отдельно по месяцам и неделям на уровне DataLens и общей формулы. Позже стало понятно, что нужны разные коэффициенты для Яндекса и Google. Поэтому расчеты были перенесены на уровень базы данных, что упростило контроль и повысило скорость загрузки чартов.

2. Позиции в Топвизоре

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

3. Загрузка датасета с историческими данными из Яндекс.Метрики

Для этой задачи потребовался стационарный ПК, так как на MacBook не хватило места на SSD-диске. Программисты написали цикл, который целую ночью загружал данные в базу небольшими частями по 1 млн строк.

4. Неожиданные вопросы

Как-то раз Сергей Торкунов (заказчик) спросил:

«Сколько страниц нужно взять из 36 млн, чтобы выборка отражала положение дел с точностью 95%?».

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

Работать с человеком, который входит в ТОП-20 известнейших SEO-персон, не только ответственно и круто, но и очень увлекательно!

5. Развитие дашборда

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

Сергей Торкунов, Head of SEO, Rusprofile:

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

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

Мы вместе много и усердно дебажили систему. В итоге — довольны результатом и имеем планы на дальнейшее развитие».