Competitor price analysis template (free spreadsheet)

A spreadsheet is the quickest way to start a competitor price analysis, and for a few products it is all you need. This free template has the formulas built in, so you add prices and dates: a Snapshot of where each product sits, and a Weekly log of who moved and when.

Download the template (.xlsx)

It follows the method in how to track competitor prices for Google Shopping, and it uses the same measures as Competitor Prices in Product Metrics. The numbers in the workbook are illustrative: replace them with your own.

What should a competitor price analysis include?

A competitor price analysis needs five inputs per product: your own price, up to three comparable competitor prices, the date you checked, one stated price basis and your margin. From these you read three outputs: price position, price ratio and price difference. Add a weekly history so you see movement, not just today’s snapshot.

How do you do a competitor price analysis?

List your products, add comparable competitor prices on one basis, read the gaps, and repeat on a schedule. The template follows those five steps.

Which mistakes skew the result?

Four things distort a competitor price analysis more than any formula:

• Mixed price bases. One price with VAT and another without, or one with shipping, makes the gap meaningless. Fix one basis and keep it.
• Products that are not comparable. A median of unlike products tells you nothing. If you doubt a match, leave the cell blank rather than guess.
• Stale dates. A price from last month is history. The “Date checked” column is there so you notice.
• Reading one competitor as the market. The median is robust here: in Trainer A’s row, one competitor at €169.95 does not move the €150.00 median, so a single outlier cannot swing the result.

What is in the Snapshot sheet?

The Snapshot sheet has one row per product and 13 columns, with formulas prefilled for 59 rows. Purple cells are inputs. The other columns calculate themselves.

The four calculated measures are plain arithmetic:

Each formula is wrapped in an IF that leaves the cell blank until your price and at least one competitor price are filled in. A competitor with the same price as you is not counted as cheaper, so a tie shares the better rank. The formulas read columns D to F, so extend the ranges if you add a fourth competitor.

How does the sheet treat a blank competitor price?

It ignores it. The median and the position count only cells that hold a number, so a product with two competitor prices still gets both. With one price, that price is the median; with none, the row stays blank. In the example below, Jacket C has two competitor prices (€99.95 and €129.95). The median is their average, €114.95, and the position is “2 of 3”: one competitor is cheaper and the other is not. Three prices give a sturdier median, so treat two as the minimum.

Example output from the Snapshot

Trainer A undercuts its median by €10.01; the other four sit within €5.04 of theirs. The ratio chart shows the same thing as a picture.

The “Reading” column turns the sign of the price difference into words, so you can scan for everything that sits above its median. Start there, then look at the largest price ratios. A ratio above 1.00 is not a reason on its own to lower a price. Read it next to margin and POAS first: a product above the median can still be profitable, and one below it can still lose money. What price intelligence is explains the difference between watching prices and acting on them.

What goes in the Weekly log?

The Weekly log records one product across up to 12 checks: your price and three competitor prices per week. Two rows calculate your price position and your gap to the cheapest competitor, and a third shows how far Competitor 2 has moved since week 1. Write the date next to each week, because “Wk 5” means little in six months.

The example uses the same eight-week series as the competitor price tracking guide, where a €139.99 trainer is never repriced. Here is what the log’s calculated rows show:

To read a trend, look at three things:

• The position. It fell from 1 to 3 while your price stayed put, so the market moved, not you.
• The gap. It widened from −€2.01 to +€15.04 by week 5, then narrowed to +€10.04 when the cheapest competitor partly recovered in week 8.
• Who moved, and how. Competitor 2 drifted down in two steps (weeks 4 and 6), while the cheapest competitor jumped once. A jump suggests a promotion or a reprice, and a drift a slower repositioning.

None of this tells you what to do with your own price. Put the gap next to margin and POAS first: a product that still meets its POAS target at position 3 may need nothing, while one that misses it is worth a closer look.

How often should you check competitor prices?

Check weekly to start with, then match the schedule to how fast your category moves: daily where competitors change prices often, monthly where they rarely do. A weekly rhythm catches most shifts without turning the analysis into a chore, and it fits the Weekly log’s one-column-per-check layout.

By hand, the schedule is limited by your time. With Competitor Prices, one check of one product costs €0.004 excluding VAT, so a weekly check of 500 products is €8.00 a month.

When does a spreadsheet stop being enough?

A spreadsheet stops being enough when finding and typing in prices takes longer than reading them. Typical signs:

• You track more than a few dozen products, or check more often than weekly.
• Matching own-brand products by hand, product by product, has become the main job.
• You want price history for every product, not one log sheet for one product.
• You want the gap next to Product Score, margin and POAS without copying numbers between tools.

Competitor Prices scans the competitor sites, matches the products for you and keeps the history, using the same definitions as this template. The guide to tracking competitor prices for Google Shopping lists what to look for in any tool, and price monitoring software compared compares the options.