Price & Margin Realization Study
Same SKU, Different Price: Segmentation vs. Inconsistency

01Executive Summary

The headline question — is the same SKU being sold at materially different prices, and is that variance defensible? — has an unusually clean answer in this business: virtually all price variance is rational segmentation. Every SKU trades within a 6% band, and that band decomposes into exactly five discrete price tiers that are a deterministic function of line quantity. Across 3,236 lines and 23.7 months, exactly one line violates the schedule — and it runs in the company's favor (+$59.99).

The margin story is different. Because transactional discipline is airtight, the margin variance that exists is structural, not behavioral: it lives at the SKU level, not the deal level. Fourteen SKUs earn gross margins 15–46 points below their category medians — driven by supplier cost increases that were never passed through to list price. The Estes Park Chair now earns 2.6% gross margin; at its deepest volume tier it would sell below cost. Re-anchoring these 14 SKUs to defensible category medians is worth $24.8K–$63.4K of annualized gross profit, against ~$123K of annualized revenue on those SKUs.

02Price Variance: The Five-Tier Structure

Every product line on every invoice and cash sale was expressed as a ratio of its unit price to the SKU's list price (the maximum observed sold price — validated against the tier structure). The entire dataset collapses into five discrete points with no scatter between them:

03Customers, Regions, Reps: Testing for Favoritism

Regions: apparent regional pricing is channel mix

Stores (San Francisco loc 1, New York loc 3) transact 100% at list — every one of their 1,592 lines. The LA Distribution Center (loc 5) and Miami (loc 12) show 94–97% realization. This is not regional pricing: DCs take 5–35-unit wholesale orders and earn quantity breaks; stores ring 1–4-unit tickets. Within every location, tier tracks quantity exactly. Geography adds zero explanatory power once quantity is known.

Reps: a 0.26-point spread, fully mix-explained

Customers: volume earns the price, nothing else does

04Margin Bands, Discount Escalation & Drift Over Time

Below-band transaction test: zero findings

Each line's gross margin (net amount − costestimate) was compared to its SKU's median line margin. Result: not a single line falls more than 5 percentage points below its SKU's median — at either a 10-point or 5-point threshold, the outlier set is empty. There are no fire-sale deals, no margin-destructive one-offs, no quiet exceptions. Whatever margin variance exists within a SKU comes from the 3–6% tier schedule and from unit-cost changes over time — not from pricing behavior.

Discount escalation test: none

Realized price as a % of list is flat across the entire window — between 96.4% and 97.9% every single month, with no trend. Discounting is not deepening; the tier schedule has not crept.

Margin drift over time: stable, slightly rising

The real drift is in cost, not price

23 SKUs show unit-cost variation greater than 5% within the window. Where cost rose and list price did not follow, margin compressed silently. Two exemplars:

05Structural Outliers: Where Margin Actually Leaks

With transactional leakage ruled out, the defensible benchmark shifts from "SKU median line" to "category median SKU margin" — Apparel 46.2%, Beauty 51.9%, Home & Decor 48.6% (medians across 121 SKUs). Fourteen SKUs sit ≥15 points below their category median. These are pricing-file problems: cost moved, price didn't.

06Leakage Attribution & Annualized Value Model

Attribution by the three requested dimensions, annualized (window factor 12 ÷ 23.7 months = 0.507):

Interpretation. "Bringing outliers to the defensible median" has no meaningful value at the transaction level — outlying transactions essentially do not exist. All recoverable value sits at the price-file level: 14 SKUs whose margins drifted 15–46 points below category norms as costs rose. The realistic, demand-aware capture is the half-gap case: ≈ $24.8K of annualized gross profit, concentrated in five SKUs that account for 74% of it. The full-gap ceiling of $63.4K requires price increases up to 90% and should be treated as an upper bound, not a target.

07Recommendations

