Кейс «Металл профиль» — как мы запустили кастомный коннектор для загрузки данных из API Google Analytics в Microsoft SQL Server

Расскажу, как с помощью инструментов веб-аналитики мы получили расширенные данные о посещениях сайта для нашего клиента — компании «Металл профиль», производителя тонколистовых кровельных и стеновых материалов на территории России и СНГ.

Задача

Большая часть данных клиента уже собиралась в развернутом на базе Microsoft SQL Server хранилище. Наша задача — расширить эти данные на основе информации о посещениях сайта, полученной из API Google Analytics.

API периодически дает сбои, поэтому дополнительно нужна проверка наличия данных за прошлый период. Для каждого запроса период проверки наличия данных важно задавать индивидуально. Также в каждом запросе индивидуально нужно уметь указывать представление Google Analytics, из которого требуется получить данные.

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

Инструменты

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

• googleAnalyticsR — интерфейс для работы с API Google Analytics;
• odbc — подключение к Microsoft SQL Server через ODBC интерфейс;
• jsonlite — чтение JSON-структур;
• dplyr — манипуляция с данными.

Схема работы коннектора ga2mssql

1. В R загружаются настройки коннектора из файла конфигурации, который содержит описание всех запросов и других требуемых настроек в JSON-структуре.
2. По очереди каждый запрос отправляется в API Google Analytics. Полученный результат дополнительно обрабатывается в R.
3. Полученные данные передаются в Microsoft SQL Server.

Настройка коннектора

В коннекторе ga2mssql нет графического пользовательского интерфейса. Для настройки и управления загрузкой с его помощью изначально необходимо внести все настройки в файл конфигурации. У файла конфигурации такая JSON-структура:

{
  "mssql_con": {
    "Driver": "SQL Server",
    "Server": "localhost",
    "Database": "my_database_name",
    "UID": "my_username",
    "PWD": "my_password",
    "Port": 1433
  },
  "google_analytics_con": {
    "googleAuthR.client_id": "000000000000-xxxxxxxxxxxxxxxxx.apps.googleusercontent.com",
    "googleAuthR.client_secret": "xxxxxxxxxxxxxxxxxxxxx",
    "googleAuthR.scopes.selected": ["https://www.googleapis.com/auth/analytics.edit", "https://www.googleapis.com/auth/analytics"]
  },
  "google_analytics_report": [
    {
      "days": 15,
      "ms_table": "table_1",
      "view_id": "ga:0000000000",
      "dimensions": ["ga:channelGrouping", "ga:day", "ga:date", "ga:region", "ga:country", "ga:medium"],
      "metrics": ["ga:sessions", "ga:bounceRate", "ga:hits", "ga:uniquePageviews", "ga:timeOnPage", "ga:totalEvents", "ga:uniqueEvents", "ga:transactions", "ga:itemQuantity", "ga:quantityAddedToCart", "ga:quantityRemovedFromCart", "ga:itemRevenue", "ga:totalValue", "ga:users", "ga:newUsers", "ga:bounces", "ga:sessionDuration", "ga:avgSessionDuration", "ga:transactionsPerSession", "ga:transactionRevenue", "ga:revenuePerTransaction", "ga:transactionRevenuePerSession", "ga:uniquePurchases", "ga:revenuePerItem", "ga:itemsPerPurchase", "ga:productAddsToCart", "ga:productCheckouts", "ga:quantityCheckedOut", "ga:refundAmount", "ga:revenuePerUser", "ga:totalRefunds", "ga:transactionsPerUser"]
    },
    {
      "days": 7,
      "ms_table": "table_2",
      "view_id": "ga:00000000000",
      "dimensions": ["ga:channelGrouping", "ga:date", "ga:source", "ga:campaign", "ga:sessionDurationBucket"],
      "metrics": ["ga:sessions", "ga:bounceRate", "ga:hits", "ga:uniquePageviews", "ga:timeOnPage", "ga:totalEvents", "ga:uniqueEvents", "ga:itemQuantity", "ga:quantityAddedToCart", "ga:quantityRemovedFromCart", "ga:itemRevenue", "ga:users", "ga:newUsers", "ga:bounces", "ga:sessionDuration", "ga:avgSessionDuration", "ga:uniquePurchases", "ga:revenuePerItem", "ga:itemsPerPurchase", "ga:productAddsToCart", "ga:productCheckouts", "ga:quantityCheckedOut"]
    },
    {
      "days": 15,
      "ms_table": "table_3",
      "view_id": "ga:000000000",
      "dimensions": ["ga:date", "ga:source", "ga:sessionDurationBucket"],
      "metrics": ["ga:pageLoadTime", "ga:bounceRate", "ga:transactions", "ga:users", "ga:transactionsPerSession", "ga:transactionRevenue", "ga:revenuePerTransaction", "ga:transactionRevenuePerSession", "ga:refundAmount", "ga:revenuePerUser", "ga:totalRefunds", "ga:transactionsPerUser"]
    },
    {
      "days": 15,
      "ms_table": "table_4",
      "view_id": "ga:00000000",
      "dimensions": ["ga:date"],
      "metrics": ["ga:pageLoadTime"]
    }
  ]
}

