How to Detect Revenue Leaks with SQL

Most revenue leaks aren't dramatic. Nobody steals money. No system crashes. A payment fails and the retry window silently expires. An invoice sits unpaid for 60 days and nobody follows up. A deal closes in HubSpot and no subscription ever gets created in Stripe. Each one is small. Added up across a year, they're a headcount you didn't hire.

I've run these queries against production billing data at three different companies. The pattern is the same every time — 3% to 8% of expected revenue leaking through gaps between systems. The six queries below will show you where yours is going.

You'll need your Stripe data and CRM data somewhere queryable. A warehouse works. Fastero's cross-source store works — it lands both into a single DuckDB instance so the joins are trivial. Get subscriptions, invoices, and customers from Stripe, and deals and contacts from your CRM.

1. Failed charges that exhausted retries

Stripe retries failed payments up to 4 times over ~23 days by default. When all retries fail, the subscription cancels. That's involuntary churn — a customer who'd keep paying if someone asked them to update their card.

SELECT
    c.email,
    s.plan_amount / 100.0    AS monthly_amount,
    i.attempt_count,
    i.next_payment_attempt    AS next_retry
FROM invoices i
JOIN subscriptions s ON i.subscription_id = s.id
JOIN customers c     ON s.customer_id = c.id
WHERE i.status = 'open'
  AND i.attempt_count >= 2
  AND s.status = 'past_due'
ORDER BY s.plan_amount DESC;

Sort by amount descending. A $5,000/month customer with a failing card is a five-minute phone call, not a support ticket. Gotcha: next_payment_attempt is null after the final retry. Nulls mean the window already closed — you're in recovery mode, not prevention.

2. Invoices aging past payment terms

Not every SaaS company bills by card-on-file. Enterprise deals often use net-30 or net-60 invoices. Those invoices age. Nobody chases them because the CRM shows the deal as closed-won and everyone assumes finance is handling it.

SELECT
    c.email,
    i.amount_due / 100.0        AS amount_due,
    i.due_date,
    CURRENT_DATE - i.due_date   AS days_overdue
FROM invoices i
JOIN customers c ON i.customer_id = c.id
WHERE i.status IN ('open', 'uncollectible')
  AND i.due_date < CURRENT_DATE - INTERVAL '14 days'
  AND i.amount_due > 0
ORDER BY i.amount_due DESC;

Fourteen days past due is where polite follow-up becomes urgent collection. By 90, you're writing it off.

The same customers show up on this list month after month. They're not refusing to pay — they've learned that nobody chases, so the invoice sits until someone remembers. A single automated reminder at day 7 and day 14 cuts aging by half. See revenue leakage causes and fixes for the full pattern.

3. Won deals with no corresponding subscription

This is the most expensive leak per occurrence. A deal moves to closed-won in the CRM. The AE celebrates. Nobody creates the subscription. The customer onboards using a trial or courtesy access. Weeks pass. Revenue: zero.

SELECT
    d.deal_name,
    d.amount                      AS deal_value,
    d.close_date,
    CURRENT_DATE - d.close_date   AS days_since_close,
    ct.email                      AS contact_email
FROM deals d
JOIN deal_contacts dc ON d.id = dc.deal_id
JOIN contacts ct      ON dc.contact_id = ct.id
LEFT JOIN customers c ON LOWER(ct.email) = LOWER(c.email)
LEFT JOIN subscriptions s
    ON c.id = s.customer_id
    AND s.created >= d.close_date - INTERVAL '7 days'
    AND s.status IN ('active', 'trialing', 'past_due')
WHERE d.stage = 'closed_won'
  AND d.close_date < CURRENT_DATE - INTERVAL '14 days'
  AND s.id IS NULL
ORDER BY d.amount DESC;

The 7-day window on s.created accounts for deals where billing was set up before the CRM stage changed. Without it, false positives.

At one company, this query returned 11 deals totaling $47,000/year. Eleven customers using the product without paying because the sales-to-billing handoff had no automation. That's not a rounding error. For a deeper walkthrough, see how to reconcile Stripe revenue with your CRM.

4. Pricing drift between CRM and billing

Your CRM says the customer pays $2,400/month. Stripe says $1,800/month. Who's right? Stripe — it's the system that charges the card. The CRM is stale. Maybe the customer downgraded six months ago. Maybe a discount was applied directly in Stripe and never recorded.

