Il registro eventi dell’abbonamento fornisce i dati di abbonamento a livello di evento per il tuo canale Prime Video. Accedi agli eventi non elaborati dell’abbonamento, tra cui le modifiche allo stato di inizio, rinnovo, annullamento e rinnovo automatico, con identificativi dei clienti resi anonimi per creare analisi personalizzate su misura per le tue esigenze aziendali. Questa guida illustra il modello di dati, le dimensioni, le metriche e le best practice per la creazione di analisi degli abbonamenti.
Perché usare il registro eventi dell’abbonamento?
Il registro eventi dell’abbonamento fornisce un changelog dell’analisi delle sottoscrizioni oltre a quanto disponibile nelle dashboard predefinite:
- Analisi personalizzate: crea metriche su misura per le tue specifiche definizioni aziendali e le tue esigenze di reporting utilizzando dati grezzi a livello di evento
- Integrazione con il data warehouse: integra i dati degli abbonamenti Prime Video con i tuoi sistemi interni e altre fonti di dati per un’analisi completa
- Logica aziendale flessibile: applica le tue regole di regolamento, la logica di deduplicazione e i calcoli delle metriche per soddisfare i tuoi requisiti aziendali
- Monitoraggio del ciclo di vita dei clienti: traccia i percorsi dei singoli clienti attraverso gli eventi di abbonamento con identificatori anonimi stabili
Come funziona
Il registro eventi dell’abbonamento fornisce i dati dell’abbonamento come flusso di eventi di sola aggiunta tramite API. Ogni record rappresenta un evento distinto nel ciclo di vita dell’abbonamento di un cliente. I dati contengono eventi grezzi senza logica aziendale preapplicata, offrendoti la massima flessibilità per calcolare le metriche in base alle tue esigenze.
Scelta del giusto metodo di accesso ai dati
Slate Insights offre due modi per accedere ai dati degli abbonamenti:
Funzionalità |
Dashboard di Slate Analytics |
Registro eventi dell’abbonamento (AOI) |
Ideale per |
Informazioni rapide, reportistica esecutiva |
Analisi personalizzate, ingegneria dei dati |
Formato dati |
Metriche preaggregate |
Dati grezzi a livello di evento |
Logica di liquidazione |
Applicato automaticamente |
Tu applichi la tua logica |
Deduplicazione |
Gestito per te |
La deduplicazione viene eseguita utilizzando last_update_time_utc |
Flessibilità |
Definizioni metriche fisse |
Personalizzazione completa |
Frequenza di aggiornamento |
Circa ogni 8 ore |
Circa ogni 4 ore |
Dati storici |
Fino a 2 anni |
Fino a 2 anni |
Metodo di accesso |
Interfaccia utente Web |
REST API |
Usa la dashboard quando hai bisogno di risposte rapide o visualizzazioni predefinite. Usa l’API Event Log quando devi creare metriche personalizzate, integrarti con il tuo data warehouse o applicare una logica specifica per l’azienda.
Concetti chiave
La comprensione di questi concetti fondamentali ti aiuterà a lavorare in modo efficace con il registro eventi dell’abbonamento.
Concetto |
Descrizione |
Evento |
Un’azione di abbonamento discreta (Inizio, Rinnovo, Annullamento, Rinnovo automatico attivato/disattivato). Ogni evento è identificato da un subscription_event_id univoco. |
Dimensione |
Un attributo che descrive il contesto di un evento, ad esempio la data in cui si è verificato, il cliente coinvolto o il tipo di offerta. Le dimensioni vengono utilizzate per filtrare e raggruppare i dati. |
Metrica |
Una misurazione calcolata derivata da eventi, come il numero di abbonati attivi, il tasso di abbandono o il tasso di conversione. Le metriche non vengono archiviate direttamente: vengono calcolate dai dati degli eventi. |
Modello Changelog |
I dati sono di sola aggiunta. Se gli attributi di un evento cambiano, viene pubblicato un nuovo record con lo stesso subscription_event_id ma con un last_update_time_utc più recente. |
Liquidazione |
Logica aziendale che filtra il rumore dagli eventi grezzi. Ad esempio, escludendo le coppie di inizio/annullamento dello stesso giorno che non rappresentano abbonamenti significativi. |
Ciclo di vita |
Un «ciclo» di abbonamento che rappresenta un periodo continuo di abbonamento. Un nuovo ciclo di vita inizia dopo ogni evento Annullamento risolto. |
Nota: Inizia con Events and Dimensions per interrogare i dati, applica la logica di liquidazione e ciclo di vita per pulirli, quindi calcola le metriche corrispondenti alle definizioni aziendali.
Dimensioni
Le dimensioni sono attributi che descrivono il contesto di ogni evento di sottoscrizione. Vengono utilizzate per filtrare i dati, raggruppare i risultati e creare report.
Dimensione dell’evento
Subscription_event_type è il tipo di evento di sottoscrizione che si è verificato. Questa è la dimensione principale per comprendere le azioni del ciclo di vita del cliente.
Value |
Descrizione |
Caso d’uso |
Inizio |
Il cliente si è iscritto al tuo canale per la prima volta. Al momento della vendita, non esiste alcun record iniziale precedente per questo cliente. |
Conta le acquisizioni di nuovi abbonati |
Rinnovo |
L’abbonamento del cliente è proseguito da qualsiasi periodo precedente nel passato. Gli eventi di rinnovo si verificano anche quando esistono degli intervalli tra i periodi di abbonamento, a condizione che il cliente disponga di un record storico degli abbonamenti. |
Conta gli abbonati fidelizzati |
Annullamento |
L’abbonamento del cliente è terminato. Può essere avviato dal cliente o derivante da un mancato pagamento. |
Calcola tasso di abbandono |
Attivo - AR ON |
Il cliente ha abilitato il rinnovo automatico. L’abbonamento si rinnoverà alla fine del periodo. |
Tieni traccia della frequenza di rinnovo automatico |
Attivo - AR OFF |
Il cliente ha disabilitato il rinnovo automatico. L’abbonamento scadrà alla fine del periodo a meno che non venga riattivato. |
Identifica gli abbonati a rischio |
Sospeso |
L’abbonamento del cliente è temporaneamente sospeso a causa di problemi di pagamento (fondi insufficienti o informazioni di fatturazione non valide). L’abbonamento riprenderà automaticamente e genererà un evento di rinnovo una volta che il cliente avrà aggiornato i dati di fatturazione e il pagamento sarà stato elaborato correttamente. |
Aiuta a tenere traccia degli abbonamenti in uno stato di errore temporaneo che potrebbe essere recuperato (diventando Rinnovi) o convertito in Annullamenti |
Nota: Un evento Inizio si verifica solo quando, al momento della vendita, non abbiamo precedenti record Inizio per quel cliente sul tuo canale. Per un conteggio affidabile degli abbonati attivi, considera gli eventi Start e Renewal come indicatori di un abbonamento attivo. Questo approccio gestisce tutti i casi limite in modo coerente.
Dimensioni dell’offerta
Queste dimensioni descrivono l’offerta di abbonamento associata a ciascun evento.
Dimensione |
Tipo |
Formato |
Descrizione |
is_promo |
intero |
0 o 1 |
Indica se l’evento di abbonamento si è verificato durante il periodo dell’offerta promozionale. 0 = Offerta standard (nessuna promozione attiva), 1 = Offerta promozionale (sconto o prezzo speciale attivo). |
offer_type |
string |
Testo |
Rappresenta la classificazione dell’offerta per Amazon. Sarà sempre ‘3P_SUBS’ per i canali. |
offer_marketplace |
string |
Codice Marketplace |
Il marketplace Amazon in cui è disponibile l’offerta di abbonamento. Valori US (Stati Uniti), UK (Regno Unito), DE (Germania), JP (Giappone), ROE (Resto d’Europa), ROW_NA (Resto del mondo - Nord America), ROW_EU (Resto del mondo - Europa), ROW_FE (Resto del mondo - Estremo Oriente). L’offer_marketplace riflette il luogo in cui è stata acquistata l’offerta, che può differire dalla posizione attuale del cliente. |
offer_name |
string |
Testo |
Il nome leggibile dall’utente dell’offerta di abbonamento configurato nella configurazione del canale. |
offer_payment_amount |
numerico |
Valore numerico (ad es. «4,99", «9,99") |
Il prezzo di listino dell’offerta di abbonamento nella valuta locale. Questo campo contiene solo i metadati dell’offerta e non rappresenta le entrate transazionali. Questo campo mostra il prezzo dell’offerta associata all’evento e non rappresenta le entrate effettive guadagnate o il denaro ricevuto. Lo stesso valore appare su ogni tipo di evento (Inizio, Rinnovo, Annullamento, Modifiche AR) indipendentemente dal fatto che il pagamento sia avvenuto o meno. Non è possibile sommare questo campo per calcolare le entrate. Per i dati sulle entrate effettive, fai riferimento ai tuoi sistemi di rendicontazione finanziaria separati. |
offer_billing_type |
string |
FT o HO |
Il modello di pagamento per l’offerta di abbonamento. FT = Prova gratuita (periodo introduttivo gratuito), HO = Hard Offer (abbonamento a pagamento). |
offer_id |
string |
Identifier |
L’identificatore univoco per l’offerta di abbonamento. Usalo per iscriverti con i metadati dell’offerta o monitorare il rendimento per offerta. |
Nota: L’offer_marketplace indica dove è stato acquistato l’abbonamento e determina prezzi, valuta e disponibilità dei contenuti. Un singolo cliente può avere abbonamenti su più marketplace. Usa offer_billing_type per distinguere tra eventi di prova gratuita e sottoscrizione a pagamento nel calcolo delle metriche di conversione.
Dimensioni del cliente
Queste dimensioni identificano e descrivono l’abbonato.
Dimensione |
Tipo |
Formato |
Descrizione |
cid |
string |
Identificatore anonimo |
Un identificativo del cliente anonimo e stabile. Questo ID è coerente per tutti gli eventi di un determinato cliente all’interno del tuo canale, consentendoti di monitorare i cicli di vita completi dei clienti. Il cid è coerente all’interno del tuo canale principale. Lo stesso cliente avrà lo stesso codice per tutte le offerte e i pacchetti che si aggiungono allo stesso canale principale all’interno di un marketplace (ad esempio, un cliente che si abbona a «Prime Ad-Supported (US)» e successivamente passa a «Prime Ad-free (US)» manterrà lo stesso cid perché entrambe le offerte condividono il canale principale «Prime (USA)»). I clienti avranno valori cid diversi tra i diversi canali dei partner, garantendo la privacy e abilitando l’analisi per canale. |
benefit_id |
string |
Identifier |
L’ID del vantaggio Prime Video associato all’abbonamento. Questo collega l’abbonamento a diritti di accesso specifici ai contenuti. |
channel_label |
string |
Testo |
Il nome del canale a cui appartiene l’abbonamento. |
channel_tier_label |
string |
Testo |
Il nome del livello specifico all’interno del canale, se applicabile. Se il tuo canale non utilizza i livelli, questo valore può essere nullo o corrispondere a channel_label. |
Nota: Il cid consente il monitoraggio del ciclo di vita dei clienti mantenendo la privacy. Usa cid per analizzare i modelli di comportamento dei singoli clienti, calcolare le metriche di fidelizzazione e identificare gli eventi di reiscrizione.
Dimensioni temporali
Queste dimensioni forniscono un contesto temporale per gli eventi di abbonamento.
Dimensione |
Tipo |
Formato |
Descrizione |
subscription_event_time_utc |
timestamp |
ISO 8601 YYYY-MM-DDTHH:MM:SS.sssZ |
Il timestamp in cui si è verificato l’evento di sottoscrizione, memorizzato in UTC. Tutti i timestamp degli eventi vengono memorizzati in UTC per coerenza. Usa subscription_event_time_zone per la conversione in ora locale per i report. |
subscription_event_time_zone |
string |
Identificatore del fuso orario (ad esempio, America/New_York, Europa/Londra) |
Il fuso orario del marketplace in cui si è verificato l’evento di abbonamento. La dashboard di Slate Analytics presenta i dati nel fuso orario locale. |
create_time_utc |
timestamp |
Formato ISO 8601 |
Il timestamp in cui il record dell’evento di sottoscrizione è stato creato per la prima volta nel changelog, archiviato in UTC. Indica quando il record è stato aggiunto al changelog, non quando si è verificato l’evento. Usa subscription_event_time_utc per l’ora effettiva dell’evento. |
last_update_time_utc |
timestamp |
Formato ISO 8601 |
Il timestamp dell’ultimo aggiornamento del record dell’evento di abbonamento nel changelog, memorizzato in UTC. Usa sempre questo campo per deduplicare i record. Quando esistono più record per lo stesso subscription_event_id, conserva solo il record con l’ultimo last_update_time_utc. |
Esempio di conversione (Athena SQL):
-- Parse ISO 8601 string and convert to local timezone
CAST(DATE_FORMAT(
AT_TIMEZONE(
parse_datetime(subscription_event_time_utc, 'yyyy-MM-dd''T''HH:mm:ss.SSS''Z'''),
subscription_event_time_zone
),
'%Y-%m-%d %H:%i:%s.%f'
) AS TIMESTAMP) AS event_time_local
Nota: Usa subscription_event_time_utc per tutti i calcoli e i filtri. Converti in fuso orario locale utilizzando subscription_event_time_zone solo per scopi di reporting e visualizzazione.
Dimensioni della gestione dei record
Queste dimensioni consentono di gestire correttamente i dati del changelog.
Dimensione |
Tipo |
Formato |
Descrizione |
subscription_event_id |
string |
Formato UUID |
L’identificatore univoco per ogni evento di abbonamento. Questa è la chiave principale per la deduplicazione. Possono esistere più record per lo stesso subscription_event_id se gli attributi dell’evento sono stati aggiornati. Deduplica sempre conservando il record con l’ultimo last_update_time_utc. |
is_deleted |
intero |
0 o 1 |
Indica se un record pubblicato in precedenza deve essere rimosso dal set di dati. 0 = record attivo (includi nell’analisi), 1 = record eliminato (escludi dall’analisi: rimuovi dalle tabelle se precedentemente inserito). I record eliminati sono rari ma possono verificarsi quando sono necessarie correzioni dei dati. Filtra sempre is_deleted = 0 nelle tue query. |
Nota: Deduplica sempre utilizzando last_update_time_utc prima di eseguire qualsiasi analisi. Filtra i record eliminati (is_deleted = 1) all’inizio della pipeline ETL per garantire l’accuratezza dei dati.
Pulizia dei dati e logica di liquidazione
Questa sezione spiega il processo di deduplicazione in due fasi utilizzato per trasformare i dati grezzi degli eventi in metriche pulite e significative per l’azienda. Alcuni partner utilizzano una logica di liquidazione diversa, quindi forniamo dati grezzi sugli eventi per consentire la flessibilità. Questa sezione fornisce la logica che utilizziamo in Prime Video in modo che i partner possano riconciliare le metriche con la dashboard di Slate Analytics e vedere come modelliamo i dati.
Perché la liquidazione è importante?
I dati grezzi relativi agli eventi di sottoscrizione contengono rumore che può aumentare o distorcere le metriche:
Problema |
Esempio |
Impatto senza liquidazione |
Abbandono immediato lo stesso giorno |
Il cliente inizia e annulla entro poche ore |
Viene conteggiato due volte sia come inizio che come annullamento |
Tentativi di pagamento |
Eventi di annullamento multipli dovuti a errori di fatturazione |
Numero di abbandoni gonfiato |
Annullamento senza inizio |
Annullamento degli eventi senza un corrispondente inizio nel ciclo di vita |
Abbandono gonfiato senza acquisizione corrispondente |
La logica di liquidazione garantisce che le metriche riflettano un’attività di abbonamento significativa, non il rumore generato dal sistema.
Fase 1: Liquidazione di fine giornata
Gestisci le coppie Start + Cancel nello stesso giorno mantenendo solo lo stato End-of-Day (EOD). Se un cliente ha sia un evento Inizio/Rinnovo che un evento Annullamento nello stesso giorno di calendario, conserva solo l’evento cronologicamente ultimo per quel giorno. Gli annullamenti in giornata spesso indicano errori nell’autorizzazione di pagamento o problemi nel flusso dell’abbonamento. Questi non rappresentano un coinvolgimento significativo degli abbonati e il conteggio aumenta gli inizi e gli annullamenti
Attuazione
-- Step 1: Identify same-day start + cancel pairs
same_day_start_and_cancel AS (
SELECT cid, offer_marketplace, event_date_local
FROM deduped_events
GROUP BY cid, offer_marketplace, event_date_local
HAVING SUM(CASE WHEN subscription_event_type IN ('Start', 'Renewal') THEN 1 ELSE 0 END) > 0
AND SUM(CASE WHEN subscription_event_type = 'Cancel' THEN 1 ELSE 0 END) > 0
),
-- Step 2: Rank events by time within each day
events_with_eod_rank AS (
SELECT e.*,
ROW_NUMBER() OVER (
PARTITION BY e.cid, e.offer_marketplace, e.event_date_local
ORDER BY e.event_time_local DESC
) AS eod_rank
FROM deduped_events e
),
-- Step 3: For same-day pairs, keep only EOD event (rank = 1)
settled_events AS (
SELECT e.*
FROM events_with_eod_rank e
LEFT JOIN same_day_start_and_cancel s
ON e.cid = s.cid
AND e.offer_marketplace = s.offer_marketplace
AND e.event_date_local = s.event_date_local
WHERE s.cid IS NULL -- Not a same-day pair, keep all events
OR e.eod_rank = 1 -- Same-day pair, keep only last event
)
Esempio
Questa tabella dimostra come la logica di liquidazione EOD gestisce le coppie di eventi Start e Cancel nello stesso giorno il 2025/10/15 mantenendo solo lo stato finale alla fine della giornata.
Ora |
Evento |
Senza EOD Settlement |
Con EOD Settlement |
2025/08/01 00:00 |
Inizio |
Conteggiato come inizio |
Conteggiato come inizio |
2025/09/01 00:00 |
Rinnovo |
- |
- |
2025/10/01 00:00 |
Rinnovo |
- |
- |
2025/10/15 07:00 |
Annullamento |
Conteggiato come Annullamento |
Escluso |
2025/10/15 09:00 |
Inizio |
Conteggiato come inizio |
Escluso |
2025/10/15 09:15 |
Annullamento |
Conteggiato come Annullamento |
Conteggiato come Annullamento |
Nota: Il cliente viene conteggiato solo come Annullamento (o non viene conteggiato affatto, a seconda del contesto del ciclo di vita), e non sia come Inizio che come Annullamento. La liquidazione EOD si applica solo alle metriche stabilite (inizi, annullamenti). Le metriche Snapshot utilizzano intervalli di abbonamento creati a partire da eventi deduplicati dal changelog senza liquidazione EOD per preservare l’intera cronologia degli eventi necessaria per i calcoli e le conversioni point-in-time.
Fase 2: Deduplicazione del ciclo di vita
Raggruppa gli eventi in «cicli» di abbonamento e conserva solo il primo Inizio e il primo Annullamento per ciclo di vita. Un Annullamento valido richiede un Inizio corrispondente nello stesso ciclo di vita.
Attuazione -- Step 1: Identify lifecycle boundaries
-- A new lifecycle starts when there's no previous event OR previous event was Cancel
events_with_lifecycle_boundaries AS (
SELECT e.*,
LAG(subscription_event_type) OVER (
PARTITION BY cid, offer_marketplace
ORDER BY event_time_local
) AS prev_event_type
FROM settled_events e
),
-- Step 2: Flag lifecycle starts
events_with_lifecycle_start_flag AS (
SELECT e.*,
CASE
WHEN prev_event_type IS NULL THEN 1 -- First event ever
WHEN prev_event_type = 'Cancel' THEN 1 -- After a cancel
ELSE 0
END AS is_lifecycle_start
FROM events_with_lifecycle_boundaries e
),
-- Step 3: Assign lifecycle numbers using cumulative sum
events_with_lifecycle_id AS (
SELECT e.*,
SUM(is_lifecycle_start) OVER (
PARTITION BY cid, offer_marketplace
ORDER BY event_time_local
ROWS UNBOUNDED PRECEDING
) AS lifecycle_num
FROM events_with_lifecycle_start_flag e
),
-- Step 4: Keep only first Start and first Cancel per lifecycle
events_with_dedup_rank AS (
SELECT e.*,
ROW_NUMBER() OVER (
PARTITION BY cid, offer_marketplace, lifecycle_num,
CASE WHEN subscription_event_type IN ('Start', 'Renewal') THEN 'Start' END
ORDER BY event_time_local
) AS start_rank,
ROW_NUMBER() OVER (
PARTITION BY cid, offer_marketplace, lifecycle_num,
CASE WHEN subscription_event_type = 'Cancel' THEN 'Cancel' END
ORDER BY event_time_local
) AS cancel_rank
FROM events_with_lifecycle_id e
),
deduped_lifecycle_events AS (
SELECT *
FROM events_with_dedup_rank
WHERE (subscription_event_type IN ('Start', 'Renewal') AND start_rank = 1)
OR (subscription_event_type = 'Cancel' AND cancel_rank = 1 AND lifecycle_has_start = 1)
)
La condizione lifecycle_has_start = 1 garantisce che gli annullamenti vengano conteggiati solo quando il ciclo di vita ha un evento di inizio corrispondente.
Esempio
Cliente |
Evento |
Ora |
Ciclo di vita |
Mantenuto? |
ABC |
Inizio |
1/1/2025 |
1 |
✓ Primo Inizio del ciclo di vita 1 |
ABC |
Rinnovo |
2/1/2025 |
1 |
✗ Non è il primo Inizio del ciclo di vita 1 |
ABC |
Annullamento |
3/1/2025 |
1 |
✓ Primo Annullamento nel ciclo di vita 1 |
ABC |
Inizio |
4/15/2025 |
2 |
✓ Primo Inizio del ciclo di vita 2 |
ABC |
Annullamento |
5/15/2025 |
2 |
✓ Primo Annullamento nel ciclo di vita 2 |
Importante: Come la liquidazione EOD, la deduplicazione del ciclo di vita viene applicata solo alle metriche stabilite. Le metriche Snapshot utilizzano gli intervalli di abbonamento non elaborati in cui ogni Inizio/Rinnovo crea un intervallo che termina all’evento successivo, preservando la sequenza temporale completa necessaria per i calcoli point-in-time.
Metriche
Le metriche sono misurazioni calcolate derivate dagli eventi di sottoscrizione. A differenza delle dimensioni, le metriche non vengono archiviate direttamente nel registro eventi ma vengono calcolate aggregando e trasformando i dati degli eventi. Nota: le formule di calcolo fornite sono raccomandazioni; puoi modificarle in modo che corrispondano alle tue definizioni aziendali.
Metriche principali
Le seguenti metriche sono fondamentali per l’analisi delle sottoscrizioni:
Metrica |
Descrizione |
Abbonati di fine periodo |
Clienti con un abbonamento in corso alla fine del periodo |
Abbonati attivi |
Clienti con un abbonamento attivo in qualsiasi momento durante il periodo |
Acquisizione abbonati (Inizio) |
Nuovi abbonati acquisiti |
Annullamenti |
Abbonati che hanno annullato |
Tasso di conversione della versione di prova gratuita |
Da versione di prova gratuita a versione a pagamento |
Inizi a pagamento netto |
Nuovi abbonati paganti che derivano da conversioni |
Metriche relative agli abbonati
Queste metriche tengono traccia delle acquisizioni e delle cancellazioni di nuovi abbonati dopo aver applicato la deduplicazione della liquidazione e del ciclo di vita.
Metrica |
Definizione |
Calcolo |
num_starts |
Conteggio degli eventi di inizio e rinnovo che segnano l’inizio di un nuovo ciclo di vita dell’abbonamento entro il periodo di riferimento. |
Dopo aver applicato la liquidazione e la deduplicazione del ciclo di vita, conta gli eventi in cui subscription_event_type IN (‘Start’, ‘Renewal’) ed eventi rientrano nel periodo. SUM(CASE WHEN subscription_event_type IN (‘Start', ‘Renewal') AND event_time_local >= period_start AND event_time_local < period_end THEN 1 ELSE 0 END) AS num_starts |
num_starts_ft |
Numero di inizi in cui il tipo di fatturazione dell’offerta è Free Trial (FT). |
SUM(CASE WHEN subscription_event_type IN (‘Start', ‘Renewal') AND offer_billing_type = ‘FT’ AND event_time_local >= period_start AND event_time_local < period_end THEN 1 ELSE 0 END) AS num_starts_ft |
num_starts_ho |
Numero di inizi in cui il tipo di fatturazione dell’offerta è Hard Offer (HO): abbonamenti a pagamento. |
SUM(CASE WHEN subscription_event_type IN (‘Start', ‘Renewal') AND offer_billing_type = ‘HO’ AND event_time_local >= period_start AND event_time_local < period_end THEN 1 ELSE 0 END) AS num_starts_ho |
num_net_starts_ho |
Al netto dei nuovi abbonati paganti, calcolati come inizi HO diretti più conversioni di periodi di prova gratuiti. Fornisce un quadro completo dei nuovi abbonati paganti, indipendentemente dal fatto che si siano iscritti direttamente a un piano a pagamento o siano all’abbonamento da una versione di prova gratuita. |
num_net_starts_ho = num_starts_ho + num_ft_conversions |
num_cancels |
Conteggio degli eventi di annullamento entro il periodo di riferimento dopo l’applicazione della liquidazione e della deduplicazione del ciclo di vita. |
SUM(CASE WHEN subscription_event_type = ‘Cancel’ AND event_time_local >= period_start AND event_time_local < period_end THEN 1 ELSE 0 END) AS num_cancels |
num_cancels_ho |
Annullamenti in cui l’abbonato aveva sottoscritto un piano Hard Offer (a pagamento). |
- |
num_cancels_ft |
Annullamenti in cui l’abbonato aveva un piano di prova gratuito (periodo di prova terminato senza conversione). |
- |
Metriche relative al periodo
Queste metriche forniscono istantanee temporali e attività periodiche utilizzando intervalli di abbonamento creati senza regolamento EOD.
Metrica |
Definizione |
Calcolo |
Varianti |
num_end_period_subs |
Numero di abbonati unici attivi alla fine del periodo di riferimento. Questa è un’istantanea puntuale. |
Da subscription_spans (creati senza regolamento EOD) CONTEGGIO (CASO DISTINTO) WHEN span_start_date < period_end AND (span_end_date IS NULL OR span_end_date >= period_end) THEN cid END) AS num_end_period_subs |
num_end_period_subs_ho — Abbonati di fine periodo ai piani Hard Offer, num_end_period_subs_ft — Abbonati di fine periodo ai piani di prova gratuiti |
num_active_period_subs |
Numero di abbonati unici attivi in qualsiasi momento durante il periodo di riferimento. Cattura tutti i clienti con un periodo di abbonamento attivo che coincide con il periodo, indipendentemente dalla data di inizio o annullamento. |
Da subscription_spans (creati senza regolamento EOD) CONTEGGIO (CASO DISTINTO) WHEN span_start_date < period_end AND (span_end_date IS NULL OR span_end_date >= period_start) THEN cid END) AS num_active_period_subs |
num_active_period_subs_ho — Abbonati attivi in qualsiasi momento durante il periodo sui piani Hard Offer (a pagamento) - num_active_period_subs_ft — Abbonati attivi in qualsiasi momento durante il periodo sui piani di prova gratuiti |
Nota: Le metriche periodiche utilizzano subscription_spans compilate senza regolamento EOD per preservare l’intera cronologia degli eventi.
Metriche di prova gratuite
Queste metriche tengono traccia delle conversioni e dei pagamenti delle versioni di prova gratuite utilizzando intervalli di abbonamento creati senza regolamento EOD.
Metrica |
Definizione |
Calcolo |
num_ft_conversions |
Numero di abbonati alla versione di prova gratuita che sono passati a un piano a pagamento (Hard Offer) entro il periodo di riferimento. Una conversione si verifica quando un abbonato a una versione di prova gratuita (offer_billing_type = ‘FT’) ha come evento successivo un Inizio/Rinnovo con offer_billing_type = ‘HO’. |
Da subscription_spans (creati senza regolamento EOD) COUNT(CASE WHEN offer_billing_type = ‘FT’ AND span_end_billing_type = ‘HO’ AND span_end_event_type IN (‘Start', ‘Renewal') AND span_end_date >= period_start AND span_end_date < period_end THEN 1 END) AS num_ft_conversions |
num_ft_settled |
Conteggio degli abbonamenti di prova gratuiti liquidati (conclusi) entro il periodo di riferimento. Una versione di prova si risolve quando l’abbonato passa a un piano a pagamento o annulla (il tipo di fatturazione successivo non è FT). |
COUNT(CASE WHEN offer_billing_type = ‘FT’ AND span_end_date IS NOT NULL AND span_end_date >= period_start AND span_end_date < period_end THEN 1 END) AS num_ft_settled |
ft_conversion_rate_pct |
Percentuale di prove gratuite concluse convertite in abbonamenti a pagamento. |
CASE WHEN num_ft_settled > 0 THEN ROUND(100.0 * num_ft_conversions / num_ft_settled, 2) ELSE 0 END AS ft_conversion_rate_pct |
Nota: Le metriche di prova gratuite utilizzano subscription_spans creati senza regolamento EOD per tracciare con precisione le conversioni quando i clienti passano da un offer_id all’altro.
Comprensione dei modelli di eventi
Questa sezione spiega i modelli di eventi più comuni che incontrerai nel registro eventi dell’abbonamento e come interpretarli correttamente.
Ciclo di vita dell’abbonamento
Un abbonamento tipico segue questo ciclo di vita, procedendo attraverso quattro fasi chiave dall’iscrizione iniziale ai cicli di rinnovo e alla potenziale cancellazione. Ogni fase genera tipi di eventi specifici che consentono di monitorare il comportamento dei clienti e calcolare le metriche degli abbonamenti.
Punti chiave del ciclo di vita
Questi punti chiave del ciclo di vita rappresentano le quattro fasi critiche del percorso di abbonamento di un cliente, ognuna delle quali genera tipi di eventi distinti che consentono il monitoraggio dei modelli di acquisizione, fidelizzazione, coinvolgimento e abbandono.
Fase |
Tipo di evento |
Descrizione |
Acquisizione |
Inizio |
Un nuovo cliente si unisce al tuo canale |
Fidelizzazione |
Rinnovo |
Il cliente esistente continua l’abbonamento |
Coinvolgimento |
Attivo - AR ON/OFF |
Il cliente modifica la preferenza per il rinnovo automatico |
Abbandono |
Annullamento |
L’abbonamento del cliente termina |
Schemi di eventi comuni
Schema 1: Rinnovo senza inizio
La cronologia degli eventi di un cliente mostra gli eventi di rinnovo ma non l’inizio. L’evento Inizio originale del cliente si è verificato più di 2 anni fa. A causa della politica di conservazione dei dati, gli eventi più vecchi di 2 anni non sono disponibili. Gli eventi di Rinnovo all’interno della finestra sono ancora accessibili.
Schema 2: Eventi di annullamento multipli
Un cliente ha diversi eventi di annullamento nel giro di pochi minuti o ore. Questo schema indica in genere errori nei tentativi di pagamento: Il sistema di fatturazione tenta di addebitare il metodo di pagamento del cliente. Ogni tentativo fallito può generare un evento di Annullamento. Il cliente potrebbe tentare di abbonarsi nuovamente con una carta rifiutata. La deduplicazione del ciclo di vita manterrà solo il primo Annullamento per ogni ciclo di vita.
Schema 3: Inizio e annullamento in giornata
Un cliente presenta sia l’Inizio che l’Annullamento nello stesso giorno di calendario. Le cause più comuni includono:
- Il cliente si è registrato e ha immediatamente annullato
- Autorizzazione al pagamento non riuscita dopo la registrazione iniziale
- Tentativo di registrazione fraudolento bloccato
EOD Settlement lo gestisce automaticamente, viene mantenuto solo lo stato finale.
Schema 4: Versione di prova gratuita per la conversione a un piano a pagamento
Un cliente ha un Inizio con offer_billing_type = ‘FT’, seguito da un altro Inizio o Rinnovo con offer_billing_type = ‘HO’. Il cliente ha completato la prova gratuita ed è passato a un abbonamento a pagamento. Usa le funzioni della finestra LEAD () per rilevare quando offer_billing_type cambia da FT a HO: conta come conversione quando l’evento di transizione rientra nel periodo di riferimento.
Eventi con rinnovo automatico
Gli eventi di rinnovo automatico (AR) rilevano quando i clienti abilitano o disattivano il rinnovo automatico dell’abbonamento. Questi eventi forniscono segnali precoci per prevedere il tasso di abbandono futuro e identificare gli abbonati a rischio.
Tipo di evento |
Descrizione |
Caso d’uso |
AR_ON |
Il cliente ha abilitato il rinnovo automatico. L’abbonamento si rinnoverà automaticamente alla fine del periodo corrente. |
Tieni traccia dei clienti che si sono impegnati a continuare il loro abbonamento. |
AR_OFF |
Il cliente ha disabilitato il rinnovo automatico. L’abbonamento scadrà alla fine del periodo corrente a meno che non venga riattivato il rinnovo automatico. |
Segnale di allarme precoce: I clienti che disattivano il rinnovo automatico segnalano l’intenzione di andarsene prima della scadenza effettiva dell’abbonamento. Previsione del tasso di abbandono: Gli abbonati AR OFF abbandoneranno definitivamente alla fine del periodo a meno che non riattivino il rinnovo automatico. Ciò rende gli eventi AR OFF uno dei predittori più affidabili del tasso di abbandono futuro. |
Nota: Gli eventi di rinnovo automatico vengono registrati ogni volta che un cliente abilita attivamente la propria preferenza di rinnovo automatico tramite l’interfaccia di gestione degli abbonamenti.
Analisi degli eventi AR
Per analizzare il comportamento del rinnovo automatico, interroga gli eventi AR ON/OFF separatamente dalla pipeline principale delle metriche del ciclo di vita. Gli eventi AR seguono le stesse regole di deduplicazione del changelog ma non sono inclusi nei calcoli del ciclo di vita Start/Renewal/Cancel.
-- Example:Filter to AR Off Events
SELECT *
FROM deduped_events
WHERE subscription_event_type = 'AR_OFF'
GROUP BY 1, 2
ORDER BY 1, 2
Pipeline ETL
Basandosi sulle definizioni delle metriche e sulle trasformazioni dei dati di cui sopra, utilizza questa pipeline ETL (Extract, Transform, Load) per trasformare i dati grezzi del registro eventi dell’abbonamento in metriche pulite e significative per l’azienda. Questa query può essere eseguita direttamente in Amazon Athena.
La pipeline implementa un’architettura a due percorsi per calcolare correttamente diversi tipi di metriche:
- Percorso A: Eventi risolti: applica la liquidazione EOD E la deduplicazione del ciclo di vita. Utilizzato per le metriche stabilite (inizi, annullamenti). Questo filtra i rumori come le coppie inizio+annullamento nello stesso giorno che non rappresentano vere acquisizioni.
- Percorso B: Intervalli di abbonamento: applica SOLO la deduplicazione del changelog. Utilizzato per le metriche delle istantanee (conteggi di fine periodo, conversioni FT).
-- ============================================================================
-- SUBSCRIPTION METRICS ETL PIPELINE
-- ============================================================================
-- Transforms raw subscription events into clean metrics using TWO PATHS:
-- PATH A: Settled Events - for settled metrics (starts, cancels)
-- Applies: Changelog dedup → EOD settlement → Lifecycle dedup
-- Filters out noise like same-day start+cancel pairs
-- PATH B: Subscription Spans - for snapshot metrics (end-of-period, conversions)
-- Applies: Changelog dedup ONLY (preserves full timeline)
-- Required for conversions because customer switches offer_id (A → B)
-- ============================================================================
WITH
-- =============================================================================
-- STEP 1: Parse timestamps and convert to local timezone
-- =============================================================================
parsed_events AS (
SELECT
subscription_event_id,
subscription_event_type,
subscription_event_time_utc,
subscription_event_time_zone,
cid,
offer_id,
offer_name,
offer_marketplace,
offer_billing_type,
offer_payment_amount,
is_promo,
channel_label,
channel_tier_label,
benefit_id,
is_deleted,
last_update_time_utc,
-- Parse and convert timestamps
parse_datetime(subscription_event_time_utc, 'yyyy-MM-dd''T''HH:mm:ss.SSS''Z''') AS event_time_utc,
CAST(DATE_FORMAT(
AT_TIMEZONE(
parse_datetime(subscription_event_time_utc, 'yyyy-MM-dd''T''HH:mm:ss.SSS''Z'''),
subscription_event_time_zone
),
'%Y-%m-%d %H:%i:%s.%f'
) AS TIMESTAMP) AS event_time_local,
CAST(DATE_FORMAT(
AT_TIMEZONE(
parse_datetime(subscription_event_time_utc, 'yyyy-MM-dd''T''HH:mm:ss.SSS''Z'''),
subscription_event_time_zone
),
'%Y-%m-%d'
) AS DATE) AS event_date_local
FROM your_subscription_events_table
WHERE is_deleted = 0
AND subscription_event_type IN ('Start', 'Renewal', 'Cancel')
),
-- =============================================================================
-- STEP 2: Deduplicate changelog records (used by BOTH paths)
-- =============================================================================
deduped_events AS (
SELECT * FROM (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY subscription_event_id
ORDER BY last_update_time_utc DESC
) AS dedup_rank
FROM parsed_events
)
WHERE dedup_rank = 1
),
-- =============================================================================
-- PATH A: SETTLED EVENTS (for starts, cancels)
-- Applies: Changelog dedup → EOD settlement → Lifecycle dedup
-- =============================================================================
-- STEP A1: EOD Settlement - Identify same-day Start+Cancel pairs
same_day_start_and_cancel AS (
SELECT cid, offer_marketplace, event_date_local
FROM deduped_events
GROUP BY cid, offer_marketplace, event_date_local
HAVING
SUM(CASE WHEN subscription_event_type IN ('Start', 'Renewal') THEN 1 ELSE 0 END) > 0
AND SUM(CASE WHEN subscription_event_type = 'Cancel' THEN 1 ELSE 0 END) > 0
),
-- Rank events by time within each day
events_with_eod_rank AS (
SELECT
e.*,
ROW_NUMBER() OVER (
PARTITION BY e.cid, e.offer_marketplace, e.event_date_local
ORDER BY e.event_time_local DESC
) AS eod_rank
FROM deduped_events e
),
-- Apply EOD settlement: for same-day pairs, keep only the last event
eod_settled_events AS (
SELECT e.*
FROM events_with_eod_rank e
LEFT JOIN same_day_start_and_cancel sd
ON e.cid = sd.cid
AND e.offer_marketplace = sd.offer_marketplace
AND e.event_date_local = sd.event_date_local
WHERE sd.cid IS NULL OR e.eod_rank = 1
),
-- STEP A2: Build customer lifecycles (on EOD settled data)
settled_events_with_prev AS (
SELECT
*,
LAG(subscription_event_type) OVER (
PARTITION BY cid, offer_marketplace
ORDER BY event_time_local
) AS prev_event_type
FROM eod_settled_events
),
settled_events_with_lifecycle_flag AS (
SELECT
*,
CASE
WHEN prev_event_type IS NULL THEN 1
WHEN prev_event_type = 'Cancel' THEN 1
ELSE 0
END AS is_lifecycle_start
FROM settled_events_with_prev
),
settled_events_with_lifecycle AS (
SELECT
*,
SUM(is_lifecycle_start) OVER (
PARTITION BY cid, offer_marketplace
ORDER BY event_time_local
ROWS UNBOUNDED PRECEDING
) AS lifecycle_num,
cid || '|' || offer_marketplace || '|' || CAST(
SUM(is_lifecycle_start) OVER (
PARTITION BY cid, offer_marketplace
ORDER BY event_time_local
ROWS UNBOUNDED PRECEDING
) AS VARCHAR
) AS lifecycle_id
FROM settled_events_with_lifecycle_flag
),
-- STEP A3: Lifecycle deduplication
events_with_type_rank AS (
SELECT
*,
CASE WHEN subscription_event_type IN ('Start', 'Renewal') THEN
ROW_NUMBER() OVER (
PARTITION BY lifecycle_id,
CASE WHEN subscription_event_type IN ('Start', 'Renewal') THEN 'START' END
ORDER BY event_time_local
)
END AS start_rank,
CASE WHEN subscription_event_type = 'Cancel' THEN
ROW_NUMBER() OVER (
PARTITION BY lifecycle_id,
CASE WHEN subscription_event_type = 'Cancel' THEN 'CANCEL' END
ORDER BY event_time_local
)
END AS cancel_rank,
MAX(CASE WHEN subscription_event_type IN ('Start', 'Renewal') THEN 1 ELSE 0 END) OVER (
PARTITION BY lifecycle_id
) AS lifecycle_has_start
FROM settled_events_with_lifecycle
),
settled_events AS (
SELECT *
FROM events_with_type_rank
WHERE
(subscription_event_type IN ('Start', 'Renewal') AND start_rank = 1)
OR (subscription_event_type = 'Cancel' AND cancel_rank = 1 AND lifecycle_has_start = 1)
),
-- =============================================================================
-- PATH B: SUBSCRIPTION SPANS (for conversions, end-of-period)
-- Applies: Changelog dedup ONLY (NO EOD settlement, NO lifecycle dedup)
-- This preserves the full timeline needed for accurate span calculations
-- =============================================================================
-- STEP B1: Calculate LEAD() on ALL deduped events (before any filtering)
events_with_span_boundaries AS (
SELECT
cid,
offer_marketplace,
offer_id,
offer_billing_type,
subscription_event_type,
event_time_local,
event_date_local,
LEAD(event_time_local) OVER (
PARTITION BY cid, offer_marketplace
ORDER BY event_time_local
) AS next_event_time,
LEAD(subscription_event_type) OVER (
PARTITION BY cid, offer_marketplace
ORDER BY event_time_local
) AS next_event_type,
LEAD(offer_billing_type) OVER (
PARTITION BY cid, offer_marketplace
ORDER BY event_time_local
) AS next_billing_type
FROM deduped_events -- Uses deduped_events directly, NOT eod_settled_events
),
-- STEP B2: Create spans from Start/Renewal events
subscription_spans AS (
SELECT
cid,
offer_marketplace,
offer_id,
offer_billing_type,
subscription_event_type,
event_time_local AS span_start,
event_date_local AS span_start_date,
next_event_time AS span_end,
CAST(next_event_time AS DATE) AS span_end_date,
next_event_type AS span_end_event_type,
next_billing_type AS span_end_billing_type
FROM events_with_span_boundaries
WHERE subscription_event_type IN ('Start', 'Renewal')
),
-- =============================================================================
-- STEP 3: Generate reporting periods
-- =============================================================================
reporting_periods AS (
SELECT
period_month,
CAST(period_month AS DATE) AS period_start,
CAST(DATE_ADD('month', 1, period_month) AS DATE) AS period_end
FROM UNNEST(SEQUENCE(DATE '2025-01-01', DATE '2025-12-01', INTERVAL '1' MONTH)) AS t(period_month)
),
-- =============================================================================
-- STEP 4A: SETTLED METRICS (from settled events - with EOD + lifecycle dedup)
-- Use for: starts, cancels
-- =============================================================================
settled_metrics AS (
SELECT
DATE_FORMAT(p.period_start, '%Y-%m') AS report_month,
e.offer_marketplace,
-- Starts (all)
SUM(CASE WHEN e.subscription_event_type IN ('Start', 'Renewal')
AND e.event_date_local >= p.period_start
AND e.event_date_local < p.period_end
THEN 1 ELSE 0 END) AS num_starts,
-- Starts (Free Trial)
SUM(CASE WHEN e.subscription_event_type IN ('Start', 'Renewal')
AND e.offer_billing_type = 'FT'
AND e.event_date_local >= p.period_start
AND e.event_date_local < p.period_end
THEN 1 ELSE 0 END) AS num_starts_ft,
-- Starts (Hard Offer / Paid)
SUM(CASE WHEN e.subscription_event_type IN ('Start', 'Renewal')
AND e.offer_billing_type = 'HO'
AND e.event_date_local >= p.period_start
AND e.event_date_local < p.period_end
THEN 1 ELSE 0 END) AS num_starts_ho,
-- Cancels (all)
SUM(CASE WHEN e.subscription_event_type = 'Cancel'
AND e.event_date_local >= p.period_start
AND e.event_date_local < p.period_end
THEN 1 ELSE 0 END) AS num_cancels,
-- Cancels (Hard Offer)
SUM(CASE WHEN e.subscription_event_type = 'Cancel'
AND e.offer_billing_type = 'HO'
AND e.event_date_local >= p.period_start
AND e.event_date_local < p.period_end
THEN 1 ELSE 0 END) AS num_cancels_ho,
-- Cancels (Free Trial)
SUM(CASE WHEN e.subscription_event_type = 'Cancel'
AND e.offer_billing_type = 'FT'
AND e.event_date_local >= p.period_start
AND e.event_date_local < p.period_end
THEN 1 ELSE 0 END) AS num_cancels_ft
FROM settled_events e
CROSS JOIN reporting_periods p
GROUP BY DATE_FORMAT(p.period_start, '%Y-%m'), e.offer_marketplace
),
-- =============================================================================
-- STEP 4B: SNAPSHOT METRICS (from subscription spans - NO EOD settlement)
-- Use for: end-of-period counts, conversions, settled trials
-- =============================================================================
snapshot_metrics AS (
SELECT
DATE_FORMAT(p.period_start, '%Y-%m') AS report_month,
s.offer_marketplace,
-- End of period counts (who was active at period end?)
COUNT(DISTINCT CASE
WHEN s.span_start_date < p.period_end
AND (s.span_end_date IS NULL OR s.span_end_date >= p.period_end)
THEN s.cid
END) AS num_end_period_subs,
COUNT(DISTINCT CASE
WHEN s.offer_billing_type = 'HO'
AND s.span_start_date < p.period_end
AND (s.span_end_date IS NULL OR s.span_end_date >= p.period_end)
THEN s.cid
END) AS num_end_period_subs_ho,
COUNT(DISTINCT CASE
WHEN s.offer_billing_type = 'FT'
AND s.span_start_date < p.period_end
AND (s.span_end_date IS NULL OR s.span_end_date >= p.period_end)
THEN s.cid
END) AS num_end_period_subs_ft,
-- Active at any point during the period
COUNT(DISTINCT CASE
WHEN s.span_start_date < p.period_end
AND (s.span_end_date IS NULL OR s.span_end_date >= p.period_start)
THEN s.cid
END) AS num_active_period_subs,
COUNT(DISTINCT CASE
WHEN s.offer_billing_type = 'HO'
AND s.span_start_date < p.period_end
AND (s.span_end_date IS NULL OR s.span_end_date >= p.period_start)
THEN s.cid
END) AS num_active_period_subs_ho,
COUNT(DISTINCT CASE
WHEN s.offer_billing_type = 'FT'
AND s.span_start_date < p.period_end
AND (s.span_end_date IS NULL OR s.span_end_date >= p.period_start)
THEN s.cid
END) AS num_active_period_subs_ft,
-- Conversions (FT span ending with HO start in this period)
COUNT(CASE
WHEN s.offer_billing_type = 'FT'
AND s.span_end_billing_type = 'HO'
AND s.span_end_event_type IN ('Start', 'Renewal')
AND s.span_end_date >= p.period_start
AND s.span_end_date < p.period_end
THEN 1
END) AS num_ft_conversions,
-- FT Settled (FT span that ended in this period)
COUNT(CASE
WHEN s.offer_billing_type = 'FT'
AND s.span_end_date IS NOT NULL
AND s.span_end_date >= p.period_start
AND s.span_end_date < p.period_end
THEN 1
END) AS num_ft_settled
FROM subscription_spans s
CROSS JOIN reporting_periods p
GROUP BY DATE_FORMAT(p.period_start, '%Y-%m'), s.offer_marketplace
)
-- =============================================================================
-- FINAL OUTPUT: Combine flow and snapshot metrics
-- =============================================================================
SELECT
sm.report_month,
sm.offer_marketplace,
-- Starts (from settled events)
sm.num_starts,
sm.num_starts_ft,
sm.num_starts_ho,
-- Net Starts (paid starts + conversions)
sm.num_starts_ho + sn.num_ft_conversions AS num_net_starts_ho,
-- Cancels (from settled events)
sm.num_cancels,
sm.num_cancels_ho,
sm.num_cancels_ft,
-- Free Trial Metrics (from spans)
sn.num_ft_conversions,
sn.num_ft_settled,
CASE WHEN sn.num_ft_settled > 0
THEN ROUND(100.0 * sn.num_ft_conversions / sn.num_ft_settled, 2)
ELSE 0
END AS ft_conversion_rate_pct,
-- Active Subscribers (from spans)
sn.num_active_period_subs,
sn.num_active_period_subs_ho,
sn.num_active_period_subs_ft,
-- End of Period Subscribers (from spans)
sn.num_end_period_subs,
sn.num_end_period_subs_ho,
sn.num_end_period_subs_ft
FROM settled_metrics sm
JOIN snapshot_metrics sn
ON sm.report_month = sn.report_month
AND sm.offer_marketplace = sn.offer_marketplace
ORDER BY sm.report_month, sm.offer_marketplace
Nota: Sostituisci your_subscription_events_table con il nome effettivo della tua tabella. Modifica l’intervallo di date reporting_periods in base alle tue esigenze di analisi.
