Inconsistent supplier files can turn one product into several records—or merge different products into one. This practical workflow shows how to normalize catalog fields, generate safe candidates, combine exact identifiers with fuzzy matching, and review confidence bands before publishing matches.
Overview
SKU matching is often treated as a simple string comparison. In real catalogs, the same item may appear as AB-123, AB123, AB 123, or a supplier description such as “Acme replacement filter model AB123.” A product may also have a manufacturer part number, supplier-specific code, barcode, model name, size, color, and pack quantity. These fields do not have equal reliability, and a matching process that treats them equally can create duplicate products or incorrect merges.
A dependable product catalog matching workflow separates four activities:
- Normalization: Convert comparable values into consistent forms without destroying useful distinctions.
- Candidate generation: Narrow each incoming record to plausible existing products.
- Scoring: Compare identifiers, names, descriptions, and attributes using field-specific rules.
- Decision and review: Automatically accept clear matches, reject clear non-matches, and route uncertain cases to an exception queue.
Fuzzy matching is most useful in the middle of this process. It can recover matches that exact lookup misses, but it should not override a reliable identifier or make an irreversible decision when the evidence is weak. The goal is controlled entity matching, not maximum similarity.
What to track
1. Build a field-normalization checklist
Normalize values before comparing them, and retain the original source values for auditability. A practical checklist includes:
- Trim leading and trailing spaces and collapse repeated internal whitespace.
- Apply consistent case handling where case is not meaningful.
- Standardize punctuation, separators, and common formatting variations in SKUs.
- Normalize Unicode characters and decide how accents should be handled.
- Convert known unit formats into a common representation, such as millimeters versus centimeters.
- Standardize pack notation, including values such as “12 pack,” “12pk,” and “case of 12,” when they mean the same thing.
- Separate brand, manufacturer, model, color, size, and quantity when the source structure allows it.
- Preserve meaningful characters. Removing every hyphen or slash may cause distinct model numbers to look identical.
- Maintain synonym and abbreviation mappings only when they are verified for the relevant catalog.
Do not assume normalization makes two values equivalent. For example, removing spaces from 10 25 and 1025 may be safe in one identifier system and dangerous in another. Keep a field-level record of each transformation so that a reviewer can understand why two records were compared as equivalent.
2. Classify fields by authority
Before selecting a similarity method, rank fields by how much evidence they provide. A verified global trade item number or manufacturer part number may be stronger than a product title. A supplier SKU may be unique only within that supplier’s system. A description can provide useful supporting evidence but may contain marketing language, missing attributes, or copied text.
For each source, document:
- Whether the field is expected to be unique.
- Whether it is stable across supplier imports.
- Whether it identifies a product or a particular offer, warehouse item, or pack size.
- How often it is missing or reused.
- Which values have already been verified by an operator.
3. Generate candidates before fuzzy scoring
Comparing every incoming row with every catalog row is expensive and increases the chance of accidental high-similarity matches. Use blocking, also called candidate generation, to create a smaller comparison set. Suitable blocking keys may include:
- Exact normalized manufacturer part number.
- Exact barcode or other trusted identifier.
- Brand plus normalized model prefix.
- Supplier account plus supplier SKU.
- Product category plus a normalized model token.
- Brand plus selected attributes such as size, voltage, or capacity.
Use more than one blocking path when fields are incomplete. An item might enter the candidate set through an exact model number, a brand-and-token key, or a verified alternate identifier. Blocking should reduce unnecessary comparisons without excluding plausible matches.
4. Combine exact and approximate signals
Use exact matches first. If a trusted identifier points to one existing product and the remaining attributes are compatible, that may be sufficient for an automatic match. If exact identifiers are absent or inconsistent, apply approximate string matching to selected fields.
Possible signals include normalized SKU equality, edit similarity for model numbers, token similarity for product names, brand agreement, attribute agreement, and pack-size compatibility. Levenshtein distance can help with short typographical changes, while token-based methods can be more useful when words are reordered or descriptions contain extra terms. The best method depends on the field and its error patterns; one global similarity score is rarely enough.
Confidence bands for decisions
| Confidence band | Typical evidence | Recommended action |
|---|---|---|
| High | Trusted exact identifier, or several independent fields agree with no conflicts | Automatically match, then sample results for quality control |
| Medium | Strong name or model similarity, but an identifier is missing, ambiguous, or incomplete | Send to an exception queue for review |
| Low | Weak similarity, conflicting attributes, or multiple near-equal candidates | Do not merge; retain as unresolved or create a new record after review |
Thresholds should be calibrated against labeled examples from your own catalog. A score that is safe for long descriptive names may be unsafe for short model numbers, where one character can distinguish an important variant.
Cadence and checkpoints
Matching quality changes as suppliers, categories, and product conventions change. Treat the workflow as an operating process rather than a one-time migration script.
For every supplier import
- Measure the percentage of rows matched by exact identifiers, fuzzy rules, and manual review.
- Check whether new formatting patterns appeared in SKUs, dimensions, or pack quantities.
- Inspect rows with conflicting brand, model, size, or quantity attributes.
- Record unmatched rows and the reason they failed to match.
- Prevent duplicate writes until medium-confidence cases are resolved or explicitly accepted.
Monthly or quarterly
- Sample automatic matches from each supplier and product category.
- Review false matches, missed matches, and duplicate products created since the last checkpoint.
- Compare the distribution of confidence scores with previous imports.
- Refresh verified synonyms, abbreviations, and supplier-specific parsing rules.
- Revisit thresholds when catalog structure, matching libraries, or upstream integrations change.
Exception queue template
Store enough information for a reviewer to make a decision without reopening the entire import:
Incoming record: [supplier ID and source row] Candidate records: [IDs and names] Identifiers: [raw and normalized values] Field comparisons: [model, brand, size, color, quantity] Similarity signals: [per-field scores and exact matches] Conflict flags: [identifier or attribute conflicts] Suggested action: [match, reject, create, or investigate] Reviewer decision: [status, reason, reviewer, timestamp]
Reviewer decisions are valuable training data. Use them to identify systematic problems, such as a supplier adding a suffix to every SKU or a category where pack quantity is frequently omitted.
How to interpret changes
A higher automatic-match rate is not automatically an improvement. It may indicate better normalization, but it may also indicate an overly permissive threshold. Read matching metrics together:
- Automatic-match rate: How many rows pass without review?
- Review rate: How much operational work is entering the exception queue?
- False-match rate: How often does a reviewed automatic match point to the wrong product?
- Missed-match rate: How often does review find that an unresolved row should have matched an existing product?
- Duplicate creation rate: How often do imports create records that later need consolidation?
- Candidate ambiguity: How often are several products nearly tied?
Prioritize false matches when they can affect inventory, fulfillment, pricing, compliance, or customer-facing product data. A conservative workflow may leave more rows for review while preventing incorrect merges. For customer-facing search, unresolved records can also contribute to duplicate results or poor product relevance, so connect catalog-matching outcomes with search-quality review.
When a metric changes, segment it by supplier, category, field availability, and import version. A global decline may be caused by one new supplier format rather than a problem with the entire matching model. Review representative examples before changing a threshold.
When to revisit
Revisit the workflow on a monthly or quarterly schedule, and immediately after a major catalog event. Useful triggers include a new supplier, a catalog migration, a change in product taxonomy, a new integration, a seasonal assortment update, or a noticeable rise in duplicates and unresolved records.
At each review, start with a small labeled sample: confirmed matches, confirmed non-matches, and difficult cases from the exception queue. Check whether the current normalization rules preserve meaningful distinctions, whether blocking keys still capture plausible candidates, and whether confidence bands produce decisions that operators can defend. Update one rule at a time where possible, then rerun the sample before deploying it to the full catalog.
Keep raw values, normalized values, candidate lists, scores, rule versions, and reviewer decisions. This history makes future changes safer and helps explain why a product was matched. For implementation details, see Entity Matching Evaluation: How to Set Thresholds and Review False Matches and Levenshtein Distance vs. Jaro-Winkler and Other Similarity Methods. For the next import, use this sequence: normalize, block, compare, assign a confidence band, review exceptions, and measure the outcome before widening automation.