Your price alert may be a store switch in disguise

Your price alert can fire because the store changed underneath it.

You've joined on itemId, calculated the difference and prepared a chart. Then your manager asks whether both prices came from the same store. That's an uncomfortable question to answer after the chart has acquired a colour scheme.

A fixed storeId doesn't guarantee a fixed price store. It identifies the store you requested; storeNumber identifies the price store Lowe's returned, which can be a nearby store.

A fixed request can produce a moving price context

For a defensible price series, the product identifier is only part of the comparison. You also need the location and currency context attached to each observation.

Dataspryva's Actor reads product data live for each product. Price, stock and fulfilment therefore reflect the collection time, rather than a frozen catalogue.

We'll use an explicit product list to keep discovery changes out of the main comparison. The capture function retains the submitted input alongside the dataset identifier and raw records.

Example input and capture cell, with the token read from the environment:

import json
import os

import duckdb
import pandas as pd
from apify_client import ApifyClient

client = ApifyClient(os.environ["APIFY_TOKEN"])

run_input = {
    "mode": "productDetails",
    "itemIds": ["5013207487", "50040962"],
    "storeId": "0208",
    "zipCode": "92253",
    "inStockOnly": False,
    "onSaleOnly": False,
    "onShelfOnly": False,
    "includeDetails": False,
    "maxItems": 50,
}

def capture():
    run = client.actor(
        "dataspryva/lowes-price-stock-and-specification-scraper"
    ).call(run_input=run_input)

    if run["status"] != "SUCCEEDED":
        raise RuntimeError(f"Capture incomplete: {run['status']}")

    records = []
    for item in client.dataset(run["defaultDatasetId"]).iterate_items():
        records.append(item)

    return {
        "dataset_id": run["defaultDatasetId"],
        "run_input": run_input.copy(),
        "records": records,
    }

captures = {"before": capture()}

Keeping the availability and sale filters off matters here. Otherwise, a product crossing a filter boundary could disappear from your snapshot just when its price becomes interesting.

Which identifier belongs in the join?

Use itemId as the product anchor. Titles are useful for human inspection, but making them join keys turns an editorial change into an apparent product departure.

The observation key also needs the snapshot identifier and requested context. That lets you collect the same product for different locations without merging those observations.

The fields fall into these roles:

Role	Fields retained	Comparison rule
Product identity	itemId	Join exactly; reject missing or duplicate keys
Requested context	Input storeId, zipCode	Retain from the input manifest and include in the join
Returned price context	storeNumber, storeZip, priceScope, currency	Require usable, matching values before subtraction
Measurement and timing	price, scrapedAt	Parse separately; retain the original representation

The input manifest is deliberate. It preserves what you asked for independently of which optional fields appear in a returned record.

Here's the comparison boundary:

same item + same requested context
                 |
       returned price context
          /              \
     matches             differs
        |                   |
  compare prices      context review

A store transition is useful information. It just belongs in a different event category from a price movement.

Keep the request beside the snapshot

The next cell captures the later observation. In a scheduled tracker, those captures belong at your collection intervals; executing this notebook straight through makes consecutive captures, not a historical backfill.

Empty fields are omitted from records. We make those absences explicit before joining, rather than letting a missing column decide whether the notebook runs.

Store identifiers remain strings. For the returned store number, we accept only digits within the store-number width and restore leading zeros. We don't extract digits from arbitrary text and hope they identify a store.

Timestamps are converted to UTC before removing timezone metadata for storage. Every observed_at value therefore uses the same UTC convention.

This cell creates the comparison frame and stops ambiguous product joins:

captures["after"] = capture()

field_map = {
    "itemId": "item_id",
    "storeNumber": "store_number",
    "storeZip": "store_zip",
    "priceScope": "price_scope",
    "currency": "currency",
    "price": "price_raw",
    "scrapedAt": "scrapedAt",
}

