Trichteranalyse-SQL in Warehouse-nativer Produktanalyse

Die Analyse und das Verständnis von Funnels sind eine essenzielle Kompetenz für jeden Produktmanager. Sie helfen ihnen, die Produkt- und Funktionsnutzung zu verstehen und zu optimieren, indem sie die Customer Journey von der Akquise über Aktivierung, Kundenbindung, Weiterempfehlung und Umsatz bis hin zu den einzelnen Nutzer-Sub-Journeys innerhalb des Produkts verfolgen und visualisieren können – und dabei Konversions- und Abbruchraten hervorheben. Mithilfe der Funnel-Analyse können Produktmanager beliebige Erfolgskriterien definieren und die Schritte nachverfolgen, die ein Nutzer durchläuft, um diese Kriterien im Falle einer Konversion zu erreichen, oder den Punkt im Produkt, an dem der Nutzer den Workflow abgebrochen hat, im Falle eines Abbruchs. Diese Art der Analyse unterscheidet sich deutlich von Business Analytics, das Kennzahlen anhand verschiedener Attribute aggregiert, um Geschäftsergebnisse zu verstehen. Aufgrund meiner über 10-jährigen Erfahrung im Aufbau von Hochleistungsdatenbanken bin ich jedoch überzeugt, dass moderne Data Warehouses bestens gerüstet sind, um solche (und tatsächlich alle Produktanalyse-)Workloads effizient und in großem Umfang auszuführen. In diesem Beitrag gehe ich detailliert auf die Berechnungsmethoden ein, die der Funnel-Analyse zugrunde liegen, und erkläre, wie moderne Data Warehouses Funnel-Abfragen in großem Umfang durchführen können. Was ist Funnel-Analyse? Wikipedia definiert Funnel-Analyse als die Abbildung und Analyse einer Reihe von Ereignissen, die zu einem definierten Ziel führen, wie beispielsweise die Customer Journey von der Anzeige bis zum Kauf im Online-Marketing. Das folgende Beispiel zeigt die Konvertierung von Nutzern zur „Bezahlten Aktivierung“ nach der Teilnahme an Schulungen auf einer Online-Lernplattform: 18,6 % der Nutzer belegen den „Analytics-Kurs“ nach der Einführung. Und davon wechseln 31 % der Nutzer zur „kostenpflichtigen Aktivierung“, was einer Gesamtkonversionsrate von 5,8 % entspricht.

Ein Produktmanager, der das Engagement steigern möchte, kann die Konversions- und Abbruchraten zwischen verschiedenen Phasen analysieren und sich beispielsweise darauf konzentrieren, die Konversion zwischen „Erste Schritte“ und „Analysekurs“ zu verbessern, um die „Bezahlte Aktivierung“ zu erhöhen. Wie man einen Funnel erstellt Konzeptionell folgt ein Funnel einer geordneten Abfolge von Ereignissen anhand der „Ereigniszeit“ für jeden Nutzer, um die am weitesten fortgeschrittene Funnel-Phase zu berechnen. Diese Berechnung lässt sich auf verschiedene Weise mit SQL ausdrücken. Wir werden zwei solcher Muster betrachten und Einblicke in deren Performance geben. Bevor wir uns jedoch mit SQL befassen, definieren wir das für diese Analyse verwendete Datenmodell. Datenmodell Angenommen, wir haben ein Schema mit einer einzigen Tabelle „Ereignisse“. Die Daten in „Ereignisse“ haben folgende Struktur:

Ich habe in diesem Modell zwei vereinfachende Annahmen getroffen. Wie ich im Folgenden erläutere, stellen diese Annahmen jedoch für moderne Data Warehouses kein Problem dar. ... Im Gegensatz zu Produktanalysetools der ersten Generation wie Amplitude und Mixpanel, die nur sehr eingeschränkte Abfragemöglichkeiten bieten, sind moderne Data Warehouses darauf ausgelegt, mehrere Tabellen effizient zu verknüpfen.

Funnel Query

• Die Unterabfrage der Stufe 1 gibt eindeutige Benutzer-IDs aus, die das Ereignis „Erste Schritte“ erreicht haben.
• Die Unterabfrage der Stufe 2 verknüpft die Tabelle „Ereignisse“ mit der Ausgabe der Stufe 1, um die einzelnen Benutzer-IDs auszugeben, die nach dem Ereignis „Erste Schritte“ das Ereignis „Analysekurs“ erreicht haben.
• Die Unterabfrage der Stufe 3 verknüpft die Tabelle „Ereignisse“ mit der Ausgabe der Stufe 2, um die eindeutigen Benutzer-IDs auszugeben, die nach den Ereignissen „Erste Schritte“ und „Analysekurs“ (in dieser Reihenfolge) das Ereignis „Bezahlte Aktivierung“ erreicht haben.
• Die Ausgabe aller drei Stufen wird aggregiert, um die Anzahl der eindeutigen Benutzer in jeder Stufe zu ermitteln.

Moderne Data Warehouses verfügen über hochentwickelte Abfrageplaner zur Optimierung relationaler Bäume.

Im obigen Plan gibt es zwei Optimierungen, die besonders hervorzuheben sind:

1. Die Berechnung jeder Phase erfolgt nur einmal – Der Optimierer lagert redundante Berechnungen im Plan als Teilpläne oder Fragmente aus, um Ergebnisse innerhalb der Abfrage wiederzuverwenden. Erstklassige Engines planen Teilpläne adaptiv, um Ergebnisse und Statistiken für die weitere Planung zu nutzen.
2. Die Aggregation in der „Endausgabe“ wird unterhalb des Joins ausgeführt – Erstklassige Optimierer sollten erkennen können, dass die Aggregation bei Many-to-One-Joins wie hier unterhalb des Left Joins ausgeführt werden kann, um die Kosten eines aufwendigen Left Joins zwischen den drei Phasen zu vermeiden. Die Erstellung einer Abfrage, die vor dem Join aggregiert, ist ebenfalls eine praktikable Option für eine Analyse-Engine, falls der Optimierer dieses Muster nicht optimieren kann.

