Analytics with Ottimo
Competitive analysis: how-to & template
Analysis and insight for competitive topics
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 on Google, you need to understand the competitive landscape.
Click the button below to duplicate the template into your Google Drive
Why we created this 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, you’ll need data from the following source:
- Keyword tool (like SEMrush or Ahrefs)
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.
Step-by-step: using the competitive 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:
- Go to the Keyword Gap tool
- Enter the root domains - first, your site's root, followed by the roots of up to four competitors
- Click Compare
- Under All Keyword Details at the bottom of the screen, click All
- 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:
- For the
E3:Iand go to Edit > Find and replace
- Put a 0 in the "Find" field and 100 in the "Replace with" field
- Click Match entire cell contents > Done
This step ensures the algorithm interprets "0" as "unranked" instead of a higher rank.
Filter 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.
Everything we have to say on SEO, content performance, and content analytics – in convenient email form.
(Having doubts? Here's a recent issue.)