Report di Analisi di Coorte - Attività Utente Mensile

Dati e reportistica
1

Questo è un rapporto di analisi di coorte in versione SQL per l’attività degli utenti, da utilizzare all’interno del plugin Data Explorer.

Il rapporto di analisi di coorte è progettato per fornire agli amministratori informazioni sull’engagement degli utenti nel tempo. Analizzando l’attività degli utenti raggruppati per il mese di registrazione (coorti), questo rapporto tiene traccia del numero di utenti attivi ogni mese dopo la registrazione che soddisfano un criterio minimo di attività di posting.

Questo rapporto può essere una risorsa preziosa per comprendere la fidelizzazione degli utenti, le tendenze di engagement, valutare la salute della community e identificare l’efficacia delle strategie di crescita della community.

Rapporto di Analisi di Coorte - Utenti Attivi per Mese

--[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 -- Usa il parametro start_date per filtrare gli utenti
),
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 -- Filtra gli utenti in base al numero minimo di post al mese
),
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 -- Aggrega per ottenere il totale degli utenti iscritti per ogni coorte
    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 "Iscritto a", -- Includi l'anno nella colonna Iscritto a
    users_signed_up AS "Utenti Iscritti",
    MAX(CASE WHEN months_after_registration = 0 THEN active_users END) AS "Mese 1",
    MAX(CASE WHEN months_after_registration = 1 THEN active_users END) AS "Mese 2",
    MAX(CASE WHEN months_after_registration = 2 THEN active_users END) AS "Mese 3",
    MAX(CASE WHEN months_after_registration = 3 THEN active_users END) AS "Mese 4",
    MAX(CASE WHEN months_after_registration = 4 THEN active_users END) AS "Mese 5",
    MAX(CASE WHEN months_after_registration = 5 THEN active_users END) AS "Mese 6",
    MAX(CASE WHEN months_after_registration = 6 THEN active_users END) AS "Mese 7",
    MAX(CASE WHEN months_after_registration = 7 THEN active_users END) AS "Mese 8",
    MAX(CASE WHEN months_after_registration = 8 THEN active_users END) AS "Mese 9",
    MAX(CASE WHEN months_after_registration = 9 THEN active_users END) AS "Mese 10",
    MAX(CASE WHEN months_after_registration = 10 THEN active_users END) AS "Mese 11",
    MAX(CASE WHEN months_after_registration = 11 THEN active_users END) AS "Mese 12"
FROM final_counts
GROUP BY cohort, users_signed_up
ORDER BY cohort

Spiegazione della Query SQL

Il rapporto opera segmentando gli utenti in coorti in base al mese in cui si sono iscritti. Quindi, tiene traccia di queste coorti per vedere quanti utenti rimangono attivi nei mesi successivi, in base a un numero minimo definito di post al mese.

Parametri

Questo rapporto ha due parametri:

  • start_date: La data di inizio da cui gli utenti vengono considerati per l’analisi di coorte. Gli utenti che si sono iscritti dopo questa data sono inclusi nel rapporto.
  • min_posts_per_month: Il numero minimo di post che un utente deve effettuare in un mese per essere considerato attivo per quel mese.

CTE

Il rapporto di analisi di coorte utilizza diverse espressioni di tabella comuni (CTE) per organizzare ed elaborare i dati per l’analisi. Ogni CTE serve a uno scopo specifico nella query generale, basandosi su quelle precedenti per produrre infine il rapporto finale. Ecco una ripartizione di come opera ogni CTE:

1. user_cohorts

Questa CTE identifica le coorti in base al mese in cui gli utenti si sono iscritti. Per ogni utente, calcola la coorte a cui appartiene troncando il suo timestamp created_at al mese. Conta anche il numero di utenti che si sono iscritti in ogni coorte.

  • Operazioni chiave:
    • DATE_TRUNC('month', created_at) AS cohort: Tronca il timestamp created_at alla granularità del mese, raggruppando efficacemente gli utenti in base al loro mese di iscrizione.
    • COUNT(id) OVER (PARTITION BY DATE_TRUNC('month', created_at)): Conta il numero di utenti in ogni coorte.

2. posts_activity