SELECT
    d.deal_name,
    ct.email,
    d.amount                                    AS crm_annual_value,
    s.plan_amount / 100.0 * 12                  AS stripe_annual_value,
    d.amount - (s.plan_amount / 100.0 * 12)     AS annual_drift,
    ROUND(
        ABS(d.amount - (s.plan_amount / 100.0 * 12))
        / NULLIF(d.amount, 0) * 100, 1
    )                                           AS drift_pct
FROM deals d
JOIN deal_contacts dc ON d.id = dc.deal_id
JOIN contacts ct      ON dc.contact_id = ct.id
JOIN customers c      ON LOWER(ct.email) = LOWER(c.email)
JOIN subscriptions s  ON c.id = s.customer_id
WHERE d.stage = 'closed_won'
  AND s.status = 'active'
  AND ABS(d.amount - (s.plan_amount / 100.0 * 12)) > 100
ORDER BY ABS(annual_drift) DESC;

The > 100 threshold filters proration noise. Adjust based on your average deal size. Drift in both directions matters — Stripe lower than CRM means you're under-collecting, Stripe higher means your CRM forecasts and net revenue retention numbers are wrong.

5. Subscriptions downgraded without a save attempt

Customers downgrade. That's normal. What's not normal is finding out about it three weeks later in a monthly report. A downgrade is a signal — the customer decided your higher tier isn't worth it. If nobody reaches out within the first 48 hours, the save rate drops to near zero.

SELECT
    c.email,
    s.previous_plan_amount / 100.0   AS old_amount,
    s.plan_amount / 100.0            AS new_amount,
    (s.previous_plan_amount - s.plan_amount) / 100.0 AS monthly_loss,
    s.plan_changed_at
FROM subscriptions s
JOIN customers c ON s.customer_id = c.id
WHERE s.previous_plan_amount > s.plan_amount
  AND s.plan_changed_at > CURRENT_DATE - INTERVAL '30 days'
  AND s.status = 'active'
ORDER BY monthly_loss DESC;

This assumes your data includes previous_plan_amount — if you're syncing subscription events, it comes from previous_attributes. Otherwise reconstruct from subscription.updated webhooks or Stripe subscription analytics. The monthly_loss column gets people's attention. A single $500/month downgrade that could have been saved is $6,000 in recovered ARR.

6. Coupons and discounts that never expired

A sales rep promises "3 months at 50% off" to close a deal. The coupon gets created in Stripe with duration: repeating and duration_in_months: 3. Except sometimes it gets created with duration: forever. Now you're giving a permanent 50% discount that nobody intended and nobody is monitoring.

SELECT
    c.email,
    s.plan_amount / 100.0   AS list_price,
    d.coupon_id,
    d.percent_off,
    d.duration,
    CURRENT_DATE - d.start  AS days_active,
    CASE WHEN d.percent_off IS NOT NULL
         THEN s.plan_amount / 100.0 * d.percent_off / 100.0
         ELSE d.amount_off / 100.0
    END                     AS monthly_leak
FROM discounts d
JOIN subscriptions s ON d.subscription_id = s.id
JOIN customers c     ON s.customer_id = c.id
WHERE d.duration = 'forever'
   OR (d.duration = 'repeating'
       AND d.start + (d.duration_in_months || ' months')::INTERVAL < CURRENT_DATE)
ORDER BY monthly_leak DESC;

The second condition catches repeating discounts past their intended end. Stripe ends repeating coupons automatically, but if someone manually re-applies the coupon after expiry, it resets the clock. I've seen support reps do this without realizing the customer already had one.

Putting it together

Run these six queries weekly. Each one targets a different crack in the revenue pipeline:

The hard part isn't the SQL. It's the join. Your CRM data and your Stripe data live in different systems with different schemas and no shared key. Email matching gets you 70% of the way. Domain matching adds another 15%. The rest requires manual mapping or a system that handles identity resolution for you.

Fastero's revenue leak detection handles this — it connects to Stripe and your CRM, lands both into a cross-source DuckDB store, resolves identities automatically, and runs these patterns continuously. When it finds a leak, you get a Slack alert with the customer, the amount, and the leak type. No warehouse required, no query maintenance. But if you have a warehouse and 30 minutes, start with query #3 (won deals, never billed). It's the highest-value leak and the easiest to fix. You'll probably find money.

Related:

• Revenue Leakage in SaaS: Causes and Fixes
• How to Reconcile Stripe Revenue with Your CRM
• How to Calculate Real MRR from Stripe API
• How to Analyze Stripe Subscriptions with SQL
• How to Calculate Net Revenue Retention in SQL

Try Fastero free — connect Stripe and your CRM, get automated leak detection with zero SQL maintenance. No credit card required.