Dashboard-Bericht - DAU/MAU

Daten & Berichterstattung
, ,
1

Dies ist eine SQL-Version des Dashboard-Berichts für DAU/MAU.

Dieser Bericht bietet eine detaillierte Analyse des Verhältnisses von täglich aktiven Benutzern (DAU) zu monatlich aktiven Benutzern (MAU) über einen bestimmten Zeitraum. Das DAU/MAU-Verhältnis ist ein wichtiger Leistungsindikator (KPI), der das Benutzerengagement misst, indem die Anzahl der eindeutigen aktiven Benutzer pro Tag mit der Anzahl der eindeutigen aktiven Benutzer über einen Zeitraum von 30 Tagen verglichen wird.

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

SQL-Abfrageerklärung

Die SQL-Abfrage verwendet Common Table Expressions (CTEs), um zwei Datensätze zu erstellen: einen für DAU und einen für MAU. Anschließend werden diese Datensätze kombiniert, um das DAU/MAU-Verhältnis für jeden Tag innerhalb des angegebenen Zeitraums zu berechnen.

Parameter

Die Abfrage akzeptiert zwei Parameter, :start_date und :end_date, mit denen der Benutzer den Zeitraum für den Bericht festlegen kann. Beide Datumsparameter akzeptieren das Datumsformat JJJJ-MM-TT.

CTEs

  • date_series: Die CTE date_series generiert eine Reihe von Daten vom :start_date bis zum :end_date mit einem Intervall von einem Tag zwischen jedem Datum. Diese Reihe wird verwendet, um sicherzustellen, dass der Bericht jeden Tag innerhalb des angegebenen Bereichs enthält, auch wenn an einigen Tagen keine Benutzeraktivität stattfindet.
  • DAU: Die CTE DAU berechnet die Anzahl der eindeutigen Benutzer, die die Website an jedem Tag besucht haben (dau_count). Dies geschieht durch Verknüpfung der Tabelle user_visits mit der CTE date_series unter der Bedingung, dass das Besuchsdatum mit dem Datum in der Reihe übereinstimmt. Das Ergebnis wird nach Datum gruppiert, um die Anzahl der eindeutigen Benutzer für jeden Tag zu ermitteln.
  • MAU: Die CTE MAU berechnet die Anzahl der eindeutigen Benutzer, die die Website innerhalb der 30 Tage vor jedem Datum in der date_series besucht haben. Dies geschieht durch Verknüpfung der Tabelle user_visits mit der CTE date_series, wobei das Besuchsdatum zwischen 30 Tagen vor und bis einschließlich des Datums in der Reihe liegt. Das Ergebnis wird nach Datum gruppiert, um die Anzahl der eindeutigen Benutzer für jeden 30-Tage-Zeitraum vor jedem Datum zu ermitteln.

Finale Auswahl

Der letzte Teil der Abfrage berechnet das DAU/MAU-Verhältnis für jeden Tag, indem die DAU-Anzahl durch die MAU-Anzahl geteilt und mit 100 multipliziert wird, um einen Prozentsatz zu erhalten. Außerdem werden die Anzahlen von DAU und MAU als Referenz ausgewählt. Die Funktion NULLIF wird verwendet, um eine Division durch Null zu vermeiden, die auftreten würde, wenn an einem bestimmten Tag keine MAU vorhanden sind. Das Ergebnis wird nach Datum sortiert, um eine chronologische Ansicht des Benutzerengagements zu erhalten.

Beispielergebnisse

Tag 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 „Gefällt mir“
2

Hier wird eine modifizierte Version dieses Berichts hinzugefügt, die nur die MAU (Monthly Active Users)-Statistiken anzeigt, falls dies für jemanden hilfreich sein könnte:

MAU (Monatlich aktive Nutzer)

-- [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 "Monat",
  mau_count AS "Monatlich aktive Nutzer"
FROM MAU
WHERE month <= CURRENT_DATE
ORDER BY month

Beispielergebnisse

Monat Monatlich aktive Nutzer
2024-07-01 2
2024-08-01 3
… …
3 „Gefällt mir“
3

Danke, @SaraDev, das ist nützlich für uns. Da wir eine kleine Community mit viel aktiveren Mitarbeitern als normalen Benutzern sind, habe ich (nun ja, ich und ChatGPT) die Abfrage so modifiziert, dass sie normale Benutzer und Mitarbeiter getrennt oder kombiniert ausführen kann. Und ich sehe es gerne grafisch dargestellt, also habe ich es so modifiziert, dass es dies ermöglicht.
Ich habe kürzlich gesehen, wie man das Datenbankschema in diesem Tipp von @tyler.lamparter herunterladen kann, und ich lade das zu Beginn dieser Sitzungen in ChatGPT hoch. Das macht es einfacher, Chatty zu bitten, z. B. staff zu trennen.

-- [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) -- Alle Benutzer
    OR (:include_staff = 0 AND user_id NOT IN (SELECT user_id FROM staff_users)) -- Nur Nicht-Mitarbeiter
    OR (:include_staff = 2 AND user_id IN (SELECT user_id FROM staff_users)) -- Nur Mitarbeiter
  )
  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) -- Alle Benutzer
    OR (:include_staff = 0 AND user_id NOT IN (SELECT user_id FROM staff_users)) -- Nur Nicht-Mitarbeiter
    OR (:include_staff = 2 AND user_id IN (SELECT user_id FROM staff_users)) -- Nur Mitarbeiter
  )
  GROUP BY month
)
SELECT
  d.day::DATE AS date,  -- ✅ X-Achse für die grafische Darstellung
  ROUND((d.dau::numeric / NULLIF(m.mau, 0)::numeric) * 100, 1)::FLOAT AS dau_mau_ratio  -- ✅ Y-Achse für die grafische Darstellung
FROM daily_users d
JOIN monthly_users m ON date_trunc('month', d.day) = m.month
ORDER BY date
-- 0 = Nur Nicht-Mitarbeiter, 1 = Alle Benutzer, 2 = Nur Mitarbeiter
1 „Gefällt mir“