Сегодня поговорим о BI-платформе Looker, над которой мне удалось поработать в течение 2019-го года.

Представляю краткое содержание статьи для удобной и быстрой навигации:

1. Что такое Looker?
2. Как и к каким СУБД можно подключиться через Looker?
3. Построение Looker ML модели данных
4. Режим Explore (исследование данных на построенной модели)
5. Построение отчетов и сохранение их в Look
6. Примеры дашбордов в Looker

Что такое Looker?

Создатели Looker позиционируют его как программное обеспечение класса business intelligence и платформу big data аналитики, которая помогает исследовать, анализировать и делиться аналитикой бизнеса в режиме реального времени.
Looker — это действительно удобный инструмент и один из немногих продуктов класса BI, который позволяет в режиме онлайн работать с преднастроенными кубами данных (на самом деле, реляционными таблицами, которые описаны в Look ML-модели).
Инженеру, работающему над Looker, требуется описать модель данных на языке Look ML (что-то среднее между CSS и SQL), опубликовать эту модель данных и далее настроить отчетность и дашборды.
Сам Look ML достаточно прост, взаимосвязи между объектами данных задаются data-инженером, что впоследствии позволяет использовать данные без знания языка SQL (если быть точным: движок Looker сам за пользователя генерирует код на языке SQL).

Буквально недавно, в июне 2019-го года Google объявил о покупке платформы Looker за $2.6 млрд.

Как и к каким СУБД можно подключиться через Looker?

Выбор СУБД, с которыми работает Looker довольно богатый. На скриншоте ниже перечислены всевозможные подключения на Октябрь 2019 г.:

Доступные СУБД для подключения

Настроить подключение к базе данных несложно через веб-интерфейс:

Веб-интерфейс подключения к СУБД

В вопросе соединений с базами данных отдельно хочется отметить два факта: к сожалению, на текущий момент и в ближайшем будущем отсутствует поддержка Clickhouse от Яндекса. Скорее всего поддержка не появится, учитывая тот факт, что Looker был приобретен конкурирующей компанией Google.
Второй досадный факт состоит в том, что построить одну модель данных, которая бы обращалась в разные СУБД нельзя. В Looker нет встроенного хранилища, которое могло бы объединять результаты запроса (кстати, в отличии даже от того же Redash).
Это означает, что аналитическая архитектура данных должна быть построена в рамках одной СУБД (желательно с высоким быстродействием или на агрегированных данных).

Построение Looker ML модели данных

Для того чтобы построить отчет или дашборд в Looker, предварительно необходимо настроить модель данных. Синтаксис языка Look ML достаточно подробно описан в документации. От себя могу добавить, что описание модели не требует долгого погружения для специалиста со знанием SQL. Скорее, необходимо перестроить подход к подготовке модели данных. Язык Look ML очень похож на CSS:

Консоль создания Look ML модели

В модели данных прописываются связи с таблицами, ключи, гранулярность, информация о том, какие поля являются фактами, какие измерениями. Для фактов прописывается агрегация. Разумеется, при создании модели можно использовать различные IF / CASE выражения.

Режим Explore

Наверное, это самая главная киллер-фича Looker, поскольку позволяет любым бизнес-поздразделениям самостоятельно получать данные без привлечения аналитиков / инженеров данных. И, видимо, поэтому использование аккаунтов с использованием режиме Explore тарифицируется отдельно.

Фактически, режим Explore это интерфейс, который позволяет использовать настроенную Look ML модель данных, выбрать необходимые метрики и измерения и построить кастомный отчет / визуализацию.
К примеру, мы хотим понять сколько каких действий в интерфейсе Лукера было совершено на прошлой неделе. Для этого, используя режим Explore, мы выберем поле Date и поставим на него фильтр: прошлая неделя (Looker в этом смысле достаточно умный и в фильтре будет достаточно написать ‘Last week’), затем из измерений выберем «Категорию» и в качестве метрики — Количество. После нажатия кнопки Run сформируется готовый отчет.

Построение отчета в Looker

Затем, используя полученные данные в табличной форме можно настроить визуализацию любого вида. Например, Pie chart:

Применение визуализации к отчету

Построение отчетов и сохранение их в Look

