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

Crm field audit

@revopslab/crm-field-audit

Audit the fields in any CRM export (HubSpot, Salesforce, or other CSV). Reports fill rates, placeholder junk like n/a and unknown, top values, and flags empty, sparse, constant, default-heavy, and likely duplicate fields, plus fill rates by owner or team. Ends with a cleanup plan: what to hide, merge, make required, or fix.

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// crm-field-audit: profile every column of a CRM export. Fill rates, placeholder
  3. 3// values, distinct counts, top values, inferred types, and flags for empty,
  4. 4// sparse, constant, default-heavy, and likely duplicate fields. Optional fill
  5. 5// rates by group (for example by owner). Node 22, built-ins only.
  6. 6//
  7. 7// Usage:
  8. 8// node scripts/field-audit.mjs <export.csv> [--sparse 10] [--group-by "Owner"]
  9. 9// [--key-fields "Industry,Amount"] [--out out] [--name field-audit]
  10. 10
  11. 11import { readFileSync, writeFileSync, mkdirSync } from "node:fs";
  12. 12import { join } from "node:path";
  13. 13
  14. 14function fail(msg) {
  15. 15 console.error(`crm-field-audit: ${msg}`);
  16. 16 process.exit(1);
  17. 17}
  18. 18
  19. 19const args = process.argv.slice(2);
  20. 20const opts = { out: "out", name: "field-audit", sparse: 10, groupBy: null, keys: [] };
  21. 21const pos = [];
  22. 22for (let i = 0; i < args.length; i++) {
  23. 23 const a = args[i];
  24. 24 if (a === "--out") opts.out = args[++i];
  25. 25 else if (a === "--name") opts.name = args[++i];
  26. 26 else if (a === "--sparse") opts.sparse = Number(args[++i]);
  27. 27 else if (a === "--group-by") opts.groupBy = args[++i];
  28. 28 else if (a === "--key-fields") opts.keys = String(args[++i]).split(",").map((s) => s.trim()).filter(Boolean);
  29. 29 else if (a === "--help" || a === "-h") {
  30. 30 console.log('Usage: node scripts/field-audit.mjs <export.csv> [--sparse 10] [--group-by "Owner"] [--key-fields "A,B"] [--out out] [--name field-audit]');
  31. 31 process.exit(0);
  32. 32 } else pos.push(a);
  33. 33}
  34. 34if (pos.length !== 1) fail("give one CSV export. Try --help.");
  35. 35if (!Number.isFinite(opts.sparse) || opts.sparse < 0 || opts.sparse > 100) fail("--sparse must be a percent between 0 and 100");
  36. 36
  37. 37function parseCsv(text) {
  38. 38 if (text.charCodeAt(0) === 0xfeff) text = text.slice(1);
  39. 39 const rows = [];
  40. 40 let row = [];
  41. 41 let field = "";
  42. 42 let q = false;
  43. 43 for (let i = 0; i < text.length; i++) {
  44. 44 const c = text[i];
  45. 45 if (q) {
  46. 46 if (c === '"') {
  47. 47 if (text[i + 1] === '"') {
  48. 48 field += '"';
  49. 49 i++;
  50. 50 } else q = false;
  51. 51 } else field += c;
  52. 52 } else if (c === '"') q = true;
  53. 53 else if (c === ",") {
  54. 54 row.push(field);
  55. 55 field = "";
  56. 56 } else if (c === "\n" || c === "\r") {
  57. 57 if (c === "\r" && text[i + 1] === "\n") i++;
  58. 58 row.push(field);
  59. 59 rows.push(row);
  60. 60 row = [];
  61. 61 field = "";
  62. 62 } else field += c;
  63. 63 }
  64. 64 if (field !== "" || row.length) {
  65. 65 row.push(field);
  66. 66 rows.push(row);
  67. 67 }
  68. 68 const data = rows.filter((r) => r.some((v) => v.trim() !== ""));
  69. 69 if (!data.length) return { header: [], records: [] };
  70. 70 const header = data.shift().map((h) => h.trim());
  71. 71 return { header, records: data.map((r) => header.map((_, i) => (r[i] ?? "").trim())) };
  72. 72}
  73. 73
  74. 74let text;
  75. 75try {
  76. 76 text = readFileSync(pos[0], "utf8");
  77. 77} catch {
  78. 78 fail(`cannot read ${pos[0]}`);
  79. 79}
  80. 80const { header, records } = parseCsv(text);
  81. 81if (!header.length) fail("the file is empty");
  82. 82const n = records.length;
  83. 83if (n === 0) fail("the file has a header but no rows");
  84. 84for (const k of [...opts.keys, ...(opts.groupBy ? [opts.groupBy] : [])])
  85. 85 if (!header.includes(k)) fail(`column "${k}" not found. Columns are: ${header.join(", ")}`);
  86. 86
  87. 87const PLACEHOLDERS = new Set(["n/a", "none", "null", "nil", "-", "--", "tbd", "unknown", "test", "x", ".", "?", "not set", "not provided", "other - please specify", "select one", "--none--"]);
  88. 88const isPlaceholder = (v) => PLACEHOLDERS.has(v.toLowerCase());
  89. 89
  90. 90function inferType(values) {
  91. 91 if (!values.length) return "empty";
  92. 92 const share = (re) => values.filter((v) => re.test(v)).length / values.length;
  93. 93 if (share(/^(true|false|yes|no|y|n|0|1)$/i) >= 0.95 && new Set(values.map((v) => v.toLowerCase())).size <= 4) return "boolean";
  94. 94 if (share(/^-?[$€£]?\s?-?[\d,]+(\.\d+)?%?$/) >= 0.95) return "number";
  95. 95 if (share(/^\d{4}-\d{2}-\d{2}|^\d{1,2}\/\d{1,2}\/\d{2,4}/) >= 0.95) return "date";
  96. 96 if (share(/^[^@\s]+@[^@\s]+\.[^@\s]+$/) >= 0.95) return "email";
  97. 97 if (share(/^(https?:\/\/|www\.)/i) >= 0.95) return "url";
  98. 98 const distinct = new Set(values).size;
  99. 99 if (distinct <= 25 && values.length >= distinct * 2) return "picklist";
  100. 100 return "text";
  101. 101}
  102. 102
  103. 103const fields = header.map((name, col) => {
  104. 104 const raw = records.map((r) => r[col]);
  105. 105 const placeholders = raw.filter((v) => v !== "" && isPlaceholder(v)).length;
  106. 106 const values = raw.filter((v) => v !== "" && !isPlaceholder(v));
  107. 107 const counts = new Map();
  108. 108 for (const v of values) counts.set(v, (counts.get(v) || 0) + 1);
  109. 109 const type = inferType(values);
  110. 110 const top = [...counts.entries()].sort((a, b) => b[1] - a[1] || a[0].localeCompare(b[0])).slice(0, 3);
  111. 111 const fill = (values.length / n) * 100;
  112. 112 const flags = [];
  113. 113 const idName = /(^|[^a-z])id$|^id\b|record.?id|uuid|\bkey$/i.test(name);
  114. 114 const identifier = values.length === n && counts.size === n && n > 1 && (idName || ["text", "email", "url"].includes(type));
  115. 115 if (values.length === 0) flags.push("empty");
  116. 116 else if (fill < opts.sparse) flags.push("sparse");
  117. 117 if (!identifier && values.length > 1 && counts.size === 1) flags.push("constant");
  118. 118 if (counts.size > 1 && top[0] && top[0][1] / values.length >= 0.9 && values.length >= 10) flags.push("default_heavy");
  119. 119 if (placeholders > 0 && placeholders / n >= 0.05) flags.push("placeholders");
  120. 120 return {
  121. 121 field: name,
  122. 122 type: identifier ? "identifier" : type,
  123. 123 filled: values.length,
  124. 124 fill_rate: Math.round(fill * 10) / 10,
  125. 125 placeholders,
  126. 126 distinct: counts.size,
  127. 127 top_values: top.map(([v, c]) => ({ value: v.length > 60 ? v.slice(0, 57) + "..." : v, count: c, share: Math.round((c / values.length) * 1000) / 10 })),
  128. 128 flags,
  129. 129 key_field: opts.keys.includes(name),
  130. 130 _col: col,
  131. 131 };
  132. 132});
  133. 133
  134. 134// likely duplicates: similar names, or the same values on rows where both are filled
  135. 135const base = (s) =>
  136. 136 s
  137. 137 .toLowerCase()
  138. 138 .replace(/__c$|_c$/, "")
  139. 139 .replace(/\b(custom|new|old|copy|legacy|v\d)\b/g, "")
  140. 140 .replace(/\d+$/, "")
  141. 141 .replace(/[^a-z]+/g, "");
  142. 142const duplicates = [];
  143. 143for (let i = 0; i < fields.length; i++)
  144. 144 for (let j = i + 1; j < fields.length; j++) {
  145. 145 const a = fields[i];
  146. 146 const b = fields[j];
  147. 147 if (a.type === "identifier" || b.type === "identifier") continue;
  148. 148 const reasons = [];
  149. 149 if (base(a.field) && base(a.field) === base(b.field)) reasons.push("similar names");
  150. 150 let both = 0;
  151. 151 let same = 0;
  152. 152 for (const r of records) {
  153. 153 const x = r[a._col];
  154. 154 const y = r[b._col];
  155. 155 if (x && y && !isPlaceholder(x) && !isPlaceholder(y)) {
  156. 156 both++;
  157. 157 if (x.toLowerCase() === y.toLowerCase()) same++;
  158. 158 }
  159. 159 }
  160. 160 if (both >= 5 && same / both >= 0.95 && a.distinct > 1) reasons.push(`same value on ${same} of ${both} rows where both are filled`);
  161. 161 if (reasons.length) duplicates.push({ fields: [a.field, b.field], fill_rates: [a.fill_rate, b.fill_rate], reasons });
  162. 162 }
  163. 163for (const d of duplicates) for (const f of fields) if (d.fields.includes(f.field) && !f.flags.includes("possible_duplicate")) f.flags.push("possible_duplicate");
  164. 164
  165. 165// fill rate by group
  166. 166let byGroup = null;
  167. 167if (opts.groupBy) {
  168. 168 const gCol = header.indexOf(opts.groupBy);
  169. 169 const target = opts.keys.length ? fields.filter((f) => f.key_field) : fields.filter((f) => f.field !== opts.groupBy && f.type !== "identifier");
  170. 170 const groups = new Map();
  171. 171 for (const r of records) {
  172. 172 const g = r[gCol] || "(blank)";
  173. 173 if (!groups.has(g)) groups.set(g, []);
  174. 174 groups.get(g).push(r);
  175. 175 }
  176. 176 byGroup = Object.fromEntries(
  177. 177 [...groups.entries()].sort().map(([g, rows]) => {
  178. 178 const rates = Object.fromEntries(
  179. 179 target.map((f) => [f.field, Math.round((rows.filter((r) => r[f._col] && !isPlaceholder(r[f._col])).length / rows.length) * 1000) / 10]),
  180. 180 );
  181. 181 const avg = Object.values(rates).reduce((a, b) => a + b, 0) / (target.length || 1);
  182. 182 return [g, { rows: rows.length, average_fill_rate: Math.round(avg * 10) / 10, fill_rates: rates }];
  183. 183 }),
  184. 184 );
  185. 185}
  186. 186
  187. 187for (const f of fields) delete f._col;
  188. 188const count = (flag) => fields.filter((f) => f.flags.includes(flag)).length;
  189. 189const result = {
  190. 190 file_rows: n,
  191. 191 fields: fields.length,
  192. 192 sparse_threshold_pct: opts.sparse,
  193. 193 summary: {
  194. 194 empty: count("empty"),
  195. 195 sparse: count("sparse"),
  196. 196 constant: count("constant"),
  197. 197 default_heavy: count("default_heavy"),
  198. 198 placeholders: count("placeholders"),
  199. 199 possible_duplicates: duplicates.length,
  200. 200 healthy: fields.filter((f) => f.flags.length === 0).length,
  201. 201 },
  202. 202 key_fields: fields.filter((f) => f.key_field).map((f) => ({ field: f.field, fill_rate: f.fill_rate })),
  203. 203 duplicates,
  204. 204 by_group: byGroup,
  205. 205 field_profiles: fields,
  206. 206};
  207. 207
  208. 208const md = [`# CRM field audit`, ""];
  209. 209md.push(`${n} records, ${fields.length} fields. Empty ${result.summary.empty}, sparse (under ${opts.sparse}%) ${result.summary.sparse}, constant ${result.summary.constant}, default-heavy ${result.summary.default_heavy}, placeholder values ${result.summary.placeholders}, possible duplicate pairs ${duplicates.length}.`, "");
  210. 210if (result.key_fields.length) {
  211. 211 md.push("## Key fields", "", "| Field | Fill rate |", "| --- | ---: |");
  212. 212 for (const k of result.key_fields) md.push(`| ${k.field} | ${k.fill_rate}% |`);
  213. 213 md.push("");
  214. 214}
  215. 215md.push("## All fields (lowest fill first)", "", "| Field | Type | Fill | Distinct | Top value | Flags |", "| --- | --- | ---: | ---: | --- | --- |");
  216. 216for (const f of [...fields].sort((a, b) => a.fill_rate - b.fill_rate || a.field.localeCompare(b.field)))
  217. 217 md.push(`| ${f.field} | ${f.type} | ${f.fill_rate}% | ${f.distinct} | ${f.top_values[0] ? `${f.top_values[0].value} (${f.top_values[0].share}%)` : ""} | ${f.flags.join(", ")} |`);
  218. 218md.push("");
  219. 219if (duplicates.length) {
  220. 220 md.push("## Possible duplicates", "");
  221. 221 for (const d of duplicates) md.push(`- ${d.fields.join(" and ")} (${d.fill_rates.join("% and ")}%): ${d.reasons.join("; ")}`);
  222. 222 md.push("");
  223. 223}
  224. 224if (byGroup) {
  225. 225 md.push(`## Fill rate by ${opts.groupBy}`, "", `| ${opts.groupBy} | Records | Average fill |`, "| --- | ---: | ---: |");
  226. 226 for (const [g, v] of Object.entries(byGroup)) md.push(`| ${g} | ${v.rows} | ${v.average_fill_rate}% |`);
  227. 227 md.push("");
  228. 228}
  229. 229
  230. 230mkdirSync(opts.out, { recursive: true });
  231. 231writeFileSync(join(opts.out, `${opts.name}.json`), JSON.stringify(result, null, 2) + "\n");
  232. 232writeFileSync(join(opts.out, `${opts.name}.md`), md.join("\n"));
  233. 233console.log(`${fields.length} fields audited over ${n} records: ${result.summary.healthy} healthy. Wrote ${join(opts.out, opts.name)}.{md,json}`);
Sign in to comment on a line