1. Reprice the two red SKUs immediately. Estes Park Chair (2.6% margin; below cost at T5 pricing) and The Bindel Jacket (10.5%) are economically broken. Even the conservative half-gap move (+31% / +25% price) is worth ~$8.1K/yr combined. If the market will not bear the price, the correct decision is discontinuation, not continued sale at cost.
2. Institute a cost-passthrough trigger. The margin-drift mechanism here is frozen list prices absorbing supplier increases (Ghost Whisperer GR M cost +100%, price unchanged). Add a standing review: any SKU whose unit cost moves >5% flags for repricing. The 23-SKU cost-variation query in §09 is the ready-made detector.
3. Close the manual-entry door. INV03 proves manually keyed invoices bypass the pricing schedule (and location coding). $60 today; the same door swings both directions. Require location + schedule validation on manual invoices, or a periodic run of the violation query in §09 (it returns exactly the exceptions, currently one row).
4. Fix zero-cost item records. 23 lines (IT-* electronics accessories) carry no cost estimate and report 100% margin — invisible to any margin control. Populate purchase costs on these items.
5. Protect the tier schedule — it is an asset. A five-tier, max-6% quantity-break structure applied with 99.97% fidelity across two years is rare discipline. Any future move to negotiated/contract pricing should be benchmarked against this baseline before it is allowed to erode.
6. Do not chase rep-level pricing controls. The data shows zero rep discretion in pricing. Controls, dashboards or comp-plan changes aimed at "rep discounting" would address a problem this business does not have.

08Methodology, Assumptions & Data Quality

Scope & universe

All CustInvc and CashSale product lines (mainline='F', taxline='F'), Sep 1 2024 – Aug 22 2026. Elimination subsidiary (id 4, xElim) excluded via transactionline.subsidiary. Item types: InvtPart, NonInvtPart, Kit, Assembly. SKUs with ≥5 sold lines qualify for tier/median statistics (121 SKUs, 3,236 lines, $2.105M). Excluded: SVC_Delivery Service (per-job service charge, $241–$11,311 per line — dispersion is job scoping, not pricing) and 13 low-volume SKUs (<5 lines, $2.4K combined).

Key definitions

List price = maximum observed unit price per SKU across the window. Validated: every SKU's observed prices sit at exactly {100, 97, 96, 95, 94}% of this anchor, confirming it is the true list price rather than an outlier. Unit price = |netamount ÷ quantity|. Gross margin = (|netamount| − |costestimate|) ÷ |netamount|; transactionline.costestimate is NetSuite's line-level estimated COGS. Defensible median = (a) at transaction level, the SKU's median line margin; (b) at SKU level, the category median SKU margin (Apparel 46.2%, Beauty 51.9%, Home & Decor 48.6%). Annualization = window totals × 0.507 (12 ÷ 23.7 months).

Assumptions

(1) Repricing uplift assumes constant unit volume and constant unit cost — no demand elasticity is modeled; the half-gap scenario exists precisely to hedge this. (2) costestimate is taken as the cost of record; where it varies within a SKU, actual costing (avg/lot) drives real margin. (3) Cash sales without a customer-level rep are classified as retail walk-in; rep attribution uses customer.salesrep (transaction-level salesrep is not exposed to SuiteQL in this account). (4) Single-currency account (USD) — no FX effects. (5) No discount or markup line items exist in the O2C data (verified — zero rows), so line net amounts are complete realized prices.

Data quality observations

(i) 23 lines on IT-* electronics items carry zero costestimate → excluded from margin statistics, flagged in §07. (ii) One line (INV03) has null location. (iii) SER_Box Spring sells at a flat $500 to 3 customers, 145 lines, zero variance — a fixed-price add-on, no action. (iv) Several locations in this account share names; all location references use internal ids (1, 3, 5, 12).

09Appendix: Source Queries

All queries are SuiteQL, run against production Aug 26, 2026. The shared m subquery derives each SKU's list-price anchor.

