Revenue Assurance for Subscriptions: My Billing Reconciliation Experiment

Most of us know what an AI subscription bill looks like. You pay a flat monthly fee for a chatbot plan, much like ChatGPT Plus. If you build an app on top of the model, you also pay for what your app uses through the API.

That bill looks simple. Behind it, several systems have to agree. One knows who is subscribed. One counts the API calls. One produces the invoice. One records the payment. When they disagree, money leaks quietly. Some customers are never billed for what they used. Others are billed twice. Nobody notices, because every bill still looks like a normal bill.

Catching that leak is called revenue assurance. In one past role, I reconciled three systems against each other: the product platforms that showed which customers were active, Salesforce for what they had ordered, and SAP for what we billed. The rule was simple. Only active customers get billed, and they get billed for what they ordered. That work found revenue we were missing, and it stopped overbilling before the bills went out.

I wanted to see how far the same idea gets you with a small, open example. So I built a made-up AI company, wrote 19 billing scenarios and ran two kinds of checks against them. A line-by-line revenue assurance check caught 12 of 14 billing errors. A quick check of the total bill caught only seven. Neither check raised a false alarm on the five correct cases.

The two errors that got through are the most interesting part. In one, a batch of API calls disappeared from every system. Every system agreed on the smaller bill, so nothing looked wrong.

What is revenue assurance?

Revenue assurance checks that a business bills every charge it is owed, bills each customer the right amount and collects what it billed. For a subscription business, that means comparing who is subscribed, what they used, what they were charged and what they paid. Missing charges, overcharges and unpaid bills each need different fixes.

Picture a customer on a $20 monthly plan who also made $10 worth of API calls. A $20 bill looks perfectly reasonable. It has the right customer, the right date and a sensible amount. It is still missing a third of what the customer owes.

Now picture a correct $30 bill that nobody paid. Billing did its job. The problem is in collections. Calling both cases “lost revenue” hides which team needs to act.

PwC’s revenue assurance paper covers leaks across contracts, service delivery, invoicing and collections. I kept this experiment narrower: monthly plans, API usage and payments. It does not cover accounting rules for when revenue is recognized.

Why test billing after testing the data?

Data quality checks confirm that each record follows the rules, such as a valid customer ID and a positive number of API calls. They cannot see a record that never arrived, or a valid record charged to the wrong customer. Revenue assurance compares the bill with an independent view of what the bill should have been.

This follows my data contract validation experiment. Those rules let three errors through: missing usage, usage charged to the wrong customer and a quantity recorded in the wrong unit. All three appear here as scenarios, and both checks in this experiment caught all three. They could, because the product usage log still had the correct API calls to compare against.

There is also a people question. In the business scenario in AI to ROI for Business Leaders, the business leader gives data quality a named owner who can pause work when the inputs are wrong. A billing error report needs the same thing. If nobody owns the fix, the report just sits there.

What does the made-up AI company sell?

The company sells two plans. Each has a monthly fee and a price for every API call. An API call is one request from a customer’s app to the AI model, such as asking it to answer a question or summarize a document. The prices are made up, and there are no real customers or payments.

Real AI APIs usually charge by tokens, the chunks of text the model reads and writes. A flat price per API call keeps the math simple enough to check by hand, and the billing problems are the same either way.

The bills cover September 2026, and I reviewed them on October 8, 2026. There are five customers:

• S001 and S005 are on Basic and active all month.
• S002 is on Pro and active all month.
• S003 canceled before September, so it owes nothing.
• S004 is on a free trial for all of September, so it also owes nothing.

The subscription list marks each customer as billable or not, and the code trusts that flag. If the flag is wrong, the checks inherit the mistake. In a real business, someone in finance should confirm those rules.

There are no discounts, taxes, refunds, annual plans or partial months. I kept the rules simple so anyone can check the math by hand.

Where does the billing data come from?

Each scenario has six small CSV files. Amounts are stored in cents, so $30.00 appears as 3000.

Here is the clean case, where everything is correct.

Subscriptions

subscription_id,customer_id,plan_id,status,billable,period
S001,C001,basic,active,1,2026-09
S002,C002,pro,active,1,2026-09
S003,C003,basic,canceled_before_period,0,2026-09
S004,C004,basic,trial_full_period,0,2026-09
S005,C005,basic,active,1,2026-09

Plans

plan_id,monthly_cents,unit_cents
basic,2000,1
pro,5000,2

Product usage log

event_id,subscription_id,quantity,period
E001,S001,1000,2026-09
E002,S002,500,2026-09
E003,S002,1500,2026-09
E004,S005,500,2026-09

