Product Identifiers and the Joins That Break
ASIN, ISBN, GTIN, SKU and internal codes rarely map one to one. Getting the join wrong produces a dataset that looks right and tracks the wrong thing.
Every rank observation has to attach to a product, and product identity across systems is messier than it appears.
The identifiers
Marketplace identifiers — assigned by the marketplace, unique within it, not portable between marketplaces.
GTIN family — UPC, EAN, ISBN. Assigned by the manufacturer or publisher, globally intended to be unique, and in practice reused, mistyped and occasionally duplicated.
ISBN specifically — different for each format and edition. Hardback, paperback, ebook and audio all carry different numbers, which is correct and is a constant source of joining errors.
Seller SKU — internal, arbitrary, not visible externally.
Internal product codes — yours, and not aligned to any of the above.
Where the joins break
One product, many marketplace listings. The same item sold by several sellers, or listed multiple times, each with its own identifier and its own rank.
One marketplace listing, many variants. Sizes and colours under one parent. Rank may be aggregated or per-variant depending on the marketplace and the category.
One GTIN, several listings. Duplicate listings for the same physical product are common, and they split the sales and therefore the rank.
One title, several ISBNs. Tracking a book by one format's ISBN misses the others, and the answer to "how is the book selling" depends on which you chose.
Bundles and multipacks carry their own identifiers and compete with the single item.
Relisting. A product removed and relisted receives a new identifier, and its history is orphaned. This is the most common cause of a series that simply stops.
Consequences for tracking
Record every identifier you can capture, not just the one you track by. Marketplace identifier, GTIN, title, brand, format, variant.
Track by the marketplace identifier, because that is what rank attaches to.
Maintain a mapping table from your internal product to all its marketplace identifiers, across marketplaces and formats.
Expect the mapping to change. Listings are created, merged, split and removed continuously.
Reconcile periodically. A monthly check that every tracked identifier still resolves to the product you think it is catches relisting and merges before the series is corrupted.
The silent failure
An identifier that has been reassigned or merged continues to return data. The series looks continuous. It is now tracking a different product.
Capture the title and brand with every observation, and alert on changes. A title change on a tracked identifier is either a listing edit or a different product, and both need investigating.
A sudden discontinuity in a series should always be checked against the identifier before being interpreted as a market event.
Aggregating across formats
For books and for products with multiple formats, the question of what to aggregate is a decision rather than a technicality.
Aggregating ranks is not possible — ranks cannot be added.
Aggregating estimated units is possible and inherits all the estimation error, multiplied across formats.
Reporting per format is usually more honest and more useful, because the formats behave differently and the audience frequently cares about one of them.
State what you aggregated. "Sales rank for the paperback edition" and "estimated combined units across four formats" are different claims and both get labelled "sales" in most reports.
The minimum record
For each observation, store: marketplace, marketplace identifier, rank type, rank value, timestamp, captured title, captured price, captured availability, and captured category path.
Nine fields. They cost almost nothing to store and they are what makes the dataset repairable when something turns out to have been wrong — which it will, and usually after a year of collection.
The mapping table
The reference that holds a multi-marketplace, multi-format tracking system together.
One row per marketplace listing, with: internal product identifier, marketplace, marketplace identifier, GTIN where known, format or variant, pack size, listing structure, and status.
Status matters: active, superseded, merged, removed. A removed listing's history stays valuable and must not be silently dropped.
Record when each mapping was verified, and re-verify quarterly.
Where a listing is superseded, link to its replacement, so the history can be chained deliberately rather than by assumption.
This table is small and it is the thing that breaks silently. A product relisted six months ago, still mapped to a dead identifier, produces a series that simply stops and nobody notices until someone asks why the chart ends in March.