10 заметок с тегом

sql

Позднее Ctrl + ↑

Когортный анализ в Redash

Время чтения текста – 6 минут

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

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

Мы разберемся с тем, как сравнить Retention пользователей недельных когорт в Redash, поскольку у Redash имеется специальный тип визуализации для построения такого отчета.
Определимся для начала c SQL-запросом. У нас как и прежде две таблицы — user (id пользователя и время установки приложения) и client_session — таймстемпы (created_at) активности каждого пользователя (user_id). Будем считать Retention первых семи дней для недельных когорт за последние 60 дней.
Запрос написан в Cloudera Impala, рассмотрим его.

Для начала построим общую численность когорт:

select trunc(from_unixtime(user.installed_at), "WW") AS cohort_week, 
	ndv(distinct user.id) as cohort_size //считаем количество пользователей в когорте
	from user 
	where from_unixtime(user.installed_at) between date_add(now(), -60) and now() //берем зарегистрированных за последние 60 дней
group by trunc(from_unixtime(user.installed_at), "WW")

Вторая часть запроса поможет посчитать количество активных пользователей на каждый день в течение первых тридцати:

select trunc(from_unixtime(user.installed_at), "WW") AS cohort_week, 
        datediff(cast(cs.created_at as timestamp),cast(user.installed_at as timestamp)) as days,
	ndv(distinct user.id) as value  //считаем количество активных пользователей на каждый день
		from user 
		left join client_session cs on user.id=cs.user_id
where from_unixtime(user.installed_at) between date_add(now(), -60) and now()
and from_unixtime(cs.created_at) >= date_add(now(), -60) //берем сессии за последние 60 дней
and datediff(cast(cs.created_at as timestamp),cast(user.installed_at as timestamp)) between 0 and 30 //отрезаем только первые 30 дней активности
group by 1,2

Итого запрос целиком:

select size.cohort_week, size.cohort_size, ret.days, ret.value from
(select trunc(from_unixtime(user.installed_at), "WW") AS cohort_week, 
		ndv(distinct user.id) as cohort_size 
	from user 
	where from_unixtime(user.installed_at) between date_add(now(), -60) and now()
group by trunc(from_unixtime(user.installed_at), "WW")) size
left join (select trunc(from_unixtime(user.installed_at), "WW") AS cohort_week, 
        datediff(cast(cs.created_at as timestamp),cast(user.installed_at as timestamp)) as days,
		ndv(distinct user.id) as value 
		from user 
		left join client_session cs on user.id=cs.user_id
where from_unixtime(user.installed_at) between date_add(now(), -60) and now()
and from_unixtime(cs.created_at) >= date_add(now(), -60)
and datediff(cast(cs.created_at as timestamp),cast(user.installed_at as timestamp)) between 0 and 30
group by 1,2) ret on size.cohort_week=ret.cohort_week

Отлично, теперь нам доступны правильно посчитанные данные.

Данные когорт в табличном виде

Создадим новую визуализацию в Redash и правильно укажем параметры:

Важно правильно указать параметры — им соответствуют колонки результирующего запроса

Обязательно отметим, что у нас недельные когорты:

Вуа-ля, наша визуализация когорт готова:

К ней можно добавить фильтры и параметры и использовать в дашборде

Материалы по теме

Обзор Looker

Время чтения текста – 13 минут

Сегодня поговорим о 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 при внедрении инструмента.

Диаграмма матрицы BCG (Boston Consulting Group)

Время чтения текста – 7 минут

Разбавлю блог интересным отчетом, который в свое время был построен для компании Yota в ноябре 2011го года. Построить данный отчет нас вдохновила матрица BCG.

У нас было: один пакет Excel, 75 VBA макросов, ODBC подключение к Oracle, SQL-запросы к БД всех сортов и расцветок. На таком стеке и рассмотрим построение отчета, но в начале немного о самой идее отчета.

Матрица BCG — это матрица размером 2х2, на которой сегменты клиентов изображаются окружностями с центрами на пересечении координат, образуемых соответствующими темпами двух выбранных показателей.

Если упростить, то нам надо было поделить всех клиентов компании на 4 сегмента: ARPU выше среднего / ниже среднего, потребление трафика (основной услуги) выше среднего / ниже среднего. Таким образом получалось, что возникает 4 квадранта, в каждый из которых необходимо поместить пузырьковую диаграмму, где размер пузырька обозначает общее количество пользователей в сегменте. Дополнительно к этому добавляется еще один пузырек в каждом квадранте (более мелкий), который показывает отток в каждом сегменте (авторское усовершенствование).

Что хотелось получить на выходе?
График подобного вида:

Представление матрицы BCG на данных компании Yota

Постановка задачи более-менее ясна, перейдем к реализации отчёта.
Предположим, что мы уже собрали нужные данные (то есть научились определять средний ARPU и среднее потребление трафика, в данном посте не будем разбирать SQL-запрос), тогда первостепенная основная задача — понять как отобразить средствами Excel пузырьки в нужных местах.

Для этого на помощь приходит базовая пузырьковая диаграмма:

Вставка — Диаграмма — Пузырьковая

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

Отлично, выходит, если предположить, что наша диаграмма будет расположена в координатах по X от -1 до 1, а по Y от -1 до 1, то центр правого верхнего пузырька это точка (0.5; 0.5) на диаграмме. Аналогичным образом, расположим все остальные основные пузырьки.

Отдельно следует подумать о пузырьках типа Churn (для отображения оттока), они расположены правее и ниже основного пузырька и могут с ним пересекаться, поэтому правый верхний пузырек разместим в эмпирически полученных координатах (0.65; 0.35).

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

Рассмотрим подробнее, как будем их использовать:

Итак, мы задаем по X — горизонтальные координаты центра наших пузырьков, которые лежат в ячейках A9:A12, по Y — вертикальные координаты центра наших пузырьков, которые лежат в ячейках B9:B12, а размеры пузырьков мы храним в ячейках E9:E12.
Далее, добавляем еще один ряд данных для Оттока и снова указываем все необходимые параметры.

Мы получим следующий график:

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

Добавив необходимые подписи данных, получим то, что требовалось в задаче.

Делитесь в комментариях — приходилось ли строить подобные графики, каким образом решали задачу?

Как посчитать Retention?

Время чтения текста – 8 минут

В этой заметке разберём как правильно построить отчет по 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-го дня:
Retention10 — количество пользователей, вернувшихся в 10-ый день / количество пользователей, установивших приложение 10 дней назад * 100%.
Rolling Retention10 — количество пользователей, вернувшихся в 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.

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

Redash — полноценная on-demand аналитика

Время чтения текста – 3 минуты

Сегодня мы рассмотрим и постараемся разобраться в весьма популярном сейчас инструменте под названием 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, позже — построение дашбордов и системы уведомлений.