Questo è un report di analisi di coorte in versione SQL per l’attività degli utenti, da utilizzare all’interno del plugin Data Explorer.
Questo report richiede che il plugin Discourse Solved sia abilitato.
Questo report di analisi di coorte fornisce informazioni sull’engagement degli utenti monitorando l’attività degli utenti che si sono iscritti a partire da una data di inizio specificata. Si concentra in particolare sugli utenti che soddisfano o superano determinate soglie per i post creati e le soluzioni fornite ogni mese dopo la loro registrazione.
Questo report è prezioso per gli amministratori che desiderano comprendere l’efficacia della loro community nel mantenere e coinvolgere i nuovi utenti nel tempo, in particolare per quanto riguarda la pubblicazione e la fornitura di soluzioni agli argomenti. Questo report può anche essere utile per valutare la salute della community e identificare l’efficacia delle strategie di crescita della community.
Utenti Attivi per Mese dopo l’Iscrizione con Parametri Minimi di Post + Soluzioni
--[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 "Iscritto a", -- Include the year in the Joined In column
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
Parametri
Questo report utilizza tre parametri:
start_date: La data da cui iniziare a tracciare le nuove iscrizioni degli utenti.min_posts_per_month: Il numero minimo di post che un utente deve effettuare in un mese per essere considerato attivo.min_solutions_per_month: Il numero minimo di soluzioni (risposte accettate) che un utente deve fornire in un mese per essere considerato attivo.
Gli utenti devono soddisfare sia i requisiti min_posts_per_month che min_solutions_per_month per essere considerati attivi per il mese.
CTE
La query SQL fornita utilizza diverse Common Table Expressions (CTE) per scomporre il processo di calcolo degli utenti attivi per mese dopo l’iscrizione, in base ai criteri minimi di post e soluzioni. Ecco una spiegazione di ciascuna CTE:
user_cohorts
Questa CTE identifica le coorti di utenti in base al loro mese di iscrizione. Per ogni utente, calcola il mese della sua iscrizione (cohort) e conta il numero totale di utenti che si sono iscritti nello stesso mese. Ciò aiuta a comprendere la dimensione iniziale di ciascuna coorte.
posts_activity
Questa CTE traccia l’attività degli utenti in termini di post effettuati dopo la loro iscrizione. Per ogni post, calcola quanti mesi sono trascorsi dalla data di registrazione dell’utente (months_after_registration) e raggruppa questi dati per coorte di iscrizione dell’utente. Questo viene utilizzato per tracciare quanto sono attivi gli utenti in termini di creazione di contenuti nel tempo.
solutions_counts
Questa CTE si concentra sul conteggio delle soluzioni (risposte accettate) fornite dagli utenti. Filtra i post contrassegnati come soluzioni e li conta per ciascun utente, assicurando che vengano considerati solo i post effettuati dopo l’iscrizione dell’utente. Calcola anche quanti mesi sono trascorsi dall’iscrizione dell’utente per ciascuna soluzione. Gli utenti sono inclusi in questo conteggio solo se soddisfano o superano il numero minimo specificato di soluzioni al mese.
activity_counts
Questa CTE aggrega il numero di post effettuati da ciascun utente al mese dopo la registrazione. Raggruppa gli utenti per la loro coorte di iscrizione e il numero di mesi dalla registrazione, quindi conta il numero di post effettuati. Solo gli utenti che soddisfano o superano il numero minimo specificato di post al mese sono inclusi in questo conteggio.
active_users
Questa CTE combina i dati da solutions_counts e activity_counts per identificare gli utenti attivi, ovvero quelli che soddisfano entrambi i criteri di post e soluzioni. Conta gli utenti distinti che sono attivi in base ai criteri impostati per post e soluzioni, raggruppati per la loro coorte di iscrizione e il numero di mesi dalla registrazione.
cohorts_series
Questa CTE genera una serie di numeri da 0 a 11, che rappresentano il numero di mesi dopo la registrazione. Questo viene utilizzato per garantire che il report finale includa i dati per ogni mese fino a 12 mesi, anche se non ci sono utenti attivi in alcuni mesi.
cohorts
Questa CTE aggrega i dati da user_cohorts per ottenere il numero totale di utenti che si sono iscritti in ciascuna coorte. Assicura che il report finale includa il numero totale di utenti iscritti per ciascuna coorte.
cross_join
Questa CTE esegue un cross join tra la CTE cohorts e la CTE cohorts_series. Ciò garantisce che ogni coorte sia rappresentata per ogni mese dopo l’iscrizione, facilitando il calcolo degli utenti attivi per ogni mese nel passaggio finale.
final_counts
Questa CTE combina tutte le CTE precedenti per calcolare i conteggi finali degli utenti attivi per ogni mese dopo l’iscrizione, per ogni coorte. Utilizza un left join per associare gli utenti attivi dalla CTE active_users con le coorti e i mesi generati nella CTE cross_join. Assicura che ogni coppia coorte-mese abbia un conteggio di utenti attivi, con un valore predefinito di 0 se non ci sono utenti attivi per quella coppia.
SELECT finale
L’istruzione SELECT finale nella query raggruppa gli utenti in coorti in base al loro mese e anno di iscrizione, e quindi calcola il numero di utenti attivi per ogni mese fino a un anno dopo l’iscrizione. Ciò viene fatto attraverso una combinazione di trasformazioni e aggregazioni condizionali, che pivotano i dati in un formato in cui ogni riga corrisponde a una coorte e ogni colonna rappresenta il numero di utenti attivi per ogni mese dopo l’iscrizione, da “Mese 1” a “Mese 12”.
Risultati
Il report produce una tabella con le seguenti colonne:
- Iscritto a: Il mese e l’anno della coorte (quando gli utenti si sono iscritti).
- Utenti Iscritti: Il numero totale di utenti che si sono iscritti in quella coorte.
- Mese 1 a Mese 12: Il numero di utenti attivi per ogni mese dopo l’iscrizione, fino a 12 mesi.
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 | 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 |
| … | … | … | … | … | … | … | … | … | … | … | … | … | … |
I risultati completi del report produrranno un anno di dati dopo la start_date.