SaaS-Kennzahlen in PostgreSQL: SQL-Abfragen fuer MRR, ARR, Churn und Kohorten-Retention

Wer nach SaaS-Analytics sucht, findet vor allem Tool-Empfehlungen: ChartMogul, Baremetrics oder ein Data Warehouse mit dbt und einer BI-Schicht obendrauf. Diese Tools haben ihren Platz. Aber wenn deine Subscription-Daten bereits in PostgreSQL liegen, kannst du deine SaaS-Kennzahlen direkt in PostgreSQL berechnen, mit SQL, das du kontrollierst, und ohne zusaetzliche Infrastruktur. Wir haben das in mehreren Kundenprojekten als erste Analytics-Schicht aufgesetzt, und sie traegt meist bis weit ueber den Product-Market-Fit hinaus. Dieser Beitrag geht die Queries durch: MRR mit Proration-Handling, ARR, Logo- und Revenue-Churn, Net Revenue Retention, Kohorten-Retention und LTV zu CAC, plus die Details, die jede dieser Zahlen still verzerren.

Warum SaaS-Kennzahlen in PostgreSQL am Anfang ein Tool schlagen

Ein dediziertes Analytics-Tool liefert Dashboards ab Tag eins. Es liefert auch eine zweite Kopie deiner Umsatzdaten und einen weiteren Sync-Job, der still driften kann. Jede Zahl, die das Tool zeigt, wurde aus Events berechnet, die deine eigene Datenbank ohnehin speichert. Bis deine Reporting-Anforderungen kompliziert werden, ist der kuerzeste Weg also, die Quelle direkt abzufragen.

Die Queries selbst zu schreiben hat einen zweiten Vorteil: Es zwingt dich, deine Kennzahlen praezise zu definieren. Zaehlt eine pausierte Subscription als Churn? Ist ein Downgrade auf den Free-Plan Churn oder Contraction? Ein Tool trifft diese Entscheidungen fuer dich, und seine Definitionen passen womoeglich nicht dazu, wie deine Investoren die Zahlen lesen. SQL macht jede Definition explizit, und jeder im Team kann sie reviewen.

Das Datenmodell, das die Queries voraussetzen

Alles Folgende laeuft gegen eine einzige Subscription-Events-Tabelle:

create table subscription_events (
  id          bigint generated always as identity primary key,
  customer_id uuid not null,
  event_type  text not null, -- created, upgraded, downgraded, canceled, reactivated
  plan_id     text not null,
  mrr_cents   integer not null, -- the customer's monthly value after this event
  currency    text not null default 'EUR',
  occurred_at timestamptz not null
);

create index on subscription_events (customer_id, occurred_at);

Jede Zeile haelt den Zustand nach dem Event fest, nicht das Delta. Der MRR eines Kunden zu einem beliebigen Zeitpunkt ist der mrr_cents-Wert seines juengsten Events vor diesem Zeitpunkt, was jede Point-in-Time-Query einfach haelt. Wenn du heute nur eine Tabelle mit dem aktuellen Subscription-Zustand hast, fang jetzt an, Events zu schreiben. Historie, die du nie aufgezeichnet hast, laesst sich nicht rekonstruieren.

Fast jede Kennzahl braucht dasselbe Zwischenergebnis, den MRR jedes Kunden zu jedem Monatsende. Es lohnt sich also, das einmal als View zu definieren:

create view customer_month_mrr as
select
  m.month_start,
  e.customer_id,
  case
    when (array_agg(e.event_type order by e.occurred_at desc))[1] = 'canceled' then 0
    else (array_agg(e.mrr_cents order by e.occurred_at desc))[1]
  end as mrr_cents
from generate_series(
       date_trunc('month', now()) - interval '23 months',
       date_trunc('month', now()),
       interval '1 month'
     ) as m(month_start)
join subscription_events e
  on e.occurred_at < m.month_start + interval '1 month'
group by m.month_start, e.customer_id;

Der Trick mit array_agg(... order by occurred_at desc) waehlt das juengste Event pro Kunde und Monat ohne Window Function, und gekuendigte Kunden tragen ab ihrem Kuendigungsmonat einen MRR von null.

Monthly Recurring Revenue

Mit der View ist MRR kurz:

select month_start::date as month, sum(mrr_cents) / 100.0 as mrr
from customer_month_mrr
group by month_start
order by month_start;

