Создание базы данных PostgreSQL для хранения данных сервиса «Яндекс Метрика»

Всем доброго дня или просто aloha!

Позвольте представиться. Меня зовут Светлана Лин, я менеджер проектов в компании Бизнес-Метрика, специализирующейся на консалтинге в области Data-проектов и аналитике. Если у вас возникла задача по аналитике, не стесняйтесь обратиться к нам.

Найти больше контента можно в нашем Telegram-канале и на нашем web-блогe о про маркетинговую/продуктовую аналитику.

Сегодня, я начинаю цикл подробных гайдов, посвященных созданию продвинутой аналитики на базе данных сервиса Яндекс Метрика.

В этой статье я расскажу только о первом этапе, но в общей сложности мы с вами пройдем следующий путь:

Этап 1 — Создадим базу данных PostgreSQL для хранения данных из сервиса Яндекс Метрика. (Текущая статья)

Этап 2 - Произведем no code интеграцию исходных данных сервиса Яндекс Метрика в PostgreSQL с помощью сервиса (Используя сервис Veeneo)

Этап 3 — Построим простые когорты на данных Яндекс.Метрики

Итак, поехали!

ЭТАП 1

Проще и понятнее всего развернуть собственную базу PostgreSQL на хостинге через VPS.

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

Кто владеет информацией, тот владеет миром, об этом известно уже более двух столетий. Спасибо кэпу за это!

Каждый аналитик, а порой и маркетолог, рано или поздно сталкивается с ограничениями Яндекс.Метрики.

Первое — это невозможность детализировать информацию на уровне пользователя, к примеру в отчете по источникам не увидеть грануляцию по конкретным юзерам, с их userID или clientID или другими их параметрами.

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

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

Закрытая воронка или зависимая — это последовательность, которая должна содержать данные о пользователях, которые не попадут на шаг 2 если сначала не совершили шаг 1.

Третье - Строить когортный анализ разных метрик

Четвертое - Смотреть путь источников от первого касания до конверсии.

На самом деле ограничений в сервисе Яндекс Метрика (его интерфейсе) +100500, вам достаточно изучить, что умеет Google Analytics 4 и понять разницу. И именно эта статья поможет понять вам как выгрузить исходные данные из сервиса Яндекс Метрика и избавиться от любых ограничений, создав собственную базу данных PostgreSQL, которая сможет хранить информацию в требуемых объемах и с нужной детализацией для решения различных кейсов.

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

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

Итак, хватит теории, LET'S GO PRACTICE друзья!

С чего начать?

С выбора провайдера VPS (англ. virtual private server, русск. виртуальный приватный сервер) — это место где будет хранится ваша облачная база данных.

Все дальнейшие шаги будут описаны на примере провайдера облачных решений, вашего провайдера VPS https://vdsina. ru.

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

Первый этап состоит всего из двух шагов:

- Создадим vps-сервер на доступном хостинге.

- Создадим базу данных PostgreSQL для хранения данных сервиса Яндекс Метрика.

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

Первое что нужно сделать - это перейти на сайт провайдера https://vdsina.ru и нажать кнопку “регистрация”. В появившемся окне указать адрес почты, которой пользуетесь, туда придут явки и пароли. Далее зайти на сайт с помощью полученных данных.

На стартовой странице будет следующая картина:

Чтобы создать сервер нужно кликнуть на синюю кнопку (на любую из них, функционал абсолютно идентичен) и перейти в конфигуратор:

На что здесь важно обратить внимание?

1.1. В разделе “Образ диска” нужно выбрать операционную систему – ubuntu 22.

1.2. В разделе “Тип сервера”: – Стандартные серверы (третий справа вариант). Частота процессора до 3.4 GHz подойдет для большинства задач.

1.3. После выбора типа сервера, откроется окно для выбора объема арендуемой на сервере памяти.

Тут можно пойти экспериментальным путем и для начала выбрать минимальный объем 2 ГБ (как в примере далее), либо произвести расчеты на основе ваших данных.

В Яндекс Метрике Logs Api передаются данные о каждом визите, либо хите (просмотре страницы или цели) посетителя. Они будут занимать одну строку таблицы весом от 0.1 Кб до десятков Кб. Строки с визитами весят чуть меньше строк с хитами.

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

Итак, осталось только пополнить баланс, если вы еще не сделали этого ранее.

После клика на кнопку “создать” появляется окно (если вы не пополняли счет до этого). Кликаем по кнопке “пополнить баланс”. Как видно на скрине, мы будем платить 15 рублей в день.

Информация о стоимости актуальна на ноябрь 2023 года, минимальная сумма пополнения 500 руб. + комиссия.

Если средства на балансе есть, то после нажатия в Конфигураторе кнопки “Создать” в вашем аккаунте появится запись о созданном сервере:

Далее нужно кликнуть по имени сервера, после чего становится доступна панель вкладок для работы с сервером: Управление, Доступ, Тариф, Переустановка и т.д.

