How to combine 10 price lists from different suppliers into one consolidated catalog?

Problems of combining multiple price lists

When working with multiple suppliers, consolidating their data becomes a significant challenge. Let's look at the main issues every online store faces.

1. Different file formats

• Excel (.xls,.xlsx) — 40% of suppliers use older versions with Cyrillic
• CSV with different delimiters — comma, semicolon, tab
• XML/JSON — modern APIs, but each has its own structure
• Online tables — Google Sheets that update in real time
• PDF — the most problematic format, requiring OCR and parsing

2. Differences in data structure

3. Identical products with different names

This is the most critical issue. The same product from different suppliers may look like this:

• Smartphone Apple iPhone 15 Pro 256GB Blue Titanium
• iPhone 15 Pro 256GB Titanium Blue
• Apple iPhone 15 Pro 256GB Blue
• iPhone 15 Pro (A2848) 256GB Blue

Without the correct matching algorithm, the system will create 4 different product cards instead of one.

4. Data conflicts

5. Regional and currency differences

If you work with international suppliers:

• Prices in different currencies (EUR, USD, GBP, PLN)
• Different number formats: 1,234.56 (US) vs 1,234.56 (EU)
• Units of measurement: inches/cm, pounds/kg, gallons/liters
• Different languages for product descriptions

Price List Consolidation Steps: A Step-by-Step Process

Step 1: Inventorying Data Sources

Create a register of all suppliers and their prices:

1. Identification: Assign a unique ID to each supplier
2. Formats: Document the file format, encoding, and delimiters.
3. Refresh rate: How often does the supplier update the price list?
4. Method of obtaining: Email, FTP, API, download from website
5. Priority: Rank suppliers by importance (primary/backup)

Step 2: Standardize the data structure

Create a single data schema (master template) to which all price lists will be converted:

Required fields:

• supplier_id — supplier identifier
• supplier_sku — the supplier's product code
• master_sku — your internal article number (filled in after matching)
• product_name — name of the product
• price — price in single currency (EUR)
• stock_status — availability status (in_stock / out_of_stock / preorder)
• stock_quantity — quantity in stock

Additional fields:

• brand - manufacturer
• category — product category
• ean — barcode (EAN-13/UPC)
• mpn — manufacturer's number
• attributes — characteristics (JSON)
• images — links to images
• description — product description
• updated_at — date of last update

Step 3: Parsing and Normalization

For each price list, create a parser that converts the data into a uniform format:

Normalization of names:

• Removing extra spaces and special characters
• Unification of Title Case
• Standardization of units of measurement (GB → GB → 128GB)
• Removing information garbage ("!!!", "BESTSELLER", "PROMOTION")

Price normalization:

• Convert to a single currency (use current rates)
• Removing currency symbols and formatting
• Convert to a single format (float, 2 decimal places)

Normalization of residuals:

• "In stock" → in_stock (quantity > 0)
• "On order", "3-5 days" → preorder
• "Out of stock", "0" → out_of_stock

Step 4: Matching

The most difficult step is to determine that goods from different suppliers are identical.

Levels of comparison (from exact to approximate):

1. By EAN/UPC: Barcode - 100% accurate (if suppliers provide it)
2. According to MPN: Manufacturer's number - 95% accuracy
3. By supplier article number: If you have already matched this SKU before
4. By brand + model: Apple + iPhone 15 Pro 256GB Blue
5. By a fuzzy comparison of names: Levenshtein algorithm with a threshold of 85%+

Data Unification: Resolving Inconsistencies

Problem 1: Different units of measurement

Problem 2: Different Currencies

Prices are available in USD, EUR, GBP, and PLN. Required:

• Get up-to-date exchange rates (Central Bank API or services like Fixer.io)
• Convert all prices to your store's base currency
• Maintain the original currency and exchange rate for transparency
• Update rates at least once a day

Problem 3: Different date and time formats

• US: MM/DD/YYYY (12/25/2024)
• EU: DD.MM.YYYY (25.12.2024)
• ISO: YYYY-MM-DD (2024-12-25)
• Timestamp: 1735084800

Solution: Convert all dates to ISO 8601 format (YYYY-MM-DD HH:MM:SS)

Problem 4: Product categorization

Each supplier has its own category system. Create a mapping:

• Supplier A: "Mobile Phones" → Your category: "Smartphones"
• Supplier B: "Smartphones & Tablets" → "Smartphones"
• Supplier B: "GSM Phones" → "Smartphones"

Conflict Resolution: Rules of Priority

When a single product is available from multiple suppliers, it is necessary to decide whose data to use.

Strategy 1: Price Priority

Rule: Always choose the lowest price