Aggregat- und Join-Operationen sind grundlegend für moderne Data Warehouses und ermöglichen die effiziente Ausführung solcher Abfragen in großem Umfang mit massiver Parallelität. Es gibt jedoch noch einige Verbesserungen, die die Abfrageleistung weiter optimieren können:

1. Verknüpfung zwischen Unterabfragen – Verknüpfungen zwischen Unterabfragen sind aufwändiger als Verknüpfungen mit der Basistabelle, da Unterabfragen keine vorab berechneten Verknüpfungsindizes besitzen. In unserem Beispiel werden „Events“ und die Ausgabe jeder Stufe auf Benutzerebene verknüpft, was bei Benutzern mit hoher Kardinalität kostspielig sein kann. Die Kreuzverknüpfung in der finalen Abfrage ist hingegen sehr effizient, da sie genau eine Zeile aus jeder Eingabe verknüpft.
2. Mehrfache Scans – Selbst nach dem Entfernen redundanter Berechnungen wird die Tabelle „Events“ dreimal durchsucht, einmal für jede Unterabfrage. Ein schnellerer und effizienterer Algorithmus würde die Tabelle „Events“ nur einmal durchsuchen.

Abfrage mit gestapelten Fensterfunktionen

Eine alternative Möglichkeit zur Deklaration dieser Berechnung ist die Verwendung von Fensterfunktionen. Fensterfunktionen ermöglichen die imperative Analyse von Ereignissequenzen über die deklarative SQL-Schnittstelle. Bei diesem Ansatz erstellen wir einen Stapel von Fensterfunktionen (Partitionierung), eine für jede Stufe im Funnel, gefolgt von der Anzahl eindeutiger „user_ids“ ganz oben im Stapel. Dieses Muster nennen wir das Muster der gestapelten Fensterfunktionen. Hier ist der SQL-Code zum Erstellen desselben Funnels mithilfe gestapelter Fensterfunktionen.

Wie zuvor nimmt der Abfrageplaner in den meisten modernen Data Warehouses zwei wichtige Optimierungen vor, um die Leistung des obigen Plans zu verbessern: Einfacher Sortieroperator über alle Fensterfunktionen hinweg – Alle Fensterfunktionen sind nach „user_id“ partitioniert und nach „event_time“ sortiert. In diesem Fall erstellt der Optimierer einen einzigen Sortieroperator, der die Daten in der gewünschten Reihenfolge an alle Fensterfunktionen weiterleitet. Lokale Aggregation mit eindeutigen Daten – Die Abfrage enthält eine Aggregation mit eindeutigen Daten nach „user_id“. Diese Aggregation kann innerhalb einer Partition (lokal) durchgeführt werden, wenn die Daten bereits nach „user_id“ partitioniert sind, da die benutzerbasierte Partitionierung sicherstellt, dass Benutzer nicht partitionenübergreifend aggregiert werden müssen. Beide oben genannten Optimierungen sind in den meisten modernen Data Warehouses Standard. Diese Formulierung der Trichterabfrage weist keine der Nachteile der Join-Sequenz-Variante auf. Es gibt jedoch ein Problem, das gelöst werden muss. Der Sortieroperator am Ende der Ereignisabfrage mag rechenintensiv erscheinen. Glücklicherweise ist diese Sortierung nicht global erforderlich; eine lokale Sortierung innerhalb der Partition genügt, da der Fensteroperator sortierte Eingaben pro Benutzer und nicht benutzerübergreifend benötigt. Diese Abfrage bietet noch Optimierungspotenzial. Die meisten Data Warehouses bieten die Möglichkeit, Tabellen mithilfe eines benutzerdefinierten Clustering-Schlüssels zu gruppieren. Ein Clustering-Schlüssel ist eine Menge von Spalten oder Spaltenausdrücken, die verwendet werden, um Tabellenzeilen für eine bessere Performance nahe beieinander zu platzieren. Das Clustern der Tabelle „Events“ nach „event_time“ bietet mehrere Vorteile: Die Kosten des Sortieroperators werden reduziert (oder sogar eliminiert), wenn die Daten nahezu (oder vollständig) nach Ereigniszeit sortiert sind. Die Scan-Performance der Tabelle „Events“ wird für Abfragen mit Zeitbereichsfiltern in der Spalte „event_time“ verbessert. Fazit In diesem Blog haben wir zwei verschiedene Formulierungen von Funnel-Analyse-SQL vorgestellt und deren jeweilige Abfragepläne analysiert, um ihr Performance-Profil in modernen Data Warehouses zu verstehen. Tools der ersten Generation wie Mixpanel und Amplitude verfügen über eine veraltete Architektur, die Datenverschiebungen erfordert, Datensilos und -duplizierungen erzeugt und zudem bei großem Umfang extrem teuer ist. Moderne Data Warehouses sind so weit entwickelt, dass sie nativ eine interaktive Benutzererfahrung für Produktanalysen bieten – ohne die genannten Einschränkungen und mit der Flexibilität, die Kosten für optimale Leistung anzupassen. Optimizely Warehouse-Native Analytics lässt sich mit allen gängigen Cloud-Data-Warehouse-Anbietern wie Snowflake, BigQuery, Redshift und Databricks verbinden und bietet so umfassende Produktanalysen – ohne Datenverschiebung und zu einem Drittel der Kosten herkömmlicher Ansätze.