tsb — Subscription Revenue Waterfall

Skills you'll use: merge, derived KPI columns, account snapshots, groupby().agg(), cohort calculations, pivotTableFull, and ranked retention tables.

1 · Join invoices to account metadata

Combine billing facts with customer segmentation, then summarize current-month MRR by segment.

TypeScript

import { DataFrame, merge } from "tsb";

const accounts = DataFrame.fromRecords([
  { acct_id: "a01", customer: "Northwind Analytics", segment: "enterprise", region: "NA", signup_month: "2023-11" },
  { acct_id: "a02", customer: "Brightline Labs", segment: "startup", region: "EU", signup_month: "2023-12" },
  { acct_id: "a03", customer: "Urban Cart", segment: "midmarket", region: "NA", signup_month: "2024-01" },
  { acct_id: "a04", customer: "Helio Health", segment: "enterprise", region: "EU", signup_month: "2024-01" },
  { acct_id: "a05", customer: "Beacon Studio", segment: "startup", region: "NA", signup_month: "2024-02" },
]);
const invoices = DataFrame.fromRecords([
  { month: "2024-03", acct_id: "a01", plan: "scale", seats: 120, mrr: 1440, status: "active" },
  { month: "2024-03", acct_id: "a02", plan: "team", seats: 0, mrr: 0, status: "churned" },
  { month: "2024-03", acct_id: "a03", plan: "growth", seats: 42, mrr: 756, status: "active" },
  { month: "2024-03", acct_id: "a04", plan: "scale", seats: 95, mrr: 1330, status: "active" },
  { month: "2024-03", acct_id: "a05", plan: "team", seats: 18, mrr: 270, status: "active" },
]);

const current = merge(invoices, accounts, { on: "acct_id", how: "left" }).assign({
  arr: (df) => df.col("mrr").mul(12),
  mrr_per_seat: (df) => df.col("mrr").div(df.col("seats")),
});

console.log("Current customer ledger:");
console.log(current.select(["acct_id", "customer", "segment", "region", "status", "mrr", "arr", "mrr_per_seat"]).toString());

const segmentSummary = current
  .groupby("segment")
  .agg({ mrr: "sum", arr: "sum", seats: "sum" }, false)
  .sortValues("mrr", false);
console.log("\nMRR by segment:");
console.log(segmentSummary.toString());

import pandas as pd

accounts = pd.DataFrame([
    {"acct_id": "a01", "customer": "Northwind Analytics", "segment": "enterprise", "region": "NA", "signup_month": "2023-11"},
    {"acct_id": "a02", "customer": "Brightline Labs", "segment": "startup", "region": "EU", "signup_month": "2023-12"},
    {"acct_id": "a03", "customer": "Urban Cart", "segment": "midmarket", "region": "NA", "signup_month": "2024-01"},
    {"acct_id": "a04", "customer": "Helio Health", "segment": "enterprise", "region": "EU", "signup_month": "2024-01"},
    {"acct_id": "a05", "customer": "Beacon Studio", "segment": "startup", "region": "NA", "signup_month": "2024-02"},
])
invoices = pd.DataFrame([
    {"month": "2024-03", "acct_id": "a01", "plan": "scale", "seats": 120, "mrr": 1440, "status": "active"},
    {"month": "2024-03", "acct_id": "a02", "plan": "team", "seats": 0, "mrr": 0, "status": "churned"},
    {"month": "2024-03", "acct_id": "a03", "plan": "growth", "seats": 42, "mrr": 756, "status": "active"},
    {"month": "2024-03", "acct_id": "a04", "plan": "scale", "seats": 95, "mrr": 1330, "status": "active"},
    {"month": "2024-03", "acct_id": "a05", "plan": "team", "seats": 18, "mrr": 270, "status": "active"},
])
current = invoices.merge(accounts, on="acct_id", how="left")
current["arr"] = current["mrr"] * 12
current["mrr_per_seat"] = current["mrr"] / current["seats"]
print(current[["acct_id", "customer", "segment", "region", "status", "mrr", "arr", "mrr_per_seat"]])
print(current.groupby("segment", as_index=False).agg({"mrr": "sum", "arr": "sum", "seats": "sum"}).sort_values("mrr", ascending=False))