• Pros: Maximum competitiveness
• Cons: There may be low margins and quality issues.
• Application: Locomotive goods, mass retail

Strategy 2: Prioritize Reliability

Rule: Prefer trusted suppliers

• Reliability criteria: Work history, % defects, delivery speed
• Pros: Fewer returns, stable supplies
• Cons: It may be more expensive
• Application: Premium segment, technology, electronics

Strategy 3: Prioritize Availability

Rule: Select a supplier with the product in stock

• In stock → higher priority than preorder
• Large balance (50+ pcs) → higher priority than small (1-5 pcs)
• Warehouse in your region → higher priority than remote

Strategy 4: Weighted Priority (Recommended)

Calculate the final score for each sentence:

Setting up priority weights

Weights should depend on the product category:

• Mass-produced goods: Price 60% | Reliability 20% | Availability 20%
• Premium segment: Price 25% | Reliability 50% | Availability 25%
• Perishable: Price 30% | Reliability 30% | Availability 40%

Resolving Conflicts in Characteristics

If product descriptions differ:

• Brand, model, article number: We trust the official distributor
• Technical specifications: We take it from the official source (manufacturer's website)
• Descriptions: Combine the best parts or generate your own
• Images: Priority is given to high-quality photos (resolution, background)

Automation of the merging process

Automation tools

1. ETL systems (Extract, Transform, Load)

• Apache NiFi — a visual data pipeline designer (open-source)
• Talend — data integration platform (free/paid)
• Pentaho Data Integration — ETL for business (open-source)

2. Specialized e-commerce platforms

• Elbuz — automatic processing of supplier price lists → more details
• Channable — Feed management for marketplaces
• DataFeedWatch — optimization of product feeds

3. Own development

For complex cases (10+ suppliers, specific formats), consider creating your own solution:

• Python + pandas — CSV/Excel processing
• Python + Beautiful Soup — HTML parsing
• Python + pdfplumber — extracting data from PDF
• PostgreSQL + stored procedures — logic of comparison
• Redis/RabbitMQ — queues for processing large volumes

Architecture of the automated system

1. Data Collector: Obtaining price lists from various sources
2. Parser: Extracting data from files of different formats
3. Normalizer: Reduction to a unified scheme
4. Matching Engine: Product comparison
5. Conflict Resolver: Conflict resolution according to the rules
6. Master Data Storage: Storing a Union Catalog
7. Export Engine: Upload to your store/CMS

Refresh rate

Select the optimal synchronization mode:

• Real-time (API): For products with rapidly changing stock levels
• Every 1-4 hours: For popular categories (electronics)
• Daily (night): For standard assortment
• Weekly: For low-turnover products

Real Case: Online Electronics Store

International Adaptation: Multiregionality

Working with international data formats

When working with suppliers from different countries:

Number formats:

• US/UK: 1,234.56 (comma is the thousands separator, period is the decimal point)
• EU: 1.234.56 (the period is the thousands separator, the comma is the decimal point)
• Solution: Detect format automatically or set it for each supplier

Text encodings:

• UTF-8 (universal, recommended)
• Windows-1251 (Cyrillic, obsolete)
• ISO-8859-1 (Western Europe)
• Solution: Autodetection of encoding (chardet library for Python)

Multicurrency: Price Management

Strategy 1: Single Base Currency

Convert all prices to one currency (e.g. EUR):

• Use APIs with up-to-date rates (ECB, Fixer.io, CurrencyLayer)
• Update your rates at least once a day
• For B2B, you can fix the rate for a period (week/month)
• Keep the original currency and conversion rate for auditing

Strategy 2: Multi-currency Catalog

Store prices in their original currencies and convert them when displaying to clients:

• Allows you to avoid the accumulation of rounding errors
• Current prices in real time
• It is more difficult to compare offers from different suppliers

Taxes and customs duties

If you work with external suppliers:

• VAT: Consider whether VAT is included in the supplier's price
• Customs duties: Add to the cost of goods from outside the EU
• Delivery: International deliveries are more expensive than local ones

Conclusion

Consolidating price lists from multiple suppliers is a complex, but solvable, task. Key findings:

• Standardization is the basis: Create a single data schema to which all sources are converted
• Automation is critical: Manual processing of 10+ price lists takes 15-20 hours per week
• Comparing products is the most difficult stage: Use EAN/MPN where possible, for the rest - fuzzy search algorithms
• Priority rules should be flexible: Different strategies for different product categories
• Monitoring and validation are mandatory: 5-10% of data may arrive with errors
• ROI is high: The systems pay for themselves in 2-4 months due to time savings and procurement optimization.

Start by taking inventory of your data sources, identifying priority providers, and creating a unified schema. Automate the process gradually—first parsing, then matching, then conflict resolution.