Duplicate products in an ecommerce catalogue: finding the same item twice in a supplier file

One product, two rows

A duplicate product in an ecommerce catalogue is one physical item held as two or more records: two SKUs, two product pages, two rows in a feed. In a supplier file it looks like this. Row 214 is TAP-ARC-CH, “Northway Arc Basin Mixer Chrome”, EAN 5060123456783. Row 1,309 is NW/ARC/BM/C, “ARC MONO BASIN MIXER TAP CHR”, barcode 5060123456783. Same barcode, same tap, two rows, and nothing in the spreadsheet says so.

Supplier files produce duplicates because nobody in the chain shares a key. The manufacturer codes the tap one way, the distributor another, and your buyer keyed a third into the purchase order. Put two files together and the same item is there twice, and it stays there through the import.

The cost lands on the shopper and the channel: two taps at two prices on one search page, two stock lines for one shelf, and a feed record Google Merchant Center flags as a duplicate. The supplier file is where the second row is cheapest to catch.

Four ways one product arrives twice

The barcode is the first clue, so be clear what it identifies. GS1 says a Global Trade Item Number “can be used by a company to uniquely identify all of its trade items”.¹⁴ Google puts it from the retailer’s side: identifiers “are assigned to each product by the manufacturer, so if you sell the same product as another retailer, the UPIs will be identical”.⁶ Two rows with one GTIN point at one trade item, unless the number itself is wrong.

• Two suppliers, one product. A distributor’s price list and the manufacturer’s trade file both carry the tap: different SKUs, titles and costs, one barcode.
• Two SKUs from one supplier. Same barcode again: a code was reissued, or the product moved range and the old row never left the file. Both rows look live, and only one has the stock.
• One SKU with two barcodes. A typing slip, a second valid number, or the code for a pack rather than the item. GS1 numbers each level of packaging separately: “A unique GTIN is assigned at every existing level of the packaging hierarchy above the retail consumer trade item/base unit level.”¹⁶ Google lists the 14-digit ITF-14 as the GTIN “for multipacks”, so a 14-digit number beside a 13-digit one deserves a second look.⁵
• The same SKU typed three ways. TAP-ARC-CH, tap-arc-ch and TAP ARC CH are one code to a person. Shopify’s help says SKUs are case-sensitive: ABC123 and abc123 are different SKUs.¹¹ Google’s IDs are case-sensitive too, but two that differ only by case may be taken for one product, so its rule is “Don’t use casing to make IDs unique.”⁴

These four are duplicates to merge. Pairs that only look like duplicates (variants, new and used copies, multipacks, bundles, own-label versions) come after the merge.

What Google, Shopify and WooCommerce do with a duplicate

Each platform tells products apart by one key, and each treats a repeat differently.

Google Merchant Center keys on the ID attribute, “a unique ID for each different product”, and recommends the SKU for it.⁴ Three issues in Needs attention deal with duplicates: Duplicate product, for offers with “the same set of attributes and values”; Duplicate identifier, for products sharing identifiers such as a GTIN or MPN; and Duplicate GTIN, for products sharing GTIN, multipack and condition, which Google says could mean “one product listed twice in your product data”.¹²³

Shopify keys on the handle, “The unique identifier for each product”, which is also used in the page’s URL, and rows that share a handle are read as one product, the later rows adding variants and images.⁹ A second copy under the same handle collides with the first: one cause Shopify gives for “Validation failed: options are not unique” is that “a product could be duplicated elsewhere in the CSV with the same handle”.¹²

The SKU is not the key. Shopify’s SKU page says “SKUs within your Shopify admin must be unique”, yet a repeat only shows a warning in the Inventory section, to be ignored if you need duplicates “for bundles or specific workflows”.¹¹ So two rows for one tap under two handles import as two products, and if their SKUs differ, as TAP-ARC-CH and NW/ARC/BM/C do, there is no warning either.

WooCommerce keys on the SKU or the product ID: its importer “uses product IDs or SKUs in the CSV to match existing products”.¹³ Imported as new products, a second SKU for the same tap makes a second product, and so does a blank SKU, because “WooCommerce generates one if this value is omitted”.¹³

The row to notice is the third. Neither store’s import matches on the barcode, so a pair that clears it reaches the feed as two IDs with one GTIN, and it is Merchant Center that flags it.

How to find each kind in a spreadsheet

A duplicate check is seven steps in a fixed order: exact keys first, fuzzy ones after, a person at the end.

