How to Perform Keyword Research for an Existing Website

Robbie Richards
December 2, 2019


It’s the lifeblood of any online business.

Without it, generating leads and feeding the sales funnel is next to impossible.

Ask any marketer how to get more traffic and you’ll inevitably hear things like:

Write more engaging content.

Send more emails.

Invest in paid traffic.

Focus on SEO.

And, while all play an important part in the overall strategy…

It’s not necessarily the best place to start.

The truth is:

You can get QUICK traffic gains without spending money on ads or writing any new content at all.


Derive more value from your existing assets.

In this tutorial, I’m going to show you how to find ALL the low hanging fruit on your website and turn it into quick traffic gains.

It’s the first strategy I use when performing content audits for new clients…

And, it can generally help an established website increase it’s organic traffic 20-25% in a few short months.

Pretty cool, right?

Let’s get started.

Identify the right traffic source

When performing keyword research for an existing website there are three different data sources to look at:

  1. Analytics data
  2. Contextual data
  3. Rankings data

In this tutorial, we’re going to be focusing on “rankings” data. Specifically, which keywords you have historically ranked for, as well as the keywords your site is currently ranking for.

This is where you will get visibility into the immediate growth opportunities on a website.


There are only a small handful of tools out there that provide quick access to real-time rankings data. The tool we’ll be using today is SEMrush.

Note: If you want to dive deeper and explore all the tool’s feature, check out the 10,000 word review I put together here.

Step #1: Collect the rankings data

For this example, I’ll be using my personal blog to demonstrate the process.

Note: Big shout out to my friend Nick Eubanks over at SEOauv. His “Master Keyword Research in 7 Days” course deserves a lot of credit for this post. And, if you want to test drive SEMrush, grab one of these free trial options.

Head over to SEMrush and log into your account:

Keyword research with SEMrush

Copy the URL of the website and paste it into the search bar on the main dashboard:

SEMrush dashboard

Hit “search”.

From the “Domain Analytics” overview report, select the “Organic Research” tab:


This report will show all the keywords a site currently ranks for in the top 100 search results in Google. For example, you can see my blog has roughly 1,900 keywords ranking in Google’s top 1o0 organic search results. If I was to bid on this same subset of keywords in AdWords, it would cost me about $8,800 a month.

Scroll further down the page and you’ll find the organic search positions table:


The table will help you find out:

  • Which keywords are ranking in the top 100 search results
  • The ranking position for each keyword
  • Monthly search volume
  • The cost-per-click for each term
  • The page (URL) ranking for each keyword
  • The percentage of organic traffic a keyword brings to a site each month
  • Level of competition
  • 12-month rolling search trends
  • Real-time snapshot of the search results page

Step #2: Export the rankings data

In less than a minute you’ve managed to find all the keywords a website currently ranks for, along with a number of other valuable data points.

Now what?

The next step is to export this data into an excel spreadsheet and extract actionable insights.

At the top right corner of the organic positions table you’ll see a button to export the data:


Click the button and download the data into an excel spreadsheet.

All the data from the SEMrush web app will be transferred:


Step #3: Filter the rankings data

So. Much. Data!

Where do you start?

Select the “Filter” button at the top of the spreadsheet. This will add a dropdown to the top of each column to better sort the data:

Filtering the data in Excel

Click the dropdown arrow at the top of the “Search Volume” column and sort from largest to smallest.

This will return a list of all the keywords your site currently ranks for in the top 100 search results, sorted by search volume from highest to lowest.

There is a lot of data here, so before we jump in any further let’s add some additional filters on top of the data to make it a little less visually intimidating and easier to follow.

Select the “Positions” tab.

Next, head back over to the “Home” tab and click the “Conditional Formatting” tab, select the “Color Scales” option and choose the “Red – Yellow – Green” scale:

Conditional formatting in Excel

This will instantly provide a better visual representation of the data.

You can see the largest traffic opportunities from organic search, and the yellow-red highlighted cells will show all the keywords with lowest but existing rankings.

This is your low hanging fruit. 

Note: You might not be able to realistically rank for some of the listed keywords, and some of the others may not even be relevant to your business.

For example, “Reddit one piece” is a search phrase that gets 6,600 monthly organic searches, but I have no interest in ranking for it.

On the other hand, there are keywords like “google adwords keyword tool”, “adwords keyword planner”, and “moz open site explorer” that are relevant to the website, but I will not be able to realistically rank for those terms any time soon.

We’re not going to invest any time or energy focusing in those areas.

Step #4: Find quick growth opportunities

This is where you mine for keyword gold.

The key is to find all the keywords that:

  1. Are most relevant to your target audience
  2. Already rank in positions 6-30

Create a new sheet titled “Keyword Opportunities”. Copy and paste the headings across from the first spreadsheet:


Next, jump back over to the first sheet and start manually reviewing the keywords. This is where the conditional formatting will make your life a LOT easier.