-- Every product line bucketed by unit price as a ratio of the SKU's list anchor
SELECT
    ROUND(ABS(tl.netamount / tl.quantity) / m.maxp, 2) AS price_ratio,
    CASE WHEN ABS(tl.quantity) >= 20 THEN 'T5' WHEN ABS(tl.quantity) >= 15 THEN 'T4'
         WHEN ABS(tl.quantity) >= 10 THEN 'T3' WHEN ABS(tl.quantity) >= 5  THEN 'T2'
         ELSE 'T1' END AS qty_tier,
    t.type AS channel,
    COUNT(*) AS line_count,
    ROUND(SUM(ABS(tl.netamount)), 2) AS revenue
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id
JOIN item i ON i.id = tl.item
JOIN (
    SELECT tl2.item AS item_id, MAX(ABS(tl2.netamount / tl2.quantity)) AS maxp
    FROM transaction t2
    JOIN transactionline tl2 ON tl2.transaction = t2.id
    WHERE t2.type IN ('CustInvc','CashSale') AND tl2.mainline = 'F' AND tl2.taxline = 'F'
      AND tl2.subsidiary <> 4 AND tl2.quantity <> 0 AND tl2.netamount <> 0
    GROUP BY tl2.item HAVING COUNT(*) >= 5
) m ON m.item_id = tl.item
WHERE t.type IN ('CustInvc','CashSale') AND tl.mainline = 'F' AND tl.taxline = 'F'
  AND tl.subsidiary <> 4 AND tl.quantity <> 0 AND tl.netamount <> 0
  AND i.itemtype IN ('InvtPart','Kit','Assembly','NonInvtPart') AND i.id <> 284
GROUP BY ROUND(ABS(tl.netamount / tl.quantity) / m.maxp, 2), qty_tier, t.type
ORDER BY 1, 2, 3

-- Lines whose realized ratio deviates >0.5% from the tier their quantity earns
SELECT t.tranid, t.type, t.trandate, c.entityid AS customer, i.itemid AS sku,
       ABS(tl.quantity) AS qty, ROUND(ABS(tl.netamount / tl.quantity), 2) AS unit_price,
       loc.id AS location_id, loc.name AS location_name
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id
JOIN customer c ON c.id = t.entity
JOIN item i ON i.id = tl.item
LEFT JOIN location loc ON loc.id = tl.location
JOIN ( /* list-anchor subquery m, as in Q1 */ ) m ON m.item_id = tl.item
WHERE t.type IN ('CustInvc','CashSale') AND tl.mainline = 'F' AND tl.taxline = 'F'
  AND tl.subsidiary <> 4 AND tl.quantity <> 0 AND tl.netamount <> 0
  AND i.itemtype IN ('InvtPart','Kit','Assembly','NonInvtPart') AND i.id <> 284
  AND ABS( ABS(tl.netamount / tl.quantity) / m.maxp -
        CASE WHEN ABS(tl.quantity) >= 20 THEN 0.94 WHEN ABS(tl.quantity) >= 15 THEN 0.95
             WHEN ABS(tl.quantity) >= 10 THEN 0.96 WHEN ABS(tl.quantity) >= 5 THEN 0.97
             ELSE 1 END ) > 0.005
ORDER BY t.trandate

-- Lines >5 margin points below their SKU's median line margin, with GP gap costed
SELECT t.tranid, c.entityid AS customer, i.itemid AS sku,
       ROUND(100 * (ABS(tl.netamount) - ABS(tl.costestimate)) / ABS(tl.netamount), 1) AS line_mgn_pct,
       med.med_mgn,
       ROUND(ABS(tl.netamount) * med.med_mgn / 100
             - (ABS(tl.netamount) - ABS(tl.costestimate)), 2) AS gp_gap_to_median
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id
JOIN customer c ON c.id = t.entity
JOIN item i ON i.id = tl.item
JOIN (
    SELECT tl2.item AS item_id,
           MEDIAN(100 * (ABS(tl2.netamount) - ABS(tl2.costestimate)) / ABS(tl2.netamount)) AS med_mgn
    FROM transaction t2 JOIN transactionline tl2 ON tl2.transaction = t2.id
    WHERE t2.type IN ('CustInvc','CashSale') AND tl2.mainline = 'F' AND tl2.taxline = 'F'
      AND tl2.subsidiary <> 4 AND tl2.quantity <> 0 AND tl2.netamount <> 0
      AND tl2.costestimate IS NOT NULL AND tl2.costestimate <> 0
    GROUP BY tl2.item HAVING COUNT(*) >= 5
) med ON med.item_id = tl.item
WHERE t.type IN ('CustInvc','CashSale') AND tl.mainline = 'F' AND tl.taxline = 'F'
  AND tl.subsidiary <> 4 AND tl.quantity <> 0 AND tl.netamount <> 0
  AND tl.costestimate IS NOT NULL AND tl.costestimate <> 0
  AND i.itemtype IN ('InvtPart','Kit','Assembly','NonInvtPart') AND i.id <> 284
  AND 100 * (ABS(tl.netamount) - ABS(tl.costestimate)) / ABS(tl.netamount) < med.med_mgn - 5

