I once inherited a subscription business where the finance team was confident everything balanced. Yet month after month we were missing nearly 15% of expected cash from billed revenue. It wasn’t fraud or a wild churn spike — it was a slow bleed of edge cases, mismatched systems and a few automation assumptions that never survived scale.

Finding that leak required a simple, repeatable approach I now call the three-check reconciliation playbook. It’s designed for product-led SaaS, subscription e-commerce and any business that bills customers on a recurring schedule. The goal is practical: run three focused checks that surface where cash is falling out of the system, quantify the impact, and create priority fixes you can deploy within weeks.

Why the three-checks? What makes 15% possible?

Subscription systems span payment gateways (Stripe, Adyen), billing engines (Chargebee, Recurly, Zuora), CRM (HubSpot, Salesforce), and accounting (Xero, QuickBooks). Each handoff is a place cash can be lost, delayed, or misrecorded:

  • Failed payments that never get retried or retried incorrectly.
  • Proration, coupons or credit logic that create invoice amounts different from the ledger.
  • Manual refunds or write-offs recorded in accounting but not reconciled to customer records.

    • Individually these issues often represent small percentages. Combined, they add up. The three-check playbook isolates failures at each handoff so you can measure and fix them quickly.

    Check 1 — Payment gateway vs. Billing engine

    What I check first is whether the payments collected by your gateway match what the billing system thinks was paid. This catches failed payments, partial payments, gateway fees and unapplied refunds.

    How to run it:

  • Export the gateway transactions for a period (typically 30–90 days). For Stripe/Adyen include gross amount, net amount, fee, refund flags, chargeback flags and currency.
  • Export the billing engine ledger for the same period: invoice ID, invoice amount, invoice date, payment ID, payment amount, status.
  • Join the two on payment ID or, when that’s missing, on composite keys (customer ID + amount + date ± 1 day).

    • Red flags you’ll see:

  • Gateway payments that don’t map to any invoice (often future-dated adjustments or misapplied manual payments).
  • Invoices marked “paid” in billing but no corresponding gateway payment (could be manual journal entries in accounting).
  • Refunds processed in the gateway but still recorded as revenue in billing.

    • Quick SQL example (pseudocode):

    <code>SELECT b.invoice_id, b.amount, g.gateway_amount, g.statusFROM billing_ledger bLEFT JOIN gateway_transactions g ON b.payment_id = g.idWHERE g.id IS NULL OR b.amount != g.gateway_amount;</code>

    Check 2 — Billing engine vs. Customer subscriptions

    Billing engines can produce invoices that don’t reflect actual subscription state: wrong plan, overlapping subscriptions, failed proration logic, or coupons applied multiple times.

    How to run it:

  • Export active subscription records (start/end dates, plan id, price, coupon ids, next billing date).
  • Export invoices for the same customers with line-item detail (plan, coupon, proration).
  • Compare expected invoiced amount from subscription state vs. actual invoice line totals.

    • Common defects:

  • Proration bugs: customer upgraded/downgraded and system created two invoices but only one collected.
  • Duplicate coupons: loyalty coupons applied twice for a migration artifact.
  • Unbilled usage: metered usage recorded in CRM but not turned into invoiced line items.

    • Example metric to compute:

  • Expected invoice total = sum(subscription_price for billing period — expected coupon/proration)
  • Invoice mismatch rate = (count of invoices where expected_total != actual_total) / total invoices

    • Check 3 — Revenue ledger vs. Cashbook (Accounting)

    Finally, you must confirm that recognized revenue and cash receipts in the general ledger (GL) reconcile to what billing and gateway report. This catches timing issues, manual journal entries, and FX or bank fees that weren’t accounted for.

    How to run it:

  • Export GL cash receipts and revenue accounts for the period.
  • Compare totals to gateway net payments (after fees) and billing recognized revenue.
  • Flag any manual journals, unapplied deposits, or bank-only refunds.

    • Points to watch:

  • Bank fees and currency conversions recorded directly in banking but not mapped to the subscription payment records.
  • Deposits grouped in accounting (e.g., lumped daily settlements) that hide which invoices they cover.
  • Chargebacks recorded as disputes in the gateway but reversed in accounting without matching memo.

    • Putting numbers to the leak

    To be persuasive and operational, measure and communicate the leak in three slices:

  • Collection gap = (Invoice amounts billed — payments collected on those invoices) / billed amounts
  • Posting gap = (Payments collected — payments posted to customer accounts) / collected
  • Accounting gap = (Payments posted — cash recorded in GL) / posted

    • Small example table that I used to present to leadership:

    MetricValue (30 days)Notes
    Invoices billed£200,000Gross invoice total
    Payments collected (gateway)£175,000After failed payments/refunds
    Payments posted (billing)£165,000Missing £10k misapplied/ungrouped
    Cash in GL£170,000Bank fees & timing differences
    Observed leak15% (approx)Combined effect of failures & mis-postings

    Fixes that scale — priorities and quick wins

    After you identify the root causes, prioritize fixes by cash impact and implementation effort:

  • High impact, low effort: Enable automatic retries and smart dunning in your payment gateway (Stripe Billing, Chargebee dunning rules). Implement email + in-app receipts for failed payments and link to one-click retry.
  • Medium impact: Fix proration and coupon application logic in the billing engine. Add unit tests for billing scenarios during product changes or migrations.
  • Operational: Add a nightly reconciliation job that produces three artifacts: unmatched gateway payments, invoice mismatches, and GL exceptions. Feed these into a ticket queue for the finance ops team.
  • Longer-term: Move to a single source of truth for payment IDs across systems. Use webhooks to write gateway events back into your billing ledger in real time.

    • KPIs to track weekly

  • Invoice Failure Rate — percent of invoices where expected != actual.
  • Gateway Unmatched Payments — count and value of gateway transactions not mapped to invoices.
  • Reconciliation Coverage — percent of payments matched across all three checks.
  • Cash Leak % — (expected billed cash — cash in GL) / expected billed cash.

    • When we started tracking these, the daily unmatched payments list became a small, actionable queue. We reduced the leak from 15% to under 2% within three months by pairing a few automation fixes with a weekly reconciliation runbook.

    If you want, I can share a starter SQL template and a spreadsheet layout I use to automate the three checks (gateway extract → billing ledger → GL). I also include a short list of webhook fields you should capture from Stripe/Adyen to avoid future mismatches.