Полученный набор данных / визуализацию в режиме Explore иногда хочется сохранить и поделиться с коллегами, для этого в Looker существует отдельная сущность — Look. Это готовый построенный отчет с выбранными фильтрами / измерениями / фактами.

Пример сохраненного Look

Примеры дашбордов в Looker

Систематизируя склад созданных Look зачастую хочется получить готовую композицию / overview ключевых метрик, которые было бы видно на одном листе.
Для этих целей отлично подходит создание дашборда. Дашборд создается либо на лету, либо используя ранее созданные Look. Одной из «фишек» дашборда является установка параметров, которые меняются на всем дашборде и могут быть применены ко всем Look сразу.

Интересные фишки одной строкой

• В Looker можно ссылаться на другие отчеты и, используя данных функционал, можно создать динамический параметр, который передается по ссылке.
  Например, построили отчет с разделениям выручки по странам и в этот отчете можем ссылаться на дашборд по отдельно взятой стране. Переходя по ссылке, пользователь видит дашборд по конкретной стране, на которую перешел
• На каждой странице Looker существует чат, в котором оперативно отвечает поддержка
• Looker не умеет работать с merge данных на уровне разных СУБД, однако может объединить данные на уровне готовых Look (в нашем случае эта функциональность работает очень странно).
• В рамках работы с различными моделями данных, я обнаружил крайне нетривиальное использование SQL для подсчета уникальных значений в ненормализованный таблице данных, Looker называет это симметричными агрегатами.
  SQL действительно выглядит очень нетривиально:

SELECT 
 order_items.order_id AS "order_items.order_id",
 order_items.sale_price AS "order_items.sale_price",
 (COALESCE(CAST( ( SUM(DISTINCT (CAST(FLOOR(COALESCE(users.age ,0)
 *(1000000*1.0)) AS DECIMAL(38,0))) + 
 CAST(STRTOL(LEFT(MD5(CONVERT(VARCHAR,users.id )),15),16) AS DECIMAL(38,0))
 * 1.0e8 + CAST(STRTOL(RIGHT(MD5(CONVERT(VARCHAR,users.id )),15),16) AS DECIMAL(38,0)) ) 
 - SUM(DISTINCT CAST(STRTOL(LEFT(MD5(CONVERT(VARCHAR,users.id )),15),16) AS DECIMAL(38,0))
 * 1.0e8 + CAST(STRTOL(RIGHT(MD5(CONVERT(VARCHAR,users.id )),15),16) AS DECIMAL(38,0))) ) 
 AS DOUBLE PRECISION) 
 / CAST((1000000*1.0) AS DOUBLE PRECISION), 0) 
 / NULLIF(COUNT(DISTINCT CASE WHEN users.age IS NOT NULL THEN users.id 
 ELSE NULL END), 0)) AS "users.average_age"
FROM order_items AS order_items
LEFT JOIN users AS users ON order_items.user_id = users.id

GROUP BY 1,2
ORDER BY 3 DESC
LIMIT 500

• При внедрении Looker к покупке обязателен JumpStart Kit, который стоит не менее $6k, в рамках которого вы получаете поддержку и консультации от Looker при внедрении инструмента.

В Redash реализован удобный функционал построения алертов. Под алертами подразумеваются оповещения, возникающие при изменении какого-то конкретного показателя. При этом степень изменения пользователь задает самостоятельно вручную в интерфейсе. Алерты можно настроить на почту или в канал / личные сообщения Slack.

Вспомним как собрать данные, используя Google Analytics, и для этих данных настроим оповещения, используя внутреннюю БД Redash (query-results). В качестве основания для оповещения нас будет интересовать падение числа пользователей на сайте за вчерашний день более, чем на 30%.

Построение запроса к Google Analytics

{
    "ids": "ga:128886640",
    "start_date": "30daysAgo",
    "end_date": "yesterday",
    "metrics": "ga:users", 
    "dimensions": "ga:date"
}

В результате выполнения данного запроса мы получим число пользователей за последние 30 дней.

Включаем хранение query results

Одной из основных фишек redash считается возможность обращения к результатам выполнения запроса, которые хранятся во внутренней СУБД SQLite.
Для того, чтобы включить хранение результатов необходимо перейти в Data Sources и включить query-results (beta).

Теперь простой командой:

select * from query_37

Мы получим аналогичный результат, что дает и предыдущий запрос к GA, но с возможностью использования языка SQL для обработки полученного сета данных.

