...

Отправить заявку на SEO-продвижение сайта от Ant-Team.ru

Заказать

Как мы создали собственную базу данных и переехали из дорогого Seowork в новый сервис 

Рассказываем, как мы создали собственную БД для кастомной системы дашбордов и безболезненно (ну почти) «перевезли» три с лишним гигабайта данных с Seowork на SpySERP. Бонусом отдаём подробный туториал по переезду с кусочками кода. 

Рис.1. Как мы создали собственную базу данных и переехали из Seowork.

О чем материал: 

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

Как родилась идея собственных дашбордов

Мы seo-агентство Ant-Team.ru, работаем с 2011 года, за это время обросли проектами и клиентами. Для проектов используем сервисы аналитики: SeoWork, Я.Метрика, GSC, Я.Вебмастер и другие. Чем больше у агентства проектов, тем сложнее работать с данными: не всегда заметна их взаимосвязь, бывает сложно учесть изменения в данных и отнести их к недавним внедрениям. В результате страдает и скорость, и качество работы.  

Частично задачу помогал решать Сеоворк: там доступны интеграции с сервисами типа Яндекс Метрики и других, можно указывать информацию о внедрениях. Но этой функциональности все равно не хватало. Поэтому мы решили, что пора создать собственную систему аналитики. Дашборды начали строить в Google Looker Studio. 

В процессе поняли, что хранить данные Сеоворка, да и остальных сервисов, которые используем, удобнее у себя. Так мы получим неограниченный доступ, исключим риски потери данных и создадим на их основе свои инструменты без оглядки на сервис.

Как настраивали собственную базу данных 

Поначалу мы пользовались опцией от Сеоворка — они бесплатно предоставляли доступ к базе данных в Google BigQuery. Это было удобно, особенно с учетом связки с Google Looker (бывш. Data) Studio.

Но в апреле 2023 года данные в BQ просто перестали обновляться! Техподдержка неделями обещала исправить это «вот прям завтра», но «завтра» не наступало. Спустя почти месяц ожидания мы получили ответ: «К сожалению, мы больше не имеем возможности поддерживать выгрузки в BQ. С июня BQ будет недоступен». Как вы понимаете, все это время наши дашборды тоже не работали. 

Тогда мы развернули свою базу данных на облачном сервисе под MySQL. Для начала задизайнили простую схему БД, которая отражала то, что мы получали по API от Сеоворка и использовали в дашбордах. Получали две таблицы: одну с проектами, другую — с позициями проектов по дням сбора. Дополнительно добавили статус проекта в соответствующую таблицу. 

Рис.2. Первая схема базы данных.

В таком виде база была слишком тяжелой, данные подтягивались долго, а нам еще нужно было хранить у себя все исторические данные, помимо актуальных. Поэтому мы изучили принципы структуры СУБД и пошли проектировать архитектуру. 

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

.

Рис.3. Мем data engineering

В итоге база данных стала выглядеть так:

Рис.4. Схема базы данных после первой попытки нормализации.

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

Как собирали данные из Сеоворка

Окей, базу мы спроектировали, даже попытались ее нормализовать. Теперь надо собрать исторические данные и залить их в БД. А еще написать скрипт, который будет собирать свежие данные из Сеоворка и записывать их туда же. Расскажем об этом в формате туториала. 

Пишем скрипт для сбора данных 

Представим, что мы с вами только-только освоили азы языка Python на степике и пишем первый скрипт выгрузки данных по API. Не будем писать свои функции, использовать многопоточность и т.д. — обойдемся циклами и самыми понятными решениями.

Что будем использовать: 

  • модули для отправки HTTP-запросов, кодирования и декодирования данных JSO;
  • библиотеку Pandas для удобной работы с данными в виде табличек; 
  • адаптер для настройки взаимодействия с HTTP-серверами;
  • класс для настройки параметров повтора запросов. 

Рис.5. Импорт библиотек и модулей

Объем исторических данных большой, выгрузка займет много времени. Нужно учесть, что сервер не всегда будет возвращать «200 ОК». Для таких ситуаций стоит предусмотреть стратегию повторных запросов.

Рис.6. Код стратегии повторных запросов

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

Дальше через техподдержку Сеоворка получаем токен:

Рис.7. Токен seowork

Для запросов по API все готово!

Первым делом выгружаем все проекты:

Рис.8. Запрос к API на выгрузку проектов из seowork

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

Забираем исторические данные из Сеоворка

Теперь самая важная часть — сбор исторических данных по позициям ключевых слов. Для этого понадобятся даты сбора. 

Свои мы выгрузили еще из BQ, но у Сеоворка есть отдельный метод для этого:

Рис.9. Метод для выгрузки всех дат сбора позиций для проекта

Пакуем данные в таблицу или список.

Затем берем нашу таблицу с проектами, таблицу с датами и пишем цикл. В этом цикле делаем запрос к API для каждого айди проекта, поисковой системы (их в Сеоворке у нас две — Яндекс и Google) и даты. 

Выглядит это так:

Рис.10. Запрос к API на выгрузку данных о сборе позиций из seowork

Запрос отдает данные в json:

Рис.11. Образец получаемых по API данных из seowork

Скрипт преобразовывает и собирает их в таблицу:

Рис.12. Образец преобразованных скриптом данных