In the spreadsheet, you will notice all the keywords ranking on the first three pages are colored in green:

Conditional formatting highlights results in top 30 search results

As you scroll through the list only focus on the terms highlighted in green.

Very quickly I’m able to unearth some golden opportunities.

For example:

The blog currently ranks #6 for the search term “how to promote your blog” which gets 1,300 monthly searches:


I know by moving this post into the top 5 organic search positions it will instantly send more traffic to the site. So, I’ll copy that row and paste it into the “Keyword Opportunities” tab:

Paste keyword into opportunities tab

Head back over to the first sheet and continue analyzing the green cells.

Three more opportunities:

link building strategies (590 monthly searches)
lead magnet (390 monthly searches)
seo case study (390 monthly searches)

Additional keyword opportunities

Continue pasting new keywords into the “Opportunities” spreadsheet.

As you can see, this is a very scalable process.

Step #5: Streamlining the process for massive websites

Right about now, some of you might be saying:

What if my website ranks for tens of thousands of keywords?

Great question.

After all, even with conditional formatting sorting through enormous lists of keywords is a very tedious and time-consuming task.

Luckily, there is an easy workaround for this.

Head back to your spreadsheet, click the “Positions” column and deselect all the numbers:

Filtering data

Next, re-select numbers 6-20.

Now, you’re left with a spreadsheet that only contains keywords ranking bottom of page 1 or page 2 in Google:

Data only showing results in the top 20 search results

These are the lowest of the low hanging fruit. The keywords you can target for the quickest traffic gains.

Step #6: Prioritize the shortlist

The goal here is to get fast traffic gains.

So, it’s important to identify which keywords from the shortlist have the lowest level of competition – the ones that will be easiest to boost rankings.

Click the “Competition” tab in the “Keyword Opportunities” sheet and sort from lowest to highest:

Sorting by level of competition in Excel

The level of competition is rated on a scale of 0 to 1. Typically, anything below 0.4 is a good target. However, if you have a high authority site you can shoot a little higher.

Regardless, make it a priority to first target the keywords with the lowest level of competition. You’ll find it easier (and quicker) to improve rankings.

Step #7: Optimize for better rankings

The final step in this process is optimizing the existing content to improve rankings. There are a number of ways to do this.

After the shortlist of keywords has been sorted by level of competition, start analyzing the URLs ranking for each keyword.

For example, my link building strategies post used to rank in position #7, but has recently fallen to the bottom of the first page.

After looking closer at the content, I quickly get a good idea as to why that might have happened.

The post has received almost 4,000 shares:

Social engagement

Has a solid backlink profile:


And, decent on-page optimization.

BUT, it was published back in 2014:

Publish date

The content is due for a refresh.

Here is a quick overview of how I would optimize this post to move it up in the rankings:

  • Update existing tactics with new screenshots and additional information
  • Add 3-5 new strategies to the post
  • Re-promote the post across social media
  • Run a paid social media campaign to build social signals
  • Launch a light outreach campaign to capture additional backlinks
  • Add internal links from several other related posts on the site

A general rule of thumb…

When identifying areas to optimize existing content, ask yourself the following questions:

Is the content due for an update?
Do I need to make it more in-depth? Google is open about it’s preference for long form content.
Is the on-page SEO as strong as it could be? 
Is the content linked to internally from other related content on the site?
Does it need more quality backlinks to boost page authority? Look at the backlink profiles of the sites ranking in positions 1-5 for the given keyword.

These questions will help you pinpoint the areas to optimize for improved rankings.


Action speaks louder than words.

Back in 2014 I wrote a monster review of the SEMrush tool.

The post ranked bottom of page one for a number of search terms that sent a stream of organic to the blog.

Rankings started to slide, so I performed the keyword research exercise outlined above.

Here is a list of the things I did:

  • Completely re-formatted the post with Thrive Content Builder to make it easier to navigate
  • Updated screenshots to reflect the new UI
  • Added new content that covered each of the tool’s new features
  • Added in-depth action items to each section of the review
  • Submitted the updated post to Google Webmaster Tools for a quick crawl

In less than two weeks the post has jumped from #7 up to #3:


And, it is seeing improved rankings for a number of related search terms:


Wrapping it up

Before you spend hours researching new keywords and investing a ton of money on paid ads…

Focus on extracting more traffic from your existing assets.

It’s one of the easiest and most scalable ways to see quick organic traffic gains in any industry.

Need help getting more visibility in the search engines? Get in touch for a free consult.

Ready to grow?

Schedule a free 1:1 strategy session with us. We’ll unpack your current demand generation efforts, and discuss practical ways you can scale traffic and conversions in the next 60 days.

Subscribe To Our Newsletter

Subscribe To Our Newsletter

Join our mailing list to receive the latest news and updates from our team.

You have Successfully Subscribed!