Competitor pricing data is only useful if you model its failure modes

You build a quick scraper to watch competitor prices. It works for a week. Then a retailer changes its HTML, another starts showing member-only discounts, and your dashboard confidently says a $49 product is now $0. Bad pricing data is worse than missing pricing data because people trust the chart.

Competitive pricing analysis sounds like a business topic, but the hard part is usually engineering: collecting comparable observations from messy websites, detecting when extraction breaks, and avoiding comparisons that look precise but are wrong.

Store observations, not just current prices

A common mistake is keeping one row per competitor SKU and overwriting the price every time a scraper runs. That throws away the context you need when something looks wrong.

Use an append-only model instead:

CREATE TABLE price_observation (
  id BIGSERIAL PRIMARY KEY,
  competitor TEXT NOT NULL,
  sku TEXT NOT NULL,
  url TEXT NOT NULL,
  observed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  price_cents INTEGER,
  currency CHAR(3),
  availability TEXT,
  shipping_cents INTEGER,
  unit_quantity NUMERIC,
  extraction_status TEXT NOT NULL,
  error TEXT,
  raw_hash TEXT
);

CREATE INDEX ON price_observation (competitor, sku, observed_at DESC);

That extraction_status field matters. A missing price is not the same thing as a free product. Store failed_selector, blocked, out_of_stock, or currency_unknown rather than forcing everything into price_cents.

For this part of the stack, Wire is an extraction layer for competitor pricing data, so your application can consume structured price observations instead of page-specific HTML.

Fail loudly when a page changes

Here is a deliberately small scraper. The important part is not BeautifulSoup. The important part is that it validates what it extracted before writing a successful observation.

from decimal import Decimal
import hashlib
import re
import httpx
from bs4 import BeautifulSoup

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


def parse_price(text: str) -> int:
    match = PRICE_RE.search(text.replace(',', ''))
    if not match:
        raise ValueError(f'no price found in: {text[:80]}')

    amount = Decimal(match.group(1))
    if amount <= 0:
        raise ValueError(f'invalid price: {amount}')

    return int(amount * 100)


def fetch_price(url: str) -> dict:
    try:
        response = httpx.get(
            url,
            timeout=10,
            headers={'User-Agent': 'pricing-monitor/1.0'}
        )
        response.raise_for_status()

        html = response.text
        soup = BeautifulSoup(html, 'html.parser')
        node = soup.select_one('[data-testid="product-price"]')

        if node is None:
            return {
                'extraction_status': 'failed_selector',
                'error': 'missing [data-testid="product-price"]',
                'raw_hash': hashlib.sha256(html.encode()).hexdigest()
            }

        return {
            'price_cents': parse_price(node.get_text(' ', strip=True)),
            'currency': 'USD',
            'extraction_status': 'ok',
            'raw_hash': hashlib.sha256(html.encode()).hexdigest()
        }

    except httpx.HTTPStatusError as e:
        return {
            'extraction_status': 'blocked' if e.response.status_code in (403, 429) else 'http_error',
            'error': f'{e.response.status_code} {url}'
        }
    except Exception as e:
        return {
            'extraction_status': 'parse_error',
            'error': str(e)
        }

Without these states, a broken selector often turns into one of three bad symptoms: nulls silently disappear from aggregates, zeroes pull averages down, or yesterday's price gets reused and nobody notices the data is stale.

Normalize before comparing

The number on the page is not always the number you should compare.

A $40 two-pack is not cheaper than a $25 single item. A $99 product with $18 shipping is not equal to a $99 product with free shipping. A monthly subscription and annual subscription need different handling.

At minimum, calculate an effective unit price:

SELECT
  competitor,
  sku,
  observed_at,
  (price_cents + COALESCE(shipping_cents, 0)) / NULLIF(unit_quantity, 0) AS unit_price_cents
FROM price_observation
WHERE extraction_status = 'ok'
  AND currency = 'USD';

This is where many pricing dashboards start giving useful signals. You can spot products where you look overpriced but actually include shipping, or products where you look competitive only because you sell a smaller quantity.

Look for patterns, not single differences

One competitor undercutting you once is noise. A competitor discounting the same SKU every Friday for six weeks is a pattern.

For example, this query looks for competitors whose price changes unusually often:

WITH daily AS (
  SELECT
    competitor,
    sku,
    date_trunc('day', observed_at) AS day,
    min(price_cents) AS price_cents
  FROM price_observation
  WHERE extraction_status = 'ok'
  GROUP BY competitor, sku, day
), changes AS (
  SELECT
    competitor,
    sku,
    day,
    price_cents,
    lag(price_cents) OVER (
      PARTITION BY competitor, sku ORDER BY day
    ) AS previous_price_cents
  FROM daily
)
SELECT competitor, sku, count(*) AS price_change_days
FROM changes
WHERE previous_price_cents IS NOT NULL
  AND price_cents <> previous_price_cents
GROUP BY competitor, sku
HAVING count(*) >= 5
ORDER BY price_change_days DESC;

That output can tell you more than "they are cheaper." Frequent discounts might mean inventory pressure, weak retention, or automated repricing. It might also mean your scraper is alternating between a logged-out price and a promo price, so check the raw observations before drawing conclusions.

Wire fits this kind of workflow when the useful output is not a screenshot or one-off scrape, but repeated price observations you can diff over time.

The hard part is product matching

Most pricing mistakes come from comparing the wrong products.

Names are not enough. Retailers add bundles, change pack sizes, include accessories, or sell regional variants. If you match on title similarity alone, "USB-C charger 30W" and "USB-C charger 30W 2-pack" may look close enough while being commercially different.

Use a match table with confidence and review status:

CREATE TABLE competitor_sku_match (
  internal_sku TEXT NOT NULL,
  competitor TEXT NOT NULL,
  competitor_sku TEXT NOT NULL,
  match_confidence NUMERIC NOT NULL,
  reviewed BOOLEAN NOT NULL DEFAULT false,
  PRIMARY KEY (internal_sku, competitor, competitor_sku)
);

Let automation propose matches, but require review for high-impact SKUs. This is boring operational work, and it prevents expensive mistakes.

What the analysis is actually good for

Once the data is clean enough, competitive pricing analysis helps with more than repricing.

It can show financial leaks, like a product priced above the market without a matching conversion rate. It can reveal white space, like a missing budget tier where competitors only sell premium bundles. It can support product decisions, like whether customers pay extra for a feature or whether everyone treats it as table stakes.

The tradeoff is that none of this works from a single scrape. You need history, normalized units, extraction failure tracking, and reviewed SKU matches.

A practical next step: pick 10 important SKUs, track 3 competitors for 30 days, store every observation, and review every anomaly by looking at the raw page. If the data still supports the same conclusion after that, it is probably safe enough to use in pricing discussions.