О веб-аналитике, интернет-рекламе и анализе данных.
Сергей Макаров
О веб-аналитике, интернет-рекламе и анализе данных.

Современный ELT стек digital аналитика: dbt, BigQuery, Sinter и Renta

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

Решить эту задачу можно 100500 разными способами.

Например, когда я был совсем маленьким, то просто использовал Google Sheets: плагин GA, остальные данные заливал вручную. Настроить это дело просто, но на этом плюсы и кончаются. Когда у вас больше 10 запросов, то спредшит начинает дико тормозить. А ещё семплинг. А ещё почти нет коннекторов.

Потом появился Power BI. В сравнении со спредшитами это небо и земля. Вытянуть данные можно из любого источника. Для трансформации данных не нужно знать программирование и даже формулы (как в Excel): все происходит в кнопочном интерфейсе.
Шло время, число запросов Power Query росло, да и модель данных все увеличивалась и увеличивалась в размерах. Дошло до того, что я ставил обновляться модель и уходил на обед. Возвращался, а модель все обновлялась (ну а потом падала с ошибкой, fuuuu).

Я погрустил немного, а потом пошел гуглить.
В итоге через пару месяцев у меня был BigQuery, 10 скриптов на R и cron.

Естественно, все это не взлетело, как хотелось бы. Apiшки иногда не отвечали, cron падал. Куча скриптов по разным папкам очень скоро стали неуправляемыми. Вышел из отпуска — уже и не разберешь когда и где какая обработка идёт. В общем, хаос.

Я снова пошел гуглить и наткнулся на клёвую статью в блоге Mode.

Не хочу пересказывать, поэтому выделю основную суть.

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

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

Статья очень хорошо зашла, и я решил дать себе ещё один шанс «сделать нормально».
Далее я расскажу о каждом микросервисе отдельно и о том, как выбирал и соединял их друг с другом.

!Дисклеймер:

Я никакой не Data Engineer и не профессионал в data modeling.

Думаю, эта статья для таких же ребят, как и я, для аналитиков, у которых нет команды BI и которые также, как и я, страдают от несовершенства аналитического мира.

Что мы знаем о Data warehouse

industrial-hall-1630740_1920-e1507904049620

В аналитической инфраструктуре DWH занимает центральное место.

Этим местом может быть любая современная аналитическая бд: BigQuery, Amazon Redshift‎ или Snowflake. Все они облачные, дешевые и очень быстрые.

Туда мы загружаем данные, а затем обрабатываем и моделируем. Процесс загрузки и обработки иногда называют ELT(extract — load — transform).

К сожалению, такие вещи, как ClickHouse или Hadoop, не подходят для простых смертных, так как там много инженерной работы и под них мало коннекторов. А нам ведь нужно как можно проще и модульнее.

Я выбрал BigQuery. Для BigQuery есть коннекторы к российским рекламным системам и системам аналитики, а еще можно пользоваться бесплатно полгода. Чтобы начать, нужно просто перейти по ссылке.

Микросервисы для pre-proccesing и загрузки

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

Например, подключиться к API рекламной системы и спарсить JSON.

Очень муторная работа. Поэтому если вы делегируете эту часть другим сервисам, то сэкономите кучу денег и времени.

Самые популярные сервисы для этого Stitch  и Fivetran.

На российском рынке — Renta и R7K12.

Я использую оба сервиса, но пользоваться Рентой немного приятнее.

Обработка и моделирование

В этой сфере очень много инструментов, как дорогих enterprise, так и open source.

Сначала я пытался освоить Airflow — open source разработка от Airbnb. К сожалению, я так и не настроил ни одного data pipeline с помощью Airflow. Скорее всего, не хватило скилла.

В статье выше упоминался dbt, и я решил его потестить.
Вообще, dbt — это тема для отдельной статьи, но если вкратце, то dbt — это инструмент командной строки, который помогает организовать SQL трансформации в полноценный проект.
Это значит, что есть логирование, тестирование, среды разработки и совместная работа через Git.

Вот пример самого примитивного репозитория dbt. Все SQL преобразования находятся в папке models.
Когда мы запускаем dbt run, то все эти запросы материализуются в нужный нам вид (вью, таблица или CTE).
Затем скомпилированную модель можно визуализировать с помощью SinterData:
12e441546b

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