Строим запрос для оценки изменений в числе пользователей

Для того, чтобы настроить оповещение необходимо в начале написать запрос, которые выдаст целевой показатель для проверки, в нашем случае — рост или падение числа пользователей на сайте.
Напишем запрос, который обращается ко внутренней СУБД Redash:

SELECT sum(CASE WHEN date(ga_date)=DATE('now', '-1 day') THEN ga_users ELSE NULL END) AS yesterday,
sum(CASE WHEN date(ga_date)=DATE('now', '-2 day') THEN ga_users ELSE NULL END) AS before_yesterday,
(sum(CASE WHEN date(ga_date)=DATE('now', '-1 day') THEN ga_users ELSE NULL END)*1.0/
sum(CASE WHEN date(ga_date)=DATE('now', '-2 day') THEN ga_users ELSE NULL END)*1.0-1)*100 AS difference
FROM query_37

В запросе выше мы считаем число пользователей за вчера и за позавчера, а также оцениваем изменение числа пользователей в процентах.
В текущем примере получили следующую таблицу данных, которую дальше будем использовать при настройке алерта:

Теперь, чтобы мы могли получать алерты, необходимо настроить выполнение запросом по расписанию (регулярное обновление результатов / scheduled query в терминах redash).

Поставим обновление на 10 утра каждого дня:

Настраиваем оповещение

Переходим в меню Create — Alert. Вводим название запроса, в моем случае — это «Alert on users».
Далее, можем поменять отображаемое название нотификации или оставить то, что предлагается системой.

Выбираем целевую метрику, в рассматриваемом примере — difference. Ниже в операторе сравненения (Op) выбираем less then и устанавливаем значение -30.

В блоке справа на странице необходимо выбрать куда именно будет отправляться алерт. Подробно о настройке пути следования алерта можно прочитать на официальном сайте Redash.

Теперь алерт появился на странице со списков алертов и по умолчанию находится в статусе OK.

Как только показатель превысит заданный нами уровень статус изменится на TRIGGERED, а оповещение отправится на почту / в Slack.

Больше по теме

• Настройка алертов на странице Redash
• Добавление Slack в качестве адреса для уведомлений

Две недели назад произошел финальный релиз Redash версии 8. Полный список усовершенствований представлен на странице релиза beta версии v8.

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

• Support for multi-select in dropdown (and query dropdown) parameters.
• Support for dynamic values in date and date-range parameters.
• Search dropdown parameter values.
• Pivot Table: support hiding totals.
• New Visualization: Details View.

Support for multi-select in dropdown (and query dropdown) parameters.

В 8-ой версии Redash появилась долгожданная поддержка выбора нескольких значений в параметре типа «выпадающий список» или «выпадающий список на основе запроса»:

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

Для нас это означает возможность использования в запросах условия типа IN с параметром:

При использовании таких значений параметра, мы получим следующий URL в строке браузера:

Support for dynamic values in date and date-range parameters.

Максимально удобная и полезная фича для выбора дат, которая часто используется в Tableau, но до сих пор не была реализована в Redash: использование динамических значений для дат:

При нажатии на молнию, можно выбрать произвольный период и Redash самостоятельно подставит нужные значения в запрос. Так, например, можно быстро посмотреть статистику за последнюю неделю или последний месяц.

Search dropdown parameter values.

Допустим, мы используем параметр типа «выпадающий список на основе запроса», и в выбранном запросе более 300 результирующих строчек, по которым нам надо найти интересующее нас значение. В 8-ой версии эта проблема решена автокомплитом и поиском по значениям параметра. В примере ниже я ввожу строку «Orga» и получаю все значения, в которых данная строка встречается, удобно.

Pivot Table: support hiding totals.

Достаточно долгожданная фича, позволяющая убрать итоги по строкам / столбцам.

New Visualization: Details View

Новое представление результатов данных: просмотр деталей по каждой строке. Предполагаю, что может быть удобно при использовании в дашборде при фильтрации по какому-то конкретному пользователю / партнеру.
Визуализирует в табличной форме названия колонок и результаты конкретной строки.

В этой заметке разберём как правильно построить отчет по Retention с использованием Redash и языка SQL.
Для начала, в двух словах, что это за метрика Retention rate, почему она важна, какой бывает и каким образом считается.

