Product data is the project nobody scoped

Product data preparation for ecommerce is the work of turning supplier spreadsheets, PDFs and legacy exports into clean, consistent product records before they reach your storefront. It covers SKUs, variants, attributes, units, categories, images and SEO fields, loaded into your platform or a PIM through a validated import — not a CSV dumped on launch day.

Key Takeaways

• Product data preparation is a scoped workstream, not a launch-week task. Budget weeks of review time, not hours.
• Most import failures come from taxonomy, units and encoding, not from the platform you chose.
• A spreadsheet is fine for a small single-channel catalogue; a PIM earns its keep once variants, channels or languages multiply.
• Every import needs a dry run against a staging store, a row-count reconciliation and a rollback copy of the live catalogue.
• Supplier data is never clean. Assume 10–20% of rows need a human decision and staff that review.
• Keep source files, mapping documents and transform scripts under version control — the second import is always harder than the first.
• Catalogue data decays. Prices, stock and specs drift, so someone has to own it after go-live.

What is product data preparation in ecommerce?

Product data preparation is the work of converting raw supplier files into records a storefront can actually sell from: one row per sellable SKU, typed attributes, consistent units, mapped categories, linked images and a stable identifier. A SKU is the smallest unit you track and sell; an attribute is any named property, such as colour, voltage or pack size.

It sits between procurement and merchandising. The supplier hands you an Excel export with merged cells and a colour column that says "Blue (Navy)". Your platform wants a category path, a decimal price, an image URL and a GTIN. Everything between those two states is the project nobody scoped.

Why does unprepared product data break an ecommerce launch?

Unprepared data breaks a launch through search, feeds and returns rather than through the storefront itself. Faceted search collapses when 40% of rows have an empty material attribute, Google Merchant Center rejects items with a missing or invalid GTIN, and wrong pack sizes create returns that cost more than the margin on the sale.

The failure is quiet. The site loads, the homepage looks fine. Then a customer filters by size, gets eleven results out of four hundred, and leaves. We have watched teams spend a fortnight tuning page speed while the real problem was that half the catalogue had no category assigned at all.

Do you need a PIM, or is a spreadsheet enough?

Use a spreadsheet while one person can hold the whole catalogue in their head, and move to a PIM — a product information management system — when variants, channels or languages multiply. The usual trigger is the third sales channel, or the first time two people edit the same file on the same day.

PIM software carries real overhead: it is another system to license, integrate, back up and train people on. A retailer with 3,000 SKUs and one website does not need one. A distributor pushing 8,000 SKUs to its own site, a marketplace and a Shopping feed probably does.

Option	Fits when	Main cost driver	Watch out for
Spreadsheet and import template	Under roughly 2,000 SKUs, one channel, one editor	Human review time	Version confusion, silent overwrites
Staging table and script	Repeatable imports, API sources, nightly price and stock sync	Engineer time to build and maintain	Nobody owns it after handover
PIM platform	Multiple channels, languages and large variant trees	Licence, integration and training	Duplicate source of truth, unused features

How does product data preparation work under the hood?

The mechanism is a staging table plus deterministic rules. You land raw rows unchanged, normalise them with code you can re-run, validate against a declared schema, then publish through the platform's API or import format. Deterministic means the same input always produces the same output — no manual edits buried inside the pipeline.

In practice, most teams land supplier files in a Postgres table with a raw_ prefix and a source_file column, then build a view that casts types and maps categories. Keeping raw rows means you can re-run the transform after fixing one rule, instead of emailing the supplier for the file again. Spreadsheet-heavy teams often do the first pass in Power Query, which is fine as long as the transform is documented and repeatable.

How do you prepare product data step by step?

Run the work in this order and every stage gives you something to check against. Jumping straight to the import is the most common reason a catalogue migration slips by a week, because you find the schema problem only after half the file is loaded.

1. Inventory the sources. List every file, who produces it, how often it changes, and which column is the real product key.
2. Freeze a canonical schema before touching any data. One row per SKU, named columns, declared types, required fields marked.
3. Write the mapping document. Source column to target field, the transformation rule, and who approves a change to it.
4. Load raw data into a staging table, keeping original values and the source filename untouched.
5. Normalise. Convert quantities to a base unit, trim whitespace, standardise case, resolve category names to codes.
6. Validate against the schema. Send bad rows to a quarantine table with a reason rather than failing the whole run.
7. Enrich. Fill the gaps suppliers leave: descriptions, image alt text, slugs, SEO titles.
8. Dry run into a staging store or a duplicate site. Never test an import against the live catalogue.
9. Reconcile. Compare row counts and a checksum per category between source and destination.
10. Publish, then archive the source file and the exact mapping version used.

