SEO competitor analysis template

In terms of clicks and conversions, there’s an exponential difference between pages that rank #1 in search and those that rank #3 (let alone #10 or #15).

That’s why competition matters in search performance. (Plus it’s fun to show your boss that you’re ranking higher than the competitors.)

But before you can start zooming past your competitors in search, you need to understand the competitive landscape.

Why we created the SEO Competitor Analysis template

Keyword tools provide an efficient, data-driven way to size up the organic search competition.

We designed this template to pull in that search data. It enables our clients to get started quickly on some fundamental strategy tasks:

• Understand what your competitors are doing right
• Generate ideas for campaigns to run (or duplicate)
• Identify areas that your competitors have overlooked.

What you need to get started

To complete this template (a free Google Sheet), you’ll need data from the following source:

• SEO keyword tool (like Semrush or Ahrefs)

Looking for free SEO competitor analysis?

If you don’t have full access to an SEO tool like Semrush or Ahrefs, you can still do free SEO competitor analysis with Semrush’s free plan. Check out this article for step-by-step instructions for an SEO competitor analysis workflow you can do without entering a credit card.

How this template works

The template uses a set of algorithms to crunch comparative search data between your site and your top competitors. It ranks keywords on a scale from 1 (strong performance) to 100 (no performance).

The template consists of several sheets of comparative data, that direct you to a few insights:

• Where you’re currently performing strongly
• Where you have opportunities to rank with new content
• Where you can make keyword plays relative to your competitors.

Note: “opportunity” and “competitive” results still require human assessment and judgment.

A keyword may be an opportunity according to the algorithm but may not fit in with your business model. This may be because the search intent behind the keyword doesn’t align with your business, or because you don’t regard the term as strategic to your mission.

Using the SEO competitor analysis template

To start, open a new blank template for your competitive analysis.

Choose Competitors to Analyze

Select 3 or 4 top competitors to compare your website against. These could be well-known competitors or brands that often come up when discussing potential clients. If you are unsure about your competitors, research who is currently ranking for topics relevant to your niche. Tools like Semrush can also help identify your major competitors from an organic search standpoint.

Extract Keyword Rankings

After determining your competitors, use Semrush or another keyword research tool to extract keyword rankings for your website and top competitors.

To extract data from Semrush:

1. Go to the Keyword Gap tool
2. Enter the root domains - first, your site's root, followed by the roots of up to four competitors
3. Click Compare
4. Under All Keyword Details at the bottom of the screen, click All
5. Click Export from the right-hand drop-down menu and export to Excel

Import Keyword Data

Once you have all the necessary data, open the csv – If you didn't enter five domains into Semrush, add columns to the CSV file before copying and pasting it into the Keyword Data tab to ensure the data matches the template.

copy and paste it into the keywordData tab (from D2 on) of your Competitive Analysis Spreadsheet. Make sure to include all keyword data.

Edit Keyword Data

Make the following edits to your data:

1. For the keywordData sheet, select E3:I and go to Edit > Find and replace
2. Put a 0 in the "Find" field and 100 in the "Replace with" field
3. Click Match entire cell contents > Done

This step ensures the algorithm interprets "0" as "unranked" instead of a higher rank.

Filter out branded keywords

On the keywordData tab, above each companies name, there is an orange box where you can filter out branded keywords from our analysis. Input the shortest unique version of each companies name into their respective cells.

Analyze the Data

The information in the spreadsheet can be very useful for understanding your competitive landscape:

• The Strong, Opportunity, and Competitive tabs provide insights into your website's performance
• The two strategic reports highlight competitive performance for keywords identified using the topic strategy & tracker

To analyze the data in-depth, filter specific keywords or topics to focus on high-priority areas. You can also analyze data for a specific competitor to understand their success and inform your strategy.