Retention rate

Метрика Retention rate довольно широко распространена и особенно известна в мобильной индустрии, поскольку позволяет понять насколько хорошо продукт вовлекает пользователей в ежедневное использование. Вспомним (или узнаем) как рассчитывается Retention:
Retention дня X — это N% процентов пользователей, которые вернутся к продукту в день X. Иными словами, если в какой-то конкретный день (день 0) пришло 100 новых пользователей, а на 1-ый день вернулось 15, то Retention 1-го дня составит 15 / 100 = 15%.
Чаще всего выделяют Retention дней 1, 3, 7 и 30 как наиболее описательные метрики продукта, однако полезно в целом рассматривать кривую Retention и делать выводы исходя из нее.

Кривая retention

В конечном итоге нас интересует построение такой кривой, которая показывает удержание пользователей с 0-го дня до 30-го.

Кривая Retention rate c 0-го по 30-ый день

Rolling Retention (RR)

Кроме классического Retention rate выделяют также Rolling Retention (далее RR). При расчете Rolling Retention помимо дня X учитываются также все последующие дни. Таким образом RR 1-го дня — количество пользователей, которые вернулись в 1-ый и последующие дни.

Сравним Retention и Rolling Retention 10-го дня:
Retention₁₀ — количество пользователей, вернувшихся в 10-ый день / количество пользователей, установивших приложение 10 дней назад * 100%.
Rolling Retention₁₀ — количество пользователей, вернувшихся в 10-ый день или позже / количество пользователей, установивших приложение 10 дней назад * 100%.

Гранулярность (retention временных отрезков)

В некоторых отраслях и соответствующих задачах полезно понимать Retention конкретного дня (чаще всего в мобильной индустрии), в других случаях полезно понимать удержание пользователя на разных временных интервалах: например, недельные отрезки или месячные (часто полезно в e-commerce, ретейле).

Пример когорт по месяцам и соответствующий им месячный Retention

Как построить Retention отчет на языке SQL?

Выше мы разобрали как посчитать Retention в формулах. Теперь приложим это к языку SQL.
Допустим, что у нас есть две таблицы: user — храним данные об идентификаторах пользователей и мета-информацию, client_session — информация о посещениях пользователями мобильного приложения.
В запросе будут фигурировать только две эти таблицы, поэтому вы с легкостью сможете адаптировать запрос под себя.
примечание: в рамках данного кода я использую Impala в качестве СУБД.

Собираем размер когорт

SELECT from_unixtime(user.installed_at, "yyyy-MM-dd") AS reg_date,
          ndv(user.id) AS users
   FROM USER
   WHERE from_unixtime(user.installed_at)>=date_add(now(), -60)
     AND from_unixtime(user.installed_at)<=date_add(now(), -31)
   GROUP BY 1

Разберем этот довольно несложный запрос: по каждому дню мы считаем количество уникальных пользователей для отрезка [60 дней назад; 31 день назад].
Чтобы не лезть в документацию: команда ndv() в Impala аналог команды count(distinct).

Считаем количество вернувшихся пользователей по каждой когорте

SELECT from_unixtime(user.installed_at, "yyyy-MM-dd") AS reg_date,
          datediff(cast(cs.created_at AS TIMESTAMP), cast(installed_at AS TIMESTAMP)) AS date_diff,
          ndv(user.id) AS ret_base
   FROM USER
   LEFT JOIN client_session cs ON cs.user_id=user.id
   WHERE 1=1
     AND datediff(cast(cs.created_at AS TIMESTAMP), cast(installed_at AS TIMESTAMP)) between 0 and 30
     AND from_unixtime(user.installed_at)>=date_add(now(), -60)
     AND from_unixtime(user.installed_at)<=date_add(now(), -31)
   GROUP BY 1, 2

В этом запросе ключевая часть содержится в команде datediff: теперь мы считаем для каждой когорты и для каждого datediff количество уникальных пользователей все той же командой ndv() (фактически, количество пользователей которые вернулись в дни от 0-го до 30-го).

Отлично, теперь у нас есть размер когорт и количество вернувшихся пользователей

Собираем все вместе

SELECT reg.reg_date AS date_registration,
       reg.users AS cohort_size,
       cohort.date_diff AS day_difference,
       cohort.ret_base AS retention_base,
       cohort.ret_base/reg.users AS retention_rate
