How to Track Profitability by SKU Without Losing Your Mind

Tracking profitability by SKU means computing, for each product, net revenue after marketplace fees and refunds, minus the landed cost of the units that shipped, minus the variable costs you can attribute to that product, on a schedule you can keep. The number you get is contribution margin per SKU. Sellers lose their minds over this because they try to do it in a spreadsheet from three channel exports, and the exports disagree with each other, with the bank, and with last month’s version of the same spreadsheet. The way to keep your sanity is to fix the data sources first and let the math fall out.

Why the spreadsheet version fails

A per-SKU P&L needs three feeds joined on SKU: what sold and for how much, what it cost to buy and land, and what the marketplace took. Each channel reports the first and third in its own format. Amazon’s settlement report gives you order ID, SKU, quantity, and an amount per amount type, which is what you need, spread across hundreds of rows per settlement. Shopify’s payout reports give you transactions and fees per payout. Walmart and eBay have their own. None of them carry your landed cost.

So the seller builds a VLOOKUP against a cost sheet that was accurate in March. By August the supplier has repriced twice, freight has moved, and the sheet says the bottle costs $7.40 when the last container landed at $8.60. Every margin in the workbook is now overstated by $1.20 a unit and nobody knows.

Step 1: Get landed cost per unit right and keep it right

Landed cost is the factory price plus inbound freight, duties, insurance, and any prep or labeling, divided by units received on that shipment. It changes per receipt. Record it per receipt, not per SKU, so that FIFO layers exist: the 3,000 units at $7.40 sell through before the 4,000 at $8.60 start hitting COGS.

This is also what the IRS expects. Publication 538 requires a business that must account for inventory to use an accrual method for purchases and sales and to value inventory consistently at the start and end of each year. A single blended cost updated when someone remembers is not consistent.

Step 2: Post revenue from the settlement, not the deposit

The deposit is net of everything. The settlement is the itemized version. Post from the settlement and you get, per SKU, the gross sale, the referral fee, the fulfillment fee, the refund, and the reimbursement, each as its own line. Post from the deposit and you get one number with no SKU on it, which ends the per-SKU exercise before it starts.

Do this for every channel. The fee structures differ, but the principle holds: the marketplace’s own report is the source, and the bank confirms it.

Step 3: Allocate the variable costs you can trace

Advertising is the big one. Amazon PPC and Shopify ad spend can be tied to a SKU or a campaign; do it, because a product with 40 percent gross margin and $6 of ad cost per unit sold is not a 40 percent product. Storage fees attributable to a SKU’s cubic volume and days in the warehouse belong here too. Payroll, software subscriptions, and rent do not; leave them below contribution margin so the per-SKU number stays comparable across products.

Step 4: The worked example

One SKU, one month, one channel. An insulated bottle on Amazon, September.

• Units shipped: 1,240. Units refunded: 62
• Gross sales: 1,240 times $29.99 equals $37,188
• Refunds: 62 times $29.99 equals $1,859
• Referral fees at the category rate on the settlement: $5,299
• FBA fulfillment fees on the settlement: $6,014
• Net revenue: $37,188 minus $1,859 minus $5,299 minus $6,014 equals $24,016
• COGS: 1,178 net units. First 900 from the $7.40 layer ($6,660), next 278 from the $8.60 layer ($2,391). Total $9,051
• Gross margin: $24,016 minus $9,051 equals $14,965, or 62 percent of net revenue
• Attributable ad spend: $4,340. Attributable storage: $410
• Contribution margin: $14,965 minus $4,340 minus $410 equals $10,215, or $8.67 per net unit

The same bottle on Shopify the same month, with card processing instead of referral and FBA fees and a lower ad load, will produce a different contribution per unit. That comparison, the same SKU across channels, is the one that changes where you spend the next ad dollar. The blended number hides it.

Step 5: Put it on a schedule you will keep

Monthly is the minimum, timed to the close. Weekly is better for the top 20 percent of SKUs by revenue. The report is two sorted lists: contribution per unit descending, to find where to push, and contribution per unit ascending, to find what to reprice, cut, or drop. Anything with negative contribution after ad spend is a decision that has already been made for you.

Step 6: Read the P&L that comes out of it

Once COGS is real and revenue is settlement-based, the consolidated P&L changes shape. Gross revenue stops being the headline. Fees get their own lines. Contribution margin per channel becomes visible. A seller who has never seen the statement in that form should expect some surprises, most of them in the direction of a product or a channel that looked fine on gross and does not on net. ConnectBooks has a walkthrough of the five lines on an ecommerce P&L that mislead sellers most often, and the list is the same set of problems per-SKU tracking is built to solve: gross revenue treated as real, estimated COGS, one blended marketplace line, mixed fixed and variable costs, and inventory expensed at purchase.

What to do this week

Pick your top ten SKUs by revenue. For each, find the last three receipts and compute landed cost per unit from the real invoices. Pull one settlement per channel and extract the fee lines for those ten. Build the contribution number for one month by hand. It will take a day. Then decide whether you want to do that every month by hand for 400 SKUs, or put the three feeds into a system that joins them on SKU and posts COGS as units ship. The math was never the hard part.