Cohort-Tabellenstruktur: Retention-Analysen in der Production skalieren

Retention-Analysen sind eine der mächtigsten Methoden, um Nutzerverhalten zu verstehen. Aber im echten Maßstab — Millionen von Events pro Tag, Hunderttausende von Nutzern — scheitern naive SQL-Abfragen nach 30 Sekunden oder erschöpfen die Slot-Kapazität. Production-ready Cohort-Analysen erfordern es, die Tabellenstruktur für die Query-Engine zu optimieren. In diesem Artikel zeigen wir dir, wie du Cohort-Tabellen in BigQuery mit Materialized Views, Partitionierung und inkrementellen Refresh-Strategien skalierst.

Warum naive Cohort-Abfragen scheitern

Klassische Cohort-Analysen funktionieren nach dieser Logik: Finde das erste Aktivitätsdatum des Nutzers (cohort_date), berechne alle nachfolgenden Aktivitäten als „N. Tag" relativ zu diesem Datum, aggregiere die Retention-Rate nach Gruppe. Das folgende SQL ist logisch korrekt, funktioniert aber nicht in der Production:

WITH first_event AS (
  SELECT user_id, MIN(DATE(event_timestamp)) AS cohort_date
  FROM `project.dataset.events`
  GROUP BY user_id
),
daily_activity AS (
  SELECT e.user_id, DATE(e.event_timestamp) AS activity_date
  FROM `project.dataset.events` e
  GROUP BY 1,2
)
SELECT 
  f.cohort_date,
  DATE_DIFF(d.activity_date, f.cohort_date, DAY) AS day_n,
  COUNT(DISTINCT d.user_id) AS retained_users
FROM first_event f
JOIN daily_activity d USING(user_id)
GROUP BY 1,2
ORDER BY 1,2;

Zwei große Probleme: (1) Die events-Tabelle wird jedes Mal vollständig gescannt — es gibt keine Partition Pruning, (2) für jedes cohort_date werden alle Nutzer und alle ihre Aktivitäten gejoined — Gefahr der kartesischen Explosion. Bei 100M Events bearbeitet diese Query 400GB Daten und dauert 2 Minuten. Für tägliche Refreshes ist das nicht nachhaltig. Die BigQuery-Rechnung vervielfacht sich zum Monatsende um das 10-Fache.

Partitionierte Basis-Tabelle reduziert Scanning-Overhead

Der erste Schritt: Partitioniere die events-Tabelle nach DATE(event_timestamp). Das ermöglicht es dem Query-Planer, nur relevante Partitionen zu scannen, wenn der Query WHERE DATE(event_timestamp) BETWEEN X AND Y enthält:

CREATE TABLE `project.dataset.events`
PARTITION BY DATE(event_timestamp)
CLUSTER BY user_id, event_name
AS SELECT * FROM ...;

Clustering nach (user_id, event_name) stellt sicher, dass Events desselben Nutzers physisch in benachbarten Blöcken gespeichert sind — das Join-Performance verbessert sich um 30–50%. Aber das reicht nicht; die Cohort-Berechnungslogik wird mit jeder Abfrage erneut ausgeführt. Hier kommen Materialized Views ins Spiel.

Materialized View: Inkrementelle Cohort-Tabelle

BigQuery Materialized Views speichern Query-Ergebnisse physisch und aktualisieren sie automatisch, wenn sich die Basis-Tabelle ändert. Für Cohort-Analysen verwenden wir diese Struktur:

CREATE MATERIALIZED VIEW `project.dataset.user_cohorts`
PARTITION BY cohort_date
CLUSTER BY user_id
AS
SELECT 
  user_id,
  MIN(DATE(event_timestamp)) AS cohort_date,
  COUNT(*) AS first_day_events
FROM `project.dataset.events`
GROUP BY user_id;

Diese View berechnet das erste Datum, an dem jeder Nutzer auftritt (cohort_date), genau einmal und speichert es. Wenn neue Events ankommen, verarbeitet BigQuery nur das Delta — kein vollständiges Rescan. Durch PARTITION BY cohort_date wird Partition Pruning in Retention-Queries möglich, wie WHERE cohort_date = '2026-05-01'.

