Cohort Analysis Report - Monatliche Benutzeraktivität nach Beiträgen und Lösungen

[SaraDev (Sara Devlaeminck)]

Dies ist eine SQL-Version eines Kohortenanalyseberichts für Benutzeraktivität, zur Verwendung innerhalb des Data Explorer Plugins.

Dieser Bericht erfordert, dass das Discourse Solved Plugin aktiviert ist.

Dieser Kohortenanalysebericht liefert Einblicke in das Benutzerengagement, indem er die Aktivität von Benutzern verfolgt, die sich seit einem bestimmten Startdatum angemeldet haben. Er konzentriert sich speziell auf Benutzer, die jeden Monat nach ihrer Registrierung bestimmte Schwellenwerte für erstellte Beiträge und bereitgestellte Lösungen erreichen oder überschreiten.

Dieser Bericht ist wertvoll für Administratoren, die verstehen möchten, wie effektiv ihre Community neue Benutzer im Laufe der Zeit bindet und engagiert, insbesondere in Bezug auf das Posten und das Anbieten von Lösungen für Themen. Dieser Bericht kann auch nützlich sein, um die Gesundheit der Community zu bewerten und die Wirksamkeit von Strategien zum Community-Wachstum zu identifizieren.

Aktive Benutzer pro Monat nach Anmeldung mit Min. Beiträge + Lösungen Parametern

