Analistable

How to merge product data from several sources

By the Analistable team · Updated · 5 min read

Pick one key (GTIN where you have it, otherwise a mapped SKU), clean it in every source, then full-outer-join the catalogues so no product is lost. For each attribute, set a precedence rule — for example price from the ERP, title from the shop, weight from the supplier — and list conflicts for review instead of overwriting silently.

Part of our guide: How to join spreadsheets on a common column

Merging is not the same as stacking

Stacking puts rows from several files under one another. Merging product data is different: the same product appears in several sources, each holding some of its attributes, and you want one row per product with the best value for each field. That is a join on a product key followed by a decision, field by field, about which source to trust.

Typical sources and what each is good for
SourceUsually reliable forUsually weak on
ERP or stock systemCost, price, stock, internal SKUMarketing titles, images
Shop platform exportTitles, descriptions, URLs, categoriesCost, dimensions
Supplier feedGTIN, dimensions, weight, materialsYour prices and naming
Marketplace listing reportMarketplace IDs (ASIN etc.)Anything you edit elsewhere

Step 1: choose and clean the key

A GTIN (the barcode number: EAN-13, UPC-A and so on) is the best key when every source carries it, because it is globally unique per product variant. In practice your shop may only hold your SKU while the supplier holds their own code, so you often need a mapping table first, as described in combining supplier price lists.

  • Store GTINs as text. Excel shows long numbers in scientific notation and drops leading zeros, so 0123456789012 becomes 1.23457E+11.
  • Pad to a consistent length: a 12-digit UPC-A can be compared with a 13-digit EAN-13 by adding a leading zero.
  • Trim spaces and upper-case SKUs in every source before joining.
  • Check for duplicate keys within each source; a duplicate on one side multiplies rows after the join.

Step 2: full outer join, then pick values

Use a full outer join so products that exist in only one source still appear; those gaps are often the most important finding. In Power Query, Merge Queries with Join Kind = Full Outer does this — the join kinds explained page covers each option. In SQL, the precedence rules become COALESCE in the order you trust the sources:

SELECT
  COALESCE(e.gtin, s.gtin, f.gtin)        AS gtin,
  COALESCE(s.title, f.title, e.name)      AS title,
  COALESCE(e.price, s.price)              AS price,
  COALESCE(f.weight_kg, e.weight_kg)      AS weight_kg,
  CASE WHEN e.price <> s.price THEN 'price differs' END AS conflict
FROM erp e
FULL OUTER JOIN shop s     ON s.gtin = e.gtin
FULL OUTER JOIN supplier f ON f.gtin = COALESCE(e.gtin, s.gtin);

COALESCE returns the first non-empty value, so the order of arguments is your precedence rule. The conflict column records where sources disagree rather than letting one quietly win.

Worked example: four products, three sources

Merged product table with source of each value
GTINTitle (shop)Price £ (ERP)Shop price £Weight kg (supplier)Flag
5012345000017Oak side table89896.2
5012345000024Oak coffee table14913911.5Price differs
5012345000031Walnut shelf5959No supplier data
5012345000048342.1Not in shop

Of four products, one is clean, one has a £10 price mismatch between the ERP and the live shop, one lacks supplier weight (a shipping-cost problem), and one is stocked and costed but not listed for sale. Each flag is a task for someone; the merge's job is to surface them, not to fix them by guesswork.

Rules that keep merged product data trustworthy

  1. Write the precedence rules down per field, and agree them with whoever owns each system.
  2. Keep a Source column for important fields so anyone can see where a value came from.
  3. Never overwrite a non-empty value with an empty one; COALESCE and Power Query's conditional columns both make this easy.
  4. Treat titles and descriptions as text to compare, not join on; small wording differences are normal.
  5. Rerun the merge from the original exports each time rather than editing the merged output.

Variants deserve care. A t-shirt in five sizes is five GTINs but may be one product in the shop with five options. Decide whether the merged table is at variant level (usually best for stock and price) or parent level (for marketing copy), and keep a parent ID column so you can roll up either way.

Doing it without writing the joins

For a one-off audit, Analistable can load the ERP export, shop export and supplier feed together and answer questions like "products where the shop price differs from the ERP" or "stocked products not listed in the shop". It works out the join on GTIN or SKU, runs it with DuckDB in your browser, and shows the SQL. It reads your exports but doesn't update your shop or ERP, so corrections still happen in the source systems. For a repeatable feed build, Power Query or a script is the better long-term home, and the guide to joining spreadsheets covers both.

Frequently asked questions

Should I merge product data on SKU or GTIN?
GTIN if every source has it, because it is unique per variant across companies. Otherwise use your SKU and build a mapping table from each supplier's code to it.
Why did my product merge create duplicate rows?
One of the sources has the same key more than once, for example two rows for one GTIN. Remove or resolve duplicates in each source before joining.
How do I decide which source wins when values conflict?
Set a precedence per field based on which system owns it — usually price and stock from the ERP, content from the shop, physical attributes from the supplier — and flag disagreements for review rather than overwriting.
My GTINs show as 5.01235E+12 in Excel. Have I lost data?
Possibly. Excel keeps 15 significant digits, so a 13-digit GTIN displayed in scientific notation is usually intact, but leading zeros are lost. Import the column as text to be safe.
Can I merge product data in Google Sheets?
Yes, for modest catalogues. XLOOKUP or VLOOKUP pulls one source's fields into another on the GTIN, and IFERROR or IF(ISBLANK()) applies a fallback when the first source is empty. For several sources and thousands of rows, Power Query, SQL or a script is easier to keep correct, because each precedence rule lives in one place rather than in every cell.

Related guides