Die Retention-Berechnungsabfrage reduziert sich auf:

SELECT 
  c.cohort_date,
  DATE_DIFF(DATE(e.event_timestamp), c.cohort_date, DAY) AS day_n,
  COUNT(DISTINCT e.user_id) AS retained_users
FROM `project.dataset.user_cohorts` c
JOIN `project.dataset.events` e 
  ON c.user_id = e.user_id 
  AND DATE(e.event_timestamp) >= c.cohort_date
WHERE c.cohort_date BETWEEN '2026-05-01' AND '2026-05-15'
GROUP BY 1,2;

Diese Query joined die Materialized View statt der Basis-Tabelle — die Anzahl der gescannten Zeilen fällt von Millionen auf Tausende. Aber der Query scannt immer noch die vollständige Events-Tabelle. Im nächsten Schritt erstellen wir eine pre-aggregierte Retention-Tabelle.

Pre-Aggregierte Retention-Tabelle: Die letzte Schicht

Cohort-Analysen werden typischerweise auf festgelegten Intervallen betrachtet — „Day 0, Day 1, Day 7, Day 30" — nicht für jeden Tag einzeln. Mit dbt implementieren wir diese Logik:

Hole täglich neue Cohorts aus der user_cohorts View
Berechne für jede Cohort die Retention der letzten 30 Tage (nach den ersten 30 Tagen ändert sich das nicht mehr)
Schreibe das Ergebnis inkrementell in die cohort_retention_summary-Tabelle

dbt-Modell:

{{
  config(
    materialized='incremental',
    unique_key=['cohort_date','day_n'],
    partition_by={'field':'cohort_date','data_type':'date'},
    cluster_by=['day_n']
  )
}}

WITH cohorts_to_update AS (
  SELECT DISTINCT cohort_date 
  FROM {{ ref('user_cohorts') }}
  WHERE cohort_date >= CURRENT_DATE() - 31
  {% if is_incremental() %}
    AND cohort_date > (SELECT MAX(cohort_date) FROM {{ this }})
  {% endif %}
),
retention_calc AS (
  SELECT 
    c.cohort_date,
    DATE_DIFF(DATE(e.event_timestamp), c.cohort_date, DAY) AS day_n,
    COUNT(DISTINCT e.user_id) AS retained_users,
    MAX(c.first_day_events) AS cohort_size
  FROM {{ ref('user_cohorts') }} c
  JOIN {{ source('raw','events') }} e 
    ON c.user_id = e.user_id
  WHERE c.cohort_date IN (SELECT cohort_date FROM cohorts_to_update)
    AND DATE(e.event_timestamp) >= c.cohort_date
    AND DATE_DIFF(DATE(e.event_timestamp), c.cohort_date, DAY) <= 30
  GROUP BY 1,2
)
SELECT 
  cohort_date,
  day_n,
  retained_users,
  cohort_size,
  SAFE_DIVIDE(retained_users, cohort_size) AS retention_rate
FROM retention_calc;

Dieses Modell aktualisiert täglich nur die letzten 31 Tage an Cohorts. Cohorts älter als 31 Tage haben stabile Retention — sie werden nicht neu berechnet. Der Slot-Verbrauch sinkt um 95%. In CDP & Retention Engineering wird diese Tabelle direkt an Dashboards angebunden — BI-Tools (Looker, Metabase) liefern Ergebnisse in 100ms.

Query-Kosten und Partition-Ablauf-Strategien

In BigQuery ist Storage billig ($0,02/GB/Monat), Computing teuer ($5/TB gescannte Daten). Retention-Analysen sind retrospektiv, also werden alte Partitionen häufig gescannt. Zwei Optimierungen:

Partition Expiration: Lösche automatisch Partitionen aus der events-Tabelle, die älter als 90 Tage sind — nach der Cohort-Berechnung wird Raw-Event-Daten nicht mehr benötigt.
Clustering-Statistiken regelmäßig aktualisieren: ANALYZE TABLE ... UPDATE STATISTICS — der Query-Optimizer wählt bessere Execution Plans.

Beispiel: Kostenvergleich (100M Events/Tag, 1M Nutzer):

