Sales KPIs Explained: Row Grain, Duplicates and Reliable Totals
Learn how an order-line dataset becomes a trustworthy sales KPI, with SQL examples, expected totals and checks that prevent double counting.
What makes a sales KPI trustworthy?
A useful KPI begins with a precise business question and a defined row grain. Row grain means what one row represents: an order, an order line, a payment or a customer. Adding values before deciding this can produce a neat chart with the wrong total.
This original exercise measures gross line revenue for completed order lines. It excludes cancellations and says nothing about tax, refunds, discounts or profit. Real reports need explicit rules for those fields.
A small worked example
Order A has two lines worth 200 and 150. Order B has one line worth 300. Order C has a cancelled line worth 400. The completed gross revenue is 650, not 1050, and completed orders are 2, not 3.
The following read-only SQL uses a VALUES CTE supported by PostgreSQL-style SQL engines. Adapt syntax for your database.
WITH lines(order_id, line_id, status, revenue) AS (
VALUES ('A',1,'completed',200), ('A',2,'completed',150),
('B',1,'completed',300), ('C',1,'cancelled',400)
)
SELECT SUM(revenue) AS completed_revenue,
COUNT(DISTINCT order_id) AS completed_orders,
COUNT(*) AS completed_lines
FROM lines WHERE status = 'completed';Expected result: completed_revenue 650, completed_orders 2, completed_lines 3. The order identifier appears twice for A because its grain is a line, not because A is necessarily duplicated.
How do joins accidentally double-count revenue?
Imagine order A has two payment records. Joining every line to every payment by order_id creates four matching rows for A. Its 350 of line revenue becomes 700 if summed after that join. This is a many-to-many multiplication problem.
Before joining, aggregate the payments to one row per order, or use an existence check when you only need to know whether a payment exists. Then verify the row count and total before and after the join. DISTINCT on the final amount is not a general repair: two genuine lines can have the same price.
Which checks belong beside the dashboard?
- Check uniqueness of the composite key order_id plus line_id.
- Reconcile included and excluded statuses with the source export.
- Keep missing revenue separate from a genuine zero.
- Define whether revenue means gross, net or collected cash.
- Record currency and reporting date rules before combining markets.
For this sample, revenue per completed order is 650 / 2 = 325. Revenue per completed line is 650 / 3, about 216.67. Both calculations are valid, but they answer different questions.
Practice task
Add a second line to B worth 100. Predict the new revenue and order count, then run the query. The answer should be revenue 750 with 2 completed orders. Explain why the denominator stayed unchanged. As a second exercise, describe how a refund would alter your metric definition before changing the code.
Learning and references
AI Leaf Global Learn serves classroom learners in Mudichur/Tambaram, Chennai, and online learners across India. Confirm current course details directly; local tags describe the audience, not additional branches.
Explore Data Analytics.
Explore Power BI.
Oracle COUNT reference.
Oracle join concepts.
Prepared by AI Leaf Global Learn. The sample illustrates metric reasoning; database-specific syntax should be checked in your practice environment.