FROM
  (SELECT from_unixtime(user.installed_at, "yyyy-MM-dd") AS reg_date,
          ndv(user.id) AS users
   FROM USER
   WHERE from_unixtime(user.installed_at)>=date_add(now(), -60)
     AND from_unixtime(user.installed_at)<=date_add(now(), -31)
   GROUP BY 1) reg
LEFT JOIN
  (SELECT from_unixtime(user.installed_at, "yyyy-MM-dd") AS reg_date,
          datediff(cast(cs.created_at AS TIMESTAMP), cast(installed_at AS TIMESTAMP)) AS date_diff,
          ndv(user.id) AS ret_base
   FROM USER
   LEFT JOIN client_session cs ON cs.user_id=user.id
   WHERE 1=1
     AND datediff(cast(cs.created_at AS TIMESTAMP), cast(installed_at AS TIMESTAMP)) between 0 and 30
     AND from_unixtime(user.installed_at)>=date_add(now(), -60)
     AND from_unixtime(user.installed_at)<=date_add(now(), -31)
   GROUP BY 1, 2) cohort ON reg.reg_date=cohort.reg_date
    ORDER BY 1,3

Мы получили запрос, который для каждой когорты считает Retention, в итоге результат можно отобразить в таком виде:

Retention rate, посчитанный для каждой когорты пользователей

Построение единственной кривой Retention

Несколько модифицируем наш запрос и получим требуемые данные для построения одной кривой Retention:

SELECT 
       cohort.date_diff AS day_difference,
       avg(reg.users) AS cohort_size,
       avg(cohort.ret_base) AS retention_base,
       avg(cohort.ret_base)/avg(reg.users)*100 AS retention_rate
FROM
  (SELECT from_unixtime(user.installed_at, "yyyy-MM-dd") AS reg_date,
          ndv(user.id) AS users
   FROM USER
   WHERE from_unixtime(user.installed_at)>=date_add(now(), -60)
     AND from_unixtime(user.installed_at)<=date_add(now(), -31)
   GROUP BY 1) reg
LEFT JOIN
  (SELECT from_unixtime(user.installed_at, "yyyy-MM-dd") AS reg_date,
          datediff(cast(cs.created_at AS TIMESTAMP), cast(installed_at AS TIMESTAMP)) AS date_diff,
          ndv(user.id) AS ret_base
   FROM USER
   LEFT JOIN client_session cs ON cs.user_id=user.id
   WHERE 1=1
     AND datediff(cast(cs.created_at AS TIMESTAMP), cast(installed_at AS TIMESTAMP)) between 0 and 30
     AND from_unixtime(user.installed_at)>=date_add(now(), -60)
     AND from_unixtime(user.installed_at)<=date_add(now(), -31)
   GROUP BY 1,2) cohort ON reg.reg_date=cohort.reg_date
    GROUP BY 1        
    ORDER BY 1

Теперь у нас для каждого дня посчитан средний по всем когортам Retention rate.

Больше по теме

• Как создавать продукты, формирующие привычки?
• Top 3 Ways To Calculate User Retention Rate With Formulas

Временно отвлечемся от проекта по сбору чековых данных. О нескольких следующих шагах этого проекта поговорим чуть позже.

Сегодня обсудим новый сервис от Яндекса — DataLens (доступ к демо мне любезно предоставил мой большой друг Василий Озеров и команда Fevlake / Rebrain). Сервис находится в режиме Preview и по сути является облачным BI. Ключевая фишка сервиса в том, что он легко и удобно работает с кликхаусом (Yandex Clickhouse).

Подключение источников данных

Рассмотрим основные вещи: подключение источника данных и настройку датасета.
Выбор СУБД не велик, но некоторые основные вещи присутствуют. Для целей нашего тестирования возьмем MySQL.

Выбор источников данных DataLens

На основе созданного подключения предлагается создать датасет:

Интерфейс настройки датасета, определение измерений и метрик

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

Визуализация данных

Что касается самого интерфейса для визуализации — все довольно легко и удобно. Напоминает облачную версию Tableau. А в сравнении с Redash, который чаще всего используется в связке с Clickhouse, возможности визаулизации — просто потрясают.
Чего стоят сводные таблицы, в которых можно использовать Measure Names в качестве названия столбцов:

