Привет всем, JetStyle.Promo здесь. В данном случае мы объясняем, как мы создали систему ETL и дашборда, которая решает задачу анализа больших объемов данных для контроля и повышения эффективности продвижения сайта с помощью поисковых исследований, усложняющуюся из-за выборки данных.
Содержание
Суть дела
Наш клиент — Руспрофиль — служба контроля и анализа контрагентов. Портал Руспрофиль содержит подробную и актуальную информацию о более чем 10 миллионах российских юридических лиц и 12 миллионах индивидуальных предпринимателях. При более чем 500 000 посещений в день наш клиент столкнулся с дичайшей выборкой Яндекс Метрики, которая не позволяет просматривать точные данные более 2 дней.
Мы создали систему ETL и дашборда, решающую эту проблему (для чего сначала мы обработали более 120 ГБ сырых данных из Яндекс Метрики и Топвизора, в результате исходная таблица базы данных содержит более 700 миллионов строк). ).
ETL (Extract, Transform, Load) — это трехэтапный процесс управления данными, дословно переводится как «извлечение, преобразование, загрузка».
На данный момент:
-
затраты на настройку системы визуализации окупились в августе;
-
экономим заказчику 300 000 рублей в месяц, устраняя необходимость подключения Метрики Про для доступа к полным данным;
-
Благодаря собственной системе хранения и обработки данных мы предлагаем постоянный доступ ко всей информации.
Выборка — это процесс отбора части данных для анализа, используемый для снижения нагрузки на систему.
Проблема бизнеса и ее решение
Проблема
Сложность анализа больших объемов данных для контроля и повышения эффективности поискового продвижения сайта из-за выборки. Полные данные доступны в интерфейсе Метрики до двух недель. Для получения более полного доступа необходимо подключить Метрику Про (от 300 000 рублей в месяц).
Решение
Создайте собственное хранилище данных и визуализируйте его в дашборде на базе Yandex DataLens.
Полная панель управления
- Отслеживание ключевых SEO-метрик: позволяет быстро отслеживать динамику органического трафика и видимости сайта по ключевым показателям (поисковая система, группа запросов, смысл поискового запроса).
- Принятие решений на основе данных. Из-за выборки в Яндекс Метрике данные часто искажаются. Панель мониторинга предоставляет четкие полные данные для анализа, что облегчает принятие стратегических решений.
- Выявление точек роста и проблемных зон: дашборд показывает динамику роста по группам запросов и помогает быстро выявить резкие падения позиций в отдельных группах, позволяя своевременно принять меры.
Реализация, технические детали
Основная особенность проекта — очень большой объем данных.
Например, загрузка данных из Яндекс Метрики за месяц содержит около 15 миллионов строк и весит более 3 ГБ, при этом используется всего 14 полей среди всех доступных в Logs API.
Для поддержания доступа к данным мы использовали только российские и открытые решения:
Нажмите «Домой». – для хранения данных, так как эта столбчатая СУБД предназначена именно для работы с большими объемами данных.
Расход воздуха – для оркестровки потоков данных.
Яндекс ДатаЛенс — для визуализации данных, поскольку инструмент российский, имеет необходимый функционал и активно развивается.
Блокноты Jupyter (Python) – для облегчения использования кода (на основе опыта Netflix: Beyond Interactive: инновации в ноутбуках в Netflix и Часть 2: планирование блокнотов в Netflix).
Мы спроектировали структуру набора данных и его хранения в базе данных, чтобы:
-
не скачивайте ненужные данные, которые не будут использоваться;
-
обеспечить защиту от дублирования данных при загрузке;
-
Защитите процесс загрузки данных от сбоев соединения.
Мы также написали коннекторы для:
Для обеспечения безопасности и увеличения скорости работы дашборда в базе данных создано несколько слоев:
-
временные данные (слой tmp) – сюда загружаются данные на начальном этапе. Если соединение не удастся, это повлияет только на временные таблицы, а основная таблица останется нетронутой;
-
неагрегированные необработанные данные (необработанный слой) – здесь данные импортируются из временных таблиц. Кроме того, проверяется отсутствие пересечения наборов данных и фиксируется дата импорта, чтобы можно было отслеживать изменения;
-
витрины данных (слой dwh) – этот слой состоит из необработанного слоя и содержит данные, агрегированные в необходимые срезы. Использование витрин снижает нагрузку на базу данных и ускоряет загрузку диаграмм в дашборде.
Такая структура защищает от ошибок и ускоряет работу панели.
При загрузке данных мы дополнительно обогащаем их, предоставляя дополнительные срезы, недоступные в текущих инструментах:
Для данных Яндекс Метрики
-
размечаем разделы сайта с помощью регулярных выражений, что позволяет быстро фильтровать по ключевым разделам в личном кабинете;
-
Мы рассчитываем плановые показатели с индивидуальными коэффициентами для каждой поисковой системы, что позволяет гибко определять план трафика и контролировать его выполнение;
По данным Топвизора — мы размечаем запросы по группам и целям поиска на основе словаря Google Sheets, что обеспечивает более гибкую сегментацию в отчетах.
Чтобы обеспечить актуальность данных отчетов, мы разработали конвейер в Apache Airflow:
-
данные из Яндекс Метрики загружаются ежедневно за предыдущий день и загружаются в базу данных;
-
в понедельник пересчитывается агрегация данных о посещениях из Яндекс Метрики;
-
также в понедельник загружаются данные Топвизора за прошедшую неделю и обновляется агрегация;
-
По результатам всех операций уведомления о статусе и обновлении загрузки отправляются в чат Telegram, а логи сохраняются на сервере в формате блокнота Jupyter, что позволяет легко определить причину сбоя в случае возникновения ошибки.
Инсайты, гипотезы, процесс создания и взаимодействие с клиентами
Сам дашборд мы создали в Yandex DataLens, следуя логике продукта:
-
Изначально у нас было четкое понимание ожиданий клиента, примерное представление о конечном результате и важных для него срезах данных.
-
После настройки загрузки данных Яндекс Метрики мы сразу реализовали MVP вкладку с данными о трафике. После этого в течение 3-4 итераций мы доработали дашборд: изменили логику расчета план-факт, ввели витрины для увеличения скорости и добавили сравнение произвольных периодов.
-
К моменту работы над вкладкой с позициями клиент уже получил работающий инструмент отслеживания динамики поискового трафика. В процессе разработки вкладка «Позиции» также претерпела ряд изменений и улучшений.
-
После создания каждой вкладки мы сверяли данные с перекрестными ссылками, чтобы исключить ошибки расчетов.
В процессе настройки дашборда мы столкнулись с некоторыми интересными проблемами, например:
1. Раздел «План-факт»
Изначально он рассчитывался отдельно по месяцам и неделям на уровне DataLens и общей формулы. Позже выяснилось, что для Яндекса и Google нужны разные коэффициенты. Поэтому расчеты были перенесены на уровень базы данных, что упростило управление и увеличило скорость загрузки графики.
2. Посты в Топвизор
Мы обнаружили, что из-за недостаточного баланса позиции могут не обновляться. В данном случае мы добавили возможность задавать позиции по шаблону на конкретный день и диапазон в Google Sheets — эти данные извлекаются при агрегации и сохраняются в таблице.
3. Загрузка датасета с историческими данными из Яндекс Метрики.
Для этой задачи требовался настольный компьютер, поскольку на MacBook не хватало места на SSD. Мы написали цикл, который всю ночь загружал данные в базу данных небольшими порциями по миллиону строк.
4. Неожиданные вопросы
Однажды Сергей Торкунов (наш клиент) спросил, например: «Сколько страниц нам следует удалить из 36 миллионов, чтобы выборка отражала положение дел с точностью 95%?» Нам приходилось периодически освежать знания по теории вероятностей, а однажды мы даже связались с профессором университета, чтобы убедиться в правильности расчетов.
Работать с кем-то, кто входит в топ-20 известных SEO-специалистов – это не только ответственно и круто, но и очень увлекательно!
5. Разработка дашборда
Пока мы писали этот случай, мы доработали набор данных для позиций на основе новых входных данных. Сейчас мы отслеживаем еженедельные данные по 515 000 поисковым запросам, которые можно фильтровать как по сайту клиента, так и по его конкурентам. При этом каждый будет иметь распределение по группам и смысловым интенциям.
