Total Cost of Ownership — Landed Cost and SKU Profitability

Landed cost = purchase cost + inbound freight + warehousing cost. This lesson assembles a full landed cost model per SKU and identifies the most and least cost-efficient products.

1. Purchase Cost per SKU

SELECT
    s.sku,
    s.name,
    s.category,
    SUM(po.quantity)                         AS total_units_received,
    SUM(po.quantity * po.unit_cost)          AS total_purchase_cost_usd,
    ROUND(AVG(po.unit_cost), 2)              AS avg_unit_cost_usd
FROM lg_skus s
JOIN lg_purchase_orders po ON po.sku_id = s.id
WHERE po.status = 'received'
GROUP BY s.sku, s.name, s.category
ORDER BY total_purchase_cost_usd DESC;

2. Allocated Freight Cost per SKU

Allocate inbound shipment costs to SKUs by weight share:

WITH sku_weights AS (
    SELECT
        po.sku_id,
        po.warehouse_id,
        po.quantity * s.weight_kg AS line_weight_kg
    FROM lg_purchase_orders po
    JOIN lg_skus s ON s.id = po.sku_id
    WHERE po.status = 'received'
),
shipment_weight AS (
    SELECT
        warehouse_id AS warehouse_id,
        SUM(freight_cents) / 100.0 AS total_freight_cost,
        SUM(weight_kg)          AS total_weight_kg
    FROM lg_shipments
    GROUP BY warehouse_id
)
SELECT
    s.sku,
    s.name,
    SUM(sw.line_weight_kg)    AS received_weight_kg,
    ROUND(SUM(
        sw.line_weight_kg
        / NULLIF(shw.total_weight_kg, 0)
        * shw.total_freight_cost
    ), 2)                     AS allocated_freight_cost_usd
FROM sku_weights sw
JOIN lg_skus s           ON s.id = sw.sku_id
LEFT JOIN shipment_weight shw             ON shw.warehouse_id = sw.warehouse_id
GROUP BY s.sku, s.name
ORDER BY allocated_freight_cost_usd DESC NULLS LAST;

3. Full Landed Cost Per Unit

WITH purchase AS (
    SELECT
        sku_id,
        SUM(quantity)              AS units,
        SUM(quantity * unit_cost)  AS purchase_cost
    FROM lg_purchase_orders WHERE status = 'received'
    GROUP BY sku_id
),
freight_alloc AS (
    SELECT
        po.sku_id,
        SUM(po.quantity * sk.weight_kg
            / NULLIF(shw.total_weight_kg, 0)
            * shw.total_freight_cost) AS freight_cost
    FROM lg_purchase_orders po
    JOIN lg_skus sk ON sk.id = po.sku_id
    JOIN (SELECT warehouse_id AS warehouse_id,
                 SUM(freight_cents) / 100.0 AS total_freight_cost,
                 SUM(weight_kg) AS total_weight_kg
          FROM lg_shipments GROUP BY warehouse_id) shw
        ON shw.warehouse_id = po.warehouse_id
    WHERE po.status = 'received'
    GROUP BY po.sku_id
)
SELECT
    s.sku,
    s.name,
    s.category,
    p.units,
    ROUND(p.purchase_cost, 2)  AS purchase_cost_usd,
    ROUND(COALESCE(fa.freight_cost, 0), 2) AS freight_cost_usd,
    ROUND(p.purchase_cost + COALESCE(fa.freight_cost, 0), 2) AS total_landed_cost_usd,
    ROUND((p.purchase_cost + COALESCE(fa.freight_cost, 0)) / NULLIF(p.units, 0), 4) AS landed_cost_per_unit,
    ROUND(100.0 * COALESCE(fa.freight_cost, 0)
        / NULLIF(p.purchase_cost + COALESCE(fa.freight_cost, 0), 0), 1) AS freight_pct_of_landed
FROM purchase p
JOIN lg_skus s ON s.id = p.sku_id
LEFT JOIN freight_alloc fa      ON fa.sku_id = p.sku_id
ORDER BY total_landed_cost_usd DESC;

What This Returns

sku_code      | name      | units | purchase_cost | freight_cost | landed_cost_per_unit | freight_pct
---------+-----------+-------+---------------+--------------+----------------------+-----------
SKU-025  | Interface | 1450  |     132185.00 |     14230.00 |               100.98 |       9.7
SKU-024  | Display   | 1400  |     124600.00 |     13890.00 |                99.64 |      10.0
...

4. Category-Level Cost Summary

WITH landed AS (
    -- simplified version using purchase cost only
    SELECT
        sk.category,
        SUM(po.quantity * po.unit_cost) AS purchase_cost,
        SUM(po.quantity)                AS units
    FROM lg_purchase_orders po
    JOIN lg_skus sk ON sk.id = po.sku_id
    WHERE po.status = 'received'
    GROUP BY sk.category
)
SELECT
    category,
    units,
    ROUND(purchase_cost, 2) AS purchase_cost_usd,
    ROUND(purchase_cost / NULLIF(units, 0), 4) AS avg_cost_per_unit
FROM landed
ORDER BY purchase_cost DESC;

Key Takeaway

Weight-based freight allocation is the standard method for attributing shared shipping costs to individual SKUs. The formula (line_weight / shipment_total_weight) × shipment_cost is a proportional allocation. Other allocation methods include value-based (proportion of invoice value) and volume-based — the choice depends on what drives actual carrier costs for your commodity type. Always verify your allocation adds up to 100% of total freight spend before using landed costs for pricing decisions.