--[params]
-- date :start_date = 2023-01-01
-- int :min_posts_per_month = 1
-- int :min_solutions_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
),
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
),
solutions_counts AS (
    SELECT
        p.user_id,
        COUNT(p.user_id) as solutions_count,
        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 discourse_solved_solved_topics dsst
    INNER JOIN posts p ON p.id = dsst.answer_post_id
    JOIN topics t ON t.id = p.topic_id
    JOIN users u ON p.user_id = u.id
    WHERE p.created_at >= u.created_at
    GROUP BY months_after_registration, cohort, p.user_id
    HAVING COUNT(p.user_id) >= :min_solutions_per_month
),
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
),
active_users AS (
    SELECT
        sc.cohort,
        sc.months_after_registration,
        COUNT(DISTINCT ac.user_id) AS active_users
    FROM solutions_counts sc
    FULL JOIN activity_counts ac ON sc.user_id = ac.user_id
    AND sc.months_after_registration = ac.months_after_registration
    AND sc.cohort = ac.cohort
    GROUP BY sc.cohort, sc.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
    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", -- Include the year in the Joined In column
    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

SQL-Abfrageerklärung

Parameter

Dieser Bericht verwendet drei Parameter:

• start_date: Das Datum, ab dem neue Benutzeranmeldungen verfolgt werden sollen.
• min_posts_per_month: Die Mindestanzahl von Beiträgen, die ein Benutzer pro Monat erstellen muss, um als aktiv zu gelten.
• min_solutions_per_month: Die Mindestanzahl von Lösungen (akzeptierten Antworten), die ein Benutzer pro Monat bereitstellen muss, um als aktiv zu gelten.

Benutzer müssen sowohl die Anforderungen von min_posts_per_month als auch min_solutions_per_month erfüllen, um für den Monat als aktiv zu gelten.

CTEs

Die bereitgestellte SQL-Abfrage verwendet mehrere Common Table Expressions (CTEs), um den Prozess der Berechnung aktiver Benutzer pro Monat nach der Anmeldung basierend auf Mindestbeitrags- und Lösungsanforderungen zu unterteilen. Hier ist eine Erklärung jeder CTE:

user_cohorts

Diese CTE identifiziert die Kohorten von Benutzern basierend auf ihrem Anmeldemonat. Für jeden Benutzer berechnet sie den Monat seiner Anmeldung (cohort) und zählt die Gesamtzahl der Benutzer, die im selben Monat angemeldet wurden. Dies hilft, die anfängliche Größe jeder Kohorte zu verstehen.

posts_activity

Diese CTE verfolgt die Aktivität von Benutzern in Bezug auf Beiträge, die nach ihrer Anmeldung erstellt wurden. Für jeden Beitrag berechnet sie, wie viele Monate seit dem Registrierungsdatum des Benutzers vergangen sind (months_after_registration) und gruppiert diese nach der Anmelde-Kohorte des Benutzers. Dies wird verwendet, um zu verfolgen, wie aktiv Benutzer im Laufe der Zeit beim Erstellen von Inhalten sind.

solutions_counts

Diese CTE konzentriert sich auf das Zählen der von Benutzern bereitgestellten Lösungen (akzeptierte Antworten). Sie filtert Beiträge, die als Lösungen markiert sind, und zählt diese für jeden Benutzer, wobei sichergestellt wird, dass nur Beiträge berücksichtigt werden, die nach der Anmeldung des Benutzers erstellt wurden. Sie berechnet auch, wie viele Monate seit der Anmeldung des Benutzers für jede Lösung vergangen sind. Benutzer werden in diese Zählung nur einbezogen, wenn sie die angegebene Mindestanzahl von Lösungen pro Monat erreichen oder überschreiten.

activity_counts

Diese CTE aggregiert die Anzahl der Beiträge, die jeder Benutzer pro Monat nach der Registrierung erstellt hat. Sie gruppiert Benutzer nach ihrer Anmelde-Kohorte und der Anzahl der Monate seit der Registrierung und zählt dann die Anzahl der erstellten Beiträge. Nur Benutzer, die die angegebene Mindestanzahl von Beiträgen pro Monat erreichen oder überschreiten, werden in diese Zählung einbezogen.

active_users

Diese CTE kombiniert die Daten aus solutions_counts und activity_counts, um aktive Benutzer zu identifizieren – diejenigen, die sowohl die Beitrags- als auch die Lösungsanforderungen erfüllen. Sie zählt eindeutige Benutzer, die basierend auf den für Beiträge und Lösungen festgelegten Kriterien aktiv sind, gruppiert nach ihrer Anmelde-Kohorte und der Anzahl der Monate seit der Registrierung.

cohorts_series

Diese CTE generiert eine Reihe von Zahlen von 0 bis 11, die die Anzahl der Monate nach der Registrierung darstellen. Dies wird verwendet, um sicherzustellen, dass der endgültige Bericht Daten für jeden Monat bis zu 12 Monate enthält, auch wenn in einigen Monaten keine aktiven Benutzer vorhanden sind.

cohorts

Diese CTE aggregiert die Daten aus user_cohorts, um die Gesamtzahl der Benutzer zu erhalten, die sich in jeder Kohorte angemeldet haben. Sie stellt sicher, dass der endgültige Bericht die Gesamtzahl der angemeldeten Benutzer für jede Kohorte enthält.

cross_join

Diese CTE führt einen Cross Join zwischen der cohorts-CTE und der cohorts_series-CTE durch. Dies stellt sicher, dass jede Kohorte für jeden Monat nach der Anmeldung dargestellt wird, was die Berechnung aktiver Benutzer für jeden Monat im letzten Schritt erleichtert.

final_counts

Diese CTE kombiniert alle vorherigen CTEs, um die endgültigen Zählungen aktiver Benutzer für jeden Monat nach der Anmeldung für jede Kohorte zu berechnen. Sie verwendet einen Left Join, um aktive Benutzer aus der active_users-CTE mit den in der cross_join-CTE generierten Kohorten und Monaten abzugleichen. Sie stellt sicher, dass jedes Kohorten-Monats-Paar eine Anzahl aktiver Benutzer hat, wobei standardmäßig 0 verwendet wird, wenn für dieses Paar keine aktiven Benutzer vorhanden sind.

Finale SELECT

Die finale SELECT-Anweisung in der Abfrage gruppiert Benutzer in Kohorten basierend auf ihrem Anmelde-Monat und -Jahr und berechnet dann die Anzahl der aktiven Benutzer für jeden Monat bis zu einem Jahr nach der Anmeldung. Dies geschieht durch eine Kombination von Transformationen und bedingten Aggregationen, die die Daten in ein Format umwandeln, bei dem jede Zeile einer Kohorte entspricht und jede Spalte die Anzahl der aktiven Benutzer für jeden Monat nach der Anmeldung von „Monat 1“ bis „Monat 12“ darstellt.

Ergebnisse

Der Bericht gibt eine Tabelle mit den folgenden Spalten aus:

• Joined In: Der Monat und das Jahr der Kohorte (wann sich Benutzer angemeldet haben).
• Users Signed Up: Die Gesamtzahl der Benutzer, die sich in dieser Kohorte angemeldet haben.
• Month 1 bis Month 12: Die Anzahl der aktiven Benutzer für jeden Monat nach der Anmeldung, bis zu 12 Monate.

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	50	40	8	4	3	3	3	4	3	2	1	1	4
Feb 2023	63	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.