​​ETL и датавиз для Rusprofile

Хранилище данных и дашборд на базе Yandex DataLens

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

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

Реализация

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

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

• ClickHouse — для хранения данных;
• Airflow — для оркестрации потоков данных;
• Yandex DataLens — для визуализации данных;
• Jupyter Notebooks (Python) — для удобства работы с кодом.

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

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

Также мы написали коннекторы (программы, которые автоматически забирают данные из внешних сервисов) для Logs API Яндекс Метрики и API Топвизора.

Дашборд работает быстро и надёжно благодаря многоуровневому хранению данных:

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

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

Обогащение и обновление данных

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

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

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

Для данных из Топвизора

Группируем поисковые запросы и размечаем их по интентам (смысловым целям) на основе словаря в Google Sheets — это даёт более гибкую сегментацию в отчетах.

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

• Ежедневно данные из Яндекс.Метрики за предыдущий день автоматически загружаются в базу;
• Каждую неделю (по понедельникам) пересчитывается агрегация визитов;
• По понедельникам загружаются свежие данные из Топвизора и обновляются отчёты.

После каждого запуска система автоматически отправляет уведомления в Telegram-чат о статусе обновления, а все логи сохраняются на сервере в формате Jupyter-ноутбуков. Это упрощает поиск и устранение ошибок, если вдруг что-то пошло не так.

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

Дашборд мы собрали в Yandex DataLens, опираясь на продуктовую логику и реальные задачи клиента.

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

В ходе настройки дашборда у нас возникло несколько любопытных задач

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

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

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

Иногда из-за недостаточного баланса данные о позициях не обновлялись. Мы предусмотрели запасной вариант: добавили возможность задавать позиции вручную через Google Sheets. При агрегации система подхватывает эти значения и записывает их в таблицу.

Исторические данные из Яндекс.Метрики

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

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

Как-то раз Сергей Торкунов (наш заказчик) спросил, например, такое: «Сколько страниц нужно взять из 36 млн, чтобы выборка отражала положение дел с точностью 95%?». Нам приходилось вспоминать теорию вероятностей, а один раз даже консультироваться с преподавателем из университета. Работать с экспертом такого уровня (Сергей входит в топ-20 SEO-специалистов в России) было не только ответственно, но и очень увлекательно.

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

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