📋 Overview
Most sellers know their overall margin but cannot say which SKUs are actually funding the business and which ones are quietly consuming it. Building true unit economics per SKU is tedious spreadsheet work — pulling fees, returns, storage, and ad spend into one row per product — which is exactly the kind of task an AI assistant can accelerate.
This article shows you how to use AI to build and maintain a SKU-level contribution margin model, which prompt patterns produce usable output, and — critically — what you must verify yourself before you act on anything the model tells you.
🎯 Who This Is For
🌱 Beginner sellers
- You have 5–30 SKUs and track profit as one number at the end of the month
- You are not sure which fees apply to which product beyond the referral fee
- You want a repeatable spreadsheet you understand, not a black box
🚀 Advanced sellers
- You manage 100+ SKUs and need to rank them by contribution dollars, not margin percentage
- You want to allocate shared ad spend and return costs down to the SKU
- You are deciding what to discontinue, reprice, or reorder ahead of a restock cycle
🔑 Key Concepts You Need to Know
💵 Contribution margin per unit
Selling price minus every variable cost tied to selling one unit: landed product cost, referral fee, fulfillment fee, allocated returns, allocated ad spend, and promotion costs. It is what one sale contributes toward fixed overhead and profit.
📦 Landed cost
Unit cost plus freight, duties, and inbound placement or prep costs. Duty treatment changed when the de minimis exemption for low-value imports was eliminated, so low-value shipments no longer enter duty-free — use your actual customs entries rather than an older assumption.
🧾 Referral fee
Amazon’s commission on each sale, charged as a percentage of the total sales price. It ranges from roughly 5% to 45% depending on category, so never apply one rate across a mixed catalog. Confirm your category’s rate in Amazon’s published fee schedule.
🔁 Return-adjusted economics
A returned unit costs you the outbound fulfillment fee, a returns-processing charge in some categories, and often the full landed cost if the unit is unsellable. A SKU with a healthy margin and a high return rate can be a net loser.
⏳ Carrying cost
Monthly storage plus the aged inventory surcharge, which applies in escalating tiers as units sit longer — currently including 181–270 days, 271–365 days, 12–15 months, and a top tier beyond 15 months. Rates change; confirm the current tiers and amounts in Amazon’s FBA fee documentation.
🛠️ Step-by-Step Guide
1️⃣ Pull the source reports before you open any AI tool
AI cannot see your account. It can only work with what you give it. Collect, for a full recent period:
- Business Reports — units ordered, sessions, and Unit Session Percentage by ASIN
- Fee Preview — per-ASIN estimated referral and fulfillment fees
- Your settlement or payments data for actual fees charged, refunds, and reimbursements
- Reports > Fulfillment > Inventory Ledger — units on hand, aged, removed, or lost
- Measurement & Reporting > Sponsored ads reports — spend and attributed sales by campaign and advertised ASIN
A good result: one folder of CSVs covering the same date range, all keyed by ASIN or SKU.
2️⃣ Have AI design the cost stack, not supply the numbers
Ask the assistant to build the structure. Keep every value blank until you fill it from your own reports.
Prompt pattern: “You are helping me build a per-unit contribution margin model for an Amazon FBA catalog. List every variable cost line that should appear as a column, grouped as: product cost, Amazon fees, advertising, returns and reimbursements, promotions, and storage. For each column, state where in Seller Central the data comes from. Do not invent any fee amounts or rates — leave value cells blank.”
💡 Pro Tip: Add “if you do not know a value, write UNKNOWN” to every prompt. Models are far more likely to flag gaps when you give them explicit permission to.
3️⃣ Load actual fees, never modeled ones
This is the single largest source of error. Fee Preview is an estimate; your settlement data is what actually happened. Where the two disagree — usually because of dimensional weight, category changes, or promotion fees — use the settlement figure and investigate the gap.
Common pitfall: letting the AI “estimate a typical FBA fee” for a SKU you did not supply data for. Delete those rows rather than accept a plausible number.
4️⃣ Ask for formulas, then verify three SKUs by hand
Language models are unreliable at long arithmetic but good at writing spreadsheet formulas you can audit.
Prompt pattern: “Given these column headers, write Google Sheets formulas for: contribution margin per unit, contribution margin percentage, total contribution dollars for the period, and break-even ACoS. Show each formula referencing column letters, and explain in one sentence what each one does.”
Then calculate three SKUs manually — one high-price, one low-price, one high-return — and confirm the sheet matches. If any tool offers file upload with code execution for CSV analysis, that path is more reliable than pasting numbers into chat, but capabilities and file limits change often; check the vendor’s current documentation rather than assuming.
5️⃣ Allocate shared ad spend down to the SKU
Campaigns rarely map cleanly to one ASIN. Decide the allocation rule yourself, then have AI apply it consistently.
- Direct spend: campaigns advertising a single ASIN
- Shared spend: multi-ASIN campaigns, split by that SKU’s share of attributed sales within the campaign
- Brand-level spend: allocate by revenue share, and label it separately so you can view margin with and without it
A good result: allocated ad spend across all SKUs sums to your total spend for the period, within a rounding difference.
6️⃣ Add returns, promotions, and carrying cost
Ask the assistant to compute, per SKU, a return rate from units returned divided by units sold, then apply your own recovery assumption — what share of returned units become sellable again. State that assumption in a visible cell so you can change it later.
Do the same for coupons and deals: coupons carry a per-coupon fee plus a percentage of coupon-attributed sales, so promotional SKUs need their own cost line. Confirm the current structure in Amazon’s promotions fee documentation before modeling it.
7️⃣ Classify SKUs using thresholds you set
Do not ask AI what your cutoffs should be — it has no view of your cash position or category. Supply them.
Prompt pattern: “Using the attached table, classify each SKU into one of four buckets and give a one-line reason: SCALE (contribution margin above 20% and contribution dollars in the top third), FIX (positive margin but below 10%), TEST (negative margin driven by ad spend rather than fees), EXIT (negative contribution margin with fees and product cost alone). Sort by total contribution dollars descending. Do not add SKUs that are not in the data.”
💡 Pro Tip: Rank by contribution dollars first, margin percentage second. A 12% margin SKU doing high volume often funds more overhead than a 45% margin SKU selling a few units a week.
8️⃣ Run a monthly variance review
Once the model exists, the recurring value is explaining change. Give the assistant two periods side by side and ask it to rank the drivers of margin movement per SKU — fee change, price change, return rate, ad spend, or unit cost — and to state which it cannot explain from the data provided.
Then verify the top three explanations against the underlying reports before you act. Reconcile total contribution dollars against your settlement deposits; a large unexplained gap usually means a missing cost line, not a bad SKU.
💼 Real-World Examples
🧩 A 40-SKU home goods seller finds a hidden loser
A seller tracking blended margin at 22% builds the per-SKU model and discovers one bestselling oversized item has a fulfillment fee and return rate that push its contribution margin close to zero once allocated ad spend is included. The action: raise price modestly, reduce bid aggression on that ASIN, and improve packaging to cut damage returns. Over the following couple of settlement cycles, contribution dollars from the rest of the catalog stop being masked by that SKU, and the seller reallocates ad budget toward two mid-volume SKUs that were already profitable.
🧮 A 300-SKU seller uses AI to compress a two-day task
An experienced seller previously rebuilt the profitability sheet by hand each quarter. Using an assistant to generate the formula set, apply the ad allocation rule, and draft the variance summary, the rebuild becomes a monthly routine instead. The first run surfaces an arithmetic error the model introduced when it tried to total a column in chat rather than in a formula — caught by the three-SKU manual check. The durable gain is not speed alone; it is that a monthly cadence catches aged-inventory drift before surcharge tiers escalate.
⚠️ Common Mistakes to Avoid
❌ Letting the AI supply fee values
Sellers do it because the model answers instantly and confidently. But fee schedules change and models have a knowledge cutoff, so the number may be outdated or invented. Instead: every fee cell must trace to Fee Preview, your settlement data, or Amazon’s published fee schedule.
🚫 Applying one referral rate across the catalog
A single blended rate is easy to model and quietly wrong for mixed catalogs, where category rates span a wide range. Instead: map referral rate by category at the SKU level and let the model pull the rate from a lookup column.
⚠️ Ignoring returns and reimbursements
Return data lives in different reports from sales data, so it gets skipped. That inflates margin on exactly the SKUs most at risk. Instead: build return rate and unsellable recovery into the model as visible, editable assumptions.
❌ Acting on an AI conclusion without reconciling totals
Discontinuing a SKU based on an unverified table is an expensive way to learn the model dropped a column. Instead: reconcile the model’s total contribution against settlement deposits, and hand-check the specific SKUs you plan to cut.
📈 Expected Results
This is a measurement discipline, not a growth tactic — it changes what you decide, not what Amazon does. Realistically:
- Within one build cycle: a per-SKU contribution margin figure that reconciles to your settlement totals, and a ranked list of SKUs by contribution dollars
- Within one to two settlement cycles: the effect of your first repricing, bid, or discontinuation decisions becomes visible in contribution dollars per SKU
- Ongoing: less capital tied up in low-contribution inventory, and fewer surprises from aged inventory surcharges
The metrics to watch are contribution margin per unit, total contribution dollars per SKU, return rate by SKU, and allocated ad cost per unit. Blended account margin is the last thing to move, and it moves slowly.
❓ FAQs
🔐 Is it safe to upload my settlement reports to an AI tool?
Treat it as you would any third-party data sharing. Check the vendor’s current data retention and training policies in their own documentation, remove buyer names and addresses before upload, and prefer SKU-level aggregates over order-level exports. Policies differ by tool and by plan and change frequently.
🤖 Which AI tool is best for this?
Any ranking would be stale quickly. Judge on capabilities instead: can it accept your file sizes, can it execute calculations rather than predict them, and does it cite which rows it used? Confirm all three on the vendor’s documentation before you rely on the output.
📊 How do I allocate ad spend for SKUs in the same campaign?
Split by each SKU’s share of attributed sales within that campaign, using the advertised-ASIN detail in Measurement & Reporting > Sponsored ads reports. It is an approximation, so keep allocated ad cost in its own column and review margin both with and without it.
🏢 Should I include overhead and salaries per SKU?
Not in contribution margin. Keep fixed costs out so the SKU-level number stays a clean decision tool, then compare total contribution dollars against your monthly fixed costs separately to see whether the catalog covers them.
🔄 How often should I rebuild the model?
Monthly is enough for most catalogs, plus an extra run before any large reorder or fee change. Keep the same date-range logic each time so period-over-period variance stays comparable.