Revenue is a definition, not a number

The idea

Ask "what was revenue in 2025?" and you can get at least three correct answers from the same database: $2,410,186, $2,362,232 or $2,221,363. Nobody is wrong. Each number is built with a different rule, and the rule is the metric.

Every revenue figure is the answer to three questions. Which orders count? What price do you use: the price tag, or what the customer paid after discounts? And do you give credit back when an order is returned? Change any answer and the number changes.

Think of it as a ladder. You always start on the top rung with everything that was sold, and every rung down applies one rule that removes something. The lower you stop, the more careful the number, and the less it looks like the figure on a price tag.

Why it exists

Different people need different numbers. A merchandiser asking "how much did we list for sale?" wants gross sales. A marketer asking "what did the coupons cost?" needs gross and net side by side. A finance lead who has to pay the bills wants net revenue, because refunded money is already gone.

If those three stay unlabelled, every meeting turns into an argument about whose spreadsheet is right. Harbor & Pine fixes this by writing the rule down once and reusing it everywhere. In this track the rule is:

• Valid line: order_items.quantity > 0. The 24 lines with zero or negative quantity are data-entry errors.
• Booked order: status IN ('completed', 'refunded').
• Gross sales = Σ quantity * unit_price on valid lines of booked orders.
• Net sales = gross − Σ line discounts, where a line discount is line gross × discount_pct / 100.
• Net revenue = net sales − Σ returns.refund_amount. No shipping fees.
• Year: order_date in the calendar year; a refund counts in the year of the order, not of the refund.

The last rule matters more than it looks. A sofa sold on December 28th and returned on January 20th is a 2025 sale and a 2025 refund. If the refund were booked in January instead, 2025 would look better than it was and January would look worse than it was, for a decision made in the other month. Keeping the refund with its order keeps each year honest about its own sales.

How it works under the hood

Step 1: decide which orders count

The orders table holds every order ever started, and its status column is the filter. Booked means the customer paid. cancelled orders were never charged; pending ones may still be cancelled. refunded stays in on purpose: money was taken first, and the refund is subtracted later, in its own step. Dropping refunded orders at step one would remove the sale and the refund together, and your net revenue would be too high.

Step 2: price the lines, not the orders

Revenue lives on order_items, one row per product on an order. Multiply quantity by unit_price per line, then add up. Do not multiply an order count by an average price: discounts differ line by line, and the line is the unit at which they are recorded.

Step 3: subtract refunds from the right place

A refund is a row in returns, linked to an order_item_id. To put it on the ladder you join it to the lines, usually with a LEFT JOIN, because most lines are never returned and an INNER JOIN would silently throw those lines away. Then you sum refund_amount. In 2025 that is 616 returned lines worth $140,869.57.

Ratios inherit the definition

Once the ladder is fixed, every ratio on top of it is fixed too. The only choice left is the division, and the rule is simple: add up the top and the bottom first, then divide once.

Average order value is net sales divided by booked orders: $2,362,232.24 / 4,409 = $535.78. Count orders with COUNT(DISTINCT order_id), never lines, or a three-line order counts three times. A discount rate is total discount divided by total gross: $47,953.28 / $2,410,185.52 = 1.99%. Take the average of each order's own discount rate and you get a different number, because small orders then count as much as big ones.

How to check your own number

Two sanity checks catch most mistakes. First, walk the ladder in order and make sure each rung is smaller than the one above: if net sales exceed gross sales, a join has repeated lines. Second, compare your gross sales with a simple SUM over all valid lines without the status filter. The difference must equal the sales on cancelled and pending orders. If it does not, a filter is hiding or adding something you did not intend.

Common misconceptions

"Revenue is just the sum of the orders table"

The orders table has no money in it. Prices are on order_items, and refunds are in returns. Even then, adding every line includes cancelled and pending orders: in 2025 the cancelled orders alone would add $158,503 of sales that were never paid. Always say which statuses you counted.

"Cancelled and refunded orders are the same thing"

A cancelled order never happened financially. A refunded order did: the customer paid, then got it back. That is why one is filtered out and the other is subtracted. If you treat them the same, the refund rate is wrong, or revenue is.

"Net revenue is a better number than gross sales"

It is a different number. Gross sales answer "what did customers choose to buy at list price?", net revenue answers "what did we keep?". A bridge from one to the other (gross, minus discounts, minus refunds) is more useful than either, because it shows where $188,823 went.