Настройка сводных таблиц в DataLens

Разумеется, в DataLens от Яндекса есть возможность собрать и базовые графики:

Построение линейного графика в DataLens

Есть и диаграммы с областями:

Построение диаграммы с областями в DataLens

Однако мне не удалось обнаружить каким образом осуществляется группировка дат по месяцам / кварталам / неделям. Судя по примеру данных, доступному в пробной версии, разработчики пока решают этот вопрос созданием дополнительных атрибутов (DayMonth, DayWeek, etc).

Дашборды

Пока интерфейс создания блоков дашборда выглядит громоздко и не всегда понятны интерфейсные окна. Вот, например, окно позволяющее указать параметр:

Не совсем очевидное окно настройки параметров дашборда

Однако в галерее примеров имеются очень функциональные и удобные дашборды с селекторами, вкладками и параметрами:

Пример работающего дашборда с параметрами и вкладками в DataLens

Ждем исправления интерфейсных недочетов, улучшения DataLens и готовимся к использованию в паре с Clickhouse!

Итак, мы планировали разобрать Funnel-визуализацию отчета в redash.
В первую очередь, построим запрос к подключенному нами источнику данных — google analytics.

Прямо вот такой текст необходимо положить в консоль запроса:

{
    "ids": "ga:128886640",
    "start_date": "30daysAgo",
    "end_date": "yesterday",
    "metrics": "ga:users,ga:goal1Completions,ga:goal2Completions,ga:goal3Completions"
}

В данном запросе мы просим API Google Analytics отдать данные за последние 30 дней по аккаунту GA: 128886640. Мы хотим увидеть число пользователей и число выполнения целей 1, 2 и 3.

В итоге получаем таблицу следующего вида:

Отлично, это именно то, что нам нужно для построения воронки.
Расскажу об одной очень полезной фиче Redash: query-results. Чтобы подключить таблицы с результатами выполнения запросов, идем в Data Sources и ищем query-results (beta). Подключаем новый источник данных.
Теперь у нас появилась возможность обращаться к результатам запросов redash. Так, например, мы можем воспользоваться результатами запроса к Google Analytics.

Как это сделать?
Необходимо выбрать слева источник данных query-results:

Выпадающее меню с выбором источников данных (в консоли — слева)

Теперь научимся делать funnel-визуализацию. Для этого пишем следующий SQL-запрос:

select 'Добавление товара в корзину' as step_name, ga_goal1Completions as goalCompletion from query_8
union all
select 'Просмотр корзины' as step_name, ga_goal2Completions from query_8
union all
select 'Оформление заказа' as step_name, ga_goal3Completions from query_8

В данном случае query_8 — это как раз таблица с результатами запроса к источнику данных Google Analytics.

Настроим визуализацию:

Аккуратно выбираем параметры, чтобы получить желаемый результат

В итоге мы получаем воронку конверсий из одной цели в другую:

Данную воронку можно отобразить в дашборде и добавить к ней фильтры / параметры

В этой статье разберем как подключить источник данных Google Analytic к сервису Redash [подробнее Redash и его возможности мы разбирали в предыдущих заметках].

Создаем сервисный аккаунт в Google

Переходим в консоль сервисных аккаунтов.

Создаем новый сервисный аккаунт

В окне создания аккаунта вводим имя, а затем формируем новый ключ. Выбираем, что нам необходим JSON-ключ и затем нажимаем «Создать».

Включаем Analytics API

Для созданного нами сервисного аккаунта необходимо включить Analytics API.

Когда мы все установили, Analytics API должен гореть зеленым

Добавляем сервисного пользователя в Google Analytics

Далее, необходимо добавить созданного нами сервисного пользователя в Google Analytics. Пользователь будет иметь примерно такой вид:
user@PROJECT-ID.iam.gserviceaccount.com.
Необходимо добавлять пользователя с правами на Чтение и Просмотр.

Создаем новый источник данных в Redash

Идем в Настройки (Settings) -> Добавляем новый источник данных

Подключаем новый источник данных.

Нас интересует источник данных Google Analytics, поэтому ищем «google»:

Ищем google analytics в источниках данных.

Вспоминаем, куда мы сохранили JSON файл, он нам сейчас потребуется

