Blog · How-to guides · Sales teams
Explore a business order export for customer concentration, product co-purchases and unusual monthly changes. This worked example shows the formulas, the reconciliation and how changing one order changes the findings.
A data mining Excel example can answer useful business questions with grouped revenue, product presence counts and period comparisons. Start with an order export, prove the totals, then treat each pattern as a question to investigate in the underlying records.
For a sales team, the goal is a short list of decisions: which customer concentration needs attention, which product pairing deserves review and which revenue change needs an explanation. Keep the analysis close to those decisions rather than collecting measures without an owner.
A US equipment distributor reviews its complete January and February 2026 order register. This small worked example contains six completed orders in USD. Each row represents a whole order, and revenue is net of discounts and credits under the same definition throughout.
Columns E and F are product presence flags. A value of 1 means the order includes that product; 0 means it does not. They describe presence, not quantities or product revenue.
Put the headers in A1:F1 and the orders in rows 2:7:
| Order | Month | Customer | Net revenue (USD) | Drill present | Bit set present |
|---|---|---|---|---|---|
| O101 | January 2026 | Aster | 6,000 | 1 | 1 |
| O102 | January 2026 | Aster | 4,000 | 1 | 1 |
| O103 | January 2026 | Birch | 3,000 | 1 | 0 |
| O104 | February 2026 | Cedar | 2,000 | 0 | 1 |
| O105 | February 2026 | Birch | 1,000 | 0 | 0 |
| O106 | February 2026 | Cedar | 2,000 | 1 | 1 |
| Total | 18,000 | 4 | 4 |
Order O105 contains other products, so both selected product flags are zero. Do not drop it: it still belongs in total revenue and the order denominator.
Check that order identifiers are populated and unique before proceeding. If your file has multiple rows per order, first establish an order-level summary without repeating the whole order amount on every product line. Review Excel data cleaning tools for the separate cleaning choices.
The starting revenue control is:
=SUM(D2:D7)
The result is $18,000. Keep that control visible beside every summary.
List Aster, Birch and Cedar in I2:I4. Calculate each customer's revenue in J2 and fill down:
=SUMIF($C$2:$C$7,I2,$D$2:$D$7)
Microsoft's SUMIF documentation describes using a criterion in one range to sum corresponding values in another. Here the customer name selects the order amounts.
In K2, calculate the customer's share of total revenue:
=J2/SUM($D$2:$D$7)
| Customer | Contributing orders | Revenue (USD) | Share calculation | Revenue share |
|---|---|---|---|---|
| Aster | O101, O102 | 10,000 | 10,000 / 18,000 | 55.6% |
| Birch | O103, O105 | 4,000 | 4,000 / 18,000 | 22.2% |
| Cedar | O104, O106 | 4,000 | 4,000 / 18,000 | 22.2% |
| Total | All orders | 18,000 | 100.0% |
Percentages are rounded to one decimal place; all calculations use the unrounded amounts. Prove that customer grouping has preserved revenue:
=SUM(J2:J4)-SUM(D2:D7)
The check returns zero. Aster accounts for more than half of this period's revenue, which gives the account owner a reason to review order timing and upcoming commitments. It does not establish that Aster is the most profitable customer because this export contains no cost information.
For a larger customer list, a PivotTable provides a separate summary-building workflow. Microsoft's PivotTable instructions describe organizing source data in columns with one header row and choosing summary fields. Check that revenue is summarized as a sum rather than a count.
Covirage is not affiliated with Microsoft.
Three orders contain both drills and bit sets: O101, O102 and O106. Four orders contain a drill. The denominators answer different questions.
| Question | Numerator | Denominator | Result |
|---|---|---|---|
| What share of all orders contains both products? | 3 orders | 6 orders | 50.0% |
| What share of drill orders also contains a bit set? | 3 orders | 4 orders | 75.0% |
Count orders containing both flags:
=COUNTIFS($E$2:$E$7,1,$F$2:$F$7,1)
Then calculate the two shares:
=COUNTIFS($E$2:$E$7,1,$F$2:$F$7,1)/COUNTA($A$2:$A$7)
=COUNTIFS($E$2:$E$7,1,$F$2:$F$7,1)/SUM($E$2:$E$7)
These formulas rely on the earlier identifier check and valid binary flags. Microsoft explains COUNTIFS as counting rows where all specified criteria are met.
The sales manager could review O103, the drill order without a bit set, to understand whether the customer already had accessories or bought a different configuration. The figures do not establish an unmet need. Pricing, existing inventory and order purpose are absent from the export.
Also keep order frequency separate from unit frequency. A large order and a small order each count as one purchase occasion here. Changing that definition changes the question and requires a different denominator.
Use the month labels in column B to total revenue. For January:
=SUMIF($B$2:$B$7,"January 2026",$D$2:$D$7)
Use the same formula with "February 2026" for February.
| Month | Orders | Revenue calculation (USD) | Revenue (USD) |
|---|---|---|---|
| January 2026 | O101, O102, O103 | 6,000 + 4,000 + 3,000 | 13,000 |
| February 2026 | O104, O105, O106 | 2,000 + 1,000 + 2,000 | 5,000 |
| Total | All orders | 13,000 + 5,000 | 18,000 |
February revenue is $8,000 lower, a 61.5% decline: (5000-13000)/13000. This compares complete monthly totals; it is not a comparison per selling day.
The customer rows help locate the change: Aster's orders appear in January and none appears in February. Ask the account owner whether that reflects normal purchasing timing before describing it as a lost customer. Use charts for the checked summary once the underlying question is clear.
Now change only O106's revenue from $2,000 to $6,000. Keep its customer, month and product flags unchanged. The input increases by $4,000, so total revenue must increase by the same amount.
| Measure | Original calculation | Changed calculation | Result after change |
|---|---|---|---|
| Total revenue (USD) | 18,000 | 18,000 + 4,000 | 22,000 |
| Cedar revenue (USD) | 2,000 + 2,000 | 2,000 + 6,000 | 8,000 |
| Aster revenue share | 10,000 / 18,000 | 10,000 / 22,000 | 45.5% |
| February revenue (USD) | 2,000 + 1,000 + 2,000 | 2,000 + 1,000 + 6,000 | 9,000 |
| February change from January | (5,000 - 13,000) / 13,000 | (9,000 - 13,000) / 13,000 | -30.8% |
Aster's revenue stays at $10,000, yet its share falls from 55.6% to 45.5% because the denominator grows. Product co-purchase shares remain 50.0% and 75.0% because neither order membership nor the presence flags changed.
Repeat both customer and monthly reconciliations against $22,000. This variation shows which findings depend on amounts and which depend on counts.
Bring an order workbook with stable identifiers, customer names, amounts and product details to Covirage. Covirage maps the columns, its tools compute and check the figures, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account, which takes about a minute, and bring a sample of your own data.
See self-service analytics for the supporting business approach.
Yes. Business exploration can use grouped totals, product presence counts and comparisons across periods to identify questions worth investigating. Those methods do not require training statistical models. Their usefulness depends on clean records, a defined business question and checks against the original export.
You need a stable order identifier and product identifiers for each order. Count whether each product is present in an order rather than treating every line as a separate purchase occasion. Keep canceled orders and returns under a stated policy so the denominator remains meaningful.
A pattern identifies where to investigate. A revenue decline could reflect order timing, a missing export, fewer customers or smaller purchases. Review the records and business context before choosing an explanation. Products appearing together also do not prove that buying either product causes the other purchase.