Die Proration-Frage kommt in jedem Projekt auf. Ein Kunde upgraded am 20. von 50 auf 90 Euro, was ist sein MRR in dem Monat? Fuer MRR lautet die Antwort 90. MRR ist ein Schnappschuss der wiederkehrenden Run-Rate zu einem Zeitpunkt, keine Revenue-Recognition-Zahl. Die anteilige Rechnung zur Monatsmitte gehoert also in dein Billing-System, nicht hierher. Der verwandte Fehler, den wir am haeufigsten sehen, ist, Rechnungsbetraege zu summieren und das Ergebnis MRR zu nennen. Einmalige Setup-Gebuehren und jaehrliche Vorauszahlungen lassen diese Zahl springen und ueberzeichnen die Run-Rate.

ARR, und welchen ARR du meinst

Fuer Produkte mit monatlicher Abrechnung ist ARR MRR mal zwoelf. Berichte ihn so und sag es dazu. Wenn du Jahresvertraege verkaufst, gibt es eine zweite Zahl: contracted ARR, die Summe der aktiven Vertragswerte. Die beiden laufen auseinander, wenn der monatliche Churn hoch ist, denn Run-Rate-ARR unterstellt, dass der heutige MRR die naechsten zwoelf Monate ueberlebt. Investoren werden fragen, welchen du zitierst. Berechne also beide, wenn es beide in deinem Geschaeft gibt.

Churn: Logo versus Revenue

Logo-Churn zaehlt die Kunden, die du verloren hast. Revenue-Churn zaehlt den MRR, den sie mitgenommen haben. Du brauchst beide, denn zehn Kunden mit 20 Euro im Monat zu verlieren ist ein voellig anderes Ereignis als einen mit 2.000 zu verlieren.

select
  curr.month_start::date as month,
  round(100.0 * count(*) filter (where prev.mrr_cents > 0 and curr.mrr_cents = 0)
    / nullif(count(*) filter (where prev.mrr_cents > 0), 0), 2) as logo_churn_pct,
  round(100.0 * sum(prev.mrr_cents - curr.mrr_cents)
      filter (where prev.mrr_cents > 0 and curr.mrr_cents = 0)
    / nullif(sum(prev.mrr_cents) filter (where prev.mrr_cents > 0), 0), 2) as revenue_churn_pct
from customer_month_mrr curr
join customer_month_mrr prev
  on prev.customer_id = curr.customer_id
 and prev.month_start = curr.month_start - interval '1 month'
group by curr.month_start
order by curr.month_start;

Entscheide einmal, ob ein Downgrade auf einen Free-Plan als Churn zaehlt, und kodiere die Entscheidung hier. Egal wie du dich entscheidest, das SQL ist die Dokumentation.

Net Revenue Retention

NRR beantwortet eine Frage, die Churn allein nicht beantworten kann: Wie hat sich fuer die Kunden, die wir vor einem Monat schon hatten, der kombinierte MRR entwickelt, inklusive Upgrades und Downgrades?

select
  curr.month_start::date as month,
  round(100.0 * sum(curr.mrr_cents) / nullif(sum(prev.mrr_cents), 0), 1) as nrr_pct
from customer_month_mrr curr
join customer_month_mrr prev
  on prev.customer_id = curr.customer_id
 and prev.month_start = curr.month_start - interval '1 month'
where prev.mrr_cents > 0
group by curr.month_start
order by curr.month_start;

Neukunden fallen durch den Join auf den Vormonat heraus, und genau das ist der Punkt: NRR misst, was mit bestehendem Umsatz passiert. Ueber 100 Prozent ueberwiegt Expansion den Churn und die Contraction, und das Geschaeft waechst selbst ohne jeden Neuverkauf. Fuer B2B-SaaS ist das eine der ersten Zahlen, die ein Investor prueft.

Kohorten-Retention

Retention nach Signup-Kohorte zeigt, ob das Produkt Kunden mit der Zeit besser haelt, was die aggregierte Churn-Rate verbirgt.

with cohorts as (
  select customer_id, date_trunc('month', min(occurred_at)) as cohort_month
  from subscription_events
  group by customer_id
)
select
  c.cohort_month::date as cohort,
  (extract(year from age(cm.month_start, c.cohort_month)) * 12
   + extract(month from age(cm.month_start, c.cohort_month)))::int as month_offset,
  count(*) filter (where cm.mrr_cents > 0) as active_customers
from cohorts c
join customer_month_mrr cm using (customer_id)
where cm.month_start >= c.cohort_month
group by 1, 2
order by 1, 2;

Jede Zeile ist eine Zelle der klassischen Kohorten-Heatmap: welche Kohorte, wie viele Monate dabei, und wie viele Kunden noch zahlen. Teile durch die Monat-null-Anzahl jeder Kohorte, um Prozentwerte zu bekommen.

LTV und CAC