Кстати, если вы знаете бесплатные сервисы наподобие dbt — напишите мне.

Scheduler

Это опциональный пункт в этой архитектуре, так как dbt можно запускать и по крону.

Но лучше все-таки это делать через Sinter, ведь гораздо приятнее оперировать джобами в одном окне с уведомлениями и другими дополнительными штуками.

Заключение

Я намеренно не рассказал о BI решениях, так как эта тема уже и так сильно разжевана. Используйте то, что вам нравится: Mode, Redash, Power BI или Tableau. Благодаря модульности архитектуры вы можете менять эти инструменты почти безболезненно.

Вот итоговая картинка:

fb48604c2a

Для себя я вынес главное преимущество использования ELT стека: меньше тратишь времени на техническую рутину и больше на моделирование и анализ.

Конечно, мир не идеален. Всегда будут источники данных, которые не покрыты микросервисами, да и сами сервисы тоже могут глючить (их делают люди, некоторые забесплатно). Но факт того, что у каждого человека с минимальными знаниями программирования есть возможность поднять данную архитектуру с нуля — это очень круто.

На этом все.
Пишите мне в FB или на почту, если есть комментарии или хотите продолжения.

0 комментариев

Почему страницы входа лучше анализировать в Метрике, чем в GA?

Опишу очередную недоделку GA.

Определяем симптом.
ga trafic

Почему неиндексированный поиск и страницы корзины получают столько трафика в отчетах?

Все дело в переопределении сеанса и стандартной модели атрибуции GA. Всеми любимый пример с чаем: пользователь зашел с organic → набрал товаров и перешел в корзину → пошел пить чай на 30 минут → переоткрыл корзину → в GA уходит новая сессия с параметрами: канал = organic и landingPagePath = корзина.

Самый быстрый способ лечения: анализировать страницы входа в метрике. Только атрибуцию выставить “Последний переход”.

0 комментариев

Native js vs jquery для отслеживания событий

Всегда было интересно, что лучше использовать для отслеживания событий: native js или jquery.

Решил проверить 3 селектора: querySelectorAll, getElementsByClassName и $(‘.classname’).

Первый тест на скорость. Вот результаты с jsperf.com

d58181aa38

Метрика operations per second — количество исполнений скрипта в секунду.

По чарту очевидно: getElementsByClassName быстрее остальных. На втором месте селектор jquery, а затем querySelectorAll.

Теперь сравним селекторы в бою. Настроим отслеживание клика одного элемента и проверим количество событий в GA.


// Отслеживание клика на категории рекомендаций в карточке jquery
$('.classname').on('click', function (){ 
	dataLayer.push({
        'event':'addEvents_makeActions',
        'action_cat':'Карточка',
        'action_name':'Рекомендации jQuery',
        'action_param': ''
     });
});

// Отслеживание клика на категории рекомендаций в карточке querySelectorAll
var selector= document.querySelectorAll('.classname');
for (var i = 0; i < selector.length; i++) {
	selector[i].addEventListener('click', function(e) {
			dataLayer.push({
        		'event':'addEvents_makeActions',
        		'action_cat':'Карточка',
        		'action_name':'Рекомендации querySelectorAll',
        		'action_param': ''
      		});
	}, false)
}
   
// Отслеживание клика на категории рекомендаций в карточке getElementsByClassName
var selector = document.getElementsByClassName('classname');
for (var i = 0; i < selector.length; i++) {
	selector[i].addEventListener('click', function(e) {
			dataLayer.push({
        		'event':'addEvents_makeActions',
        		'action_cat':'Карточка',
        		'action_name':'Рекомендации getElementsByClassName',
        		'action_param': ''
      		});
	}, false)
}

Результаты:

e6b21ea22f

Я сделал такой вывод: если на сайте уже стоит jquery, то используйте, что хотите. Если jquery нет, то не стоит его ставить отдельно, лучше трекать через js.

0 комментариев

Загрузка сегментов в GA с помощью googleAnalyticsR

Расскажу, как передавать сегменты клиентов в Google Analytics, чтобы использовать в списках ремаркетинга AdWords.