Methode	Verarbeitete Daten/Tag	Monatliche Compute-Kosten
Naive Query (Full Scan)	12TB	$600
Partitioniert + Materialized View	800GB	$40
Pre-aggregierte Tabelle (inkrementell)	50GB	$2,50

Eine pre-aggregierte Schicht reduziert die Compute-Kosten um den Faktor 240. In der Production ist dieser Unterschied kritisch — besonders wenn Retention-Analysen stündlich refreshed werden.

Echtzeit-Cohort-Analysen und Latenz-Tradeoffs

Materialized Views und pre-aggregierte Strukturen führen zu Latenz-Tradeoffs: Daten verspäten sich um 1–5 Minuten. Wenn Echtzeit-Cohort-Analysen erforderlich sind (z.B. für die ersten 24 Stunden), kannst du einen hybriden Ansatz verwenden:

Letzte 24 Stunden: Streaming Insert + Real-Time Query (Cache deaktiviert)
Daten älter als 24 Stunden: Pre-aggregierte Tabelle

BI-Queries verbinden beide Quellen mit UNION ALL:

SELECT * FROM cohort_retention_summary WHERE cohort_date < CURRENT_DATE()
UNION ALL
SELECT * FROM realtime_cohort_view WHERE cohort_date = CURRENT_DATE();

Die Real-Time View ist zwar teuer, läuft aber nur für die aktuelle Cohort — der gesamte Compute-Overhead bleibt begrenzt.

Cohort-Segmentierung und Kardinalitäts-Explosion

Das Aufschlüsseln der Retention nach Nutzer-Segmenten (Plattform, Land, Akquisitionskanal) kann zu Kardinalitätsproblemen führen. Beispiel: 5 Segmente × 30 Tage × 365 Cohorts = 54.750 eindeutige Zeilen. In diesem Fall:

Segment-Anzahl begrenzen: Analysiere nur die 3–5 wichtigsten Segmente. Für andere erstelle separate Tabellen.
Dynamische Segmentierung: Statt Segment-Info in der pre-aggregierten Tabelle zu speichern, nutze Join-Time Filtering — das bewahrt Query-Flexibilität, erhöht aber Slot-Nutzung.
Rollup-Tabelle: Erstelle eine separate Tabelle für wöchentliche Cohorts (weekly_cohort_retention) — das senkt die Kardinalität um 85%.

In Roibase's Veri Analizi & İçgörü Mühendisliği kombinieren wir die Segment-Strategie mit Acquisition Source Attribution — Cohort-Analysen werden direkt an Kanal-Performance gekoppelt.

Monitoring und Regression-Detection

Um Cohort-Pipelines in der Production zu überwachen, verfolge diese Metriken:

Query Slot Time: BigQuery Slot-Nutzung des täglichen Refreshs — plötzliche Anstiege deuten auf Kardinalitäts-Explosion oder Partition Pruning-Verlust hin.
Row Count Delta: Anzahl der Zeilen, die bei jedem Refresh hinzugefügt werden — mehr als erwartet bedeutet mögliches Data-Quality-Problem.
Retention Rate Stddev: Plötzliche Sprünge von >10% in Day 1 Retention sind ein Signal für Datenqualitäts-Probleme.

Du kannst diese Checks als Tests in dbt integrieren:

tests:
  - dbt_utils.expression_is_true:
      expression: "retention_rate BETWEEN 0 AND 1"
  - dbt_utils.recency:
      datepart: day
      field: cohort_date
      interval: 1

Wenn Tests fehlschlagen, wird ein Slack- oder PagerDuty-Alert ausgelöst — keine manuelle Überprüfung erforderlich.

Cohort-Tabellenarchitektur hebt Retention-Analysen von „Ad-Hoc-Queries" auf die Stufe „Production Data Product". Materialized Views mit inkrementellen Refreshes, Partitionierung mit Query Pruning, Pre-Aggregation mit Slot-Optimierung — jede Schicht reduziert die Kosten um den Faktor 10. Retention-Analysen über Millionen von Nutzern und Milliarden von Events reduzieren sich auf 100ms Dashboard-Queries. Welche Retention-Patterns du überwachen möchtest, entscheidest du — aber die Daten in dieser Geschwindigkeit zu verarbeiten ist kein Engineering-Problem mehr.