Job page

How to Clean Up Messy Product Data in a Small Business

Answer: Messy product data is an item list where the same product has different names, codes, units, or categories in different places, so your counts and sales reports stop agreeing. Clean it in order: give every item one internal SKU, merge duplicates, fix units and pack sizes, then names, categories, and only the missing attributes your job needs. An agent can take over the matching and retyping. A person still decides the master list, any merge that matches only on name, and prices.

Say your shop sells a black large t-shirt. The register calls it "Blk Tee L." The online store calls it "Classic T-Shirt, Black / Large." The supplier's price list calls it "TS100-BK-LG." The accounting tool files it under "Merch." The spreadsheet you count from has two rows for it because someone added it again in the spring.

Each of those was a reasonable choice by whoever typed it. Together they mean nobody can answer "how many did we sell, and what did we make on them?" without an afternoon of matching by eye.

This page covers that item list: catalogs, SKUs, supplier spreadsheets, and the same product living in your point of sale (POS), online store, inventory sheet, and accounting tool. For customers, quotes, or job status, start with How to clean messy business data so an AI system can use it. It covers picking one job, making working copies, and bounding what a system can see. In CRUXIBL terms, this page is the Clean and organize beat of an Intelligent Business Layer (IBL), applied to products.

What messy product data looks like

One product, several names: register abbreviations, marketing names online, supplier codes on the invoice.
Duplicates, often after an import, a new supplier, or a staff member who could not find the old row.
Units that do not agree. "ea," "each," "cs," "case of 12," and "12ct" in one column. Bought by the case, sold by the each, counted by whoever is closing.
Variants handled several ways. Some sizes are separate items, some are one item with a note.
Categories that drifted: "Hair Care," "Haircare," "Retail," and "Misc" holding the same kind of product.
Missing attributes: no cost, no supplier part number, no size, no barcode, blank descriptions online.
Supplier price lists, each with its own columns, codes, and units, and a layout that changes without notice.
Systems that disagree. The POS price is not the online price. The item exists in inventory but not in accounting.

Item lists usually end up this way because a business adds tools one at a time and each tool keeps its own copy of the list.

How to tell how bad it is

Give it an hour before you fix anything. You want a few numbers you can run again after cleanup.

Export the item list from every tool that has one: POS, online store, inventory sheet, accounting, and your latest supplier price list. If a tool will not export, note it and move on.
Count the rows in each export. A big gap between the POS and the store is the first thing to explain.
Pull 50 items you actually sell. Find each one in every other export and mark how: same SKU, same barcode, name only, or not found.
In your main list, count blanks in the columns you care about (cost, supplier part number, category, unit).
List every distinct value in the unit and category columns. A filter or pivot table will show them.

Items that match by SKU or barcode across exports are tidy-up work. Items that match only by name mean you have an ID problem, and that comes first. Items you cannot find at all are dead products or missing records, and a person who knows the shelf has to say which.

Keep the sheet. When you finish, run the same sample again.

A cleanup order a small business can follow

Later steps depend on earlier ones. Fixing category names on rows that are about to be merged is wasted work.

1. Pick the job and freeze the exports

Name what the clean list is for (reordering, the online store, a margin report). The job decides which fields get cleaned. Save the raw exports with today's date and work on copies.

2. Decide where each field comes from

Before you touch rows, write down which system owns each field going forward. A common split is below. Yours may differ.

Field	Suggested owner	Who updates it
Internal SKU and name	Your master item list	One named person
Selling price	POS	Owner or manager
Online description and photos	Online store	Whoever runs the site
Cost and supplier part number	Supplier price list, mapped to your SKU	Whoever orders
On-hand count	Inventory sheet or tool	Whoever counts
Income category or account	Accounting	Bookkeeper

The master item list can be a spreadsheet: one row per sellable item, and one person allowed to add rows.

3. Give every item one internal SKU

Keep the format boring: short, all caps, no spaces, never reused. A short category prefix is fine. Cramming size, color, or supplier into the code makes it break the first time one of those changes.

Supplier part numbers and barcodes go in their own columns, mapped to your SKU, so an item bought from two suppliers still has one code. That mapping table (your SKU, supplier, supplier part number, barcode) is often called a crosswalk.

4. Merge duplicates

Match in this order, most reliable first:

Same barcode.
Same supplier and supplier part number.
Same cleaned-up name plus same size or variant.

