Informe del Panel de Control - DAU/MAU

[SaraDev (Sara Devlaeminck)]

Este es un informe de panel de control en versión SQL para DAU/MAU.

Este informe proporciona un análisis detallado de la relación entre Usuarios Activos Diarios (DAU) y Usuarios Activos Mensuales (MAU) durante un rango de fechas específico. La relación DAU/MAU es un indicador clave de rendimiento (KPI) que mide la participación del usuario al comparar el número de usuarios activos únicos por día con el número de usuarios activos únicos durante un período de 30 días.

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

Explicación de la consulta SQL

La consulta SQL utiliza Expresiones Comunes de Tabla (CTE) para crear dos conjuntos de datos: uno para DAU y otro para MAU. Luego, combina estos conjuntos de datos para calcular la relación DAU/MAU para cada día dentro del rango de fechas especificado.

Parámetros

La consulta acepta dos parámetros, :start_date y :end_date, lo que permite al usuario especificar el rango de fechas para el informe. Ambos parámetros de fecha aceptan el formato de fecha YYYY-MM-DD.

CTEs

• date_series: La CTE date_series genera una serie de fechas desde :start_date hasta :end_date, con un intervalo de un día entre cada fecha. Esta serie se utiliza para garantizar que el informe incluya todos los días dentro del rango especificado, incluso si no hay actividad de usuario en algunos días.
• DAU: La CTE DAU calcula el número de usuarios únicos que visitaron el sitio cada día (dau_count). Lo hace uniendo la tabla user_visits con la CTE date_series bajo la condición de que la fecha de visita coincida con la fecha de la serie. El resultado se agrupa por fecha para obtener el recuento de usuarios únicos para cada día.
• MAU: La CTE MAU calcula el número de usuarios únicos que visitaron el sitio dentro de los 30 días anteriores a cada fecha en date_series. Lo hace uniendo la tabla user_visits con la CTE date_series, donde la fecha de visita está entre 30 días antes y hasta, e incluyendo, la fecha en la serie. El resultado se agrupa por fecha para obtener el recuento de usuarios únicos para cada período de 30 días anterior a cada fecha.

Selección Final

La parte final de la consulta calcula la relación DAU/MAU para cada día dividiendo el recuento de DAU por el recuento de MAU y multiplicando por 100 para obtener un porcentaje. También selecciona los recuentos de DAU y MAU como referencia. La función NULLIF se utiliza para evitar la división por cero, que ocurriría si no hay MAU para un día determinado. El resultado se ordena por día para proporcionar una vista cronológica de la participación del usuario.

Resultados de ejemplo

día	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)]

Añadiendo una versión modificada de este informe aquí que muestra solo las estadísticas de MAU (Usuarios Activos Mensuales) en caso de que esto sea útil para alguien:

MAU (Usuarios Activos Mensuales)

-- [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 "Mes",
  mau_count AS "Usuarios Activos Mensuales"
FROM MAU
WHERE month <= CURRENT_DATE
ORDER BY month

Resultados de ejemplo

Mes	Usuarios Activos Mensuales
2024-07-01	2
2024-08-01	3
…	…

[NiceOldGuy (Shap Wolf)]

Gracias, @SaraDev, esto nos resulta útil. Dado que somos una comunidad pequeña con personal mucho más activo que los usuarios habituales, yo (bueno, ChatGPT y yo) modificamos la consulta para poder ejecutar usuarios habituales y usuarios del personal por separado o combinados. Y me gusta verlo representado gráficamente, así que lo modifiqué para habilitarlo.
Recientemente vi cómo descargar el esquema de la base de datos en este consejo de @tyler.lamparter, y lo subo a ChatGPT al principio de estas sesiones; hace que sea más fácil pedirle a chatty que separe staff, por ejemplo.

-- [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) -- Todos los usuarios
    OR (:include_staff = 0 AND user_id NOT IN (SELECT user_id FROM staff_users)) -- Solo usuarios no del personal
    OR (:include_staff = 2 AND user_id IN (SELECT user_id FROM staff_users)) -- Solo personal
  )
  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) -- Todos los usuarios
    OR (:include_staff = 0 AND user_id NOT IN (SELECT user_id FROM staff_users)) -- Solo usuarios no del personal
    OR (:include_staff = 2 AND user_id IN (SELECT user_id FROM staff_users)) -- Solo personal
  )
  GROUP BY month
)
SELECT
  d.day::DATE AS date,  -- ✅ Eje X para graficar
  ROUND((d.dau::numeric / NULLIF(m.mau, 0)::numeric) * 100, 1)::FLOAT AS dau_mau_ratio  -- ✅ Eje Y para graficar
FROM daily_users d
JOIN monthly_users m ON date_trunc('month', d.day) = m.month
ORDER BY date
-- 0 = Solo No Personal, 1 = Todos los Usuarios, 2 = Solo Personal