List 40 to 60 items in one sheet with cost, shelf price, weekly units and competitor shelf prices. Leave the key value items alone, raise or cut a dozen mid-visibility items by one small step against matched control items, run the change for several weeks, and let AI check the margin arithmetic and flag noise while you decide.
Two warnings come first. AI cannot predict how your shoppers will react to a price; it can read your sheet, do the arithmetic and point at patterns in your own sales. And a small store's weekly sales are noisy enough that most casual price tests measure luck, which is why the test design matters more than the tool. Every number below is invented for teaching, so the example shows a method and proves nothing about your customers.
One line must never be crossed: price-fixing. Looking at a competitor's public shelf prices is ordinary research. Agreeing, hinting at or swapping future prices with a competitor, directly or through a supplier or a messaging group, can break competition law in many places. Your test changes your own prices using your own costs and your own sales, and nothing else. If you are unsure where a line sits, ask a competition-law adviser before you act.
Give every item a role before changing a single price
Not every item should be tested. Shoppers carry a rough price list in their heads for a small set of staples, and those key value items (KVIs) shape how they judge your whole store. One point-of-sale vendor's guide describes them as the staples on every shopper's list, such as bread, milk, eggs and bananas, and estimates that for most independent grocers they make up 5 to 15% of items. A mistake on a KVI costs more than a mistake anywhere else, so the test starts by sorting the range.
Find your own KVIs in your till data. Sort the last 13 weeks by units sold, and by the share of baskets each item appears in. If milk sells 410 times in a week and the store rings up 4,100 baskets, milk sits in about one basket in ten. Then add the items staff say customers mention when a price moves. That gives you a list to freeze.
| Role | Examples | Treatment in the test |
|---|---|---|
| Key value items | Milk, eggs, bread, loose bananas | Freeze. Watch competitors every week; never probe |
| Traffic builders | Items you promote to bring people in, such as a multipack or a seasonal staple | Leave to your promotions plan and keep out of the test |
| Mid-visibility lines | Olive oil, pasta sauce, dishwasher tablets, kitchen roll | Test here: bought regularly, rarely remembered by price |
| Convenience and impulse | Items by the till, drinks from the chiller | Test cautiously; baskets barely notice, but check the margin |
| Own-label | Store-brand versions of staples | Test the gap to the brand and to competitors' own-label |
Pick the twelve test items from the mid-visibility, convenience and own-label rows, then choose a matched control item for each: same category, similar price and weekly units, not on promotion. Controls tell you what happens to sales when you do nothing, which is the comparison that makes the whole test readable. Choose the pairs from the sheet, sorted by category and units, not by eye.
The sheet layout an AI assistant can read without guessing
AI assistants read tables well and guess badly. Most failures come from a sheet that made sense to its owner: merged cells, prices with currency symbols, and meaning carried by cell colour. Set the workbook up so that nothing needs guessing.
- One header row and one item per row, with no merged cells, no blank rows and no totals mixed into the data.
- One fact per cell. A price is a number such as 5.50, not "$5.50 ea" or "5.50/500 ml".
- Pack size and unit of measure in their own columns, so a per-100 g or per-litre price can be calculated.
- Dates in a fixed format such as 2026-10-29, never "last Tuesday".
- Anything you would mark with a colour goes in a column instead (role, group), because assistants read values, not colours.
- A stable item_id that never changes even when the description does.
- A second tab called event_log (date, item_id or category, event) for stockouts, promotions, late deliveries, label errors, holidays and bad weather, and a third called data_dictionary that says what each column means and where it came from.
The first tab, with eight sample rows, looks like this:
| item_id | item | role | group | unit_cost | shelf_price | comp_a | comp_b | comp_c | avg_weekly_units |
|---|---|---|---|---|---|---|---|---|---|
| 1001 | Whole milk 2 L | KVI | Hold | 1.62 | 2.09 | 2.05 | 1.99 | 2.19 | 410 |
| 1002 | Bananas, loose, per kg | KVI | Hold | 0.88 | 1.39 | 1.35 | 1.29 | 1.45 | 290 |
| 2014 | Extra virgin olive oil 500 ml | Mid-visibility | Test | 4.20 | 5.50 | 5.79 | 5.49 | 5.99 | 62 |
| 2015 | Pasta sauce 400 g | Mid-visibility | Test | 1.60 | 2.49 | 2.59 | 2.49 | 2.79 | 118 |
| 2016 | Pasta sauce 400 g, own label | Own-label | Control | 1.05 | 1.79 | 1.69 | 1.85 | 1.79 | 96 |
| 3021 | Yoghurt 6 x 100 g | Mid-visibility | Hold | 3.10 | 4.29 | 4.49 | 4.19 | 4.59 | 74 |
| 4005 | Dishwasher tablets, 30 | Mid-visibility | Test | 4.90 | 6.49 | 6.99 | 6.29 | 6.79 | 41 |
| 4006 | Kitchen roll, 2 rolls | Mid-visibility | Control | 1.95 | 2.79 | 2.99 | 2.85 | 3.19 | 88 |
A separate weekly_sales tab holds one row per item per week (item_id, week_start, units, price). If your till exports item sales by week as a CSV, use it; if the assistant misreads formulas in a workbook, export each tab to CSV instead, which strips the formulas and leaves the values. A short companion read on the same job is the tutorial on checking your margins product by product with AI, and for the mechanics of getting an assistant to work with a workbook there is the ChatGPT and Excel guide.
A competitor shelf survey in one morning, and where it stops
The survey gives your test its context. Choose the three competitors you actually lose shoppers to and the 40 items from your sheet: ten KVIs plus the test and control items. Visit all three in the same two-hour window, because shelf prices can change from day to day. For each item record the shelf price, the pack size, the unit price (per 100 g or per litre), and whether the price is a promotion or needs a loyalty card. Some shops do not allow photography, so check the rules, and where a competitor publishes prices on its website, use that and note the date.
An assistant can turn a folder of shelf-label photos into rows. The instruction needs to forbid guessing:
Attached are photos of shelf labels from three shops (comp_a, comp_b, comp_c). For each label, return one CSV row: shop, item description as printed, pack size, shelf price, unit price, promotion flag (yes/no), and the item_id from my list if it clearly matches. If any digit is not clearly legible, write UNREADABLE rather than guessing. Never put a unit price in the shelf price column. List labels you could not match to my items separately.Check at least one row in ten against the photo. Look for the usual suspects: a 3 read as an 8, a unit price copied into the shelf price column, and a multi-buy price taken as the single price. Tell the assistant which kind you found and ask it to re-check the rest of that kind.
From the cleaned survey, calculate a price index: your price divided by the average competitor price, times 100. Four items from the sample sheet:
| Item | Our price | Competitor average | Price index |
|---|---|---|---|
| Olive oil 500 ml | 5.50 | 5.76 | 95.5 |
| Pasta sauce 400 g | 2.49 | 2.62 | 94.9 |
| Dishwasher tablets, 30 | 6.49 | 6.69 | 97.0 |
| Whole milk 2 L | 2.09 | 2.08 | 100.6 |
A low index on a mid-visibility line means some headroom for a small rise. An index above 100 on a KVI means you are exposed, and a match is worth considering. Pick your own threshold for "headroom"; 97 or below is used in this illustration. The survey tells you what competitors charge today. It says nothing about what they will charge tomorrow, and you should never try to find out by asking them. Keeping the line clear is easier with the two lists side by side.
| Ordinary market research | Crossing the line |
|---|---|
| Reading shelf labels and public flyers | Asking a competitor's staff about future prices |
| Checking prices on public websites | Swapping price lists or plans |
| Buying a basket and keeping the receipt | Agreeing not to undercut, or to move together |
| Dating every record and noting promotions | Discussing prices in trade-group meetings or messaging groups |
| Changing your own prices on your own costs and sales | Using a shared supplier to pass price signals |
Rules differ by place and the penalties can be severe, so treat that table as orientation rather than legal advice. A wider read on tracking rival prices without crossing it is in the tutorial on tracking competitors' prices and offers with AI.
The margin check: how far volume can fall before a rise loses money
Before any test, work out what each change has to achieve. If an item with unit cost C moves from an old price P0 to a new price P1, gross profit stays level when units change by (P0 minus C) divided by (P1 minus C), minus 1. In a sheet with cost in column B, old price in C and new price in D, that is =(C2-B2)/(D2-B2)-1. A negative answer is the fall in units you can absorb; a positive one is the rise in units you need.
| Item | Cost | Old price | New price | Unit margin, old to new | Units can fall by, or must rise by |
|---|---|---|---|---|---|
| Olive oil 500 ml | 4.20 | 5.50 | 5.72 (+4.0%) | 1.30 to 1.52 | Can fall 14.5% |
| Pasta sauce 400 g | 1.60 | 2.49 | 2.59 (+4.0%) | 0.89 to 0.99 | Can fall 10.1% |
| Dishwasher tablets, 30 | 4.90 | 6.49 | 6.75 (+4.0%) | 1.59 to 1.85 | Can fall 14.1% |
| Yoghurt 6 x 100 g, a cut for contrast | 3.10 | 4.29 | 4.09 (-4.7%) | 1.19 to 0.99 | Must rise 20.2% |
Two lessons sit in that table. A thinner margin leaves less room: pasta sauce, with 89 cents of margin, can lose about 10% of its units on a 4% rise, while olive oil, with $1.30, can lose about 14.5%. And cuts are expensive: a 4.7% cut on the yoghurt has to bring in more than a fifth more units just to stand still. Work in gross profit dollars, not margin percentages, because a percentage can look healthy while the dollars shrink. Add the costs that move with volume, such as waste and promotion funding, if they matter on the line.
Why a small store cannot read single-item results
Units sold behave a little like random counts. For counts of that kind, the typical chance wobble (one standard deviation) is about the square root of the average. An item that sells 100 units a week wobbles by about 10 from luck alone, which is why a 5% change of five units is invisible. The table sizes the problem for a before-and-after comparison with four weeks on each side.
| Average units a week | Units in four weeks | Smallest before-and-after change you can separate from chance |
|---|---|---|
| 25 | 100 | about 28% |
| 100 | 400 | about 14% |
| 400 | 1,600 | about 7% |
| 1,200 (twelve items pooled) | 4,800 | about 4% |
The last row is the point. Twelve items pooled can show a change of around 4%, while a single item selling 100 a week cannot show anything under about 14%, and one selling 25 a week nothing under 28%. So read the test at group level, twelve test items against twelve controls, and never celebrate or panic about one item. The figures use a rough rule (about two standard deviations), and real sales wobble more than random counts because of weather, pay days, promotions and stockouts. Treat the table as a best case, and use the event log to remove weeks that were distorted.
A worked test: twelve items over eight weeks
In this illustration, weeks 1 to 4 are the baseline and weeks 5 to 8 are the test. The twelve test items rise by about 4% to the nearest sensible price ending. The twelve controls and the KVIs stay as they are. Nothing else changes on those items: no promotions, no move to a different shelf.
| Group | Units a week, before | Units a week, after | Change |
|---|---|---|---|
| Test: twelve items, price up about 4% | 1,200 | 1,104 | -8.0% |
| Control: twelve items, unchanged | 1,180 | 1,160 | -1.7% |
| Net effect credited to the price rise | about -6.4% |
- Net effect. Test units fell 8.0% while the controls fell 1.7%, so the gap, about 6.4%, is what the rise cost. In a sheet:
=(C2/B2)/(C3/B3)-1. That is roughly a 1.6% fall in units for each 1% on the price. - Break-even allowance. The test items average a cost of $2.25 and a price of $3.00, rising to $3.12. Margin goes from $0.75 to $0.87, so units can fall by 0.75 divided by 0.87 minus 1, which is 13.8%, before gross profit drops.
- Gross profit. Before: 1,200 units at $0.75 is $900 a week. After: 1,104 units at $0.87 is about $960. Had nothing changed, the test group would have sold about 1,180 units, worth $885, so the rise adds about $76 a week. Revenue, meanwhile, fell from $3,600 to about $3,444. A test judged on revenue would call this a failure; judged on gross profit it is a gain.
- Noise. Add up one divided by each of the four four-week unit totals (4,800, 4,416, 4,720 and 4,640), then take the square root. In a sheet:
=SQRT(1/B5+1/C5+1/D5+1/E5). The answer is about 2.9%, so the net effect of -6.4% could plausibly sit anywhere from about -0.5% to -12.3% (two standard deviations either side). - Decision test. Compare the gloomy end of that range with the break-even allowance. A 12.3% loss is inside 13.8%, so even the pessimistic reading leaves gross profit marginally ahead (about $900 against $885). The rise does not lose money, but the gap between the gloomy end and the allowance is only 1.5 percentage points, which the outcomes table below classes as a thin win.
- Basket check. Baskets went from 4,100 to 4,070 a week (-0.7%) and milk units from 410 to 406 (-1.0%). Both are within chance for counts of that size, so there is no sign that the price rise damaged the store's price image.
None of that says anything about your own shoppers. What carries over is the reading order: revenue, margin, noise and baskets, looked at together before anyone calls the result.
Prompts that make AI check the work instead of inventing it
Use two prompts, in this order. The first checks the data and nothing else; analysis on dirty data wastes the assistant's effort and your trust.
You have my price-test workbook (tabs: items, weekly_sales, event_log, data_dictionary). Before any analysis, report only problems: missing costs or prices, items with a shelf price below cost, duplicate item_ids, test items with no matched control, missing weeks for any item, prices that changed during the baseline weeks, and any item whose weekly units vary by more than three times between weeks. List each problem with the item_id. Do not fix anything and do not analyse results yet.A reply on a sheet like the one above might flag the following (illustrative):
- Item 3021 has no weekly_sales row for week 3 and no event_log entry, so a stockout is possible.
- Item 4005 shows two prices in week 2 (6.49 and 6.29), which suggests an unlogged promotion.
- Item 2014 is a test item with no matched control in the same category.
- Item 1002 varies more than threefold between week 1 and week 5 (loose bananas, a Hold item, so probably seasonal).
You fix these in the sheet, log the stockout or the promotion, and decide whether to keep or drop the affected items. Then the analysis prompt:
Using the cleaned workbook: weeks 1-4 are baseline and weeks 5-8 are the test. (1) For the Test and Control groups, total units per week before and after and the % change. (2) The net effect: test % change versus control % change, as a ratio. (3) Weekly gross profit before and after for each group, weighting by units and using unit_cost and the weekly price. (4) The break-even change in units for the Test group: old margin divided by new margin, minus 1. (5) Recalculate (1) to (4) excluding any week or item named in event_log. (6) Using the square root of the unit totals, say which differences are inside normal chance variation. Show every formula. Do not recommend prices.Suppose the reply gets the unit arithmetic right. A typical slip then appears in step 3, where the assistant averages the item margin percentages instead of weighting by units, and reports a group margin of 26.3% where the weighted figure is 25.0%. You catch it because you asked for formulas, and you ask it to redo the step weighted by units. Another slip to look for is a reply that ignores the exclusions in step 5. Say what you found and what you want, and have it redo only that step.
Any assistant that accepts spreadsheet files will do. Claude accepts CSV and XLSX uploads, ChatGPT accepts file uploads with daily limits on the free plan, and Excel and Google Sheets have AI panes of their own, though what they offer depends on your plan. Your costs and sales are commercially sensitive, so use a business plan, where content is not used for training by default, or switch off the training setting on a personal plan, and use item IDs rather than supplier names.
Rules that keep a price test honest and lawful
- Labels match the till. Change the shelf-edge label and the till price on the same day, then check both again midweek. A mismatch misleads shoppers and wrecks the data.
- No invented "was" prices. Do not raise a price and then mark it down to create a "sale". Rules on reference prices vary by place, so check yours before any promotion around a test.
- One price for every shopper at the shelf. Do not test different prices on different people using loyalty data without advice.
- No contact with competitors about prices. Not before, during or after.
- Log events. Stockouts, promotions, delivery delays, bad weather and holidays go in the event log as they happen, not from memory.
- Tell staff. Shoppers notice price moves and ask about them; staff should know a test is running and what to say.
- Check price rules. Some categories carry price rules in some places. Take any such item out of the test.
- A stop rule. Revert after two weeks if the test group's net volume loss already exceeds the break-even allowance.
Reading the result: five outcomes and what to do
| Outcome | What it looks like | What to do |
|---|---|---|
| Clear win | Gross profit up, and even the gloomy end of the noise range sits several points inside the allowance; baskets and KVI units flat | Roll out to similar items in stages and repeat the shelf survey |
| Thin win | Gross profit up, but the gloomy end of the noise range comes within a point or two of the allowance, or passes it | Extend four more weeks or pool more items before rolling out |
| Flat | Gross profit about the same | Revert, or try half the step on a fresh group |
| Loss | Net volume loss beyond the allowance | Revert at once and note the type of item for next time |
| Collateral damage | Baskets or KVI units fall while the test runs | Revert, and treat it as a warning about price perception |
Whatever the outcome, run the next test in a different season before treating any result as a rule, because a price that works in one month can behave differently in another. For the wider picture of where pricing sits among the other jobs AI can do in a small store, read how AI can help a small supermarket with pricing, stock and posters. And if you would rather set the test up with someone looking at your till exports, that is the kind of job my AI implementation consultation covers.
Questions about testing prices in a small store
Should key value items ever be in a test?
Not in a first test. They carry your store's price image, so a mistake costs more there than anywhere else. If a competitor undercuts you on one, matching is usually a safer response than probing. If you do test one later, use a small step, a short window, a daily watch on baskets and a firm stop rule.
How many weeks should a test run?
Long enough that the noise band is narrower than your break-even allowance. For a pooled group selling around 1,000 units a week, four weeks before and four weeks after is a sensible start. Avoid holidays and pay-day spikes, and change nothing else on those items, such as promotions or placement, during the window.
Can AI set the prices for me?
It can propose steps, calculate margins and flag odd data, but it does not know your shoppers, your supplier terms or your obligations on price marking. Use it as analyst and checker, and keep the decision, and the responsibility for it, with a person.
Do I need electronic shelf labels to test prices?
No. Paper labels work if the label and the till change together on the same day and someone checks both. Electronic labels make changes faster and reduce mismatches, but they are an investment to consider after you know that testing pays in your store.
Further reads
- AI Pricing for Grocery Stores: Can It Set Short-Date Markdowns? — Short-date markdowns are a separate pricing job with their own rules.
- AI for Convenience Stores: A Weekly Routine for Reorders and Rotas — A weekly routine that puts price checks next to reorders.
- Restaurant Menu Price Increase: Test Each Change by Dish With AI — The same test-and-control idea applied to dishes on a menu.
- AI Poster Maker for a Supermarket: Weekly Offers in Canva — Turning promotions into weekly offer posters.
- AI Tools for a Grocery Store: Supplier Invoices and Shelf Prices — One week of supplier invoices in a one-shop grocery: read them with AI, check the totals, flag cost changes and set shelf prices that hold your margin.
- Car Wash Pricing Strategy: Can AI Set Your Tiers and Memberships? — Cost per wash, a three-tier ladder, a membership break-even sheet and price tests, with AI building the sheets and you keeping the final say on every price.
- How AI Helps Marketing in a Small Supermarket: 5 Jobs and the Limits — Offers emails, social posts, local-event content, review replies and loyalty messages: what AI drafts well in a small supermarket and what you must still check.
- AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.
Sources: IT Retail's guide to key value items (a point-of-sale vendor, so read it as a vendor view); the reference article on the Poisson distribution for the square-root rule of chance variation; vendor help pages for spreadsheet upload and data-handling points. Every price, cost and sales figure in the worked examples is invented for teaching.