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

Pipeline hygiene

@revopslab/pipeline-hygiene

Check open deals in a CRM export (HubSpot, Salesforce, or any CSV) for past close dates, deals stuck in a stage, no recent activity, missing required fields (also by stage), zero amounts, and close dates too far out. Writes a hygiene score and a fix list per rep you can send out.

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// pipeline-hygiene: check open opportunities in a CRM export for past close dates,
  3. 3// stale stages, no recent activity, missing fields, zero amounts, and close dates
  4. 4// too far out. Writes a per-rep fix list. Node 22, built-ins only.
  5. 5//
  6. 6// Usage:
  7. 7// node scripts/hygiene.mjs <opportunities.csv> [--config hygiene.json] [--today YYYY-MM-DD]
  8. 8// [--out out] [--name hygiene] [--map field=Column Name]...
  9. 9
  10. 10import { readFileSync, writeFileSync, mkdirSync } from "node:fs";
  11. 11import { join } from "node:path";
  12. 12
  13. 13function fail(msg) {
  14. 14 console.error(`pipeline-hygiene: ${msg}`);
  15. 15 process.exit(1);
  16. 16}
  17. 17
  18. 18const args = process.argv.slice(2);
  19. 19const opts = { out: "out", name: "hygiene", config: null, today: null, map: {} };
  20. 20const pos = [];
  21. 21for (let i = 0; i < args.length; i++) {
  22. 22 const a = args[i];
  23. 23 if (a === "--out") opts.out = args[++i];
  24. 24 else if (a === "--name") opts.name = args[++i];
  25. 25 else if (a === "--config") opts.config = args[++i];
  26. 26 else if (a === "--today") opts.today = args[++i];
  27. 27 else if (a === "--map") {
  28. 28 const [k, ...v] = String(args[++i]).split("=");
  29. 29 opts.map[k.trim()] = v.join("=").trim();
  30. 30 } else if (a === "--help" || a === "-h") {
  31. 31 console.log("Usage: node scripts/hygiene.mjs <opportunities.csv> [--config hygiene.json] [--today YYYY-MM-DD] [--out out] [--name hygiene] [--map field=Column]");
  32. 32 process.exit(0);
  33. 33 } else pos.push(a);
  34. 34}
  35. 35if (pos.length !== 1) fail("give one opportunity export CSV. Try --help.");
  36. 36
  37. 37const DEFAULTS = {
  38. 38 stale_days: 30,
  39. 39 activity_days: 14,
  40. 40 far_future_days: 365,
  41. 41 required: ["amount", "close_date", "owner", "next_step"],
  42. 42 required_by_stage: {},
  43. 43 closed_stages: ["closed won", "closed lost", "won", "lost", "closed"],
  44. 44};
  45. 45let config = { ...DEFAULTS };
  46. 46if (opts.config) {
  47. 47 try {
  48. 48 config = { ...DEFAULTS, ...JSON.parse(readFileSync(opts.config, "utf8")) };
  49. 49 } catch (e) {
  50. 50 fail(`cannot read config ${opts.config}: ${e.message}`);
  51. 51 }
  52. 52}
  53. 53
  54. 54function parseCsv(text) {
  55. 55 if (text.charCodeAt(0) === 0xfeff) text = text.slice(1);
  56. 56 const rows = [];
  57. 57 let row = [];
  58. 58 let field = "";
  59. 59 let q = false;
  60. 60 for (let i = 0; i < text.length; i++) {
  61. 61 const c = text[i];
  62. 62 if (q) {
  63. 63 if (c === '"') {
  64. 64 if (text[i + 1] === '"') {
  65. 65 field += '"';
  66. 66 i++;
  67. 67 } else q = false;
  68. 68 } else field += c;
  69. 69 } else if (c === '"') q = true;
  70. 70 else if (c === ",") {
  71. 71 row.push(field);
  72. 72 field = "";
  73. 73 } else if (c === "\n" || c === "\r") {
  74. 74 if (c === "\r" && text[i + 1] === "\n") i++;
  75. 75 row.push(field);
  76. 76 rows.push(row);
  77. 77 row = [];
  78. 78 field = "";
  79. 79 } else field += c;
  80. 80 }
  81. 81 if (field !== "" || row.length) {
  82. 82 row.push(field);
  83. 83 rows.push(row);
  84. 84 }
  85. 85 const data = rows.filter((r) => r.some((v) => v.trim() !== ""));
  86. 86 if (!data.length) return { header: [], records: [] };
  87. 87 const header = data.shift().map((h) => h.trim());
  88. 88 return { header, records: data.map((r) => Object.fromEntries(header.map((h, i) => [h, (r[i] ?? "").trim()]))) };
  89. 89}
  90. 90const norm = (s) => s.toLowerCase().replace(/[^a-z0-9]+/g, "_").replace(/^_|_$/g, "");
  91. 91const ALIASES = {
  92. 92 id: ["id", "opportunity_id", "deal_id", "record_id", "hs_object_id"],
  93. 93 name: ["name", "opportunity_name", "deal_name", "dealname"],
  94. 94 owner: ["owner", "opportunity_owner", "deal_owner", "owner_name", "hubspot_owner", "rep"],
  95. 95 amount: ["amount", "amount_converted", "deal_amount", "amount_usd"],
  96. 96 close_date: ["close_date", "closedate", "expected_close_date"],
  97. 97 stage: ["stage", "stage_name", "deal_stage", "dealstage"],
  98. 98 next_step: ["next_step", "nextstep", "next_steps", "hs_next_step"],
  99. 99 stage_entered: ["stage_entered", "stage_entered_date", "date_entered_current_stage", "last_stage_change_date", "stage_change_date"],
  100. 100 stage_days: ["stage_duration", "stage_duration_days", "days_in_stage", "time_in_stage_days"],
  101. 101 last_activity: ["last_activity", "last_activity_date", "last_activity_date_time", "notes_last_updated", "last_contacted"],
  102. 102};
  103. 103
  104. 104let text;
  105. 105try {
  106. 106 text = readFileSync(pos[0], "utf8");
  107. 107} catch {
  108. 108 fail(`cannot read ${pos[0]}`);
  109. 109}
  110. 110const { header, records } = parseCsv(text);
  111. 111const byNorm = new Map(header.map((h) => [norm(h), h]));
  112. 112const cols = {};
  113. 113for (const [f, list] of Object.entries(ALIASES)) {
  114. 114 if (opts.map[f]) {
  115. 115 if (!header.includes(opts.map[f])) fail(`--map ${f}=${opts.map[f]} names a column that does not exist`);
  116. 116 cols[f] = opts.map[f];
  117. 117 } else {
  118. 118 const hit = list.find((a) => byNorm.has(a));
  119. 119 if (hit) cols[f] = byNorm.get(hit);
  120. 120 }
  121. 121}
  122. 122for (const req of ["id", "stage"]) if (!cols[req]) fail(`no column found for "${req}". Columns are: ${header.join(", ")}. Use --map ${req}=<column>.`);
  123. 123
  124. 124function parseDate(s) {
  125. 125 if (!s) return null;
  126. 126 let m = s.match(/^(\d{4})-(\d{1,2})-(\d{1,2})/);
  127. 127 if (m) return Date.UTC(+m[1], +m[2] - 1, +m[3]);
  128. 128 m = s.match(/^(\d{1,2})\/(\d{1,2})\/(\d{4})/);
  129. 129 if (m) return Date.UTC(+m[3], +m[1] - 1, +m[2]);
  130. 130 return null;
  131. 131}
  132. 132const todayStr = opts.today || new Date().toISOString().slice(0, 10);
  133. 133const TODAY = parseDate(todayStr);
  134. 134if (TODAY === null) fail("--today must be YYYY-MM-DD");
  135. 135const days = (t) => Math.round((TODAY - t) / 86400000);
  136. 136const iso = (t) => new Date(t).toISOString().slice(0, 10);
  137. 137
  138. 138const closed = new Set(config.closed_stages.map((s) => s.toLowerCase()));
  139. 139const valueOf = (r, field) => (cols[field] ? r[cols[field]] : r[field] ?? r[Object.keys(r).find((k) => norm(k) === norm(field))] ?? "");
  140. 140
  141. 141const deals = [];
  142. 142let skippedClosed = 0;
  143. 143for (const r of records) {
  144. 144 const stage = r[cols.stage] || "";
  145. 145 if (closed.has(stage.toLowerCase())) {
  146. 146 skippedClosed++;
  147. 147 continue;
  148. 148 }
  149. 149 const issues = [];
  150. 150 const add = (code, detail) => issues.push({ code, detail });
  151. 151 const close = parseDate(valueOf(r, "close_date"));
  152. 152 const amountRaw = valueOf(r, "amount");
  153. 153 const amount = Number(String(amountRaw).replace(/[$,\s]/g, ""));
  154. 154
  155. 155 if (close !== null && close < TODAY) add("past_close_date", `close date ${iso(close)} is ${days(close)} days ago`);
  156. 156 if (close !== null && -days(close) > config.far_future_days) add("far_future_close", `close date ${iso(close)} is more than ${config.far_future_days} days out`);
  157. 157 if (amountRaw !== "" && Number.isFinite(amount) && amount <= 0) add("zero_amount", "amount is 0");
  158. 158
  159. 159 let inStage = null;
  160. 160 if (cols.stage_days && r[cols.stage_days] !== "") inStage = Number(r[cols.stage_days]);
  161. 161 else if (cols.stage_entered) {
  162. 162 const t = parseDate(r[cols.stage_entered]);
  163. 163 if (t !== null) inStage = days(t);
  164. 164 }
  165. 165 if (inStage !== null && Number.isFinite(inStage) && inStage > config.stale_days) add("stale_stage", `${inStage} days in ${stage}`);
  166. 166
  167. 167 if (cols.last_activity) {
  168. 168 const t = parseDate(r[cols.last_activity]);
  169. 169 if (t === null) add("no_activity_logged", "no activity date");
  170. 170 else if (days(t) > config.activity_days) add("no_recent_activity", `last activity ${days(t)} days ago`);
  171. 171 }
  172. 172
  173. 173 const required = [...config.required, ...(Object.entries(config.required_by_stage).find(([s]) => s.toLowerCase() === stage.toLowerCase())?.[1] || [])];
  174. 174 const missing = required.filter((f) => String(valueOf(r, f)).trim() === "");
  175. 175 if (missing.length) add("missing_fields", missing.join(", "));
  176. 176
  177. 177 deals.push({
  178. 178 id: r[cols.id],
  179. 179 name: cols.name ? r[cols.name] : r[cols.id],
  180. 180 owner: (cols.owner && r[cols.owner]) || "(no owner)",
  181. 181 stage,
  182. 182 amount: Number.isFinite(amount) ? amount : 0,
  183. 183 close_date: close !== null ? iso(close) : null,
  184. 184 issues,
  185. 185 });
  186. 186}
  187. 187
  188. 188const CODES = ["past_close_date", "stale_stage", "no_recent_activity", "no_activity_logged", "missing_fields", "zero_amount", "far_future_close"];
  189. 189const issueCounts = Object.fromEntries(CODES.map((c) => [c, deals.filter((d) => d.issues.some((i) => i.code === c)).length]));
  190. 190const owners = {};
  191. 191for (const d of deals) {
  192. 192 owners[d.owner] ||= { open: 0, clean: 0, with_issues: 0, amount_with_issues: 0 };
  193. 193 const o = owners[d.owner];
  194. 194 o.open++;
  195. 195 if (d.issues.length) {
  196. 196 o.with_issues++;
  197. 197 o.amount_with_issues += d.amount;
  198. 198 } else o.clean++;
  199. 199}
  200. 200const clean = deals.filter((d) => !d.issues.length).length;
  201. 201const result = {
  202. 202 today: todayStr,
  203. 203 config: { stale_days: config.stale_days, activity_days: config.activity_days, far_future_days: config.far_future_days, required: config.required },
  204. 204 columns_used: cols,
  205. 205 open_deals: deals.length,
  206. 206 closed_skipped: skippedClosed,
  207. 207 clean_deals: clean,
  208. 208 hygiene_score: deals.length ? Math.round((clean / deals.length) * 100) : 100,
  209. 209 issue_counts: issueCounts,
  210. 210 by_owner: Object.fromEntries(Object.entries(owners).sort((a, b) => b[1].with_issues - a[1].with_issues)),
  211. 211 deals: deals.filter((d) => d.issues.length).sort((a, b) => b.issues.length - a.issues.length || b.amount - a.amount),
  212. 212 checks_skipped: [
  213. 213 ...(cols.stage_entered || cols.stage_days ? [] : ["stale_stage (no stage-entered date or days-in-stage column)"]),
  214. 214 ...(cols.last_activity ? [] : ["no_recent_activity (no last activity column)"]),
  215. 215 ],
  216. 216};
  217. 217
  218. 218const money = (n) => "$" + Math.round(n).toLocaleString("en-US");
  219. 219const LABEL = {
  220. 220 past_close_date: "Close date in the past",
  221. 221 stale_stage: `Stuck in stage over ${config.stale_days} days`,
  222. 222 no_recent_activity: `No activity in ${config.activity_days}+ days`,
  223. 223 no_activity_logged: "No activity logged",
  224. 224 missing_fields: "Missing required fields",
  225. 225 zero_amount: "Zero amount",
  226. 226 far_future_close: `Close date over ${config.far_future_days} days out`,
  227. 227};
  228. 228const md = [`# Pipeline hygiene, ${todayStr}`, ""];
  229. 229md.push(`**Hygiene score ${result.hygiene_score}%**: ${clean} of ${deals.length} open deals are clean.`, "");
  230. 230md.push("| Issue | Deals |", "| --- | ---: |");
  231. 231for (const c of CODES) if (issueCounts[c]) md.push(`| ${LABEL[c]} | ${issueCounts[c]} |`);
  232. 232md.push("", "## By rep", "", "| Rep | Open | With issues | Amount with issues |", "| --- | ---: | ---: | ---: |");
  233. 233for (const [o, v] of Object.entries(result.by_owner)) md.push(`| ${o} | ${v.open} | ${v.with_issues} | ${money(v.amount_with_issues)} |`);
  234. 234md.push("", "## Fix list");
  235. 235let lastOwner = null;
  236. 236for (const d of [...result.deals].sort((a, b) => a.owner.localeCompare(b.owner))) {
  237. 237 if (d.owner !== lastOwner) {
  238. 238 md.push("", `### ${d.owner}`, "");
  239. 239 lastOwner = d.owner;
  240. 240 }
  241. 241 md.push(`- **${d.name}** (${d.stage}, ${money(d.amount)}): ${d.issues.map((i) => `${LABEL[i.code]}: ${i.detail}`).join("; ")}`);
  242. 242}
  243. 243if (result.checks_skipped.length) md.push("", "## Checks skipped", "", ...result.checks_skipped.map((s) => `- ${s}`));
  244. 244md.push("");
  245. 245
  246. 246const csvCell = (s) => (/[",\n]/.test(String(s)) ? `"${String(s).replace(/"/g, '""')}"` : String(s));
  247. 247const csv = ["id,name,owner,stage,amount,close_date,issues"]
  248. 248 .concat(result.deals.map((d) => [d.id, d.name, d.owner, d.stage, d.amount, d.close_date ?? "", d.issues.map((i) => `${i.code}: ${i.detail}`).join(" | ")].map(csvCell).join(",")))
  249. 249 .join("\n");
  250. 250
  251. 251mkdirSync(opts.out, { recursive: true });
  252. 252writeFileSync(join(opts.out, `${opts.name}.json`), JSON.stringify(result, null, 2) + "\n");
  253. 253writeFileSync(join(opts.out, `${opts.name}.md`), md.join("\n"));
  254. 254writeFileSync(join(opts.out, `${opts.name}-fix-list.csv`), csv + "\n");
  255. 255console.log(`Hygiene score ${result.hygiene_score}% (${clean}/${deals.length} clean). Wrote ${join(opts.out, opts.name)}.{md,json} and ${opts.name}-fix-list.csv`);
Sign in to comment on a line