# Build prompt: Claude Enterprise usage analytics dashboard

Copy everything below the line into Claude (Cowork or Claude Code, with the Google
Workspace connector enabled). It is written to be pasted in one go.

---

I want to build a usage analytics dashboard for my organisation's Claude
Enterprise plan, in Google Sheets, powered by Google Apps Script.

## Goal

A spreadsheet that accumulates our org's Claude usage data daily, so I can see
adoption trends over time: who is using it, which skills and connectors get
picked up, what we spend, and how that changes week to week. Raw history first,
charts second.

## Architecture I want

**Storage model: append-only history tabs, one row per record per day.** Not
snapshot tabs that overwrite. I want the full time series retained so I can
build any view I want later without re-querying the API.

Tabs to create:

| Tab | Source | Grain |
|---|---|---|
| Summary History | `/summaries` | one row per day, org-level |
| Users History | `/users` | one row per user per day |
| Skills History | `/skills` | one row per skill per day |
| Connectors History | `/connectors` | one row per connector per day |
| Projects History | `/apps/chat/projects` | one row per project per day |
| Tokens | `/user_usage_report` | one row per user per day |
| Spend | `/user_cost_report` | one row per user per day |
| Debug | raw API dumps | scratch |

## The API

Base URL: `https://api.anthropic.com/v1/organizations/analytics`

Auth: `x-api-key` header. The key needs the `read:analytics` scope — a key
without it returns 404, not 403, which is confusing. Generate it in the Anthropic
Console under your organisation's API keys.

Things to establish before you write the fetch layer:

- **Date parameter naming is not consistent across endpoints.** Some take a
  single `date`. Some take `starting_date` / `ending_date`. Some take
  `starting_at` / `ending_at` as full ISO timestamps. Do not assume — probe each
  endpoint and confirm.
- **End dates appear to be exclusive.** To fetch a single day you likely pass
  `date` and `date + 1`. Verify this rather than trusting it.
- **There is a processing lag of roughly 3 days** between a usage date and that
  data being available. Querying yesterday will return empty. Build a
  `latestAvailableDate()` helper rather than scattering date maths.
- **Earliest supported date is 2026-01-01.** Anything before that will fail.
- **Responses are paginated** with a `next_page` cursor. Follow it to exhaustion
  and sleep ~300ms between pages to stay clear of rate limits. A 429 means back
  off and retry.
- Retry once on 503 after a short sleep.

## Do this first, before writing the main code

Write a set of `debugXxxResponse()` functions — one per endpoint — that fetch a
tiny page (limit 2 or 3) and dump the raw JSON into the Debug tab, pretty
printed. Run them. Show me the actual response shapes.

Then, and only then, define the column mappings. Nested response objects should
be flattened to dot-paths (`chat_metrics.message_count`,
`claude_code_metrics.core_metrics.commit_count`, `actor.email`) and each tab
should have an explicit `[["Column header", "dot.path"]]` spec so I can see and
edit the mapping. Do not guess at field paths from the endpoint names — half of
them will be wrong and I will get silently blank columns.

## Behaviours the code must have

1. **Resumable backfill.** Apps Script hard-kills execution at 6 minutes. The
   backfill must track elapsed time, stop cleanly around the 5-minute mark, log
   where it stopped, and be safe to simply re-run to continue. Tell me it
   stopped rather than failing silently.
2. **Idempotent.** Before writing a date to a tab, check whether that date is
   already present in column A and skip if so. I will re-run these functions and
   they must not duplicate rows.
3. **Per-tab backfill functions**, not just one monolith. If Users History
   breaks I want to reload only that tab.
4. **Separate small daily trigger functions** rather than one function that does
   all seven tabs. Grouping everything into one daily run will hit the 6-minute
   limit as the org grows. Split into a few functions that each handle one or two
   tabs, and I will add a trigger per function.
5. **Never overwrite an existing header row.** Write headers only when the sheet
   is empty. Freeze the header row and the first two columns.
6. **Errors per date must not abort the run.** Log and continue to the next date.

## Security requirements

Do not hardcode the API key in the script. Read it from
`PropertiesService.getScriptProperties().getProperty("API_KEY")` and tell me how
to set it. Same for any spreadsheet IDs or org identifiers. This file will end up
in version control or pasted into a chat window, and a leaked analytics key is a
leaked view of my whole org's usage.

## A date bug to avoid

`new Date().toISOString().split("T")[0]` converts to UTC before formatting. If
the script runs in a timezone ahead of UTC, or late in the evening, you get
off-by-one dates and rows land on the wrong day. Use the script's configured
timezone explicitly — `Utilities.formatDate(d, Session.getScriptTimeZone(),
"yyyy-MM-dd")` — everywhere a date is stringified.

Also avoid `indices.indexOf(i)` inside a `.map()` over the full user list; it is
quadratic and will get slow past a few thousand rows. Build a lookup object.

## Setup flow I want at the end

Give me a numbered runbook, no more than six steps, covering: paste the code,
set the API key property, run a one-time setup function that creates the tabs,
run the debug functions to confirm field mappings, run the backfill (noting it
may need re-running), add the daily triggers.

## Ask me before you start

1. My organisation's earliest meaningful Claude usage date, so we don't backfill
   hundreds of empty days.
2. Whether I want Claude Code and Office agent metrics included, or just chat
   and Cowork.
3. Whether I want spend in the sheet at all — some orgs would rather not have
   per-person cost visible in a shared spreadsheet.

## Phase two, only after the data pipeline works

Once history is accumulating cleanly, help me build views on top: adoption curve
(daily and weekly active users against licensed seats), skill leaderboard with
week-over-week movement, connector attach rate, a list of licensed users with
zero activity in the last 30 days, and cost per active user over time. Build
these as formulas and charts reading from the history tabs, never as a second
set of API calls.

---

## Notes for whoever is handing this prompt over

- The tab list, endpoint paths and lag behaviour above are taken from a working
  implementation, not from documentation. Treat them as strong hints and let
  Claude confirm each one against the live API during the debug step. If
  Anthropic changes the analytics API, the debug-first pattern is what makes the
  build survive it.
- The single highest-value instruction in this prompt is "run the debug dumps
  before defining column mappings." Skipping it is the difference between a
  working sheet and forty columns of blanks.
- Nothing in this prompt contains organisation data, user emails, or API
  credentials. It is safe to share as-is.