Cohort Analysis Report - Monatliche Nutzeraktivität

[SaraDev (Sara Devlaeminck)]

Dies ist eine SQL-Version eines Kohortenanalyseberichts für Benutzeraktivitäten, zur Verwendung im Data Explorer-Plugin.
Der Kohortenanalysebericht soll Administratoren Einblicke in das Benutzerengagement im Laufe der Zeit geben. Durch die Analyse der Aktivität von Benutzern, die nach ihrem Registrierungsmonat (Kohorten) gruppiert sind, verfolgt dieser Bericht die Anzahl der aktiven Benutzer jeden Monat nach der Registrierung, die ein Mindestkriterium für die Posting-Aktivität erfüllen.
Dieser Bericht kann eine wertvolle Ressource für das Verständnis der Benutzerbindung, Engagement-Trends, die Bewertung der Community-Gesundheit und die Identifizierung der Wirksamkeit von Community-Wachstumsstrategien sein.

Kohortenanalysebericht - Aktive Benutzer pro Monat

--[params]
-- date :start_date = 2023-01-01
-- int :min_posts_per_month = 1

WITH user_cohorts AS (
    SELECT
        id AS user_id,
        DATE_TRUNC('month', created_at) AS cohort,
        COUNT(id) OVER (PARTITION BY DATE_TRUNC('month', created_at)) AS users_signed_up
    FROM users
    WHERE created_at >= :start_date -- Verwenden Sie den Parameter start_date, um Benutzer zu filtern
),
posts_activity AS (
    SELECT
        p.user_id,
        EXTRACT(YEAR FROM AGE(p.created_at, u.created_at)) * 12 + EXTRACT(MONTH FROM AGE(p.created_at, u.created_at)) AS months_after_registration,
        DATE_TRUNC('month', u.created_at) AS cohort
    FROM posts p
    JOIN users u ON p.user_id = u.id
    WHERE p.created_at >= u.created_at
),
activity_counts AS (
    SELECT
        cohort,
        months_after_registration,
        COUNT(user_id) AS posts_count,
        user_id
    FROM posts_activity
    GROUP BY cohort, months_after_registration, user_id
    HAVING COUNT(user_id) >= :min_posts_per_month -- Filtern Sie Benutzer nach der Mindestanzahl von Beiträgen pro Monat
),
active_users AS (
    SELECT
        cohort,
        months_after_registration,
        COUNT(DISTINCT user_id) AS active_users
    FROM activity_counts
    GROUP BY cohort, months_after_registration
),
cohorts_series AS (
    SELECT generate_series AS months_after_registration
    FROM generate_series(0, 11)
),
cohorts AS (
    SELECT
        cohort,
        MAX(users_signed_up) AS users_signed_up -- Aggregieren, um die Gesamtzahl der für jede Kohorte angemeldeten Benutzer zu erhalten
    FROM user_cohorts
    GROUP BY cohort
),
cross_join AS (
    SELECT
        c.cohort,
        c.users_signed_up,
        cs.months_after_registration
    FROM cohorts c
    CROSS JOIN cohorts_series cs
),
final_counts AS (
    SELECT
        cj.cohort,
        cj.users_signed_up,
        cj.months_after_registration,
        COALESCE(au.active_users, 0) AS active_users
    FROM cross_join cj
    LEFT JOIN active_users au ON au.cohort = cj.cohort AND au.months_after_registration = cj.months_after_registration
)
SELECT
    TO_CHAR(cohort, 'Mon YYYY') AS "Joined In", -- Fügen Sie das Jahr in die Spalte "Joined In" ein
    users_signed_up AS "Users Signed Up",
    MAX(CASE WHEN months_after_registration = 0 THEN active_users END) AS "Month 1",
    MAX(CASE WHEN months_after_registration = 1 THEN active_users END) AS "Month 2",
    MAX(CASE WHEN months_after_registration = 2 THEN active_users END) AS "Month 3",
    MAX(CASE WHEN months_after_registration = 3 THEN active_users END) AS "Month 4",
    MAX(CASE WHEN months_after_registration = 4 THEN active_users END) AS "Month 5",
    MAX(CASE WHEN months_after_registration = 5 THEN active_users END) AS "Month 6",
    MAX(CASE WHEN months_after_registration = 6 THEN active_users END) AS "Month 7",
    MAX(CASE WHEN months_after_registration = 7 THEN active_users END) AS "Month 8",
    MAX(CASE WHEN months_after_registration = 8 THEN active_users END) AS "Month 9",
    MAX(CASE WHEN months_after_registration = 9 THEN active_users END) AS "Month 10",
    MAX(CASE WHEN months_after_registration = 10 THEN active_users END) AS "Month 11",
    MAX(CASE WHEN months_after_registration = 11 THEN active_users END) AS "Month 12"