Questa CTE tiene traccia dell’attività di posting degli utenti rispetto alla loro data di registrazione. Unisce le tabelle posts e users per associare ogni post all’utente che lo ha creato e calcola quanti mesi sono trascorsi dalla registrazione dell’utente al momento di ogni post.

  • Operazioni chiave:
    • EXTRACT(YEAR FROM AGE(p.created_at, u.created_at)) * 12 + EXTRACT(MONTH FROM AGE(p.created_at, u.created_at)): Calcola il numero di mesi trascorsi dalla registrazione dell’utente per ogni post.
    • DATE_TRUNC('month', u.created_at) AS cohort: Identifica la coorte dell’utente in base al mese di registrazione.

3. activity_counts

Questa CTE aggrega l’attività di posting da posts_activity per contare il numero di post che ogni utente ha effettuato in ogni mese dopo la registrazione. Filtra questi conteggi per includere solo gli utenti che soddisfano l’attività minima di posting specificata dal parametro min_posts_per_month.

  • Operazioni chiave:
    • GROUP BY cohort, months_after_registration, user_id: Raggruppa i dati per coorte, mesi dopo la registrazione e ID utente per preparare il conteggio dei post.
    • HAVING COUNT(user_id) >= :min_posts_per_month: Filtra i dati raggruppati per includere solo gli utenti che hanno effettuato almeno il numero minimo di post in un mese.

4. active_users

Questa CTE aggrega ulteriormente i dati da activity_counts per contare il numero di utenti attivi distinti in ogni coorte per ogni mese dopo la registrazione.

  • Operazioni chiave:
    • COUNT(DISTINCT user_id) AS active_users: Conta il numero di utenti attivi unici in ogni coorte per ogni mese dopo la registrazione.

5. cohorts_series

Questa CTE genera una serie di interi da 0 a 11, che rappresentano i mesi dopo la registrazione. Questa serie viene utilizzata per garantire che il rapporto finale includa tutti i mesi fino a 12 per ogni coorte, anche se mancano dati di attività per alcuni mesi.

  • Operazioni chiave:
    • generate_series(0, 11): Genera una serie di interi da 0 a 11.

6. cohorts

Questa CTE aggrega i dati da user_cohorts per ottenere il numero totale di utenti iscritti per ogni coorte.

  • Operazioni chiave:
    • MAX(users_signed_up) AS users_signed_up: Aggrega il numero totale di utenti iscritti per ogni coorte.

7. cross_join

Questa CTE esegue un cross join tra cohorts e cohorts_series per creare una griglia di tutte le possibili combinazioni di coorti e mesi dopo la registrazione. Ciò garantisce che il rapporto finale includa righe per ogni mese per ogni coorte, facilitando il calcolo degli utenti attivi per mese.

8. final_counts

Questa CTE combina i dati da cross_join e active_users per calcolare il conteggio finale degli utenti attivi per ogni coorte per ogni mese dopo la registrazione. Utilizza un left join per garantire che vengano incluse tutte le combinazioni di coorti e mesi, anche se non ci sono utenti attivi per alcuni.

  • Operazioni chiave:
    • COALESCE(au.active_users, 0) AS active_users: Assicura che il rapporto mostri 0 utenti attivi per le combinazioni senza attività, anziché lasciarle vuote.

L’istruzione SELECT finale al di fuori delle CTE formatta e presenta quindi questi dati, mostrando il numero di utenti iscritti e il numero di utenti attivi per ogni mese dopo la registrazione per ogni coorte.

Risultati

Il rapporto genera una tabella con le seguenti colonne:

  • Iscritto a: Il mese e l’anno in cui è stata creata la coorte, indicando quando questi utenti si sono iscritti.
  • Utenti Iscritti: Il numero totale di utenti che si sono iscritti in quella coorte.
  • Mese 1 a Mese 12: Ciascuna di queste colonne rappresenta il numero di utenti attivi per la coorte in ogni mese successivo all’iscrizione, fino a 12 mesi. Un utente attivo è definito come qualcuno che ha effettuato almeno il numero minimo di post specificato dal parametro min_posts_per_month.

Risultati di esempio

Iscritto a Utenti Iscritti Mese 1 Mese 2 Mese 3 Mese 4 Mese 5 Mese 6 Mese 7 Mese 8 Mese 9 Mese 10 Mese 11 Mese 12
Gen 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
… … … … … … … … … … … … … …

I risultati completi del rapporto produrranno un anno di dati dopo la start_date.

3 Mi Piace