Reconciling Historical Cost Data Across a Provider Migration

Two CSV exports, two schemas, two currencies, two billing periods, and a question from finance about whether the migration saved money. The work is not the join; it is deciding what the rows are supposed to mean before you write it.

Pick the grain first

Providers export at whatever grain suits their billing system: a daily-per-model rollup, an hourly usage series, a line item per invoice section, sometimes a per-request log behind a separate API. You cannot reconcile at a grain finer than the coarsest export you have, so decide up front and resist the temptation to go finer for one side.

Day-by-model-by-tenant is usually the right choice. It is coarse enough that every provider can produce it, fine enough to survive a cycle boundary landing mid-month, and it keeps the tenant dimension that makes the resulting numbers useful for anything other than a single total. If you have per-request records of your own — and you should, because they are the only source that is not vendor-shaped — keep them, but roll them to the same grain for the reconciliation rather than trying to match them to invoice lines one by one.

The common schema

One table, both providers, one row per grain cell. The important design decision is that quantities and prices are separate columns and money is derived, never stored as the only representation.

day            DATE     -- UTC calendar day
provider       TEXT
endpoint       TEXT     -- responses | messages | generateContent ...
model          TEXT     -- exact model string, not a family
tenant         TEXT
tier           TEXT     -- standard | batch | priority | cached
tokens_in      BIGINT   -- billable input, cache-exclusive
tokens_cache_w BIGINT
tokens_cache_r BIGINT
tokens_out     BIGINT
tokens_reason  BIGINT   -- subset of tokens_out where reported
requests       BIGINT
units_of_work  BIGINT   -- conversations, documents, tickets
price_in       NUMERIC  -- per token, from a dated price table
price_cache_w  NUMERIC
price_cache_r  NUMERIC
price_out      NUMERIC
currency       CHAR(3)  -- the currency of the price table
fx_to_base     NUMERIC  -- rate used, with its own date
source         TEXT     -- api_usage | invoice | own_logs

Three columns do the heavy lifting. tokens_in is defined as cache-exclusive so that the same column means the same thing on a provider whose usage object nests cached tokens inside the headline count and one whose usage object lists them as siblings — the direction of that correction is the subject of rebuilding the per-conversation metric, and getting it wrong here silently doubles or halves your input line. units_of_work is what makes a before-and-after comparison mean anything, because a month with more traffic costing more is not a finding. And source lets one cell exist twice, once from the API and once from the invoice, which is what turns a reconciliation into something you can check.

The normalisation, in order

1. Freeze the window. Choose a comparison period that excludes the switch month entirely — a full month before cutover and a full month after, once traffic has stabilised. The switch month contains partial bills from both sides and belongs in a separate line called migration cost, not in either comparison.
2. Pull both sides at the chosen grain from the providers’ usage APIs or exports, and pull your own request logs for the same window. Three sources, not two.
3. Map fields into the schema explicitly, one mapping file per provider, with a comment on every column that required a transformation. Do not map by column-name similarity: an input_tokens on one side and a prompt_tokens on the other may or may not include cached tokens, and the mapping file is where that decision gets recorded.
4. Normalise the calendar. Convert every timestamp to UTC before taking the day, and record which timezone the source used. A provider that closes its day at a local midnight will move roughly one twenty-fourth of your traffic across a day boundary relative to one that uses UTC, which is invisible in a monthly total and obvious in a daily chart.
5. Normalise currency with a stated policy. Pick one base currency and one rate convention — a single month-end rate, or the daily rate, or the rate your accounting system used — write it in the fx_to_base column with its date, and never re-derive historical rows with today’s rate. A comparison whose numbers change when you re-run it cannot be reviewed.
6. Attach a dated price table rather than a stored money amount. Prices change mid-period; a price table with validity dates lets you cost the same tokens under both providers, which is the counterfactual finance will ask for within a day of seeing the first version.
7. Reconcile API sums against invoices per provider, per month, and record the discrepancy as a percentage before you compare anything across providers. An unexplained gap on one side invalidates the comparison regardless of how clean the other side is.
8. Derive the metric per unit of work and only then compare. Cost per conversation, per ticket, per document — whatever your units_of_work counts — with a median and a 95th percentile rather than a mean.

Why the sum will not equal the invoice

Expect a gap, decide what size of gap is acceptable, and investigate only when it is exceeded. The usual causes, roughly in order of how often they explain it: requests that failed after being billed or succeeded without being logged by you; rounding, because providers price per million tokens and round somewhere you cannot see; credits, free tiers and committed-spend drawdowns applied at invoice level and absent from usage records; a cycle boundary that does not align with your calendar month; and taxes appearing on the invoice and not in usage.

A one per cent band is a reasonable starting tolerance for a token-derived sum against an invoice, tightened once you know your own data. What matters more than the number is that it is written down and monitored, because a reconciliation that is only performed when somebody is suspicious tells you nothing about when the drift started. The same discipline applies to your own request logs against the provider’s usage API: that gap is your logging bug, and it is worth knowing separately from the vendor gap. See cost attribution for the general problem of getting spend back to the thing that caused it.

The comparison that survives

Present three numbers per provider rather than one: cost per unit of work, tokens per unit of work, and effective price per token. They decompose the change. If cost per conversation fell while tokens per conversation rose, the new provider is cheaper per token and more verbose, and the saving will erode if output lengths keep growing. If tokens fell and the effective price rose, you gained on prompt design and lost on rate, which is a negotiation input rather than a migration result.

Keep the mapping files, the price tables and the FX policy in version control next to the query. The reconciliation will be re-run — at the next price change, at renewal, and the first time somebody disputes the result — and the value of this work is almost entirely in being able to re-run it and get the same answer.