Выбираем созданный ранее JSON файл

Пишем запрос к новому источнику данных

Именно в таком виде запрос выполняется в консоли redash:

{
    "ids": "ga:128886640",
    "start_date": "30daysAgo",
    "end_date": "yesterday",
    "metrics": "ga:users,ga:newUsers,ga:goal1Starts,ga:goal2Completions,ga:goal3Starts,ga:transactions,ga:transactionRevenue", 
    "dimensions": "ga:date"
}

Как узнать параметры для выполнения запроса?

У Google есть отличный ресурс Query Explorer, в котором можно найти все необходимые метрики и измерения, которые доступны в Google Analytics.

Надеюсь, данная инструкция оказалась вам полезной, далее мы разберемся как построить воронку целей в Redash на основании данных из Google Analytics.

В Redash легко и удобно визуализировать информацию, я рассмотрю в этом посте примеры отображения данных на различных графиках. Все примеры будут на временных рядах, построенных на данных за каждый месяц.

Поскольку я про аналитику, то вместе с графиками мы будем изучать полезные показатели бизнеса. Начнем с довольной традиционной метрики для ритейла/e-commerce AOV (Average Order Value) — средняя сумма заказа (в данном случае за месяц). Показатель позволяет отслеживать изменения, связанные с покупательским поведением (стали ли в среднем больше или меньше покупать).

Пример столбиковой диаграммы в Redash на основе показателя AOV (Average Order Value)

С точки зрения отображения столбиковой диаграммы все довольно штатно, но удобно — есть возможность управлять цветом диаграммы, подписью данных, форматом подписи данных (убрать или показать данные после запятой).

Зачастую динамика гораздо отчетливее, если посмотреть либо на традиционной график, либо на так называемую area-диаграмму. В данном случае мы исследуем новых пользователей, а также какую долю MAU (Monthly Active Users) занимают новые пользователи.

В диаграмме используется принцип stacked — это когда данные двух рядов суммируются и показываются один над другим.

В этом примере наша диаграмма максимально информативна — мы даем понять какую долю занимают новые пользователи, а используя принцип stacked показываем сколько всего было активных пользователей за месяц (по сути, убиваем двух зайцев сразу).

А вообще мы могли бы представить данные и в несколько ином виде. Например, довольно популярно смешение разных типов диаграмм. Представим, что MAU представлено столбиковой диаграммой (зеленым на графике), а доля новых пользователей от MAU красной линией, которая отложена по вспомогательной (правой оси).

Два типа диаграмм на одном графике

C redash можно строить сводные таблицы, отображать воронки и когорты, а также использовать карт для отображения гео-данных.
В следующих постах я расскажу о диаграмме для построения воронки (но перед этим научимся подключать google analytics).

Сегодня мы рассмотрим и постараемся разобраться в весьма популярном сейчас инструменте под названием Redash. Инструмент крайне удобен тем, что он умеет работать с Clickhouse. Все остальные разработчики BI-утилит не поддерживают Clickhouse. Буквально недавно появился ODBC драйвер под Tableau.

Я сделаю краткий обзор полезных фич Redash’a, а в дальнейшей серии постов разберу какие полезные запросы и отчеты с помощью Redash можно построить.

Домашняя страница Redash

Итак, что такое Redash? Это инструмент для on-demand аналитики, его можно использовать на совершенно разнообразных база данных. К примеру, Redash можно подключить к БД под управлением MySQL или HP Vertica.

Основное, что предлагает нам Redash — удобная консоль для написания SQL-запросов к базе данных.

Консоль для написания SQL-запросов

Соответственно, первый главный вывод: для того, чтобы использовать redash вы должны знать SQL или иметь в штате специалиста, который знает SQL.

Помимо базовой консоли Redash предлагает инструменты визуализации (в их числе построение Funnel-диаграмм, когортного анализа и сводных таблиц (правда функционал последних крайне скудный)), а также инструменты построения дашбордов и систем оповещения (по почте или в slack).

Разберем чуть детальнее базовую консоль. Пользователю доступны полезные фичи: использование фильтров, мультифильтров и параметров запроса:

Использование параметров, фильтров и мульти-фильтров в запросах одновременно

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

В следующей заметке подробнее обсудим визуализацию в Redash, позже — построение дашбордов и системы уведомлений.