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

Retention cohorts

@usagemetrics/retention-cohorts

Build weekly or monthly retention cohorts for users or accounts from an events CSV. Writes the cohort table, a size-weighted average curve, where the curve flattens, and the best and worst cohorts, as markdown, CSV, and JSON.

SkillDatav1.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// Retention cohorts from an events CSV.
  3. 3// Usage: node scripts/cohorts.mjs <events.csv> [--period week|month] [--entity user|account] [--periods 12]
  4. 4// [--active-events ran_query,created_report] [--as-of 2026-10-30] [--include-current] [--out out/retention]
  5. 5// Columns: timestamp, account_id, user_id, event.
  6. 6import { DAY_MS, dayKey, fail, mdTable, monthKey, parseArgs, parseDate, readCsv, round, toCsv, weekKey, writeOut } from "./lib.mjs";
  7. 7
  8. 8const args = parseArgs(process.argv.slice(2), ["include-current"]);
  9. 9const input = args._[0];
  10. 10if (!input) fail("usage: node scripts/cohorts.mjs <events.csv> [--period week|month] [--entity user|account]");
  11. 11const period = args.period || "month";
  12. 12if (!["week", "month"].includes(period)) fail("--period must be week or month.");
  13. 13const entity = args.entity || "user";
  14. 14if (!["user", "account"].includes(entity)) fail("--entity must be user or account.");
  15. 15const maxPeriods = Number(args.periods ?? 12);
  16. 16const outPrefix = args.out || "out/retention";
  17. 17const activeEvents = args["active-events"] ? new Set(String(args["active-events"]).split(",").map((s) => s.trim().toLowerCase())) : null;
  18. 18
  19. 19const rows = readCsv(input, "events CSV", ["timestamp", entity === "user" ? "user_id" : "account_id", "event"]);
  20. 20const MONDAY0 = Date.UTC(1970, 0, 5);
  21. 21const keyOf = (d) => (period === "week" ? weekKey(d) : monthKey(d));
  22. 22function index(key) {
  23. 23 if (period === "month") {
  24. 24 const [y, m] = key.split("-").map(Number);
  25. 25 return y * 12 + (m - 1);
  26. 26 }
  27. 27 return Math.round((Date.parse(key) - MONDAY0) / (7 * DAY_MS));
  28. 28}
  29. 29function labelOf(i) {
  30. 30 if (period === "month") return `${Math.floor(i / 12)}-${String((i % 12) + 1).padStart(2, "0")}`;
  31. 31 return dayKey(new Date(MONDAY0 + i * 7 * DAY_MS));
  32. 32}
  33. 33
  34. 34const first = new Map();
  35. 35const activeIn = new Map();
  36. 36let maxDate = null;
  37. 37let skipped = 0;
  38. 38for (const r of rows) {
  39. 39 const d = parseDate(r.timestamp);
  40. 40 const id = entity === "user" ? r.user_id : r.account_id;
  41. 41 if (!d || !id) {
  42. 42 skipped++;
  43. 43 continue;
  44. 44 }
  45. 45 if (!maxDate || d > maxDate) maxDate = d;
  46. 46 const i = index(keyOf(d));
  47. 47 // First seen counts any event; "active" can be limited to meaningful events.
  48. 48 if (!first.has(id) || i < first.get(id)) first.set(id, i);
  49. 49 if (activeEvents && !activeEvents.has((r.event || "").toLowerCase())) continue;
  50. 50 if (!activeIn.has(id)) activeIn.set(id, new Set());
  51. 51 activeIn.get(id).add(i);
  52. 52}
  53. 53if (!first.size) fail(`no usable rows in ${input}.`);
  54. 54const asOf = args["as-of"] ? parseDate(args["as-of"]) : maxDate;
  55. 55const current = index(keyOf(asOf));
  56. 56const lastComplete = args["include-current"] ? current : current - 1;
  57. 57
  58. 58const cohorts = new Map();
  59. 59for (const [id, i] of first) {
  60. 60 if (i > lastComplete) continue;
  61. 61 if (!cohorts.has(i)) cohorts.set(i, []);
  62. 62 cohorts.get(i).push(id);
  63. 63}
  64. 64const keys = [...cohorts.keys()].sort((a, b) => a - b).slice(-maxPeriods);
  65. 65const table = keys.map((c) => {
  66. 66 const ids = cohorts.get(c);
  67. 67 const counts = [];
  68. 68 for (let k = 0; k < maxPeriods; k++) {
  69. 69 if (c + k > lastComplete) break;
  70. 70 counts.push(ids.filter((id) => activeIn.get(id)?.has(c + k)).length);
  71. 71 }
  72. 72 return { cohort: labelOf(c), size: ids.length, retained: counts, rates: counts.map((n) => round(n / ids.length, 4)) };
  73. 73});
  74. 74
  75. 75// Weighted average curve: only cohorts old enough to have each period.
  76. 76const curve = [];
  77. 77for (let k = 0; k < maxPeriods; k++) {
  78. 78 let n = 0;
  79. 79 let d = 0;
  80. 80 for (const row of table)
  81. 81 if (row.retained.length > k) {
  82. 82 n += row.retained[k];
  83. 83 d += row.size;
  84. 84 }
  85. 85 if (!d) break;
  86. 86 curve.push({ period: k, rate: round(n / d, 4), cohorts: table.filter((r) => r.retained.length > k).length });
  87. 87}
  88. 88let flattensAt = null;
  89. 89// Flat = less than 2 points lost per period for two periods in a row, each averaged over 3+ cohorts.
  90. 90for (let k = 2; k + 1 < curve.length; k++)
  91. 91 if (curve[k + 1].cohorts >= 3 && curve[k - 1].rate - curve[k].rate < 0.02 && curve[k].rate - curve[k + 1].rate < 0.02) {
  92. 92 flattensAt = k - 1;
  93. 93 break;
  94. 94 }
  95. 95const p1 = table.filter((r) => r.rates.length > 1 && r.size >= 3);
  96. 96const best = p1.length ? p1.reduce((a, b) => (b.rates[1] > a.rates[1] ? b : a)) : null;
  97. 97const worst = p1.length ? p1.reduce((a, b) => (b.rates[1] < a.rates[1] ? b : a)) : null;
  98. 98
  99. 99const unit = period === "week" ? "W" : "M";
  100. 100const result = {
  101. 101 source: input,
  102. 102 period,
  103. 103 entity,
  104. 104 active_definition: activeEvents ? `any of: ${[...activeEvents].join(", ")}` : "any event",
  105. 105 as_of: dayKey(asOf),
  106. 106 last_complete_period: labelOf(lastComplete),
  107. 107 cohorts: table,
  108. 108 average_curve: curve,
  109. 109 flattens_at: flattensAt,
  110. 110 best_period1: best && { cohort: best.cohort, rate: best.rates[1] },
  111. 111 worst_period1: worst && { cohort: worst.cohort, rate: worst.rates[1] },
  112. 112 skipped_rows: skipped,
  113. 113};
  114. 114writeOut(`${outPrefix}.json`, JSON.stringify(result, null, 2) + "\n");
  115. 115const cols = Array.from({ length: maxPeriods }, (_, k) => `${unit}${k}`);
  116. 116writeOut(
  117. 117 `${outPrefix}.csv`,
  118. 118 toCsv(
  119. 119 table.map((r) => ({ cohort: r.cohort, size: r.size, ...Object.fromEntries(r.rates.map((v, k) => [`${unit}${k}`, v])) })),
  120. 120 ["cohort", "size", ...cols.slice(0, Math.max(...table.map((r) => r.rates.length), 1))],
  121. 121 ),
  122. 122);
  123. 123
  124. 124const width = Math.max(...table.map((r) => r.rates.length), 1);
  125. 125const pctCell = (v) => (v === undefined ? "" : `${Math.round(v * 100)}%`);
  126. 126const md = [
  127. 127 `# ${entity === "user" ? "User" : "Account"} retention by ${period}ly cohort`,
  128. 128 "",
  129. 129 `Active means ${result.active_definition}. Cohort = ${period} of first event. As of ${result.as_of}; the current ${period} is ${args["include-current"] ? "included (partial)" : "left out because it is not complete"}.`,
  130. 130 "",
  131. 131 mdTable(["Cohort", "Size", ...cols.slice(0, width)], table.map((r) => [r.cohort, r.size, ...Array.from({ length: width }, (_, k) => pctCell(r.rates[k]))])),
  132. 132 "",
  133. 133 "## Average curve (weighted by cohort size)",
  134. 134 "",
  135. 135 mdTable(["Period", "Retained", "Cohorts in average"], curve.map((c) => [`${unit}${c.period}`, pctCell(c.rate), c.cohorts])),
  136. 136 "",
  137. 137 flattensAt !== null ? `The curve flattens around ${unit}${flattensAt} (less than 2 points lost per ${period} for two ${period}s in a row), at about ${pctCell(curve[flattensAt].rate)}.` : "The curve has not flattened yet in this data.",
  138. 138 best && worst && best !== worst ? `Best ${unit}1: ${best.cohort} (${pctCell(best.rates[1])}). Worst ${unit}1: ${worst.cohort} (${pctCell(worst.rates[1])}).` : "",
  139. 139 "",
  140. 140].join("\n");
  141. 141writeOut(`${outPrefix}.md`, md);
  142. 142
  143. 143process.stdout.write(`Wrote ${outPrefix}.md, ${outPrefix}.csv, ${outPrefix}.json\n`);
  144. 144process.stdout.write(`${table.length} cohorts. ${unit}1 average ${curve[1] ? pctCell(curve[1].rate) : "n/a"}${flattensAt !== null ? `, flattens around ${unit}${flattensAt}` : ""}.\n`);
Sign in to comment on a line