Updating Shopify Cost and Price by SKU When the Supplier File Has No Handle

A supplier price list usually carries three useful columns: the supplier's part number, a barcode, and a price. Shopify's product importer wants a fourth one the supplier will never send you: the Handle. That gap is why updating cost and price "by SKU" turns into days of copy-paste. What fixes it is a bridge table that translates the supplier's keys into Shopify's keys. You build it once and reuse it every time a new file lands.

Why the native import can't match by SKU

The product CSV importer identifies a product by its Handle, and a variant inside that product by its option values (Option1 Value, plus Option2 and Option3 if you use them). The importer treats Variant SKU and Variant Barcode as values to write onto the product and never uses them to find one. So a file that contains only SKU and a new cost has nothing the importer can use to find the row it belongs to.

Two consequences follow:

• You cannot import the supplier file directly. It has to be translated into Shopify's own layout first.
• Every column you leave in the translated file gets written, and an empty cell in a column that's present can blank that field on the product. Keep the file narrow.

The general routes for bulk editing costs, and the trap of blanked fields, are covered in how to bulk update cost per item. Below is the step before that: getting a supplier's SKU-only file to line up with your catalog.

Step 1: build the bridge table from your own export

Export all products to CSV. From it, keep one row per variant with these columns:

Then add a normalised key column next to SKU and barcode: trimmed, upper-cased, with spaces and dashes removed if your supplier is inconsistent about them. Do the same to the supplier's file. Most failed matches come from formatting rather than from missing products: leading zeros dropped by a spreadsheet, a trailing space, "ab-1020" against "AB1020". Import both files with identifier columns set to Text, or the spreadsheet will damage them before you start. Reading a supplier price list covers the rest of the formatting traps.

Check the bridge for duplicates before you use it. If two variants in your catalog share a SKU, a lookup will silently pick the first one and leave the other on the old cost. Fix the duplicates in Shopify or exclude them from the update and handle them by hand.

Step 2: match in two passes and keep the leftovers

Run the lookup by normalised SKU first, then by barcode for whatever didn't match. In Excel or Google Sheets that is one formula per pass:

=XLOOKUP(B2, Supplier!A:A, Supplier!C:C, "")

A worked example. A store has 2,400 variants; the supplier file has 1,850 rows.

1,610 + 150 + 90 = 1,850, so every supplier row is accounted for. Read the 90 unmatched rows. They can be products you don't carry, new items, renamed part numbers, or discontinued lines that were replaced. Put them in their own sheet; a renamed part number left unmatched means your old product keeps the old cost indefinitely. The handling of that pile is described in the price-file process.

Also check the other direction: variants in your catalog from this supplier that the file didn't mention. A row that disappeared is often the only sign a product was dropped.

Step 3: decide the new retail, not just the cost

Cost per item feeds your reports; customers pay Variant Price. If you change that too, set one rule before you fill the column instead of deciding row by row. The hold-margin formula is:

new price = new cost / (1 − target margin)

For one SKU whose cost rises from $11.20 to $12.40 with a 35% target margin: 12.40 / 0.65 = $19.08. Round to your price endings. At $18.99 the margin is (18.99 − 12.40) / 18.99 = 34.7%; at $19.49 it is 7.09 / 19.49 = 36.4%. Decide once whether you round up or down and apply it to the whole file.

Not every match deserves a price change. Products with a MAP floor, items on contract prices, and lines you deliberately run thin should be flagged and left out of the price column. Recalculating margin after a cost increase goes through when holding the margin is the wrong call.

Step 4: import a narrow file

Build the import file from the bridge table, keeping only:

• Handle
• Option1 Value (and Option2, Option3 if your products use them)
• Cost per item
• Variant Price, only if you decided to change prices

Delete everything else: images, inventory quantities, tags, descriptions, status. Each extra column is something the import can overwrite. Export a dated copy of the full catalog first; Shopify keeps no history of the cost field, so that export is your only undo. Import three rows, check them in the admin, then import the rest with "overwrite existing products" ticked.

The API route, for repeat updates

If the same supplier sends a file every month, the bridge can live in a script instead. Shopify stores cost on the inventory item and price on the variant, so it takes two calls. First, find the variant by SKU:

query {
  productVariants(first: 5, query: "sku:AB1020") {
    nodes { id sku product { id } inventoryItem { id } }
  }
}

Compare the returned sku to yours exactly, since search can return near matches. Then update cost with inventoryItemUpdate on the inventory item ID and price with productVariantsBulkUpdate on the product ID. For thousands of rows, one search per SKU is slow and eats rate limit; pull all variants once with a bulk operation, build the SKU map locally, and then send only the updates. Log every old and new value, because Shopify won't.

When a tool is worth it

The manual route works and costs nothing but time. Its limit is that the bridge goes stale: products get added, SKUs get renamed, and each month you rebuild the lookup and re-read the unmatched pile. Bulk-import apps can match on SKU directly and update only the columns you supply, which removes the Handle step. You still have to read the unmatched rows yourself.

CostSentry does the matching side of this: it takes supplier price lists in any column layout, matches them to the catalog by SKU, with aliases for supplier codes that differ from yours, and keeps the cost history Shopify doesn't. On paid plans it can write the cost per item back to Shopify. To see how a file lines up before changing anything, the free supplier margin checker runs the same matching on a pasted price list.