Описание всех узлов файла конфигурации:

mssql_con — параметры подключения к Microsoft SQL Server;

• PWD — пароль пользователя;
• Database — имя базы данных;
• Driver — название ODBC-драйвера (посмотреть название можно в диспетчере ODBC-источников данных);
• Server — IP сервера, на котором развернут SQL Server;
• UID — имя пользователя;
• Port — порт подключения;

google_analytics_con — учетные данные приложения в Google Cloud Console;

google_analytics_report — описание запросов к API Google Analytics (количество запросов не ограничено)

• dimensions — параметры, запрашиваемые в запросе. Можно указывать до 7 параметров в одном запросе. При этом необходимо предварительно проверить сочетание всех параметров между собой и выбранными показателями. Для проверки используйте специальный инструмент от Google. Среди запрашиваемых параметров обязательно должен присутствовать ga:date;
• ms_table — название таблицы, в которую будут записаны данные по запросу;
• days — количество дней, за который необходимо проверять наличие данных в базе;
• view_id — ID представления в Google Analytics, из которого будут запрашиваться данные;
• metrics — набор показателей, которые вы хотите получить из Google Analytics. Необходимо указать как минимум одну метрику в запросе. При этом ограничения на их максимальное количество нет. Все запрашиваемые показатели должны сочетаться между собой и со всеми указанными параметрами. Проверить это можно с помощью специального инструмента от Google.

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

Состав коннектора ga2mssql

Коннектор состоит из архива файлов:

• GA2MSSQL.bat — запускной файл коннектора для сбора и проверки данных за прошлый период;
• GA2MSSQL_today.bat — запускной файл коннектора для сбора данных за текущий день;
• config.json — файл конфигурации и управления коннектором;
• package_installer.bat — файл для установки необходимых пакетов;
• auth.rds — файл для хранения учетных данных для доступа к Google Analytics;
• ga2mssql_scr.R — код коннектора для загрузки и проверки наличия данных за прошлый период;
• ga2mssql_scr.Rout — лог последнего запуска скрипта ga2mssql_scr.R;
• ga2mssql_today_scr.R — код коннектора для загрузки и проверки наличия данных за текущий день;
• ga2mssql_today_scr.Rout — лог последнего запуска скрипта ga2mssql_today_scr.R.

Установка и настройка расписания сбора данных через коннектор

Коннектор ga2mssql — кроссплатформенный. Он будет работать на Windows, Unix и MacOS и других операционных системах.

В нашем случае коннектор установили на Windows Server.

Для установки коннектора на Windows нужно:

1. Установить язык R.

2. Прописать в переменную окружения PATH путь к папке bin (в директорию, куда был установлен Язык R). По умолчанию для версии R 3.6.1 путь будет «C:\Program Files\R\R-3.6.1\bin».
3. Распаковать архив с коннектором ga2mssql.zip в корень диска C. Путь к коннектору: «C:\ga2mssql».
4. Перед настройкой коннектора запустить от имени администратора файл package_installer.bat для установки требуемых пакетов.
5. Предоставить пользователю, указанному в файле конфигурации в узле mssql_con:UID в Microsoft SQL Server, права на запись и редактирование данных, а также на создания таблиц.

Далее в планировщике заданий Windows необходимо создать задачи:

• ежедневный запуск файла GA2MSSQL.bat для сбора данных за прошлый период;
• ежечасный запуск файла GA2MSSQL_today.bat для обновления данных за текущий день.

Комментарий клиента

Выводы

Существуют готовые решения по сбору данных из API Google Analytics в Microsoft SQL Server, но их стоимость достаточно высокая. Как правило, нужны регулярные оплаты подписки в течении всего периода использования.

Преимущества коннектора ga2mssql:

1. Вы самостоятельно задаете расписание обновления данных.
2. Можно задать любое количество собственных, пользовательских запросов в API Google Analytics.
3. Коннектор обходит ограничение в 10 показателей на 1 запрос к API.
4. ga2mssql — кроссплатформенный, разница состоит только в настройке расписания запуска.
5. Достаточно легко переключить коннектор ga2mssql на работу с любой другой базой данных, включая MySQL, PostgreSQL, BigQuery, ClickHouse и другие.