Digital Marketing Academy · Lektion

Kohorten- und Trichterabfragen

Beantworten Sie Fragen zur Kundenbindung.

Kohorten- und Trichterabfragen ist eine kostenlose Digital Marketing Academy-Lektion auf CoddyKit. Dies ist Lektion 4 von 4. Du kannst die komplette Lektion unten kostenlos lesen – dann übst du sie direkt im Browser mit einem integrierten Code-Editor und einem KI-Tutor rund um die Uhr. Sie ist Teil des Digital Marketing Academy-Lernpfads, und dein Fortschritt wird über Web und CoddyKit-App synchronisiert. Der Digital Marketing Academy-Kurs umfasst insgesamt 4 Lektionen.

Was ist eine Kohorte?

Eine Kohorte ist eine Gruppe von Benutzern mit einem gemeinsamen Startereignis, normalerweise dem Registrierungsmonat. Wenn Sie jede Kohorte über die Zeit verfolgen, erkennen Sie die tatsächliche Retention.

Aggregierte Kennzahlen verbergen Abwanderung; Kohorten zeigen, ob neue Benutzer tatsächlich bleiben.

SELECT user_id,
       DATE_TRUNC('month', signup_date) AS cohort_month
FROM users;

Die Kohorte definieren

Der erste Schritt besteht darin, jeden Benutzer seiner Kohorte zuzuordnen. DATE_TRUNC rundet Registrierungsdaten auf den Monatsanfang ab, sodass Benutzer sauber gruppiert werden.

Speichern Sie dies als wiederverwendbaren Baustein für alles Weitere.

SELECT DATE_TRUNC('month', signup_date) AS cohort_month,
       COUNT(*) AS cohort_size
FROM users
GROUP BY 1
ORDER BY 1;

Aktivitätsperioden

Retention misst die Aktivität in den Monaten nach der Registrierung. Berechnen Sie die Periode als Abstand zwischen dem Monat einer Bestellung und dem Kohortenmonat.

Periode 0 ist der Registrierungsmonat, Periode 1 der Folgemonat und so weiter.

SELECT o.user_id,
       (DATE_PART('year', o.order_date) - DATE_PART('year', u.signup_date)) * 12
       + (DATE_PART('month', o.order_date) - DATE_PART('month', u.signup_date)) AS period
FROM orders o
JOIN users u ON u.user_id = o.user_id;

Ein Retention-Raster erstellen

Kombinieren Sie Kohortenmonat und Periode und zählen Sie anschließend die unterschiedlichen aktiven Benutzer pro Zelle. Das Ergebnis ist das klassische Retention-Dreieck.

Jede Zeile entspricht einer Kohorte; jede Spalte gibt die Monate seit der Registrierung an.

SELECT DATE_TRUNC('month', u.signup_date) AS cohort,
       DATE_PART('month', AGE(o.order_date, u.signup_date)) AS period,
       COUNT(DISTINCT o.user_id) AS active
FROM users u
JOIN orders o ON o.user_id = u.user_id
GROUP BY 1, 2;

Retention-Rate

Rohzahlen lassen sich zwischen Kohorten unterschiedlicher Größe nur schwer vergleichen. Teilen Sie die aktiven Benutzer jeder Periode durch die ursprüngliche Größe der Kohorte.

Eine Window-Funktion übernimmt die Größe von Periode 0 in jede Zeile und ermöglicht so die Berechnung des Verhältnisses.

SELECT cohort, period,
       active * 1.0 / FIRST_VALUE(active) OVER (
         PARTITION BY cohort ORDER BY period
       ) AS retention
FROM cohort_activity;

Was ist ein Funnel?

Ein Funnel verfolgt Benutzer durch aufeinanderfolgende Schritte: Besuch, Registrierung, Hinzufügen zum Warenkorb, Kauf. Der Rückgang zwischen den Schritten zeigt, an welcher Stelle Sie Benutzer verlieren.

Funnel-Analysen verwandeln vage Beschwerden über „niedrige Conversion“ in eine konkrete undichte Funnel-Stufe.

SELECT step, COUNT(DISTINCT user_id) AS users
FROM events
WHERE step IN ('visit', 'signup', 'cart', 'purchase')
GROUP BY step;

Jeden Schritt zählen

Bedingte Aggregation zählt Benutzer für jede Funnel-Stufe in einem einzigen Durchlauf. FILTER zählt die unterschiedlichen Benutzer, die jede Stufe erreicht haben.

Eine Abfrage, eine Zeile, der gesamte Funnel auf einen Blick.

SELECT
  COUNT(DISTINCT user_id) FILTER (WHERE step = 'visit') AS visits,
  COUNT(DISTINCT user_id) FILTER (WHERE step = 'signup') AS signups,
  COUNT(DISTINCT user_id) FILTER (WHERE step = 'purchase') AS buyers
FROM events;

Conversion-Raten der Schritte

