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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Что нужно:

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

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

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

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

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

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

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

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

Подписывайтесь на наш телеграм-канал, чтобы первыми узнавать о выходе новых материалов. И смотрите наши бесплатные обучающие видео по SEO на всех площадках: YouTube, VK video, Rutube.