Supplier data onboarding: from the spreadsheet a supplier sends to a catalogue you can import

What supplier data onboarding involves

Supplier data onboarding is everything that happens between a supplier’s product spreadsheet landing in your inbox and those products importing cleanly into your store or PIM. It is four jobs in one: mapping the supplier’s columns to your fields, cleaning what they sent, filling in what they left out, and checking the result before anything goes live.

The file is rarely built for you. A supplier’s spreadsheet is an export from their own systems, so the columns are named for their warehouse, the categories are in their words, and the codes mean something to their stock room rather than to your shoppers. Every new supplier brings a new version of the same problem.

The filling in is product data enrichment, and the guide to what product data enrichment covers goes into where missing values come from. This guide is about the pipeline around it: what to do with a supplier file from the moment it arrives.

Why a supplier template only gets you part of the way

A template helps, and it is worth having one, but it does not onboard a supplier on its own.

Most onboarding advice starts by asking every supplier to fill in your template: your columns, your units, your category names. When it works, it saves real time. In practice, templates often come back partly filled, weeks late or not at all, and a supplier with ten thousand products has little reason to retype them into your columns when their own system already exports a file.

Even a completed template needs checking. The columns get filled, but with “N/A”, with a width in centimetres where you asked for millimetres, with a colour that matches none of your filter values. A template sets out the minimum a supplier should send. It cannot make what they send correct.

The alternative is to accept each file exactly as it arrives and do the mapping yourself, once per supplier. It is how RefynData takes supplier files: any columns, any names, no template, and a SKU or barcode per row is enough to start.

What to ask a new supplier for instead

Ask a new supplier for the things that make their file easier to work with, none of which means retyping a single product.

• The export straight from their system, in the format it comes out in. A native export keeps its codes intact; a file someone has tidied by hand may not.
• Their product code and the barcode for every item that has one, in separate columns, so neither has to be untangled from the other.
• Images at the largest size they hold, as links or files, rather than whatever their own website shows in its gallery.
• Links to datasheets and manuals, where they exist. They are often where the specifications nobody typed in are written down.
• A named person to ask when a value could mean two things.

It is a short list and it asks for things the supplier already has, which is what makes it a reasonable request of a supplier who will never fill in your template.

Keep the supplier’s file exactly as it arrived

The first rule of onboarding is to leave the original file alone and work on a copy.

Part of the reason is provenance. When a value turns out to be wrong, you need to be able to show whether the supplier sent it or someone changed it on the way. The other part is that spreadsheet software changes data without asking. Microsoft’s own documentation says Excel “automatically removes leading zeros, and converts large numbers to scientific notation”.¹ For product codes, that is damage.

A 12-digit UPC that starts with a zero loses its first digit. A 13-digit EAN can be displayed as 5.01235E+12. Anything of 16 digits or more fares worse: Excel keeps 15 significant digits and rounds everything after them down to zero.¹ Shopify adds a warning of its own: a product CSV sorted in a spreadsheet editor such as Excel or Numbers can lose the link between products and their image rows, and with it the images.²

The fix is to bring identifier columns in as text rather than as numbers. Microsoft suggests formatting the columns as Text before any data goes in, or using Get & Transform (Power Query) to set them to text as the file is imported.¹ Better still, keep supplier files out of spreadsheet programs until the identifiers are safe. Why a barcode has to survive intact is covered in the guide to GTINs, EANs and MPNs.

Map each supplier’s columns once

Every supplier names its columns differently, so the core of onboarding is a mapping from their headers to your fields, built the first time a supplier’s file arrives and reused on every file after that.

Most of a mapping is renaming: their “Colour”, “colour” and “Finish/Colour” all become your Colour. Some of it is splitting, where one supplier sends height, width and depth in a single cell and another sends three columns. Some of it is deciding what not to import, like the warehouse bin location that means nothing on a product page.

Store the mapping per supplier. The second file from the same supplier then maps itself, and only a column you have not seen before needs a decision. Values work the same way: choose the word you want for each value once, and every future import lands on it. RefynData applies the same principle to the accessory links on supplier pages, which are learned once per supplier and then applied to everything they send.

Mapping is also where categorisation starts. A supplier’s category column is one more field, but it maps onto your category tree rather than into a text field, and that has rules of its own, set out in mapping supplier categories to your own tree.

The onboarding sequence, from inbox to import

With the columns mapped, the rest is a sequence, and the order matters because each step needs the one before it.

1. Keep the original. Save the file exactly as it arrived and work on a copy, with identifiers held as text.
2. Map the columns. Apply the supplier’s saved mapping, or build it if this is their first file.
3. Identify every product. Match each SKU or barcode to the real product, and check that every page you take data from shows that product.
4. Split, convert and fold. Split compound cells into their own fields, put every measure in one unit, fold spelling variants onto one word, and clear values that were never values, such as “N/A”. The guide to normalising attribute values shows why this comes before filling gaps.
5. Fill the gaps from sources. Find missing specifications on manufacturer pages, datasheets and other retailers’ listings, and keep a link to the source of every value you add.
6. File into your categories. Your category tree, not the supplier’s, with parts and spares kept apart from the products they fit.
7. Check and replace the images. Fetch every image link before the file is built, find the full-size original behind each thumbnail, and drop logos, badges and duplicates.
8. Write copy from the values. Titles, descriptions, page titles and search snippets, written only from values already in the data, to the lengths your platforms allow.
9. Review and sign off. Check each number against the rest of its category and each title against its own specifications. Nothing should import until someone has approved it.
10. Export in your import format. The columns, tab structure and file type your store or PIM expects, tested on a small batch first.

RefynData runs this sequence on the file as it was sent and returns a Google Sheet or Excel file with one tab per category, formatted to import into your store or PIM. Nothing is exported until you approve it, or deliberately skip that step.

Check every image link before the file is built

Image links are the part of a supplier file most likely to fail at import, and they fail silently until the importer runs.

A supplier’s image column points at their own website or their own image host. Pages get moved, hosts start refusing requests, thumbnails stand in for the full-size photograph, and some links were never images at all. Shopify’s importer needs each image URL to be “a publicly accessible direct link to the image”,³ so every one of those becomes a product without a photograph, found only after it has gone live.

The fix is to fetch every link while the file is still being built. On one of our recent jobs, for a musical instrument retailer, we fetched all 12,015 image links on the catalogue before the file was built. 3,566 came back broken or blocked, nearly a third of them. The import never saw one.

Fetching is also the moment to measure. A link that works can still point at a thumbnail too small to zoom into, a logo, a payment badge or the same shot twice. RefynData measures every image, finds the full-size original behind each thumbnail, drops the logos, badges and duplicates, and converts formats a store will not import.

What trips up the import itself

The last step usually fails for reasons that have nothing to do with the products: the file’s encoding, its size and its identifiers.

Shopify’s rules are a fair example of what an importer checks. The CSV must be UTF-8 encoded.³ It cannot be larger than 15 MB.² Each product needs a unique handle, and no two variants of a product can share the same option values.³ A row whose handle matches an existing product is never imported as a new one. Tick “Overwrite products with matching handles” and the file’s values replace that product’s, column by column, with a blank cell in the file blanking the value in your store; leave it unticked and the matching rows are ignored.² Both are worth knowing before a second supplier sends a product you already stock.

Other platforms and PIMs have their own versions of these rules. Whatever yours are, put a small batch through the real importer before the whole catalogue goes in, and check the result on the live product pages rather than trusting the import log alone.