Click ▶ Run to execute

Ctrl+Enter to run · Tab to indent

2 · Build the monthly MRR movement waterfall

Compare each account to its previous snapshot and classify revenue movement.

TypeScript

import { DataFrame, pivotTableFull } from "tsb";

const snapshots = [
  { month: "2024-01", acct_id: "a01", mrr: 1200 },
  { month: "2024-01", acct_id: "a02", mrr: 450 },
  { month: "2024-01", acct_id: "a03", mrr: 560 },
  { month: "2024-01", acct_id: "a04", mrr: 1100 },
  { month: "2024-02", acct_id: "a01", mrr: 1440 },
  { month: "2024-02", acct_id: "a02", mrr: 450 },
  { month: "2024-02", acct_id: "a03", mrr: 700 },
  { month: "2024-02", acct_id: "a04", mrr: 1045 },
  { month: "2024-02", acct_id: "a05", mrr: 240 },
  { month: "2024-03", acct_id: "a01", mrr: 1440 },
  { month: "2024-03", acct_id: "a02", mrr: 0 },
  { month: "2024-03", acct_id: "a03", mrr: 756 },
  { month: "2024-03", acct_id: "a04", mrr: 1330 },
  { month: "2024-03", acct_id: "a05", mrr: 270 },
];

const previous = new Map<string, number>();
const rows = snapshots.map((row) => {
  const prior = previous.get(row.acct_id) ?? 0;
  previous.set(row.acct_id, row.mrr);
  const delta = row.mrr - prior;
  let movement = "retained";
  if (prior === 0 && row.mrr > 0) movement = "new";
  else if (prior > 0 && row.mrr === 0) movement = "churn";
  else if (delta > 0) movement = "expansion";
  else if (delta < 0) movement = "contraction";
  return { ...row, prior_mrr: prior, delta_mrr: delta, movement };
});

const movements = DataFrame.fromRecords(rows);
console.log(movements.select(["month", "acct_id", "prior_mrr", "mrr", "delta_mrr", "movement"]).toString());

const waterfall = movements
  .groupby("movement")
  .agg({ delta_mrr: "sum", mrr: "sum" }, false)
  .sortValues("delta_mrr", false);
console.log("\nMovement totals:");
console.log(waterfall.toString());

const byMonth = pivotTableFull(movements, {
  index: "month",
  columns: "movement",
  values: "delta_mrr",
  aggfunc: "sum",
  fill_value: 0,
  margins: true,
});
console.log("\nMonthly MRR waterfall:");
console.log(byMonth.toString());

import numpy as np
import pandas as pd

