Das Abonnement-Ereignisprotokoll enthält Abonnementdaten auf Veranstaltungsebene für Ihren Prime Video-Kanal. Greife mit anonymisierten Kundenkennungen auf unformatierte Abonnementereignisse wie Start, Renewal, Cancel und automatische Verlängerung zu, um benutzerdefinierte Analysen zu erstellen, die auf deine Geschäftsanforderungen zugeschnitten sind. In diesem Handbuch werden das Datenmodell, die Dimensionen, Metriken und Best Practices für die Erstellung von Abonnementanalysen behandelt.
Warum sollte ich das Abonnement-Ereignisprotokoll (Subscription Event Log) verwenden?
Das Abonnement-Ereignisprotokoll bietet ein Änderungsprotokoll mit Abonnementanalysen, das über das hinausgeht, was in vorgefertigten Dashboards verfügbar ist:
- Custom Analytics – Erstelle mithilfe von Rohdaten auf Veranstaltungsebene Kennzahlen, die auf deine spezifischen Geschäftsdefinitionen und Berichtsanforderungen zugeschnitten sind.
- Data Warehouse Integration – Integriere Prime Video-Abonnementdaten in deine internen Systeme und andere Datenquellen für eine umfassende Analyse.
- Flexible Business Logic – Wende deine eigenen Abrechnungsregeln, Deduplizierungslogik und Kennzahlberechnungen an, um deinen Geschäftsanforderungen gerecht zu werden.
- Kundenlebenszyklus-Tracking – Verfolge individuelle Kundenerlebnisse über Abonnementereignisse hinweg mit stabilen, anonymisierten Identifikatoren.
So funktioniert’s
Das Abonnement-Ereignisprotokoll liefert Abonnementdaten als Event-Stream, der nur zum Anhängen verwendet werden kann, über eine API. Jeder Datensatz steht für ein einzelnes Ereignis im Abonnementlebenszyklus eines Kunden. Die Daten enthalten Rohereignisse ohne vorab angewandte Geschäftslogik, sodass du die Metriken ganz nach deinen Bedürfnissen berechnen kannst.
Auswahl der richtigen Datenzugriffsmethode
Slate Insights bietet zwei Möglichkeiten, auf Abonnementdaten zuzugreifen:
Funktion |
Slate Analytics Dashboard |
Subscription Event Log (API) |
Am besten für |
Schnelle Einblicke, Berichterstattung für Leitungspersonal |
Kundenspezifische Analytik, Datentechnik |
Datenformat |
Vorab aggregierte Metriken |
Rohdaten auf Veranstaltungsebene |
Abrechnungslogik |
Automatisch angewendet |
Du wendest deine eigene Logik an |
Deduplizierung |
Für dich erledigt |
Du deduplizierst mit last_update_time_utc |
Flexibilität |
Feste Metrikdefinitionen |
Vollständige Anpassung |
Aktualisierungsfrequenz |
Ungefähr alle 8 Stunden |
Ungefähr alle 4 Stunden |
Historische Daten |
Bis zu 2 Jahre |
Bis zu 2 Jahre |
Zugriffsmethode |
Webbenutzeroberfläche |
REST-API |
Verwende das Dashboard, wenn du schnelle Antworten oder vorgefertigte Visualisierungen benötigst. Verwende die Event Log API, wenn du benutzerdefinierte Metriken erstellen, in dein Data Warehouse integrieren oder geschäftsspezifische Logik anwenden musst.
Die wichtigsten Konzepte
Das Verständnis dieser grundlegenden Konzepte hilft dir dabei, effektiv mit dem Subscription Event Log zu arbeiten.
Konzept |
Beschreibung |
Event |
Eine diskrete Abonnementaktion (Starten, Verlängern, Stornieren, Automatische Verlängerung ein/aus). Jedes Ereignis wird durch eine eindeutige subscription_event_id identifiziert. |
Dimension |
Ein Attribut, das den Kontext eines Ereignisses beschreibt, z. B. das Datum, an dem es eingetreten ist, den beteiligten Kunden oder den Angebotstyp. Dimensionen werden verwendet, um Daten zu filtern und zu gruppieren. |
Metrik |
Eine berechnete Messung, die aus Ereignissen wie Anzahl aktiver Abonnenten, Abwanderungsrate oder Konversionsrate abgeleitet wird. Metriken werden nicht direkt gespeichert, du berechnest sie aus den Eventdaten. |
Changelog-Modell |
Daten können nur angehängt werden. Wenn sich die Attribute eines Ereignisses ändern, wird ein neuer Datensatz mit derselben subscription_event_id, aber einer neueren last_update_time_utc veröffentlicht. |
Abrechnung |
Geschäftslogik, die Störungen aus Rohereignissen herausfiltert. Beispielsweise durch Ausschluss von Paaren aus Start- und Stornierungen am selben Tag, die keine aussagekräftigen Abonnements darstellen. |
Lebenszyklus |
Ein Abonnement-"Zyklus“, der einen kontinuierlichen Abonnementzeitraum darstellt. Ein neuer Lebenszyklus beginnt nach jedem abgerechneten Stornierungsereignis. |
Hinweis: Beginne mit Events and Dimensions, um Daten abzufragen, wende Settlement- und Lifecycle-Logik an, um sie zu bereinigen, und berechne dann Metrics, die deinen Geschäftsdefinitionen entsprechen.
Dimensions
Dimensions sind Attribute, die den Kontext jedes Abonnementereignisses beschreiben. Sie werden verwendet, um Daten zu filtern, Ergebnisse zu gruppieren und Berichte zu erstellen.
Event Dimension
Subscription_Event_Type ist der Typ des Abonnementereignisses, das aufgetreten ist. Dies ist die wichtigste Dimension, um die Aktionen im Kundenlebenszyklus zu verstehen.
Wert |
Beschreibung |
Anwendungsfall |
Start |
Der Kunde hat deinen Kanal zum ersten Mal abonniert. Zum Zeitpunkt des Verkaufs existiert kein vorheriger Startdatensatz für diesen Kunden. |
Zähle die Akquisition neuer Abonnenten |
Renewal |
Das Abonnement des Kunden wurde aus allen früheren Zeiträumen der Vergangenheit fortgesetzt. Renewal-Ereignisse treten auch dann auf, wenn zwischen den Abonnementzeiträumen Lücken bestehen, vorausgesetzt, der Kunde hat historische Abonnementdaten. |
Zähle gebliebene Abonnenten |
Cancel |
Das Abonnement des Kunden ist abgelaufen. Kann vom Kunden veranlasst worden sein oder auf einen Zahlungsausfall zurückzuführen sein. |
Abwanderung berechnen |
Active - AR ON |
Der Kunde hat die automatische Verlängerung aktiviert. Das Abonnement wird am Ende des Zeitraums verlängert. |
Rate der automatischen Verlängerung verfolgen |
Active - AR OFF |
Der Kunde hat die automatische Verlängerung deaktiviert. Das Abonnement läuft am Ende des Zeitraums ab, sofern es nicht erneut aktiviert wird. |
Gefährdete Abonnenten identifizieren |
Suspended |
Das Abonnement des Kunden wurde aufgrund von Zahlungsproblemen (unzureichendes Guthaben oder ungültige Rechnungsinformationen) vorübergehend ausgesetzt. Das Abonnement wird automatisch fortgesetzt und ein Renewal-Ereignis generiert, sobald der Kunde seine Rechnungsinformationen aktualisiert hat und die Zahlung erfolgreich verarbeitet wurde. |
Hilft bei der Nachverfolgung von Abonnements, die vorübergehend ausgefallen sind (werden Renewals) oder zu Stornierungen (Cancellations) führen könnten |
Hinweis: Ein Startereignis tritt nur ein, wenn wir zum Zeitpunkt des Verkaufs noch keinen Startdatensatz für diesen Kunden auf deinem Kanal haben. Für eine zuverlässige Zählung der aktiven Abonnenten solltest du sowohl Start- als auch Renewal-Ereignisse als Indikatoren für ein aktives Abonnement behandeln. Bei diesem Ansatz werden alle Randfälle konsistent behandelt.
Offer Dimensions
Diese Dimensionen beschreiben das Abonnementangebot, das mit jedem Event verbunden ist.
Dimension |
Typ |
Format |
Beschreibung |
is_promo |
Ganzzahl |
0 oder 1 |
Gibt an, ob das Abonnementereignis während eines Aktionsangebotszeitraums stattgefunden hat. 0 = Standardangebot (keine Werbeaktion aktiv), 1 = Aktionsangebot (Rabatt oder Sonderpreis aktiv). |
offer_type |
Zeichenfolge |
Text |
Stellt die Angebotsklassifizierung für Amazon dar. Für Channels wird dies immer ‘3P_SUBS’ sein. |
offer_marketplace |
Zeichenfolge |
Marketplace code |
Der Amazon-Marketplace, auf dem das Abonnementangebot verfügbar ist. Werte: USA (Vereinigte Staaten), Großbritannien (Großbritannien), DE (Deutschland), JP (Japan), ROE (Rest von Europa), ROW_NA (Rest der Welt – Nordamerika), ROW_EU (Rest der Welt – Europa), ROW_FE (Rest der Welt – Fernost). Der offer_marketplace gibt an, wo das Angebot gekauft wurde, was vom aktuellen Standort des Kunden abweichen kann. |
offer_name |
Zeichenfolge |
Text |
Der menschenlesbare Name des Abonnementangebots, wie er in deinem Kanal-Setup konfiguriert ist. |
offer_payment_amount |
numerisch |
Numerischer Wert (z. B. „4,99", „9,99") |
Der Listenpreis des Abonnementangebots in der Landeswährung. Dieses Feld dient nur Angebotsmetadaten und stellt keinen Transaktionsumsatz dar. Dieses Feld zeigt den Preis des Angebots im Zusammenhang mit des Events an – es handelt sich dabei nicht um tatsächlich erzielte Einnahmen oder erhaltene Gelder. Derselbe Wert wird für jeden Ereignistyp (Start, Renewal, Cancel, AR changes) angezeigt, unabhängig davon, ob die Zahlung erfolgt ist. Du kannst dieses Feld nicht summieren, um den Umsatz zu berechnen. Die tatsächlichen Umsatzdaten findest du in deinen separaten Finanzberichtssystemen. |
offer_billing_type |
Zeichenfolge |
FT oder HO |
Das Zahlungsmodell für das Abonnementangebot. FT = Free Trial (kostenlose Einführungsphase), HO = Hard Offer (kostenpflichtiges Abonnement). |
offer_id |
Zeichenfolge |
Identifier |
Die eindeutige Kennung für das Abonnementangebot. Verwende diese Option, um Angebots-Metadaten hinzuzufügen oder die Leistung nach Angebot zu verfolgen. |
Hinweis: Der offer_marketplace gibt an, wo das Abonnement gekauft wurde, und bestimmt die Preise, die Währung und die Verfügbarkeit von Inhalten. Ein einzelner Kunde kann Abonnements auf mehreren Marketplaces haben. Verwende offer_billing_type, um bei der Berechnung der Konversionsmetriken zwischen Gratiszeiträumen und kostenpflichtigen Abonnements zu unterscheiden.
Customer Dimensions
Diese Dimensionen identifizieren und beschreiben den Abonnenten.
Dimension |
Typ |
Format |
Beschreibung |
cid |
Zeichenfolge |
Anonymisierter Identifier |
Eine anonymisierte, stabile Kundenidentifikation. Diese ID ist für alle Ereignisse eines bestimmten Kunden in deinem Kanal konsistent, sodass du den gesamten Kundenlebenszyklus verfolgen kannst. Die CID ist in deinem übergeordneten Kanal konsistent. Derselbe Kunde hat dieselbe ID für alle Angebote und Pakete, die auf demselben übergeordneten Kanal innerhalb eines Marketplaces angeboten werden (Beispiel: Ein Kunde, der „Prime Ad-Supported (US)“ abonniert und später zu „Prime Ad-free (US)“ wechselt, behält dieselbe ID, da sich beide Angebote den übergeordneten Kanal „Prime (US)“ teilen). Kunden werden auf den verschiedenen Partnerkanälen unterschiedliche CID-Werte haben, was den Datenschutz gewährleistet und gleichzeitig Analysen pro Kanal ermöglicht. |
benefit_id |
Zeichenfolge |
Identifier |
Die mit dem Abonnement verknüpfte Prime Video-Vorteils-ID. Dadurch wird das Abonnement mit bestimmten Inhaltszugriffsrechten verknüpft. |
channel_label |
Zeichenfolge |
Text |
Der Name des Kanals, zu dem das Abonnement gehört. |
channel_tier_label |
Zeichenfolge |
Text |
Der Name der spezifischen Stufe innerhalb des Kanals, falls zutreffend. Wenn dein Kanal keine Stufen verwendet, ist dieser Wert möglicherweise Null oder entspricht dem channel_label. |
Hinweis: Die CID ermöglicht die Nachverfolgung des Kundenlebenszyklus bei gleichzeitiger Wahrung der Privatsphäre. Verwende cid, um individuelle Verhaltensmuster von Kunden zu analysieren, Kundenbindungskennzahlen zu berechnen und Ereignisse bei erneuten Abonnements zu identifizieren.
Time Dimensions
Diese Dimensionen bieten den zeitlichen Kontext für Abonnementereignisse.
Dimension |
Typ |
Format |
Beschreibung |
subscription_event_time_utc |
Zeitstempel |
ISO 8601: jjjj-mm-ttthh:mm: ss.sssz |
Der Zeitstempel, zu dem das Abonnementereignis eingetreten ist, gespeichert in UTC. Alle Event-Zeitstempel werden aus Konsistenzgründen in UTC gespeichert. Verwende subscription_event_time_zone, um für die Berichterstattung in die Ortszeit umzurechnen. |
subscription_event_time_zone |
Zeichenfolge |
Zeitzonenkennung (z. B. America/New_York, Europe/London) |
Die Zeitzone der Marketplace-Site, in der das Abonnement-Ereignis eingetreten ist. Das Slate Analytics-Dashboard präsentiert Daten in der lokalen Zeitzone. |
create_time_utc |
Zeitstempel |
ISO 8601-Format |
Der Zeitstempel, zu dem der Abonnementereignisdatensatz zum ersten Mal im Changelog erstellt wurde, gespeichert in UTC. Dies gibt an, wann der Datensatz zum Changelog hinzugefügt wurde, nicht, wann das Ereignis eingetreten ist. Verwende subscription_event_time_utc für die tatsächliche Ereigniszeit. |
last_update_time_utc |
Zeitstempel |
ISO 8601-Format |
Der Zeitstempel, zu dem der Abonnementereignisdatensatz zuletzt im Changelog aktualisiert wurde, gespeichert in UTC. Verwende dieses Feld immer, um Datensätze zu deduplizieren. Wenn mehrere Datensätze für dieselbe subscription_event_id existieren, behalte nur den Datensatz mit der neuesten last_update_time_utc bei. |
Beispiel für eine Konvertierung (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
Hinweis: Verwende subscription_event_time_utc für alle Berechnungen und Filterung. Zur Konvertierung in die lokale Zeitzone verwende subscription_event_time_zone nur für Berichts- und Anzeigezwecke.
Record Management Dimensions
Diese Dimensionen helfen dir, die Changelog-Daten korrekt zu verwalten.
Dimension |
Typ |
Format |
Beschreibung |
subscription_event_id |
Zeichenfolge |
UUID-Format |
Die eindeutige Kennung für jedes Abonnementereignis. Dies ist der Primärschlüssel für die Deduplizierung. Es können mehrere Datensätze für dieselbe subscription_event_id existieren, wenn die Attribute des Ereignisses aktualisiert wurden. Dedupliziere immer, indem du den Datensatz mit der neuesten last_update_time_utc behalten. |
is_deleted |
Ganzzahl |
0 oder 1 |
Gibt an, ob ein zuvor veröffentlichter Datensatz aus deinem Datensatz entfernt werden soll. 0 = Aktiver Datensatz (in die Analyse einbeziehen), 1 = Gelöschter Datensatz (von der Analyse ausschließen – aus deinen Tabellen entfernen, wenn er zuvor aufgenommen wurde). Gelöschte Datensätze sind selten, können aber auftreten, wenn Datenkorrekturen erforderlich sind. Filtere in deinen Abfragen immer is_deleted = 0. |
Hinweis: Dedupliziere immer mit last_update_time_utc, bevor du eine Analyse durchführst. Filtere gelöschte Datensätze (is_deleted = 1) zu Beginn deiner ETL-Pipeline heraus, um die Datengenauigkeit sicherzustellen.
Data Cleansing & Settlement Logic
In diesem Abschnitt wird der zweistufige Deduplizierungsprozess erläutert, mit dem rohe Ereignisdaten in saubere, geschäftsrelevante Kennzahlen umgewandelt werden. Einige Partner verwenden eine andere Abrechnungslogik. Aus Gründen der Flexibilität stellen wir daher unformatierte Eventdaten zur Verfügung. Dieser Abschnitt enthält die Logik, die wir bei Prime Video verwenden, damit Partner Metriken mit dem Slate Analytics-Dashboard abgleichen und sehen können, wie wir die Daten modellieren.
Warum ist Settlement wichtig?
Rohdaten zu Abonnementereignissen enthalten Störungen, die deine Kennzahlen in die Höhe treiben oder verzerren können:
Problem |
Beispiel |
Wirkung ohne Abrechnung |
Abwanderung am selben Tag |
Der Kunde startet und storniert innerhalb weniger Stunden |
Wird sowohl als Start als auch als Abbruch doppelt gezählt |
Wiederholte Zahlungsversuche |
Mehrere Stornierungsereignisse aufgrund von Abrechnungsfehlern |
Überhöhte Abwanderungszahlen |
Storniert ohne Start |
Ereignisse ohne entsprechenden Start im Lebenszyklus stornieren |
Überhöhte Kundenabwanderung ohne entsprechende Akquisition |
Die Abrechnungslogik stellt sicher, dass Ihre Kennzahlen aussagekräftige Abonnementaktivitäten widerspiegeln und nicht systembedingte Störungen.
Stage 1: End of Day Settlement
Behandele Paare mit Start und Abbruch am selben Tag, indem du nur den Tagesendstatus (EOD) beibehältst. Wenn für einen Kunden am selben Kalendertag sowohl ein Start/Renewal- als auch ein Cancel-Termin stattfindet, behalte nur das chronologisch letzte Ereignis für diesen Tag bei. Stornierungen am selben Tag deuten häufig auf Fehler bei der Zahlungsautorisierung oder auf Probleme beim Anmeldevorgang hin. Diese stehen nicht für eine sinnvolle Abonnentenbindung und das Zählen von sowohl aufgeblähten Starts als auch Stornierungen.
Implementation
-- 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
)
Beispiel
Diese Tabelle zeigt, wie die EOD-Abrechnungslogik die Start- und Cancel-Ereignispaare am selben Tag am 15.10.2025 behandelt, indem nur der endgültige Status am Ende des Tages beibehalten wird.
Zeit |
Event |
Ohne EOD-Abrechnung |
Mit EOD-Abrechnung |
2025/08/01 00:00 |
Start |
Als Start gezählt |
Als Start gezählt |
2025/09/01 00:00 |
Renewal |
- |
- |
2025/10/01 00:00 |
Renewal |
- |
- |
15.10.2020 07:00 |
Cancel |
Als Stornierung gezählt |
Excluded |
15.10.2020 09:00 |
Start |
Als Start gezählt |
Excluded |
2025/10/15 09:15 |
Cancel |
Als Stornierung gezählt |
Als Stornierung gezählt |
Hinweis: Der Kunde wird nur als Cancel (oder gar nicht, je nach Lebenszykluskontext) gezählt, nicht gleichzeitig als Start- und Storniervorgang. Die EOD-Abrechnung gilt nur für abgewickelte Kennzahlen (Starts, Stornierungen). Snapshot-Metriken verwenden Abonnementzeiträume, die aus im Changelog deduplizierten Ereignissen ohne EOD-Abrechnung erstellt wurden, um den vollständigen Zeitplan für Ereignisse beizubehalten, der für Point-in-Time-Berechnungen und Konvertierungen benötigt wird.
Stage 2: Lifecycle Deduplication
Gruppiere Ereignisse in Abonnement-Zyklen und behalte nur den ersten Start- und den ersten Cancel pro Lebenszyklus bei. Ein gültiger Abbruch erfordert einen entsprechenden Start im selben Lebenszyklus.
Implementation-- 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)
)
Die Bedingung lifecycle_has_start = 1 stellt sicher, dass Stornierungen nur gezählt werden, wenn der Lebenszyklus ein entsprechendes Startereignis hat.
Beispiel
Kunde |
Event |
Zeit |
Lebenszyklus |
Behalten? |
ABC |
Start |
01.01.2025 |
1 |
✓ Erster Start im Lebenszyklus 1 |
ABC |
Renewal |
1.2.2025 |
1 |
✗ Nicht der erste Start in Lebenszyklus 1 |
ABC |
Cancel |
01.03.2025 |
1 |
✓ Erster Abbruch im Lebenszyklus 1 |
ABC |
Start |
15.04.2025 |
2 |
✓ Erster Start im Lebenszyklus 2 |
ABC |
Cancel |
15.05.2025 |
2 |
✓ Erster Abbruch im Lebenszyklus 2 |
Wichtig: Wie bei der EOD-Abrechnung wird die Lifecycle-Deduplizierung nur auf abgewickelte Metriken angewendet. Snapshot-Metriken verwenden die unformatierten Abonnementspannen, wobei bei jedem Start/Renewal eine Zeitspanne entsteht, die mit dem nächsten Ereignis endet, sodass der vollständige Zeitplan erhalten bleibt, der für Point-in-Time-Berechnungen benötigt wird.
Metrics
Metriken sind berechnete Messwerte, die aus Abonnementereignissen abgeleitet werden. Im Gegensatz zu Dimensionen werden Metriken nicht direkt im Event-Log gespeichert, sondern du berechnest sie, indem du die Eventdaten aggregierst und transformierst. Beachte, dass es sich bei den bereitgestellten Berechnungsformeln um Empfehlungen handelt. Du kannst sie an deine Geschäftsdefinitionen anpassen.
Core Metrics
Die folgenden Kennzahlen sind für Abonnementanalysen von grundlegender Bedeutung:
Metrik |
Beschreibung |
End of Period Subscribers |
Kunden mit einem aktuellen Abonnement am Ende des Zeitraums |
Active Subscribers |
Kunden, die während des Zeitraums jederzeit ein aktives Abonnement haben |
Subscriber Acquisition (Starts) |
Neue dazugewonnene Abonnenten |
Cancellations |
Abonnenten, die gekündigt haben |
Free Trial Conversion Rate |
Konversion Gratiszeitraum zu kostenpflichtig |
Net Paid Starts |
Neue zahlende Abonnenten, die Konversionen berücksichtigen |
Subscriber Metrics
Diese Kennzahlen verfolgen die Akquisition und Kündigung neuer Abonnenten nach Anwendung der Abrechnung und der Deduplizierung über den Lebenszyklus.
Metrik |
Definition |
Berechnung |
num_starts |
Anzahl der Start- und Renewal-Ereignisse, die den Beginn eines neuen Abonnementlebenszyklus innerhalb des Berichtszeitraums markieren. |
Nachdem du die Abrechnung und die Lebenszyklus-Deduplizierung angewendet hast, zähle Ereignisse, bei denen subscription_event_type IN (‘Start’, ‘Renewal’) und das Ereignis in den Zeitraum fallen. 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 |
Anzahl der Starts, bei denen die Abrechnungsart Gratiszeitraum (FT) für das Angebot lautet. |
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 |
Anzahl der Starts, bei denen die Angebotsabrechnungstyp Hard Offer (HO) – kostenpflichtige Abonnements ist. |
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 |
Netto neuer zahlender Abonnenten, berechnet als direkte HO-Starts plus Konversionen aus Gratiszeiträumen. Bietet einen vollständigen Überblick über neue zahlende Abonnenten, unabhängig davon, ob sie sich direkt für einen kostenpflichtigen Tarif angemeldet oder von einem Gratiszeitraum konvertiert haben. |
num_net_starts_ho = num_starts_ho + num_ft_conversions |
num_cancels |
Anzahl der Stornierungsereignisse innerhalb des Berichtszeitraums nach Anwendung der Abrechnungs- und Lebenszyklus-Deduplizierung. |
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 |
Stornierungen, bei denen der Abonnent einen Tarif mit Hard Offer (kostenpflichtig) hatte. |
- |
num_cancels_ft |
Stornierungen, bei denen der Abonnent einen Gratiszeitraum-Plan hatte (der Gratiszeitraum wurde ohne Konvertierung beendet). |
- |
Period Metrics
Diese Kennzahlen liefern Momentaufnahmen und Periodenaktivitäten anhand von Abonnementzeiträumen, die ohne EOD-Abrechnung erstellt wurden.
Metrik |
Definition |
Berechnung |
Varianten |
num_end_period_subs |
Anzahl der eindeutigen Abonnenten, die am Ende des Berichtszeitraums aktiv waren. Dies ist ein Point-in-Time-Snapshot. |
Von subscription_spans (ohne EOD-Abrechnung erstellt) COUNT(DISTINCT CASE 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 – Abonnenten am Ende des Zeitraums mit Hard-Offer-Plänen, num_end_period_subs_ft – Abonnenten am Ende des Zeitraums mit Gratiszeitraum-Plänen |
num_active_period_subs |
Anzahl der eindeutigen Abonnenten, die zu einem beliebigen Zeitpunkt im Berichtszeitraum aktiv waren. Erfasst alle Kunden, deren aktive Abonnementdauer sich mit dem Zeitraum überschneidet, unabhängig davon, wann sie begonnen oder gekündigt wurden. |
Von subscription_spans (ohne EOD-Abrechnung erstellt) COUNT(DISTINCT CASE 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 – Abonnenten, die zu einem beliebigen Zeitpunkt während des Zeitraums mit Hard-Offer (kostenpflichtig) aktiv sind; num_active_period_subs_ft – Abonnenten, die zu einem beliebigen Zeitpunkt während des Zeitraums mit Gratiszeitraum-Plänen aktiv sind |
Hinweis: Periodenmetriken verwenden subscription_spans, die ohne EOD-Abrechnung erstellt wurden, um den vollständigen Zeitplan der Ereignisse beizubehalten.
Free trial metrics
Diese Kennzahlen verfolgen Konversionen und Abrechnungen aus Gratiszeiträumen anhand von Abonnementzeiträumen, die ohne EOD-Abrechnung erstellt wurden.
Metrik |
Definition |
Berechnung |
num_ft_conversions |
Anzahl der Abonnenten der des Gratiszeitraums, die innerhalb des Berichtszeitraums auf einen kostenpflichtigen Tarif (Hard Offer) umgestellt haben. Eine Konvertierung erfolgt, wenn das nächste Ereignis eines Abonnenten eines Gratiszeitraums (offer_billing_type = ‘FT’) ein Start/Renewal mit offer_billing_type = ‘HO’ ist. |
Von subscription_spans (ohne EOD-Abrechnung erstellt) 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 |
Anzahl der kostenlosen Testabonnements, die innerhalb des Berichtszeitraums abgewickelt (abgeschlossen) wurden. Ein Gratiszeitraum wird abgerechnet, wenn der Abonnent entweder auf kostenpflichtig umstellt oder storniert (die nächste Abrechnungsart ist nicht 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 |
Prozentsatz der abgerechneten Gratiszeiträume, die in kostenpflichtige Abonnements umgewandelt wurden. |
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 |
Hinweis: Die Metriken für Gratiszeiträume verwenden subscription_spans, die ohne EOD-Abrechnung erstellt wurden, um Konversionen genau zu verfolgen, bei denen Kunden von einer Angebots-ID zu einer anderen wechseln.
Grundlegendes zu Ereignismustern
In diesem Abschnitt werden allgemeine Ereignismuster, auf die du im Subscription Event Log stößt, und erklärt, wie du sie richtig interpretierst.
Subscription Lifecycle
Ein typisches Abonnement folgt diesem Lebenszyklus und durchläuft vier wichtige Phasen, von der ersten Anmeldung über die Verlängerungszyklen bis hin zur möglichen Kündigung. In jeder Phase werden bestimmte Ereignistypen generiert, mit denen du das Kundenverhalten verfolgen und Abonnementkennzahlen berechnen kannst.
Key Lifecyle Points
Diese wichtigen Lebenszykluspunkte stellen die vier kritischen Phasen des Abonnementprozesses eines Kunden dar. Jede dieser Phasen generiert unterschiedliche Ereignistypen, anhand derer Akquise-, Bindungs- und Abwanderungsmuster nachverfolgt werden können.
Stage |
Event Type |
Beschreibung |
Acquisition |
Start |
Neuer Kunde tritt deinem Kanal bei |
Retention |
Renewal |
Bestandskunde setzt Abonnement fort |
Engagement |
Active - AR ON/OFF |
Kunde passt die Präferenz für die automatische Verlängerung an |
Churn |
Cancel |
Das Abonnement des Kunden endet |
Allgemeine Ereignismuster
Pattern 1: Renewal ohne Start
In der Eventhistorie eines Kunden werden Renewal-Ereignisse, aber kein Start angezeigt. Das ursprüngliche Startereignis des Kunden ereignete sich vor mehr als 2 Jahren. Aufgrund der Richtlinie zur Datenspeicherung sind Ereignisse, die älter als 2 Jahre sind, nicht verfügbar. Die Renewal-Ereignisse im Fenster sind weiterhin zugänglich.
Pattern 2: Multiple Cancel Events
Ein Kunde hat innerhalb von Minuten oder Stunden mehrere Stornierungsereignisse. Dieses Muster weist in der Regel auf fehlgeschlagene Zahlungsversuche hin: Das Abrechnungssystem versucht, die Zahlungsart des Kunden zu belasten. Jeder fehlgeschlagene Versuch kann ein Cancel-Ereignis auslösen. Der Kunde versucht möglicherweise, mit einer abgelehnten Karte ein erneutes Abonnement abzuschließen. Bei der Lifecycle-Deduplizierung wird nur der erste Abbruch pro Lebenszyklus beibehalten.
Pattern 3: Same-Day Start and Cancel
Ein Kunde hat sowohl einen Start- als auch einen Cancel-Vorgang am selben Kalendertag. Zu den häufigsten Ursachen gehören:
- Kunde hat sich angemeldet und sofort storniert
- Die Zahlungsautorisierung ist nach der ersten Anmeldung fehlgeschlagen
- Betrügerischer Anmeldeversuch blockiert
EOD Settlement erledigt dies automatisch, nur der endgültige Status wird beibehalten.
Pattern 4: Free Trial to Paid Conversion
Ein Kunde hat einen Start mit offer_billing_type = ‘FT’, gefolgt von einem weiteren Start oder einem Renewal mit offer_billing_type = ‘HO’. Der Kunde hat seinen Gratiszeitraum abgeschlossen und in ein kostenpflichtiges Abonnement umgewandelt. Verwende die LEAD()-Fensterfunktionen, um zu erkennen, wann offer_billing_type von FT zu HO wechselt – zählt als Konversion, wenn das Übergangsereignis in deinen Berichtszeitraum fällt.
Ereignisse automatisch verlängern
Ereignisse zur automatischen Verlängerung (AR) verfolgen, wann Kunden die automatische Abonnementverlängerung aktivieren oder deaktivieren. Diese Ereignisse liefern frühe Signale, um zukünftige Kundenabwanderung vorherzusagen und gefährdete Abonnenten zu identifizieren.
Event Type |
Beschreibung |
Anwendungsfall |
AR_ON |
Der Kunde hat die automatische Verlängerung aktiviert. Das Abonnement des Kunden verlängert sich automatisch am Ende des aktuellen Zeitraums. |
Verfolge Kunden, die sich verpflichtet haben, ihr Abonnement fortzusetzen. |
AR_OFF |
Der Kunde hat die automatische Verlängerung deaktiviert. Ihr Abonnement läuft am Ende des aktuellen Zeitraums ab, sofern sie die automatische Verlängerung nicht erneut aktivieren. |
Frühwarnsignal: Kunden, die die automatische Verlängerung deaktivieren, signalisieren, dass sie beabsichtigen, das Abonnement zu verlassen, bevor ihr Abonnement tatsächlich endet. Abwanderungsprognose: AR OFF-Abonnenten werden am Ende des Zeitraums definitiv abwandern, sofern sie die automatische Verlängerung nicht erneut aktivieren. Dies macht AR-OFF-Ereignisse zu einem der zuverlässigsten Prädiktoren für zukünftige Kundenabwanderung. |
Hinweis: Ereignisse zur automatischen Verlängerung werden protokolliert, wenn ein Kunde seine automatische Verlängerungsoption aktiv über die Abonnementverwaltungsoberfläche umschaltet.
Analyzing AR events
Um das Verhalten bei der automatischen Verlängerung zu analysieren, frage AR ON/OFF-Ereignisse getrennt von der wichtigsten Pipeline für Lifecycle-Metriken ab. AR-Ereignisse folgen denselben Änderungsprotokoll-Deduplizierungsregeln, sind jedoch nicht in den Lebenszyklusberechnungen für Start/Renewal/Cancel enthalten.
-- Example:Filter to AR Off Events
SELECT *
FROM deduped_events
WHERE subscription_event_type = 'AR_OFF'
GROUP BY 1, 2
ORDER BY 1, 2
ETL-Pipeline
Verwende diese ETL-Pipeline (Extract, Transform, Load), aufbauend auf den oben genannten Metrikdefinitionen und Datentransformationen, um Rohdaten des Abonnementereignisprotokolls in saubere, geschäftsrelevante Metriken umzuwandeln. Diese Abfrage kann direkt in Amazon Athena ausgeführt werden.
Die Pipeline implementiert eine Zwei-Pfad-Architektur, um verschiedene Metriktypen korrekt zu berechnen:
- Pfad A: Settled Events – Wendet EOD-Abrechnung UND Lifecycle-Deduplizierung an. Wird für abgewickelte Metriken (Starts, Cancels) verwendet. Dadurch werden Störungen wie Start-/Stornierungspaare am selben Tag herausgefiltert, die keine echten Akquisitionen darstellen.
- Pfad B: Subscription Spans – Wendet NUR die Changelog-Duplizierung an. Wird für Snapshot-Metriken (Zähler am Periodenende, FT-Konvertierungen) verwendet.
-- ============================================================================
-- 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
Hinweis: Ersetze your_subscription_events_table durch deinen tatsächlichen Tabellennamen. Passe den Datumsbereich reporting_period an deine Analyseanforderungen an.
