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

redash

Разница между Retention на основе 24-часовых окон и календарных дней

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

Вчера в Telegram мне написал читатель блога:

Допустим, сегодня понедельник, я сделал 187 установок, и хочу посмотреть Retention первого дня, в какой день недели это можно сделать?

Речь идет о посте про Retention. Дам некоторые пояснения на этот счёт. Retention можно считать как на основе календарных дней, так и на основе 24-часовых окон. Retention нулевого дня в данном случае будет понедельник, а первого дня — вторник. Но здесь есть небольшая загвоздка.

К примеру, если вы начали продвижение в понедельник 5 октября в 23:59, то все установки этой минуты через пару минут будут иметь Retention первого дня. Это проблема календарного исчисления. Для решения этой проблемы некоторые аналитики измеряют Retention не только по календарю, но и по 24-часовым окнам.

Приложим эту идею к вышеописанному случаю:

  • Retention нулевых суток в таком случае — все инсталлы с 5 октября 23:59 по 6 октября 23:59
  • Retention первого дня: с 6 октября 23:59 по 7 октября 23:59
  • И так далее со сдвигом 24-часового окна.

Как рассчитать Retention на основе 24-часовых окон с использованием SQL?

Вспомним запрос из предыдущего поста в блоге. Там мы считали разницу в днях между датой установки и датой активности пользователя. Модифицируем запрос так, чтобы активность считалась в 24-часовых окнах: заменим расчёт datediff на расчёт 24-часовых окон, обновив строки, выделенные жирным


SELECT from_unixtime(user.installed_at, "yyyy-MM-dd") AS reg_date,
   floor((cast(cs.created_at as int)-cast(installed_at as int))/(24*3600)) 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 floor((cast(cs.created_at as int)-cast(installed_at as int))/(24*3600)) 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

Обновленный запрос:

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,
    floor((cast(cs.created_at as int)-cast(installed_at as int))/(24*3600)) 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 floor((cast(cs.created_at as int)-cast(installed_at as int))/(24*3600)) 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, посчитанный методом 24-часового окна, несколько ниже.

 Нет комментариев    191   17 дн   redash   retention   вопросы

Гайд по современным BI-системам

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

В новой серии постов постараемся подробно изучить различные BI-системы на популярной группе датасетов SuperStore Sales. В основе данных — продажи и прибыль сетевого ритейлера в долларах.

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

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

Ниже перечислен перечень BI-систем и инструментов для работы, с данными, которые хотелось бы опробовать и описать опыт построения дашборда. Приглашаю тех, кто желает поучаствовать в решении данной задачи написать мне в Telegram — @valiotti. Разумеется, авторство дашборда будет указано. Проект некоммерческий, но полезный для сравнения современных систем для аналитики независимо от квадрантов Gartner.

Сейчас в планах подготовить материалы о следующих инструментах:

Бесплатные (Open source):

  • Metabase
  • Redash
  • Apache Superset
  • Dash / Plotly

Бесплатные (cloud):

  • Google Studio
  • Yandex Datalens

Платные (cloud):

  • Mode
  • Cluvio
  • Holistic
  • Chartio
  • Periscope
  • DeltaDNA
  • Klipfolio
  • Count.co

Платные:

  • PowerBI
  • Tableau
  • Looker
  • Excel
  • Alteryx
  • Qlik Sense
  • Qlik View

Итоговая цель — оценить системы по нескольким внутренним критериям:

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

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

Параллельно, результаты работы будут представлены в Telegram-канале @leftjoin, и подписчики также смогут высказать свое мнение относительно полученного результата.
В итоге каждый инструмент будет описан точкой на плоскости, а сама плоскость будет поделена на 4 части.

По мере написания новых материалов в цикле этот пост будет обновляться: будут добавляться ссылки на посты и оценки.

 Нет комментариев    373   1 мес   bi   BI-инструменты   excel   looker   powerbi   redash   tableau

Передаём и анализируем собранные данные по рекламным кампаниям в Redash

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

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

from facebook_business.api import FacebookAdsApi
from facebook_business.adobjects.adaccount import AdAccount
from facebook_business.adobjects.adreportrun import AdReportRun
from facebook_business.adobjects.adsinsights import AdsInsights
from facebook_business.adobjects.adaccountuser import AdAccountUser as AdUser
from facebook_business.exceptions import FacebookRequestError
import time

Redash, к примеру, принимает данные в формате json. Ранее мы не разбирали этот источник данных, сегодня изучим и его. Файл формата json выглядит как список словарей — его наш новый скрипт и будет передавать в Redash, предварительно переведя в json. Из прошлого скрипта нам понадобятся переменная с полями, access token, app id, app secret и две функции: wait_for_async_job() и get_insights(). Вторая функция принимает все параметры аккаунта и асинхронно собирает информацию по рекламным кампаниям — из них мы выбираем поля клики, просмотры, затраты и даты.

В материале используется практически весь скрипт из статьи «Собираем данные по рекламным кампаниям Facebook»

Получение данных обернём в функцию return_json() — она будет вызывать get_insights(), заполняя список insights_lists. Так как рекламных кампаний в аккаунте может быть несколько, наш результат окажется не списком словарей, а списком списков словарей. Для этого используем простое лямбда-выражение, «сглаживающее» список списков в список. Затем возвращаем наш insights_lists_flatten.

def return_json():
   insights_lists = []
   date_preset = 'last_year'
   for elem in my_accounts:
       elem_insights = get_insights(elem, date_preset)
       insights_lists.append(elem_insights)
   flatten = lambda lst: [item for sublist in lst for item in sublist]
   insights_lists_flatten = flatten(insights_lists)
   return insights_lists_flatten

Теперь наш скрипт возвращает список словарей с информацией. Но ещё нам нужен сервер на aiohttp, который будет собирать список и возвращать его в формате json. Создадим новый файл, импортируем библиотеку aiohttp и функцию get_json() из нашего прошлого скрипта. Напишем простой обработчик запросов — данные из Facebook скрипт получает асинхронно, так что асинхронная функция handler будет «спать», пока вся информация не будет собрана и передана. Возвращает функция данные через json_response, чтобы информация передавалась в формате json.

from aiohttp import web
from get_json import return_json
 
async def handler(request):
   data = return_json()
   return web.json_response(data)

Далее инициализируем и запускаем наше приложение.

app = web.Application()
app.add_routes([web.get('/json', handler)])
web.run_app(app)

Теперь идём на AWS, создаём папку и через sftp кидаем туда оба скрипта. Проверим, что в ACL на AWS нужный порт открыт. Заходим в console — network & security — security groups — default.

Запускаем файл с сервером. После запуска можно проверить, работает ли скрипт, обратившись к нему по ip сервера с портом 0880 на route, указанный в скрипте с сервером — там мы указали /json.

Теперь обратимся через Redash к URL и получим ту самую таблицу, которую вернул скрипт:

url: ip сервера

Сформировав query results, можем написать запрос следующего вида:

select date_start, sum(clicks) as clicks, sum(impressions) as impressions, sum(spend) as spend from query_45
group by date_start

Получаем такую таблицу, сгруппированную по колонке date_start:

Теперь по этой таблице можно отобразить данные на графике — посмотрим, как количество затрат на рекламу влияет на количество кликов по ней:

Готово! В следующий раз поработаем с ВКонтакте — получим информацию по рекламным кампаниям уже оттуда.

Когортный анализ в 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 и правильно укажем параметры:

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

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

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

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

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

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

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

Самое удобное и полезное в 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’.

Ранее Ctrl + ↓