Calculate return rates with the right denominator

“Return rate” is not a complete metric definition. It may mean the share of orders with a return, the share of units returned, or the value refunded as a share of eligible order value. Those measures use different numerators and denominators, so they can all produce different, valid numbers.

The dataset in this lesson records refund events rather than physical item returns. We can calculate refunded-order and refund-value measures. We cannot infer returned-unit rates because the necessary unit-return records are absent.

That limitation is part of the answer. Renaming a refund table “returns” does not create the missing business evidence.

Inspect the events and the eligible population

The synthetic commerce lab has eight completed orders worth 104,000 paise. Its refund events are:

There are four events but only three refunded orders. O1005 has two partial refunds. Counting events as though they were orders would overstate the share of orders affected.

Our order-level metric is: completed orders with at least one recorded refund, divided by all completed orders in the fixture. It describes this fixed dataset, not a fully matured customer cohort or a financial reporting standard.

Calculate the refunded-order rate

SELECT
    COUNT(DISTINCT r.order_id) AS refunded_orders,
    COUNT(DISTINCT o.order_id) AS eligible_orders,
    100.0 * COUNT(DISTINCT r.order_id)
        / NULLIF(COUNT(DISTINCT o.order_id), 0) AS refunded_order_pct
FROM orders AS o
LEFT JOIN refunds AS r ON r.order_id = o.order_id
WHERE o.status = 'completed';

Expected output: three refunded orders, eight eligible orders and 37.5%.

The LEFT JOIN preserves completed orders with no refunds. Distinct order keys prevent multiple refund events from counting the same order more than once. COUNT ignores the NULL right-side keys on unrefunded orders. SQLite aggregate semantics.

An INNER JOIN would keep only refunded orders. If you then divided refunded orders by the remaining order count, the result could misleadingly be 100%. The denominator must remain the full eligible population.

Calculate the refund-value ratio separately

The total refund amount is 17,500 paise. Dividing by 104,000 gives approximately 16.83%. That differs from 37.5% because two of the refunded orders are only partially refunded.

Pre-aggregate refunds before combining monetary measures:

WITH refund_totals AS (
    SELECT order_id, SUM(refund_paise) AS refund_paise
    FROM refunds
    GROUP BY order_id
)
SELECT
    SUM(COALESCE(r.refund_paise, 0)) AS refunded_value_paise,
    SUM(o.order_total_paise) AS eligible_value_paise,
    100.0 * SUM(COALESCE(r.refund_paise, 0))
        / NULLIF(SUM(o.order_total_paise), 0) AS refunded_value_pct
FROM orders AS o
LEFT JOIN refund_totals AS r ON r.order_id = o.order_id
WHERE o.status = 'completed';

This preserves one row per order and prevents O1005's header value from being counted twice. The two percentages should be labelled distinctly in a dashboard: refunded-order percentage and refunded-value percentage.

Decide the time window before comparing cohorts

An order created yesterday has had less time to be refunded than one created three months ago. Comparing their observed refund rates can confuse customer behaviour with unequal observation time.

A cohort definition might allow a fixed follow-up period after order completion. Alternatively, an operational report might count refund events issued during a calendar month. Both can be useful, but they answer different questions.

The fixture has no refund timestamps, so it cannot demonstrate a fixed follow-up-window calculation. A real implementation needs the relevant event times and a rule for late-arriving or corrected events. Do not publish a “30-day return rate” from data that cannot identify that window.

Keep eligibility consistent

Should cancelled orders be eligible? Are exchanges included? Are shipping refunds part of the monetary numerator? What happens to a refund larger than the original amount? These are business rules to resolve with the process owner.

Also confirm whether a refund event represents an approved request, an attempted transfer or a settled payment. Using pending refund requests as though money has already moved can create another mismatch.

For quality checks, compare refund totals per order with the expected allowed range, flag orphan refund records and check for repeated event IDs. Preserve exceptions instead of silently capping them to make the rate look reasonable.

Explain the result in one precise sentence

For this fixture: “Three of eight completed orders have at least one recorded refund, a refunded-order rate of 37.5%; the recorded refunded value is 16.83% of their total order value.”

That sentence states the population and distinguishes order incidence from money. It does not claim the rate predicts future refunds or measures physical returns.

Exercise: add another partial refund to O1005 in a copy of the lab. Predict which metric changes. The refunded-order rate stays 37.5%, while event count and refunded-value ratio increase. Explain why that is the expected behaviour.

NeuraPath's Data Analytics with Generative AI programme includes the SQL, statistics and reporting skills used here. Defining the denominator is part of analytical correctness, especially when a generated narrative tries to turn several different rates into one confident headline.

Continue learning

This article is part of the SQL foundations for reliable analysis sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in SQL subqueries versus CTEs: make an audit-friendly query.
• Continue with SQL percentage change when the previous value is zero.