Relatório do Painel - DAU/MAU

[SaraDev (Sara Devlaeminck)]

Este é um relatório de painel em versão SQL para DAU/MAU.

Este relatório fornece uma análise detalhada da proporção de Usuários Ativos Diários (DAU) para Usuários Ativos Mensais (MAU) em um intervalo de datas específico. A proporção DAU/MAU é um indicador chave de desempenho (KPI) que mede o engajamento do usuário, comparando o número de usuários ativos únicos por dia com o número de usuários ativos únicos em um período de 30 dias.

-- [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

Explicação da Consulta SQL

A consulta SQL usa Expressões de Tabela Comuns (CTEs) para criar dois conjuntos de dados: um para DAU e outro para MAU. Em seguida, combina esses conjuntos de dados para calcular a proporção DAU/MAU para cada dia dentro do intervalo de datas especificado.

Parâmetros

A consulta aceita dois parâmetros, :start_date e :end_date, permitindo que o usuário especifique o intervalo de datas para o relatório. Ambos os parâmetros de data aceitam o formato de data AAAA-MM-DD.

CTEs

• date_series: A CTE date_series gera uma série de datas de :start_date a :end_date, com um intervalo de um dia entre cada data. Essa série é usada para garantir que o relatório inclua todos os dias dentro do intervalo especificado, mesmo que não haja atividade do usuário em alguns dias.
• DAU: A CTE DAU calcula o número de usuários únicos que visitaram o site em cada dia (dau_count). Isso é feito juntando a tabela user_visits com a CTE date_series na condição de que a data da visita corresponda à data na série. O resultado é agrupado por data para obter a contagem de usuários únicos para cada dia.
• MAU: A CTE MAU calcula o número de usuários únicos que visitaram o site nos 30 dias anteriores a cada data na date_series. Isso é feito juntando a tabela user_visits com a CTE date_series, onde a data da visita está entre 30 dias antes e até, e incluindo, a data na série. O resultado é agrupado por data para obter a contagem de usuários únicos para cada período de 30 dias que antecede cada data.

Seleção Final

A parte final da consulta calcula a proporção DAU/MAU para cada dia, dividindo a contagem de DAU pela contagem de MAU e multiplicando por 100 para obter uma porcentagem. Ela também seleciona as contagens de DAU e MAU como referência. A função NULLIF é usada para evitar divisão por zero, que ocorreria se não houvesse MAU para um determinado dia. O resultado é ordenado por dia para fornecer uma visão cronológica do engajamento do usuário.

Resultados de Exemplo

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)]

Adicionando uma versão modificada deste relatório que mostra apenas as estatísticas de MAU (Usuários Ativos Mensais) caso isso possa ser útil para alguém:

MAU (Usuários Ativos Mensais)

-- [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 "Mês",
  mau_count AS "Usuários Ativos Mensais"
FROM MAU
WHERE month <= CURRENT_DATE
ORDER BY month

Exemplo de Resultados

Mês	Usuários Ativos Mensais
2024-07-01	2
2024-08-01	3
…	…

[NiceOldGuy (Shap Wolf)]

Obrigado, @SaraDev, isso é útil para nós. Como somos uma comunidade pequena com funcionários muito mais ativos do que usuários regulares, eu (bem, eu e o ChatGPT) modificamos a consulta para poder executar usuários regulares e usuários da equipe separadamente ou combinados. E eu gosto de ver isso em um gráfico, então modifiquei para habilitar isso.
Recentemente, vi como baixar o esquema do banco de dados nesta dica (https://meta.discourse.org/t/is-there-a-document-introducing-the-database-schema-design/171729/4?u=niceoldguy) de @tyler.lamparter, e eu faço upload disso para o ChatGPT no início dessas sessões; torna mais fácil pedir ao chatty para separar staff, por exemplo.

-- [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) -- All users
    OR (:include_staff = 0 AND user_id NOT IN (SELECT user_id FROM staff_users)) -- Non-staff only
    OR (:include_staff = 2 AND user_id IN (SELECT user_id FROM staff_users)) -- Staff only
  )
  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) -- All users
    OR (:include_staff = 0 AND user_id NOT IN (SELECT user_id FROM staff_users)) -- Non-staff only
    OR (:include_staff = 2 AND user_id IN (SELECT user_id FROM staff_users)) -- Staff only
  )
  GROUP BY month
)
SELECT
  d.day::DATE AS date,  -- ✅ X-axis for graphing
  ROUND((d.dau::numeric / NULLIF(m.mau, 0)::numeric) * 100, 1)::FLOAT AS dau_mau_ratio  -- ✅ Y-axis for graphing
FROM daily_users d
JOIN monthly_users m ON date_trunc('month', d.day) = m.month
ORDER BY date
-- 0 = Non-Staff Only, 1 = All Users, 2 = Staff Only