AI Spend Analysis

ai-spend-analysis

Turn an organization's Anthropic spend into a person / department / product / model dashboard. The design rests on a three-surface model — three genuinely separate pools of money that must stay visually distinct everywhere:

Deeper framing, the plan-tier matrix, the security/privacy model, the data traps, and the roadmap live in references/dashboard-guide.md — read it once before the first run.

Two paths: is this a first run or a monthly run?

• config/pipeline.json does not exist in the workspace → run SETUP (below).
• It exists → run the MONTHLY pipeline (below).

Throughout: $SKILL = this skill's directory (the folder holding this file; its scripts/ subdir holds the pipeline). $ROOT = the operator's workspace folder (e.g. AI-Spend-Analytics/), which holds config/, raw/, workbooks/, artifacts/, .secrets/. The workspace is separate from the skill — it holds the org's data and is never committed anywhere.

Setup (first run — conversational)

Run it as a friendly first-run setup — ask, confirm, write; the operator mostly confirms rather than types.

1. Pick / confirm the workspace root $ROOT — a folder for this org's data (roster, monthly exports, dashboards). Suggest an easy-to-find spot like ~/Desktop/AI-Spend-Analytics, and keep it outside any git repo you might publish — it holds real per-person spend (a scaffolded .gitignore protects it, but living outside a repo is belt-and-suspenders). Scaffold it: python3 $SKILL/scripts/scaffold.py "$ROOT" — creates the folder tree and seeds config/ from $SKILL/config.example/ templates.
2. Ask for: company name; plan tier (team or enterprise); seat types and their monthly list prices; then write them into $ROOT/config/pipeline.json (company_name, plan_tier) and $ROOT/config/thresholds.json (standard_seat_rate_usd).
3. Org id (optional). Only the optional Team browser-automation shortcut uses it; the manual export path does not, so the operator can safely skip it. If they want the automation: copy it from their claude.ai admin URL → write it to pipeline.json org_id. (Team only; Enterprise's Analytics API doesn't use it.)
4. Roster — no roster needed up front. On the first run I read the people straight out of your spend export (bootstrap_roster.py) and write a fill-in roster at config/roster.xlsx — Excel, with a standard/premium dropdown (or config/roster.csv if openpyxl isn't installed). Emails and a name guess come pre-filled; the operator adds department and seat_type two ways: edit the sheet, or paste/attach their own roster and I fill it in for them. Per-seat dollars derive from seat_rates in pipeline.json, so there's no dollar column to touch. Every run then regenerates config/org_chart.csv + config/seats.csv from the roster (apply_roster.py). Bringing your own? Drop a config/roster.csv (email,name,department,seat_type), or hand-maintain org_chart.csv/seats.csv — with no roster file present the pipeline uses those CSVs as-is.
5. Model families — the shipped config/models.json classifies models into frontier | sonnet | haiku | other by regex. Confirm the patterns match the org's current model lines; it drives the what-if routing panel's colors and labels (no code changes needed to update).
6. Optional — Surface C (API/Console cash). If they want it, the operator places the key — never paste it in chat. Easiest: open the scaffolded $ROOT/PASTE_ADMIN_KEY_HERE.txt, paste the whole sk-ant-… key, save. On the next run secure_key.py moves it into $ROOT/.secrets/anthropic_admin_key (chmod 600, gitignored) and deletes the drop file — the key never touches chat, a command, or a log. (Env var ANTHROPIC_ADMIN_API_KEY also works.) Skipping it is fine — the dashboard shows "Surface C not configured". If a key ever appears in chat, refuse it, tell the operator to rotate it, and point them at the drop file. (Cowork has no secrets store yet — run Surface C from a local/CLI session or leave it off; see the reference guide.)
7. Validate by doing a first monthly run (below): pull the export, let me bootstrap the roster from it for the operator to fill, then run the pipeline and show the dashboard. (The very first build, before the roster is filled, shows everyone under "(unmapped)" — expected; it sharpens once the roster lands.)

Monthly run (target period = last full calendar month, YYYY-MM)

Step 1 — Surface B: the seat-overage export

Team plan (default): manual export is the primary, documented path. It works everywhere, including plain Claude Code with no browser. The operator: claude.ai → Settings → Analytics → "How much is Claude costing?" → Export spend report → Last Month, then drops the CSV at $ROOT/raw/<YYYY-MM>/overage_spend.csv and writes raw/<YYYY-MM>/meta.json: {"period":"YYYY-MM","days_covered":<n>,"partial":false,"source":"manual export <date>"}.

90-day window — do not skip a month. claude.ai only exports the trailing ~90 days; a missed month is unrecoverable. If a month is missing at run time, lead the summary with the gap — never backfill silently later.

Optional automation (Team, Claude-in-Chrome only): an authenticated in-page fetch of the same export over an unofficial endpoint (org_id + pattern in pipeline.json). It is the operator's own Owner session pulling their own export — same data as the button — but undocumented, so it can drift. Use only if a browser is available; fail loud (the validator + banner catch drift); never treat it as required. Details in references/dashboard-guide.md.

Enterprise plan: no browser step — run_pipeline.py (Step 2) fetches Surface B via the documented Analytics API (fetch_surface_b_enterprise.py). See the "Enterprise" caveat in the reference guide (built from docs, awaiting a live validator — see the roadmap).

On any failure (not logged in, non-200, non-CSV): do not fabricate or skip silently. Write meta.json anyway so the period registers; validation stamps the gap; the dashboard and summary lead with the "Surface B missing" banner.

Step 2 — Scripted remainder

python3 $SKILL/scripts/run_pipeline.py "$ROOT" YYYY-MM

In order: secure_key.py (ingest a dropped PASTE_ADMIN_KEY_HERE.txt, if present); (Enterprise only) fetch_surface_b_enterprise.py; then fetch_surface_c.py (Admin API via key; gracefully not_configured if unset), validate_surface_b.py (schema + sanity → surface_b_status.json), apply_roster.py (only when a config/roster.xlsx|csv exists — regenerates config/org_chart.csv + config/seats.csv from it), build_workbooks.py (rebuilds both .xlsx from all periods in raw/; writes build_summary.json), build_artifact.py (writes artifacts/<period>_ai_spend.html), draft_slack.py (writes artifacts/<period>_slack_draft.txt).

openpyxl is optional. Without it, the summary, dashboard, and Slack draft still build; only the two .xlsx workbooks are skipped. Install it for the Excel files: pip install openpyxl.

Step 3 — Verify before reporting

• workbooks/build_summary.json — confirm the new period appears with plausible row counts and the expected flags.
• If openpyxl is installed, recalculate ai_spend_output.xlsx (Excel/LibreOffice recalc on open) and require zero formula errors.
• Confirm artifacts/<period>_ai_spend.html exists and, if any surface status isn't ok, carries the right banner.

Step 4 — Deliver

• Present the artifact file and the Slack draft text verbatim to the operator.
• Never send the Slack message; never auto-send anything. If a Slack tool is connected, you may offer to stage a draft only. The operator sends it.

Hard rules

• Read-only on every financial surface. Never modify seats, plans, billing, or org settings; sessions/keys are used for GET-equivalent reads only.
• Secrets never leak — the operator places them, not the assistant. API keys live only in env vars or $ROOT/.secrets/ (gitignored). The friendly path is the PASTE_ADMIN_KEY_HERE.txt drop file → secure_key.py (file → file, never through chat or a command). Never solicit a key in chat; if one appears there, refuse it and tell the operator to rotate. Never write a key to config, workbook, artifact, log, or chat.
• Three surfaces stay visually distinct everywhere. Overage is usage-credit spend beyond allotments; in-allotment usage is tokens-only by design (not a bug).
• Fail loud. A missing surface produces a banner + first-line summary alert, never a silent gap. Empty months never pollute flag lookbacks.
• Config, not workbooks. New hires / seat changes go in the roster (config/roster.xlsx|csv) — or edit config/*.csv directly; new API keys go in config/api_key_map.csv. The workbooks and artifact fully regenerate every run.
• No real org data, no other vendors. This ships with synthetic fixtures only; it covers Anthropic/Claude spend, not OpenAI/Gemini (on the roadmap).

Files

Maintenance notes

• Seat prices in seats.csv are list-rate assumptions; update if actual invoices differ.
• Endpoint drift (Team automation): if the in-page spend-report fetch 404s, rediscover the endpoint via DevTools/network capture on the export modal and update pipeline.json. The manual export is always the safe fallback.
• Model lines evolve: update config/models.json patterns — no code change needed.