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

< >

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

Задача

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

То есть необходимо разработать инструмент, с помощью которого «Металл профиль» мог бы отправлять любое количество пользовательских запросов к API Google ***ytics. И не просто отправлять, а делать это с использованием любых параметров и показателей, которые возможно запрашивать в рамках одного запроса. А затем — записывать полученные данные в MS SQL.

Это решение помогает обогатить внутренние данные из базы данных заказчика (в том числе о продажах) данными из Google ***ytics о поведении пользователей сайта. В первую очередь это нужно, чтобы увидеть полноценную картину оффлайн- и онлайн-заказов.

Это только один из примеров использования коннектора. Финальный результат кастомизированной веб-аналитики зависит от конкретных пожеланий клиента и четкого технического задания.

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

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

Инструменты

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

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

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

1. В R загружаются настройки коннектора из файла конфигурации, который содержит описание всех запросов и других требуемых настроек в JSON-структуре.
2. По очереди каждый запрос отправляется в API Google ***ytics. Полученный результат дополнительно обpaбатывается в 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_***ytics_con\": { \"googleAuthR.client_id\": \"000000000000-xxxxxxxxxxxxxxxxx.apps.googleusercontent.com\", \"googleAuthR.client_secret\": \"xxxxxxxxxxxxxxxxxxxxx\", \"googleAuthR.scopes.selected\": [\"https://www.googleapis.com/auth/***ytics.edit\", \"https://www.googleapis.com/auth/***ytics\"] }, \"google_***ytics_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_***ytics_con — учетные данные приложения в Google Cloud Console;

google_***ytics_report — описание запросов к API Google ***ytics (количество запросов не ограничено)

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

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

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

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

• GA2MSSQL.bat — запускной файл коннектора для сбора и проверки данных за прошлый период;
• GA2MSSQL_today.bat — запускной файл коннектора для сбора данных за текущий день;
• config.json — файл конфигурации и управления коннектором;
• package_installer.bat — файл для установки необходимых пакетов;
• auth.rds — файл для хранения учетных данных для доступа к Google ***ytics;
• 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 в интернет-маркетинге — дайджест полезных публикаций Установить язык 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 для обновления данных за текущий день.

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

Анастасия Чистова, ведущий менеджер-аналитик в компании «Металл профиль»

Запуск коннектора мы встроили в общее расписание заданий MSSQL с запуском ежечасно для получения актуальной информации и раз в день. На протяжении двух недель тестирования коннектор отработал без сбоев и обеспечил загрузку полного объема данных, определенных настроенным представлением. Также мы настроили дополнительные запросы данных Google ***ytics по трафику — благодаря структуре коннектора это выполнено достаточно гибко.

Выводы

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

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

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

{\"0\":{\"lid\":\"1531306243545\",\"ls\":\"10\",\"loff\":\"\",\"li_type\":\"nm\",\"li_name\":\"name\",\"li_ph\":\"Имя\",\"li_req\":\"y\",\"li_nm\":\"name\"},\"1\":{\"lid\":\"1573230091466\",\"ls\":\"20\",\"loff\":\"\",\"li_type\":\"ph\",\"li_name\":\"phone\",\"li_req\":\"y\",\"li_masktype\":\"a\",\"li_maskcountry\":\"UA\",\"li_nm\":\"phone\"},\"2\":{\"lid\":\"1573567927671\",\"ls\":\"30\",\"loff\":\"y\",\"li_type\":\"in\",\"li_name\":\"surname\",\"li_ph\":\"Фамилия\",\"li_req\":\"y\",\"li_nm\":\"surname\"},\"3\":{\"lid\":\"1531306540094\",\"ls\":\"40\",\"loff\":\"\",\"li_type\":\"in\",\"li_name\":\"domains\",\"li_ph\":\"Адрес сайта\",\"li_rule\":\"url\",\"li_req\":\"y\",\"li_nm\":\"domains\"},\"4\":{\"lid\":\"1573230077755\",\"ls\":\"50\",\"loff\":\"\",\"li_type\":\"em\",\"li_name\":\"email\",\"li_ph\":\"Email\",\"li_req\":\"y\",\"li_nm\":\"email\"},\"5\":{\"lid\":\"1575903646714\",\"ls\":\"60\",\"loff\":\"\",\"li_type\":\"hd\",\"li_name\":\"comment\",\"li_value\":\"Автоматический коммент: заявка из блога, без пользовательского комментария\",\"li_nm\":\"comment\"},\"6\":{\"lid\":\"1575903664523\",\"ls\":\"70\",\"loff\":\"\",\"li_type\":\"hd\",\"li_name\":\"lead_channel_id\",\"li_value\":\"24\",\"li_nm\":\"lead_channel_id\"},\"7\":{\"lid\":\"1584374224865\",\"ls\":\"80\",\"loff\":\"\",\"li_type\":\"hd\",\"li_name\":\"ip\",\"li_nm\":\"ip\"},\"8\":{\"lid\":\"1609939359940\",\"ls\":\"90\",\"loff\":\"\",\"li_type\":\"hd\",\"li_name\":\"post_id\",\"li_nm\":\"post_id\"}} Хочу быстро найти клиентов онлайн