Case study 09
From a handful of seed rows to a price snapshot you can defend
A new product needed to know where it sat in its price range before a sales conversation. A handful of seed rows became 100 to 150 checked rows across 30 to 40 competitors, every row tied to a store, a date and a source, checked twice and frozen as a dated snapshot.
The brief was a short seed sheet, a walkthrough recording and an email. The hard part is that web prices mislead in quiet ways: a sale typed in as the regular price, a member price that looks like a discount, a related item's price beside the real one, page text that describes the package wrong.
I wrote the rules first, then filled the sheet.
Two tiers. Direct competitors a shopper would swap for, and benchmarks on the same shelf. The account lead confirmed the split first.
The store's own pages only. One row per product per store, read from the store's own product page, never a reseller or a delivery marketplace. Store categories and one location per region were fixed up front.
Regular versus sale. The regular price is what the store charges. A true store sale goes only in the discount column. Coupons, member prices and "compare at" prices are neither. A struck-through price shown next to a member price is the regular price. Prices from related items on the same page do not count.
Competitive Price Sheet, Prices tab: Ridgeline Mix Classic 10 oz, plus one coupon row
| Row | Shown | Struck | Label | Reg. | Disc. |
|---|---|---|---|---|---|
| Store 1 | $4.29 | none | $4.29 | ||
| Store 2 | $4.49 | $5.29 | Sale | $5.29 | $4.49 |
| Store 3 | $4.49 | $5.21 | Compare at | $4.49 | |
| Store 4 | $3.79 | $4.29 | Member price | $4.29 | |
| Store 3 (Pocket Trail) | $4.79 | Coupon | $4.79 |
Competitive Price Sheet, Summary tab (direct tier)
| Store category | Rows | Avg price | Per oz | Sample |
|---|---|---|---|---|
| Mass | 8 | $4.45 | $0.48 | solid |
| Specialty | 10 | $5.76 | $0.57 | solid |
| Online | 8 | $4.64 | $0.50 | solid |
| Direct | 5 | $5.73 | $0.60 | solid |
| Club | 0 | $0.34 | no rows in band | |
| All categories | 31 | $5.13 | $0.51 | solid |
| Hearth Oat Mix 10 oz | $5.29 | $0.53 | +3.0% per oz |
Size from the package photo. Size, count and format come from the front of the pack. Page text is the fallback, not the source.
Store and date on every row. Each row carries the store, location, date checked and a clickable source link. Every miss goes to a research notes tab with the reason, so a gap is a finding, not a blank.
Two checks, then a freeze. A second reviewer re-opened every source and marked each row OK, corrected or unverified, with evidence. Then an independent acceptance audit by people who had not seen the build checked every row against every column. Errors were fixed, drift was logged, disputes went to the rule owner, and the file was frozen and dated.
Drift is logged, not fixed. Online prices change by assigned store and by day. A different price read later at a different store is drift, noted and left alone. Only rows that could not have been right when read get corrected: a wrong size, a sale entered as regular, a related item's price.
Click any image to see it full size. Every file here is rebuilt with invented data.
The results, in bands. 100 to 150 checked rows across 30 to 40 competitors, each linked to its source and checked against the package photo, and a summary by store category and tier with the client's product beside it. Averages on fewer than three rows are labeled directional.
- The sheet went through several rounds of narrow re-checks, and each one found something new. I replaced them with one independent audit against a checklist written in advance, then froze the file.
- A re-check read page text instead of the package photo and changed a value that was correct. I re-ran it from the images. Checkers now get a flag column, never the answer column.
- The audit flagged a competitor as sitting in the wrong tier. The tier came from the brief, so the flag went to the account lead instead of into the file, and the original call stood.
- At first no rule separated "wrong when read" from "changed since". Every re-check turned into fixes until the drift rule was written down.
Competitive Price Sheet (demo workbook)The Shelf Price NormalizerThe Price Check playbook (PDF)The Final Check playbook (PDF)A snapshot is not a mistakeLive demos of the working filesPart of the work in case study 07