Home›Learn›Analytics

How to find top customers by revenue

Customer revenue ranking with order count, AOV, and percentage of total — using SQL

Definition

What it is

Top customers by revenue is a customer ranking that shows each customer's total revenue contribution over a period, typically with supporting metrics: number of orders, average order value (AOV), and percentage of total revenue. It is one of the most fundamental customer analytics queries — identifying your highest-value customers, understanding concentration risk (what percentage of revenue comes from your top 10 customers), and informing retention and upsell priorities.

Formula

Customer Revenue Ranking Formula

Formula

Customer Revenue = SUM(order_total) per customer_id AOV = Customer Revenue ÷ Order Count Revenue Share (%) = (Customer Revenue ÷ Total Revenue) × 100

Use ROW_NUMBER() or RANK() window function to assign rank by revenue descending. Include net revenue (after refunds) for accurate ranking. Filter to a specific time window (last 12 months, last quarter) to make the ranking actionable rather than dominated by long-tenure customers.

Business impact

Why it matters

The 80/20 rule (Pareto principle) holds strongly in most B2B and many B2C businesses — roughly 20% of customers generate 80% of revenue. Identifying that top 20% allows you to prioritise retention efforts, offer loyalty incentives, and flag churn risk early when a top customer shows declining order frequency. Revenue concentration — if your top 3 customers represent 60% of revenue — is also a business risk worth quantifying.

The challenge

How most teams track this today

Most CRM and eCommerce platforms show customer-level revenue in some form, but rarely with the flexibility to add custom dimensions, filter by date range, or combine with other data. The SQL approach gives you full control over the ranking window, included metrics, and segmentation.

Run it on your data

Calculate this automatically with Taptic Data

Connect your SQL Server account and Taptic generates this calculation from plain English against your actual data — no Excel exports, no manual joins. The SQL runs against your real schema, your real tables, your real numbers.

SQL query for top customers by revenue with order count and AOV →Connect SQL Server →Try Free — $29.99/mo

FAQ

Common questions

Should I rank by gross or net revenue?

Net revenue (after refunds) gives a more accurate picture of customer value. A customer who places large orders but returns 40% of them is less valuable than their gross revenue suggests. If refund data is not available, gross revenue ranking is still useful — just note the limitation.

What is a healthy revenue concentration ratio?

For B2B businesses, having your top customer represent more than 25% of revenue is generally considered a concentration risk — losing that customer would be catastrophic. For DTC/eCommerce, the top customer rarely exceeds 2–3% of revenue, making revenue concentration less of an issue but cohort-level analysis more important.

Can I filter this ranking by product category or acquisition channel?

Yes. Because Taptic gives you raw SQL access, you can add WHERE clauses to filter by product, category, channel, or any other dimension in your order tables. You could, for example, rank top customers by revenue from a specific product line only.

How is this different from LTV?

Top customers by revenue ranks customers by actual historical revenue over a specific window. LTV (lifetime value) is typically a projected or modelled figure that estimates future revenue based on historical behaviour. The top customers ranking is backward-looking; LTV is forward-looking. Both are useful and complementary.

Analytics hub

All Analytics queries, use cases, and SQL examples in one place.

SQL Query Examples hub — queries for databases, warehouses, and eCommerce data →

Stop calculating this in spreadsheets

Connect SQL Server to Taptic Data and run this calculation automatically from plain English — against your real data, on a schedule, delivered to your team.

Start Free — $29.99/mo Try the Live Demo