Rapport du tableau de bord - DAU/MAU

Données et rapports
, ,
1

Ceci est une version SQL du rapport de tableau de bord pour DAU/MAU.

Ce rapport fournit une analyse détaillée du ratio d’utilisateurs actifs quotidiens (DAU) sur utilisateurs actifs mensuels (MAU) sur une période de dates spécifique. Le ratio DAU/MAU est un indicateur clé de performance (KPI) qui mesure l’engagement des utilisateurs en comparant le nombre d’utilisateurs actifs uniques par jour au nombre d’utilisateurs actifs uniques sur une période de 30 jours.

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

Explication de la requête SQL

La requête SQL utilise des expressions de table communes (CTE) pour créer deux ensembles de données : un pour le DAU et un pour le MAU. Elle combine ensuite ces ensembles de données pour calculer le ratio DAU/MAU pour chaque jour dans la plage de dates spécifiée.

Paramètres

La requête accepte deux paramètres, :start_date et :end_date, permettant à l’utilisateur de spécifier la plage de dates pour le rapport. Les deux paramètres de date acceptent le format de date AAAA-MM-JJ.

CTE

  • date_series : La CTE date_series génère une série de dates de :start_date à :end_date, avec un intervalle d’un jour entre chaque date. Cette série est utilisée pour garantir que le rapport inclut chaque jour dans la plage spécifiée, même s’il n’y a pas d’activité utilisateur certains jours.
  • DAU : La CTE DAU calcule le nombre d’utilisateurs uniques qui ont visité le site chaque jour (dau_count). Elle le fait en joignant la table user_visits avec la CTE date_series à la condition que la date de visite corresponde à la date de la série. Le résultat est regroupé par date pour obtenir le nombre d’utilisateurs uniques pour chaque jour.
  • MAU : La CTE MAU calcule le nombre d’utilisateurs uniques qui ont visité le site dans les 30 jours précédant chaque date de la date_series. Elle le fait en joignant la table user_visits avec la CTE date_series, où la date de visite se situe entre 30 jours avant et jusqu’à, et y compris, la date de la série. Le résultat est regroupé par date pour obtenir le nombre d’utilisateurs uniques pour chaque période de 30 jours précédant chaque date.

Sélection finale

La dernière partie de la requête calcule le ratio DAU/MAU pour chaque jour en divisant le nombre de DAU par le nombre de MAU et en multipliant par 100 pour obtenir un pourcentage. Elle sélectionne également les nombres de DAU et de MAU à titre de référence. La fonction NULLIF est utilisée pour éviter la division par zéro, qui se produirait s’il n’y avait pas de MAU pour un jour donné. Le résultat est trié par jour pour fournir une vue chronologique de l’engagement des utilisateurs.

Résultats d’exemple

jour 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
… … … …
2 « J'aime »
2

Ajout d’une version modifiée de ce rapport qui montre uniquement les statistiques MAU (Utilisateurs Actifs Mensuels) au cas où cela pourrait être utile à quelqu’un :

MAU (Utilisateurs Actifs Mensuels)

-- [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 "Mois",
  mau_count AS "Utilisateurs Actifs Mensuels"
FROM MAU
WHERE month <= CURRENT_DATE
ORDER BY month

Exemple de résultats

Mois Utilisateurs Actifs Mensuels
2024-07-01 2
2024-08-01 3
… …
3 « J'aime »
3

Merci, @SaraDev, c’est utile pour nous. Puisque nous sommes une petite communauté où le personnel est beaucoup plus actif que les utilisateurs réguliers, j’ai (enfin, moi et ChatGPT) modifié la requête pour pouvoir exécuter séparément ou combiné les utilisateurs réguliers et les utilisateurs du personnel. Et j’aime le voir sous forme de graphique, j’ai donc modifié pour l’activer.
J’ai récemment vu comment télécharger le schéma de la base de données dans cette astuce de @tyler.lamparter, et je télécharge cela dans ChatGPT au début de ces sessions ; cela rend plus facile de demander à chatty de séparer staff, par exemple.

-- [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) -- Tous les utilisateurs
    OR (:include_staff = 0 AND user_id NOT IN (SELECT user_id FROM staff_users)) -- Uniquement les non-membres du personnel
    OR (:include_staff = 2 AND user_id IN (SELECT user_id FROM staff_users)) -- Uniquement les membres du personnel
  )
  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) -- Tous les utilisateurs
    OR (:include_staff = 0 AND user_id NOT IN (SELECT user_id FROM staff_users)) -- Uniquement les non-membres du personnel
    OR (:include_staff = 2 AND user_id IN (SELECT user_id FROM staff_users)) -- Uniquement les membres du personnel
  )
  GROUP BY month
)
SELECT
  d.day::DATE AS date,  -- ✅ Axe X pour le graphique
  ROUND((d.dau::numeric / NULLIF(m.mau, 0)::numeric) * 100, 1)::FLOAT AS dau_mau_ratio  -- ✅ Axe Y pour le graphique
FROM daily_users d
JOIN monthly_users m ON date_trunc('month', d.day) = m.month
ORDER BY date
-- 0 = Uniquement les non-membres du personnel, 1 = Tous les utilisateurs, 2 = Uniquement les membres du personnel
1 « J'aime »