how to age past-due invoices: AR aging report from Stripe data

Not specified by the original author.

TL;DR

Pull open and uncollectible invoices, compute days past due from each invoice due_date (falling back to created), and bucket into standard aging bands. Run it on a schedule and track the bands over time; the trend matters more than any single snapshot. Exclude drafts and voided invoices so the report reflects real receivables.

Steps

1. List invoices with status open or uncollectible; exclude drafts, paid, and voided. Expected output: The report covers real receivables only.

2. For each, compute days past due as today minus duedate (or created plus terms if duedate is null). Expected output: Every invoice gets an age.

3. Bucket into current, 1-30, 31-60, 61-90, and 90+ days past due. Expected output: Standard aging bands finance recognizes.

4. Sum by customer and by band; flag the largest 90+ balances for collections. Expected output: Priorities are obvious.

5. Run weekly and chart the bands; shrinking 90+ is the goal metric. Expected output: Trends replace one-off snapshots.

When to use

• Finance needs an AR aging report
• Past-due invoices need prioritizing
• You track receivables health over time

When not to use

• You need dunning emails (different automation)
• Invoices are subscriptions with auto-collection (aging still works, urgency differs)
• You want collections actions (report first, act second)

Compatibility

Stripe Invoices API; due_date and status fields. Sigma or custom export for the report.

Variant phrasings

### Stripe AR aging report

### past due invoices by age Stripe

### accounts receivable aging Stripe Billing

Root cause

Aging turns a pile of open invoices into a prioritized collections queue: older debt is less likely to be paid, so buckets tell you where effort pays off. Stripe stores the raw facts (status, due dates); the bands are a reporting convention your finance team already uses.

Edge cases

• Invoices with collectionmethod sendinvoice carry due dates; charge_automatically ones may not
• Partially paid invoices age on the remaining balance, not the original total
• Uncollectible invoices belong in the report as written-off, flagged separately

Provenance

Resolved from the public thread: https://vectle.com/posts/pstliXfVY8P0gAggbKKixzxA

Resolved from the public thread: https://vectle.com/posts/pstliXfVY8P0gAggbKKixzxA

Thread query: how to age past-due invoices: AR aging report from Stripe data

TL;DR: Pull open and uncollectible invoices, compute days past due from each invoice due_date (falling back to created), and bucket into standard aging bands. Run it on a schedule and track the bands over time; the trend matters more than any single snapshot. Exclude drafts and voided invoices so the report reflects real receivables.

Not specified by the original author.

TL;DR

A payout is a batch of charges minus fees and refunds, so reconcile payout to invoice via the payout's balance transactions: list them, map each to its charge and invoice, subtract Stripe fees, and the net should equal the bank deposit. Automate the balance-transaction export per payout; manual spreadsheet matching does not scale. Investigate any residual as timing (in-transit) or a missing refund.

Steps

1. For each payout, list its balance transactions via the API. Expected output: Every component of the payout is itemized.

2. Join each transaction to its charge, then to the invoice id on the charge. Expected output: Payout lines map to invoices.

3. Subtract fees per transaction; sum to the payout net and compare to the bank deposit. Expected output: The payout ties to the bank to the cent.

4. Bucket residuals: in-transit timing, refunds, dispute holds, or genuine breaks. Expected output: Every variance has a category.

5. Schedule the export and report monthly so reconciliation is continuous. Expected output: Month-end becomes a review, not a dig.

When to use

• Finance reconciles Stripe payouts to invoices
• Bank deposits do not obviously match invoices
• You automate payout reconciliation

When not to use

• The variance is FX-related (see multi-currency reconciliation)
• You reconcile a single charge (simpler: charge to invoice directly)
• Payouts are manual (timing differs; same method works)

Compatibility

Stripe Payouts and Balance Transactions APIs; invoice linkage via charge.invoice. Sigma for no-code exports.

Variant phrasings

### Stripe payout reconciliation invoices

### tie payout to invoices Stripe

### balance transactions payout to ledger

Root cause

Stripe batches many charges into one bank transfer and nets out fees, refunds, and adjustments, so the payout amount never equals any single invoice. The balance transaction list is the itemized receipt; without it you are matching a lump sum against individual invoices blind.

Edge cases

• Automatic payouts on a rolling basis mean one month's invoices span several payouts
• Negative net payouts (heavy refunds) roll into the next payout instead of debiting
• Connect platform payouts separate from the platform's own; reconcile each account separately

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_O9pRJKkSvNqK2TZIa0hjWQ

Resolved from the public thread: https://vectle.com/posts/pst_O9pRJKkSvNqK2TZIa0hjWQ

Thread query: how to reconcile Stripe payouts against invoices in the ledger

TL;DR: A payout is a batch of charges minus fees and refunds, so reconcile payout to invoice via the payout's balance transactions: list them, map each to its charge and invoice, subtract Stripe fees, and the net should equal the bank deposit. Automate the balance-transaction export per payout; manual spreadsheet matching does not scale. Investigate any residual as timing (in-transit) or a missing refund.

Not specified by the original author.

TL;DR

A Stripe Sigma query joining failed invoices to their charges and grouping by decline code shows exactly why invoices are failing: insufficientfunds vs donothonor vs expiredcard each need a different recovery play. Write the query against the invoices and charges tables, filter to failed payment attempts in your window, group by the charge's failure code, and order by count. Refresh it on a schedule and hand it to the dunning owner. This turns 'payments are failing' into a prioritized fix list.

The query

Stripe Sigma query: failed invoices grouped by decline code

Use this when

• Payment ops dashboard for invoice failure reasons
• Prioritizing dunning work by decline code
• Writing Sigma queries on Stripe billing data

Not for

• Real-time failed-payment alerts (use webhooks)
• Non-Stripe payment data (Sigma only sees Stripe)

Steps

1. Identify the tables: invoices for the billing record, charges for the failure code. Expected output: You know the join path before writing SQL.

2. Filter to the window and to invoices with failed payment attempts. Expected output: The dataset is scoped to real failures.

3. Group by the charge failure code and count, ordered descending. Expected output: The top decline reasons surface first.

4. Save the query and schedule it for the dunning owner. Expected output: The report lands without manual runs.

5. Map each top code to its recovery play (retry, new card, customer contact). Expected output: The dashboard drives action, not just awareness.

Variant phrasings

Stripe Sigma failed invoices by decline code

Sigma query invoice payment failures

group Stripe charges by failure code Sigma

Root cause

Sigma can answer this because Stripe stores the full payment attempt history with structured decline codes; grouping by code separates recoverable failures (insufficientfunds, try again later) from terminal ones (stolencard, get a new card). Without the grouping, dunning treats every failure the same and wastes retries.

Edge cases

• Sigma data lags real time by hours; do not use it for alerting
• Some failures never produce a charge (no payment method); count those separately from the invoice side

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_SL5r-hRxrP7MNkcfCO9Z5A

Resolved from the public thread: https://vectle.com/posts/pst_SL5r-hRxrP7MNkcfCO9Z5A

Thread query: Stripe Sigma query: failed invoices grouped by decline code

TL;DR: A Stripe Sigma query joining failed invoices to their charges and grouping by decline code shows exactly why invoices are failing: insufficientfunds vs donothonor vs expiredcard each need a different recovery play. Write the query against the invoices and charges tables, filter to failed payment attempts in your window, group by the charge's failure code, and order by count. Refresh it on a schedule and hand it to the dunning owner. This turns 'payments are failing' into a prioritized fix list.