Retail pricing data is messier than a price field

You can have a clean pricing model and still make bad pricing decisions because the input data is wrong. In retail, the problem usually starts with a simple assumption: one product page has one price. Then you discover variants, coupons, member pricing, out-of-stock items, regional pricing, marketplace sellers, and JavaScript-rendered pages that sometimes return nothing at all.

Retail teams talk about competitive pricing, dynamic pricing, discount pricing, and value-based pricing. Developers usually inherit the operational part: collect enough reliable data so those strategies do not run on guesses.

Treat price as an observation, not a product attribute

A common first version of a price monitor stores this:

CREATE TABLE competitor_products (
  sku TEXT PRIMARY KEY,
  competitor_url TEXT NOT NULL,
  price_cents INTEGER,
  currency TEXT,
  updated_at TIMESTAMP
);

That works until the same URL shows different prices depending on size, location, login state, promotion, or seller. Updating one row destroys useful history and hides uncertainty.

A better pattern is to store every scrape result as an observation:

CREATE TABLE price_observations (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  internal_sku TEXT NOT NULL,
  competitor TEXT NOT NULL,
  competitor_url TEXT NOT NULL,
  observed_at TIMESTAMP NOT NULL,

  variant_key TEXT,
  seller_name TEXT,
  price_cents INTEGER,
  list_price_cents INTEGER,
  currency TEXT,
  in_stock BOOLEAN,
  promo_text TEXT,

  http_status INTEGER,
  extraction_status TEXT NOT NULL,
  raw_hash TEXT,
  error_message TEXT
);

This schema lets you answer different questions later:

• What was the lowest available price today?
• Did the competitor discount the item or change the base price?
• Was the product actually purchasable?
• Did our scraper fail, or did the site stop showing the product?

That distinction matters. A missing price is not the same thing as a zero price, and an out-of-stock product should not always trigger an aggressive price drop.

Capture the effective price, not just the visible number

Retail pages often expose several numbers:

• List price: $129.99
• Sale price: $99.99
• Coupon: Save 10% at checkout
• Loyalty price: $89.99 with membership
• Bundle price: 2 for $170

If your parser grabs the first currency-looking string, it will eventually make a bad decision. You might parse the crossed-out list price instead of the sale price. Or you might treat a coupon as unavailable because it appears outside the price container.

A simple extractor should make the decision explicit:

from bs4 import BeautifulSoup
from decimal import Decimal
import re

PRICE_RE = re.compile(r"(\d+[\d,]*\.\d{2})")

def cents(text: str) -> int | None:
    if not text:
        return None
    match = PRICE_RE.search(text.replace(",", ""))
    if not match:
        return None
    return int(Decimal(match.group(1)) * 100)

def extract_price(html: str) -> dict:
    soup = BeautifulSoup(html, "html.parser")

    sale = soup.select_one('[data-testid="sale-price"]')
    list_price = soup.select_one('[data-testid="list-price"]')
    stock = soup.select_one('[data-testid="stock-status"]')
    promo = soup.select_one('[data-testid="promo-message"]')

    price_cents = cents(sale.get_text(" ")) if sale else None
    list_price_cents = cents(list_price.get_text(" ")) if list_price else None

    if price_cents is None:
        raise ValueError("sale price selector matched nothing or contained no parseable price")

    return {
        "price_cents": price_cents,
        "list_price_cents": list_price_cents,
        "in_stock": bool(stock and "in stock" in stock.get_text(" ").lower()),
        "promo_text": promo.get_text(" ", strip=True) if promo else None,
    }

This is intentionally boring code. The important part is that extraction failure is explicit. If the selector breaks, you get a ValueError instead of silently inserting NULL and letting downstream pricing code interpret it however it wants.

Expect matching to be harder than scraping

Competitive pricing depends on comparing the same product. That sounds obvious, but matching breaks in small ways:

• A competitor lists a 12-pack while you sell a single unit.
• Color names differ: navy, midnight blue, blue black.
• Product titles include accessories or bundles.
• A marketplace page changes the default seller.
• Variant URLs canonicalize back to the parent product.

Do not match only on title similarity. Store the evidence you used for the match:

{
  "internal_sku": "SHOE-123-BLK-10",
  "competitor": "example-store",
  "competitor_url": "https://example.com/products/running-shoe",
  "match_confidence": 0.92,
  "matched_fields": ["brand", "model", "color", "size"],
  "unit_quantity": 1
}

If a price suddenly drops by 70%, the first debugging question is not “what did the pricing algorithm do?” It is “did we compare the right item?”

Separate collection failures from business signals

Scrapers fail for normal reasons. You get 403 Forbidden, 429 Too Many Requests, stale cached HTML, bot checks, changed markup, and pages that render prices only after client-side API calls. These are collection failures, not market events.

Your pipeline should classify them separately:

try:
    response = fetch(url)
    data = extract_price(response.text)
    status = "ok"
    error = None
except TimeoutError as exc:
    data = {}
    status = "fetch_timeout"
    error = str(exc)
except ValueError as exc:
    data = {}
    status = "extraction_failed"
    error = str(exc)

Then add a rule: pricing decisions can use only observations where extraction_status = 'ok' and observed_at is recent enough for the category. Grocery prices might need hourly checks. Furniture might not.

If you are buying rather than building this extraction layer, Wire fits this part of the problem: competitor price monitoring where variant matching, promotion capture, and extraction failure handling all affect the final dataset.

Make pricing rules conservative by default

Even if the business wants dynamic pricing, the first implementation should avoid automatic overreaction. Put guardrails around changes:

SELECT internal_sku,
       MIN(price_cents) AS competitor_min_price
FROM price_observations
WHERE extraction_status = 'ok'
  AND in_stock = 1
  AND observed_at > datetime('now', '-6 hours')
GROUP BY internal_sku;

Then apply limits outside SQL:

• Do not change price if fewer than two competitors have fresh observations.
• Do not match an out-of-stock competitor.
• Do not drop below margin floor.
• Do not react to one anomalous observation without a second confirmation.
• Log the input observations used for every price change.

That last point saves time. When someone asks why an item changed from $49.99 to $44.99, you need more than “the job ran.”

A practical next step

Pick ten SKUs and track them across two competitors for a week. Store observations instead of overwriting rows. Include stock, promo text, variant, seller, extraction status, and raw response hashes. Before writing any pricing algorithm, inspect the weird cases manually. Those weird cases are the system requirements.