Analyze product sales, customer behavior, and inventory trends in real-time
✓ Free to download · ✓ Excel 2016+ and Google Sheets · ✓ Custom version in 24h
Store analytics dashboards report on sessions and orders. What they rarely tell you is contribution after fees, shipping and returns — which is the number that decides whether a product line is worth keeping.
The actual columns, with sample rows
| A | B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|---|
| 1 | Order | SKU | Gross | Fees | Ship | Returned | Net |
| 2 | #10241 | TSH-BLK-M | 48.00 | 2.16 | 5.90 | No | 39.94 |
| 3 | #10242 | MUG-CER | 22.00 | 0.99 | 5.90 | No | 15.11 |
| 4 | #10243 | TSH-BLK-L | 48.00 | 2.16 | 5.90 | Yes | -8.06 |
| 5 | #10244 | HDY-GRY-S | 72.00 | 3.24 | 0.00 | No | 68.76 |
Why each one is written the way it is
=IF([@Returned]="Yes", -([@Fees]+[@Ship]), [@Gross]-[@Fees]-[@Ship])
Net contribution per order. A return does not net to zero — you keep the fees and the outbound shipping, which is why return-heavy SKUs can be loss-making at a healthy gross margin.
=SUMIFS(Orders[Net], Orders[SKU], [@SKU])/COUNTIFS(Orders[SKU], [@SKU])
Average net per SKU, the ranking that actually informs what to stop selling.
=COUNTIFS(Orders[SKU],[@SKU],Orders[Returned],"Yes")/COUNTIFS(Orders[SKU],[@SKU])
Return rate by SKU. Anything above about 15% usually means a sizing or description problem, not a quality one.
=SUMIFS(Orders[Gross],Orders[Customer],[@Customer])
Revenue per customer, the input to repeat-rate and cohort analysis on the Cohorts tab.
A worked example with real numbers
A clothing store ranked products by gross revenue and kept its best-selling t-shirt in the top three all year. Recomputed on net contribution, it fell to eleventh: a 23% return rate meant roughly one order in four returned €8 of unrecoverable fees and shipping. Adding a size chart to the product page cut returns to 14% within two months — a change that only became visible once returns were charged against the SKU that caused them.
Tab by tab
| Tab | Contents |
|---|---|
| Orders | One row per order line, with fees, shipping and return flag. |
| SKUs | Product reference with cost, category and supplier. |
| Cohorts | First-purchase month by customer, for repeat-rate analysis. |
| Dashboard | Net contribution by SKU, channel and month. |
| README | Which columns come from the platform export and which are entered. |
What is included, and what to look at next
Specific to this workbook, not generic
Yes — map the export columns onto the Orders sheet once. The fee and shipping columns are the ones that usually need attention, since platforms report them at order level rather than per line.
The template splits it by line value by default. Splitting by weight is more accurate if you have the data; splitting evenly per line penalises cheap add-ons and distorts their contribution.
Add a Cost column to the SKUs sheet and the net formula picks it up. Without it you are measuring contribution after fees, not true margin.
"This e-commerce dashboard has transformed how we track our online sales. The real-time insights have helped us optimize inventory and boost revenue."