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

Weekly forecast rollup

@revopslab/weekly-forecast-rollup

Roll an opportunity export from HubSpot, Salesforce, or any CRM into closed won, commit, best case, and pipeline by rep and team for the quarter, then compare with last week: what moved, which deals slipped or were pulled in, upgrades, downgrades, new, won, lost, and dropped deals.

SkillRevOpsv1.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// weekly-forecast-rollup: roll an opportunity export up into closed won, commit,
  3. 3// best case, and pipeline by rep and team for a period, and compare with last
  4. 4// week's export: totals that moved, slipped deals, pulled-in deals, category
  5. 5// changes, new, won, lost, and dropped deals. Node 22, built-ins only.
  6. 6//
  7. 7// Usage:
  8. 8// node scripts/forecast-rollup.mjs <this-week.csv> [last-week.csv]
  9. 9// --period-start YYYY-MM-DD --period-end YYYY-MM-DD
  10. 10// [--out out] [--name forecast] [--map field=Column Name]...
  11. 11
  12. 12import { readFileSync, writeFileSync, mkdirSync } from "node:fs";
  13. 13import { join } from "node:path";
  14. 14
  15. 15function fail(msg) {
  16. 16 console.error(`weekly-forecast-rollup: ${msg}`);
  17. 17 process.exit(1);
  18. 18}
  19. 19
  20. 20const args = process.argv.slice(2);
  21. 21const opts = { out: "out", name: "forecast", start: null, end: null, map: {} };
  22. 22const pos = [];
  23. 23for (let i = 0; i < args.length; i++) {
  24. 24 const a = args[i];
  25. 25 if (a === "--out") opts.out = args[++i];
  26. 26 else if (a === "--name") opts.name = args[++i];
  27. 27 else if (a === "--period-start") opts.start = args[++i];
  28. 28 else if (a === "--period-end") opts.end = args[++i];
  29. 29 else if (a === "--map") {
  30. 30 const [k, ...v] = String(args[++i]).split("=");
  31. 31 opts.map[k.trim()] = v.join("=").trim();
  32. 32 } else if (a === "--help" || a === "-h") {
  33. 33 console.log(
  34. 34 "Usage: node scripts/forecast-rollup.mjs <this-week.csv> [last-week.csv] --period-start YYYY-MM-DD --period-end YYYY-MM-DD [--out out] [--name forecast] [--map field=Column]",
  35. 35 );
  36. 36 process.exit(0);
  37. 37 } else pos.push(a);
  38. 38}
  39. 39if (pos.length < 1 || pos.length > 2) fail("give this week's export and, optionally, last week's. Try --help.");
  40. 40if (!opts.start || !opts.end) fail("--period-start and --period-end are required (for example the quarter: 2026-10-01 and 2026-12-31)");
  41. 41
  42. 42// ---------- CSV ----------
  43. 43function parseCsv(text) {
  44. 44 if (text.charCodeAt(0) === 0xfeff) text = text.slice(1);
  45. 45 const rows = [];
  46. 46 let row = [];
  47. 47 let field = "";
  48. 48 let q = false;
  49. 49 for (let i = 0; i < text.length; i++) {
  50. 50 const c = text[i];
  51. 51 if (q) {
  52. 52 if (c === '"') {
  53. 53 if (text[i + 1] === '"') {
  54. 54 field += '"';
  55. 55 i++;
  56. 56 } else q = false;
  57. 57 } else field += c;
  58. 58 } else if (c === '"') q = true;
  59. 59 else if (c === ",") {
  60. 60 row.push(field);
  61. 61 field = "";
  62. 62 } else if (c === "\n" || c === "\r") {
  63. 63 if (c === "\r" && text[i + 1] === "\n") i++;
  64. 64 row.push(field);
  65. 65 rows.push(row);
  66. 66 row = [];
  67. 67 field = "";
  68. 68 } else field += c;
  69. 69 }
  70. 70 if (field !== "" || row.length) {
  71. 71 row.push(field);
  72. 72 rows.push(row);
  73. 73 }
  74. 74 const data = rows.filter((r) => r.some((v) => v.trim() !== ""));
  75. 75 if (!data.length) return { header: [], records: [] };
  76. 76 const header = data.shift().map((h) => h.trim());
  77. 77 return { header, records: data.map((r) => Object.fromEntries(header.map((h, i) => [h, (r[i] ?? "").trim()]))) };
  78. 78}
  79. 79
  80. 80const norm = (s) => s.toLowerCase().replace(/[^a-z0-9]+/g, "_").replace(/^_|_$/g, "");
  81. 81const ALIASES = {
  82. 82 id: ["id", "opportunity_id", "deal_id", "record_id", "hs_object_id", "opportunity_id_18"],
  83. 83 name: ["name", "opportunity_name", "deal_name", "dealname"],
  84. 84 owner: ["owner", "opportunity_owner", "deal_owner", "owner_name", "hubspot_owner", "rep", "account_executive"],
  85. 85 team: ["team", "sales_team", "owner_team", "owner_role", "region", "segment"],
  86. 86 amount: ["amount", "amount_converted", "deal_amount", "amount_usd", "amount_in_company_currency", "arr", "acv"],
  87. 87 close_date: ["close_date", "closedate", "expected_close_date"],
  88. 88 stage: ["stage", "stage_name", "deal_stage", "dealstage"],
  89. 89 forecast_category: ["forecast_category", "forecastcategory", "forecast_category_name", "forecastcategoryname", "hs_manual_forecast_category"],
  90. 90};
  91. 91function resolveColumns(header, file) {
  92. 92 const byNorm = new Map(header.map((h) => [norm(h), h]));
  93. 93 const cols = {};
  94. 94 for (const [field, list] of Object.entries(ALIASES)) {
  95. 95 if (opts.map[field]) {
  96. 96 if (!header.includes(opts.map[field])) fail(`${file}: --map ${field}=${opts.map[field]} names a column that does not exist`);
  97. 97 cols[field] = opts.map[field];
  98. 98 continue;
  99. 99 }
  100. 100 const hit = list.find((a) => byNorm.has(a));
  101. 101 if (hit) cols[field] = byNorm.get(hit);
  102. 102 }
  103. 103 for (const req of ["id", "amount", "close_date"])
  104. 104 if (!cols[req]) fail(`${file}: no column found for "${req}". Columns are: ${header.join(", ")}. Use --map ${req}=<column>.`);
  105. 105 return cols;
  106. 106}
  107. 107
  108. 108function parseDate(s) {
  109. 109 if (!s) return null;
  110. 110 let m = s.match(/^(\d{4})-(\d{1,2})-(\d{1,2})/);
  111. 111 if (m) return `${m[1]}-${m[2].padStart(2, "0")}-${m[3].padStart(2, "0")}`;
  112. 112 m = s.match(/^(\d{1,2})\/(\d{1,2})\/(\d{4})/);
  113. 113 if (m) return `${m[3]}-${m[1].padStart(2, "0")}-${m[2].padStart(2, "0")}`;
  114. 114 return null;
  115. 115}
  116. 116if (!parseDate(opts.start) || !parseDate(opts.end)) fail("period dates must be YYYY-MM-DD");
  117. 117const START = parseDate(opts.start);
  118. 118const END = parseDate(opts.end);
  119. 119
  120. 120function category(rawCat, rawStage) {
  121. 121 const c = norm(rawCat || "");
  122. 122 const s = norm(rawStage || "");
  123. 123 if (/closed_?won|^won$/.test(s) || c === "closed" || c === "closed_won" || c === "won") return "closed_won";
  124. 124 if (/closed_?lost|^lost$/.test(s) || c === "closed_lost" || c === "lost") return "lost";
  125. 125 if (c.startsWith("omit")) return "omitted";
  126. 126 if (c === "commit") return "commit";
  127. 127 if (c === "best_case" || c === "bestcase" || c === "upside") return "best_case";
  128. 128 if (c === "pipeline") return "pipeline";
  129. 129 return null;
  130. 130}
  131. 131
  132. 132function load(path) {
  133. 133 let text;
  134. 134 try {
  135. 135 text = readFileSync(path, "utf8");
  136. 136 } catch {
  137. 137 fail(`cannot read ${path}`);
  138. 138 }
  139. 139 const { header, records } = parseCsv(text);
  140. 140 const cols = resolveColumns(header, path);
  141. 141 const deals = new Map();
  142. 142 const problems = [];
  143. 143 for (const r of records) {
  144. 144 const id = r[cols.id];
  145. 145 if (!id) {
  146. 146 problems.push("row with no id skipped");
  147. 147 continue;
  148. 148 }
  149. 149 const amount = Number(String(r[cols.amount] || "0").replace(/[$,\s]/g, ""));
  150. 150 const close = parseDate(r[cols.close_date]);
  151. 151 let cat = category(cols.forecast_category ? r[cols.forecast_category] : "", cols.stage ? r[cols.stage] : "");
  152. 152 if (!cat) {
  153. 153 cat = "pipeline";
  154. 154 problems.push(`${id}: no forecast category, counted as pipeline`);
  155. 155 }
  156. 156 if (!Number.isFinite(amount)) problems.push(`${id}: amount "${r[cols.amount]}" is not a number, counted as 0`);
  157. 157 if (!close) problems.push(`${id}: close date "${r[cols.close_date]}" not understood, left out of the period`);
  158. 158 deals.set(id, {
  159. 159 id,
  160. 160 name: cols.name ? r[cols.name] : id,
  161. 161 owner: (cols.owner && r[cols.owner]) || "(no owner)",
  162. 162 team: (cols.team && r[cols.team]) || "(no team)",
  163. 163 amount: Number.isFinite(amount) ? amount : 0,
  164. 164 close,
  165. 165 stage: cols.stage ? r[cols.stage] : "",
  166. 166 cat,
  167. 167 inPeriod: Boolean(close && close >= START && close <= END),
  168. 168 });
  169. 169 }
  170. 170 return { deals, cols, problems };
  171. 171}
  172. 172
  173. 173const cur = load(pos[0]);
  174. 174const prev = pos[1] ? load(pos[1]) : null;
  175. 175
  176. 176const CATS = ["closed_won", "commit", "best_case", "pipeline"];
  177. 177function rollup(deals, key) {
  178. 178 const groups = new Map();
  179. 179 const total = { closed_won: 0, commit: 0, best_case: 0, pipeline: 0, deals: 0 };
  180. 180 for (const d of deals.values()) {
  181. 181 if (!d.inPeriod || !CATS.includes(d.cat)) continue;
  182. 182 const k = key ? d[key] : "total";
  183. 183 if (!groups.has(k)) groups.set(k, { closed_won: 0, commit: 0, best_case: 0, pipeline: 0, deals: 0 });
  184. 184 for (const g of [groups.get(k), total]) {
  185. 185 g[d.cat] += d.amount;
  186. 186 g.deals++;
  187. 187 }
  188. 188 }
  189. 189 const finish = (g) => ({ ...g, forecast: g.closed_won + g.commit, upside: g.closed_won + g.commit + g.best_case });
  190. 190 return { total: finish(total), groups: Object.fromEntries([...groups.entries()].sort().map(([k, g]) => [k, finish(g)])) };
  191. 191}
  192. 192
  193. 193const byOwner = rollup(cur.deals, "owner");
  194. 194const byTeam = rollup(cur.deals, "team");
  195. 195const ownerTeam = {};
  196. 196for (const d of cur.deals.values()) ownerTeam[d.owner] = d.team;
  197. 197
  198. 198let wow = null;
  199. 199let changes = null;
  200. 200if (prev) {
  201. 201 const pOwner = rollup(prev.deals, "owner");
  202. 202 const diffOf = (a, b) => Object.fromEntries(["closed_won", "commit", "best_case", "pipeline", "forecast", "upside"].map((k) => [k, (a?.[k] || 0) - (b?.[k] || 0)]));
  203. 203 const owners = [...new Set([...Object.keys(byOwner.groups), ...Object.keys(pOwner.groups)])].sort();
  204. 204 wow = {
  205. 205 total: { last_week: pOwner.total, this_week: byOwner.total, change: diffOf(byOwner.total, pOwner.total) },
  206. 206 by_owner: Object.fromEntries(owners.map((o) => [o, diffOf(byOwner.groups[o], pOwner.groups[o])])),
  207. 207 };
  208. 208 const RANK = { pipeline: 1, best_case: 2, commit: 3, closed_won: 4 };
  209. 209 changes = { new: [], won: [], lost: [], slipped: [], pulled_in: [], upgraded: [], downgraded: [], amount_changed: [], dropped: [] };
  210. 210 for (const d of cur.deals.values()) {
  211. 211 const p = prev.deals.get(d.id);
  212. 212 const brief = { id: d.id, name: d.name, owner: d.owner, amount: d.amount, close_date: d.close, category: d.cat };
  213. 213 if (!p) {
  214. 214 if (d.inPeriod && d.cat !== "lost" && d.cat !== "omitted") changes.new.push(brief);
  215. 215 continue;
  216. 216 }
  217. 217 if (!p.inPeriod && !d.inPeriod) continue;
  218. 218 if (d.cat === "closed_won" && p.cat !== "closed_won") changes.won.push(brief);
  219. 219 else if ((d.cat === "lost" || d.cat === "omitted") && !(p.cat === "lost" || p.cat === "omitted"))
  220. 220 changes.lost.push({ ...brief, was_category: p.cat });
  221. 221 else {
  222. 222 if (d.close && p.close && d.close > p.close)
  223. 223 changes.slipped.push({ ...brief, was_close_date: p.close, out_of_period: p.inPeriod && !d.inPeriod });
  224. 224 if (d.close && p.close && d.close < p.close) changes.pulled_in.push({ ...brief, was_close_date: p.close, into_period: !p.inPeriod && d.inPeriod });
  225. 225 if (RANK[d.cat] && RANK[p.cat] && RANK[d.cat] > RANK[p.cat]) changes.upgraded.push({ ...brief, was_category: p.cat });
  226. 226 if (RANK[d.cat] && RANK[p.cat] && RANK[d.cat] < RANK[p.cat]) changes.downgraded.push({ ...brief, was_category: p.cat });
  227. 227 }
  228. 228 if (Math.abs(d.amount - p.amount) >= 0.005) changes.amount_changed.push({ ...brief, was_amount: p.amount, change: d.amount - p.amount });
  229. 229 }
  230. 230 for (const p of prev.deals.values())
  231. 231 if (!cur.deals.has(p.id) && p.inPeriod && CATS.includes(p.cat))
  232. 232 changes.dropped.push({ id: p.id, name: p.name, owner: p.owner, amount: p.amount, close_date: p.close, category: p.cat });
  233. 233}
  234. 234
  235. 235const result = {
  236. 236 period: { start: START, end: END },
  237. 237 columns_used: cur.cols,
  238. 238 totals: byOwner.total,
  239. 239 by_team: byTeam.groups,
  240. 240 by_owner: byOwner.groups,
  241. 241 owner_team: ownerTeam,
  242. 242 week_over_week: wow,
  243. 243 changes,
  244. 244 data_problems: [...cur.problems, ...(prev ? prev.problems.map((p) => `last week: ${p}`) : [])],
  245. 245};
  246. 246
  247. 247// ---------- markdown ----------
  248. 248const money = (n) => (n < 0 ? "-" : "") + "$" + Math.round(Math.abs(n)).toLocaleString("en-US");
  249. 249const delta = (n) => (n === 0 ? "0" : (n > 0 ? "+" : "-") + money(Math.abs(n)).replace("-", ""));
  250. 250const md = [`# Forecast rollup, ${START} to ${END}`, ""];
  251. 251const t = byOwner.total;
  252. 252md.push(`**Closed won ${money(t.closed_won)} | Commit ${money(t.commit)} | Forecast (won + commit) ${money(t.forecast)} | Best case ${money(t.best_case)} | Upside ${money(t.upside)} | Pipeline ${money(t.pipeline)}**`, "");
  253. 253if (wow) md.push(`Week over week: forecast ${delta(wow.total.change.forecast)}, best case ${delta(wow.total.change.best_case)}, pipeline ${delta(wow.total.change.pipeline)}.`, "");
  254. 254const table = (title, groups, extra) => {
  255. 255 const label = title.split(" ").pop();
  256. 256 md.push(`## ${title}`, "", `| ${label[0].toUpperCase() + label.slice(1)} | Won | Commit | Forecast | Best case | Upside | Pipeline | Deals${extra ? " | Forecast WoW" : ""} |`);
  257. 257 md.push(`| --- | ---: | ---: | ---: | ---: | ---: | ---: | ---: |${extra ? " ---: |" : ""}`);
  258. 258 for (const [k, g] of Object.entries(groups))
  259. 259 md.push(`| ${k} | ${money(g.closed_won)} | ${money(g.commit)} | ${money(g.forecast)} | ${money(g.best_case)} | ${money(g.upside)} | ${money(g.pipeline)} | ${g.deals} |${extra ? ` ${delta(extra[k]?.forecast || 0)} |` : ""}`);
  260. 260 md.push("");
  261. 261};
  262. 262table("By team", byTeam.groups);
  263. 263table("By rep", byOwner.groups, wow?.by_owner);
  264. 264if (changes) {
  265. 265 const section = (title, list, fmt) => {
  266. 266 if (!list.length) return;
  267. 267 md.push(`## ${title} (${list.length})`, "");
  268. 268 for (const d of list) md.push(`- ${fmt(d)}`);
  269. 269 md.push("");
  270. 270 };
  271. 271 const who = (d) => `${d.name} (${d.owner}, ${money(d.amount)})`;
  272. 272 section("Slipped", changes.slipped, (d) => `${who(d)}: ${d.was_close_date} to ${d.close_date}${d.out_of_period ? ", OUT OF PERIOD" : ""}`);
  273. 273 section("Downgraded", changes.downgraded, (d) => `${who(d)}: ${d.was_category} to ${d.category}`);
  274. 274 section("Lost", changes.lost, (d) => `${who(d)}: was ${d.was_category}`);
  275. 275 section("Dropped from the export", changes.dropped, (d) => `${who(d)}: was ${d.category}, closing ${d.close_date}`);
  276. 276 section("Won", changes.won, (d) => who(d));
  277. 277 section("Upgraded", changes.upgraded, (d) => `${who(d)}: ${d.was_category} to ${d.category}`);
  278. 278 section("Pulled in", changes.pulled_in, (d) => `${who(d)}: ${d.was_close_date} to ${d.close_date}${d.into_period ? ", INTO PERIOD" : ""}`);
  279. 279 section("New", changes.new, (d) => `${who(d)}: ${d.category}, closing ${d.close_date}`);
  280. 280 section("Amount changed", changes.amount_changed, (d) => `${who(d)}: ${delta(d.change)} (was ${money(d.was_amount)})`);
  281. 281}
  282. 282if (result.data_problems.length) {
  283. 283 md.push("## Data problems", "");
  284. 284 for (const p of result.data_problems.slice(0, 50)) md.push(`- ${p}`);
  285. 285 if (result.data_problems.length > 50) md.push(`- and ${result.data_problems.length - 50} more`);
  286. 286 md.push("");
  287. 287}
  288. 288
  289. 289mkdirSync(opts.out, { recursive: true });
  290. 290writeFileSync(join(opts.out, `${opts.name}.json`), JSON.stringify(result, null, 2) + "\n");
  291. 291writeFileSync(join(opts.out, `${opts.name}.md`), md.join("\n"));
  292. 292console.log(
  293. 293 `Forecast ${money(t.forecast)}, upside ${money(t.upside)}, pipeline ${money(t.pipeline)}${changes ? `; ${changes.slipped.length} slipped, ${changes.downgraded.length} downgraded` : ""}. Wrote ${join(opts.out, opts.name)}.{md,json}`,
  294. 294);
Sign in to comment on a line