1. Work on a copy. Keep the file as it arrived, as with any supplier file being taken on, and add the supplier’s name and original row number to every row of the copy, so each pair can be traced.
2. Clean the key columns. Trim spaces, put SKUs into one case in a helper column, and store barcodes as text so 5060123456783 cannot become 5.06012E+12. Google ignores spaces and dashes in a GTIN, so strip those too.⁵ Test every check digit, which Google requires to be “present and correct”, and set the failures aside, since a wrong number cannot be a match key.⁵ The post on GTINs, EANs and MPNs has the arithmetic.
3. Count rows per barcode. =COUNTIF(F:F, F2)>1 marks every row whose barcode appears more than once. Sort by barcode and the pairs sit together. This finds the two-supplier and two-SKU cases outright.
4. Count rows per brand and MPN. Join the two in a helper column and count again. Rows with a part number but no barcode surface here.
5. Count rows per normalised title. Lower-case it, strip punctuation and filler words, and fold spellings onto one form, chrome for CHR or CP, as the post on normalising attribute values sets out. Join it with the brand and the attributes that define a product in that category, finish and size for a tap, and count. This is the only step that finds a pair with no shared identifier.
6. Use the picture as a tie-breaker. Two rows on one image URL are worth a look, but a supplier may reuse one photograph across finishes, so a shared picture supports a candidate rather than finding one. Comparing the pixels, with a perceptual hash, is a job for software.
7. Review every candidate against its source. Open the manufacturer’s page for the barcode and decide: merge; keep both as variants, a bundle or a pack; or leave the pair flagged. Google’s rule for identifiers holds for that last case too: “Don’t make up, guess or include values from similar products.”⁶

What to keep when two rows merge

Here is the tap from the opening, before and after the merge.

Four rules decide the merged row.

Keep the key that is already live. If one of the pair is already in the store, its SKU, handle and feed ID stay. On Shopify the handle is part of the product page’s URL, and on Google “If you change the ID, you’ll overwrite your product and its history.”⁹⁴

Keep the barcode, once. One valid GTIN goes on the merged row. A second valid number for the same unit can go too: Google allows for products with “a global GTIN and a GTIN specific to the distributor” and asks for each value, and Shopify takes up to 20 barcodes per product or variant.⁵⁹ A case code is not a second barcode for the unit: it belongs in a pack column or nowhere.

Merge field by field, not row by row. The fuller row gives most values and the other fills its blanks. Where both rows have a value and they differ, as the heights do, keep the one that matches the manufacturer’s page. A difference no page can settle stays flagged for a person: two trade prices are a buying decision, not a data error.

Keep the losing code as a supplier code. NW/ARC/BM/C moves to a supplier codes column with the supplier’s name and price beside it, so that supplier’s next file maps onto the merged product instead of creating a second row.

When two rows are not a duplicate

The counts above find candidates, not verdicts. Four kinds of pair look like duplicates and are not.

Variants. The chrome tap and the brushed brass tap share a title stem but not a barcode: “Each product and variant of a product (different colours or sizes) has its own GTIN”.⁵ Google’s Duplicate GTIN issue also fires when variants wrongly share one number, and the fix is a unique GTIN for each variant plus an item group ID.³ Two rows, two GTINs, one item group.

Condition. A new and a used copy of one product carry the same manufacturer’s GTIN, with “used” in the condition attribute.⁵ Google’s duplicate check compares GTIN, multipack and condition together, so the two offers stay distinct.³

Multipacks and bundles. A manufacturer’s box of six cartridges, or the tap boxed with its waste, has a GTIN of its own. GS1 says the items inside a predefined assortment “carry their own, unique GTIN separate from the GTIN assigned to the assortment”, and Google wants the pack’s number “and not the individual products”.¹⁵⁵ A pack you make up yourself takes the single or main product’s GTIN, with “6” in the multipack attribute or “yes” in the bundle attribute, and neither attribute is used for a manufacturer’s pack.⁵⁸⁷ For a multipack, that count is what keeps it apart from the single under the Duplicate GTIN check.³

Own-label versions. The same tap relabelled with a distributor’s brand is what Google calls private-label, “produced by one company, but relabelled by the company selling it”.⁵ GS1’s rule is that a change to the primary brand on the item “requires assignment of a new GTIN”, and Google’s guidance for shop-brand and private-label products allows for there being none.¹⁷⁵ So the two rows carry different barcodes, or one carries none, and listing both is the buyer’s decision, not the duplicate check’s.

Before the next supplier file

Finding duplicates is part of the identity work that comes first in product data enrichment: a value can only be checked, merged or filled once it is clear which product it belongs to. It is also where RefynData’s product data enrichment service starts. Every SKU or barcode is matched to the real product, so two codes for one tap lead to the same tap, and every source page is checked to confirm it shows that product. A pair that no source settles stays flagged for a person, as the review step asks, rather than being merged on a guess.

Whoever does the work, do it before the import. A duplicate is one row to delete in a spreadsheet, and two product pages, two feed records and a redirect once it is live. Start with the barcode count: one formula and a sort finds every pair that shares a barcode. Then keep the supplier codes, and the next file from either supplier maps onto one product instead of bringing the second row back.