FROM final_counts
GROUP BY cohort, users_signed_up
ORDER BY cohort

Erklärung der SQL-Abfrage

Der Bericht funktioniert, indem Benutzer in Kohorten basierend auf dem Monat ihres Beitritts segmentiert werden. Anschließend werden diese Kohorten verfolgt, um zu sehen, wie viele Benutzer in den folgenden Monaten aktiv bleiben, basierend auf einer definierten Mindestanzahl von Beiträgen pro Monat.

Parameter

Dieser Bericht hat zwei Parameter:

• start_date: Das Anfangsdatum, ab dem Benutzer für die Kohortenanalyse berücksichtigt werden. Benutzer, die nach diesem Datum beigetreten sind, werden in den Bericht aufgenommen.
• min_posts_per_month: Die Mindestanzahl von Beiträgen, die ein Benutzer pro Monat leisten muss, um für diesen Monat als aktiv zu gelten.

CTEs

Der Kohortenanalysebericht verwendet mehrere Common Table Expressions (CTEs), um Daten für die Analyse zu organisieren und zu verarbeiten. Jede CTE dient einem bestimmten Zweck in der Gesamtabfrage und baut auf den vorherigen auf, um letztendlich den endgültigen Bericht zu erstellen. Hier ist eine Aufschlüsselung, wie jede CTE funktioniert:

1. user_cohorts
Diese CTE identifiziert die Kohorten basierend auf dem Monat, in dem Benutzer beigetreten sind. Für jeden Benutzer berechnet sie die Kohorte, zu der er gehört, indem er seinen created_at-Zeitstempel auf den Monat kürzt. Sie zählt auch die Anzahl der Benutzer, die sich in jeder Kohorte angemeldet haben.

• Schlüsseloperationen:
  • DATE_TRUNC('month', created_at) AS cohort: Kürzt den created_at-Zeitstempel auf Monatsgranularität und gruppiert effektiv Benutzer nach ihrem Anmeldemonat.
  • COUNT(id) OVER (PARTITION BY DATE_TRUNC('month', created_at)): Zählt die Anzahl der Benutzer in jeder Kohorte.

2. posts_activity
Diese CTE verfolgt die Posting-Aktivität von Benutzern im Verhältnis zu ihrem Registrierungsdatum. Sie verknüpft die Tabellen posts und users, um jeden Beitrag dem Benutzer zuzuordnen, der ihn erstellt hat, und berechnet, wie viele Monate seit der Registrierung des Benutzers zum Zeitpunkt jedes Beitrags vergangen sind.

• Schlüsseloperationen:
  • EXTRACT(YEAR FROM AGE(p.created_at, u.created_at)) * 12 + EXTRACT(MONTH FROM AGE(p.created_at, u.created_at)): Berechnet die Anzahl der Monate, die seit der Registrierung des Benutzers für jeden Beitrag vergangen sind.
  • DATE_TRUNC('month', u.created_at) AS cohort: Identifiziert die Kohorte des Benutzers basierend auf seinem Registrierungsmonat.

3. activity_counts
Diese CTE aggregiert die Posting-Aktivität aus posts_activity, um die Anzahl der Beiträge zu zählen, die jeder Benutzer in jedem Monat nach der Registrierung erstellt hat. Sie filtert diese Zählungen, um nur Benutzer einzuschließen, die die Mindestposting-Aktivität erfüllen, die durch den Parameter min_posts_per_month angegeben ist.

