What is invoice collection rate?
Definition
Invoice collection rate measures how much invoiced value has been collected. A cohort view groups invoices by issue month and follows later payments; a period view compares cash collected in a period with invoices issued in that period. These answer different questions and should not be mixed.
Formula: Cohort collection rate = payments allocated to an invoice cohort / invoice value in that cohort x 100
What data do you need?
- Invoices with issue date, total, customer, and currency
- Payments and credit notes allocated to invoices
- Payment date and allocation date
- Voids, write-offs, and refunds
- Reporting-currency conversion at a documented basis
SQL pattern
Collection rate by invoice cohortPostgreSQL
SELECT
date_trunc('month', invoice_date) AS invoice_cohort,
SUM(invoice_amount) AS invoiced,
SUM(collected_amount) AS collected_to_date,
ROUND(100.0 * SUM(collected_amount)
/ NULLIF(SUM(invoice_amount), 0), 1) AS collection_rate_pct
FROM modeled_invoice_collections
WHERE invoice_status <> 'VOIDED'
GROUP BY 1
ORDER BY 1;Common pitfalls
Comparing this month's cash with this month's invoices.→ Use invoice cohorts for collection effectiveness or label the period cash view separately.
Counting unapplied payments as collected invoices.→ Only include payments allocated to the invoice cohort; show unapplied cash separately.
Ignoring partial payments and credits.→ Model allocation amount at invoice-payment grain.
Where does this metric apply?
This metric commonly uses data from NetSuite, Xero, FreshBooks, QuickBooks, SAP, plus any reconciled warehouse or ledger models that provide the same business grain.
Related
Dashboards
Integrations
Metrics
Analytics
FAQ
Can collection rate exceed 100%?
It can if credits, overpayments, FX, or duplicated allocations are handled incorrectly. Reconcile at invoice grain.
When is a cohort complete?
Choose an observation window, such as 30, 60, or 90 days after invoice, and compare cohorts at the same maturity.