Дальше из таблички извлекаем уникальные ключи с их параметрами ID и name, а также  все остальные данные из справочников. Скрипт грузит справочники в БД, а затем и в основную таблицу с данными сбора. Но уже без значений ключей, урлов документов и имен категорий, а только с айдишниками. 

Забираем актуальные данные

Займемся получением свежих данных. Для этого используем тот же скрипт, но уберем из цикла «прогулку» по списку дат. Так как данные для всех проектов мы всегда собираем по воскресеньям, просто ограничим запрос датой последнего воскресенья.

Сделать это можно, например, вот так:

Рис.13. Код получения даты последнего воскресенья

Прощаемся с Сеоворком и переезжаем на SpySERP

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

Но радость была недолгой. Вскоре после запуска БД Сеоворк поднял нам ценник. Воспоминания о том, в какое неприятное положение нас поставил отвалившийся BQ,  были еще свежи. Так что соглашаться с новыми условиями мы не стали, тем более что одновременно с этим получили выгодное предложение от Спайсерп. Оно привлекло нас хорошей ценой и реальной возможностью влиять на улучшение продукта. Мы согласились. 

Для начала нужно было передать данные в SpySERP: 3,2 гигабайта данных и около восьми миллионов строк. Плюс мы хотели перенести в новый сервис не только все проекты, но и собранные за пять лет данные по позициям. Обсудили вопрос с ребятами из Спайсерп и быстро договорились о выгрузке в CSV на Google Диск. 

Выгружаем таблицу с позициями

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

  • date (дата сбора позиций) 
  • search engine (поисковая система)
  • name (ключевое слово)
  • document (релевантная ссылка)
  • category_name (название категории) 
  • position (позиция)
  • project_id (айди проекта) 

      + таблица проектов.

Можно сохранить эти таблицы в CSV и передавать в SpySERP. Но сохранять их  целиком не получится — у CVS ограничение в 1 млн строк на хранение, а наша таблица больше. Значит, нужно действовать через цикл кусками (чанками) по 1 000 000 строк или меньше. Например, так: 

Рис.14. Код для разбивки таблицы данных на чанки и сохранения их в CVS 

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

Импортируем данные 

Например, в процессе подготовки импорта выяснилось, что Спайсерп не разделяет первую и нулевую* позицию в выдаче. А для нас этот момент принципиально важен. Дело в том, что между ними возможна разница в контексте пользовательского поведения, а такие моменты нужно отслеживать. Например, если проект попал на нулевую позицию и дает точный ответ на запрос пользователя, трафик конкретно на эту страницу может упасть, ведь пользователь уже получил нужную информацию. Хотя видимость страницы по такому запросу — лучше некуда.

*Zero-click, или нулевая позиция – это блок, который отображается непосредственно на странице выдачи и расположен над всеми другими результатами поиска (включая органику и рекламу).

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

Рис.15. Отчет спайсерпа с флагом нулевой позиции

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

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

Автоматизируем выгрузку данных из SpySERP в БД

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

Методы и выдача API Спайсерпа достаточно сильно отличаются от API Сеоворка. Тем не менее само API и документация удобные. Плюс у Спайсерпа есть сущности, которых нет в Сеоворке. Например: домен, поисковая система с привязкой к региону и устройству.     

Какое-то время ушло на то, чтобы научиться доставать по API необходимые данные и приводить их в соответствие с БД. Для этого написали скрипт, который преобразует данные и заливает их в базу. Параллельно в базу добавили новые сущности и справочники. Вообще, стоит сказать, что структура ответов API SpySERP отлично подходит для того, чтобы данные поместить в базу без лишней обработки. 

Сколько времени занял переезд

У нас с техподдержкой Спайсерп ушло чуть больше месяца на формирование механизма импорта, исправление косяков в собственных данных, поиск способа интеграции новых данных в БД (ведь нужно было как-то подружить новые айди со старыми) и добавления нужных фич. 

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

Пайплайн переезда

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

  1. Собрать таблицу со своими проектами.

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

  • идентификатор проекта (если есть)
  • домен
  • информация об устройстве (ПК/Смартфон)
  • регион (можно текстом)
  • поисковые системы
  • статус (если у вас есть замороженные проекты)
  • язык (языковой сегмент)

Наша таблица выглядела так:

Рис.16. Таблица с проектами для переезда

  1. Выгрузить данные из текущего сервиса.

Тут тоже несложно. Выгружайте табличками через интерфейс или по API. Все зависит от количества данных и/или умения программировать на начальном уровне (если речь об API). 

Что нужно:

  • идентификатор проекта (если есть) или домен
  • дата съема позиции
  • поисковая система
  • ключевое слово
  • релевантный урл (страница, на которую должен привести запрос)
  • фактический урл (какая страница по этому запросу попала в выдачу)
  • позиция

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

У нас это выглядело так:

Рис.17. Таблица с данными о позициях для переезда

  1. Передать данные в SpySERP.

Все собранные данные нужно передать команде SpySERP. Мы это сделали, залив файлы в CSV-формате на Google Диск. Если появятся косяки и ошибки в данных (что случается у всех), их можно исправить при импорте с помощью скрипта. 

Что в итоге

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

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

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

Автор: Анна Чечкова, руководитель отдела аналитики Ant-Team.ru

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *