Your competitor's price is not a float

You add competitor pricing to a dashboard and start with the obvious model: SKU, competitor, price. It works for retailer pages. Then someone asks why the same product costs $6.49 on one site, $7.29 on a delivery app, and $14.74 at checkout. The bug is not in your scraper. The bug is the assumption that a product has one competitor price.

A price is an observation, not a property

For a nationally distributed product, customers may see different prices on your site, Amazon, Walmart, Instacart in one zip code, Instacart in another zip code, and DoorDash or Uber Eats from a nearby store.

Those are not copies of the same decision. Different systems set them:

• Your site price is usually controlled by you.
• Amazon may expose a Buy Box price affected by third-party sellers and marketplace rules.
• A retailer site reflects that retailer's margin and promo calendar.
• Instacart can vary by store, banner, zip code, fulfillment path, and shopper context.
• Delivery apps often add platform markup, delivery fees, service fees, small-basket fees, and sometimes demand-based charges.

If your schema stores products.price, you will overwrite useful data or average it into something no customer actually saw.

A better primitive is a price observation:

create table price_observations (
  id bigserial primary key,
  sku text not null,
  competitor_sku text,
  surface text not null,              -- amazon, walmart, instacart, doordash
  retailer text,
  postcode text,
  observed_at timestamptz not null,
  subscriber_context text not null,   -- none, prime, instacart_plus, dashpass
  item_price_cents integer not null,
  delivery_fee_cents integer,
  service_fee_cents integer,
  surcharge_cents integer,
  currency char(3) not null default 'USD',
  raw_url text,
  raw_payload jsonb
);

This looks more annoying than price numeric(10,2), because it is. It also matches the thing you are measuring.

For teams collecting these delivery and quick-commerce prices as structured data, Wire treats the surface, postcode, fee components, and shopper context as part of the price record rather than metadata to recover later.

The checkout total can reverse the signal

On delivery surfaces, the listed item price is not the number the customer pays. That matters when you compare competitors.

Say your item is $7.29 on a grocery delivery app. A competitor's item is $5.99. If you track only item price, you report a 17.8% gap.

But the checkout may look like this:

from dataclasses import dataclass

@dataclass
class DeliveryPrice:
    item: int
    delivery_fee: int
    service_fee: int
    surcharge: int = 0

    @property
    def delivered_total(self) -> int:
        return self.item + self.delivery_fee + self.service_fee + self.surcharge

mine = DeliveryPrice(item=729, delivery_fee=399, service_fee=146, surcharge=200)
theirs = DeliveryPrice(item=599, delivery_fee=399, service_fee=146, surcharge=200)

item_gap = (mine.item - theirs.item) / theirs.item
total_gap = (mine.delivered_total - theirs.delivered_total) / theirs.delivered_total

print(f"item gap: {item_gap:.1%}")
print(f"delivered gap: {total_gap:.1%}")

Output:

item gap: 21.7%
delivered gap: 9.1%

The fee stack did not eliminate the difference, but it changed its size. In some baskets it compresses the competitive gap. In others it amplifies it, especially when fees depend on basket size or subscription status.

The common failure mode is a dashboard that says "we are 20% more expensive" while a customer sees a much smaller checkout difference. Or the reverse: item prices look close, but one platform's local fees make the delivered total much worse.

Location is part of the key

A national average is often useful for retailer sites. It is much less useful for delivery apps.

A delivery price depends on the local store, local platform markup, delivery supply, fee rules, and sometimes the exact address. Two zip codes in the same metro area can produce different item prices and different fees for the same SKU.

That means this query is usually wrong:

select sku, avg(item_price_cents) as avg_price
from price_observations
where surface = 'instacart'
group by sku;

It creates a number that no shopper paid. Use it only if you explicitly want a statistical summary.

For pricing decisions, keep geography in the grouping key:

select
  sku,
  postcode,
  percentile_cont(0.5) within group (order by item_price_cents) as median_item_price,
  percentile_cont(0.5) within group (
    order by item_price_cents
      + coalesce(delivery_fee_cents, 0)
      + coalesce(service_fee_cents, 0)
      + coalesce(surcharge_cents, 0)
  ) as median_delivered_total
from price_observations
where surface in ('instacart', 'doordash', 'ubereats')
group by sku, postcode;

This is not just a reporting detail. If a pricing team can only change prices regionally, collect data regionally. If it can act at store or zip-code level, collect at that level. The measurement grain should match the decision grain.

Subscription context is another split

Instacart+, DashPass, Uber One, Walmart+, and Prime can create different effective prices for the same product at the same time.

Sometimes the item price changes. Sometimes the delivery fee disappears. Sometimes the platform applies member-only promotions at checkout. If your scraper runs from a logged-in account, you may collect a subscriber price and label it as the public price. If it runs logged out, you may miss the price your most loyal customers see.

This is the kind of bug that does not throw an exception. It just poisons comparisons quietly.

Store the context explicitly:

{
  "sku": "DOGFOOD-12LB",
  "surface": "doordash",
  "postcode": "77002",
  "subscriber_context": "dashpass",
  "item_price_cents": 1899,
  "delivery_fee_cents": 0,
  "service_fee_cents": 285,
  "surcharge_cents": 0
}

If a vendor or internal pipeline cannot tell you whether an observation came from a subscriber or non-subscriber session, do not mix those rows without labeling the result.

Wire separates subscriber and non-subscriber delivery observations so downstream pricing analysis can compare like with like.

What I would build first

Before building a large monitoring system, run a small audit.

Pick one SKU. Collect prices from your own site, Amazon, one major retailer, Instacart in two distant zip codes, and one delivery app. For delivery surfaces, record item price, delivery fee, service fee, surcharge, postcode, timestamp, and subscription context.

Then calculate two spreads:

• item price spread
• delivered total spread

If those numbers are close, your current item-price tracker may be good enough for that category. If they differ materially, your data model needs to capture fees, location, and shopper context before the dashboard can answer the pricing question correctly.