OpenAI cost per Stripe customer: a SQL reconciliation recipe

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

For account A, two USD payments of $10 and $20 and two usage costs of $2 and $3 yield $25, not $50. A separate EUR payment of €9 and €1 cost yield €8 on a separate row. Account B with a $10 payment and unknown usage yields null contribution. Account C with only a $2 usage row also yields null contribution. These scenarios are executed in the Sidecar's SQLite tests.

For production accounting precision, use currency minor units or a database decimal type rather than binary floating point. This teaching template uses small illustrative amounts; it is not financial reporting software.

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