Build a Returns Analysis Report

Returns cost twice: the refund, and the handling, shipping and stock write-offs that come with it. A returns report should say which products and channels are returned most, why, and whether things are getting better. The common spreadsheet version divides the returns booked in a month by the sales booked in the same month — and gets misleading answers, because a return in March usually belongs to a sale in February or January. When sales jump in November and the returns arrive in December, the November rate looks excellent and the December rate terrible, though nothing about the products changed. This page builds the analysis with pandas the right way round: each return is linked to its original sale, rates are calculated by the month of sale, products and channels are compared, reasons are ranked, the small set of products behind most refunds is found, and the most recent months are flagged as incomplete because their returns have not all arrived yet. It belongs to Building Finance and Operations Reports.

Prerequisites

pip install pandas openpyxl

Two exports: sales lines from January to September 2026 with order number, date, product, channel and price, and returns with the order number they relate to, the date the return was received, the reason the customer gave and the refund. Each order here is a single item, which keeps the matching simple; with multi-line orders, match on order and line number instead. June had a large promotion.

Step 1 — Link each return to its sale

import pandas as pd

sales = pd.read_excel("sales.xlsx", parse_dates=["Date"])
returns = pd.read_excel("returns.xlsx", parse_dates=["Return date"])

linked = returns.merge(sales, on="Order", how="left", validate="one_to_one", indicator=True)
print("returns without a matching sale:", int((linked["_merge"] != "both").sum()))
linked["Days to return"] = (linked["Return date"] - linked["Date"]).dt.days
print(linked["Days to return"].describe(percentiles=[0.5, 0.9]).round(1)[["mean", "50%", "90%", "max"]].to_dict())

sales["Sale month"] = sales["Date"].dt.to_period("M")
sales["Returned"] = sales["Order"].isin(returns["Order"])
sales = sales.merge(returns[["Order", "Reason", "Refund"]], on="Order", how="left")

Every return carries the order number of the sale it came from, so a merge brings the product, channel, price and sale date onto the return. Two checks belong here: validate="one_to_one" guarantees no order was returned twice or sold twice in the data, and the indicator column counts returns whose order cannot be found — usually returns of sales made before the period the export covers, which should be reported, not silently dropped. In this export every return matches a sale. The time from sale to return is worth seeing on its own: half of returns arrive within 16 days, but one in ten take more than 35 days, and the slowest here took 99. That lag is what makes the month of return a poor denominator, and it decides how long a month must age before its return rate is final. Flagging each sale as returned or not, with the reason and refund attached, gives one table that every view below is built from.

Step 2 — Return rate by month of sale, with immature months flagged

by_month = sales.groupby("Sale month").agg(sold=("Order", "size"), returned=("Returned", "sum"))
by_month["Return rate"] = (by_month["returned"] / by_month["sold"]).round(3)

naive = (linked.groupby(linked["Return date"].dt.to_period("M")).size() / sales.groupby("Sale month").size()).round(3)
by_month["Naive (by return month)"] = naive

lag_90 = linked["Days to return"].quantile(0.9)
period_end = pd.Timestamp("2026-09-30")
by_month["Mature"] = (period_end - by_month.index.to_timestamp(how="end")).days >= lag_90
print(by_month.to_string())

Grouping by the month of sale answers the right question: of the items sold in March, what share came back? The naive version, returns received in a month divided by sales in that month, is shown next to it for comparison, and the difference is clearest around the June promotion. By month of sale, June's rate is 11.1%, in line with the other months, because the extra sales brought proportionally extra returns. By month of return, June looks unusually good at 9.1% — its large sales figure is divided into returns that mostly came from smaller May sales — and July looks bad at 13.7%, as June's returns arrive against normal July sales. January's naive rate of 4.3% is the extreme case: the export has no December sales, so the returns that would have come from them are missing altogether.

The most recent months carry a different caveat. September's sales have had at most thirty days to come back, and many returns take longer. A month is marked mature only once the time since its end is longer than the 90th percentile of the return lag — 35 days here, so August is not yet mature and September, at 4.9%, is far from it; before that its rate will still rise, and the report should say so instead of celebrating an apparent improvement.

Step 3 — Products, channels and reasons

