«Металл профиль» — как мы запустили кастомный коннектор для загрузки данных из API Google Analytics в Microsoft SQL Server
Расскажу, как с помощью инструментов веб-аналитики мы получили расширенные данные о посещениях сайта для нашего клиента — компании «Металл профиль», производителя тонколистовых кровельных и стеновых материалов на территории России и СНГ.
Задача
То есть необходимо разработать инструмент, с помощью которого «Металл профиль» мог бы отправлять любое количество пользовательских запросов к API Google Analytics. И не просто отправлять, а делать это с использованием любых параметров и показателей, которые возможно запрашивать в рамках одного запроса. А затем — записывать полученные данные в MS SQL.
Это решение помогает обогатить внутренние данные из базы данных заказчика (в том числе о продажах) данными из Google Analytics о поведении пользователей сайта. В первую очередь это нужно, чтобы увидеть полноценную картину оффлайн- и онлайн-заказов.
Это только один из примеров использования коннектора. Финальный результат кастомизированной веб-аналитики зависит от конкретных пожеланий клиента и четкого технического задания.
API периодически дает сбои, поэтому дополнительно нужна проверка наличия данных за прошлый период. Для каждого запроса период проверки наличия данных важно задавать индивидуально. Также в каждом запросе индивидуально нужно уметь указывать представление Google Analytics, из которого требуется получить данные.
Данные за текущий день необходимо обновлять раз в час. Утром необходимо обновить данные за предыдущий день и проверить наличие данных за указанный под каждый запрос период. При необходимости — загрузить данные (если они отсутствуют за какой-либо из прошедших дней).
Инструменты
Коннектор, разработанный под решение поставленной задачи, полностью написан на
- googleAnalyticsR — интерфейс для работы с API Google Analytics;
- odbc — подключение к Microsoft SQL Server через ODBC интерфейс;
- jsonlite — чтение JSON-структур;
- dplyr — манипуляция с данными.
Схема работы коннектора ga2mssql
- В R загружаются настройки коннектора из файла конфигурации, который содержит описание всех запросов и других требуемых настроек в JSON-структуре.
- По очереди каждый запрос отправляется в API Google Analytics. Полученный результат дополнительно обрабатывается в R.
- Полученные данные передаются в 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 нужно:
Установить язык
R. - Прописать в переменную окружения PATH путь к папке bin (в директорию, куда был установлен Язык R). По умолчанию для версии R 3.6.1 путь будет «C:\Program Files\R\R-3.6.1\bin».
- Распаковать архив с коннектором ga2mssql.zip в корень диска C. Путь к коннектору: «C:\ga2mssql».
- Перед настройкой коннектора запустить от имени администратора файл package_installer.bat для установки требуемых пакетов.
- Предоставить пользователю, указанному в файле конфигурации в узле mssql_con:UID в Microsoft SQL Server, права на запись и редактирование данных, а также на создания таблиц.
Далее в планировщике заданий Windows необходимо создать задачи:
- ежедневный запуск файла GA2MSSQL.bat для сбора данных за прошлый период;
- ежечасный запуск файла GA2MSSQL_today.bat для обновления данных за текущий день.
Комментарий клиента
Запуск коннектора мы встроили в общее расписание заданий MSSQL с запуском ежечасно для получения актуальной информации и раз в день. На протяжении двух недель тестирования коннектор отработал без сбоев и обеспечил загрузку полного объема данных, определенных настроенным представлением. Также мы настроили дополнительные запросы данных Google Analytics по трафику — благодаря структуре коннектора это выполнено достаточно гибко.
Выводы
Существуют готовые решения по сбору данных из API Google Analytics в Microsoft SQL Server, но их стоимость достаточно высокая. Как правило, нужны регулярные оплаты подписки в течении всего периода использования.
Преимущества коннектора ga2mssql:
- Вы самостоятельно задаете расписание обновления данных.
- Можно задать любое количество собственных, пользовательских запросов в API Google Analytics.
- Коннектор обходит ограничение в 10 показателей на 1 запрос к API.
- ga2mssql — кроссплатформенный, разница состоит только в настройке расписания запуска.
- Достаточно легко переключить коннектор ga2mssql на работу с любой другой базой данных, включая MySQL, PostgreSQL, BigQuery, ClickHouse и другие.
По теме
Как увеличить количество ежемесячных подписок на 100% — кейс kulibin.com.ua
Благодаря внедрению интерактивной формы подписки увеличили количество ежемесячных подписок на 100%, доход — на 51%, а коэффициент транзакций — на 71%.
Рекламные источники получения подписчиков в Telegram. Кейс канала Артёма Бородатюка
Полтора года работы и 32 тысячи привлеченных подписчиков. Рассказываем о плюсах и минусах продвижения телеграм-канала.
Как реклама доставки черной пиццы принесла ROMI 616% — кейс Neropizza
Поисковая реклама для службы доставки еды — практические советы, как выделиться на фоне конкурентов в популярной нише. На примере харьковской доставки пиццы мы покажем, как правильно продавать еду. Читайте в статье!
Свежее
Ежемесячная подписка в Telegram — что это значит и кому может быть полезна
Субъективный рейтинг новых функций мессенджера от Head of Telegram Network в Netpeak.
Как подготовить и написать предложение блогеру в Instagram
Чтобы эффективно продвигать товар или услугу в Instagram, необходимо повысить охват целевой аудитории. Одно из возможных решений — привлечение блогеров. Казалось бы, достаточно предложить платное размещение рекламы…
Кейс Office-Expert.kz. Как дополнительно стимулировать покупки на 50 млн тенге в месяц
Мы решили внедрить товарные рекомендации на сайте. Они приносят дополнительно до 10% дохода.