I keep a folder of SQL queries I run against our warehouse every Monday morning. They take about 2 minutes to execute and consistently surface $5,000-$20,000 in revenue discrepancies that would otherwise go unnoticed until someone in Finance asks "why doesn't the CRM number match the bank?"
These queries assume you have both your CRM data (HubSpot or Salesforce) and your billing data (Stripe) landing in a warehouse — BigQuery, Snowflake, Postgres, Redshift, whatever. If you're using Fivetran, Airbyte, or Stitch to sync both systems, you already have what you need. The table names will differ (Fivetran uses stripe.subscription, Airbyte might use raw_stripe__subscriptions), but the join patterns are universal.
I've included both HubSpot and Salesforce variants where the schema differs. Pick the one that matches your stack.
1. Won-but-unpaid deals
The classic: deal marked closed-won, no corresponding Stripe subscription.
-- HubSpot variant
SELECT
d.deal_id,
d.dealname,
d.amount,
d.closedate,
d.pipeline_stage_display_order,
c.email AS contact_email,
CURRENT_DATE - d.closedate::date AS days_since_close
FROM hubspot_deals d
JOIN hubspot_deal_to_contact dc ON d.deal_id = dc.deal_id
JOIN hubspot_contacts c ON dc.contact_id = c.contact_id
LEFT JOIN stripe_subscriptions s
ON LOWER(c.email) = LOWER(s.customer_email)
AND s.created >= d.closedate - INTERVAL '7 days'
AND s.status IN ('active', 'trialing', 'past_due')
WHERE d.dealstage = 'closedwon'
AND d.closedate >= CURRENT_DATE - INTERVAL '90 days'
AND s.id IS NULL
ORDER BY d.amount DESC NULLS LAST;-- Salesforce variant
SELECT
o.id AS opportunity_id,
o.name,
o.amount,
o.close_date,
ct.email AS contact_email,
CURRENT_DATE - o.close_date AS days_since_close
FROM salesforce_opportunities o
JOIN salesforce_opportunity_contact_roles ocr ON o.id = ocr.opportunity_id
JOIN salesforce_contacts ct ON ocr.contact_id = ct.id
LEFT JOIN stripe_subscriptions s
ON LOWER(ct.email) = LOWER(s.customer_email)
AND s.created >= o.close_date - INTERVAL '7 days'
AND s.status IN ('active', 'trialing', 'past_due')
WHERE o.stage_name = 'Closed Won'
AND o.close_date >= CURRENT_DATE - INTERVAL '90 days'
AND o.is_won = true
AND s.id IS NULL
ORDER BY o.amount DESC NULLS LAST;2. MRR drift — CRM value vs. actual Stripe billing
What the CRM says vs. what Stripe actually charges, per customer.
SELECT
c.email,
d.dealname,
d.amount AS crm_deal_value,
COALESCE(SUM(si.unit_amount * si.quantity), 0) / 100.0 AS stripe_monthly,
COALESCE(SUM(si.unit_amount * si.quantity), 0) / 100.0 * 12 AS stripe_annual,
d.amount - (COALESCE(SUM(si.unit_amount * si.quantity), 0) / 100.0 * 12) AS annual_drift
FROM hubspot_deals d
JOIN hubspot_deal_to_contact dc ON d.deal_id = dc.deal_id
JOIN hubspot_contacts c ON dc.contact_id = c.contact_id
JOIN stripe_customers sc ON LOWER(c.email) = LOWER(sc.email)
JOIN stripe_subscriptions s ON sc.id = s.customer_id AND s.status = 'active'
JOIN stripe_subscription_items si ON s.id = si.subscription_id
WHERE d.dealstage = 'closedwon'
GROUP BY c.email, d.dealname, d.amount
HAVING ABS(d.amount - (SUM(si.unit_amount * si.quantity) / 100.0 * 12)) > 500
ORDER BY ABS(d.amount - (SUM(si.unit_amount * si.quantity) / 100.0 * 12)) DESC;3. Silent churn — paying but not using
Customers with active Stripe subscriptions and zero product usage in the last 30 days.
SELECT
sc.email,
s.plan_name,
SUM(si.unit_amount * si.quantity) / 100.0 AS monthly_revenue,
MAX(pe.created_at) AS last_product_event,
CURRENT_DATE - MAX(pe.created_at)::date AS days_inactive
FROM stripe_subscriptions s
JOIN stripe_customers sc ON s.customer_id = sc.id
JOIN stripe_subscription_items si ON s.id = si.subscription_id
LEFT JOIN product_events pe
ON sc.email = pe.user_email
AND pe.created_at >= CURRENT_DATE - INTERVAL '90 days'
WHERE s.status = 'active'
GROUP BY sc.email, s.plan_name
HAVING MAX(pe.created_at) < CURRENT_DATE - INTERVAL '30 days'
OR MAX(pe.created_at) IS NULL
ORDER BY SUM(si.unit_amount * si.quantity) DESC;4. Failed payments at risk of churning
Invoices that have failed 2+ times and haven't been recovered yet.
SELECT
sc.email,
i.id AS invoice_id,
i.amount_due / 100.0 AS amount,
i.attempt_count,
i.created AS first_attempt,
i.next_payment_attempt AS next_retry,
s.cancel_at AS scheduled_cancellation,
ch.failure_code,
ch.failure_message
FROM stripe_invoices i
JOIN stripe_subscriptions s ON i.subscription_id = s.id
JOIN stripe_customers sc ON i.customer_id = sc.id
LEFT JOIN stripe_charges ch ON i.charge_id = ch.id
WHERE i.status = 'open'
AND i.attempted = true
AND i.attempt_count >= 2
AND s.status = 'past_due'
ORDER BY i.amount_due DESC;5. Discount leakage — coupons that should have expired
Active subscriptions with discounts running longer than they should.
SELECT
sc.email,
s.id AS subscription_id,
d.coupon_name,
d.percent_off,
d.amount_off / 100.0 AS amount_off_dollars,
d.start AS discount_start,
d.end AS discount_end,
CURRENT_DATE - d.start::date AS days_discounted,
SUM(si.unit_amount * si.quantity) / 100.0 AS full_monthly_price,
SUM(si.unit_amount * si.quantity) / 100.0 * COALESCE(d.percent_off, 0) / 100 AS monthly_discount_value
FROM stripe_subscriptions s
JOIN stripe_customers sc ON s.customer_id = sc.id
JOIN stripe_subscription_items si ON s.id = si.subscription_id
JOIN stripe_discounts d ON s.discount_id = d.id
WHERE s.status = 'active'
AND d.end IS NULL -- No expiration set
AND d.start < CURRENT_DATE - INTERVAL '90 days' -- Running for 90+ days
ORDER BY SUM(si.unit_amount * si.quantity) * COALESCE(d.percent_off, 0) / 100 DESC;6. Revenue by marketing channel — CRM attributed vs. Stripe collected
What marketing thinks it generated vs. what Stripe confirms.
WITH attributed_revenue AS (
SELECT
COALESCE(d.hs_analytics_source, 'Unknown') AS channel,
COUNT(DISTINCT d.deal_id) AS deals_attributed,
SUM(d.amount) AS crm_attributed_revenue
FROM hubspot_deals d
WHERE d.dealstage = 'closedwon'
AND d.closedate >= CURRENT_DATE - INTERVAL '180 days'
GROUP BY COALESCE(d.hs_analytics_source, 'Unknown')
),
collected_revenue AS (
SELECT
COALESCE(d.hs_analytics_source, 'Unknown') AS channel,
COUNT(DISTINCT d.deal_id) AS deals_collected,
SUM(DISTINCT inv.amount_paid) / 100.0 AS stripe_collected_revenue
FROM hubspot_deals d
JOIN hubspot_deal_to_contact dc ON d.deal_id = dc.deal_id
JOIN hubspot_contacts c ON dc.contact_id = c.contact_id
JOIN stripe_customers sc ON LOWER(c.email) = LOWER(sc.email)
JOIN stripe_invoices inv ON sc.id = inv.customer_id
AND inv.status = 'paid'
AND inv.created >= d.closedate
WHERE d.dealstage = 'closedwon'
AND d.closedate >= CURRENT_DATE - INTERVAL '180 days'
GROUP BY COALESCE(d.hs_analytics_source, 'Unknown')
)
SELECT
a.channel,
a.deals_attributed,
c.deals_collected,
a.crm_attributed_revenue,
COALESCE(c.stripe_collected_revenue, 0) AS stripe_collected_revenue,
a.crm_attributed_revenue - COALESCE(c.stripe_collected_revenue, 0) AS gap,
ROUND(
(a.crm_attributed_revenue - COALESCE(c.stripe_collected_revenue, 0))
/ NULLIF(a.crm_attributed_revenue, 0) * 100, 1
) AS gap_pct
FROM attributed_revenue a
LEFT JOIN collected_revenue c ON a.channel = c.channel
ORDER BY gap DESC;7. Net MRR waterfall — new, expansion, contraction, churn
The real MRR movement by component, calculated from Stripe events.
WITH mrr_changes AS (
SELECT
DATE_TRUNC('month', s.created) AS month,
'new' AS type,
SUM(si.unit_amount * si.quantity) / 100.0 AS amount
FROM stripe_subscriptions s
JOIN stripe_subscription_items si ON s.id = si.subscription_id
WHERE s.created >= CURRENT_DATE - INTERVAL '6 months'
AND s.status IN ('active', 'trialing')
GROUP BY DATE_TRUNC('month', s.created)
UNION ALL
SELECT
DATE_TRUNC('month', s.canceled_at) AS month,
'churned' AS type,
-SUM(si.unit_amount * si.quantity) / 100.0 AS amount
FROM stripe_subscriptions s
JOIN stripe_subscription_items si ON s.id = si.subscription_id
WHERE s.canceled_at >= CURRENT_DATE - INTERVAL '6 months'
AND s.status = 'canceled'
GROUP BY DATE_TRUNC('month', s.canceled_at)
)
SELECT
month,
type,
amount
FROM mrr_changes
ORDER BY month DESC, type;8. Customers approaching plan limits (expansion opportunity)
Usage approaching plan thresholds — revenue waiting to be captured.
SELECT
sc.email,
s.plan_name,
SUM(si.unit_amount * si.quantity) / 100.0 AS current_mrr,
pu.current_usage,
pu.plan_limit,
ROUND(pu.current_usage::numeric / NULLIF(pu.plan_limit, 0) * 100, 1) AS usage_pct
FROM stripe_subscriptions s
JOIN stripe_customers sc ON s.customer_id = sc.id
JOIN stripe_subscription_items si ON s.id = si.subscription_id
JOIN product_usage pu ON sc.email = pu.user_email
WHERE s.status = 'active'
AND pu.current_usage::numeric / NULLIF(pu.plan_limit, 0) >= 0.8
ORDER BY SUM(si.unit_amount * si.quantity) DESC;9. Trial-to-paid conversion gap
Trials that expired without converting — revenue that almost happened.
SELECT
sc.email,
s.plan_name,
s.trial_start,
s.trial_end,
s.status,
CASE
WHEN s.status = 'active' AND s.trial_end < CURRENT_TIMESTAMP THEN 'converted'
WHEN s.status = 'canceled' THEN 'churned'
WHEN s.status = 'incomplete_expired' THEN 'payment_failed'
ELSE 'other'
END AS trial_outcome,
SUM(si.unit_amount * si.quantity) / 100.0 AS plan_value
FROM stripe_subscriptions s
JOIN stripe_customers sc ON s.customer_id = sc.id
JOIN stripe_subscription_items si ON s.id = si.subscription_id
WHERE s.trial_start >= CURRENT_DATE - INTERVAL '90 days'
AND s.trial_end < CURRENT_TIMESTAMP
GROUP BY sc.email, s.plan_name, s.trial_start, s.trial_end, s.status
HAVING CASE
WHEN s.status = 'active' AND s.trial_end < CURRENT_TIMESTAMP THEN 'converted'
WHEN s.status = 'canceled' THEN 'churned'
WHEN s.status = 'incomplete_expired' THEN 'payment_failed'
ELSE 'other'
END != 'converted'
ORDER BY SUM(si.unit_amount * si.quantity) DESC;10. Invoice aging — money owed but not yet collected
Open invoices sorted by how long they've been outstanding.
SELECT
sc.email,
i.id AS invoice_id,
i.amount_due / 100.0 AS amount_owed,
i.created,
i.due_date,
CURRENT_DATE - i.created::date AS days_outstanding,
i.attempt_count,
CASE
WHEN CURRENT_DATE - i.created::date <= 30 THEN '0-30 days'
WHEN CURRENT_DATE - i.created::date <= 60 THEN '31-60 days'
WHEN CURRENT_DATE - i.created::date <= 90 THEN '61-90 days'
ELSE '90+ days'
END AS aging_bucket
FROM stripe_invoices i
JOIN stripe_customers sc ON i.customer_id = sc.id
WHERE i.status = 'open'
AND i.amount_due > 0
ORDER BY i.amount_due DESC;11. Duplicate subscriptions (double-billing risk)
Customers with multiple active subscriptions — could be intentional (multi-product) or a billing error.
SELECT
sc.email,
COUNT(s.id) AS active_subscriptions,
ARRAY_AGG(s.plan_name) AS plans,
SUM(si.unit_amount * si.quantity) / 100.0 AS total_monthly
FROM stripe_subscriptions s
JOIN stripe_customers sc ON s.customer_id = sc.id
JOIN stripe_subscription_items si ON s.id = si.subscription_id
WHERE s.status = 'active'
GROUP BY sc.email
HAVING COUNT(s.id) > 1
ORDER BY SUM(si.unit_amount * si.quantity) DESC;12. Comprehensive revenue leak summary
One query to rule them all — a summary dashboard showing total leakage by category.
WITH won_but_unpaid AS (
SELECT COUNT(*) AS count, COALESCE(SUM(d.amount), 0) AS value
FROM hubspot_deals d
JOIN hubspot_deal_to_contact dc ON d.deal_id = dc.deal_id
JOIN hubspot_contacts c ON dc.contact_id = c.contact_id
LEFT JOIN stripe_subscriptions s
ON LOWER(c.email) = LOWER(s.customer_email)
AND s.status IN ('active', 'trialing', 'past_due')
WHERE d.dealstage = 'closedwon'
AND d.closedate >= CURRENT_DATE - INTERVAL '90 days'
AND s.id IS NULL
),
past_due AS (
SELECT
COUNT(*) AS count,
COALESCE(SUM(i.amount_due), 0) / 100.0 AS value
FROM stripe_invoices i
WHERE i.status = 'open' AND i.attempted = true AND i.attempt_count >= 2
),
expired_discounts AS (
SELECT
COUNT(*) AS count,
COALESCE(SUM(si.unit_amount * si.quantity * d.percent_off / 100), 0) / 100.0 AS monthly_value
FROM stripe_subscriptions s
JOIN stripe_subscription_items si ON s.id = si.subscription_id
JOIN stripe_discounts d ON s.discount_id = d.id
WHERE s.status = 'active' AND d.end IS NULL
AND d.start < CURRENT_DATE - INTERVAL '90 days'
)
SELECT 'Won-but-unpaid deals' AS category, count, value AS estimated_annual_impact
FROM won_but_unpaid
UNION ALL
SELECT 'At-risk invoices', count, value * 12 FROM past_due
UNION ALL
SELECT 'Perpetual discounts', count, monthly_value * 12 FROM expired_discounts;Turning queries into monitoring
Running these manually every Monday is better than not running them at all. But the real value comes from automation:
- Schedule them — most warehouses support scheduled queries (BigQuery Scheduled Queries, Snowflake Tasks, or dbt + cron)
- Alert on thresholds — if won-but-unpaid count exceeds 3, or past-due value exceeds $5k, fire a Slack alert
- Track trends — log the results daily and chart them over time to see whether your leakage is growing or shrinking
If the idea of maintaining 12 SQL queries, their scheduling, alerting thresholds, and the underlying data syncs sounds like a lot — that's because it is. Fastero runs these reconciliation patterns continuously against your connected sources, with built-in alerting and without you maintaining warehouse syncs or scheduled queries. But even if you prefer the DIY path, these 12 queries give you a working reconciliation system you can run today.
Try Fastero free — connect your CRM and billing data, get live revenue dashboards, and set up alerts that catch leaks before your next board meeting. No credit card required.
Last updated: July 2026.