Требования:
— У вас настроен сбор client id. Если нет, то Simo Ahava описал хороший способ.
— Вы настроили кастомный параметр, в который хотите передавать сегмент. В данном примере это dimension18.
— Вы можете связать cid и реальные покупки клиента. У меня  id транзакции в GA совпадает с id транзакции CRM, поэтому я смогу сделать связку.
— Нужны данные о продажах. Ограничусь минимальным набором: дата покупки, id клиента, сумма покупки.
— R и RStudio. Как установить googleAnalyticsR.

Получаем данные из CRM. В моем случае это продакшн база сайта.


library(DBI)
library(RMySQL)
proddb = dbConnect(MySQL(), user="ваш_логин", password="ваш_пароль",
                   dbname="название_бд", host="ваш_хост", port="номер_порта")

mysql_data = dbGetQuery(proddb, "SELECT orderId, date, phone, price from table")

Получили данные. Проверим на выбросы.


boxplot(mysql_data$price)$out

Нашли аутлаеров и скорректировали запрос к бд. Идем дальше.

Делаем сегментацию. Можно начать с простой RFM.


library(easyRFM)
result = rfm_auto(mysql_data,id="phone",payment="price",
                   date="date")
rfm_data = result$rfm
rfm_data$rfm = paste(rfm_data$RecencyClass,rfm_data$FrequencyClass,rfm_data$MonetaryClass,sep = "")

Теперь сдожойним данные сегментации с cid.


# Подключаем библиотеки
library(googleAuthR)
library(googleAnalyticsR)

service_token = gar_auth_service("ваш_service_token.json")

# id представления GA
ga_view = 'id_представления'

# Указываем диапазон дат.
start_date = "2016-01-01"
end_date  = as.character.Date(Sys.Date() - 1)

# Выгружаем данные по транзакциям
ga_transactions = google_analytics_4(ga_view, 
                                     date_range = c(start_date,end_date),
                                     dimensions=c('transactionId','dimension1'), 
                                     metrics = c('transactionRevenue'),
                                     anti_sample = TRUE)

# Формируем таблицу для отправки в Google Analytics
library(sqldf)
get_rfm = sqldf("SELECT DISTINCT merge_data.dimension1 AS dimension1, rfm_data.rfm AS dimension18 FROM 
(SELECT * FROM mysql_data JOIN ga_transactions ON mysql_data.orderId = ga_transactions.transactionId) AS merge_data JOIN rfm_data ON merge_data.phone = rfm_data.phone")

head(get_rfm)
             dimension1 dimension18
1 1414965770.1485039352         455
2  273724521.1480928137         355
3  778576015.1471948435         555
4 1038165458.1456421858         555
5 1534727395.1487304449         555
6  882453544.1469718993         555

Получили сегмент для каждого cid. Теперь загрузим в GA. Есть 2 пути:
— Передать данные с помощью Custom Data import и Management API
— Передать с помощью Measurement Protocol

Custom Data import

Суть метода: формируем файл из нашего датафрейма и отправляем в GA с помощью Management API.


ga_import = ga_custom_upload_file(id_представления, 
                                  "id_ресурса", 
                                  "id_импорта", 
                                  get_rfm)
ga_custom_upload(upload_object = ga_import)

У этого метода серьезные ограничения.

  1. Нельзя загрузить больше 10 GB на ресурс. Для больших проектов этот лимит исчерпается быстро. Выход: удалять старые загрузки.
  2. Из справки: Users, who didn’t visit the site in the 10 days prior to the data upload, will be added to remarking lists upon their next visit to the site. На практике это означает, что списки ремаркетинга получатся урезанными. Если для вас это критично, то посмотрим следующий способ.

Measurement Protocol

Можно отправить данные с помощью Measurement Protocol. Имейте в виду, при отправке запросов в Google Analytics генерируется новая сессия.


library(googleMeasureR)
library(stringi)

for (row in 1:nrow(get_rfm)){
  random = stri_rand_strings(n=1, length=18, pattern="[A-Za-z0-9]")
  gmr_post(list(v=1,t="event",tid="id_ресурса",cid=get_rfm$dimension1[row],ec="rfm_send",ea="rfm_v1",cd18=get_rfm$dimension18[row],z=random))
}
0 комментариев