snapshots = pd.DataFrame([
    {"month": "2024-01", "acct_id": "a01", "mrr": 1200},
    {"month": "2024-01", "acct_id": "a02", "mrr": 450},
    {"month": "2024-01", "acct_id": "a03", "mrr": 560},
    {"month": "2024-01", "acct_id": "a04", "mrr": 1100},
    {"month": "2024-02", "acct_id": "a01", "mrr": 1440},
    {"month": "2024-02", "acct_id": "a02", "mrr": 450},
    {"month": "2024-02", "acct_id": "a03", "mrr": 700},
    {"month": "2024-02", "acct_id": "a04", "mrr": 1045},
    {"month": "2024-02", "acct_id": "a05", "mrr": 240},
    {"month": "2024-03", "acct_id": "a01", "mrr": 1440},
    {"month": "2024-03", "acct_id": "a02", "mrr": 0},
    {"month": "2024-03", "acct_id": "a03", "mrr": 756},
    {"month": "2024-03", "acct_id": "a04", "mrr": 1330},
    {"month": "2024-03", "acct_id": "a05", "mrr": 270},
]).sort_values(["acct_id", "month"])
snapshots["prior_mrr"] = snapshots.groupby("acct_id")["mrr"].shift(fill_value=0)
snapshots["delta_mrr"] = snapshots["mrr"] - snapshots["prior_mrr"]
snapshots["movement"] = np.select(
    [
        (snapshots["prior_mrr"] == 0) & (snapshots["mrr"] > 0),
        (snapshots["prior_mrr"] > 0) & (snapshots["mrr"] == 0),
        snapshots["delta_mrr"] > 0,
        snapshots["delta_mrr"] < 0,
    ],
    ["new", "churn", "expansion", "contraction"],
    default="retained",
)
print(snapshots[["month", "acct_id", "prior_mrr", "mrr", "delta_mrr", "movement"]])
print(snapshots.groupby("movement", as_index=False).agg({"delta_mrr": "sum", "mrr": "sum"}))
print(pd.pivot_table(snapshots, index="month", columns="movement", values="delta_mrr", aggfunc="sum", fill_value=0, margins=True))

Click ▶ Run to execute

Ctrl+Enter to run · Tab to indent

3 · Turn monthly activity into a retention matrix

Measure how much of each signup cohort remains active as it ages.

TypeScript

import { DataFrame, pivotTableFull } from "tsb";

const retention = DataFrame.fromRecords(retentionRows);
const matrix = pivotTableFull(retention, {
  index: "signup_month",
  columns: "cohort_age",
  values: "retention_pct",
  aggfunc: "mean",
  fill_value: 0,
});
console.log("Retention matrix (% active):");
console.log(matrix.toString());

const m2 = retention
  .filter(retention.col("cohort_age").eq("M2"))
  .sortValues("retention_pct", true);
console.log("\nMonth-2 retention watchlist:");
console.log(m2.select(["signup_month", "active_accounts", "cohort_size", "retention_pct"]).toString());

import pandas as pd

activity = pd.DataFrame([
    {"signup_month": "2024-01", "active_month": "2024-01", "active_accounts": 22},
    {"signup_month": "2024-01", "active_month": "2024-02", "active_accounts": 20},
    {"signup_month": "2024-01", "active_month": "2024-03", "active_accounts": 17},
    {"signup_month": "2024-02", "active_month": "2024-02", "active_accounts": 18},
    {"signup_month": "2024-02", "active_month": "2024-03", "active_accounts": 15},
    {"signup_month": "2024-02", "active_month": "2024-04", "active_accounts": 13},
    {"signup_month": "2024-03", "active_month": "2024-03", "active_accounts": 25},
    {"signup_month": "2024-03", "active_month": "2024-04", "active_accounts": 21},
    {"signup_month": "2024-03", "active_month": "2024-05", "active_accounts": 19},
])
def month_number(value: str) -> int:
    return int(value[:4]) * 12 + int(value[5:7])
activity["cohort_size"] = activity.groupby("signup_month")["active_accounts"].transform("max")
activity["cohort_age"] = activity.apply(lambda r: f"M{month_number(r['active_month']) - month_number(r['signup_month'])}", axis=1)
activity["retention_pct"] = (activity["active_accounts"] / activity["cohort_size"] * 100).round(1)
print(pd.pivot_table(activity, index="signup_month", columns="cohort_age", values="retention_pct", aggfunc="mean", fill_value=0))
print(activity[activity["cohort_age"] == "M2"].sort_values("retention_pct")[["signup_month", "active_accounts", "cohort_size", "retention_pct"]])

Click ▶ Run to execute

Ctrl+Enter to run · Tab to indent

💳 Subscription Revenue Waterfall

1 · Join invoices to account metadata

2 · Build the monthly MRR movement waterfall

3 · Turn monthly activity into a retention matrix