Supplier categories end up in Uncategorised: use a mapping table with an unmapped list, not loose matching

What goes wrong with loose matching

Category rules often start as a few if-statements: if the supplier category contains hammer, use Hand tools. They work until the supplier adds Hammer drills, renames a category, or sends a path such as Tools > Accessories where Accessories also exists under another parent. Products then land in a default or in a wrong category, and nothing records why. The failure is silent because the import still succeeds.

A mapping table with one row per supplier value

The replacement is a table, owned by the person who merchandises the store, with one row for each distinct supplier value as it appears in the file, including the whole path where the supplier sends one. Each row has one target store category, a short reason and a state: approved or unmapped. Several supplier values may map to the same store category; one supplier value maps to only one. Matching is exact after a stated normalisation, such as trimming spaces and ignoring letter case, and the normalisation is written down and tested. Anything not found in the table is not matched approximately: it goes to an unmapped list that someone reviews.

• Map the full path, not the last segment, whenever the same leaf name occurs under different parents.
• Never put an unmapped product in a default category; hold it or import it flagged, as agreed.
• Report new and changed values on every run, so a rename is a visible event.

A second target: Google's category

If the same table also feeds a shopping platform, a second target column can hold the Google category. Google's help page says the google_product_category attribute takes a predefined category as either a numeric ID or a full text path, not both. Because its own examples differ in wording, prefer the numeric ID and check it against the published taxonomy file. The attribute is described as optional, and where no Google category fits, product_type carries your own labels. Do not invent category values; an unknown value is a data problem, not a mapping.

Other diagnoses to rule out

Products may be in the wrong category because the supplier's own field is wrong, because a different column than the category column was mapped, or because the header changed. If every product is in the default, check the header first. If only new products are affected, the table is missing rows. If categories moved after a supplier rename, the table's keys no longer match exactly.

A safe first investigation

In a copy of a supplier sample, sort the category column and remove duplicates, or ask your developer to print the distinct values. Look for near-identical spellings, path separators, trailing spaces and different capitalisation. Each distinct text is a separate value the importer must treat consistently. Compare the number of distinct values with the number of rows in your table. A mismatch is your first list of unmapped values.

How the paid job is accepted

The job mapping-supplier-categories-to-store-categories starts from £395 for up to 200 distinct supplier values, quoted after we see the count and your target list. Every distinct value in the agreed sample must appear once in the table with one target or the unmapped state; a synthetic file with a mapped, a new, a renamed and a many-to-one value must land each product in its target with new and renamed values listed and none placed in a default; and two runs must give the same result. Your named approver signs each mapping row, and we do not decide your merchandising. Prices are untested proposals, and payment follows the agreed checks and your sign-off. Nothing is booked or charged by an enquiry.