Rapporto Dashboard - DAU/MAU

[SaraDev (Sara Devlaeminck)]

Questo è una versione SQL del Report Dashboard per DAU/MAU.

Questo report fornisce un’analisi dettagliata del rapporto tra Utenti Attivi Giornalieri (DAU) e Utenti Attivi Mensili (MAU) per un intervallo di date specifico. Il rapporto DAU/MAU è un indicatore chiave di prestazione (KPI) che misura il coinvolgimento degli utenti confrontando il numero di utenti attivi unici al giorno con il numero di utenti attivi unici su un periodo di 30 giorni.

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

Spiegazione della Query SQL

La query SQL utilizza Common Table Expressions (CTE) per creare due set di dati: uno per DAU e uno per MAU. Quindi combina questi set di dati per calcolare il rapporto DAU/MAU per ogni giorno all’interno dell’intervallo di date specificato.

Parametri

La query accetta due parametri, :start_date e :end_date, che consentono all’utente di specificare l’intervallo di date per il report. Entrambi i parametri di data accettano il formato data AAAA-MM-GG.

CTE

• date_series: La CTE date_series genera una serie di date da :start_date a :end_date, con un intervallo di un giorno tra ogni data. Questa serie viene utilizzata per garantire che il report includa ogni giorno all’interno dell’intervallo specificato, anche se in alcuni giorni non c’è attività degli utenti.
• DAU: La CTE DAU calcola il numero di utenti unici che hanno visitato il sito ogni giorno (dau_count). Lo fa unendo la tabella user_visits con la CTE date_series sulla condizione che la data della visita corrisponda alla data nella serie. Il risultato viene raggruppato per data per ottenere il conteggio degli utenti unici per ogni giorno.
• MAU: La CTE MAU calcola il numero di utenti unici che hanno visitato il sito nei 30 giorni precedenti ogni data nella date_series. Lo fa unendo la tabella user_visits con la CTE date_series, dove la data della visita è compresa tra 30 giorni prima e fino alla data nella serie, inclusa. Il risultato viene raggruppato per data per ottenere il conteggio degli utenti unici per ogni periodo di 30 giorni precedente ogni data.

Selezione Finale

La parte finale della query calcola il rapporto DAU/MAU per ogni giorno dividendo il conteggio DAU per il conteggio MAU e moltiplicando per 100 per ottenere una percentuale. Seleziona anche i conteggi di DAU e MAU come riferimento. La funzione NULLIF viene utilizzata per evitare la divisione per zero, che si verificherebbe se non ci fossero MAU per un dato giorno. Il risultato viene ordinato per giorno per fornire una visualizzazione cronologica del coinvolgimento degli utenti.

Risultati di esempio

day	DAU/MAU	dau_count	mau_count
2023-11-19	15.3191489	360	2350
2023-11-20	23.9222316	566	2366
2023-11-21	23.5564854	563	2390
2023-11-22	23.0992937	556	2407
2023-11-23	19.3949441	468	2413
…	…	…	…

[SaraDev (Sara Devlaeminck)]

Aggiungo qui una versione modificata di questo report che mostra solo le statistiche MAU (Monthly Active Users) nel caso possa essere utile a qualcuno:

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 "Mese",
  mau_count AS "Utenti Attivi Mensili"
FROM MAU
WHERE month <= CURRENT_DATE
ORDER BY month

Esempio di Risultati

Mese	Utenti Attivi Mensili
2024-07-01	2
2024-08-01	3
…	…

[NiceOldGuy (Shap Wolf)]

Grazie, @SaraDev, questo ci è utile. Poiché siamo una piccola community con staff molto più attivo degli utenti regolari, io (beh, io e ChatGPT) abbiamo modificato la query per poter eseguire separatamente o combinati utenti regolari e utenti staff. E mi piace vederlo graficato, quindi ho modificato per abilitarlo.
Recentemente ho visto come scaricare lo schema del database in questo suggerimento da @tyler.lamparter, e lo carico su ChatGPT all’inizio di queste sessioni; rende più facile chiedere a chatty di separare staff, ad esempio.

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