Map SKUs across Shopify, Amazon and TikTok without breaking margin

Map each source product or variant to a canonical product using its source system, account and native identifier. Keep the source SKU as an attribute, and apply cost using an explicit date and unit rule. Joining every order to a table on SKU text alone can duplicate revenue or assign the wrong cost.

Shopify recommends unique SKUs for product variants and warns that duplicate SKUs can affect integrations and inventory. That doesn't make a SKU globally unique across every store, marketplace account and historical catalog in your warehouse.

The useful output is a product crosswalk you can explain, not a spreadsheet of names that happen to look similar.

Start with the unit you sell

Suppose a generic product is sold as a single item on a website and as a two-pack on a marketplace. The names are almost the same. The economic units aren't.

Source listing	Canonical item	Units per sold pack	Item cost	Cost per sold pack
Website single	Product A	1	$8	$8
Marketplace two-pack	Product A	2	$8	$16

If the two-pack sells for $30 after discounts, its merchandise margin before other costs is $14. Mapping it to one unit of cost would report $22. The product match looks plausible, but the margin is wrong by $8.

Keep sellable packs and inventory components distinguishable. For a mixed bundle, use a separate component table with quantities. Don't copy the bundle's full sales value onto every component and then sum those rows as total revenue.

Use a crosswalk with enough context

For each mapping, retain source system, source account or store, native product or variant ID, source SKU, canonical product ID, effective start and end dates, mapping status and the reason for the match.

The native ID is the primary reference within its source scope. SKU text helps users review the match. A canonical ID lets reporting group equivalent items across systems without erasing the original identities.

If a source identifier changes, preserve the old mapping for the historical records that used it. If a SKU is reused for a different item, a timeless SKU lookup can rewrite the meaning of old sales. Effective dates make that change explicit.

For sources that lack a reliable identifier, document the fallback matching rule and its limitations. Similar names are candidates for review, not automatic proof. An unresolved product should remain unresolved until someone can establish the match.

Test the join before calculating margin

At the intended order-line level, each line should match one applicable sellable-product mapping. Zero matches need review. More than one applicable mapping is a conflict.

Compare row count, quantity and merchandise sales before and after the join. If adding product attributes changes those totals, investigate the matching logic. A distinct count of order IDs won't catch duplicated line amounts.

For example, two applicable cost rows joined to one $30 line produce two $30 rows. Summing them gives $60. Taking an average afterward doesn't repair the model; it can hide the duplication while breaking other measures.

Bundles require a deliberate exception to the one-row rule. Expand components only in a table designed for component consumption and cost. Keep sales at the sellable-line level, or allocate sales using a documented method if component-level revenue is required.

Give costs their own dates

Product identity and product cost solve different problems. A product can stay the same while its cost changes.

Keep cost amount, currency, cost basis, effective dates and the units covered. Decide which date selects the cost for the report. A reporting convention based on order date differs from a shipment-date or inventory-valuation calculation. State the convention and use it consistently.

The $8 example is a simple reporting cost assumption. It isn't a recommendation for an inventory accounting method. If your report must reconcile to finance, use the agreed valuation and allocation rules rather than substituting the latest catalog cost.

Unknown cost is not zero. Show known-cost margin with its coverage, plus the sales and units excluded because cost is missing. A product with no mapped cost shouldn't look like the most profitable item in the catalog.

Build a review queue people can use

A practical queue shows unmapped source items, conflicting mappings, overlapping effective dates, missing costs and unexpected pack quantities. Include source IDs and a short explanation so the owner can resolve the issue in the right system.

Prioritize by affected sales as well as row count. One high-value unmapped bundle can matter more than many inactive catalog records. Keep an audit trail of corrections and rerun affected periods intentionally.

In Sigma, a shared data model can expose the reconciled sales table and mapping exceptions to downstream workbooks. The mapping policy still needs an owner. Publishing a model doesn't validate ambiguous product identity or cost coverage for you.

Validate identity and timing together

Before release, trace a single item, a multipack, a renamed SKU, an item with a cost change and an unmatched product. Check their quantities, sales and costs back to the source records and your stated rules.

Also check that the same customer purchase hasn't arrived through multiple channels. Product mapping won't fix duplicate Shopify, Amazon and TikTok order records. And a correct product mapping won't choose the right refund reporting date.

These checks are part of why reliable ecommerce reporting takes time. Once the rules are explicit, a product-margin dashboard becomes easier to maintain because every exception has a place to go.