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

Gross margin by customer

@agentcosts/gross-margin-by-customer

Gross margin per customer for an AI product: revenue minus LLM cost minus allocated hosting and other cost of revenue. Flags negative and below-target customers, names the task driving their cost, and gives the price each one would need to hit your target margin.

SkillFinancev1.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// Gross margin by customer for an AI product: revenue minus LLM cost minus allocated other cost of revenue.
  3. 3// Usage: node scripts/margin.mjs <usage.csv> --revenue revenue.csv [--prices prices.csv] [--cogs cogs.csv]
  4. 4// [--target 0.7] [--out out/margin]
  5. 5// revenue.csv: customer, month (YYYY-MM), revenue
  6. 6// cogs.csv (optional): item, month, amount, allocation (usage | revenue | even | customer:<name>)
  7. 7import { fail, mdTable, money, monthKey, num, parseArgs, parseDate, pct, readCsv, round, toCsv, writeOut } from "./lib.mjs";
  8. 8import { loadPrices, readUsage, warnStalePrices } from "./usage.mjs";
  9. 9
  10. 10const args = parseArgs(process.argv.slice(2));
  11. 11const input = args._[0];
  12. 12if (!input || !args.revenue) fail("usage: node scripts/margin.mjs <usage.csv> --revenue revenue.csv [--prices prices.csv] [--cogs cogs.csv] [--target 0.7]");
  13. 13const target = Number(args.target ?? 0.7);
  14. 14const outPrefix = args.out || "out/margin";
  15. 15const prices = args.prices ? loadPrices(args.prices) : null;
  16. 16if (prices) warnStalePrices(prices, args.prices);
  17. 17const { rows } = readUsage(input, prices);
  18. 18
  19. 19const key = (c, m) => `${c}|${m}`;
  20. 20const cells = new Map();
  21. 21const cell = (c, m) => {
  22. 22 if (!cells.has(key(c, m))) cells.set(key(c, m), { customer: c, month: m, revenue: 0, llm_cost: 0, other_cogs: 0, tasks: new Map(), has_revenue: false });
  23. 23 return cells.get(key(c, m));
  24. 24};
  25. 25for (const r of rows) {
  26. 26 const x = cell(r.customer, monthKey(r.date));
  27. 27 x.llm_cost += r.cost;
  28. 28 x.tasks.set(r.task, (x.tasks.get(r.task) || 0) + r.cost);
  29. 29}
  30. 30for (const r of readCsv(args.revenue, "revenue CSV", ["customer", "month", "revenue"])) {
  31. 31 const d = parseDate(r.month);
  32. 32 const v = num(r.revenue);
  33. 33 if (!d || !Number.isFinite(v)) continue;
  34. 34 const x = cell(r.customer, monthKey(d));
  35. 35 x.revenue += v;
  36. 36 x.has_revenue = true;
  37. 37}
  38. 38const months = [...new Set([...cells.values()].map((c) => c.month))].sort();
  39. 39
  40. 40// Allocate other cost of revenue (hosting, vector DB, support tooling) to customers.
  41. 41const allocations = [];
  42. 42if (args.cogs) {
  43. 43 for (const r of readCsv(args.cogs, "cogs CSV", ["item", "month", "amount"])) {
  44. 44 const d = parseDate(r.month);
  45. 45 const amount = num(r.amount);
  46. 46 if (!d || !Number.isFinite(amount)) continue;
  47. 47 const m = monthKey(d);
  48. 48 const how = (r.allocation || "usage").toLowerCase();
  49. 49 const list = [...cells.values()].filter((c) => c.month === m && c.customer !== "internal");
  50. 50 if (!list.length) continue;
  51. 51 let weights;
  52. 52 if (how.startsWith("customer:")) {
  53. 53 const name = r.allocation.slice(9).trim();
  54. 54 weights = list.map((c) => (c.customer === name ? 1 : 0));
  55. 55 } else if (how === "revenue") weights = list.map((c) => c.revenue);
  56. 56 else if (how === "even") weights = list.map(() => 1);
  57. 57 else weights = list.map((c) => c.llm_cost);
  58. 58 const total = weights.reduce((a, b) => a + b, 0);
  59. 59 if (!total) {
  60. 60 process.stderr.write(`Could not allocate "${r.item}" in ${m}: nothing to allocate by (${how}).\n`);
  61. 61 continue;
  62. 62 }
  63. 63 list.forEach((c, i) => (c.other_cogs += (amount * weights[i]) / total));
  64. 64 allocations.push({ item: r.item, month: m, amount, allocation: how });
  65. 65 }
  66. 66}
  67. 67
  68. 68const results = [...cells.values()]
  69. 69 .map((c) => {
  70. 70 const cogs = c.llm_cost + c.other_cogs;
  71. 71 const gp = c.revenue - cogs;
  72. 72 const margin = c.revenue ? gp / c.revenue : null;
  73. 73 const topTask = [...c.tasks.entries()].sort((a, b) => b[1] - a[1])[0];
  74. 74 const status = !c.has_revenue ? (c.customer === "internal" ? "internal" : "no revenue row") : margin < 0 ? "negative" : margin < target ? "below target" : "ok";
  75. 75 return {
  76. 76 customer: c.customer,
  77. 77 month: c.month,
  78. 78 revenue: round(c.revenue, 2),
  79. 79 llm_cost: round(c.llm_cost, 2),
  80. 80 other_cogs: round(c.other_cogs, 2),
  81. 81 gross_profit: round(gp, 2),
  82. 82 gross_margin: margin === null ? null : round(margin, 4),
  83. 83 status,
  84. 84 top_cost_task: topTask ? topTask[0] : "",
  85. 85 top_cost_task_share: topTask && c.llm_cost ? round(topTask[1] / c.llm_cost, 4) : null,
  86. 86 price_for_target: c.has_revenue ? round(cogs / (1 - target), 2) : null,
  87. 87 };
  88. 88 })
  89. 89 .sort((a, b) => (a.month === b.month ? (a.gross_margin ?? 9) - (b.gross_margin ?? 9) : a.month < b.month ? -1 : 1));
  90. 90
  91. 91const totals = months.map((m) => {
  92. 92 const list = results.filter((r) => r.month === m && r.status !== "internal" && r.status !== "no revenue row");
  93. 93 const rev = list.reduce((a, r) => a + r.revenue, 0);
  94. 94 const cost = list.reduce((a, r) => a + r.llm_cost + r.other_cogs, 0);
  95. 95 const internal = results.filter((r) => r.month === m && (r.status === "internal" || r.status === "no revenue row")).reduce((a, r) => a + r.llm_cost, 0);
  96. 96 return { month: m, revenue: round(rev, 2), cost_of_revenue: round(cost, 2), gross_margin: rev ? round((rev - cost) / rev, 4) : null, unattributed_llm_cost: round(internal, 2), negative: list.filter((r) => r.status === "negative").map((r) => r.customer), below_target: list.filter((r) => r.status === "below target").map((r) => r.customer) };
  97. 97});
  98. 98
  99. 99writeOut(`${outPrefix}.csv`, toCsv(results, ["month", "customer", "revenue", "llm_cost", "other_cogs", "gross_profit", "gross_margin", "status", "top_cost_task", "top_cost_task_share", "price_for_target"]));
  100. 100writeOut(`${outPrefix}.json`, JSON.stringify({ target, totals, allocations, customers: results }, null, 2) + "\n");
  101. 101
  102. 102const md = [`# Gross margin by customer (target ${pct(target, 0)})`, ""];
  103. 103for (const t of totals) {
  104. 104 md.push(`## ${t.month}`, "");
  105. 105 md.push(`Revenue ${money(t.revenue)}, cost of revenue ${money(t.cost_of_revenue)}, gross margin ${t.gross_margin === null ? "n/a" : pct(t.gross_margin)}.${t.unattributed_llm_cost ? ` ${money(t.unattributed_llm_cost, 2)} of LLM cost has no revenue row (internal use, trials, or unmapped keys) and is not in the margin.` : ""}`, "");
  106. 106 md.push(
  107. 107 mdTable(
  108. 108 ["Customer", "Revenue", "LLM cost", "Other COGS", "Gross margin", "Status", "Biggest cost", `Price for ${pct(target, 0)}`],
  109. 109 results.filter((r) => r.month === t.month).map((r) => [r.customer, money(r.revenue), money(r.llm_cost, 2), money(r.other_cogs, 2), r.gross_margin === null ? "n/a" : pct(r.gross_margin), r.status === "negative" ? "**negative**" : r.status, r.top_cost_task ? `${r.top_cost_task} (${pct(r.top_cost_task_share, 0)})` : "", r.price_for_target === null ? "" : money(r.price_for_target)]),
  110. 110 ),
  111. 111 "",
  112. 112 );
  113. 113}
  114. 114if (allocations.length) md.push("## Other cost of revenue allocated", "", mdTable(["Item", "Month", "Amount", "Allocated by"], allocations.map((a) => [a.item, a.month, money(a.amount), a.allocation])), "");
  115. 115writeOut(`${outPrefix}.md`, md.join("\n"));
  116. 116
  117. 117process.stdout.write(`Wrote ${outPrefix}.md, ${outPrefix}.csv, ${outPrefix}.json\n`);
  118. 118for (const t of totals) process.stdout.write(`${t.month}: gross margin ${t.gross_margin === null ? "n/a" : pct(t.gross_margin)}; negative: ${t.negative.join(", ") || "none"}; below target: ${t.below_target.join(", ") || "none"}.\n`);
Sign in to comment on a line