Competitor Price Analysis Template: Free Price & Offer Workbook + AI Prompt
A competitor price analysis template is a spreadsheet that compares your prices with competitors' prices, product by product. This free workbook compares landed price (price plus shipping) for up to 500 SKUs against 5 competitors, scores 88 offer points, and returns a price band, a suggested price and a feed label for every SKU.
Built by Abdul Rauf Ali, founder of Just Lead Market. Last updated: November 20, 2026.
Or have us run it on your store for $99
Google Sheet, Excel file and a 2-page cheat sheet, sent to your inbox. The full method is on this page, free to read.
What is in the free competitor price analysis template
A table of competitor prices is only the start. This competitor pricing analysis template ends in decisions: what to reprice, what to promote, what to pull from ads, and which offer gaps cost you the sale.
| What you get | What it is | Format |
|---|---|---|
| Price & Offer Intelligence Workbook | 3 tabs: Read me, Price Matrix, Offer Matrix. 500 SKU rows, 105 columns, 5 competitor blocks, 23,050 formulas already in place | Google Sheets and Excel (.xlsx) |
| AI prompt | About 1,000 words. Fills both matrices in Claude, ChatGPT or Gemini from your product export | On this page and inside the workbook |
| Cheat sheet | The formulas, bands, verdicts, the 15 Critical checks and the run order on 2 pages | |
| Done-for-you run (optional) | We run it on your store: up to 25 SKUs and up to 5 competitors | $99 |
One run returns:
- Every SKU in one of four price bands against the lowest credible competitor landed price.
- A suggested price for each identical match, with a margin check against your floor.
- A feed label per SKU, so campaigns can be split by price position.
- An offer gap list ranked Critical, High and Standard.
- A 40-metric scorecard that fills itself from the two matrices.
What is free to read and what is emailed. Everything you need to run the method is on this page: the rules, the worked example, all 88 offer checks and the full AI prompt. The formatted Google Sheet, the Excel file and the PDF cheat sheet are emailed. Building 23,050 formulas by hand is the slow part, and that part is done.
Compare landed price, not shelf price
Shoppers do not compare shelf prices. They compare what it costs to get the product to their door. To compare competitor prices fairly, the workbook uses three numbers.
- Effective price = the sale price if a sale is live today, otherwise the list price.
- Landed price = effective price + shipping for one unit.
- Price index = your landed price / the lowest competitor landed price.
A $44.99 product with $6.95 shipping lands at $51.94. A $49.00 product that ships free lands at $49.00. The lower shelf price is the more expensive purchase.
Landed price here means the shopper's delivered price. It is not your landed cost, which is what a unit costs you to buy and bring in.
The four price bands and the feed label each one gets
| Price index | Band | Feed label | First move |
|---|---|---|---|
| 1.00 or below | Cheapest or equal | price_cheapest | Promote. Give it budget and a price asset |
| 1.00 to 1.05 | Within 5% | price_par | Hold, if your offer is at par or better |
| 1.05 to 1.15 | 5-15% higher | price_high | Reprice if the match is identical and the margin allows. Otherwise justify the price on the page |
| Above 1.15 | Over 15% higher | price_over | Reprice, reframe or exclude from ads. Never let it spend the budget of a cheapest SKU |
| Read, nothing comparable | No match | price_nomatch | A positioning finding, not missing data |
| Not read yet | Not checked | no label | Keep it out of the feed file until it is read |
The 5% and 15% cut-offs are a Just Lead Market working convention, not a research threshold. Treat them as a starting point.
Not checked is not the same as No match
A SKU with no competitor price shows Not checked until you enter the date you looked. After that it shows No match. Not checked means nobody has looked yet. No match means you looked and no competitor sells anything comparable. That second one is a finding you can use in your positioning.
Competitive price analysis example: one SKU, three stores
This is the worked example in row 4 of the Price Matrix: a 2 lb tub of organic whey protein. Your store ships free over $75 and charges a $7.95 flat rate below that.
| Your store | Competitor 1 | Competitor 2 | |
|---|---|---|---|
| Match type | Identical (same GTIN) | Equivalent (1.8 lb tub) | |
| List price | $59.99 | $54.99 | $49.00 |
| Sale price | $49.99 | $44.99 | none |
| Effective price | $49.99 | $44.99 | $49.00 |
| Shipping, 1 unit | $7.95 | $6.95 | $0.00 |
| Landed price | $57.94 | $51.94 | $49.00 |
| Price per lb | $25.00 | $22.50 | $27.22 |
Read 1: the index. On shelf price, competitor 1 looks like the store to beat at $44.99. On landed price, competitor 2 is the cheapest because it ships free. Your price index is $57.94 / $49.00 = 1.18. Band: Over 15% higher. Feed label: price_over.
Read 2: the unit. Competitor 2 sells a 1.8 lb tub. Per lb you charge $25.00, competitor 2 charges $27.22 and competitor 1 charges $22.50. The band is being set by a smaller pack. The real gap is against competitor 1, the identical product: you land 11.6% above it.
Read 3: the verdict. Reprice. Suggested price = competitor 1's landed price minus your shipping = $51.94 - $7.95 = $43.99. Your cost is $22.00, so the margin at $43.99 is 50%, above the 40% floor. Then show the price per lb on the product page, so the smaller tub stops looking cheaper.
After the change. Your landed price is $51.94. The index is 1.00 against the identical product and 1.06 against the smaller pack. Skip the per-unit check and you would have chased a price set by a smaller tub.
How to do a competitor price analysis: setup, then 6 steps
The Read me tab of the workbook carries the same steps. One rule runs through all of them: nothing changes in a live store, feed or ad account during the run. The output is a list of changes for the owner to approve.
- Step 0
Fill the store inputs. Enter your market and currency, one ZIP or postcode to use on every shipping check, your free shipping threshold and flat rate, your gross margin and your minimum acceptable margin. The formulas read these cells, so nothing computes correctly until they are yours.
- Step 1
Build the competitor set. List candidates from Google Ads Auction insights, the live Google results for 10 core queries and your own knowledge of the market. Pick up to 5 direct competitors: same product type, same market. Include the biggest sellers and the lowest-priced Google Shopping advertisers, because they are often different stores.
- Step 2
Load your catalog. Paste one row per advertised SKU or variant from your Merchant Center feed or your store's product export. Add 90-day ad cost, conversion value and conversions per item ID so the priority tier computes. Work Tier 1 first: the top 20% of SKUs by conversion value or ad cost.
- Step 3
Read competitor prices. For each SKU, find the identical or equivalent product at each competitor. Record the list price, the sale price, the units per item, the URL, the match type and the confidence. Open each competitor's cart or checkout once with nothing entered, to catch automatic discounts and the real shipping cost.
- Step 4
Read competitor offers. Fill the 88 Offer Matrix rows for your own store first, then for each competitor: homepage, top product page on mobile, cart, checkout up to the shipping step, and the policy pages. Write the number, the threshold or the exact wording. Never write a bare yes.
- Step 5
Set a verdict for every SKU. The workbook computes landed price, price index, band and feed label. Choose one of eight recommendations per SKU. Where the match is identical and you are 5% or more above, enter the suggested price and keep it only if the margin stays at or above your floor.
- Step 6
Hand over the changes and test them. Export three lists: price changes, feed labels and offer gaps. Nothing changes in a live store during the run. Apply each price change as a test with control SKUs left unchanged, and read the result after at least 14 days and 30 conversions.
The Price Matrix: one row per SKU, five competitors side by side
The Price Matrix is the competitor price comparison template inside the workbook: 500 SKU rows, 105 columns and 46 formulas per row. You fill the yellow cells. The gray cells compute.
| Columns | Block | What it holds |
|---|---|---|
| A to L | Your product | ID, feed item ID, title, brand, GTIN, MPN, product type, variant, unit label, units per item, list price, sale price |
| M to Q | Your landed price (formulas) | Effective price, discount %, shipping for 1 unit, landed price, price per unit |
| R to W | Cost and priority | Cost of goods, margin %, 90-day ad cost, conversion value and conversions, priority tier |
| X to Y | Merchant Center benchmark | Benchmark price from the price competitiveness report, and your price against it |
| Z to AN | Verdict | Lowest competitor landed price and who, price index, band, per-unit index, lowest Google Shopping seller, recommendation, suggested price, margin at that price, rationale, feed label, evidence tier, verified date, notes |
| AO to DA | Competitors 1 to 5 | 13 columns each: matched product, URL, match type, confidence, units per item, list, sale, effective, discount %, shipping, landed, per unit, your price against this competitor |
In the workbook the columns for your store are headed "Client". That means your store, or your client's store if you run this for someone else.
Match rules: compare product to product
| Match type | Meaning | Confidence |
|---|---|---|
| Identical | Same GTIN or MPN, or the same brand and model. For own-brand products: the same design, type and size. This match sets the reprice target | High |
| Equivalent | Same category and key specs (size, material, count, volume), compared per unit. Context only: justify on the page before any cut | Medium or Low |
| None | The competitor sells nothing comparable. Write None. Never force a match to fill the block, and never match on title words alone | n/a |
Evidence tiers: only dated reads carry a verdict
- T1: read during this run from the competitor's product page, product data, checkout or the Merchant Center price competitiveness report, with a URL and a date.
- T2: a price seen in a Google Shopping listing or a search ad.
- T3: a third-party estimate from an SEO tool or a price tracker.
- T4: someone told you, or you remember it from a past run. Context only.
Only T1 and T2 carry a verdict. A read older than 14 days is stale.
Priority tiers: where to start
- T1: in the top 20% of SKUs by 90-day conversion value or by 90-day ad cost.
- T2: in the top 50% by either.
- T3: the rest, including SKUs with no cost and no sales.
Ad cost counts because a SKU that spends and does not convert is the first suspect for a price problem. Match T1 fully before you touch T3.
The eight verdicts
| Verdict | When to use it |
|---|---|
| Hold | Cheapest or equal, or within 5% with a stronger offer |
| Reprice | Identical match, 5% or more above, and the margin at the suggested price is at or above your floor |
| Raise price (test) | Identical match and a price index below 0.90. Test a price toward an index of 0.97 on half of those SKUs and keep the other half as the control |
| Reframe | The margin forbids a cut. Change the offer instead: a bundle, free shipping on this SKU, a gift, a longer warranty |
| Justify on page | Equivalent match with a spec advantage the page does not show. Add a comparison table, spec callouts and the per-unit price |
| Promote | Cheapest or equal and a Tier 1 SKU |
| Exclude from ads | Over 15% higher, identical match, no margin room and no offer answer |
| Investigate | Low confidence or conflicting reads |
The suggested price rule
Suggested price = the lowest identical competitor's landed price minus your shipping, rounded to your price ending (.99, .95 or .00). Keep it only when the margin at that price is at or above your floor. If it is not, the verdict is Reframe or Exclude from ads, never Reprice.
The rule runs both ways. An identical match with an index below 0.90 means you are more than 10% cheaper than every credible competitor. That is margin you may be giving away, so the verdict is Raise price (test).
Every price change is a test: a start date, control SKUs left unchanged, and a readout after at least 14 days and 30 conversions.
The Offer Matrix: 88 checks that decide the sale when prices are close
Price is one line of the comparison a shopper makes. Shipping cost and speed, returns, warranty, reviews, payment options and the product page make up the rest. The Offer Matrix is an ecommerce competitive analysis at store level: 88 points in 8 groups, read for your store and for each competitor.
How to fill it
- Write the number, the threshold or the exact wording. Never a bare yes.
- Write None when a store has nothing. Write Not read when a page could not be reached.
- Grade each row for your store: Leads, At par, Behind or Not verified.
- Fix Critical gaps first. Each gap should name the competitor's number.
| Group | Checks | Critical | High | Standard |
|---|---|---|---|---|
| A. Price presentation and discount mechanics | 20 | 2 | 12 | 6 |
| B. Shipping and delivery | 10 | 3 | 4 | 3 |
| C. Returns, trial, guarantee, warranty | 10 | 3 | 4 | 3 |
| D. Trust and social proof | 14 | 0 | 10 | 4 |
| E. Product page persuasion and checkout | 16 | 3 | 11 | 2 |
| F. Urgency and scarcity (verify or avoid) | 5 | 2 | 3 | 0 |
| G. Positioning and offer architecture | 5 | 2 | 2 | 1 |
| H. Paid presence in Google | 8 | 0 | 8 | 0 |
| Total | 88 | 15 | 54 | 19 |
A. Price presentation and discount mechanics (20 checks)
| ID | Check | What to capture | Where to look | Grade |
|---|---|---|---|---|
| O01 | List price visible above the fold on the product page | Yes / No; price near the add to cart button on mobile | Top matched product page, mobile viewport (390 px) | High |
| O02 | Share of catalog on sale right now | % of products with a strikethrough or compare-at price (full catalog, or a 50-product sample) | products.json / store API; else the two largest category pages | High |
| O03 | Typical discount depth | Median % off on discounted items in the same sample | Same sample | High |
| O04 | Reference price legitimacy | Was the strikethrough really charged? Compare with a Wayback snapshot 60 to 90 days back; note "perpetual sale" | web.archive.org; Google Shopping price history | Critical |
| O05 | Reference price wording | MSRP / RRP / "compare at" / "was", and whether it is explained | Product page price block | Standard |
| O06 | Unit price shown | Yes / No and the unit | Product page price block | High |
| O07 | Multi-buy and quantity break pricing | Exact offer, e.g. "Buy 2 save 10%, buy 3 save 15%" | Product page near add to cart; cart | High |
| O08 | Bundles and bundle saving | Bundle names and stated saving | Product page, collections, cart upsell | High |
| O09 | Subscribe and save | % off, frequency options, cancel terms | Product page purchase options | High |
| O10 | First-order discount (email or SMS capture) | % or $ off, trigger, minimum order | Homepage popup, footer sign-up | High |
| O11 | Site-wide promo code visible | Code, value, minimum, end date | Announcement bar, homepage hero, product page | High |
| O12 | Loyalty or rewards program | Points per $, redemption value, tiers, sign-up bonus | Rewards page, account area | Standard |
| O13 | Referral program | Give / get amounts | Referral page, footer | Standard |
| O14 | Price match or lowest price guarantee | Window in days, exclusions, how to claim | Policy page, product page badge | High |
| O15 | Financing / buy now pay later | Providers; whether "from $X/mo" shows on the product page | Product page under price; checkout | High |
| O16 | Trade, pro or bulk pricing program | Program name and discount level | Navigation, footer | Standard |
| O17 | Taxes and mandatory fees disclosed before checkout | Shown near the CTA; any surprise fee at checkout | Product page, cart, checkout to the shipping step | Critical |
| O18 | Price localization / currency | Currency switcher; prices in the shopper's currency | Header | Standard |
| O19 | Sale event live right now | Event name, mechanism, stated end date | Announcement bar, homepage | High |
| O20 | Gift with purchase / free gift threshold | Item and threshold | Cart drawer, product page | Standard |
B. Shipping and delivery (10 checks)
| ID | Check | What to capture | Where to look | Grade |
|---|---|---|---|---|
| O21 | Free shipping threshold | $ amount, "All orders", or "None" | Shipping policy, announcement bar, cart drawer | Critical |
| O22 | Shipping cost below the threshold | $ flat or range for a 1-unit order to the ZIP | Checkout shipping step with the ZIP | Critical |
| O23 | Delivery time promise on the product page | Days range or dated promise near the CTA | Product page | Critical |
| O24 | Same-day dispatch cutoff | Time shown; timer; is the cutoff real | Product page, cart | High |
| O25 | Express or expedited options and cost | Options and $ | Checkout shipping step | Standard |
| O26 | Ships to | US only / US + CA / international | Shipping policy | Standard |
| O27 | Shipping protection / insurance add-on | Present; opt-in or pre-ticked; $ | Cart drawer, checkout | High |
| O28 | Free shipping progress bar in cart | Yes / No, with amount remaining | Cart drawer | High |
| O29 | Shipping cost shown near the CTA on the product page | Yes / No | Product page | High |
| O30 | Local pickup / store availability | Yes / No | Product page | Standard |
C. Returns, trial, guarantee, warranty (10 checks)
| ID | Check | What to capture | Where to look | Grade |
|---|---|---|---|---|
| O31 | Return window | Days | Returns policy | Critical |
| O32 | Who pays return shipping | Free / customer pays $X / restocking fee % | Returns policy | High |
| O33 | Return method | Prepaid label / pickup / in-store / portal | Returns policy | Standard |
| O34 | Home trial period | Nights or days and conditions | Product page badge, policy page | High |
| O35 | Money-back or satisfaction guarantee | Length and terms | Product page badge, policy page | High |
| O36 | Warranty length and coverage | Years / lifetime; what is covered; registration required | Warranty page | High |
| O37 | Exchange policy | Free exchange; window | Returns policy | Standard |
| O38 | Returns and guarantee summarized near the CTA | Yes / No; wording | Product page | Critical |
| O39 | Policy honesty check | Do product page badges match the policy page? Flag a "free returns" badge with a buried restocking fee | Product page vs policy page | Critical |
| O40 | Order cancellation window | Hours / until dispatch | Policy page, FAQ | Standard |
D. Trust and social proof (14 checks)
| ID | Check | What to capture | Where to look | Grade |
|---|---|---|---|---|
| O41 | Reviews on the top products | Average review count and rating across the top 5 matched products | Product page review widget | High |
| O42 | Review provider | Judge.me / Yotpo / Okendo / Trustpilot / Google / native | Product page source | Standard |
| O43 | Site-wide rating displayed and matching the live platform | Rating on site vs live Trustpilot or Google rating | Homepage or footer vs platform page | High |
| O44 | Negative reviews visible and filterable | Yes / No; star filter | Product page review widget | High |
| O45 | Photo and video reviews | Yes / No; count on the top product | Product page | High |
| O46 | Q&A on the product page | Yes / No; answered by the brand | Product page | Standard |
| O47 | Trust badges near the CTA | Which ones | Product page | High |
| O48 | Press and awards | Outlets named; linked or not | Homepage, product page | Standard |
| O49 | Certifications and third-party tests | e.g. CertiPUR-US, GOTS, NSF, USDA Organic, UL | Product page, about page | High |
| O50 | User-generated content and social embeds | Yes / No; where | Product page, homepage | Standard |
| O51 | Order or customer volume claims | Wording; sourced or not | Product page, homepage | High |
| O52 | Physical address and phone visible | Yes / No; where | Footer, contact page | High |
| O53 | Live chat and support hours | Chat; hours; response-time claim | Site-wide | High |
| O54 | Google seller rating / Google Customer Reviews | Stars and count in the search results for the brand name; badge on site | Google search for the brand name | High |
E. Product page persuasion and checkout (16 checks)
| ID | Check | What to capture | Where to look | Grade |
|---|---|---|---|---|
| O55 | Headline promise above the fold | One line, verbatim | Homepage hero, product page header | High |
| O56 | Benefit bullets near the title | Yes / No; count | Product page | High |
| O57 | Comparison table | Yes / No; vs own models or named competitors | Product page | High |
| O58 | Product video | Yes / No | Product page gallery | High |
| O59 | Variant images, 360 or AR view | Yes / No | Product page gallery | High |
| O60 | Size, fit or dimensions guide | Yes / No; format | Product page near options | High |
| O61 | FAQs on the product page | Yes / No; count | Product page | High |
| O62 | Cross-sells and bundles on product page and in cart | Yes / No; mechanism | Product page, cart drawer | High |
| O63 | Sticky add to cart on mobile | Yes / No | Product page, mobile | High |
| O64 | Express payments | Shop Pay / Apple Pay / Google Pay / PayPal on product page or cart | Product page, cart | High |
| O65 | Stock and availability shown | "In stock" / "Ships in X days" near the CTA | Product page | High |
| O66 | What is in the box | Yes / No | Product page | Standard |
| O67 | Spec table completeness | Number of spec rows on the top matched product | Product page | Standard |
| O68 | Guest checkout | Yes / No | Checkout | Critical |
| O69 | Checkout steps and fields | Steps; fields to the payment step | Checkout test, stop before payment | Critical |
| O70 | Mobile page speed on the top product page | LCP seconds, field data 75th percentile (PageSpeed Insights, mobile) | pagespeed.web.dev | Critical |
F. Urgency and scarcity (verify or avoid) (5 checks)
| ID | Check | What to capture | Where to look | Grade |
|---|---|---|---|---|
| O71 | Countdown timer | Present; stated end date; resets on reload (= fake) | Product page, cart, announcement bar | Critical |
| O72 | Low stock messages | Present; wording; same number on two visits an hour apart (= app-generated) | Product page | Critical |
| O73 | "X people viewing" or "bought today" widgets | Present; sourced or not | Product page | High |
| O74 | Cart reservation timer | Present; real or not | Cart, checkout | High |
| O75 | Sale end date stated and honored | Yes / No; re-check after the date | Announcement bar, sale page | High |
G. Positioning and offer architecture (5 checks)
| ID | Check | What to capture | Where to look | Grade |
|---|---|---|---|---|
| O76 | Price tier vs the set | Entry, median and top price on the matched category | Price Matrix | High |
| O77 | Hero product and hero offer | What the store pushes first and the offer attached | Homepage hero, best sellers | Standard |
| O78 | Differentiators claimed | Materials, origin, sustainability, design, service | Homepage, about page, product page | High |
| O79 | Risk reversal stack | Trial + returns + warranty + guarantee as one line per store | Composite of group C | Critical |
| O80 | Total cost of a $100 basket | Landed cost for a first-time buyer: price + shipping minus first-order discount | Composite of groups A and B | Critical |
H. Paid presence in Google (8 checks)
| ID | Check | What to capture | Where to look | Grade |
|---|---|---|---|---|
| O81 | Google Shopping ads on the core queries | Count of 10 core queries with this domain's Shopping ad | Google search in your market, Shopping tab | High |
| O82 | Search text ads on the core queries | Count of 10; headline hooks | Google search; the paid-ads report in any SEO tool | High |
| O83 | Shopping ad price vs product page price | Same / different (a mismatch is a Merchant Center policy risk) | Search results listing vs product page | High |
| O84 | Shopping ad annotations | Sale price / price drop / free shipping / free returns / stars | Search results listing | High |
| O85 | Merchant promotions in Shopping | "Special offer" badge and text | Search results listing | High |
| O86 | Google store rating / Top Quality Store badge | Present; rating | Search results listing; your Merchant Center store quality | High |
| O87 | Auction overlap with your store | Impression share and overlap rate (Shopping, 30 days) | Google Ads > Insights and reports > Auction insights | High |
| O88 | Offer messaging in the ads | Verbatim headlines about free shipping, % off, guarantee, financing | The paid-ads report in any SEO tool; the search results page | High |
In the workbook, every row also carries a source tag for its grade (field practice, published UX research, or a regulator or Google policy) and the section of the Ecommerce CRO Checklist where the fix lives. The Offer Matrix tells you where you are behind. The checklist tells you how to fix the page.
Legal and policy flags: record it, flag it, never copy it
Some tactics you will find on competitor sites should never go on yours. The workbook has a flag column for them. Record that a competitor uses the tactic, then recommend only the honest version: a real end date, live inventory, a price that was really charged.
| Tactic you may find | What to check before you react |
|---|---|
| A strikethrough or "was" price the store never really charged | UK: CMA guidance under the DMCC Act 2024. US: the FTC Guides Against Deceptive Pricing, 16 CFR Part 233 |
| A sale that never ends | The same reference price rules. Google also tests price history before it shows a sale price annotation (see below) |
| Fake reviews, or paid reviews presented as independent | UK: fake reviews and concealed incentivized reviews are banned practices under the DMCC Act 2024. US: the FTC rule on consumer reviews and testimonials, 16 CFR Part 465, in effect since October 21, 2024 |
| Mandatory fees that appear late in checkout (drip pricing) | UK: a banned practice under the DMCC Act 2024, in force since April 6, 2025. Everywhere: show the total early |
| A countdown that resets on reload, low stock messages not tied to inventory, fake viewer counts | Misleading urgency. Record it. Recommend a real end date and live inventory only |
| Pre-ticked paid add-ons | Record it. Never recommend it |
| A badge that contradicts the policy page, such as "free returns" with a restocking fee | Record both wordings. The policy page is what the shopper is held to |
Google's sale price annotation. Google runs its own price history test before it shows a sale price annotation, and the test differs by country. For Shopping ads in the United States, the base price must have been charged for 5 days within the past 30 days or 15 days within the past 200 days. In the United Kingdom and most other countries, it must have been valid for 30 days within the past 200 days. The discount must be more than 5% and less than 90%. Check the live Merchant Center help page before you rely on these numbers.
Google's misrepresentation policy covers dishonest pricing and hidden charges as well. Resetting timers, permanent sales and fake compare-at prices are a Merchant Center misrepresentation risk, not only a legal one.
This section is general information, not legal advice.
The AI prompt: let Claude, ChatGPT or Gemini fill the workbook
This is the competitor analysis prompt written for the workbook. It is about 1,000 words. It tells the assistant how to match products, where to read prices, when to stop and what to hand back. Copy it, fill the inputs at the top, and paste it into a new chat with the workbook attached.
PROMPT NAME
Price & Offer Intelligence: competitor price and offer analysis for an ecommerce store (Public Edition, Just Lead Market)
YOUR INPUTS (fill before running)
store_name: {your store}
store_url: {https://www.yourstore.com}
market_and_currency: {US, USD}
zip_or_postcode: {10001} # one delivery address used on every store
gross_margin_pct: {55} # used when a SKU has no cost
minimum_margin_pct: {40} # the floor; no suggested price goes below it
competitors_i_know: [{domain}, {domain}] # up to 5, optional
competitors_to_ignore: [{domain}]
hero_skus: [{item id or title}] # optional
sku_scope: {top 25 by ad spend or revenue | all | list}
cannot_discount: [{MAP-priced or contract SKUs}]
attached: the Price & Offer Intelligence workbook; my product export (Merchant Center feed, Shopify export or CSV); optional: 90-day Google Ads product report (item ID, cost, conversion value, conversions)
ROLE
You are a pricing and merchandising analyst for an ecommerce store. You compare what a shopper would actually pay and get at my store against the competitors that shopper already has in mind. You read, match, calculate and recommend. You change nothing in any live store, feed or ad account.
FIRST, STATE YOUR MODE
Mode A: you can browse live pages. Mode B: you cannot browse; work only from files and pasted pages, and ask me to paste what you cannot read. Say which mode you are in, in one line.
STEP 1. Read the workbook
Open the Read me tab and follow its rules. Yellow cells are inputs. Gray cells are formulas: never overwrite them. Fill section 3 from my inputs above.
STEP 2. Competitor set
Use competitors_i_know first. In Mode A also search 10 core queries for my top products in my market and log every store with a Shopping ad or a top organic listing, and search each hero product name on the Google Shopping tab. Pick up to 5 direct competitors: same product type, same market, similar price tier. Write each competitor's platform, free shipping threshold and flat shipping rate into section 4 before using any formula. Log the rejected candidates and why in section 5.
STEP 3. My catalog
One Price Matrix row per advertised SKU or variant within sku_scope: item id, title, brand, GTIN, MPN, product type, variant, unit label, units per item, list price, live sale price, cost when I gave it. Add 90-day ad cost, conversion value and conversions per item when the report is attached. Work Tier 1 rows first.
STEP 4. Price reads (Price Matrix)
For each SKU and each competitor, find the same product. Match rules:
- Identical: same GTIN or MPN, or same brand and model. For own-brand products, the same design, type and size.
- Equivalent: same type and key specs. Compare per unit.
- None: the competitor does not sell it. Write None. Never force a match and never match on title words alone.
Write for every match: product name, URL, match type, confidence (High, Medium, Low), units per item, list price, sale price if live today.
Read each competitor's cart or checkout once with nothing entered. If a discount or fee is applied automatically, apply it to that competitor's prices and say so.
Every price needs a URL and today's date. If you could not read a page, write Not read and the reason. Never estimate a price.
When a row's competitors are all done, write the Verified date.
STEP 5. Offer reads (Offer Matrix)
For my store first, then each competitor, read: announcement bar and homepage; the top matched product page on a mobile viewport; cart and checkout up to the shipping step with zip_or_postcode (stop before payment, enter no personal data); shipping, returns, warranty, guarantee, price match and contact pages. Fill all 88 rows with the number or the exact wording, never a bare yes. Write None when a store has nothing and Not read when a page was unreachable. Then set Best in set, my position (Leads, At par, Behind, Not verified), and a recommendation that carries the number.
STEP 6. Verdicts
The bands compute from landed price: price plus one-unit shipping. Check the per-unit index before trusting a band driven by an Equivalent match.
Give every matched SKU one recommendation: Hold, Reprice, Raise price (test), Reframe, Justify on page, Promote, Exclude from ads, or Investigate.
Suggested price for Reprice: the lowest Identical competitor's landed price minus my shipping, rounded to my price ending. Keep it only if the margin at that price is at or above minimum_margin_pct. Below the floor, recommend Reframe or Exclude from ads, never Reprice. Never suggest a price for a SKU in cannot_discount.
If an Identical match puts my index below 0.90, recommend Raise price (test) toward 0.97.
Write a rationale per SKU with names, numbers, the landed arithmetic and the date.
LEGAL AND POLICY FLAGS
Record, flag and never recommend: a strikethrough price that was not really charged, a sale that never ends, a countdown that resets, low stock messages not tied to inventory, fake viewer counts, pre-ticked paid add-ons, a badge that contradicts the policy page. Recommend only the honest version. You are not giving legal advice.
OUTPUT, IN THIS ORDER
1. Mode line and what you could not read.
2. The filled workbook, same structure, formulas intact. If you cannot edit files, output two paste-ready tables: Price Matrix inputs by cell range, and Offer Matrix columns H to Q.
3. Scorecard: SKUs checked, count per band, average index, 90-day ad cost on SKUs over 15% higher.
4. The 10 SKUs losing most and the 10 winning, with suggested price and margin.
5. Offer gaps, Critical first, each with the competitor's number.
6. Feed label list: item id and price label, checked rows only.
7. Assumptions and unknowns, labeled.
RULES THAT DO NOT BEND
Read only. No invented prices, stock, reviews or policies. Every claim has a URL and a date. None is a finding. Landed price, not shelf price. Margin before price. Ask at most one batch of questions, and only for inputs that change a verdict.How to run the prompt
- Get the workbook as an Excel file. If you made a Google Sheets copy, use File > Download > Microsoft Excel.
- Export your products: your Merchant Center feed, your Shopify or WooCommerce product export, or a CSV with title, price, sale price, GTIN and item ID.
- Open Claude, ChatGPT or Gemini in a new chat. Attach the workbook and the product export.
- Fill the YOUR INPUTS block at the top of the prompt, then paste the whole prompt.
- Read the first line of the reply. Mode A means the assistant can browse and will read live pages. Mode B means it cannot, and it will give you a checklist of what to collect.
- Check five price reads against the live pages before you act on any verdict.
What the AI can and cannot do
- It can read public product pages, policy pages and Google results when it has web browsing.
- It cannot see your Merchant Center, Auction insights or cost data unless you export and attach them.
- It stops before payment and enters no personal data on any site.
- About 50 SKUs fit in one chat. Above that, fill the Price Matrix in one chat and the Offer Matrix in a second chat with the filled workbook attached.
- Prices move and models misread. The five-price check in step 6 is not optional.
What the results change in your Google Ads account
A price position per SKU is a targeting input. This is how to use it.
- Load the feed label. The workbook writes one of five labels per SKU. Add it to a free
custom_labelslot through a supplemental feed in Merchant Center. - Split by price position. Give
price_cheapestSKUs their own campaign or asset group and their own budget. Keepprice_overSKUs from spending that budget until they are repriced, reframed or excluded. - Recompute break-even ROAS before you change a price. A lower price cuts margin. At a 50% margin your break-even ROAS is 2.0. At 40% it is 2.5. Run the numbers in Ad Profit Check or read how break-even ROAS works.
- Fix Critical offer gaps before you raise bids. A missing free shipping threshold or a missing returns summary is cheaper to fix than a price. This is where conversion rate optimization and the price work meet.
If you want this run inside a managed account, it is part of how we handle Google Shopping management.
Done for you: we run it on your store for $99
If you would rather have the answer than the spreadsheet, we run the analysis for you. One store, up to 25 SKUs, up to 5 competitors, $99.
What you get
- Both matrices filled: every price with a URL and a date, and all 88 offer rows for your store and each competitor.
- A checkout read on every competitor, so automatic discounts and real shipping costs are in the numbers.
- The Google Shopping sellers of each product, so the lowest live price is not missed.
- A suggested price per SKU with the margin check, using your margin floor.
- A feed label file (item ID and label), ready for a supplemental feed.
- A one-page summary: the price moves and the offer gaps to fix first.
What we need from you: your store URL, your product export or Merchant Center feed, your margin floor, and any competitors you already watch.
How it works. Send the form. We reply by email to confirm scope, payment and the delivery date before any work starts. Nothing in your store or ad account is changed. You get a list to approve.
Spending $10,000 or more a month on Google Ads in the US, or £5,000 or more in the UK? Start with a Just Free Audit instead. It covers the whole account, not only price.
Who this template is for, and who should skip it
Use it if
- You run an ecommerce store with its own catalog on Shopify, WooCommerce, BigCommerce or Magento.
- You advertise on Google Shopping or Performance Max and need to know which SKUs deserve budget.
- Conversion rate is under target and you cannot tell whether price, offer or traffic is the cause.
- You are the founder or marketing lead who has to defend a pricing decision with evidence.
- You need an ecommerce competitor analysis template that ends in a list of changes, not a slide.
Skip it if
- You sell only on marketplaces. Marketplace pricing has its own tools and rules.
- Your prices are quoted or negotiated per account.
- You need hourly repricing across thousands of SKUs. That is a job for price monitoring software.
Price & Offer Intelligence Workbook: key facts
| Resource | Price & Offer Intelligence Workbook, Public Edition |
|---|---|
| Type | Competitor price analysis template with a store-level offer comparison |
| Publisher | Just Lead Market |
| Author | Abdul Rauf Ali |
| Price | Free |
| Formats | Google Sheets, Excel (.xlsx), 2-page PDF cheat sheet |
| Capacity | 500 SKU rows, 5 competitors per SKU |
| Formulas | 23,050 in total, 46 per SKU row |
| Comparison basis | Landed price: effective price plus shipping for one unit |
| Price index | Your landed price divided by the lowest competitor landed price |
| Price bands | Cheapest or equal (1.00 or below), Within 5% (to 1.05), 5-15% higher (to 1.15), Over 15% higher (above 1.15) |
| Feed labels | price_cheapest, price_par, price_high, price_over, price_nomatch |
| Verdicts | 8: Hold, Reprice, Raise price (test), Reframe, Justify on page, Promote, Exclude from ads, Investigate |
| Offer Matrix | 88 store-level checks in 8 groups: 15 Critical, 54 High, 19 Standard |
| Scorecard | 40 metrics, computed from the two matrices |
| AI prompt | About 1,000 words, for Claude, ChatGPT or Gemini |
| Stale read rule | A price read older than 14 days is stale |
| Test rule | Control SKUs left unchanged, readout after at least 14 days and 30 conversions |
| Done-for-you option | $99 for one store, up to 25 SKUs and up to 5 competitors |
| Last updated | November 20, 2026 |
| Use terms | Free to use and adapt for your own store or your clients |
Competitor price analysis: questions and answers
Didn’t find what you’re looking for?
Get the workbook, or get the answer
Do it yourself
The workbook, the AI prompt and the cheat sheet. Free.
Spending $10,000+ a month on Google Ads
A review of the whole account, not only price. Free.
Just Free Audit