How this workbook was built.

Every observation comes from Amazon's product catalog via the Keepa API, the same upstream source used by most major reseller research tools. We never scrape Amazon directly.

3a. Fields used.

Keepa supplies the following on a per-ASIN basis:

Each category is sliced into a fixed grid of 28 cells (up to 9 BSR bands by up to 4 drops bands). Every reseller question reduces to: which cell does my product land in?

4a. BSR bands.

Nine bands covering ranks 1 to 300,000. Ranks above 300K are outside scope.

Every sample passes through six cleaning gates, then through a per-cell aggregator that trims outliers, removes suspect variation-parent rollups, computes percentiles, detects bimodality, and assigns confidence and floor flags.

5a. Six cleaning gates (stage 1).

Rows that fail any gate are dropped with a logged reason:

1. ASIN present.
2. Category ID present and resolvable in the cat tree.
3. Cell key parses (BSR band plus drops band).
4. BSR present.
5. Listing age determinable (either isYoung or listedSinceDays).
6. Category exists in our loaded cat tree.

This step also bins each surviving product into its cell using Keepa-aligned band boundaries.

5b. Top-5% trim plus rollup-suspect filter (stage 2).

For each (category by cell) tuple we compute percentiles, but only after two defenses.

Trim. Sort the cell's monthlySold values, drop the highest 5%, then compute p10 / p25 / p50 / p75 on what remains. The trim defends against Amazon's variation-parent rollup problem. p95 and max are computed on the untrimmed data so true outliers stay visible.

Rollup-suspect filter (k=5). Variation parents can aggregate child sales into a single monthlySold value while still reporting parent-level drops. A parent with 80,000 monthly sold and only 50 drops shows a per-drop ratio (1,600) two orders of magnitude above the category typical, which is often 3 to 10 per drop. Without filtering, a handful of these parents distort every cell they land in.

The filter:

1. Per category, compute the median of monthlySold ÷ drops30 across qualifying samples (where drops30 ≥ 5 and monthlySold ≥ 100).
2. Mark any sample whose ratio exceeds 5× that median as rollup-suspected.
3. Drop it from the cell aggregator.

12,074 samples dropped on US (5.6% of cleaned input), 3,667 on CA (4.0%). Affected cells get an N parents removed annotation when N is 2 or more.

5c. Bimodality detection.

Some cells contain two distinct sales populations (a slot where half the products sell ~100/mo and half sell ~1,000/mo, for instance). A simple median misleads in those cases.

The algorithm:

• Bin monthlySold values into the 29 Keepa-aligned tier buckets: 50, 100, 200, ..., 1K, 2K, ..., 100K.
• Require at least 30 total samples in the cell.
• Find local-maxima buckets with at least 3 samples and at least as many as their immediate neighbours.
• Take the two strongest peaks, order low to high.
• Reject if peaks are less than 2 bucket indices apart.
• Reject if the smallest valley count is not at least half the smaller peak count.

Cells passing every check are flagged bimodal and earn a two tiers (X vs Y) note showing the two peak bucket centres.

1,137 of 6,541 US cells flagged bimodal (17.4%). 140 of 4,345 CA cells flagged bimodal (3.2%).

5d. Floor share, two-tier flag.

Products selling between roughly 25 and 75 per month all report as 50 (the badge minimum). A cell where many products sit at this floor has a structurally compressed distribution and a misleadingly low typical reading.

For each cell we compute floor_share = fraction of samples whose monthlySold equals exactly 50, then assign:

• Hard floor (red badge, "most barely badged"): floor_share ≥ 0.50. Most products at the badge minimum, so the true worst case is below what we can measure.
• Soft floor (amber badge, displays percentage): 0.30 ≤ floor_share < 0.50. Notable share at floor, typical reading is pulled down.
• No badge: below 0.30.

Two cross-category multipliers live in the workbook on dedicated sheets: sales per rank drop, and sales per added review. Both are computed per category, not extrapolated from any single rule.

7a. Sales per Drop.

For each category, we compute the p25 / p50 / p75 of monthlySold ÷ drops30 across qualifying samples (drops30 ≥ 5 AND monthlySold ≥ 100). Categories with fewer than 30 qualifying samples are excluded: the ratio percentiles would be too noisy.

Survivors: 259 categories on US, 182 on CA, 441 combined.

The Sales per Drop sheet gives a single per-category multiplier ("each 30-day rank drop in this category typically represents N sales"). Spread (p75 ÷ p25) flags wide categories where the median should be used with caveat.

7b. Sales per Review.

Same approach using monthlySold ÷ reviewsAdded30, with qualifying rule reviewsAdded30 ≥ 3 and same n ≥ 30 minimum. The per-category p50 powers the "Reviews per month" cross-check on every category sheet: type in a review count, the workbook converts to an estimated sales count using the category's measured rate.

Categories without enough qualifying data fall back to a default 5% review rate (~20 sales per review). The irony is intentional: when the data is too thin to support a category-specific number, the workbook honestly defaults to the conventional heuristic rather than inventing a calibrated-looking one. Cells affected are still Thin-flagged.

Implied review rate per category = 100 ÷ p50%. Cumulative review count is never used: it is pooled across variation families on Amazon's product page and varies wildly by category, which would mislead more than inform.

Three guards keep the workbook honest: a tier classification that requires sample support, two category-level filters that decide which sheets ship, and a byte-reproducibility check on every build.

8a. Tier classification.

The cell's typical sales value (sold_p50, post-trim, post-rollup-filter) maps to one of six tiers, but only when confidence is Strong or Limited: