Price Monitoring Competitor Intelligence Workflows

Building a Competitive Price Index

A competitive price index reduces a table of competitor prices to a single ratio per SKU — your price over a market reference — and then rolls those ratios up to one portfolio number that tells you, in aggregate, whether you are priced above or below the market where your revenue actually sits. This is a focused, runnable recipe for computing both, in Python with pandas, from a flat table of competitor PriceObservation rows. It sits under price positioning and competitive benchmarking, which frames the wider positioning problem, and it pairs with detecting competitor price changes, which tells you when a competitor observation is fresh enough to warrant recomputing the index.

Prerequisites & Input Contract

The index is only as honest as its inputs, so two things must already be true. Every competitor price must be attached to the correct canonical product_id — matching is upstream, per fuzzy matching algorithms for SKU alignment — and every price must be in one base currency and unit basis, per currency conversion and exchange-rate sync. This recipe consumes clean observations; it does not clean them.

Library versions: Python 3.11+, pandas>=2.1, numpy>=1.26. The competitor observations arrive as one row per (product, competitor, scrape):

import pandas as pd
import numpy as np
from datetime import datetime, timedelta, timezone

# One row per competitor observation, already matched + normalized.
obs = pd.DataFrame([
    # product_id, competitor_id, price, in_stock, observed_at (UTC), tier
    ("p-100", "acme",   199.00, True,  "2026-07-15T08:00Z", 1),
    ("p-100", "globex", 205.00, True,  "2026-07-15T07:30Z", 1),
    ("p-100", "initech",189.00, True,  "2026-07-15T06:10Z", 2),
    ("p-100", "umbrella",9999.0, False,"2026-07-15T05:00Z", 3),  # OOS placeholder
    ("p-200", "acme",    49.00, True,  "2026-07-14T02:00Z", 1),  # stale (>24h)
    ("p-200", "globex",  51.00, True,  "2026-07-15T07:45Z", 1),
    ("p-300", "acme",    12.50, True,  "2026-07-15T07:00Z", 1),  # thin coverage
], columns=["product_id","competitor_id","price","in_stock","observed_at","tier"])
obs["observed_at"] = pd.to_datetime(obs["observed_at"])

# Your own catalogue prices and trailing revenue per SKU.
own = pd.DataFrame([
    ("p-100", 189.00, 42000.0),
    ("p-200",  53.00,  9000.0),
    ("p-300",  11.90,   400.0),
], columns=["product_id","own_price","trailing_revenue"])

Step-by-Step Implementation

Step 1 — Filter to fresh, in-stock observations

An out-of-stock listing is not a market price, and a stale one describes yesterday’s market. Both must be excluded before any statistic is computed, not down-weighted after.

def fresh_in_stock(obs: pd.DataFrame, staleness_hours: int = 24) -> pd.DataFrame:
    cutoff = datetime.now(timezone.utc) - timedelta(hours=staleness_hours)
    # Compare tz-aware timestamps; a naive `observed_at` would raise here.
    return obs[(obs["in_stock"]) & (obs["observed_at"] >= cutoff)].copy()

With a fixed “now” of 2026-07-15T09:00Z, this drops the umbrella out-of-stock row on p-100 and the stale acme row on p-200, leaving three usable observations on p-100, one on p-200, and one on p-300.

Step 2 — Reduce each competitor set to one reference price

Compute several reference statistics in one group-by so a category can later pick the one it trusts. The median is the robust default; the minimum drives price-match rules; the trimmed mean needs enough points to be meaningful, so it falls back to the median below five observations.

def trimmed_mean(s: pd.Series, trim: float = 0.10) -> float:
    if len(s) < 5:
        return float(s.median())          # not enough points to trim
    lo, hi = s.quantile(trim), s.quantile(1 - trim)
    kept = s[(s >= lo) & (s <= hi)]
    return float(kept.mean()) if len(kept) else float(s.median())

def build_reference(obs: pd.DataFrame, min_competitors: int = 3) -> pd.DataFrame:
    fresh = fresh_in_stock(obs)
    ref = fresh.groupby("product_id")["price"].agg(
        ref_min="min",
        ref_median="median",
        ref_trimmed=trimmed_mean,
        n_competitors="count",
    ).reset_index()
    # Coverage flag: below the floor the reference is one seller's opinion.
    ref["low_coverage"] = ref["n_competitors"] < min_competitors
    return ref

ref = build_reference(obs)
print(ref[["product_id","ref_median","n_competitors","low_coverage"]])

Expected output:

  product_id  ref_median  n_competitors  low_coverage
0      p-100       199.0              3         False
1      p-200        51.0              1          True
2      p-300        12.5              1          True

p-100 has full coverage; p-200 and p-300 are flagged because a single fresh competitor is not a market.

Step 3 — Divide your price into the reference to get the index

The index and the signed gap are two vectorized operations on the join. A SKU with no fresh competitor produces a NaN index by design — that is the signal for “no reference”, and it must survive to the caller intact.

def compute_index(own: pd.DataFrame, ref: pd.DataFrame,
                  reference_col: str = "ref_median") -> pd.DataFrame:
    df = own.merge(ref, on="product_id", how="left")
    df["reference"] = df[reference_col]
    df["price_index"] = df["own_price"] / df["reference"]
    df["gap_pct"] = df["price_index"] - 1.0            # signed, unit-free
    return df

