Rapport du tableau de bord - DAU/MAU

Données et rapports
, ,
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 »