Billing usage log

event_id,subscription_id,quantity,period
E001,S001,1000,2026-09
E002,S002,500,2026-09
E003,S002,1500,2026-09
E004,S005,500,2026-09

In the clean case, the two usage logs match. Each error scenario changes one or both of them, or the bills and payments that follow.

Invoices

invoice_id,subscription_id,customer_id,amount_cents,status,due_date,period
I001,S001,C001,3000,paid,2026-10-05,2026-09
I002,S002,C002,9000,paid,2026-10-05,2026-09
I005,S005,C005,2500,paid,2026-10-05,2026-09

Payments

payment_id,invoice_id,amount_cents,status
P001,I001,3000,settled
P002,I002,9000,settled
P005,I005,2500,settled

“Settled” means the money arrived. Declined and pending payments do not count toward what was paid.

Download and run the experiment

The package has the data, the code that builds every scenario, the SQL, the checks, the tests and my recorded results. It runs on Python 3.10 or later with no extra installs, accounts or paid services. Enter your email below and I’ll send you the download link.

Extract the ZIP, open a terminal in the revenue-assurance-experiment folder and run:

python3 experiment.py
python3 verify_experiment.py

On Windows, use py if python3 does not work. The first command runs the checks and saves the results. The second confirms the results match what I recorded. build_fixtures.py shows exactly what I changed in each scenario.

How much should each customer pay?

Each billable customer owes the monthly fee plus their API calls times the plan’s price per call. In the clean case, S001 owes $30, S002 owes $90 and S005 owes $25, for a total of $145. The canceled customer and the trial customer owe nothing.

The expected bill comes from this SQL:

WITH usage_totals AS (
    SELECT subscription_id, SUM(quantity) AS quantity
    FROM source_usage
    GROUP BY subscription_id
)
SELECT s.subscription_id, s.customer_id,
       CASE WHEN s.billable = 1
            THEN p.monthly_cents + COALESCE(u.quantity, 0) * p.unit_cents
            ELSE 0 END AS expected_cents
FROM subscriptions s
JOIN plans p ON s.plan_id = p.plan_id
LEFT JOIN usage_totals u ON s.subscription_id = u.subscription_id
ORDER BY s.subscription_id;

The query adds up each customer’s API calls before joining to the plan. S002 has two batches of API calls. Joining first would add the $50 monthly fee twice.

I also kept a separate answer key with the correct amount and customer for each subscription. The checks never see it. I only use it afterward to score how much each error cost.

How did I test the billing checks?

I ran all 19 scenarios through two checks. Five scenarios are correct, including ones that look odd but are fine, like a customer paying in two parts. Fourteen contain a deliberate billing error. Each scenario starts from the clean case, so errors never pile up.

Both checks use the same expected bill. Only the level of detail differs.

The usage check in experiment.py is short enough to read:

source = {r['event_id']: (r['subscription_id'], int(r['quantity']))
          for r in data['source_usage']}
billing = {r['event_id']: (r['subscription_id'], int(r['quantity']))
           for r in data['billing_usage']}
for eid in sorted(set(source) | set(billing)):
    if source.get(eid) != billing.get(eid):
        flag('usage_event_mismatch', eid,
             f'Source {source.get(eid)}; billing {billing.get(eid)}.')

For every batch of API calls in either log, it checks that both logs have it, with the same customer and the same count. Before that, the code rejects duplicate IDs and references to customers that do not exist, so a bad row cannot slip through a join unnoticed.

Which billing errors did revenue assurance catch?

The line-by-line check caught 12 of the 14 billing errors. The quick total check caught seven. The line-by-line check found missing charges, overcharges, unpaid bills, a bill marked paid with no payment and a bill sent to the wrong customer. Neither check flagged the five correct cases. These counts describe the scenarios I chose, not real-world detection rates.

“Flagged” means the check raised at least one issue for a person to review. The code never changes a bill or contacts a customer.

Each row is a separate what-if. Adding the dollar amounts down a column would not give a meaningful total.

Two errors can hide inside a correct total

In one scenario, S001 is overcharged $10 and S002 is undercharged $10. The total is still $145, so the quick check passes. The line-by-line check flags both customers.

This is why I would never put a single net variance at the top of a billing report. Zero can mean every bill is right. It can also mean two customers have opposite problems.

Right bill, wrong customer

Here, the correct $90 bill for S002 goes to customer C001. The amount is right and the subscription is right. The total passes, and so does the amount check. Only the check on who received the bill catches it.

No revenue is missing on paper. In practice, one customer has another customer’s bill, and the customer who owes the money may never see it.