Здесь нам важна вкладка “Доступ”.

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

Важно!

Шаги по входу описаны для системы Windows, если у вас Linux или Мас алгоритм будет другим, в таком случае вам нужно обратиться к вашему программисту или сисадмину.

Прежде чем начать описывать дальнейшие шаги, обращу ваше внимание на важные детали:

1. Тайм аут (разрыв) соединения с сервером происходит очень быстро и зачастую неожиданно для неопытного пользователя. Если вы увидите в консоли данное сообщение:

это означает, что нужно перейти к шагу 2.1 вне зависимости от того, на каком шаге вы находитесь сейчас.

2. Если вы ранее не работали с консолью, вот что нужно знать:

- Вызвать консоль можно написав в строке поиска Windows PowerShell

- Вставка скопированных данных в консоль происходит по клику правой кнопки мыши или CTR+V

ШАГ 2.1 - Заходим на сервере под root юзером

Перейдите во вкладку “Доступ”.

Чтобы получить реквизиты для доступа к root аккаунту нажмите на “глаз” как на рисунке ниже.

Обязательно сохраните всю информацию куда-то в блокнот. Пароль, Сервер, Порт. Потом вам это пригодится.

Далее перейдите в виртуальную консоль, нажав на синюю кнопку под реквизитами (см. рисунок ниже):

Кликаем на кнопку ">_ VNC".

Откроется отдельное окно виртуальной консоли операционной системы сервера (что это такое знать не обязательно):

Главное, на что необходимо обратить внимание, - это параметры сервера, которые отображены на сером фоне снизу. Из них нужен только IP (вторая строка), поэтому копируем IP адрес в текстовый документ к уже имеющимся реквизитам доступа.

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

Далее. Откройте консоль Windows PowerShell (как найти описано ранее) и введите команду в консоли на месте мигающего курсора: ssh root@IP_адрес (например: ssh root@195.2.81.124). Тут нужен именно ваш IP адрес, будьте внимательны.

и нажмите Enter .

⚠ Если вы находитесь в другом регионе (не в России), то вам скорее всего придется использовать VPN на самом компьютере, а не в браузере.

Выходит вопрос действительно ли вы хотите продолжить соединение? Наберите на клавиатуре yes и нажмите Enter . После ввода yes нужно заново ввести ваш SSH (например: ssh root@195.2.81.124). Тут нужен именно ваш IP адрес, будьте внимательны, далее нажать Enter.

Далее система вас попросит ввести пароль. Имеется ввиду пароль, который вы сохранили в документ вместе с адресом сервера и номером порта. Введите пароль с клавиатуры или скопируйте из документа и вставьте (чтобы не ошибиться, второй вариант предпочтительнее).

🟡 Внимание! При вводе символы не будут появляться на экране - пароль не будет отображаться, это нормально. После ввода пароля нажмите Enter.

Смотрим на последнюю строку, если видим, что появился root@буквы/цифры (например root@v2197054), значит соединение с сервером прошло успешно.

ШАГ 2.2 - Скачиваем скрипты (Docker образ базы данных PostgreSQL)

Нужно в консоль Power Shell скопировать и вставить 2 следующих скрипта последовательно:

и нажать enter.

и нажать enter.

----

Почувствовали себя Neo ? XD

Осталось немного, держитесь!

-----

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

ШАГ 2.3 - Запускаем установку базы данных

Перед выполнением команды:

придумайте и запишите названия для следующих параметров (в коде подсвечены зеленым цветом):

db_name - название для базы данных;
db_user - пользователь базы данных (должен быть достаточно сложный, для вашей безопасности);
db_pass - пароль для входа (должен быть достаточно сложный, для вашей безопасности);
db_port - порт (5432 - это официально зарезервированный порт TCP/IP для работы с базой данных PostgreSQL, если он окажется занят, тогда можно использовать любой порт в диапазоне от 5400 до 5499).

название базы данных db_name : “veeneo_postgres”

имя пользователя db_user : “abracaDabrA”

пароль db_pass : “qpvbtsgqvklzm7”

порт db_port : 5432

⛔ Ни в коем случае не используйте эти логины и пароли, так как они являются выдуманными.

В этих идентификаторах нельзя использовать символы “@” или “,” или “/” или “:” и пробелы, а название базы и имени пользователя не должны начинаться с “pg_”.

Важно!

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

То есть дальше в консоли нам необходимо вписать наши значения.

Таким образом базовая конструкция скрипта:

bash /tmp/veeneo_init_pg.sh -n db_name -u db_user -s db_pass -p db_port

с приведенными в примере параметрами будет выглядеть следующим образом:

bash /tmp/veeneo_init_pg.sh -n "veeneo_postgres" -u "abracaDabrA" -s "qdfsghfxlzm7" -p 5432

В кавычки взято то, что нужно вставить в код, вам в кавычки брать не нужно.

Чтобы запустить процесс установки, скопируйте готовый код в консоль и нажмите enter.

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

