---
title: "Competitor price analysis template (free spreadsheet) | Product Metrics"
description: "Download a free competitor price analysis template: compare your prices with three competitors, see price position and ratio, and log changes weekly."
canonical: "https://www.productmetrics.io/blog/competitor-price-analysis-template"
pageType: article
language: en
publisher: "Product Metrics"
author: "Berend Vrakking"
datePublished: 2026-10-07
dateModified: 2026-10-08
image: "https://www.productmetrics.io/og/blog/competitor-price-analysis-template.png"
---

> Content index: https://www.productmetrics.io/llms.txt

# Competitor price analysis template (free spreadsheet)

## Key takeaways

- A competitor price analysis needs five inputs per product: your price, up to three comparable competitor prices on one basis (list price, without shipping), the date checked and your margin.
- The Snapshot sheet turns them into a matched median, price position, price ratio and price difference. A blank competitor cell is ignored, so two prices still give a median.
- The Weekly log shows movement a snapshot hides: in the example your price never changes, yet you slide from cheapest to third of four.
- Check weekly to start with. Move to software when matching products by hand or typing in prices becomes the job.

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)](https://www.productmetrics.io/downloads/competitor-price-analysis-template.xlsx)

It follows the method in [how to track competitor prices for Google Shopping](https://www.productmetrics.io/blog/how-to-track-competitor-prices-google-shopping), and it uses the same measures as [Competitor Prices](https://www.productmetrics.io/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.

| Input | Why it matters | Where it goes in the template |
|---|---|---|
| Your price | The number you compare | Snapshot, "Your price" |
| Comparable competitor prices | The market you are measured against | Snapshot, "Competitor 1" to "Competitor 3" |
| One price basis | Keeps the gap about price alone | How to use: list price, without shipping |
| Date checked | A price from last month is history | Snapshot, "Date checked" |
| Margin | Tells you whether a gap matters | Snapshot, "Margin %" (context only) |

## 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.

**Do a competitor price analysis in five steps**

1. **List the products that matter most.** Start with the products that take most of your ad spend. Ten is a sensible first batch. Enter your own price in the purple cells.
2. **Pick three comparable competitor products.** Match on use, size, material and colour. For own-brand products do not rely on GTIN: nobody else sells yours.
3. **Enter list prices on one basis.** Use public list prices without shipping, and write the date you checked.
4. **Read the Snapshot columns.** Matched median, price position, price ratio and price difference fill in on their own.
5. **Log the same products every week.** Copy the prices into the Weekly log to see who moved and when, then read each gap next to margin and POAS.

### 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.

| Column | Type | What it holds |
|---|---|---|
| Product, Category | Input | Your label and a grouping such as "Running" |
| Your price (€) | Input | Your list price |
| Competitor 1 to 3 (€) | Input | Comparable competitor list prices |
| Matched median (€) | Formula | The middle competitor price |
| Price position | Formula | Your rank among the products, 1 = cheapest |
| Price ratio | Formula | Your price relative to the median |
| Price difference (€) | Formula | Your price minus the median |
| Margin % | Input | Your margin, for context |
| Reading | Formula | "Above median", "Below median" or "At median" |
| Date checked | Input | When you looked |

The four calculated measures are plain arithmetic:

| Measure | In plain text | Formula in the sheet (row 2) |
|---|---|---|
| Matched median | MEDIAN of the competitor prices entered | `MEDIAN(D2:F2)` |
| Price position | 1 + the number of competitor prices below yours | `1+COUNTIF(D2:F2,"<"&C2)` |
| Price ratio | Your price ÷ matched median | `C2/G2` |
| Price difference | Your price − matched median | `C2-G2` |

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.

**Figure: Price ratio: your price divided by the competitor median, five example products.** Each bar is your price divided by the median of the competitor prices, so 1.00 means you match the median. Trainer A sits 7% below its median (0.93), while Jacket C and Cap D sit 4% above theirs (1.04).

| Product | Your price ÷ competitor median |
| --- | --- |
| Trainer A | 0.93 |
| Trainer B | 1.00 |
| Jacket C | 1.04 |
| Cap D | 1.04 |
| Socks E | 1.00 |

_Source: Illustrative data. Price ratio is your price divided by the median of the competitor prices entered._

| Product | Your price | Competitor prices | Matched median | Price position | Price ratio | Price difference |
|---|---:|---|---:|---:|---:|---:|
| Trainer A | €139.99 | €142.00, €150.00, €169.95 | €150.00 | 1 of 4 | 0.93 | −€10.01 |
| Trainer B | €129.99 | €119.95, €129.95, €139.95 | €129.95 | 3 of 4 | 1.00 | +€0.04 |
| Jacket C | €119.99 | €99.95, €129.95 (one blank) | €114.95 | 2 of 3 | 1.04 | +€5.04 |
| Cap D | €27.99 | €24.95, €29.95, €26.95 | €26.95 | 3 of 4 | 1.04 | +€1.04 |
| Socks E | €15.99 | €14.99, €15.99, €17.99 | €15.99 | 2 of 4 | 1.00 | €0.00 |

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](https://www.productmetrics.io/blog/what-is-price-intelligence) explains the difference between watching prices and acting on them.

> **Sidenote:** Use publicly visible prices only. Observing a competitor's public price is different from discussing or agreeing prices with them, which competition rules restrict in most markets. This is not legal advice: check the rules where you sell.

## 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](https://www.productmetrics.io/blog/how-to-track-competitor-prices-google-shopping), where a €139.99 trainer is never repriced. Here is what the log's calculated rows show:

| Week | Your price | Cheapest competitor | Price position | Gap to cheapest | Competitor 2 vs week 1 |
|---|---:|---:|---:|---:|---:|
| Wk 1 | €139.99 | €142.00 | 1 of 4 | −€2.01 | 0.0% |
| Wk 2 | €139.99 | €141.00 | 1 of 4 | −€1.01 | 0.0% |
| Wk 3 | €139.99 | €136.00 | 2 of 4 | +€3.99 | 0.0% |
| Wk 4 | €139.99 | €136.00 | 2 of 4 | +€3.99 | −3.3% |
| Wk 5 | €139.99 | €124.95 | 2 of 4 | +€15.04 | −3.3% |
| Wk 6 | €139.99 | €124.95 | 3 of 4 | +€15.04 | −7.3% |
| Wk 7 | €139.99 | €124.95 | 3 of 4 | +€15.04 | −7.3% |
| Wk 8 | €139.99 | €129.95 | 3 of 4 | +€10.04 | −7.3% |

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.

| Schedule | Fits | Checks per product per month |
|---|---|---:|
| Daily | Fast categories with frequent promotions | 30 |
| Weekly | A sensible start for most products | 4 |
| Monthly | Slow categories with stable prices | 1 |

By hand, the schedule is limited by your time. With [Competitor Prices](https://www.productmetrics.io/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](https://www.productmetrics.io/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](https://www.productmetrics.io/blog/how-to-track-competitor-prices-google-shopping) lists what to look for in any tool, and [price monitoring software compared](https://www.productmetrics.io/blog/price-monitoring-software-compared) compares the options.

## Frequently asked questions

### Does the template work in Excel and Google Sheets?

It is a standard .xlsx file that uses basic formulas (MEDIAN, COUNT, COUNTIF, IF). Open it in Excel, or upload it to Google Drive and open it with Google Sheets.

### How many competitor prices do I need per product?

Three is the aim, and the sheet has three input columns. It counts only the cells that hold a number, so two prices still give a median (their average) and one price gives that price. With none, the calculated columns stay blank.

### Should I include shipping in the comparison?

No. Compare list prices without shipping for every product, so the gap reflects price alone. Delivery terms and shipping thresholds differ by shop and change the total, so judge them separately.

### How do I compare own-brand products that share no barcode?

Match on the dimensions shoppers weigh, such as use, size, material and colour, and pick competitor products that fit most of them. The guide on how to track competitor prices for Google Shopping describes the method.

### What is the difference between price position and price ratio?

Price position is your rank among the products, counting yourself, where 1 is the cheapest. Price ratio is your price divided by the matched median, so 1.00 means you match it. Position shows the order, ratio shows the size of the gap.

### What is a good price ratio?

There is no good number. A ratio of 1.00 means you match the median, and above 1.00 means you are more expensive. Read it next to margin, stock and POAS: a product above the median can still be profitable.

### When should I stop using a spreadsheet?

When you track more than a few dozen products, check more often than weekly, or spend more time matching products and typing in prices than reading them. At that point software saves the work.

---

Written by Berend Vrakking, founder of Product Metrics. Last updated 2026-10-08.

HTML version: https://www.productmetrics.io/blog/competitor-price-analysis-template