Automatic Product Matching with Supplier Catalog

The owner of an online store received a price list from a supplier with 50,000 items. Each item must be matched to the existing catalog. Manual processing would take weeks and cost approximately 200,000 rubles if outsourced. We automate this process using a combination of deterministic rules and trigram-based fuzzy search. Our team has extensive experience in catalog automation and certified PostgreSQL engineers. We have completed over 100 projects with catalogs up to 500,000 products. We guarantee matching accuracy during the testing phase. Automation of mapping reduces product loading time by 10 times compared to manual methods, and operational cost savings can reach 500,000 rubles per year for a store with a turnover of 10 million rubles.

What matching methods are compared?

Mapping works layer by layer — from exact to approximate. For clarity, here is a table comparing methods:

Implementing Matching Algorithms

How is exact matching implemented?

The most reliable method is exact SKU match. We select the product by the sku field. If the SKU is missing, we check the barcode (EAN). If that is also absent, we move to the normalized name. Here are the steps:

1. Query product by sku.
2. If not found, query by ean.
3. If not found, normalize the supplier name and query by name_normalized.
4. If still not found, perform fuzzy search using trigram similarity.

class ProductMatcher { public function match(SupplierProduct $sp): MatchResult { if ($p = Product::where('sku', $sp->article)->first()) { return MatchResult::exact($p->id, 'sku'); } if ($sp->ean && $p = Product::where('ean', $sp->ean)->first()) { return MatchResult::exact($p->id, 'ean'); } $normalized = $this->normalize($sp->name); if ($p = Product::where('name_normalized', $normalized)->first()) { return MatchResult::exact($p->id, 'name_normalized'); } $candidate = $this->fuzzySearch($normalized); if ($candidate && $candidate->score >= 0.88) { return MatchResult::fuzzy($candidate->id, $candidate->score); } return MatchResult::unmatched(); } } 

The Critical Role of Name Normalization

Normalization brings strings to a uniform format: removes extra characters, service words (art, ref, no), and standardizes case. Without it, identical products with different spellings (e.g., "Smartphone X10" and "Smartphone X10 Pro") would not be matched. The normalized value is stored in an indexed name_normalized field, which speeds up search by 3 times compared to raw names. In practice, normalization is 50 times faster than manual review.

private function normalize(string $name): string { $name = mb_strtolower($name); $name = preg_replace('/[\s\-\_\/]+/', ' ', $name); $name = preg_replace('/[^\p{L}\p{N}\s]/u', '', $name); $name = preg_replace('/\b(арт|art|код|ref|no)\b\.?\s*/iu', '', $name); return trim($name); } 

Implementing Fuzzy Search with PostgreSQL

For fuzzy search, we use the pg_trgm extension. It computes string similarity based on trigrams and is more computationally efficient than Levenshtein distance for large datasets. This method processes 10,000 products in 30 seconds, which is 10 times faster than manual mapping.

CREATE EXTENSION IF NOT EXISTS pg_trgm; CREATE INDEX products_name_trgm_idx ON products USING gin (name_normalized gin_trgm_ops); 

Query to find similar:

SELECT id, name_normalized, similarity(name_normalized, :query) AS score FROM products WHERE similarity(name_normalized, :query) > 0.7 ORDER BY score DESC LIMIT 5; 

In PHP via Eloquent, we get one best candidate with a threshold of 0.88.

Handling Unmatched Items and Mapping Storage

Handling Unmatched Items

If an item is not found automatically, the system checks if there is a similar product with a low score. If found, a record is created with confirmed=false and the operator is notified. If no similar product exists, a draft product is created or the item is listed for manual matching. This reduces the risk of missing an item. Ask for a consultation on configuring this process.

Storing the Mapping

For storing matches, we use the following table:

CREATE TABLE supplier_product_mapping ( id serial PRIMARY KEY, supplier_id int NOT NULL, supplier_sku varchar(100) NOT NULL, product_id int REFERENCES products(id), match_type varchar(20), match_score float, confirmed boolean DEFAULT false, confirmed_by int, confirmed_at timestamptz, created_at timestamptz DEFAULT now(), UNIQUE (supplier_id, supplier_sku) ); 

Confirmed mappings (confirmed = true) are used directly. Unconfirmed fuzzy mappings require operator review.

Category Mapping and Duplicate Detection

Supplier categories are mapped to the site's category tree after manual mapping. The duplicate detector looks for identical EANs or similar normalized names within one price list. Such duplicates are merged or removed, preventing catalog clutter. This saves up to 20% of product loading time.

Performance Optimization for Large Catalogs

Deliverables and Scope of Work

• Analysis of the supplier price list and catalog structure.
• Implementation of matching algorithms (exact, fuzzy, manual).
• Database configuration (pg_trgm, indexes).
• Creation of an admin interface for the operator.
• Integration with the supplier (API, export, import).
• Employee training and documentation.
• Guarantee of mapping accuracy during the testing phase.

Timeline

• Exact matching, storage, drafts: 3 days.
• Normalization, fuzzy, confirmation queue: +2 days.
• Category mapping, duplicate detection, admin UI: +2–3 days.
• Total: 5 to 8 days depending on complexity.

Automatic matching reduces product loading time by 10 times compared to manual mapping. For a catalog of 50,000 products, this saves approximately 500,000 rubles annually in operational costs. Contact us to discuss integration with your supplier. Order the implementation of automatic mapping now.