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

Activation funnel

@usagemetrics/activation-funnel

Measure an activation funnel from an events CSV: how many users or accounts reach each step within a time window, conversion step to step, median time between steps, the biggest drop, and splits by plan or any segment. Writes markdown, CSV, JSON, and a list of who got stuck.

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// Activation funnel from an events CSV.
  3. 3// Usage: node scripts/funnel.mjs <events.csv> --steps signed_up,connected_source,created_report,shared_dashboard
  4. 4// [--window-days 14] [--entity user|account] [--any-order] [--include-recent] [--as-of 2026-10-30]
  5. 5// [--from 2026-08-01] [--to 2026-09-30] [--segments data/accounts.csv --segment-column plan] [--out out/funnel]
  6. 6// Columns: timestamp, account_id, user_id, event.
  7. 7import { DAY_MS, dayKey, fail, mdTable, median, parseArgs, parseDate, readCsv, round, toCsv, writeOut } from "./lib.mjs";
  8. 8
  9. 9const args = parseArgs(process.argv.slice(2), ["any-order", "include-recent"]);
  10. 10const input = args._[0];
  11. 11if (!input || !args.steps) fail("usage: node scripts/funnel.mjs <events.csv> --steps step1,step2,step3 [--window-days 14]");
  12. 12const steps = String(args.steps).split(",").map((s) => s.trim().toLowerCase()).filter(Boolean);
  13. 13if (steps.length < 2) fail("--steps needs at least two events, in order.");
  14. 14const windowDays = Number(args["window-days"] ?? 14);
  15. 15const entity = args.entity || "user";
  16. 16const ordered = !args["any-order"];
  17. 17const outPrefix = args.out || "out/funnel";
  18. 18
  19. 19const rows = readCsv(input, "events CSV", ["timestamp", "event", entity === "user" ? "user_id" : "account_id"]);
  20. 20const byEntity = new Map();
  21. 21const accountOf = new Map();
  22. 22let maxT = 0;
  23. 23for (const r of rows) {
  24. 24 const d = parseDate(r.timestamp);
  25. 25 const id = entity === "user" ? r.user_id : r.account_id;
  26. 26 if (!d || !id) continue;
  27. 27 const ev = (r.event || "").toLowerCase();
  28. 28 maxT = Math.max(maxT, d.getTime());
  29. 29 if (r.account_id) accountOf.set(id, r.account_id);
  30. 30 if (!steps.includes(ev)) continue;
  31. 31 if (!byEntity.has(id)) byEntity.set(id, []);
  32. 32 byEntity.get(id).push({ t: d.getTime(), ev });
  33. 33}
  34. 34const asOf = args["as-of"] ? parseDate(args["as-of"])?.getTime() + DAY_MS - 1 : maxT;
  35. 35const from = args.from ? parseDate(args.from)?.getTime() : -Infinity;
  36. 36const to = args.to ? parseDate(args.to)?.getTime() + DAY_MS - 1 : Infinity;
  37. 37
  38. 38let segmentOf = null;
  39. 39if (args.segments) {
  40. 40 const col = (args["segment-column"] || "segment").toLowerCase();
  41. 41 const seg = readCsv(args.segments, "segments CSV", ["account_id", col]);
  42. 42 const map = new Map(seg.map((s) => [s.account_id, s[col] || "(blank)"]));
  43. 43 segmentOf = (id) => map.get(entity === "account" ? id : accountOf.get(id)) || "(unknown)";
  44. 44}
  45. 45
  46. 46// Walk each entity through the steps.
  47. 47const journeys = [];
  48. 48let tooRecent = 0;
  49. 49for (const [id, evs] of byEntity) {
  50. 50 evs.sort((a, b) => a.t - b.t);
  51. 51 const start = evs.find((e) => e.ev === steps[0]);
  52. 52 if (!start || start.t < from || start.t > to) continue;
  53. 53 if (!args["include-recent"] && start.t + windowDays * DAY_MS > asOf) {
  54. 54 tooRecent++;
  55. 55 continue;
  56. 56 }
  57. 57 const deadline = start.t + windowDays * DAY_MS;
  58. 58 const times = [start.t];
  59. 59 let prevT = start.t;
  60. 60 for (let i = 1; i < steps.length; i++) {
  61. 61 const hit = evs.find((e) => e.ev === steps[i] && e.t <= deadline && (ordered ? e.t >= prevT : e.t >= start.t));
  62. 62 if (!hit) break;
  63. 63 times.push(hit.t);
  64. 64 prevT = hit.t;
  65. 65 }
  66. 66 journeys.push({ id, times, segment: segmentOf ? segmentOf(id) : null });
  67. 67}
  68. 68if (!journeys.length) fail(`no ${entity}s did "${steps[0]}" in the chosen range (and at least ${windowDays} days before the as-of date). Try --include-recent or check the step names.`);
  69. 69
  70. 70function funnel(list) {
  71. 71 const n0 = list.length;
  72. 72 return steps.map((s, i) => {
  73. 73 const reached = list.filter((j) => j.times.length > i);
  74. 74 const prev = i === 0 ? n0 : list.filter((j) => j.times.length > i - 1).length;
  75. 75 const hrsFromPrev = i === 0 ? [] : reached.map((j) => (j.times[i] - j.times[i - 1]) / 3600000);
  76. 76 const hrsFromStart = reached.map((j) => (j.times[i] - j.times[0]) / 3600000);
  77. 77 return {
  78. 78 step: s,
  79. 79 reached: reached.length,
  80. 80 pct_of_start: n0 ? round(reached.length / n0, 4) : 0,
  81. 81 pct_of_previous: prev ? round(reached.length / prev, 4) : 0,
  82. 82 dropped: prev - reached.length,
  83. 83 median_hours_from_previous: i === 0 || !hrsFromPrev.length ? null : round(median(hrsFromPrev), 1),
  84. 84 median_hours_from_start: i === 0 || !hrsFromStart.length ? null : round(median(hrsFromStart), 1),
  85. 85 };
  86. 86 });
  87. 87}
  88. 88const overall = funnel(journeys);
  89. 89const biggest = overall.slice(1).reduce((a, b) => (b.pct_of_previous < a.pct_of_previous ? b : a));
  90. 90const segments = {};
  91. 91if (segmentOf) for (const s of [...new Set(journeys.map((j) => j.segment))].sort()) segments[s] = funnel(journeys.filter((j) => j.segment === s));
  92. 92
  93. 93const result = {
  94. 94 source: input,
  95. 95 entity,
  96. 96 steps,
  97. 97 window_days: windowDays,
  98. 98 ordered,
  99. 99 as_of: dayKey(new Date(asOf)),
  100. 100 entered: journeys.length,
  101. 101 excluded_too_recent: tooRecent,
  102. 102 completed: overall[overall.length - 1].reached,
  103. 103 conversion: overall[overall.length - 1].pct_of_start,
  104. 104 biggest_drop: { into_step: biggest.step, pct_of_previous: biggest.pct_of_previous, dropped: biggest.dropped },
  105. 105 funnel: overall,
  106. 106 segments,
  107. 107};
  108. 108writeOut(`${outPrefix}.json`, JSON.stringify(result, null, 2) + "\n");
  109. 109const flat = [];
  110. 110for (const [seg, f] of [["all", overall], ...Object.entries(segments)]) for (const s of f) flat.push({ segment: seg, ...s });
  111. 111writeOut(`${outPrefix}.csv`, toCsv(flat, ["segment", "step", "reached", "pct_of_start", "pct_of_previous", "dropped", "median_hours_from_previous", "median_hours_from_start"]));
  112. 112// Who got stuck at the biggest drop (IDs only, for follow-up lists).
  113. 113const stuckIndex = steps.indexOf(biggest.step);
  114. 114writeOut(`${outPrefix}-stuck.csv`, toCsv(journeys.filter((j) => j.times.length === stuckIndex).map((j) => ({ id: j.id, account_id: accountOf.get(j.id) || "", last_step: steps[stuckIndex - 1], started: dayKey(new Date(j.times[0])) })), ["id", "account_id", "last_step", "started"]));
  115. 115
  116. 116const p = (v) => `${Math.round(v * 100)}%`;
  117. 117const dur = (h) => (h === null ? "" : h < 48 ? `${h.toFixed(1)} h` : `${(h / 24).toFixed(1)} d`);
  118. 118const funnelTable = (f) => mdTable(["Step", "Reached", "% of start", "% of previous", "Dropped", "Median time from previous"], f.map((s) => [s.step, s.reached, p(s.pct_of_start), p(s.pct_of_previous), s.dropped, dur(s.median_hours_from_previous)]));
  119. 119const md = [
  120. 120 `# Activation funnel (${entity}s, ${windowDays}-day window${ordered ? ", steps in order" : ", any order"})`,
  121. 121 "",
  122. 122 `${journeys.length} ${entity}s started with "${steps[0]}"; ${result.completed} (${p(result.conversion)}) reached "${steps[steps.length - 1]}" within ${windowDays} days.${tooRecent ? ` ${tooRecent} started less than ${windowDays} days before ${result.as_of} and are left out.` : ""}`,
  123. 123 "",
  124. 124 funnelTable(overall),
  125. 125 "",
  126. 126 `Biggest drop: into "${biggest.step}" (${p(biggest.pct_of_previous)} of the previous step made it; ${biggest.dropped} dropped). Their IDs are in \`${outPrefix}-stuck.csv\`.`,
  127. 127 "",
  128. 128 ...Object.entries(segments).flatMap(([seg, f]) => [`## Segment: ${seg} (${f[0].reached} started, ${p(f[f.length - 1].pct_of_start)} completed)`, "", funnelTable(f), ""]),
  129. 129].join("\n");
  130. 130writeOut(`${outPrefix}.md`, md);
  131. 131
  132. 132process.stdout.write(`Wrote ${outPrefix}.md, ${outPrefix}.csv, ${outPrefix}.json, ${outPrefix}-stuck.csv\n`);
  133. 133process.stdout.write(`${journeys.length} started, ${p(result.conversion)} completed. Biggest drop into "${biggest.step}" (${p(biggest.pct_of_previous)}).\n`);
Sign in to comment on a line