Ciaren

Blog

How to Pivot Monthly Return Rates

Pivot monthly return rates in Ciaren using units sold and returned. See why two stores with rates of 50% and 10% can have a combined return rate of 14%.

About this post

Published
Reading time
3 min
Author
Ciaren team
pivot tablesaggregationdata analysis

One store sells 10 units in April and gets 5 back. A second store sells 90 and gets 9 back. The store return rates are 50% and 10%. Put those percentages in a pivot table, choose mean, and April reads 30%. Yet the returns desk handled 14 units out of 100 sold. Where did the extra 16 percentage points come from?

An average of store rates gives each store one vote. To answer “What share of units came back?” each unit needs one vote. Keep the two counts through the aggregation and divide once per month. Here is a small Ciaren flow you can check with a pencil.

Start with the two columns behind the rate

Save these rows as a CSV. In this example, each return belongs to the month of the original sale. If your returns file records the month a parcel arrived, match returns to sales before using this definition.

csv
month,store,sold,returned,metric
2026-04,Harbor,10,5,return_pct
2026-04,Hill,90,9,return_pct
2026-05,Harbor,20,0,return_pct
2026-05,Hill,80,4,return_pct

Load the file with File Input. Add Group By Aggregate, group by month and metric, and choose sum for both sold and returned. The April row should now contain 100 sold and 14 returned. May also has 100 sold, but just 4 returned. Those four counts are worth keeping in the output. If someone questions the percentage, they can trace the difference back to either sales or returns. Ciaren's grouping options

Calculate the rate before turning months into columns

On the monthly rows, add a Calculated Column named return_pct with the expression 100 * returned / sold. This fixture has sales in both months. In a real ledger, set aside any monthly row with zero units sold before dividing. A month with no sales has an undefined rate. A month with sales and zero returns has a measured rate of 0%. Calculated Column expressions

Branch the flow here. Send the monthly rows to a CSV File Output named monthly-return-counts so the person checking April can see the counts beside the rate. On the second branch, prepare the wide table for the spreadsheet. For a sheet that expects months across the top, the metric column supplies the row label. In Pivot Table, use metric as the index, month for columns and return_pct for values. Choose first. At this point each metric and month occurs exactly once, so there is only one value to put in each cell. Connect Pivot Table to a second File Output and choose Excel and name the wide report monthly-return-rates. Pivot settings and aggregation rules

metric2026-042026-05
return_pct144

Check the report when the next store lands

Suppose a late April file adds a third store with 50 units sold and 1 returned. The corrected April cell should read 10%: 15 returns divided by 150 sales. May should still read 4%. That single extra row tests both the denominator and the month grouping.

The same arithmetic applies to a quarter. To combine the original April and May rows, add 14 and 4 returns, then divide by 200 sales. The resulting 9% happens to equal the mean of 14% and 4% because the two months sold the same number of units. After the late file arrives, the quarter-to-date figure for those two months becomes 19 out of 250, or 7.6%. Keeping the counts makes the report work in both cases.