Skip to content

Order-level vouchers: allocate them for true SKU profit

Allocate order-level vouchers to products by line value: four methods compared, a worked example, Excel formulas, and no fake SKU profit.

By The DataxLand team· · 6 min read

Shop vouchers usually apply to the whole order: "30,000 ₫ off orders from 299,000 ₫". But profit reports need numbers per product: which SKU deserves more ads, which needs a price increase, which should go. Between those two levels sits a step most Excel files skip: splitting the order-level voucher across the order lines.

Skipping it does not change the shop's total profit, but it does distort the profit of each product — and SKU decisions rest on exactly that distorted number. This post compares four ways to split a voucher, shows which one is right for a profit and loss report, and gives the formulas to do it in Excel.

Which vouchers come off the shop's revenue

Before splitting, you need to know which discounts are really the shop's money.

Discount typeWho paysDeducted from shop revenue?Applies at
Product discount (strikethrough price)ShopYes, at line levelLine
Shop voucherShopYesUsually the whole order
Co-funded voucherShop and marketplace share itYes, the shop's shareWhole order or product
Marketplace voucherMarketplaceUsually not: the marketplace reimburses the shopWhole order
Free-shipping codeDepends on the programOnly the shop's share, best recorded as shipping costWhole order

The general rule: only the discount the shop funds reduces the shop's revenue. How marketplaces record each voucher type varies by program, so check against the voucher column in the settlement report in Seller Center before building your formulas. For example, according to Tuoi Tre (21 May 2026), for co-funded vouchers on Shopee the seller contributes 40% of the discount value, up to 50,000 ₫ per product (read on 16 Nov 2026). That 40% is the shop's; the rest is not.

Four ways to split an order-level voucher

Illustrative example (assumed): an order with one bottle of essential oil at 300,000 ₫ (cost of goods 150,000 ₫) and one candle at 60,000 ₫ (cost of goods 40,000 ₫), using a 50,000 ₫ shop voucher. To keep it simple, marketplace fees and ads are left out for now.

The order's true profit is a single number: 360,000 − 50,000 − 190,000 = 120,000 ₫. The question is how to split that 120,000 ₫ between the two SKUs.

MethodOil's voucherCandle's voucherOil profitCandle profitTotal across SKUs
1. No split (leave the voucher out of SKU profit)0 ₫0 ₫150,000 ₫20,000 ₫170,000 ₫ (wrong)
2. All on one line (the cheapest line)0 ₫50,000 ₫150,000 ₫−30,000 ₫120,000 ₫
3. Split evenly by number of lines25,000 ₫25,000 ₫125,000 ₫−5,000 ₫120,000 ₫
4. Split by line value41,667 ₫8,333 ₫108,333 ₫11,667 ₫120,000 ₫

Reading the table:

  • Method 1 makes total SKU profit 50,000 ₫ higher than the true profit. Over a month, the product report "earns" more than the accountant's report, and nobody can explain the gap.
  • Methods 2 and 3 keep the total right, but turn the candle into a loss-maker. The candle is a cheap add-on; if you keep loading the voucher onto it, you will wrongly conclude you should stop selling candles.
  • Method 4 keeps the total right and splits by how much each product "benefits" from the order: the oil is 300/360 = 83.3% of the order value, so it carries 83.3% of the voucher.

Method 4 is the sensible choice for a management profit and loss report, because the voucher exists to win the whole basket, not any single item.

The line-value allocation formula

Line value         = Price × Quantity − Line discount
Allocated voucher  = Order voucher × Line value ÷ Sum of line values in the order
Line net revenue   = Line value − Allocated voucher − Line refunds

Three details that often get missed:

  1. Use the value after line discounts, not the list price. A product already at 50% off carries less voucher.
  2. Round, then put the remainder on one line. 50,000 × 300/360 = 41,666.67 ₫. Rounding each line can leave the total 1 ₫ off the real voucher; put the remainder on the last line so the total always matches.
  3. Orders whose line values are zero (0 ₫ gifts): you cannot split proportionally, so split evenly or assign it to a line with value.

Step by step in Excel

Suppose sheet order_lines has columns A order_id, B sku, C price, D quantity, E line_discount; sheet orders has A order_id, B shop_voucher.

  1. Add column F line_value:

    =C2*D2-E2
  2. Add column G order_total_value (the sum of line values with the same order ID):

    =SUMIFS($F:$F,$A:$A,A2)
  3. Add column H order_voucher, looked up from sheet orders:

    =XLOOKUP(A2,orders!$A:$A,orders!$B:$B,0)
  4. Add column I allocated_voucher, split proportionally, or evenly when the order total is zero:

    =IF(G2=0,H2/COUNTIFS($A:$A,A2),ROUND(H2*F2/G2,0))
  5. Check: the sum of column I for each order must equal the order's voucher. Use a PivotTable by order_id and compare the total column with sheet orders; orders that are off by 1–2 ₫ are rounding, so add the difference to the last line.

From here, net revenue per SKU is F − I − refunds, and SKU profit then subtracts cost of goods, marketplace fees and ads as in how to calculate true profit.

Marketplace fees follow the voucher too

Most category fees and transaction fees are charged on the value after the shop's discounts, so a shop voucher reduces both revenue and the fee base. If you allocate marketplace fees to order lines, allocate them by line net revenue after vouchers, the same base the marketplace uses. Allocating fees by list price makes high-priced products carry more fees than they really do.

On DataxLand sample data (a simulated scented-candle and essential-oil shop, 1 Aug – 29 Oct 2026), GMV is 831,421,000 ₫ while net revenue is 755,554,000 ₫: about 75.9 million ₫ went to discounts, vouchers and refunds. That is more than enough to reshuffle the profit ranking of many SKUs if split wrongly.

How DataxLand does this

DataxLand splits order-level vouchers across lines in proportion to line value after discounts, right in the data model layer, and always keeps each order's voucher total equal before and after the split. Orders whose line values are zero are split evenly. Net revenue, contribution margin and profit after fees by product all read from the same allocated order-line table, so the SKU totals always equal the shop totals.

The definition of each metric (net revenue after allocated vouchers, refunds deducted only once) is in the metric handbook. You can see the results on the retail profit and loss dashboard, split by channel and product, or ask Si "which products lost money after vouchers last month" and read the Sources section to see which metric the number came from. See also the multi-channel profit and loss solution.

Frequently asked questions

Do shop vouchers count against revenue?

Yes, in the sense that the shop's net revenue is after the vouchers the shop funds. The GMV on a marketplace's overview page usually has not deducted vouchers; a profit and loss report should use net revenue.

Do marketplace vouchers reduce the shop's profit?

Usually not directly: the marketplace reimburses the discount. But joining some marketplace voucher programs comes with a program fee (for example Voucher Extra), and the shop pays that fee. Record program fees under marketplace fees, not mixed in with vouchers.

Should vouchers be split by value or by quantity?

By line value after discounts. Splitting by quantity makes cheap, high-quantity products carry too much voucher and look like loss-makers.

How do I handle the voucher when an order is partly returned?

Keep the voucher already allocated to each line; the returned line only subtracts its own actual refund. Do not re-allocate the voucher after the return, otherwise the remaining line's profit would change because of an event unrelated to it.

Read next: How to calculate true profit on Shopee, TikTok Shop and Lazada · Profit after marketplace fees · Profit and loss dashboard: 8 metrics

Related posts

Verified dashboards from your own data, this afternoon

Free plan with no time limit · No card required · Sample workspace ready in minutes