A correct bill can still go unpaid

In the unpaid scenario, S002’s $90 bill is right, but no payment arrived by the due date. The quick check passes because billing did nothing wrong. The line-by-line check reports $90 overdue. In the partly paid scenario, $30 arrives and $60 is overdue.

Normal payment behavior should not raise alarms

Three correct scenarios test this. One customer pays $90 in two parts, $30 and $60. Another has a declined card followed by a successful payment. A third has a bill that is not due yet. None of them should be flagged, and none were.

Stripe’s invoice documentation explains that a bill can be marked paid through account credits or a payment made outside Stripe. My example does not model those, so a bill marked paid with no payment needs a person to investigate before anyone assumes the customer did not pay.

Why did two billing errors get through?

Two errors got through because every system made the same mistake. In one, a batch of API calls was missing from both usage logs. In the other, the wrong monthly price was used by both the expected bill and the real bill. Each cost $10. The checks saw systems that agreed, with nothing independent to show they were all wrong.

For the missing usage, I removed 500 of S002’s API calls from both logs. S002 is billed for 1,500 calls instead of 2,000, so the bill drops from $90 to $80. The usage logs match, the expected bill is also $80 and the customer pays $80. Everything agrees.

For the price, I changed the Pro monthly fee from $50 to $40 in the price list. The expected bill and the real bill both use $40. The bill again drops to $80, and again everything agrees.

I built these two failures in on purpose. A test with only easy wins would make the checks look better than they are. In a real business, the fix is a source the billing system does not control, such as the signed contract for the price or a separate count from the servers that handled the API calls.

Even then, be careful. Two copies of the same broken log are still one broken log. Saving it twice does not make it independent.

How did I check the results?

An automated test suite of 19 tests passed. It checks the bill math, the correct cases, every caught error, the two errors that got through and the dollar impact of the main errors. I reran everything from a fresh download and got identical results. This proves the code behaves as described. It does not replace a finance review.

I recorded the results on October 9, 2026, using Python 3.13 and SQLite 3.45, then reran them on Python 3.10 to confirm the minimum version works. All 38 results matched.

The expected bill comes from SQL, while the bills in each scenario come from separate Python code, so one cannot quietly copy the other. The tests also do simple math independently, such as $50 plus 2,000 API calls at $0.02. For the scenario where two errors cancel out, the tests confirm the report shows $10 underbilled and $10 overbilled instead of a misleading zero.

Two of the payment checks, an unexpected payment with no matching bill and a payment larger than its bill, are not triggered by any scenario. Two extra tests trigger them directly, so no check goes untested.

All data is made up. No real billing system, payment provider or bank was used, and no finance, billing or product team reviewed the results.

Who should fix each billing problem?

Each flagged issue needs one owner who can confirm the facts and decide the fix. Finance owns what customers were entitled to and what they still owe. Billing operations owns corrected bills and customer messages. Product owns missing usage, and data engineering fixes broken data feeds. Finding a problem does not authorize a charge or a refund.

These are the owners I would propose. The experiment does not run those reviews.

For a leadership dashboard, I would show confirmed missing charges, overcharges, overdue bills and open issues as separate numbers. I would add how long issues take to fix and which root causes keep coming back. A flagged issue only counts as recovered money once the fix is approved and the money shows up.

That is the same principle behind The Data-Driven Executive: every metric needs a clear meaning and a decision attached to it.

Would I use this approach for real subscription billing?

Yes, as a starting point. The line-by-line comparisons are the right foundation. A real business would add its actual pricing rules, a usage source the billing system cannot change and a clear process for working through flagged issues. This example skips partial months, credits, refunds, taxes, late usage and annual plans.

The systems I reconciled in my own work were far larger than six CSV files, but the questions were the same. Is this customer active? What did they order? What did we bill? What did they pay?

Timing matters too. Stripe notes that usage is processed asynchronously, so recent API calls may not show up right away. A gap before the agreed cutoff might just be a delay. The same gap after the cutoff needs escalation. This example uses finished month-end data and skips that problem.

My next step would be an agreed cutoff for when usage counts as complete, plus a queue where a person reviews and closes each flagged issue. I would check the results against signed contracts and an independent usage count before letting any of this change a customer’s bill.

I would also relax the one-bill-per-month rule if the business legitimately sends several bills in a month. A check that does not match how the business really works creates false alarms, and people learn to ignore it.

Official resources

• Stripe invoice lifecycle
• Stripe usage recording
• Python SQLite documentation
• PwC revenue assurance paper

What are you building, which subscription or usage data do you have, and what is stopping you from checking the bills?