The first two can usually merge. Name-only matches need a person who knows the product, because two rows that both say "Shampoo 8oz" can be two different brands.

Write a merge log: old ID, source system, new SKU, date, who decided. POS sales history points at the old IDs, and you will need the log to read last year's numbers. Where a tool keeps sales history on an old item, mark the item inactive and leave it in place.

5. Fix units and pack sizes

Split one muddled unit column into three: purchase unit (case, box, each), selling unit (usually each), and units per purchase unit (12, 24, 1). Then pick one spelling per unit and map the rest to it, so "cs," "case," and "CS" all become "case." This step makes cost per item and reorder counts come out right. Finish it before you trust any margin number.

6. Fix names and variants

Write a naming pattern and apply it everywhere: brand, product, variant, size. For example, "Acme Shampoo Lavender 8 oz." Keep an alias list beside it so old register abbreviations still map.

Pick one way to handle variants, such as one parent product with a separate SKU per size or color, and use it for every product. A report cannot add up sizes that are sometimes rows and sometimes notes.

7. Shrink the category list

Take every category value from the check, pick a short list one or two levels deep, and give each item exactly one category. Map every old value to a new one. The status-list method on the general cleanup page works the same way: find every unique value, map it, and allow "unknown" instead of guessing.

8. Fill only the attributes the job needs

Reordering needs cost, supplier, part number, and pack size. The online store needs descriptions, photos, and sizes. Fill the ones your job uses and let the rest wait.

Get missing specs from the supplier's spec sheet or the package, and mark the rest unknown. Do not let anyone, person or model, guess a weight, an ingredient, or a safety claim to make the row look complete.

9. Push the clean list back and keep it clean

Load the cleaned names, categories, and units into each system one at a time, starting with the one you check most. Then write the rule that keeps it clean: new items get created in the master list first, with a SKU, before they go into the POS or the store. If retyping into each tool is the bottleneck, How to push AI output into existing software covers filling those tools with a person confirming each save.

What an agent can take over, and what a person decides

Product data suits an agent because most of the work is matching and retyping against rules you already wrote. In the IBLthis is Connect plus Clean and organize, and later boards such as "what sells with what" or margin by item depend on it. The split below follows the rule CRUXIBL uses everywhere: machines handle retyping and glitches, and people handle business.

An agent can:

Normalize names, units, and categories to your lists and alias table.
Flag likely duplicates with the reason (same barcode, same supplier code, similar name) and leave name-only matches for review.
Map each new supplier price list to your SKUs when it arrives, and flag lines with no match or a changed cost.
Compare systems on a schedule and flag where the POS price, the store price, and the master list disagree.
Draft updates to each system for someone to confirm.

A person decides:

Which system is the master and who may add items.
The SKU format, the category list, and the naming pattern.
Every name-only merge.
Prices, which cost changes you accept, and anything that retires a product.
Any attribute that ends up on a label or in a safety or legal claim.

Supplier costs and contract terms belong in a feed you control. What not to paste into ChatGPT covers where to draw that line for staff who use chat tools.

When to stop

Stop the first pass when your 50-item sample matches by SKU or barcode across the systems you care about and a new hire can add an item by following the written rule. Descriptions, photos, and extra attributes can come later, one job at a time.

FAQ

What is messy product data?

It is an item list where the same product has different names, codes, units, or categories across your POS, online store, inventory, accounting, and supplier sheets, or shows up twice in one list. When that happens, counts and sales reports stop agreeing.

Should I use the supplier's part number as my SKU?

Usually no. Suppliers change codes, and you may buy the same item from more than one. Keep your own SKU and map supplier part numbers and barcodes to it in a crosswalk table.

Can AI clean up my product catalog by itself?

It can do most of the matching and normalizing, and it can flag what it is unsure about. It should not decide name-only merges, prices, or attributes that are missing from every source. Those need a person, because a model asked to fill a gap will guess.

Do I need a new inventory system or a product information management (PIM) tool first?

Not for a first pass. A spreadsheet master list, a crosswalk, and written rules are enough to start. Whether a dedicated tool is worth it later depends on how many items and sales channels you run.

Soft next step

If your item list lives in four places and you want it in one, email hello@cruxibl.com or use cruxibl.com/#contact. Tell us which tools hold your items and which job is failing. We build it. You help hone it. You direct it.

If your item list lives in four places and you want it in one, email hello@cruxibl.com. Tell us which tools hold your items and which job is failing. That is enough. Or use the form.