-- quarantine rows that cannot be published
SELECT sku, reason
FROM staging.products_clean
WHERE sku IS NULL OR price IS NULL OR category_code IS NULL;

Which configuration decisions matter most?

Four decisions are expensive to reverse: taxonomy depth, attribute typing, the variant model and the unit convention. Get those wrong and every later import needs a translation layer that nobody documents, usually discovered by whoever inherits the system.

Keep taxonomy to two or three levels unless you genuinely need more. Store numbers as numbers — price as a numeric column, weight as a value plus a unit column, never as free text. Model variants as a parent with child SKUs so stock and pricing stay per sellable unit. And pick one base unit per dimension, storing the pack multiplier separately so a case of twelve is still one product.

How do you verify the import actually worked?

Verify with counts first, then with behaviour. A row-count match proves the file loaded; it does not prove a customer can find anything. So the last checks are run through the storefront and the feed, not through the admin panel.

Count rows per category on both sides. Spot-check twenty random SKUs against the source file, including the awkward ones with punctuation in the name. Test the faceted navigation for a filter you know should return results. Run the merchant feed through its own diagnostics, place a test order for a variant with options, and confirm every image URL returns a 200. Our frequently asked questions cover the checks clients ask about most often during handover.

Why do product imports fail, and how do you debug them?

Most import failures come from encoding, delimiters and type coercion, not from the platform. A file saved as Windows-1252 turns "Müller" into "Müller" and Postgres refuses the load with invalid byte sequence for encoding "UTF8". A semicolon-delimited CSV read as comma-delimited collapses into one column.

Work through it in this order, and stop at the first signal that explains the symptom:

1. Check the file itself — encoding, delimiter, header row, total row count. Open it in a text editor, not in Excel.
2. Check the parse. Did the loader see the columns you expected, with the names you expected?
3. Check the types. A price column that arrives as text will sort and filter wrongly without erroring.
4. Read the quarantine table, not the summary line. The reason column tells you which rule fired.
5. Check platform limits: upload payload size, request timeout, and image fetches that fail silently.
6. Check what actually published. Re-running an import with a broken key creates duplicates, so confirm SKU uniqueness after every run.

What does it cost in time and operational effort?

Cost is dominated by human review, not software. An engineer builds the mapping and validation once; someone still has to decide what happens to the 15% of rows that fail validation, and that person usually sits in merchandising rather than IT. That review is the line item nobody puts in the plan.

The drivers are the number of sources, how often they change, how many channels and languages you publish to, whether a PIM licence is involved, image storage and egress if you serve media from object storage, and the ongoing price and stock sync. Cloud and licence figures change constantly, so confirm current numbers with the vendor's own calculator, or talk to us about scoping the work.

Which mistakes do teams make most often?

The recurring mistakes are structural, not technical: treating the import as a one-off, editing the destination instead of the source, and leaving no owner after go-live. Each one guarantees the second import is harder than the first.

• Importing directly into production without a dry run or a rollback copy.
• Fixing rows by hand in the admin UI, so the source file and the live store disagree within a week.
• Importing on a name instead of a stable SKU, which creates duplicates on every re-run.
• Assuming the supplier's category names are stable — they change them without telling you.
• Treating data preparation as finished at launch, then discovering nobody owns price and stock updates.

What does this look like on a real project?

An outdoor retailer in Kathmandu had 4,200 SKUs from three suppliers and one week before a seasonal launch. Two files were usable, one arrived as a PDF price list that had to be keyed in by hand. Nobody had noticed that two suppliers used different pack sizes for the same item.

We built a staging schema, deduplicated on a normalised SKU and found 180 duplicate products across the three sources. The dry run surfaced roughly 300 rows with no category and a unit mismatch on gas canisters that would have shipped wrong. Launch moved by four days. Afterwards the nightly price and stock sync went into the same pipeline. Work like this sits alongside the projects in our portfolio, and a scope conversation is usually cheaper than a failed import.

In short

• Treat product data preparation as a project with a schema, an owner and a deadline.
• Normalise units and taxonomy before you load anything.
• Dry run, reconcile counts, keep a rollback copy.
• Plan for human review — it is the biggest real cost.
• Hand over the mapping document, not just the imported catalogue.

People also search for

• Connecting inventory to an ecommerce store
• Moving a product catalogue out of spreadsheets
• Cleaning data before a migration
• Why scope creep quietly doubles a web project
• Changing web developers mid-project
• Custom software versus off-the-shelf
• What a project handover meeting should cover
• Using website analytics data properly

If your catalogue is heading for a migration, a replatform or a marketplace feed and nobody has scoped the data work yet, that is the part worth fixing first. Our team can help you inventory the sources, define the schema and build an import you can re-run safely — see our services and get in touch with the files you actually have.