Ситуация 1. Сразу появится такое диалоговое окно с меню сервисов:

Оно управляется с помощью клавиатуры: необходимо с помощью стрелок или клавиши табуляции встать на кнопку ОК и нажать enter.

Ситуация 2. Может предварительно возникнуть необходимость в рестарте процессов и только потом появится окно сервисов:

После нажатия OK (управление с клавиатуры через enter) возникнет меню сервисов:

Что делать в этой ситуации, мы уже обсудили выше. Жмите OK.

Ситуация 3. У вас могут в консоли появится такие строки:

Вам нужно будет в консоль прописать слово reboot и подождать несколько минут после чего заново ввести строку ssh root@___._.__.___ ввести пароль, и заново провести действие. А если опять у вас может ничего не вышло, то возвращаемся к шагу 2.2 и вводим команды заново.
Ситуация 4. Так же у вас может и не появится ни синее ни фиолетовое окошко,
а сразу показать строку с вводом username и password. Тогда смотрим статью дальше.

После этого необходимо ввести ввести username и password.

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

Важно! Логины и пароли которые вы ввели на прошлом шаге должны отличатся от этих.

И в дальнейшей работе вам понадобится именно это имя пользователя (последнее_.

Если что-то пошло не так, вы можете увидеть следующую картину:

Или подобное последнее сообщение:

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

А если все прошло удачно, тогда сообщение будет выглядеть так:

ШАГ 2.4 Проверка работы базы данных

После всех установок и настроек введите в консоль команду проверки:

runuser -l username -c "docker ps" (например runuser -l test_user_2 -c "docker ps"), где username - это имя пользователя базы данных, которое вы придумали и ввели на прошлом шаге.

Если все прошло успешно, вы увидите в консоли следующие сообщения:

А если что-то пошло не так, тогда данные базы будут пустыми:

Для дополнительной проверки можно выйти с помощью команды reboot, а потом зайти снова через ssh root@ip_adress и проверить работает ли контейнер с помощью команды.

runuser -l username -c "docker ps".

Результат должен быть таким же, за исключением времени создания:

Ну вот и финиш, друзья!

Если у вас возникли трудности с этой задачей, лучше не мучайтесь, обратитесь к программисту. Мы постарались учесть разные кейсы, но сами понимаете, что это IT и тут может все пойти не так.

Итак, после оплаты минимальной суммы (на момент написания статьи 645 рублей) вы получили vps-сервер без необходимости обладать собственными серверами, системными администраторами и devops инженерами. На этом сервере вами развернута база данных PostgreSQL, которую дальше нужно будет подключить к Veeneo и загрузить в нее данные сервиса Яндекс Метрика или других источников с помощью No Code функционала.

Вся работа с vps-сервером осуществляется удаленно через консоль любого доступного персонального компьютера. Аккаунт vps-сервера нужен только для пополнения баланса и управления такими настройками сервера, как расширение объема арендуемой памяти. То есть вы просто вносите деньги и можете на время забыть о нем (но помните про важность своевременного пополнения баланса).

Если вам по какой-то причине нужно разместить на одном сервере несколько баз данных, после прохождения предыдущих шагов можно ввести следующие команды: su username (например su user_test_2), где username - это имя пользователя, внесенное на прошлом шаге.

docker run --restart unless-stopped -e POSTGRES_PASSWORD=пароль -d postgres:15

где вместо слова “пароль” вводите пароль для каждой из баз данных.

Убедиться, что базы созданы можно следующим образом:

Что такое сбойные ситуации? Простыми словами - это когда что-то перестает работать в штатном режиме.

Чем они опасны? Можно потерять часть или все данные.

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

Цель профилактики - обеспечить надежную и безопасную работу баз данных, а также минимизировать потенциальные проблемы и риски для бизнеса.

Со временем следует обратить внимание на следующие вещи:

- Базовые настройки по безопасности (отключить вход для root, добавить sudo юзеров и тд.)
- мониторинг работы сервера и сбор его метрик (Нагрузка ЦПУ, расход диска и тд.)
- резервное копирование БД (облачное хранилище, внешний жесткий диск и т.п.).

Если эти вещи вам непонятны и вы не готовы разбираться, то лучше найти себе специалиста, кто будет ухаживать за вашим сервером и базой данной иначе рано или поздно 100% что-то может пойти не так, и вы будете не готовы к такому.

Переходите к следующим эпизодам общего цикла статей про связку PostgreSQL + Яндекс.Метрику

Этап 2 - No code интеграция исходных данных сервиса Яндекс Метрика в PostgreSQL с помощью сервиса Veeneo

Этап 3 - Работа с данными Яндекс.Метрики на примере простого когортного анализа

----

Наш сайт Услуги аналитики данных

Телеграм-канал (Канал про настройку и применение аналитики данных в бизнесе. Web/Mobile аналитика. Трекеры, DWH, ETL, BI)

Всем экспоненциального роста!