Блог об аналитике, визуализации данных, data science и BI

эл. почта: nikolay@valiotti.com
подписаться: @leftjoin
Valiotti Analytics  — построение аналитики для мобильных и digital-стартапов
О чем этот блог?

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

В одной из прошлых заметок мы рассматривали построение 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 и правильно укажем параметры:

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

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

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

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

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

Clickhouse в качестве consumer для Amazon MSK

Disclaimer: заметка носит технический характер, поэтому может быть интересна меньшему числу лиц с бизнес-бэкграундом.

В этом блоге еще ни разу не затрагивалась тема Clickhouse, одной из самых быстрых баз данных от компании Яндекс. Краткая справка без деталей: Clickhouse — наиболее эффективно написанная СУБД колоночного типа с точки зрения программного кода, информация о СУБД довольно подробно описана в документации и во множестве видео на Youtube (раз, два, три).

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

Архитектура аналитического сервиса, в основе которого лежит Clickhouse, в основном облачная. И в рассматриваемой задаче было именно так. Предположим, у вас существует выделенный instance EC2 в Amazon (на который вы установили Clickhouse) и отдельный Kafka-кластер (решение Amazon MSK).

Задача: подключить Clickhouse в качестве consumer для получения информации с брокеров вашего кластера Kafka. На самом деле, в документации на сайте Amazon MSK довольно подробно описано как именно подключаться к кластеру Kafka, не буду дублировать эту информацию. В моем случае гайд помог: топики создавались продюсером с машины, на которой установлен Clickhouse и с неё читались консюмером.

Но возникла проблема: при подключении Clickhouse к Kafka в качестве консюмера, происходила следующая ошибка:

020.02.02 18:01:56.209132 [ 46 ] {e7124cd5-2144-4b1d-bd49-8a410cdbd607} <Error> executeQuery: std::exception. Code: 1001, type: cppkafka::HandleException, e.what() = Local: Timed out, version = 20.1.2.4 (official build) (from 127.0.0.1:46586) (in query: SELECT * FROM events), Stack trace (when copying this message, always include the lines below):

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

Поговорив со всеми своими знакомыми экспертами в области инфраструктуры и Clickhouse (Вася, Макс, привет!), с ходу мы не смогли определить в чем проблема. Проверили firewall, настройки сети, все было открыто. Подтверждалось также тем, что с локальной машины можно было отправить в топик удаленного брокера Kafka сообщения командой bin/kafka-console-producer.sh и прочитать оттуда же bin/kafka-console-consumer.sh.

Затем мне пришла в голову мысль обратиться к главному гуру и разработчику Clickhouse — Алексею Миловидову. Алексей с радостью постарался ответить на возникшие вопросы и предложил ряд гипотез, которые мы проверили (трассировку сетевых подключений и т. п.), однако и после более низкоуровневого аудита проблему локализовать не удалось. Тогда Алексей посоветовал обратиться к Михаилу Филимонову из компании Altinity. Михаил оказался очень отзывчивым экспертом и одну за другой предлагал гипотезы для проверки (параллельно подсказывая как именно будет лучше их проверить).

В итоге в ходе совместных усилий мы обнаружили, что проблема возникает у библиотеки librdkafka, так как другой пакет kafkacat, который использует эту же библиотеку отваливается от подключения к брокеру с той же проблемой (Local: timed out).

После изучения подключения через bin/kafka-console-consumer.sh и параметров подключения, Михаил посоветовал добавить такую строку в /etc/clickhouse-server/config.xml:

<kafka><security_protocol>ssl</security_protocol></kafka>

И, о чудо! Clickhouse подключился к кластеру и вытянул необходимые данные с брокера.

Надеюсь, этот рецепт и мой опыт позволит вам сэкономить время и силы на изучение похожей проблемы :)

Использование параметров в Redash

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

Параметр в отчете может быть следующих типов:

Про каждый тип по отдельности:

  • Text — текстовое поле свободного ввода, может использоваться в конструкциях типа LIKE, параметр такого типа не может использоваться в публичных дашбордах
  • Number — число, которое вводит пользователь
  • Dropdown list — список значений, из которых пользователь сможет выбрать одно или несколько (недавно появилась возможность мульти-ввода параметров в выпадающим списке)
  • Query dropdown list — аналогично предыдущему, однако значения будут браться из имеющегося запроса
  • Date / Date and Time / Date and Time (with seconds) — поля ввода даты
  • Date Range / Date and Time Range / Date and Time Range (with seconds) — поля ввода интервалов дат. Удобно использовать для конструкции вида
between '{{parameter.start}}' and '{{parameter.end}}'

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

Из неочевидных, но крайне полезных решений: как сделать параметр отключаемым?
Предположим, что параметр типа «выпадающий список» называется parameter и мы хотим его настроить для столбца geo таблицы, тогда в коде запроса будет что-то типа:

WHERE
    ('{{parameter}}' = 'Disabled' or geo = '{{parameter}}')

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

Kazakhstan Marketing Conference 2020

Вчера мне удалось выступить на крупнейшей маркетинговой конференции в Казахстане: Kazakhstan Marketing Conference 2020.

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

Приятный бонус для участников конференции: презентация моего выступления доступна на slideshare (осторожно, VPN!), так можно будет вспомнить о чем шла речь.

Помимо выступления в основной секции форума я проводил мастер-класс на тему: «Как построить понятное техническое задание на аналитику?».
И в рамках работы с аудиторией нам удалось сформулировать тезисы для шаблона технического задания.

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

Английская версия блога

С недавних пор блог Left Join стал доступен на английском языке. Пока готова только треть текущего контента и английский блог постепенно наполняется.

Соответствующая ссылка появилась в правом верхнем углу этого блога.
Буду благодарен вашим комментариям по текстам на английском языке :)

Ранее Ctrl + ↓