Twelve spellings, eight bath types: normalising product attribute values so filters work

What normalising attribute values means

Normalising product attribute values means choosing one way to write each value of an attribute, and mapping every variant a supplier sends onto it. An attribute is a column such as Type, Colour or Width; a value is what sits in each cell. “Freestanding Baths” and “Freestanding” are two ways of writing one value, and until they are folded together, a filter treats them as two different things.

That matters because a filter is usually built from the distinct values in a column, so every spelling becomes an option of its own. Tick one, and you usually see only the products filed under that exact spelling. The rest of the range sits behind an option that looks like a duplicate.

Baymard Institute, which researches ecommerce usability, puts the requirement plainly: to offer harmonised filtering values, vendors’ product data and branded feature names have to be post-processed into “common name” attributes, which can then be used to filter product lists and search results.¹ Suppliers name things for their own systems. Your filters need names for your shoppers.

Normalising is one part of the wider work of product data enrichment, and it comes first. You cannot tell which values are missing until the ones that are present mean the same thing everywhere.

Twelve spellings, eight bath types

On one bathroom retailer’s catalogue of 329 baths, the Type column held twelve different values, and they described eight real types of bath. It is one of our recent jobs, and a clean example of how the problem looks in practice.

Three types had each arrived under two names. Single Ended sat beside Single End Bath, Freestanding beside Freestanding Baths, and Corner beside Corner Bath. Five more were already written one way: Double Ended, Left Hand, Right Hand, L Shape Bath and P Shape Bath. The twelfth value was Freestanding Taps, which is not a bath at all. It was a tap that had been filed among them.

So shoppers were offered twelve filter options for eight real things. Someone who ticked Freestanding saw the baths filed under that exact word and missed the ones filed as Freestanding Baths, which sat behind an option of their own. The filters only started working once the twelve became eight.

Two things in that column were not spelling problems, and they are worth separating early. The tap is a categorisation error. Folding it into a bath type would hide it, so it is flagged and moved instead, which is the subject of mapping supplier categories. And a few baths had no type at all. A blank is not a variant; it is a gap, and it is filled by finding the value, not by mapping one.

Where the variants come from

Variants arrive because every supplier writes values for its own systems, and because people type them. Once a catalogue draws on more than one supplier, each brings its own vocabulary, and the column holds all of them at once. The same few patterns account for most of them.

• Extra words and plurals. Corner and Corner Bath; Freestanding and Freestanding Baths. The product type repeated inside the value is the commonest variant of all.
• Different word forms. Single Ended and Single End Bath describe the same bath in two grammatical shapes.
• Casing and punctuation. Freestanding, freestanding and Free-standing look like one value to a person and three to a database.
• Invisible differences. A trailing space, a curly apostrophe where another row has a straight one, or a non-printing character copied in from a PDF. Microsoft warns that exactly these can make Excel’s COUNTIF return unexpected results.²
• Two names in one cell. A finish supplied as Steel/Inox is one finish named twice: once in English, and once in the shorthand many European suppliers use for stainless steel.

Casing is the one that slips past a quick check. Excel’s COUNTIF ignores case: “apples” and “APPLES” match the same cells, so “freestanding” and “Freestanding” are counted as one value.² Your store may still show them as two separate options, so a spreadsheet that looks clean can feed a filter that is not.

Units, and cells that hold three values

Numbers have the same problem, and it is harder to see because every version is correct. A width of 60cm in one column and 595 in a column headed Width (mm) are one fact written twice, and a cell reading 1850x595x655 is three facts with no unit at all.

The rule is one unit per field and one fact per cell. Split compound cells into their own fields, convert every measure in a field to the same unit, and state the unit the same way on every row, never on some rows and not others. OpenAI’s product feed specification for ChatGPT asks for the same discipline: length, width and height as numbers, with “one unit for all supplied axes”.³

The 60cm is not wrong, and it should not simply be thrown away. It is the nominal size the product is sold by, which is why it appears in the product’s name. If your shoppers filter by nominal width, give it an attribute of its own. The measured width belongs in the width field, in millimetres, like every other width in the category.

Values that were never values

Some cells hold something that is not a value at all, and those should be emptied rather than mapped. N/A, absent and see image are the common ones, along with a category name such as Baths sitting in a type column.

Google’s Merchant Center rules for the colour attribute name the problem directly: do not submit a value that is not a colour, and N/A is among the examples it gives.⁴ The same rules read a slash in a colour as separating a main colour from up to two others, so Steel/Inox would be taken as two colours rather than one finish.⁴

Consistency matters beyond your own filters, too. Google asks that the colour in your product data is the same one shown on your landing page,⁴ and OpenAI’s feed specification asks for a colour consistent with the product image.³ Both are far easier to meet when every channel reads from one canonical value, rather than from whatever each supplier typed.

Emptying a non-value can feel like losing data. It is the opposite. An empty cell is an honest gap that a quality check can count and enrichment can fill. An N/A is a gap disguised as data, and it sits in a filter as an option nobody wants to click.

How to normalise attribute values, step by step

The method is the same for any attribute, and the order matters, because each step needs the one before it.

1. List every distinct value, with a count. A pivot table, COUNTIF or a GROUP BY query will do it. Trim stray spaces first, and remember that Excel’s counts ignore case, so casing variants will not show up as rows of their own.²
2. Decide the canonical list. One value per real thing, in the words your shoppers use, written in one style. It is the “common name” list Baymard describes, and it is yours to set: a supplier should not decide what your filters say. Agree it with whoever owns the category pages, because they are closest to the distinctions shoppers actually use.
3. Map every variant to it. A two-column table, supplier value on the left and canonical value on the right. Anything that fits no canonical value gets a flag, never a best guess.
4. Empty the non-values. N/A, absent and see image become blanks, so they are counted as gaps and not offered as options.
5. Apply the mapping and count again. The number of distinct values should now equal the number of real things. Then look at the filter on the live category page, because that is where a shopper will meet it.
6. Keep the mapping. It is the most valuable thing the whole exercise produces. Run every future import through it, and only the new variants need a decision.

One judgement runs through all of it. Folding spellings is safe; folding distinctions is not. If an old value carries a meaning shoppers use, keep it as an attribute of its own. When a bathroom retailer’s sixty-two wall panels were pulled out of fifteen categories into one range, the old category was kept as a filter shoppers can still use.

Keeping the next supplier file clean

Normalising once fixes today’s catalogue; keeping the mapping fixes the next one. Every new supplier file arrives with its own spellings, and unless the mapping is applied as the file comes in, the variants come straight back.

That is why normalising belongs in the intake of supplier data rather than in an occasional clean-up. The guide to supplier data onboarding shows where it sits in that sequence. A consistency check, one word per value and one unit per field, is how you know it is still holding.

It is also the part of the work that RefynData’s product data enrichment service automates: it folds every variant onto the one word you choose, and clears the values that were never values. The vocabulary stays yours. Set the canonical word once and every future import lands on it.