Blog · Board and management reporting · Sales teams
Analyze product mix from a transaction export using units, net revenue and contribution by channel. A worked table and spreadsheet formulas show which products support the portfolio and how to reconcile the result.
Product mix analysis compares each product's share of units, revenue and contribution using the same transactions and period. Calculate it within each channel before deciding which assortment deserves more attention. A product that leads shipment volume can contribute much less of the money available to cover fixed costs.
Start with the question: which products support contribution in the direct and reseller channels? Fix the date range, currency, channel assignments and product level before adding the numbers. The product taxonomy guide covers the choice between SKU, family and line; use one level consistently here.
Unit share = Product units / Portfolio units
Revenue share = Product net revenue / Portfolio net revenue
Contribution share = Product contribution / Portfolio contribution
Contribution = Net revenue − Variable costs
The denominator must match the numerator's scope. A product's revenue inside the direct channel divided by company revenue is its contribution to company sales, not its share of the direct channel.
Contribution also differs from gross profit. OpenStax defines contribution margin as sales less variable costs. Include variable selling and fulfillment costs under a documented policy; do not silently substitute ledger cost of goods sold, which may include fixed production overhead.
Use completed sales for September 2026 from a US supplier of replacement filter cartridges. Every quantity is an individual cartridge. Each transaction line needs a unique line identifier, date, channel, product, net units, net revenue and variable costs.
Apply credits, returns and discounts consistently to revenue and units. Match cost reversals to returned items, and investigate missing costs rather than treating them as zero. Exclude sales tax from revenue and reconcile net revenue to the sales ledger before analysis.
For the worked example, the export is summarized into four product and channel rows. Put the following table on a sheet named Mix, with headers in row 1 and data in rows 2 through 5. Columns A through E are inputs; F is calculated.
| A: Channel | B: Product | C: Units | D: Net revenue (USD) | E: Variable costs (USD) | F: Contribution (USD) |
|---|---|---|---|---|---|
| Direct | Core | 1,000 | 50,000 | 35,000 | 15,000 |
| Direct | Premium | 500 | 50,000 | 25,000 | 25,000 |
| Reseller | Core | 1,500 | 60,000 | 48,000 | 12,000 |
| Reseller | Premium | 500 | 40,000 | 28,000 | 12,000 |
| Total, row 6 | 3,500 | 200,000 | 136,000 | 64,000 |
Direct Core contributes 50,000 − 35,000 = $15,000. Reseller Core contributes 60,000 − 48,000 = $12,000 despite its larger unit count. Channel economics already differ before any shares are calculated.
The formulas below use US spreadsheet syntax. On Mix, enter =D2-E2 in F2 and fill through F5. Put =SUM(C2:C5) in C6 and copy across through F6.
To build C2:E5 from transaction lines instead of typing summaries, use a separate sheet named Sales: A date, B channel, C product, D units, E net revenue and F variable costs. Filter that export to the agreed period first. For units in Mix!C2, use:
=SUMIFS(Sales!$D$2:$D$50000,Sales!$B$2:$B$50000,$A2,Sales!$C$2:$C$50000,$B2)
Fill down through C5. For revenue in D2, change the sum range to Sales!$E$2:$E$50000; for variable costs in E2, use Sales!$F$2:$F$50000. Extend every range together if the file is larger. Google's SUMIFS documentation describes summing a range against multiple criteria, which keeps both product and channel in scope.
Add G1 Channel unit share, H1 Channel revenue share, I1 Channel contribution share and J1 Contribution rate. Enter these formulas in row 2 and fill through row 5:
G2: =IF(SUMIF($A$2:$A$5,A2,$C$2:$C$5)=0,NA(),C2/SUMIF($A$2:$A$5,A2,$C$2:$C$5))
H2: =IF(SUMIF($A$2:$A$5,A2,$D$2:$D$5)=0,NA(),D2/SUMIF($A$2:$A$5,A2,$D$2:$D$5))
I2: =IF(SUMIF($A$2:$A$5,A2,$F$2:$F$5)=0,NA(),F2/SUMIF($A$2:$A$5,A2,$F$2:$F$5))
J2: =IF(D2=0,NA(),F2/D2)
Format G:J as percentages with one decimal place. Covirage is not affiliated with Google Sheets.
| Channel | Product | Channel unit share | Channel revenue share | Channel contribution share | Contribution rate |
|---|---|---|---|---|---|
| Direct | Core | 66.7% | 50.0% | 37.5% | 30.0% |
| Direct | Premium | 33.3% | 50.0% | 62.5% | 50.0% |
| Reseller | Core | 75.0% | 60.0% | 50.0% | 20.0% |
| Reseller | Premium | 25.0% | 40.0% | 50.0% | 30.0% |
Direct Premium's unit share is 500 / (1,000 + 500) = 33.3%, while its contribution share is 25,000 / (15,000 + 25,000) = 62.5%. These answer different questions. Its contribution rate is 25,000 / 50,000 = 50.0%, which uses its own revenue as denominator.
Across both channels, Core totals 2,500 units, $110,000 revenue and $27,000 contribution. Premium totals 1,000 units, $90,000 revenue and $37,000 contribution. The company shares are:
| Product | Unit share | Revenue share | Contribution share |
|---|---|---|---|
| Core | 71.4% | 55.0% | 42.2% |
| Premium | 28.6% | 45.0% | 57.8% |
Core drives most units; Premium supplies most contribution. That supports reviewing availability and commercial attention for Premium, subject to demand and capacity. It does not establish that extra Premium sales would replace Core sales or that customers would accept a different assortment.
For changes between periods, use price volume mix analysis to separate price, volume and mix effects. For the ongoing presentation, product analysis dashboards own the dashboard question; product funnel analysis addresses progression before sales. Media mix analysis concerns marketing channels rather than the product assortment sold here.
Each channel's unit, revenue and contribution shares must separately sum to 100% before display rounding. Direct contribution is 15,000 + 25,000 = $40,000; reseller contribution is 12,000 + 12,000 = $24,000. Together they equal $64,000, also 200,000 − 136,000.
The company contribution rate is 64,000 / 200,000 = 32.0%. A second check weights every row's rate by its revenue, without averaging percentages:
=SUMPRODUCT(D2:D5,J2:J5)/SUM(D2:D5)
That must also return 32.0%. Revenue totals should tie to the filtered export and ledger; unit totals should tie to the same included transaction lines. Retain full precision in formulas and round only displayed percentages.
Bring a sample of your own sales export with product, channel, units, net revenue and variable costs. Covirage maps the columns, its tools compute and check the portfolio comparisons, and the external AI model explains them with every figure traced to its rows. Create a free Covirage account; account creation takes about a minute, then you set up your business and bring your file.
See revenue driver analysis for the supporting business use.
Product mix can describe the assortment offered, while sales mix describes the proportions actually sold. For transaction analysis, state whether the proportions use units, revenue or contribution. An unsold product may belong to the assortment but contribute nothing to the period's sales mix.
Use both when units are comparable, then add contribution. Units describe physical demand, revenue describes commercial scale, and contribution describes what remains after variable costs. A high volume product can have a smaller contribution share because of its price or cost structure.
Yes. A product with negative contribution has a negative share when total contribution is positive. Other products' shares can then exceed the total before the negative item offsets them. If total contribution is zero or close to zero, emphasize dollars because the shares become undefined or unstable.
No. Check customer demand, complementary purchases, contractual obligations and which costs would actually disappear. Contribution share describes the current portfolio. A removal decision needs a separate assessment of lost sales, avoidable costs and effects on other products.