idx = compute_index(own, ref)
print(idx[["product_id","own_price","reference","price_index","gap_pct","low_coverage"]].round(4))

Expected output:

  product_id  own_price  reference  price_index  gap_pct  low_coverage
0      p-100     189.00      199.0       0.9497  -0.0503        False
1      p-200      53.00       51.0       1.0392   0.0392         True
2      p-300      11.90       12.5       0.9520  -0.0480         True

p-100 is priced ~5% below market on full coverage — an actionable, trustworthy signal. p-200 reads 3.9% above market but on a single competitor, so it is low-confidence.

Step 4 — Roll up to a revenue-weighted portfolio index

An unweighted mean of the per-SKU indices lets low-value SKUs dominate. Weight by trailing revenue so the portfolio number tracks where the money competes, and exclude no-reference SKUs from both numerator and denominator.

def portfolio_index(idx: pd.DataFrame, weight_col: str = "trailing_revenue") -> float:
    valid = idx.dropna(subset=["price_index"])
    w = valid[weight_col]
    if w.sum() == 0:
        return float("nan")
    return float(np.average(valid["price_index"], weights=w))

print(round(portfolio_index(idx), 4))     # -> 0.9553

The portfolio sits at 0.9553 — about 4.5% below market overall — and because p-100 carries the revenue, its below-market position drives the aggregate far more than the small, thin-coverage SKUs. The full data flow is below.

Pipeline from competitor observations to a portfolio price indexFour stages left to right. Competitor observations flow into a freshness and in-stock filter, then into a per-product reference statistic such as median, then divide into your own price to yield a per-SKU index and gap, and finally roll up weighted by revenue into a single portfolio index.Observationsfresh + in-stock filterReferencemedian · trimmed · minPer-SKU indexown ÷ referencePortfolio indexrevenue-weighted

Verification & Testing

Pin the behaviour with assertions on the worked example, then guard the two failure edges that matter most: the reference must ignore out-of-stock and stale rows, and a no-reference SKU must yield NaN, never 1.0.

def test_index():
    r = build_reference(obs)
    # p-100 median is over the 3 fresh in-stock prices, not the OOS placeholder.
    assert r.loc[r.product_id == "p-100", "ref_median"].iloc[0] == 199.0
    i = compute_index(own, r)
    assert round(i.loc[i.product_id == "p-100", "price_index"].iloc[0], 4) == 0.9497
    # A SKU absent from the reference must be NaN, not silently at-market.
    extra = pd.DataFrame([("p-999", 10.0, 100.0)],
                         columns=["product_id","own_price","trailing_revenue"])
    j = compute_index(extra, r)
    assert pd.isna(j["price_index"].iloc[0])
    print("index recipe verified")

test_index()   # -> index recipe verified

For a live check, spot-audit a handful of high-revenue SKUs against the raw observations weekly: the reference should match a hand computation over exactly the fresh, in-stock rows.

Edge Cases & Gotchas

  • Out-of-stock placeholder prices. Marketplaces frequently keep a last-known or sentinel price ($9,999, $0.01) on out-of-stock listings. The in_stock filter is the guard; if your feed lacks a reliable stock flag, treat prices beyond a MAD threshold from the median as suspect and tie into statistical outlier detection for price data.
  • Timezone-naive timestamps. Comparing a naive observed_at to a tz-aware cutoff raises in pandas. Normalize every observation to UTC-aware at ingestion; a silent errors="coerce" here would turn parse failures into NaT and quietly drop rows.
  • Thin coverage read as confident. An index built on one competitor has a real value but no stability. Always carry the low_coverage flag alongside the index so a repricing engine can hold rather than chase a single seller.
  • Zero or missing reference. A reference of 0 from a corrupt feed makes the index inf. Filter non-positive prices before the group-by, and let a missing reference stay NaN rather than back-filling it.

Performance Notes

The whole computation is one groupby plus two vectorized column operations, so it is dominated by the filter and the join, not the arithmetic. At a few hundred thousand SKUs with a handful of competitors each, the full recompute runs in low single-digit seconds in pandas and sub-second in polars. The scaling mistake is recomputing the entire portfolio on every scrape event: cache the reference table keyed by product_id and refresh only the products whose competitor set changed on this scrape — decided by detecting competitor price changes — so a single competitor move re-divides one row instead of rebuilding the catalogue.

Frequently Asked Questions

Which reference statistic should I use by default? The median. It is robust to a single mispriced or scrape-corrupted competitor without any tuning, and it converges with the trimmed mean once you have five or more observations. Use the minimum only for explicit price-match SKUs, where the cheapest competitor is the target by policy.

What index value counts as “at market”? An index of exactly 1.00 is at the reference, but you should treat a band around it — typically 0.97 to 1.03 for consumer electronics, wider for commodities — as at-market, because sub-percent differences are noise, not positioning.

How should the portfolio index handle SKUs with no competitors? Exclude them from both the weighted numerator and the denominator. Their index is undefined, not 1.0, so folding them in as at-market would bias the portfolio number toward the middle and hide real coverage gaps.