mature_sales = sales[sales["Sale month"].isin(by_month.index[by_month["Mature"]])]
by_product = mature_sales.groupby("Product").agg(sold=("Order", "size"), returned=("Returned", "sum"), refunds=("Refund", "sum"))
by_product["Return rate"] = (by_product["returned"] / by_product["sold"]).round(3)
by_product = by_product.sort_values("refunds", ascending=False)
by_product["Share of refunds"] = (by_product["refunds"] / by_product["refunds"].sum()).round(3)
by_product["Cumulative share"] = by_product["Share of refunds"].cumsum().round(3)
print(by_product.to_string())

rates = mature_sales.pivot_table(index="Product", columns="Channel", values="Returned", aggfunc="mean").round(3)
print(rates.to_string())

reasons = mature_sales.dropna(subset=["Reason"]).pivot_table(index="Product", columns="Reason", values="Order", aggfunc="count", fill_value=0)
print(reasons.div(reasons.sum(axis=1), axis=0).round(2).to_string())

Restricting the product, channel and reason views to mature months keeps the comparisons fair. Sorting products by refund value and adding a cumulative share shows the concentration that is typical of returns: three products — the dress, the jacket and the boots — account for 81.7% of the money refunded, the boots despite a below-average return rate because they are the most expensive item, so they are where effort pays off. The channel view shows that the same product is returned more from the marketplace than from the web shop, and much less when bought in a store, where customers can try it on: the dress comes back 27.4% of the time from the marketplace, 17.9% from the web and 9.1% from stores. The reason mix, as shares per product, points at the fix: the dress, jeans and boots are returned mostly for wrong size, which calls for better size guidance on the product page, while the cheaper items are returned for a spread of reasons with no single cause.

Step 4 — The report

with pd.ExcelWriter("returns_analysis_2026-09.xlsx") as xw:
    by_month.rename(index=str).to_excel(xw, sheet_name="By sale month")
    by_product.to_excel(xw, sheet_name="By product")
    rates.to_excel(xw, sheet_name="Product x channel")
    reasons.to_excel(xw, sheet_name="Reasons")
    linked.drop(columns="_merge").to_excel(xw, sheet_name="Returns detail", index=False)
print("written")

The workbook starts with the monthly trend by sale month, with the immature months marked, then the product ranking with cumulative refund share, the product-by-channel rates, the reason mix and the linked detail. Refunds are only part of the cost of a return; if the business knows its average handling and shipping cost per return, adding it per product makes the ranking reflect the real cost and often moves cheap, frequently returned items up the list. A chart of the monthly rate with the immature months drawn differently — a lighter colour or a dashed line — prevents the most common misreading of a returns trend. To show both views of the trend side by side on a dashboard, see Compare Two Periods Side by Side in a Pivot.

Common pitfalls

• Dividing by sales in the return month. Link returns to sales and rate by month of sale.
• Reading recent months as final. Flag months younger than the usual return lag.
• Dropping unmatched returns. Report returns whose sale is outside the data.
• Ranking by rate alone. Weigh by refund value; a high rate on a cheap item may not matter.
• Ignoring channel. The same product can have very different rates by channel.

Conclusion

A returns analysis that people can act on links every return to the sale it came from and measures return rates by the month of sale, flagging recent months whose returns are still arriving. From the linked table come the product ranking by refund value with its cumulative share, return rates by product and channel, and the reason mix per product — enough to show which few products cost the most and what would reduce their returns.

Frequently asked questions

How do I calculate a return rate correctly? Link each return to its original sale and divide the number of returned items from each month's sales by that month's sales.

Why does my return rate jump after a promotion? Returns from the promotion arrive in later months; dividing them by those months' normal sales inflates the rate.

How long until a month's return rate is final? Until more time has passed than nearly all returns take — for example the 90th percentile of days from sale to return.

How do I find the products that cause most returns cost? Sum refund value by product, sort descending and add a cumulative share; the top few rows usually cover most of the cost.

Related

• Up one level: Building Finance and Operations Reports — finance and operations reports.
• Run a Pareto Analysis on Excel Data with pandas — the few products that matter.
• Build a Cohort Retention Table from Excel Data — another analysis by starting month.
• Build a Warranty Claims Report — returns after the return window.