A catalog where the same product was listed four times
An online retailer combining feeds from several suppliers finds that its catalog cannot tell shoppers, or its own stock system, which listings are the same item.
Illustrative example. The organization and its data are invented to show a typical pattern. This is not a customer story.
What they hold
- Product feeds from each supplier, in each supplier's own layout.
- A catalog with titles, attributes such as size and color, prices, and stock levels.
- Order history that refers to products by the retailer's own item codes.
The mess
The same item under different codes
Two suppliers sell the same kettle. Each feed has its own code for it, and the manufacturer's barcode, which would prove they match, lost its leading zero in one feed. The catalog lists the kettle twice, at two prices.
Attributes with no agreed values
Color is Navy, navy blue, NVY, and Dark Blue. Size is M, Medium, and 38. A shopper filtering by blue sees some of the blue items.
Dimensions in mixed units
Weight arrives in grams from one supplier and in pounds from another, with the unit sometimes inside the cell and sometimes not. Shipping cost is calculated from that column.
Descriptions with broken characters
A feed saved in one encoding and read in another turns accented letters and symbols in product names into strings of odd characters, which then appear on the product page.
What it costs
- Stock for one product is split across duplicate listings, so one shows as sold out while the other has units on the shelf.
- Shoppers cannot find items through filters, and leave.
- Shipping is undercharged or overcharged on every order where the weight was misread.
- Garbled product names make the store look careless, and customers hesitate to buy.
- Returns rise when the size a shopper selected is not the size the label meant.
What clean looks like
- Listings for the same item are linked on the manufacturer's barcode, stored as text, with one listing chosen as the one shoppers see.
- Each attribute has a fixed list of allowed values, and every supplier's wording maps to it.
- Weights and dimensions are held in one unit, stated in the column name.
- Text is read with the encoding it was written in, and product names display as the supplier wrote them.
Problems in this example
- The same entity in two systemsOne person or organization exists in two systems under different identifiers, with nothing linking the records.
- Identifiers that lost their leading zerosA code that starts with zero, such as a postal code or account number, was read as a number and the zeros were dropped.
- Inconsistent category labelsA column meant to hold a few fixed values holds many spellings of them.
- Mixed unitsValues in one column are measured in different units, sometimes with the unit written in the cell and sometimes not.
- Broken accented charactersAccented letters appear as pairs of odd symbols or as question marks, because the file was read with the wrong encoding.