Blog · Board and management reporting
Reconcile sales-export rows before comparing customer frequency, order value, returns, and product mix. Use a worked transaction table and spreadsheet formulas that count orders once and keep refunds visible.
Customer transaction data analysis starts by reconciling the rows, then compares purchase frequency, net order value, refunds, and product mix. Decide whether each row is an order, a product line, or a payment event before counting it: those units produce different answers.
A US office-supply distributor is reviewing October 2026 purchases. Its export contains one row per order line, including completed purchases that were subsequently refunded. Merchandise sales are after discounts and exclude sales tax and shipping. Refunds are positive merchandise amounts matched back to their original lines.
The report includes October orders with refunds recorded through November 7, 2026. That is an order-based outcome view. A separate accounting-period report would place credits in their posting periods, so its monthly totals can differ. Record which view the reader is seeing.
Keep the untouched export for reconciliation. In the analysis sheet, preserve unique line IDs, order IDs, customer IDs, order timestamps, product labels, currency, and status. Exclude canceled orders before the calculations. If refunds arrive as separate negative rows, match them to their orders before using the layout below; do not also subtract them from amounts already net of refunds.
This is preparation for a customer comparison. Broader interpretation belongs in customer value analysis, while customer revenue analysis examines changes in account sales.
Place the six records below in Transactions!A1:F7. The two lines belonging to O1 must remain separate for product mix, but O1 is only one order.
| Line ID | Order ID | Customer | Product category | Sales (USD) | Refunds (USD) |
|---|---|---|---|---|---|
| L1 | O1 | A | Core | 200 | 0 |
| L2 | O1 | A | Add-on | 50 | 0 |
| L3 | O2 | A | Core | 150 | 30 |
| L4 | O3 | B | Add-on | 120 | 0 |
| L5 | O4 | B | Core | 80 | 80 |
| L6 | O5 | C | Core | 300 | 0 |
| Total | 900 | 110 |
Add G for net sales and H for an order-count flag. Enter these formulas and fill down:
G2 =E2-F2
H2 =--(COUNTIF($B$2:B2,B2)=1)
The expanding range gives the first occurrence of each order ID a flag of one and all later occurrences zero. Calculate the flags across the complete analysis sheet before building customer summaries. Do not treat a flag on one product line as an order's product classification.
Check line uniqueness before accepting the file:
=COUNTIF($A$2:$A$7,A2)
Every result must be one. Then confirm these controls:
=SUM(E2:E7)-SUM(F2:F7)
=SUM(G2:G7)
=SUM(H2:H7)
The first two formulas both return 790: sales of 900 less refunds of 110. The last returns five orders. The file has six product lines, so counting rows would overstate orders. Every order ID must also belong to one customer; investigate conflicts before reporting.
On Customers, put A, B, and C in A2:A4. Use B for order count, C for sales, D for refunds, E for net sales, F for net average order value, and G for refund share of sales.
Microsoft documents COUNTIFS for counting rows meeting conditions and SUMIFS for summing amounts meeting conditions. These Excel formulas apply those functions to the checked order flags and amounts:
B2 =COUNTIFS(Transactions!$C$2:$C$7,A2,Transactions!$H$2:$H$7,1)
C2 =SUMIFS(Transactions!$E$2:$E$7,Transactions!$C$2:$C$7,A2)
D2 =SUMIFS(Transactions!$F$2:$F$7,Transactions!$C$2:$C$7,A2)
E2 =C2-D2
F2 =IF(B2>0,E2/B2,"")
G2 =IF(C2>0,D2/C2,"")
Covirage is not affiliated with Microsoft.
| Customer | Orders | Sales (USD) | Refunds (USD) | Net sales (USD) | Net order value (USD) | Refund share |
|---|---|---|---|---|---|---|
| A | 2 | 400 | 30 | 370 | 185 | 7.5% |
| B | 2 | 200 | 80 | 120 | 60 | 40.0% |
| C | 1 | 300 | 0 | 300 | 300 | 0.0% |
| Total | 5 | 900 | 110 | 790 | 158 | 12.2% |
Percentages round to one decimal place. Overall net order value is 790 divided by 5, or $158. Refund share is 110 divided by 900, or 12.2%. Neither company figure is the simple average of customer percentages or averages; see weighted averages in Excel for the denominator principle.
Monthly purchase frequency is five orders divided by three purchasing customers, or 1.67 orders per customer, rounded to two decimals. That denominator excludes customers who did not buy. Use a complete opening customer list if the question instead concerns frequency across an established account base.
Customer B's fully refunded O4 remains an order placed. The low net order value and high refund share identify a return-related question, not automatically weak customer demand. Review the underlying order before changing account treatment.
With Core in Mix!A2 and Add-on in A3, calculate net category revenue in B2 and share in C2:
B2 =SUMIFS(Transactions!$G$2:$G$7,Transactions!$D$2:$D$7,A2)
C2 =B2/SUM(Transactions!$G$2:$G$7)
| Product category | Net sales (USD) | Net sales share |
|---|---|---|
| Core | 620 | 78.5% |
| Add-on | 170 | 21.5% |
| Total | 790 | 100.0% |
Core is 200 plus 120 plus zero plus 300, or 620. Add-on is 50 plus 120, or 170. Both categories together equal the customer summary and the transaction controls. This agreement proves consistent grouping, though it cannot prove the original export was complete; compare its controls with the source report too.
Bring your sales export with customer IDs, order IDs, product lines, and matched refunds. Covirage maps the columns; its tools compute and check the customer comparisons, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.
See sales insights for the supporting business context.
Keep customer ID, order ID, line ID, transaction date, product category, merchandise sales after discounts, and linked refunds. Add currency and order status when relevant. These fields establish who bought, what was purchased, and which amounts should be counted.
Frequency divides qualifying orders by customers over a stated period. A transaction count may instead count product lines, payment events, or refunds. Define the unit first and count each order once before comparing customer purchasing frequency.
For net order value, subtract attributed merchandise refunds from sales and divide by the original qualifying order count. Keep fully refunded orders in that denominator. If you also report retained-order value, label its different denominator explicitly.