def normalise(snapshot_id, captured):
    frame = pd.DataFrame(captured["records"]).reindex(
        columns=list(field_map)
    ).rename(columns=field_map)

    for column in field_map.values():
        frame[column] = (
            frame[column].astype("string").str.strip().replace("", pd.NA)
        )

    valid_store = frame["store_number"].str.fullmatch(
        r"[0-9]{1,4}", na=False
    )
    frame["store_number"] = (
        frame["store_number"].where(valid_store).str.zfill(4)
    )
    frame["store_zip"] = frame["store_zip"].where(
        frame["store_zip"].str.fullmatch(r"[0-9]{5}", na=False)
    )
    frame["observed_at"] = pd.to_datetime(
        frame["scrapedAt"], errors="coerce", utc=True
    ).dt.tz_localize(None)
    frame["snapshot_id"] = snapshot_id
    frame["requested_store"] = captured["run_input"]["storeId"]
    frame["requested_zip"] = captured["run_input"]["zipCode"]
    return frame

snapshot_frame = pd.concat(
    [normalise(label, captured) for label, captured in captures.items()],
    ignore_index=True,
)

observation_key = [
    "snapshot_id", "requested_store", "requested_zip", "item_id"
]
if snapshot_frame["item_id"].isna().any():
    raise ValueError("Missing itemId: product identity needs review")
if snapshot_frame.duplicated(observation_key).any():
    raise ValueError("Duplicate observation key: refusing an ambiguous join")

manifest_frame = pd.DataFrame([
    {
        "snapshot_id": label,
        "dataset_id": captured["dataset_id"],
        "input_json": json.dumps(captured["run_input"], sort_keys=True),
    }
    for label, captured in captures.items()
])

The Actor deduplicates products across queries and pages, but a snapshot boundary still deserves its own check. A later concatenation or ingestion retry can introduce duplicates even when the original collection didn't.

Dropping duplicates would hide which observation survived. Refusing the join leaves the disagreement available for inspection.

Store a value you can defend

A price parser should have an explicit boundary. Removing every non-numeric character can turn a range or explanatory label into a plausible amount. Plausible amounts are particularly efficient at reaching reports.

The parser below accepts plain numeric strings and dollar-prefixed amounts, including comma grouping. It stores decimal values and leaves unsupported formats null. It doesn't infer currency from the symbol.

This cell creates the snapshot tables and enforces the observation key:

con = duckdb.connect()
con.register("snapshot_frame", snapshot_frame)
con.register("manifest_frame", manifest_frame)

con.execute(r"""
CREATE TABLE snapshot_manifest AS
SELECT * FROM manifest_frame;

CREATE TABLE snapshots AS
SELECT *,
    CASE
        WHEN regexp_full_match(
            price_raw,
            '\$?\s*(?:[0-9]+|[0-9]{1,3}(?:,[0-9]{3})+)(?:\.[0-9]{1,4})?'
        )
        THEN TRY_CAST(
            replace(replace(price_raw, '$', ''), ',', '')
            AS DECIMAL(18,4)
        )
    END AS price
FROM snapshot_frame;

CREATE UNIQUE INDEX observation_key ON snapshots
(snapshot_id, requested_store, requested_zip, item_id);
""")

The notebook uses an in-memory database so it needs no local files. For a continuing tracker, retain these tables, the raw captures and their manifests in durable storage, using an immutable identifier for each snapshot.

Keep price_raw even after parsing. A null parsed price can then be traced to an absent value, an unsupported representation or an amount outside the chosen decimal range.

Missing price never becomes zero.

Classify the context before subtracting

A full outer join preserves products present in either snapshot. An inner join would quietly remove the membership changes from your audit.

For matched products, we use a conservative comparison policy: all returned context fields must be present and equal. priceScope stays an opaque label because we don't need to invent a vocabulary to compare it.

This policy may withhold usable observations when context is incomplete. That's an explicit cost of making the remaining comparisons easier to defend.

The query assigns context events before considering price values:

con.execute("""
CREATE TABLE snapshot_diff AS
WITH paired AS (
    SELECT b AS prior_row, a AS next_row
    FROM (
        SELECT * FROM snapshots WHERE snapshot_id = 'before'
    ) b
    FULL OUTER JOIN (
        SELECT * FROM snapshots WHERE snapshot_id = 'after'
    ) a
    USING (item_id, requested_store, requested_zip)
)
SELECT *,
    CASE
        WHEN prior_row.item_id IS NULL THEN 'entered_snapshot'
        WHEN next_row.item_id IS NULL THEN 'left_snapshot'
        WHEN prior_row.store_number IS NULL
          OR next_row.store_number IS NULL
          OR prior_row.store_zip IS NULL
          OR next_row.store_zip IS NULL
          OR prior_row.price_scope IS NULL
          OR next_row.price_scope IS NULL
          OR prior_row.currency IS NULL
          OR next_row.currency IS NULL
            THEN 'context_missing'
        WHEN prior_row.store_number IS DISTINCT FROM next_row.store_number
          OR prior_row.store_zip IS DISTINCT FROM next_row.store_zip
          OR prior_row.price_scope IS DISTINCT FROM next_row.price_scope
          OR prior_row.currency IS DISTINCT FROM next_row.currency
            THEN 'context_changed'
        WHEN prior_row.observed_at IS NULL
          OR next_row.observed_at IS NULL
          OR next_row.observed_at <= prior_row.observed_at
            THEN 'time_unusable'
        WHEN prior_row.price IS NULL OR next_row.price IS NULL
            THEN 'price_unusable'
        WHEN prior_row.price <> next_row.price THEN 'price_changed'
        ELSE 'price_unchanged'
    END AS event
FROM paired;
""")

The timestamp check prevents an equal or reversed observation time from becoming a forward price movement. Retaining product-level timestamps also avoids pretending that every product was observed simultaneously.

Notice that a context transition takes precedence even when the price is unchanged. Otherwise, an unchanged amount could conceal a break in the series.

Separate price events from catalogue noise

The worked example uses Lowe's Price, Stock & Specification Scraper, but the analytical boundary applies anywhere a requested location can differ from the location supplying an observation.

Now produce separate frames for comparable price movements, context reviews and event counts:

price_changes = con.sql("""
SELECT
    prior_row.item_id AS item_id,
    prior_row.requested_store AS requested_store,
    prior_row.store_number AS price_store,
    prior_row.currency AS currency,
    prior_row.observed_at AS before_at,
    next_row.observed_at AS after_at,
    prior_row.price AS before_price,
    next_row.price AS after_price,
    next_row.price - prior_row.price AS price_delta
FROM snapshot_diff
WHERE event = 'price_changed'
""").df()

context_review = con.sql("""
SELECT *
FROM snapshot_diff
WHERE event IN ('context_missing', 'context_changed')
""").df()

event_counts = con.sql("""
SELECT event, count(*) AS product_observations
FROM snapshot_diff
GROUP BY event
ORDER BY event
""").df()

Those counts belong beside the price chart. Without them, a shrinking comparable set can look like a quieter market. With them, you can distinguish fewer price movements from fewer observations eligible for comparison.

For a store-switch review, inspect the before and after store_number values inside context_review. The category also catches scope, currency and returned ZIP changes, so its total should never be labelled solely as store switches.

Other fields need their own interpretation. A changed position describes placement, not a product price event. A changed title may warrant catalogue review without breaking identity. Differences in stock or fulfilment deserve separate measures rather than being treated as incidental row churn.

Likewise, left_snapshot means absent from that capture. It doesn't establish that a product was withdrawn from sale. Search and category discovery return candidate sets, so membership changes in those modes carry an additional coverage question.

The gaps between observations stay gaps

Matching product and price context makes subtraction interpretable. It doesn't establish why the amount changed, which price a customer paid, or what happened between captures.

A returned-store transition also doesn't reveal why Lowe's selected that store. The records preserve the transition, not its cause.

Your report can defend an observed price difference within a retained context. When that context changes, the useful finding is the break itself.

A tidy line chart shouldn't get to choose the store.