Invoice Analytics and Billing Patterns

Understanding how invoices are structured — their size distribution, line-item composition, and pricing patterns — reveals billing inefficiencies and pricing optimisation opportunities. Invoice analytics is the starting point for revenue operations (RevOps) work: ensuring that what you're billing matches what you intended to bill.

Why Invoice Analytics Matters

Finance teams run invoice analytics to answer questions like:

• Are we pricing consistently, or do some account managers discount heavily?
• Are our high-revenue invoices from many small items or a few large line items?
• Are line item totals calculated correctly (quantity × unit_price = line_total)?
• Which product categories drive the most invoice value?

These queries catch billing errors before they become client disputes and identify where pricing policy is being applied inconsistently.

Invoice Size Distribution

Before analysing averages, understand the distribution. The average invoice amount often misleads — if most invoices are £500 but a handful are £50,000, the average is dominated by the outliers. Bucketing into size ranges reveals the true shape.

The CASE WHEN ... THEN 'bucket_label' pattern creates categorical buckets from continuous values. We then aggregate by bucket to see how invoice value is distributed:

WITH invoice_totals AS (
    SELECT invoice_id, SUM(quantity * unit_price) AS total
    FROM invoice_items GROUP BY invoice_id
)
SELECT
    CASE
        WHEN total < 500 THEN '< £500'
        WHEN total < 2000 THEN '£500 – £2K'
        WHEN total < 10000 THEN '£2K – £10K'
        WHEN total < 50000 THEN '£10K – £50K'
        ELSE '£50K+'
    END AS size_bucket,
    COUNT(*) AS invoice_count,
    ROUND((AVG(total))::NUMERIC, 2) AS avg_invoice,
    ROUND(SUM(total), 2) AS total_billed,
    ROUND(SUM(total) / SUM(SUM(total)) OVER () * 100, 1) AS pct_of_revenue
FROM invoice_totals
GROUP BY size_bucket
ORDER BY MIN(total);

What This Returns

Each row is one size bucket. pct_of_revenue shows what share of total billed revenue each bucket contributes. In most B2B businesses, the "large invoice" buckets (£10K+) generate 70-80% of revenue despite being a small minority of invoice count — the classic 80/20 pattern.

The SUM(SUM(total)) OVER () is a window function applied to an aggregate — it computes the grand total revenue across all buckets, allowing the per-bucket percentage to be computed in the same query.