Отчет по дашборду - DAU/MAU

[SaraDev (Sara Devlaeminck)]

Это SQL-версия отчета Dashboard для показателя DAU/MAU.

Данный отчет предоставляет детальный анализ соотношения ежедневных активных пользователей (DAU) к ежемесячным активным пользователям (MAU) за определенный период времени. Соотношение DAU/MAU является ключевым показателем эффективности (KPI), который измеряет вовлеченность пользователей путем сравнения количества уникальных активных пользователей в день с количеством уникальных активных пользователей за 30-дневный период.

-- [params]
-- date :start_date = 2023-01-01
-- date :end_date = 2024-01-01

WITH date_series AS (
  SELECT generate_series(:start_date::date, :end_date::date, '1 day'::interval) AS date
),

DAU AS (
  SELECT date(date) AS day, COUNT(DISTINCT user_id) AS dau_count
  FROM user_visits
  JOIN date_series ON date(user_visits.visited_at) = date_series.date
  GROUP BY date
),
MAU AS (
  SELECT date_series.date, COUNT(DISTINCT user_visits.user_id) AS mau_count
  FROM user_visits
  JOIN date_series ON date(user_visits.visited_at) >= date_series.date - INTERVAL '30 days'
  AND date(user_visits.visited_at) <= date_series.date 
  GROUP BY date_series.date
)
SELECT
  DAU.day,
  (DAU.dau_count::FLOAT / NULLIF(MAU.mau_count, 0)) * 100 AS "DAU/MAU",
  DAU.dau_count,
  MAU.mau_count
FROM DAU
JOIN MAU ON DAU.day = MAU.date
ORDER BY DAU.day

Пояснение к SQL-запросу

SQL-запрос использует общие табличные выражения (CTE) для создания двух наборов данных: одного для DAU и одного для MAU. Затем эти наборы данных объединяются для расчета соотношения DAU/MAU для каждого дня в указанном диапазоне дат.

Параметры

Запрос принимает два параметра: :start_date и :end_date, что позволяет пользователю указать диапазон дат для отчета. Оба параметра даты принимают формат ГГГГ-ММ-ДД.

CTE

• date_series: CTE date_series генерирует последовательность дат от :start_date до :end_date с интервалом в один день между каждой датой. Эта последовательность используется для обеспечения того, чтобы отчет включал каждый день в указанном диапазоне, даже если в некоторые дни не было активности пользователей.
• DAU: CTE DAU вычисляет количество уникальных пользователей, посетивших сайт в каждый день (dau_count). Это делается путем соединения таблицы user_visits с CTE date_series по условию, что дата посещения совпадает с датой в последовательности. Результат группируется по дате для получения количества уникальных пользователей для каждого дня.
• MAU: CTE MAU вычисляет количество уникальных пользователей, посетивших сайт в течение 30 дней, предшествующих каждой дате в date_series. Это делается путем соединения таблицы user_visits с CTE date_series, где дата посещения находится в диапазоне от 30 дней до указанной даты в последовательности включительно. Результат группируется по дате для получения количества уникальных пользователей за каждый 30-дневный период, предшествующий каждой дате.

Финальный выбор

Финальная часть запроса вычисляет соотношение DAU/MAU для каждого дня путем деления количества DAU на количество MAU и умножения на 100 для получения процента. Также выбираются количества DAU и MAU для справки. Функция NULLIF используется для избежания деления на ноль, которое произошло бы, если бы для определенного дня не было MAU. Результат сортируется по дате для предоставления хронологического обзора вовлеченности пользователей.

Примеры результатов

day	DAU/MAU	dau_count	mau_count
2023-11-19	15.31914893617021	360	2350
2023-11-20	23.92223161453931	566	2366
2023-11-21	23.55648535564853	563	2390
2023-11-22	23.09929372663066	556	2407
2023-11-23	19.394944053046	468	2413
…	…	…	…

[SaraDev (Sara Devlaeminck)]

Добавляю здесь модифицированную версию этого отчёта, которая показывает только статистику MAU (Monthly Active Users) — возможно, это кому-то пригодится:

MAU (Monthly Active Users)

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01

WITH date_series AS (
  SELECT generate_series(:start_date::date, :end_date::date, '1 month'::interval) AS month_start
),

MAU AS (
  SELECT 
    date_trunc('month', date_series.month_start) AS month,
    COUNT(DISTINCT user_visits.user_id) AS mau_count
  FROM user_visits
  JOIN date_series 
    ON date(user_visits.visited_at) >= date_series.month_start - INTERVAL '1 month'
   AND date(user_visits.visited_at) < date_series.month_start + INTERVAL '1 month'
  GROUP BY date_trunc('month', date_series.month_start)
)
SELECT 
  month::date AS "Месяц",
  mau_count AS "Ежемесячные активные пользователи"
FROM MAU
WHERE month <= CURRENT_DATE
ORDER BY month

Пример результатов

Месяц	Ежемесячные активные пользователи
2024-07-01	2
2024-08-01	3
…	…

[NiceOldGuy (Shap Wolf)]

Спасибо, @SaraDev, это нам пригодится. Поскольку мы — небольшое сообщество, где сотрудники гораздо активнее обычных пользователей, я (вернее, мы с ChatGPT) доработал запрос, чтобы можно было запускать отчёт отдельно для обычных пользователей, отдельно для сотрудников или объединённо. Мне также нравится видеть результаты в виде графика, поэтому я добавил соответствующую возможность.

Недавно я узнал, как скачать схему базы данных из этого совета от @tyler.lamparter. Я загружаю её в ChatGPT в начале каждой сессии — так проще попросить его отделить staff.

-- [params]
-- int :months_ago = 3
-- int :include_staff = 1


WITH staff_users AS (
  SELECT user_id FROM group_users WHERE group_id = 3
),
daily_users AS (
  SELECT 
    date_trunc('day', visited_at)::DATE AS day, 
    COUNT(DISTINCT user_id) AS dau
  FROM user_visits
  WHERE visited_at >= CURRENT_DATE - INTERVAL ':months_ago months'
  AND (
    (:include_staff = 1) -- Все пользователи
    OR (:include_staff = 0 AND user_id NOT IN (SELECT user_id FROM staff_users)) -- Только не-сотрудники
    OR (:include_staff = 2 AND user_id IN (SELECT user_id FROM staff_users)) -- Только сотрудники
  )
  GROUP BY day
),
monthly_users AS (
  SELECT 
    date_trunc('month', visited_at)::DATE AS month, 
    COUNT(DISTINCT user_id) AS mau
  FROM user_visits
  WHERE visited_at >= CURRENT_DATE - INTERVAL ':months_ago months'
  AND (
    (:include_staff = 1) -- Все пользователи
    OR (:include_staff = 0 AND user_id NOT IN (SELECT user_id FROM staff_users)) -- Только не-сотрудники
    OR (:include_staff = 2 AND user_id IN (SELECT user_id FROM staff_users)) -- Только сотрудники
  )
  GROUP BY month
)
SELECT 
  d.day::DATE AS date,  -- ✅ Ось X для построения графика
  ROUND((d.dau::numeric / NULLIF(m.mau, 0)::numeric) * 100, 1)::FLOAT AS dau_mau_ratio  -- ✅ Ось Y для построения графика
FROM daily_users d
JOIN monthly_users m ON date_trunc('month', d.day) = m.month
ORDER BY date
-- 0 = Только не-сотрудники, 1 = Все пользователи, 2 = Только сотрудники