Die aussagekräftige Zahl ist das Verhältnis zwischen aufeinanderfolgenden Schritten. Wenn Sie jeden Schritt durch den vorherigen teilen, wird der größte Rückgang sichtbar.

Wandeln Sie den Wert in eine Dezimalzahl um, damit Ganzzahldivision Ihre Rate nicht auf null abrundet.

SELECT signups * 1.0 / NULLIF(visits, 0) AS visit_to_signup,
       buyers * 1.0 / NULLIF(signups, 0) AS signup_to_buy
FROM funnel_counts;

Geordnete Funnels mit Zeitstempeln

Ein strikter Funnel erfordert, dass die Schritte in der richtigen Reihenfolge stattfinden. Verknüpfen Sie jeden Schritt nur dann mit dem nächsten, wenn sein Zeitstempel später liegt.

So verhindern Sie, dass ein Kauf gezählt wird, der vor der zugehörigen Registrierung stattgefunden hat.

SELECT COUNT(DISTINCT v.user_id) AS visited,
       COUNT(DISTINCT p.user_id) AS purchased
FROM events v
LEFT JOIN events p
  ON p.user_id = v.user_id
  AND p.step = 'purchase'
  AND p.event_time > v.event_time
WHERE v.step = 'visit';

Funnel nach Kanal

Wenn Sie den Funnel nach Akquisekanal aufteilen, sehen Sie, welche Quellen tatsächlich konvertierende Benutzer liefern und nicht nur Klicks erzeugen.

Gruppieren Sie die bedingten Zählungen nach Kanal, um die Qualität der Quellen zu vergleichen.

SELECT channel,
       COUNT(DISTINCT user_id) FILTER (WHERE step = 'visit') AS visits,
       COUNT(DISTINCT user_id) FILTER (WHERE step = 'purchase') AS buyers
FROM events
GROUP BY channel;

Von Erkenntnissen zu Maßnahmen

Kohorten zeigen Ihnen, ob sich die Retention von Release zu Release verbessert; Funnels zeigen, welche Stufe Sie zuerst optimieren sollten.

Zusammen verlagern sie den Schwerpunkt im Marketing von der Berichterstattung darüber, was passiert ist, zur Diagnose, warum es passiert ist und wo als Nächstes investiert werden sollte.

Kurztest

Ihr Funnel zählt Benutzer bei Besuch, Registrierung und Kauf. Was zeigen die Verhältnisse aufeinanderfolgender Schritte?

Zusammenfassung

Kohorten gruppieren Benutzer nach Startmonat und verfolgen die Retention über mehrere Perioden hinweg, wobei DATE_TRUNC und AGE verwendet werden. Funnels zählen die unterschiedlichen Benutzer pro geordnetem Schritt und vergleichen die Verhältnisse aufeinanderfolgender Schritte.

Sie verfügen nun über das fortgeschrittene Werkzeug, um Retention und Conversion vollständig in SQL zu analysieren.

Häufig gestellte Fragen

Ist die Lektion „Kohorten- und Trichterabfragen“ kostenlos?

Ja — der vollständige Text von „Kohorten- und Trichterabfragen“ ist hier im Web kostenlos zu lesen. Um sie interaktiv zu üben (integrierter Code-Editor und 24/7 KI-Tutor) und den Rest des Digital Marketing Academy-Kurses freizuschalten, upgrade auf CoddyKit PRO. Der Digital Marketing Academy-Kurs umfasst insgesamt 4 Lektionen.

Was lerne ich in „Kohorten- und Trichterabfragen“?

Beantworten Sie Fragen zur Kundenbindung. Du übst Digital Marketing Academy mit praktischem Code, den du direkt im Browser ausführst, und ein 24/7 KI-Tutor beantwortet deine Fragen während du die Lektion bearbeitest.

Brauche ich Erfahrung, um Digital Marketing Academy zu starten?

Keine Vorkenntnisse erforderlich. Digital Marketing Academy auf CoddyKit ist für Anfänger bis fortgeschrittene Lernende strukturiert, sodass du hier starten oder von Anfang an beginnen und in deinem eigenen Tempo voranschreiten kannst. Dies ist Lektion 4 von 4.

Wie lange dauert die Lektion „Kohorten- und Trichterabfragen“?

Die meisten CoddyKit-Lektionen dauern etwa 5–10 Minuten. Jede ist kompakt und interaktiv, sodass du stetig Fortschritte machst und genau dort weitermachst, wo du aufgehört hast – im Web und in der App.

Kann ich in dieser Digital Marketing Academy-Lektion Code schreiben und ausführen?

Ja. Jede Digital Marketing Academy-Lektion enthält einen integrierten Code-Editor, sodass du echten Code direkt in deinem Browser schreibst und ausführst und sofort KI-Feedback erhältst — ohne lokale Einrichtung erforderlich.

Alle Lektionen in diesem Kurs

← Zurück zu Digital Marketing Academy