Query per coorti e funnel
Risponda alle domande sulla fidelizzazione
Query per coorti e funnel è una lezione Digital Marketing Academy gratuita su CoddyKit. Questa è la lezione 4 di 4. Puoi leggere la lezione completa qui gratuitamente — poi esercitati direttamente nel browser con un editor di codice integrato e un tutor IA disponibile 24/7. Fa parte del percorso di apprendimento Digital Marketing Academy, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso Digital Marketing Academy include 4 lezioni in totale.
Che cos'è una coorte?
Una coorte è un gruppo di utenti che condividono un evento iniziale, generalmente il mese di registrazione. Monitorare ogni coorte nel tempo rivela la retention effettiva.
Le metriche aggregate nascondono il churn; le coorti mostrano se i nuovi utenti rimangono davvero attivi.
SELECT user_id,
DATE_TRUNC('month', signup_date) AS cohort_month
FROM users;Definire la coorte
Il primo passaggio consiste nell'assegnare ogni utente alla propria coorte. DATE_TRUNC arrotonda le date di registrazione al mese, così gli utenti vengono raggruppati correttamente.
Salvate questo elemento come base riutilizzabile per tutto ciò che segue.
SELECT DATE_TRUNC('month', signup_date) AS cohort_month,
COUNT(*) AS cohort_size
FROM users
GROUP BY 1
ORDER BY 1;Periodi di attività
La retention misura l'attività nei mesi successivi alla registrazione. Calcolate il periodo come la distanza tra il mese di un ordine e il mese della coorte.
Il periodo 0 è il mese della registrazione, il periodo 1 è il mese successivo e così via.
SELECT o.user_id,
(DATE_PART('year', o.order_date) - DATE_PART('year', u.signup_date)) * 12
+ (DATE_PART('month', o.order_date) - DATE_PART('month', u.signup_date)) AS period
FROM orders o
JOIN users u ON u.user_id = o.user_id;Costruire una griglia della retention
Combinate il mese della coorte e il periodo, quindi contate gli utenti attivi distinti in ogni cella. Il risultato è il classico triangolo della retention.
Ogni riga rappresenta una coorte; ogni colonna rappresenta i mesi trascorsi dalla registrazione.
SELECT DATE_TRUNC('month', u.signup_date) AS cohort,
DATE_PART('month', AGE(o.order_date, u.signup_date)) AS period,
COUNT(DISTINCT o.user_id) AS active
FROM users u
JOIN orders o ON o.user_id = u.user_id
GROUP BY 1, 2;Tasso di retention
I conteggi grezzi sono difficili da confrontare tra coorti di dimensioni diverse. Dividete gli utenti attivi in ogni periodo per la dimensione iniziale della coorte.
Una funzione finestra riporta la dimensione del periodo 0 su ogni riga per calcolare il rapporto.
SELECT cohort, period,
active * 1.0 / FIRST_VALUE(active) OVER (
PARTITION BY cohort ORDER BY period
) AS retention
FROM cohort_activity;Che cos'è un funnel?
Un funnel segue gli utenti attraverso passaggi ordinati: visita, registrazione, aggiunta al carrello, acquisto. Il calo tra un passaggio e l'altro mostra dove perdete utenti.
L'analisi del funnel trasforma le vaghe segnalazioni di «bassa conversione» nell'individuazione di una fase precisa che disperde utenti.
SELECT step, COUNT(DISTINCT user_id) AS users
FROM events
WHERE step IN ('visit', 'signup', 'cart', 'purchase')
GROUP BY step;Contare ogni passaggio
L'aggregazione condizionale conta gli utenti di ogni fase in un'unica elaborazione. FILTER conta gli utenti distinti che hanno raggiunto ciascun passaggio.
Una query, una riga, l'intero funnel a colpo d'occhio.
SELECT
COUNT(DISTINCT user_id) FILTER (WHERE step = 'visit') AS visits,
COUNT(DISTINCT user_id) FILTER (WHERE step = 'signup') AS signups,
COUNT(DISTINCT user_id) FILTER (WHERE step = 'purchase') AS buyers
FROM events;Tassi di conversione per passaggio
Il dato più interessante è il rapporto tra passaggi consecutivi. Dividendo ogni passaggio per quello precedente si individua il calo maggiore.
Convertite il risultato in un decimale per evitare che la divisione tra interi azzeri il tasso.
SELECT signups * 1.0 / NULLIF(visits, 0) AS visit_to_signup,
buyers * 1.0 / NULLIF(signups, 0) AS signup_to_buy
FROM funnel_counts;Funnel ordinati con timestamp
Un funnel rigoroso richiede che i passaggi avvengano nell'ordine corretto. Collegate ogni passaggio a quello successivo solo quando il relativo timestamp è posteriore.
In questo modo evitate di contare un acquisto avvenuto prima della registrazione corrispondente.
SELECT COUNT(DISTINCT v.user_id) AS visited,
COUNT(DISTINCT p.user_id) AS purchased
FROM events v
LEFT JOIN events p
ON p.user_id = v.user_id
AND p.step = 'purchase'
AND p.event_time > v.event_time
WHERE v.step = 'visit';Funnel per canale
Suddividere il funnel per canale di acquisizione mostra quali fonti portano utenti che effettuano davvero una conversione, non soltanto un clic.
Raggruppate i conteggi condizionali per canale per confrontare la qualità delle diverse fonti.
SELECT channel,
COUNT(DISTINCT user_id) FILTER (WHERE step = 'visit') AS visits,
COUNT(DISTINCT user_id) FILTER (WHERE step = 'purchase') AS buyers
FROM events
GROUP BY channel;Dall'insight all'azione
Le coorti indicano se la retention migliora da una release all'altra; i funnel mostrano quale passaggio correggere per primo.
Insieme, spostano il marketing dal semplice resoconto di ciò che è accaduto alla diagnosi del perché e di dove investire in seguito.
Controllo rapido
Il vostro funnel conta gli utenti nelle fasi di visita, registrazione e acquisto. Che cosa rivelano i rapporti tra passaggi consecutivi?
Riepilogo
Le coorti raggruppano gli utenti per mese di inizio e monitorano la retention nei vari periodi usando DATE_TRUNC e AGE. I funnel contano gli utenti distinti per passaggio ordinato e confrontano i rapporti consecutivi.
Ora disponete degli strumenti avanzati per diagnosticare retention e conversione interamente in SQL.
Domande Frequenti
La lezione «Query per coorti e funnel» è gratuita?
Sì — il testo completo di «Query per coorti e funnel» è gratuito qui sul web. Per esercitarvi in modo interattivo (un editor di codice integrato e un tutor IA 24/7) e sbloccare il resto del corso Digital Marketing Academy, passa a CoddyKit PRO. Il corso Digital Marketing Academy include 4 lezioni in totale.
Cosa imparerò in «Query per coorti e funnel»?
Risponda alle domande sulla fidelizzazione Eserciti Digital Marketing Academy con codice pratico che esegui direttamente nel browser, e un tutor IA 24/7 risponde alle tue domande mentre lavori sulla lezione.
Ho bisogno di esperienza per iniziare Digital Marketing Academy?
Non è richiesta alcuna esperienza precedente. Digital Marketing Academy su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 4 di 4.
Quanto tempo richiede la lezione «Query per coorti e funnel»?
La maggior parte delle lezioni CoddyKit richiede circa 5–10 minuti. Ogni lezione è breve e interattiva, quindi fai progressi costanti e riprendi esattamente da dove hai lasciato su web e app.
Posso scrivere ed eseguire codice in questa lezione Digital Marketing Academy?
Sì. Ogni lezione Digital Marketing Academy include un editor di codice integrato, quindi scrivi ed esegui codice reale direttamente nel tuo browser e ricevi feedback istantaneo dall'IA — nessuna configurazione locale necessaria.
Tutte le lezioni di questo corso
- Perché i marketer imparano SQL
- SELECT, WHERE, GROUP BY
- Unire le tabelle di marketing
- Query per coorti e funnel