SELECT TO_CHAR(t.trandate, 'YYYY-MM') AS mo,
       ROUND(100 * (SUM(ABS(tl.netamount)) - SUM(ABS(tl.costestimate)))
             / SUM(ABS(tl.netamount)), 2) AS mgn_pct,
       ROUND(100 * SUM(ABS(tl.netamount)) / SUM(ABS(tl.quantity) * m.maxp), 2) AS realized_pct_of_list
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id
JOIN item i ON i.id = tl.item
JOIN ( /* list-anchor subquery m */ ) m ON m.item_id = tl.item
WHERE /* standard universe filters as in Q1 */
GROUP BY TO_CHAR(t.trandate, 'YYYY-MM')
ORDER BY 1

-- SKUs whose unit cost varied >5% within the window, with best/worst margin at list
SELECT i.itemid AS sku,
       ROUND(MIN(ABS(tl.costestimate / tl.quantity)), 2) AS min_unit_cost,
       ROUND(MAX(ABS(tl.costestimate / tl.quantity)), 2) AS max_unit_cost,
       ROUND(MAX(ABS(tl.netamount / tl.quantity)), 2) AS list_price,
       ROUND(100 * (1 - MAX(ABS(tl.costestimate / tl.quantity))
                      / MAX(ABS(tl.netamount / tl.quantity))), 1) AS worst_mgn_pct
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id
JOIN item i ON i.id = tl.item
WHERE /* standard universe filters + costestimate present */
GROUP BY i.itemid
HAVING COUNT(*) >= 5
   AND MAX(ABS(tl.costestimate / tl.quantity)) > MIN(ABS(tl.costestimate / tl.quantity)) * 1.05
ORDER BY MAX(ABS(tl.costestimate / tl.quantity)) / MIN(ABS(tl.costestimate / tl.quantity)) DESC

-- Rep rollup; swap the GROUP BY to c.entityid for the customer view
SELECT COALESCE(e.entityid, '(no rep assigned)') AS rep,
       COUNT(DISTINCT c.id) AS custs, COUNT(*) AS ln,
       ROUND(SUM(ABS(tl.netamount)), 0) AS rev,
       ROUND(100 * SUM(ABS(tl.netamount)) / SUM(ABS(tl.quantity) * m.maxp), 2) AS pct_of_list,
       ROUND(100 * (SUM(ABS(tl.netamount)) - SUM(ABS(tl.costestimate)))
             / SUM(ABS(tl.netamount)), 1) AS mgn_pct
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id
JOIN customer c ON c.id = t.entity
LEFT JOIN employee e ON e.id = c.salesrep
JOIN item i ON i.id = tl.item
JOIN ( /* list-anchor subquery m */ ) m ON m.item_id = tl.item
WHERE /* standard universe filters */
GROUP BY COALESCE(e.entityid, '(no rep assigned)')
ORDER BY rev DESC

-- Category medians across per-SKU margins (>=5 lines, cost present)
SELECT s.category, ROUND(MEDIAN(s.mgn_pct), 1) AS cat_median_mgn, COUNT(*) AS sku_count
FROM (
    SELECT COALESCE(cl.name, '(none)') AS category, i.itemid AS sku,
           100 * (SUM(ABS(tl.netamount)) - SUM(ABS(tl.costestimate)))
               / SUM(ABS(tl.netamount)) AS mgn_pct
    FROM transaction t
    JOIN transactionline tl ON tl.transaction = t.id
    JOIN item i ON i.id = tl.item
    LEFT JOIN classification cl ON cl.id = tl.class
    WHERE /* standard universe filters + costestimate present */
    GROUP BY COALESCE(cl.name, '(none)'), i.itemid
    HAVING COUNT(*) >= 5
) s
GROUP BY s.category
ORDER BY s.category
-- Outliers: per-SKU margins >=15 pts below these medians (14 SKUs) were repriced in a
-- computation layer: new_revenue = cost / (1 - target_margin); uplift = Δrevenue × 0.507 (annualization).