Calculating Weighted Average Cost for 14,000 ShipHero SKUs

Say a pallet of the same product arrives at a different cost than the last one. Maybe the supplier raised prices, maybe it was a rush order. Whatever the reason, you can't just overwrite the old unit cost with the new one. That would misstate the value of everything already sitting on the shelf. You need a weighted average: blend the cost of what's already on hand with the cost of what just arrived, in proportion to how much of each there is.

That's the problem a 3PL merchant brought us, across a catalog of roughly 14,000 SKUs living in ShipHero. The formula itself isn't the hard part:

New WAC = (old quantity × old WAC + newly received quantity × new price) ÷ (old quantity + newly received quantity)

Anyone who's touched inventory accounting has seen that equation before. What made this project interesting is what happens before you're allowed to plug numbers into it.

What does "newly received" actually mean?

We built the automation on Make.com, pulling purchase-order events from ShipHero's GraphQL API and writing the corrected cost back once it's calculated. Google Sheets holds the cost basis, quantities, receiving history, and audit trail: the ledger the whole system reasons from.

The complication showed up almost immediately: ShipHero's purchase-order updates report cumulative received quantities, not incremental ones. A webhook firing twice for the same partial shipment would, taken at face value, count those units twice and quietly skew every average downstream. So the workflow keeps its own memory per line item, keyed by line_item_uuid, and compares the latest cumulative total against the last one it saw. The difference is the real, incremental receipt. A repeated or duplicate webhook resolves to zero newly received units and gets ignored rather than double-counted.

There were smaller landmines too. The webhook payload didn't always carry the landed price the formula needs, which meant adding a follow-up GraphQL call to go fetch it. Product mutations on 3PL accounts needed the right customer_account_id scope or they'd silently apply to the wrong place. And zero-price lines had to be filtered out before they could corrupt a perfectly good cost basis.

Trust, but verify before you write anything live

Before this system touched a single live ShipHero value, we ran it in shadow mode: every calculation it would have made got logged to an AuditLog sheet, with no actual product_update mutation sent. We tested it against known numbers first (a $2 unit cost and a $4 unit cost, which should average out to exactly $3.00) and only switched on live writes once that checked out, alongside a separate backfill scenario that seeded the ledger for all roughly 14,000 existing products using their current cost as a starting point.

That audit trail is the real payoff. When a cost figure looks wrong six weeks from now, whoever's investigating doesn't have to guess which webhook caused it. They can open the sheet and see the prior basis, the new receipt, and the exact calculation that produced the current number.

Where it's headed

The system is live across the full catalog today. The one honest limitation on record: very large purchase orders can bump into Google Sheets' API throughput limits, and moving the ledger to something like Supabase is the documented next step if that becomes a bottleneck. The calculation itself wouldn't need to change, only where it keeps its memory.

If your platform doesn't support the costing rule your business actually needs, we'd like to hear what it looks like.