Which products are returned more than average?

The data

In SQL

SELECT sku, SUM(units) AS sold, SUM(returns) AS returned,
       ROUND(100.0 * SUM(returns) / SUM(units), 1) AS return_rate
FROM sold
GROUP BY sku;

Overall: 38 returns ÷ 700 units = 5.4%. By channel, LAMP-05 is 15% on Amazon but 2.5% on eBay — a difference worth investigating (listing photos, size description, or a damaged batch at one warehouse).

In a spreadsheet

Return rate =SUMIFS(Sales[Returned], Sales[SKU], A2) / SUMIFS(Sales[Sold], Sales[SKU], A2)
Above avg   =[@[Return rate]] > SUM(Sales[Returned]) / SUM(Sales[Sold])

Watch out for

• Returns lag sales: a return in October may be for a September sale. Compare over a longer window or match returns to the original order.
• Low volumes: 2 returns out of 10 is 20%, but not meaningful yet. Set a minimum units threshold.
• Return reasons (if exported) explain the rate — group by reason for the flagged products.

Ask it in Analistable: “Which SKUs have a return rate above the overall average, by marketplace?” Related: matching Amazon fees to orders.

Match returns to the original sale

When returns arrive weeks after the sale, a month's return rate mixes sales from different months. If both exports carry the order ID, attribute each return to its original order month instead:

Sale month =XLOOKUP([@[Order ID]], Sales[Order ID], Sales[Month], "")

Then calculate return rate per product and sale month. The rate for recent months will still rise as late returns arrive, so compare months that are at least one return window old.

Return rate by marketplace

Amazon's rate is almost three times eBay's. Part of that is product mix (TEA-02 sells only on Amazon), so compare the same SKU across channels, as with LAMP-05, before blaming the channel.

SELECT sku, marketplace, SUM(units) AS sold, SUM(returns) AS returned,
       ROUND(100.0 * SUM(returns) / SUM(units), 1) AS return_rate
FROM sold
GROUP BY sku, marketplace
HAVING SUM(units) >= 30
ORDER BY return_rate DESC;

The HAVING clause applies a minimum volume so tiny SKUs don't top the list by chance.

Excel 2019 and pivot tables

In a pivot table with SKU and Marketplace in Rows, add a calculated field (PivotTable Analyze › Fields, Items & Sets › Calculated Field, called Options or Analyze in older versions) with the formula =Returned / Sold. Calculated fields work on the summed values, so the rate per row is correct — averaging individual rates would not be.

Troubleshooting

Repeat monthly

• Stack each marketplace's sales and returns exports with a Marketplace column and the same SKU key.
• Track each flagged SKU's rate over three months to see whether a fix (new photos, better size guide) worked.
• Add return reasons where exported and group the flagged SKUs by reason.

Frequently asked questions

How do I calculate return rate per product?

Divide units returned by units sold for each product, using SUMIFS across the combined marketplace data.

Should I calculate return rate by channel?

Yes — the same product can have very different return rates on different marketplaces.

Why compare with the average?

It shows which products are unusual for your catalogue, which is more useful than an absolute threshold.

What minimum volume should I use?

Enough units that one or two returns don't swing the rate wildly, for example 30 or more per SKU and channel.

Why not average the return rates?

Averages of rates ignore volume. Sum returns and units first, then divide.

How do I calculate a return rate in a pivot table?

Add a calculated field of Returned ÷ Sold so the rate is calculated on summed values.

Related guides

• What sells best, and who spends most, across all our stores?
• Which invoices are more than 30 days overdue?
• Have any suppliers billed us twice?
• How do sales reps rank across all regions?
• How much has each customer spent, net of refunds?