OpenAI cost per Stripe customer: a SQL reconciliation recipe

How do I calculate OpenAI cost per Stripe customer?

1. Link the verified Stripe customer and server-side model calls to the same internal account ID.
2. For one reporting period and currency, aggregate net payments and priced usage independently.
3. Join those account totals; subtract cost from net revenue only when both sides are complete. Keep unknown or unmatched records visible.

The SQL and six-row example below make this check reproducible without signing up or uploading customer data. The result is contribution after the included costs, not accounting profit.

Start with the account, not the email

This recipe is for a live AI product with paid customers, a verified Stripe source and server-observed OpenAI usage. Use the Stripe identity recipe to link the payer to an internal account, and the official OpenAI client streaming recipe to attach that account to usage. Browser analytics alone cannot observe server model cost. GOD native collection does not require GA4.

The JavaScript wrapper supports the official OpenAI and Anthropic clients. This is not a native Vercel AI SDK or AI Gateway adapter. Never wrap an unverified interface or infer successful task outcomes from a completed model response.

Prepare two same-window inputs

This is an example staging schema, not a query against Grow or Die's internal database. Export your own deduplicated, authorized records into payment_rows(account_id, currency, net_revenue) and usage_rows(account_id, currency, cost). IDs are opaque and non-null; amounts are decimal currency values in one reporting window. Payment amounts are already net of the refunds and fees your definition includes. Never subtract those again.

A null amount means unknown, not zero. A missing payment row is unknown revenue unless an independently complete source proves there was no payment. Do not combine currencies or silently convert them with today's exchange rate. Apply your reporting-window filter before this query.

Copy the reconciliation template

Download the SQL template. Run it in your own staging environment after preparing the two inputs above; no customer data is uploaded by this page.

WITH revenue AS (
  SELECT account_id, currency, SUM(net_revenue) AS net_revenue,
         COUNT(*) AS rows_seen, COUNT(net_revenue) AS rows_known
  FROM payment_rows GROUP BY account_id, currency
), costs AS (
  SELECT account_id, currency, SUM(cost) AS ai_cost,
         COUNT(*) AS rows_seen, COUNT(cost) AS rows_known
  FROM usage_rows GROUP BY account_id, currency
), accounts AS (
  SELECT account_id, currency FROM revenue
  UNION SELECT account_id, currency FROM costs
)
SELECT a.account_id, a.currency,
       CASE WHEN r.rows_seen = r.rows_known THEN r.net_revenue END AS net_revenue,
       CASE WHEN c.rows_seen = c.rows_known THEN c.ai_cost END AS ai_cost,
       CASE WHEN r.rows_seen = r.rows_known AND c.rows_seen = c.rows_known
            THEN r.net_revenue - c.ai_cost END AS contribution
FROM accounts a
LEFT JOIN revenue r USING (account_id, currency)
LEFT JOIN costs c USING (account_id, currency)
ORDER BY a.account_id, a.currency;

The account-and-currency union keeps revenue-only and usage-only accounts visible. A contribution value is produced only if both sides exist and every amount on both sides is known. This prevents a partial sum from looking complete.

Test it before trusting the result

Synthetic fixture v1 · 2026-10-03 · no customer records. Download the SQLite input fixture, the query, the expected intermediate evidence, and the expected final JSON output into one directory. Use SQLite 3.33 or later for JSON output, in an empty in-memory database:

sqlite3 -json :memory: ".read account-ai-cost-fixture.sql" ".read account-ai-cost-reconciliation.sql"

Follow account A from raw input to contribution

The two-day UTC example window starts September 1, 2026. Explicit identity links map payer cus_a and server user usr_a to account a, without inferring identity from a name or email. A $24 payment minus $1 tax and $3 observed fee produces $20; a separate $5 refund leaves $15. These fee fields are supplied synthetic evidence, not a claim that every billing connector supplies them.

Each request has 1,000,000 input and 500,000 output tokens. The fictional model's sample-v1 price is $1/input-million and $2/output-million, producing $2. At September 2 00:00 UTC, sample-v2 changes input to $2/million, producing $3. The query matches the price effective at request time with an inclusive start and exclusive end. These invented rates are not a current provider price list. The $15 revenue minus $5 usage cost yields $10 contribution, not accounting profit.

Unknown is SQL NULL, not zero. Never add the EUR and USD rows without a separately documented conversion policy. The table is checked against the downloadable query's actual output.

sqlite3 -json :memory: ".read account-ai-cost-fixture.sql" "SELECT * FROM payment_evidence ORDER BY event_id;" "SELECT * FROM usage_evidence ORDER BY event_id;"

Compare those two result arrays with the intermediate evidence download. Account A's separate pre-normalized EUR row has €8 revenue and €1 cost, yielding €7 without FX. B's unknown model has no price version and keeps null cost; C has only usage, D unknown revenue, and E only revenue. All four retain null contribution rather than assuming a missing side is zero. One unmapped server user remains visible in unmatched_usage and is not assigned to any account. The automated regression executes these exact public files and checks intermediate evidence and all six final rows.

For production accounting precision, use currency minor units or a database decimal type rather than binary floating point. This bounded fixture is not a general importer or a completeness check; refunds, identity verification and non-overlapping price windows need independent evidence. It intentionally creates tables and should never be run against a production database.

Turn an unmatched row into a concrete task

1. Missing payer link: verify the server-side account-to-payment identity.
2. Unknown model cost: recover provider usage and the price effective at request time.
3. Duplicate rows: enforce event idempotency before aggregation.
4. Currency disagreement: reconcile in the original currency, with an explicit FX policy only if needed.

See the Stripe identity recipe, Lemon Squeezy identity recipe, and price versioning method.

What this evidence can and cannot prove

The query cannot prove an export is complete, payments are authentic or account identities are correct. Inputs must be independently verified. The example does not reveal any production customer records.

Primary references

• Grow or Die payer identity contract
• Grow or Die price versioning

Use the SQL after the identity and usage recipes.

• Link Stripe to AI usage — One internal account ID, no email joins.
• OpenAI streaming cost by account — Preserve final streamed usage without collecting prompts.
• AI contribution margin — Processor-net revenue minus attributable model cost.