Skip to content
SlashClone
DiscoverUse in ClaudeSubmit a link
Sign in
Made by@ledgerly

Arr waterfall

@ledgerly/arr-waterfall

Turn a monthly subscriptions export into an ARR waterfall: starting ARR, new, reactivation, expansion, contraction, churn, ending ARR, plus NRR and GRR. Writes markdown, CSV, and a per-customer movements file. Works offline from a CSV.

SkillFinancev1.0.0 (latest)Tested
Use in Claude Save Like Clone Comment
Activity
New: be the first to try it
  1. 1#!/usr/bin/env node
  2. 2// ARR waterfall from a monthly subscriptions export.
  3. 3// Usage: node scripts/arr-waterfall.mjs <subscriptions.csv> [--out out/arr-waterfall] [--from 2026-01] [--to 2026-09]
  4. 4// Input columns: month, customer_id, and either mrr or arr. One row per customer per month
  5. 5// (several rows for the same customer and month are added together).
  6. 6import {
  7. 7 addMonths,
  8. 8 fail,
  9. 9 mdTable,
  10. 10 money,
  11. 11 monthKey,
  12. 12 num,
  13. 13 parseArgs,
  14. 14 parseDate,
  15. 15 pct,
  16. 16 readCsv,
  17. 17 round,
  18. 18 toCsv,
  19. 19 writeOut,
  20. 20} from "./lib.mjs";
  21. 21
  22. 22const args = parseArgs(process.argv.slice(2));
  23. 23const input = args._[0];
  24. 24const outPrefix = args.out || "out/arr-waterfall";
  25. 25if (!input) fail("usage: node scripts/arr-waterfall.mjs <subscriptions.csv> [--out out/arr-waterfall]");
  26. 26
  27. 27const rows = readCsv(input, "subscriptions CSV", ["month", "customer_id"]);
  28. 28if (!rows.length) fail(`${input} has no data rows.`);
  29. 29const hasArr = "arr" in rows[0];
  30. 30const hasMrr = "mrr" in rows[0];
  31. 31if (!hasArr && !hasMrr) fail(`${input} needs an "mrr" or an "arr" column.`);
  32. 32
  33. 33// customer -> month -> ARR
  34. 34const arr = new Map();
  35. 35const months = new Set();
  36. 36const bad = [];
  37. 37rows.forEach((r, i) => {
  38. 38 const d = parseDate(r.month);
  39. 39 const v = num(hasArr ? r.arr : r.mrr);
  40. 40 if (!d || !r.customer_id || Number.isNaN(v)) {
  41. 41 bad.push(i + 2);
  42. 42 return;
  43. 43 }
  44. 44 const m = monthKey(d);
  45. 45 months.add(m);
  46. 46 const value = hasArr ? v : v * 12;
  47. 47 if (!arr.has(r.customer_id)) arr.set(r.customer_id, new Map());
  48. 48 const byMonth = arr.get(r.customer_id);
  49. 49 byMonth.set(m, (byMonth.get(m) || 0) + value);
  50. 50});
  51. 51if (bad.length) process.stderr.write(`Skipped ${bad.length} row(s) with a bad month, customer, or amount (lines ${bad.slice(0, 10).join(", ")}${bad.length > 10 ? ", ..." : ""}).\n`);
  52. 52if (!months.size) fail("no usable rows.");
  53. 53
  54. 54const sorted = [...months].sort();
  55. 55const first = args.from || sorted[0];
  56. 56const last = args.to || sorted[sorted.length - 1];
  57. 57const span = [];
  58. 58for (let m = first; m <= last; m = addMonths(m, 1)) span.push(m);
  59. 59if (span.length < 2) fail("need at least two months of data to build a waterfall.");
  60. 60
  61. 61const at = (c, m) => arr.get(c)?.get(m) || 0;
  62. 62const customers = [...arr.keys()];
  63. 63const seenBefore = (c, m) => [...arr.get(c).keys()].some((k) => k < m && arr.get(c).get(k) > 0);
  64. 64
  65. 65const table = [];
  66. 66const movers = [];
  67. 67for (let i = 1; i < span.length; i++) {
  68. 68 const prevM = span[i - 1];
  69. 69 const m = span[i];
  70. 70 const row = { month: m, starting: 0, new: 0, reactivation: 0, expansion: 0, contraction: 0, churn: 0, ending: 0, customers_start: 0, customers_end: 0, new_logos: 0, churned_logos: 0 };
  71. 71 for (const c of customers) {
  72. 72 const p = at(c, prevM);
  73. 73 const n = at(c, m);
  74. 74 row.starting += p;
  75. 75 row.ending += n;
  76. 76 if (p > 0) row.customers_start++;
  77. 77 if (n > 0) row.customers_end++;
  78. 78 let kind = null;
  79. 79 let delta = n - p;
  80. 80 if (p === 0 && n > 0) {
  81. 81 kind = seenBefore(c, prevM) || seenBefore(c, m) ? "reactivation" : "new";
  82. 82 if (kind === "new") row.new_logos++;
  83. 83 } else if (p > 0 && n === 0) {
  84. 84 kind = "churn";
  85. 85 row.churned_logos++;
  86. 86 } else if (p > 0 && n > p) kind = "expansion";
  87. 87 else if (p > 0 && n < p) kind = "contraction";
  88. 88 if (!kind) continue;
  89. 89 row[kind] += Math.abs(delta);
  90. 90 movers.push({ month: m, customer_id: c, movement: kind, from_arr: round(p), to_arr: round(n), change: round(delta) });
  91. 91 }
  92. 92 row.nrr = row.starting > 0 ? (row.starting + row.expansion - row.contraction - row.churn) / row.starting : null;
  93. 93 row.grr = row.starting > 0 ? (row.starting - row.contraction - row.churn) / row.starting : null;
  94. 94 row.check = round(row.starting + row.new + row.reactivation + row.expansion - row.contraction - row.churn - row.ending);
  95. 95 table.push(row);
  96. 96}
  97. 97
  98. 98// Cohort NRR and GRR across the whole range: customers active in the first month.
  99. 99function cohortRetention(fromM, toM) {
  100. 100 let base = 0;
  101. 101 let now = 0;
  102. 102 let floor = 0;
  103. 103 for (const c of customers) {
  104. 104 const b = at(c, fromM);
  105. 105 if (b <= 0) continue;
  106. 106 const e = at(c, toM);
  107. 107 base += b;
  108. 108 now += e;
  109. 109 floor += Math.min(b, e);
  110. 110 }
  111. 111 return base > 0 ? { from: fromM, to: toM, base_arr: round(base), nrr: now / base, grr: floor / base } : null;
  112. 112}
  113. 113const period = cohortRetention(span[0], span[span.length - 1]);
  114. 114const t12 = span.length >= 13 ? cohortRetention(span[span.length - 13], span[span.length - 1]) : null;
  115. 115
  116. 116const totals = {
  117. 117 starting_arr: round(table[0].starting),
  118. 118 new: round(table.reduce((a, r) => a + r.new, 0)),
  119. 119 reactivation: round(table.reduce((a, r) => a + r.reactivation, 0)),
  120. 120 expansion: round(table.reduce((a, r) => a + r.expansion, 0)),
  121. 121 contraction: round(table.reduce((a, r) => a + r.contraction, 0)),
  122. 122 churn: round(table.reduce((a, r) => a + r.churn, 0)),
  123. 123 ending_arr: round(table[table.length - 1].ending),
  124. 124};
  125. 125
  126. 126const outRows = table.map((r) => ({
  127. 127 month: r.month,
  128. 128 starting_arr: round(r.starting),
  129. 129 new: round(r.new),
  130. 130 reactivation: round(r.reactivation),
  131. 131 expansion: round(r.expansion),
  132. 132 contraction: round(r.contraction),
  133. 133 churn: round(r.churn),
  134. 134 ending_arr: round(r.ending),
  135. 135 net_new_arr: round(r.ending - r.starting),
  136. 136 nrr: r.nrr === null ? "" : round(r.nrr, 4),
  137. 137 grr: r.grr === null ? "" : round(r.grr, 4),
  138. 138 customers_start: r.customers_start,
  139. 139 customers_end: r.customers_end,
  140. 140 new_logos: r.new_logos,
  141. 141 churned_logos: r.churned_logos,
  142. 142}));
  143. 143
  144. 144const columns = Object.keys(outRows[0]);
  145. 145writeOut(`${outPrefix}.csv`, toCsv(outRows, columns));
  146. 146writeOut(`${outPrefix}-movements.csv`, toCsv(movers, ["month", "customer_id", "movement", "from_arr", "to_arr", "change"]));
  147. 147
  148. 148const json = {
  149. 149 source: input,
  150. 150 basis: hasArr ? "arr column" : "mrr x 12",
  151. 151 from: span[0],
  152. 152 to: span[span.length - 1],
  153. 153 totals,
  154. 154 period_cohort: period && { ...period, nrr: round(period.nrr, 4), grr: round(period.grr, 4) },
  155. 155 trailing_12m: t12 && { ...t12, nrr: round(t12.nrr, 4), grr: round(t12.grr, 4) },
  156. 156 months: outRows,
  157. 157 unreconciled_months: table.filter((r) => Math.abs(r.check) > 0.01).map((r) => r.month),
  158. 158 skipped_rows: bad.length,
  159. 159};
  160. 160writeOut(`${outPrefix}.json`, JSON.stringify(json, null, 2) + "\n");
  161. 161
  162. 162const neg = (v) => (v > 0 ? `-${money(v)}` : money(0));
  163. 163const lastMonthMoves = movers.filter((m) => m.month === span[span.length - 1]);
  164. 164const top = (kind) =>
  165. 165 lastMonthMoves
  166. 166 .filter((m) => m.movement === kind)
  167. 167 .sort((a, b) => Math.abs(b.change) - Math.abs(a.change))
  168. 168 .slice(0, 5)
  169. 169 .map((m) => `${m.customer_id} (${money(m.change)})`)
  170. 170 .join(", ") || "none";
  171. 171
  172. 172const md = [
  173. 173 `# ARR waterfall: ${span[0]} to ${span[span.length - 1]}`,
  174. 174 "",
  175. 175 `Basis: ${json.basis}. Source: \`${input}\`.`,
  176. 176 "",
  177. 177 `Starting ARR ${money(totals.starting_arr)} | New ${money(totals.new)} | Reactivation ${money(totals.reactivation)} | Expansion ${money(totals.expansion)} | Contraction ${neg(totals.contraction)} | Churn ${neg(totals.churn)} | Ending ARR ${money(totals.ending_arr)}`,
  178. 178 "",
  179. 179 period ? `Cohort retention, customers active in ${period.from}, measured in ${period.to}: NRR ${pct(period.nrr)}, GRR ${pct(period.grr)}.` : "",
  180. 180 t12 ? `Trailing 12-month NRR ${pct(t12.nrr)}, GRR ${pct(t12.grr)}.` : "Trailing 12-month NRR needs 13 months of data.",
  181. 181 "",
  182. 182 "## By month",
  183. 183 "",
  184. 184 mdTable(
  185. 185 ["Month", "Starting", "New", "Reactivation", "Expansion", "Contraction", "Churn", "Ending", "NRR (mo)", "GRR (mo)"],
  186. 186 outRows.map((r) => [r.month, money(r.starting_arr), money(r.new), money(r.reactivation), money(r.expansion), neg(r.contraction), neg(r.churn), money(r.ending_arr), r.nrr === "" ? "n/a" : pct(r.nrr), r.grr === "" ? "n/a" : pct(r.grr)]),
  187. 187 ),
  188. 188 "",
  189. 189 `## Biggest movers in ${span[span.length - 1]}`,
  190. 190 "",
  191. 191 `- Expansion: ${top("expansion")}`,
  192. 192 `- Contraction: ${top("contraction")}`,
  193. 193 `- Churn: ${top("churn")}`,
  194. 194 `- New: ${top("new")}`,
  195. 195 "",
  196. 196 json.unreconciled_months.length ? `Warning: months that do not tie out: ${json.unreconciled_months.join(", ")}.` : "Every month ties out: starting + new + reactivation + expansion - contraction - churn = ending.",
  197. 197 "",
  198. 198].join("\n");
  199. 199writeOut(`${outPrefix}.md`, md);
  200. 200
  201. 201process.stdout.write(`Wrote ${outPrefix}.md, ${outPrefix}.csv, ${outPrefix}.json, ${outPrefix}-movements.csv\n`);
  202. 202process.stdout.write(`Ending ARR ${money(totals.ending_arr)} (from ${money(totals.starting_arr)}).${period ? ` Cohort NRR ${pct(period.nrr)}, GRR ${pct(period.grr)}.` : ""}\n`);
Sign in to comment on a line