«Металл профиль» — как мы запустили кастомный коннектор для загрузки данных из API Google ***ytics в Microsoft SQL Server
Расскажу, как с помощью инструментов веб-аналитики мы получили расширенные данные о посещениях сайта для нашего клиента — компании «Металл профиль», производителя тонколистовых кровельных и стеновых материалов на территории России и СНГ.
Задача
Большая часть данных клиента уже собиралась в развернутом на базе Microsoft SQL Server хранилище. Наша задача — расширить эти данные на основе информации о посещениях сайта, полученной из API Google ***ytics.То есть необходимо разработать инструмент, с помощью которого «Металл профиль» мог бы отправлять любое количество пользовательских запросов к API Google ***ytics. И не просто отправлять, а делать это с использованием любых параметров и показателей, которые возможно запрашивать в рамках одного запроса. А затем — записывать полученные данные в MS SQL.
Это решение помогает обогатить внутренние данные из базы данных заказчика (в том числе о продажах) данными из Google ***ytics о поведении пользователей сайта. В первую очередь это нужно, чтобы увидеть полноценную картину оффлайн- и онлайн-заказов.
Это только один из примеров использования коннектора. Финальный результат кастомизированной веб-аналитики зависит от конкретных пожеланий клиента и четкого технического задания.
API периодически дает сбои, поэтому дополнительно нужна проверка наличия данных за прошлый период. Для каждого запроса период проверки наличия данных важно задавать индивидуально. Также в каждом запросе индивидуально нужно уметь указывать представление Google ***ytics, из которого требуется получить данные.
Данные за текущий день необходимо обновлять раз в час. Утром необходимо обновить данные за предыдущий день и проверить наличие данных за указанный под каждый запрос период. При необходимости — загрузить данные (если они отсутствуют за какой-либо из прошедших дней).
Инструменты
Язык R в веб-аналитике и интернет-маркетинге — интервью с Алексеем Селезневым Коннектор, разработанный под решение поставленной задачи, полностью написан на
- google***yticsR — интерфейс для работы с API Google ***ytics;
- odbc — подключение к Microsoft SQL Server через ODBC интерфейс;
- jsonlite — чтение JSON-структур;
- dplyr — манипуляция с данными.
Схема работы коннектора ga2mssql
- В R загружаются настройки коннектора из файла конфигурации, который содержит описание всех запросов и других требуемых настроек в JSON-структуре.
- По очереди каждый запрос отправляется в API Google ***ytics. Полученный результат дополнительно обpaбатывается в 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_***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 нужно:
Язык R в интернет-маркетинге — дайджест полезных публикаций Установить язык
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 ***ytics по трафику — благодаря структуре коннектора это выполнено достаточно гибко.
Выводы
Существуют готовые решения по сбору данных из API Google ***ytics в Microsoft SQL Server, но их стоимость достаточно высокая. Как правило, нужны регулярные оплаты подписки в течении всего периода использования.
Преимущества коннектора ga2mssql:
- Вы самостоятельно задаете расписание обновления данных.
- Можно задать любое количество собственных, пользовательских запросов в API Google ***ytics.
- Коннектор обходит ограничение в 10 показателей на 1 запрос к API.
- ga2mssql — кроссплатформенный, разница состоит только в настройке расписания запуска.
- Достаточно легко переключить коннектор ga2mssql на работу с любой другой базой данных, включая MySQL, PostgreSQL, BigQuery, ClickHouse и другие.
Хочу быстро найти клиентов онлайн
Комментарии:
Сервисы для сбора поисковых фраз позволяют регулярно расширять семантическое ядро, обеспечивая сайту высокую видимость и увеличение трафика по тематическим запросам...
23 04 2024 3:19:10
Метод не для всех, но увлеченным веб-аналитикой точно понравится!...
22 04 2024 1:17:27
Как вести успешную рекламную кампанию в Японии, не зная языка....
21 04 2024 6:25:56
Рост дохода в шесть раз, ROMI +500% за пять месяцев....
20 04 2024 12:22:48
Лучшие фишки от спикеров конференции 8P 2020. В конце каждой конференции спикеры 8P собираются за круглым столом, чтобы поделиться пpaктически применимыми советами, которые можно внедрить в свой проект и сразу почувствовать отдачу...
19 04 2024 9:34:11
Как пользователи и разработчики приложений живут без Google Play? Авторская колонка....
18 04 2024 16:54:26
Как отключить блокировку API запрещенных сервисов в скриптах R и продолжать использовать пакеты ryandexdirect, rvkstat, rmytarget, Google Sheets и BigQuery...
17 04 2024 5:55:55
82% малых бизнесов тонут как раз из-за проблем с денежными потоками (по данным исследований U.S. Bank, 2020). Как предприниматели попадают в кассовые разрывы и что делать в таких ситуациях, разберем в этой статье....
16 04 2024 15:29:15
Украинские реалии того, в каких тематиках трафик из Yandex.Direct дороже Google Ads. Новое исследование Алексея Селезнева....
15 04 2024 6:58:13
Оживший бар. О том, как один украинский бренд за полтора месяца получил более 4 000 фанов!...
14 04 2024 13:21:10
Система индексирования страниц — как поисковик и его роботы разбирают перечень контента по полочкам...
13 04 2024 3:54:51
Анализ цены за клик в 25 тематиках и 852 городах Украины, России, Казахстана, Болгарии...
12 04 2024 3:59:34
Как автоматически формировать и выгружать готовые рекламные кампании для Яндекс.Директ и Google Ads со структурой «одна группа объявлений — один key»...
11 04 2024 21:27:29
Пост о том, как и когда тратит деньги американский и украинский покупатель...
10 04 2024 18:21:26
Система почтового маркетинга. Как удержать доверия подписчиков одним приветственным письмом...
09 04 2024 3:15:14
Мануал, по которому вы создадите специальный отчет, чтобы применить его на своем сайте....
08 04 2024 10:58:32
Настройка пикселя TikTok — короткий и понятный мануал. Устанавливаем пиксель вручную: вставка кода на сайт и интеграция через Install pixel code by 3rd party tool. Читать дальше....
07 04 2024 5:37:37
Для эффективной оценки работы кампаний не обойтись без вычисляемых показателей. Они экономят время PPC-специалиста и позволяют правильно анализировать рекламные кампании....
06 04 2024 18:28:12
Как протестировать MVP мобильного приложения, получить обратную связь и сформировать гипотезы...
05 04 2024 8:16:40
UTM-метки позволяют эффективно отслеживать источники трафика. Но составлять их нужно правильно. Как именно, давайте разбираться....
04 04 2024 17:46:47
Динамический ремаркетинг: как вернуть посетителя на сайт и продать, когда человеку «надо ещё подумать»....
03 04 2024 13:21:22
Низкочастотные, низкоконкурентные, Long Tail и другие термины, которые нужно знать и понимать....
02 04 2024 16:39:23
C какими сложностями пришлось столкнуться при создании интернет-магазина....
01 04 2024 4:56:35
Неля Серебро рассказывает про внутреннюю кухню блога Netpeak...
31 03 2024 11:18:28
Мануал по Microsoft Power BI — мощному инструменту для бизнес-аналитики. Освоив эту платформу, вы сможете с легкостью создавать понятные отчеты и обновлять их в режиме реального времени....
30 03 2024 4:13:37
В этом году часть пользователей предпочла вернуться к привычным покупкам в оффлайн-магазинах....
29 03 2024 17:48:26
Сооснователь SUPERLUDI Влад Ноздрачев рассказал в своем подкасте о развитии инфобизнеса. Предлагаем вам вольный пересказ....
28 03 2024 22:32:48
Как мы проводили самую летнюю конференцию в условиях постлокдayна, пандемии и неизвестности....
27 03 2024 1:12:48
То, как вы читаете на мониторе или в смартфоне, значительно отличается от чтения книги или печатного журнала. Когда вы пишете текст для веба, очень важно знать об этой особенности....
26 03 2024 18:48:32
Спикер ОА Петр Аброськин рассказал всем читателям блога о фишках настройки ремаркетинга в контекстной рекламе и социальных сетях...
25 03 2024 21:18:22
Что учитывать при продвижении недвижимости, клиник красоты, магазинов косметики, сайтов по продаже семян, шин?...
24 03 2024 14:56:56
Настройка целей в Google ***ytics, а также отслеживание событий....
23 03 2024 16:24:29
Самые популярные блоги Рунета по интернет-маркетингу — все RSS-фиды в одном месте...
22 03 2024 2:13:14
Онлайн-корпоративы стали трендом 2020 года, но останутся с нами надолго если не в чистом, то в гибридном формате. Делимся опытом организации дня рождения компании в Zoom....
21 03 2024 14:27:40
Правила преобразования фидов в Google Merchant Center помогут сегментировать товары и сделать вашу рекламу более эффективной...
20 03 2024 11:59:41
Как специалисту с нуля обучиться контекстной рекламе и совмещать работу с хобби...
19 03 2024 9:29:24
Чтобы сделать что-то лучше, иногда надо довести это до абсурда. Так думают приверженцы теории «странных изделий» — chindogu...
18 03 2024 15:36:40
Снимать скрин нужно красиво и логично, вне зависимости от того, кому вы его покажете: начальнику, другу или многотысячной аудитории блога...
17 03 2024 5:44:15
Как не нужно делать, запуская свой профиль и таргетированную рекламу в Instagram...
16 03 2024 19:40:55
Один из самых известных отчетов в Google ***ytics «Аудитория/Обзор». Но кроме него есть еще несколько, которые будут весьма полезны для вашего блога. Какие именно, читайте дальше....
15 03 2024 17:42:58
Почему нельзя просто взять и перевести. В худшем случае специализированный текст на иностранном языке должен стать для переводчика дорожной картой, в лучшем — источником вдохновения. Почему? Читайте об этом!...
14 03 2024 19:14:13
Первая реакция на перегорание — послать все к чертям собачьим. Очень заманчиво, но правильно ли убегать от всех проблем?...
13 03 2024 10:15:37
Как стать лидером ниши не на словах, а на деле. Пошаговый алгоритм....
12 03 2024 3:18:27
Книга про пять полезных фишек + стратегия продвижения интернет-магазина...
11 03 2024 20:13:20
Четкий мануал для настройки Яндекс.Аудиторий от создания сегмента, работы с геолокацией, пикселем аудитории, похожими аудиториями, ретаргетингом и до корректировки ставок...
10 03 2024 12:42:42
Мотивация сотрудников к работе с текстами. Опыт бюро копирайтинга Texbroker...
09 03 2024 18:43:21
Масштабируем кампанию в Google за счет управления конверсиями и работы с эффективными каналами продвижения....
08 03 2024 6:11:32
Инструменты веб-мастера: что может заменить Google ***ytics?...
07 03 2024 16:49:41
Эффективность продвинутой сегментации стала заметна уже в первый месяц ее функционирования....
06 03 2024 20:59:16
Supplemental results, они же «сопли» или дополнительные результаты — страницы и документы сайта, которые не находятся в основном индексе Google...
05 03 2024 4:49:19
Еще:
понять и запомнить -1 :: понять и запомнить -2 :: понять и запомнить -3 :: понять и запомнить -4 :: понять и запомнить -5 :: понять и запомнить -6 :: понять и запомнить -7 ::