Why a SQL Join Can Double Your Ecommerce Revenue

A revenue report can pass every syntax check and still count the same order several times. The trigger is often innocent: someone adds line items to filter by product, then joins refunds to calculate net sales. Both tables contain multiple records per order. Their combinations multiply the order rows before the final sum runs.

EcomToolkit’s recommendation is to make the unit represented by each row visible during every reporting change. This article uses a deliberately small, hypothetical dataset to explain join fanout, show a safer query pattern and distinguish technical correctness from the commercial definition of revenue. No example represents a merchant’s actual sales.

Table of Contents

• Name the grain before writing the join
• Follow one order through the multiplication
• Aggregate children before combining facts
• Why DISTINCT is not a universal repair
• Keep product questions separate from order questions
• Build a reconciliation gate
• Handle changing records and reporting periods
• The EcomToolkit view

Name the grain before writing the join

The grain is what one row means. An orders table may contain one row per order. A line-items table contains one row per order line. A refunds table may contain one row per refund transaction, refund line or refund event. Those last three descriptions are not interchangeable.

Write the key and grain above the query before selecting measures. Confirm uniqueness with data, not a table name. An orders export that includes historical snapshots can have several rows with the same order identifier even though the business system considers that identifier unique.

PostgreSQL’s table-expression documentation explains how joins produce matching row combinations. A left join preserves unmatched left-side records, but it does not limit each matched record to a single result. That distinction is central to reliable ecommerce aggregation.

A join may be valid for detail exploration and unsafe for a particular measure. Repeated order totals are useful context on a line-item report; summing those repeated totals is the error. Decide which columns are additive at the resulting grain.

Follow one order through the multiplication

Imagine order A has a total of £100, two item rows and two settled refunds of £10 each. Joining all three raw tables on order ID produces four rows: each item pairs with each refund. Summing the repeated order total returns £400. Summing refund amounts returns £40.

Subtracting those sums gives £360, even though the intended order-level calculation is £100 minus £20, or £80. The calculation is arithmetically correct on the wrong relation. A dashboard may display it confidently because neither the database nor the charting tool knows your intended business grain.

Now imagine order B has no refund and only one item. It may remain one row. This uneven multiplication is especially dangerous: the error is not a constant factor that an analyst can spot by comparing overall row counts. Orders with larger baskets or more service activity distort the result more strongly.

Aggregate children before combining facts

For an order-level question, first reduce each child fact to one row per order. Then join those summaries to the canonical orders table. The following illustrative SQL assumes that orders are already unique, refunds contain only settled transactions and all amounts use one reporting currency.

WITH item_counts AS (
  SELECT order_id, COUNT(*) AS line_count
  FROM order_items
  GROUP BY order_id
), refund_totals AS (
  SELECT order_id, SUM(amount) AS refunded_amount
  FROM settled_refunds
  GROUP BY order_id
)
SELECT
  o.order_id,
  o.order_total,
  COALESCE(i.line_count, 0) AS line_count,
  COALESCE(r.refunded_amount, 0) AS refunded_amount,
  o.order_total - COALESCE(r.refunded_amount, 0) AS retained_total
FROM orders o
LEFT JOIN item_counts i ON i.order_id = o.order_id
LEFT JOIN refund_totals r ON r.order_id = o.order_id;

The retained total is deliberately named cautiously. Whether it equals the business’s net revenue depends on how taxes, shipping, discounts, cancellations and refund components are represented. A correct join cannot repair an inconsistent accounting definition.

If the only need is to find orders containing a particular product, an existence condition can be clearer than joining all matching item rows. It asks whether a qualifying child exists without expanding the parent result. If you need line-level sales, build a line-level measure instead of copying an order-level amount onto every item.

Why DISTINCT is not a universal repair

Counting distinct order identifiers can recover an order count after fanout. It does not automatically fix a revenue sum. SUM(DISTINCT order_total) deduplicates equal numeric values, not repeated business entities. Two different orders worth £100 each would contribute only £100 to that expression.

Selecting distinct rows is similarly fragile. Adding a refund identifier or product attribute makes previously identical rows different again. A report that relies on accidental equality can change its total when a colleague adds an apparently harmless dimension.

Some semantic layers provide specialised protections. Google’s explanation of Looker symmetric aggregates describes key-aware handling of repeated facts. That is different from applying ordinary SQL DISTINCT to a measure. Looker’s relationship guidance also makes correct join relationships and primary keys central to the model.

Treat those features as model capabilities to validate, not permission to skip reconciliation. A wrong key, incorrect relationship or unsupported measure can still undermine the intended result. Inspect generated SQL when a semantic-layer total changes unexpectedly.

Keep product questions separate from order questions

An order containing shoes and accessories creates a definition problem before it creates a SQL problem. Does the footwear report include the entire order value, only footwear line sales or a proportion of shipping and order-level discounts? Each can answer a legitimate question, but the results differ.

For category revenue, allocate shared amounts using an explicit policy or report them separately. For orders containing a category, label the measure as whole-order value among qualifying orders. Do not add such category totals together and expect them to equal store revenue when orders can qualify for several categories.

Returns create another boundary. A refund transaction can cover multiple items and shipping. Joining it to every returned line without an allocation rule repeats the refund. Preserve the original transaction total as a reconciliation control while calculating any allocated line-level figures.

The broader analytics quality framework helps align these business definitions across finance and reporting. Join correctness is one layer within that agreement, not a substitute for it.

Build a reconciliation gate

Before a modified report replaces a trusted one, compare the parent facts on an identical population. Fix the reporting window, order status, currency and excluded test orders. A difference caused by filters should not be mistaken for fanout, and fanout should not be excused as a filtering difference.

Include hand-calculated fixtures: two equal-value orders, one order with several items and refunds, and an order with no child rows. These cases detect errors that large aggregate comparisons can conceal. Use exact monetary types or integer minor units according to the warehouse’s conventions.

Handle changing records and reporting periods

Refunds arriving later raise a separate question: are you measuring orders placed in a period after all known refunds, or financial activity occurring during that period? Pre-aggregation works for both, but the date filters belong to different facts. Document the choice beside the measure.

Resolve ingestion versions before aggregating. Summing every update to the same refund can double count money even without a join. Keep stable source identifiers and a deterministic current-record rule, then retain enough history to audit corrections.

Use the late-arriving events guide when historical reports need restatement. Annotate corrected periods and preserve the previous published total. Quietly changing a number without explaining the grain or time rule makes later reconciliation unnecessarily difficult.

The EcomToolkit view

Revenue should survive the addition of descriptive context. If adding a product label or refund field changes an order-level total, stop and inspect the relationship before debating commercial performance. The most useful reporting safeguard is a small set of invariants that every query revision must preserve.

If your warehouse and commerce totals diverge, request an EcomToolkit audit to trace the discrepancy from source records to the final measure.