Welcome to another week of Tutorial Tuesday here on Linkarati. This week I’ll be showing you how to dig into your search competitors’ keyword rankings via SEMrush. Before we move on I do want to note this will require a paid subscription to SEMrush. I highly recommend the tool if you have the budget - depending upon your projects and goals, it should absolutely be a top consideration.
Specifically, this is how this Tutorial Tuesday will break down:
- We’ll go into SEMrush to view your organic keywords
- Check SEMrush for search competitors
- View your competitors' organic keywords
- Export it all to Excel to compare and contrast
- Open-ended data fun.
Tracking keyword rankings for both your own website and your competitors is fundamental to SEO strategy. You need to understand which keywords in your industry are driving traffic, how you compare to your competitors, and how specifically you can improve.
Building and researching competitor keyword rankings will help you:
- Generate new keyword and content ideas
- Better understand competitors' target audience
- Discover which keyword rankings you could improve for increased traffic
- Find keywords that are driving traffic to your competitors’ websites
- Guide decision-making for website expansion.
The best part about all of this? It’s extremely easy to accomplish, and extremely actionable. This is insight and data every SEO needs – particularly if you’re just beginning in a new project.
Let’s jump into it.
Learn more about how to research keywords for link building in our downloadable ebook:
Step One: Head into SEMrush
First and foremost you need to head into SEMrush. Don’t forget you’ll need a paid subscription, which frankly is well worth it.
Once there, you’ll need to start with your own website. Type your domain into the search bar at the top of the dashboard:
Then click the search button.
This should take you to an overview for your domain.
Important to note: SEMrush’s data isn’t perfect. Depending on website age, size, industry, and traffic it might be a pretty inaccurate picture. It certainly is for Linkarati. Despite this some data is better than none, and SEMrush generally does a solid job reporting on keywords and competition, which is what we’re primarily concerned with.
If this is your first time in SEMrush – or even your tenth time – I’d recommend taking some time to look around and play with all the various menus and options. Get a feel for SEMrush, compare to other analytics, get a sense for the lay of the land.
Becoming familiar and comfortable with any tool is extremely important to efficient and effective usage, and the easiest way to foster this understanding is by simply spending time clicking around and discovering what is and is not possible. So take the time to understand your options.
Step Two: Check Your Organic Keywords and Export to Excel
Now we're ready to dig into the keyword data, starting with your own site.
Ideally you should already have a keyword list with tracked rankings, along with a strong understanding of which pages drive your organic traffic. If you do not, I highly recommend taking the time to build one, using tools such as:
- Google Webmaster Tools > Search Traffic > Search Queries
- Google Analytics > Acquisition > Channels > Organic Search
- Google Analytics > Behavior > Site Content > Content Drilldown > Segment Organic Traffic
- Keyword list building tools such as:
You should know before beginning a website which keywords are important, relevant, and traffic-driving for your industry. SEMrush will provide you another source of data, allowing you to see where you’re underperforming and where you should concentrate efforts.
Scroll down to the Organic keywords section of the overview and click either “full report” or “organic keywords” to your keyword list:
This will take you inside your keyword ranking list. Click the Export button to take the data to Excel.
Make sure you choose Excel when the option appears:
Once it’s finished downloading open up your file:
And make sure to save the file immediately. Don’t forget to save it as an Excel workbook - the auto-populated option for me was as an HTML document, which makes the data useless later.
This should leave you with an Excel sheet of keywords your website is ranking for, along with the position, search volume, URL, and traffic percentage.
Now it’s time to check your competitors.
Step Three: Checking Search Competitors in SEMrush
You've downloaded your own keyword rankings from SEMrush – now it’s time to find competitors.
Within the "Organic Research" menu click over to Competitors:
This will take you to an overview of websites who share search keywords with you.
The more positions SEMrush has listed for your website, the more likely SEMrush will get your search competitors right. Regardless, you shouldn’t take SEMrush’s competitor list on faith alone. Make sure you’re doing the work to determine search competitors.
Step Four: Choose Three to Five Competitors
Depending on the size of your site and competitors’ websites, I strongly recommend keeping a narrow focus initially.
If this is your first time running a competitive keyword analysis then a narrow spectrum will keep the data limited, specific, and actionable. Including too many competitors, particularly if they’re large websites ranking for a wide gamut of terms, can create too much data noise.
In this case let’s pick out four competitors.
Since Linkarati is still a relatively new website in a narrow niche of an industry, SEMrush doesn't have much data to go on for us. Despite this, they do a fair job picking out real competitors, although they include broad spectrum websites such as Twitter, LinkedIn, Facebook, and YouTube as well.
Since I’m extremely familiar with our industry and have done plenty of competitor analysis I’m well aware of which sites will yield the most useful data upon analysis. Let’s pick out Moz, PointblankSEO, Backlinko, and SEERinteractive:
All are outstanding websites with quality, relevant content, which should give us a nice data range.
Step Five: View Competitors’ Organic Keywords and Export to Excel
Once you've picked which competitors you want to analyze simply click on their names within the competitor list to be taken to SEMrush’s overview of their website. I’d recommend doing each competitor in a new tab:
This will take you to SEMrush's overview of your competitor’s website. Scroll down and click on “Organic keywords” to see the list of keywords sending them search traffic.
Once again click export and choose excel.
Again, be sure you save the file somewhere you’ll remember as an Excel workbook.
Rinse and repeat for each competitor you wish to analyze.
Step Six: Combine SEMrush Competitor Data in Excel
You should now have four to six Excel files open, each with SEMrush’s keyword data on a different site, including your own site plus three to five competitors.
It’s time to combine them all together.
First, add as many blank worksheets as necessary in the file housing your own website’s SEMrush keyword data. You want all of this data to live in a single file and eventually a single sheet.
Don’t just dump it all in a single sheet to start with though – we’ll do that with a master sheet at the end. Always preserve data before manipulating it.
Rename each sheet to the domain name of the website whose data it will house.
Add one more sheet, name it appropriately, and move it to the front. This will be the sheet you’ll combine all data into.
Go ahead and copy all the data from the separate files into each appropriate sheet. The easiest way by far to do this is to click the highlight all button above row one and to the left of column A:
Control+c the information, navigate to the appropriate sheet, and dump it on in. The cells will auto-format to match the original layout/data. Pretty awesome, eh?
Now, let’s flip over to our master sheet which will house a combination of all the data. First, you’ll want to freeze row one to make it easy to track data as you scroll down. Flip over to the “View” tab, select “Freeze Panes” and then click “Freeze Top Row.”
That will do the trick.
Next, let’s dump all the data into the master sheet. Simply copy and paste each individual sheet into the master sheet. And don’t forget to skip copying row one with each data dump – no need since that is a repeat of your top row, which will be your frozen header row.
The data will still be organized since each sheet is formatted the same, with the same data in the same column.
The end result should be a pretty hefty Excel sheet with keyword, position, search volume, etc., information with your website plus three to five others all stacked together.
In the case of combined SEMrush keyword data for Linkarati, Moz, PointBlankSEO, Backlinko, and SEER we ended up with 13,653 rows of data. The majority of which (roughly 12,000) came from Moz.
Not bad, eh?
Step Seven: Format the Data in Excel
Now we have all of our data in a single sheet, backed up in individual sheets within the workbook as well as individual files. The data should be safe if something goes wrong during manipulation.
It’s time to format the sheet to streamline the data.
First, expand the rows so all the data is visible. I’d recommend expanding column A, F, and J.
Then bold and color row one to really make it visible, especially as you scroll up and down the sheet.
Finally, delete column E, H, I, J, K, and L. That should leave you with keyword, position, previous position, search volume, URL, and traffic %.
Now it’s time to play with the data.
Step Eight: Manipulate the Data in Excel
There are a wide variety of ways you can now manipulate this data in Excel to grab important SEO/traffic insights.
Assuming your skills are pretty basic in Excel, there are still a number of extremely easy ways to filter the data into quick insights.
First, I recommend filtering the data by column D: search volume.
Simply tab over to “Data,” click “Filter,” and hit the arrow now at the top of column D.
Choose “sort Largest to Smallest”:
That will show you which keywords you and your competitors are ranking for that drive the most impressions. Keep in mind much of this will probably be overly broad terms. As with all processes it will require manual sorting to find relevant keywords worth pursuing.
For example, at the top of search volume are terms like “Fridays” “sites” “open” “authority” and “seer.” None of these terms alone would be worth pursuing.
A little digging though reveals some surprisingly high search volume terms worth pursuing. That includes:
- [http status codes] at 135000 search volume
- [Google citations] at 6600
- [Google answers] at 6600
- [Google business listing] at 5400
- [301 redirect] at 5400
- [dmoz] at 5400
- [history of Google] at 4400
And on and on the list goes.
Sorting by search volume is particularly useful if you're engaged in creating content on your site. This can help with content ideation, keyword list building, content direction, etc.
But what about if you simply want to see opportunities for improving search volume? This is where you need to find relevant terms you’re ranking for, but not as well as you should. Even seeing where a competitor is having a near miss is helpful – all the more opportunity for you.
What you’ll need to do to highlight these within your list is conditional formatting. Let’s keep our spreadsheet filtered by search volume, but add some conditional formatting.
First, select column B “Position.” Then select “Conditional Formatting” and choose “Color Scales” and use the green to red color scheme.
This will highlight any high opportunities to improve traffic in green, since the lower you rank in search the less overall traffic you receive for the term. Of course you need to be sure the page ranking actually deserves to rank. Often adjustments will need to be made to the page to improve visibility and ranking.
This if course has longtail implications too – typically when you rank well for head terms, you’ll rank for longtail terms involved those head keywords as well.
Again, you’ll need to manually check these terms and ensure they’re worth spending time and resources on improving. But the opportunity for growth exists.
That’s but two simple ways to manipulate this data for fast and easy insight. It’s all there in front of you – you’ll just need to take the time necessary to understand, engage, and learn.
Best of luck, and happy hunting.
Recap
SEMrush is extremely powerful for competitive analysis and keyword research. If you’re new to SEO, or need to run analysis on a new website, you ought to take a look at SEMrush’s data.
Whether you’re unsure of who your search competitors are, how your website compares with other sites in your industry, or just want to do a competitor analysis SEMrush is quite handy.
The data easily pulled from SEMrush will help you:
- Determine content gaps
- Find high opportunity keywords
- Compare and contrast competitor content/ranking efforts
- Develop and guide content and SEO tasks.
So what are you waiting for? Go dig in!