Der Customer Lifetime Value kommt aus einer Formel statt aus einer grossen Query: durchschnittlicher Umsatz pro Account, mal Bruttomarge, geteilt durch monatlichen Revenue-Churn. Die ersten beiden kommen aus deiner Buchhaltung, der dritte aus der Churn-Query oben. Behandle das Ergebnis mit Misstrauen, bis du mindestens ein Jahr Churn-Historie hast, denn fruehe Churn-Raten sind verrauscht.

CAC braucht eine kleine Zusatztabelle mit monatlichen Marketing- und Vertriebsausgaben:

select
  date_trunc('month', s.spent_on)::date as month,
  round(sum(s.amount_cents) / 100.0
    / nullif(count(distinct e.customer_id)
        filter (where e.event_type = 'created'), 0), 2) as cac
from marketing_spend s
left join subscription_events e
  on date_trunc('month', e.occurred_at) = date_trunc('month', s.spent_on)
group by 1
order by 1;

Ein LTV-zu-CAC-Verhaeltnis um 3 ist die Zahl, die fuer B2B-SaaS ueblicherweise als gesund gilt. Der Trend zaehlt mehr als der Zielwert, und ebenso die Ehrlichkeit bei den Eingaben, insbesondere Vertriebsgehaelter als Teil der Ausgaben zu zaehlen.

Die Fallstricke, die jede der Zahlen oben verzerren

Zuerst Zeitzonen. date_trunc('month', occurred_at) schneidet in der Session-Zeitzone ab, dieselbe Query liefert also unterschiedliche Monatsgrenzen fuer eine Verbindung auf UTC und eine auf Europe/Berlin. Pinne sie explizit mit occurred_at at time zone 'Europe/Berlin' und nutze dieselbe Zone, in der dein Billing laeuft.

Dann Trials. Ein Kunde im Trial mit mrr_cents = 0 faellt von allein aus jeder Umsatzkennzahl heraus, und genau das willst du. Der Fehler ist, waehrend des Trials den kuenftigen Planpreis zu erfassen. Erfasse null, bis Geld zugesagt ist, sonst enthaelt dein MRR Umsatz, den es nicht gibt, und dein Churn schnellt hoch, sobald eine Charge Trials auslaeuft.

Drittens Waehrungen. Speichere Betraege in Cents mit expliziter Waehrungsspalte und summiere in einer Kennzahlen-Query niemals ueber Waehrungen hinweg. Wenn du in mehr als einer Waehrung abrechnest, ergaenze eine monatliche Wechselkurstabelle und normalisiere bei der Aggregation auf deine Berichtswaehrung. Halte die Kurse monatlich und fix, sonst bewegt sich der berichtete MRR, ohne dass irgendein Kunde etwas tut.

Die Zahlen auf ein Grafana-Dashboard bringen

Alle diese Queries lassen sich unveraendert in Grafanas PostgreSQL-Data-Source einsetzen. MRR und NRR funktionieren als Zeitreihen-Panels mit month als Zeitspalte. Aktueller MRR und Logo-Churn passen in Stat-Panels mit der juengsten Zeile. Fuer die Kohorten-Query nimm ein Tabellen-Panel mit Farbskala auf active_customers oder das Heatmap-Panel mit der Kohorte auf einer Achse und month_offset auf der anderen. Stelle den Dashboard-Refresh auf etwas Langsames wie sechs Stunden. Das sind Monatskennzahlen, und jede Query scannt die volle Events-Tabelle.

Wenn die Dashboard-Queries anfangen wehzutun, nimm das ernst. In Code-Quality- und Performance-Audits finden wir regelmaessig Analytics-Queries, die auf der Produktions-Primary laufen und mit dem Checkout-Traffic konkurrieren. Eine Read-Replica loest das fuer Jahre.

Wann das nicht mehr reicht

SaaS-Kennzahlen so in PostgreSQL zu berechnen ist die minimal tragfaehige Analytics-Schicht, und fuer viele Teams haelt sie Jahre. Du waechst heraus, wenn Finance auditierbare Revenue Recognition braucht, wenn Marketing Attribution ueber Ad-Plattformen und Produkt-Events hinweg will oder wenn Leute ausserhalb des Engineerings eigene Reports bauen muessen. Dann ist die Antwort ein Warehouse mit BI-Tool obendrauf, und das SQL oben laesst sich fast unveraendert portieren. Wann sich diese Investition lohnt, ist die Art von Frage, die wir in Tech-Stack-Strategie-Engagements durcharbeiten, und wenn du das Subscription-System selbst baust, beschreibt unsere Seite zu Custom Software Development, wie wir das angehen.

Wenn du ein zweites Paar Augen fuer deine eigenen Kennzahlen-Queries willst, oder die Zahlen daraus seltsam aussehen und du nicht sagen kannst warum, schreib an hello@wolf-tech.io. Mehr dazu, wie wir arbeiten, findest du auf wolf-tech.io.