Revenue Management Pricing & forecasting pipelines
5 min read Ingestion

Normalizing scraped rates to a canonical schema

Scraped competitor rates arrive as a mess: “$1,299”, “1.299,00 EUR”, “from £180/night”, taxes sometimes in, sometimes out. Before any of it can inform a pricing decision it has to become a clean, comparable, canonical record. This guide builds that normalization layer, extending Competitor Rate Scraping Pipelines in the Data Ingestion & OTA API Integration Workflows pillar, and it feeds the contract gate described in Data Quality & Schema Contracts.

Prerequisites

  • Python 3.11+
  • pydantic for the canonical model; standard library re, decimal, logging
  • Raw extracted fields from the scraping parser (price string, currency hint, tax flag)
  • The internal rate taxonomy from Rate Plan Structuring & Mapping
  • A tax model consistent with Tax & Fee Resolution

Step 1 — Parse messy price strings into integer minor units

Never store money as a float or a display string. Strip formatting, detect the decimal convention, and convert to integer cents so downstream comparisons are exact.

python 
from __future__ import annotations

import logging
import re
from decimal import Decimal, InvalidOperation

logger = logging.getLogger("scrape.normalize")

_CURRENCY_SYMBOL = {"$": "USD", "£": "GBP", "€": "EUR", "¥": "JPY"}


def parse_price_cents(raw: str) -> int:
    symbol = next((s for s in _CURRENCY_SYMBOL if s in raw), None)
    digits = re.sub(r"[^\d.,]", "", raw)
    # Treat the last separator as the decimal point, strip the rest as grouping
    if "," in digits and "." in digits:
        digits = digits.replace(",", "") if digits.rfind(".") > digits.rfind(",") \
            else digits.replace(".", "").replace(",", ".")
    elif "," in digits:
        digits = digits.replace(",", ".") if len(digits.split(",")[-1]) == 2 \
            else digits.replace(",", "")
    try:
        amount = Decimal(digits)
    except InvalidOperation as exc:
        raise ValueError(f"unparseable price {raw!r}") from exc
    return int((amount * 100).to_integral_value())

Step 2 — Resolve currency and tax basis

A rate is only comparable once you know its currency and whether tax is included. Prefer an explicit currency field, fall back to a detected symbol, and normalize every rate to a tax-exclusive basis so competitor prices line up with your own net rates.

python 
def resolve_currency(raw_price: str, hint: str | None) -> str:
    if hint:
        return hint.upper()
    for symbol, code in _CURRENCY_SYMBOL.items():
        if symbol in raw_price:
            return code
    raise ValueError(f"no currency for {raw_price!r}")


def to_tax_exclusive(gross_cents: int, tax_rate: float, tax_included: bool) -> int:
    if not tax_included:
        return gross_cents
    if not 0.0 <= tax_rate < 1.0:
        raise ValueError("tax_rate out of range")
    return round(gross_cents / (1.0 + tax_rate))

Step 3 — Assemble the canonical record

Bring the pieces into a validated model keyed by the same identity your internal rates use, so a competitor rate can be compared to yours without translation.

python 
from datetime import date
from pydantic import BaseModel, Field


class CanonicalRate(BaseModel):
    competitor_id: str
    room_category: str
    stay_date: date
    net_rate_cents: int = Field(gt=0)
    currency: str = Field(pattern=r"^[A-Z]{3}$")


def normalize(raw: dict, tax_rate: float) -> CanonicalRate:
    currency = resolve_currency(raw["price"], raw.get("currency"))
    gross = parse_price_cents(raw["price"])
    net = to_tax_exclusive(gross, tax_rate, raw.get("tax_included", False))
    record = CanonicalRate(
        competitor_id=raw["competitor_id"],
        room_category=raw["room_category"],
        stay_date=date.fromisoformat(raw["stay_date"]),
        net_rate_cents=net,
        currency=currency,
    )
    logger.info("normalized %s %s -> %d %s net",
                record.competitor_id, record.stay_date, net, currency)
    return record

Normalizing to a tax-exclusive net rate is the step that makes comparisons honest: a competitor’s tax-inclusive headline is not really cheaper than your tax-exclusive rate until both are on the same basis.

Verification and testing

python 
def test_european_format_and_tax_strip() -> None:
    raw = {"competitor_id": "c1", "room_category": "std", "stay_date": "2026-08-01",
           "price": "1.299,00 €", "currency": "EUR", "tax_included": True}
    rec = normalize(raw, tax_rate=0.10)
    # 1299.00 gross, 10% tax removed -> 1180.91 -> 118091 cents
    assert rec.net_rate_cents == 118091
    assert rec.currency == "EUR"

Common pitfalls and edge cases

  • Float money. Parsing to float introduces rounding error; use Decimal then integer cents.
  • Ambiguous separators. “1,299” is 1299 in the US and 1.299 in the EU; decide by decimal-place count and locale hint.
  • Mixed tax bases. Comparing a tax-inclusive scrape to a net internal rate overstates competitiveness; normalize the basis first.
  • Currency assumed. Defaulting a missing currency to USD silently corrupts cross-market comparisons; require a resolvable currency.
  • Category mismatch. A competitor’s “Deluxe” is not your “Deluxe”; map to your room taxonomy before comparing.