• Schlüsseloperationen:
  • GROUP BY cohort, months_after_registration, user_id: Gruppiert die Daten nach Kohorte, Monate nach Registrierung und Benutzer-ID, um die Beiträge zu zählen.
  • HAVING COUNT(user_id) >= :min_posts_per_month: Filtert die gruppierten Daten, um nur Benutzer einzuschließen, die mindestens die Mindestanzahl von Beiträgen in einem Monat geleistet haben.

4. active_users
Diese CTE aggregiert die Daten aus activity_counts weiter, um die Anzahl der einzelnen aktiven Benutzer in jeder Kohorte für jeden Monat nach der Registrierung zu zählen.

• Schlüsseloperationen:
  • COUNT(DISTINCT user_id) AS active_users: Zählt die Anzahl der eindeutigen aktiven Benutzer in jeder Kohorte für jeden Monat nach der Registrierung.

5. cohorts_series
Diese CTE generiert eine Reihe von ganzen Zahlen von 0 bis 11, die die Monate nach der Registrierung darstellen. Diese Reihe wird verwendet, um sicherzustellen, dass der endgültige Bericht alle Monate bis zu 12 für jede Kohorte enthält, auch wenn für einige Monate keine Aktivitätsdaten vorhanden sind.

• Schlüsseloperationen:
  • generate_series(0, 11): Generiert eine Reihe von ganzen Zahlen von 0 bis 11.

6. cohorts
Diese CTE aggregiert die Daten aus user_cohorts, um die Gesamtzahl der für jede Kohorte angemeldeten Benutzer zu erhalten.

• Schlüsseloperationen:
  • MAX(users_signed_up) AS users_signed_up: Aggregiert die Gesamtzahl der für jede Kohorte angemeldeten Benutzer.

7. cross_join
Diese CTE führt einen Cross Join zwischen cohorts und cohorts_series durch, um ein Raster aller möglichen Kombinationen von Kohorten und Monaten nach der Registrierung zu erstellen. Dies stellt sicher, dass der endgültige Bericht Zeilen für jeden Monat für jede Kohorte enthält, was die Berechnung aktiver Benutzer pro Monat erleichtert.

8. final_counts
Diese CTE kombiniert die Daten aus cross_join und active_users, um die endgültige Anzahl aktiver Benutzer für jede Kohorte für jeden Monat nach der Registrierung zu berechnen. Sie verwendet einen Left Join, um sicherzustellen, dass alle Kombinationen von Kohorten und Monaten enthalten sind, auch wenn für einige keine aktiven Benutzer vorhanden sind.

• Schlüsseloperationen:
  • COALESCE(au.active_users, 0) AS active_users: Stellt sicher, dass der Bericht 0 aktive Benutzer für Kombinationen ohne Aktivität anzeigt, anstatt sie leer zu lassen.

Die endgültige SELECT-Anweisung außerhalb der CTEs formatiert und präsentiert diese Daten und zeigt die Anzahl der angemeldeten Benutzer und die Anzahl der aktiven Benutzer für jeden Monat nach der Registrierung für jede Kohorte an.

Ergebnisse

Der Bericht generiert eine Tabelle mit den folgenden Spalten:

• Joined In: Der Monat und das Jahr, in dem die Kohorte erstellt wurde, was angibt, wann sich diese Benutzer angemeldet haben.
• Users Signed Up: Die Gesamtzahl der Benutzer, die sich in dieser Kohorte angemeldet haben.
• Month 1 bis Month 12: Jede dieser Spalten stellt die Anzahl der aktiven Benutzer für die Kohorte in jedem aufeinanderfolgenden Monat nach dem Beitritt dar, bis zu 12 Monate. Ein aktiver Benutzer ist definiert als jemand, der mindestens die Mindestanzahl von Beiträgen geleistet hat, die durch den Parameter min_posts_per_month angegeben sind.

Beispielergebnisse

Joined In	Users Signed Up	Month 1	Month 2	Month 3	Month 4	Month 5	Month 6	Month 7	Month 8	Month 9	Month 10	Month 11	Month 12
Jan 2023	120	40	8	4	3	3	3	4	3	2	1	1	4
Feb 2023	119	40	7	5	3	2	2	7	2	2	2	1	1
…	…	…	…	…	…	…	…	…	…	…	…	…	…

Die vollständigen Ergebnisse des Berichts liefern ein Jahr an Daten nach dem start_date.