What is invoice collection rate?
What does an invoice collection rate chart look like in Metabase?
Group invoices by issue month and chart the share collected at the same maturity for every cohort, with the dashed line marking the 95% goal. The climb across cohorts shows collections tightening until the newest cohorts cross the target, while a dip like November's usually isolates to a handful of disputed invoices in that cohort rather than a broad slowdown.
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.
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
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
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.