Master Keyword Research – Nick Eubanks

Unlock Your Potential with the Master Keyword Research – Nick Eubanks Course on CoursesGB.

At CoursesGB, we're dedicated to providing top-quality online courses designed to equip you with the essential skills and knowledge for personal and professional success. The Master Keyword Research – Nick Eubanks course offers practical insights, actionable techniques, and proven strategies, making it ideal for learners of all skill levels. Enjoy a 100% online and self-paced learning experience guided by industry experts. Enroll now and take your learning journey to the next level!

How To Master Keyword Research

This post was originally published as a paid product in 2015. At that time I sold this information as a PDF (selling over 3,500 copies) for $127 each — but now, as the information has gotten a bit outdated (but the process is still solid, I promise) I’m publishing it here, for free.

If you find any value at all in me making my paid products free – please let me know by either dropping a quick comment or giving this post a share. Thank you.

A Quick Note

To complete this course you will need to use some 3rd party tools. All of them should have a free tier, and if you use the links I have provided in this course (affiliate links with specific tracking parameters) you will be given a sweeter deal than you would otherwise.

In addition to some 3rd party tools, this course does get technical (not writing code technical) but it does involve using some Excel formulas, which I provide to you – but you need to be ready to get your hands dirty and do the work.

However, I promise you that if you complete all 7 lessons, and follow these steps – you will truly have a grasp on how to leverage keyword research to increase both traffic and conversions for your websites.

This is the exact process I’ve developed over the past 15 years – which I’ve used to grow new websites to 100,000 organic visits per month in only 9 months and new sites to over 1 million visits in only 6 months and was the process behind the SEO success in this case study.

I’ve combined this approach to fold in websites that I have built to absorb hundreds of thousands of visits, and continue to grow.

It’s also the process I use to increase my Ecommerce stores qualified website traffic on average 40%, resulting in millions of additional dollars in revenue for my companies.

All I ask is that you take this seriously – keywords are the building blocks of organic search, and can be used to build neighborhoods of successful websites – and increase the size of those houses exponentially until they become traffic mansions.

Lesson 1 – The Nuances Between New vs. Existing Websites

Tools used in this lesson: SEMRush (<– use link for 7 day free trial they don’t offer otherwise)

To get started, I want to introduce a concept that is constantly overlooked; the nuances between doing research for new websites versus existing websites.

The difference between the two is actually much greater than you may have realized; existing websites provide the opportunity to analyze existing directional data, whereas new websites require a much greater front-load of competitor research.

I feel like this distinction is not given the attention it deserves and all of the big publications don’t speak to how to properly approach each scenario for maximum impact.

I’m all about deriving value from existing assets, so I’m going to start with how to approach doing keyword research for existing websites and then move onto new websites. If you are not looking for information on keyword analysis for existing websites please scroll ahead to the new website’s section.

Starting Keyword Research for an Existing Website

When performing keyword research for an existing website you have a few beacons of intelligence that offer you some light on which path to take:

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

Each of these data sources offers its own unique value when used to start the keyword research process.

Using Analytics Data

For the first, assuming you have been using Google Analytics, Clicky, or some other 3rd party analytics platform to capture and record your traffic data, you can look back over the previous 12 months to see what sent you 1) the highest volume of traffic, and 2) the most valuable traffic (the visits that created conversions).

Using Ranking Data

Looking at what you’ve both 1) historically ranked for, as well as 2) what you are currently ranking for  – which can provide visibility into where there’s immediate opportunity.

This is not the most straightforward path as there’s really only a few tools you can use to get this information, one being SEMRush (which is great to an extent, and for the purposes of this lesson we will be using), and the other is SearchMetrics, which is crazy expensive, but also an incredible tool.

As mentioned, for the purposes of this lesson I’m going to lean on SEMRush for 2 reasons; 1) use my link for 7 days of premium access at no cost (so you can use the full version to get the data you want without having to pay for it) and 2) if you choose to continue using the premium tool (as I do) it’s only $99 per month, opposed to all the big enterprise tool that’s are thousands of dollars per month.

Grabbing Your Rankings Data from SEMRush

Head over to SEMRush and sign up for your free 7 day account (if you don’t already have one) so you can export your data.

Once you’re logged in, enter your website URL into the top field:

That will bring you to the organic overview screen, which you can verify by looking back at the URL field up top, which should say your domain followed by (by organic).

Within this view you will notice a large table underneath the rest of the shorter tables labeled “Organic keywords 1 – x” with x being the number of rankings your current website has. On the far right side of this table, at the very top, you will see a button to “Export:”

Click that button and choose Excel:

Once your file has finished downloading, crack it open – now it’s time to do a bit of formatting.

I’m going to run through the intuitive, straight-forward pieces without visual aids so I don’t have to wad this email full of images, but for anything even slightly unintuitive I’ll be using them.

Start by selecting the first row, your header row, by clicking the #1 on the top left side. Then turn on Filters by clicking Data > Filter:

You should now have a list of all of your existing rankings sorted by highest keyword search volume.

Now we’re going to make it a bit easier to visually scan this file for opportunity’s, so head over to Column B (Position) and select the column, by clicking the B:

From the top visual menu, select Home > Conditional Formatting > Color Scales > Then the second option from the left (Red – Yellow – Green Color Scale):

You now have a visual guide highlighting your largest traffic opportunities from SEO; the yellow to red highlighted cells closer to the top are the keywords with the highest monthly search volume and the lowest (but existing) rankings.

Create a new sheet in your Excel file; call it “low hanging fruit,” or “opportunities,” or “existing rankings” – something obvious.

Now grab each of these rows, by clicking on the Row number and then copy and paste each of these individually into your new sheet. Don’t forget to also copy and paste the column headers into the top row so all of this data makes sense.

You now have a shortlist of existing rankings (with traffic) that you should be targeting. The reason you created a new sheet for this will make more sense later in the course.

Using Contextual Data

In addition to this list from SEMRush, you can go see how Google currently contextualizes your website. This is quickly and easily done using the Keyword Planner’s URL functionality.

Open up keyword planner >

Select the top option “Search for new keywords and Ad Group ideas:”

Enter your website into the URL field:

This next part is a small, but important distinction: Click the “Keyword options” panel and turn “ON” the first option to “Only show ideas closely related to my search terms,” this is so you get a true sense of how Google is currently contextualizing your website:

Click the blue “Get Ideas” button, and then from the next screen click the tab for “Keyword ideas:”

Click the download button and save this data down as a CSV, you will need this data in the next lesson.

Approaching Keyword Research for a New Website

Back to my opening points for a moment; approaching keyword research for a brand new website is very different from having all of the data above at your fingertips to jump start your process.

You essentially are starting with a dollar and a dream. What I mean specifically is you really don’t know what you need to be ranking for or how you will fair for terms important to your business; all you have is your beliefs.

So that’s where we’re going to start.

Take what you believe to be the best root keyword for your new website, and head over to Google to help us answer some questions. What you are specifically looking for is the following:

• Does this website offer my product or service, or one closely related to it?
• Is the content on this page relevant to my audience?
• What other words are being used in this context?

Use Your Ranking Barometer

Here is where you need to use some common sense. You are looking for websites that are in your wheelhouse; specifically sites that are not in the Alexa 1000. Or in other words, websites you might actually be able to outrank.

Don’t pay attention to tier-one publications like national newspapers, Wikipedia, or major media portals like Yahoo! Answers, Quora, Forbes, Inc, etc.

You are on the hunt for a webpage (more specifically a URL) that you can use to steal intelligence.

For the purposes of this example I picked search engine optimization, please note this is actually not a great keyword as the intent is informational (we’ll cover that in a later lesson) and the competition is almost purely reference, so all evergreen. But I think it will help to get my point across.

As I scan the SERP I see mostly storm clouds that I don’t want to go near; Wikipedia,

Google.com, Search Engine Land, Search Engine Watch, etc. *But* there is a glimmer of hope, I see an agency site; mainstreethost.com

That’s going to be our first mole; grab the URL and save it somewhere for reference, and let’s keep going.

The Trick To Getting Started

Is finding a contextually relevant root keyword that you can use to start you down a brainstorming path.

With the “search engine optimization” keyword example as I look over the results, I see that outside of mainstreethost.com and 1 other agency site, that all the results are informational resources – so unless I’m trying to rank reference content – this is not going to be a good fit.

Keep running additional variations and pay close attention to competitor title tags – at this stage in the process there is no need to reinvent the wheel, and it’s best to let others who have put in the work provide us with initial directional data.

Pay Attention to Paid Search Results

As I dive deeper into the search results; page 2, 3, and so on I’m starting to notice a pattern; the keywords “SEO Company” and “SEO Consultant” are showing up more and more in paid search.

These seem like they may be better conversion-focused keywords for me than the more informational keyword “search engine optimization,” so let me run a quick search for the consultant term (as it’s more relevant to me as an individual) and see what it returns.

Yahtzee!

The paid search results are for services, so is the first organic results (for Outspoken Media), and I even get a local result for an individual consultant – this is a better head term to look at.

Grab The Search Data

Now take the URL you’ve found and fire up Keyword Planner, and we’re going to run through the same process as above but using this competitive URL.

1. Click “Search for new keyword and Ad Group Ideas”
2. Enter the URL into the “Your landing page” field.
3. Set the “keyword options” to “Only show ideas closely related to my search terms” And click “Get Ideas”

On the next screen, select the tab for “Keyword Ideas” and then export this data to a CSV file. You will need this data for the next lesson.

Nice Work!

Lesson 2 – Expanding Your Keyword List

After completing the first lesson, you should now have a CSV file that you exported from Google Keyword Planner with a base of relevant keywords.

This is in no way comprehensive or very actionable, so the first thing we’re going to do is expand on potential words and phrases.

There is a bunch of ways to do this, but one of the best is using Google’s autosuggest feature, which I’ll come back to later in this lesson.

I want to review one of my favorite manual techniques for finding valuable keywords; analyzing your competitors.

Specifically we want to look at 3 buckets of keywords:

1. Conversion Keywords – Anywhere there is a direct cost.
2. Traffic Keywords – Anywhere they can edit their anchor text.
3. Engagement Keywords – Anywhere they have control over their description and search visibility is important.

Conversion Keywords

The quickest way to identify conversion focused keywords is to analyze what are being paid for.

PPC

Use this SEMRush Report to see specifically which keywords they’re bidding on.

ReTargeting

This is a bit sneaky, and not an exact science (as in it doesn’t work if your competitors don’t have an active ReMarketing campaign), but when possible it can provide some insight into which keywords they’re using to drive re-targeting campaigns, or in plain English, the keywords they consider to be valuable enough to show you ads, twice.

Scan through the results in the SEMRush Report, look for non-brand terms (keywords that are not their brand name or the name of any of their products), and write a few of them down.

Now open a browser tab, and Google the first keyword.

Look for your competitor’s ad, and click on it. Yes, this is going to cost them some money, but you need to get that paid search remarketing pixel into your browser.

In that same browser window open up 3 new tabs and go to the following websites:

1. www.linkedin.com
2. www.facebook.com
3. www.mashable.com

Do you see anything for your competitor’s website? If not, close all your windows and repeat – do this for all of the high impression keywords you grabbed from the SEMRush report.

Traffic Keywords

Traffic keywords are exactly what they sound like; these are the terms that are being targeted for maximum search traffic.

The easiest way to find these is to look at:

• Page titles for category’s, products, and any top-level landing pages
• Blog post titles
• Guest post titles and anchor text (find where they’re guest posting by searching for people’s names who are actively writing on their blog – do this using a simple query of “by Person’s Name” in Google, and make sure to include quotes (“”) around your query.
• Social profile anchor text (Linked In, Twitter, and Google+)

Engagement Keywords

Engagement keywords are the words that your competitors are going to use to position themselves in your market – often times these can be great search terms.

For instance, blog comments are a great way to glean this intelligence, here an example; on a post on MOZ’s blog about front-end content development, let’s look at the first comment:

I’ve highlighted some of keywords that jump out at me immediately, and if I had to guess Patrick is involved with website design and front-end development.

Looking closer at this profile and his company (he’s a founder), they in fact offer digital design and marketing services; perfect example of how blog comments are often used my people to position themselves and their company’s.

Additional sources for engagement keywords are:

Executive Bio’s on websites, professional and trade organizations, and LinkedIn:

Above in a dentist and here is a sales executive at content firm Brafton:

Beyond bio’s, look for language in groups (LinkedIn), Yahoo Answers, Quora, and Twitter; I almost always find some terms I wouldn’t have thought of.

In addition to analyzing sources for more conversion, traffic, and engagement keywords – there’s one more well-kept secret for finding golden keywords; your customer contact channels.

Looking at the actual language your customers use when they are looking for help or are trying to figure out if your product is right for them are usually the highest converting keywords.

These come from channels like support tickets, website contact forms, online chat, and emails between your sales or support staff. Emails are probably the most commonly overlooked data source for money keywords. A lot of pre-sales questions get answered via email, after contact has already been established.

This is really tedious and very inefficient.

The good news is people have built a number of tools to do this for you, the most popular ones being UberSuggest and KeywordTool.io. UberSuggest only looks at Google, where KeywordTool.io has filters to let you mine keyword suggest data from YouTube, Bing, and the Apple AppStore (super convenient).

I really do like KeywordTool.io, but there are 2 important data sources it’s missing; Amazon and Ebay. Being as these are 2 of the largest shopping destinations in the world – there’s a lot of important product queries happening on those websites that we want to include.

My favorite tool to crawl the suggested keywords across Google, YouTube, Yahoo, Bing, Amazon, and Ebay is actually the free keyword tool at semantic.io

The way that I like to use it is to take the 1 keyword from keyword planner that is the most relevant for my project with the highest monthly search volume.

Pay close attention to the relevance factor. For example if you have a store that sells shoes, and you have a great new model you really want to start pushing. It’s a pair of blue shoes with stripes, but you also have a lot of other variations of blue shoes. You might be tempted to enter in “shoes,” but that’s not the *most* relevant high volume keyword.

Within your initial data set you see that there’s also search volume for blue shoes, and even for blue shoes with stripes. Blue shoes with stripes is very low volume though, only 40 per month – where as “blue shoes” get 590 searches each month.

“Blue shoes” is the best keyword to use to seed your initial scrape.

What I love about Semantic.io’s tool is:

1. You can start running it and just let it run in the background, continuing to gather gobs of keywords.
2. You can add filters for words you don’t want to include in your results, including “greater than” a number of terms, as well as matching filters like contains, starts with, and ends with.

Bonus Tool

This is one of the best kept secrets in the whole SEO world; the Google Trends keyword correlation tool. This tool actually describes what it does best “Google Correlate finds search patterns which correspond with real-world trends.”

One important element to be aware of is that all of these tools are just to mine raw keywords; none of them will provide you with any search volume or cost per click data – we’ll have to go back out and get that.

I hope you found this helpful and you’re in the process of building a big list of great keywords. Keep churning through this step; it takes a few times to develop your own rhythm.

Try to get to a base of at least 5,000 keywords as when we start to tag and pair them down we will be cutting a lot of them out (usually at least 80%).

Lesson 3 – First Cut Filtering & More Data Mining

Tools you need for this lesson: TermExplorer

I know the last 2 lessons have been long, so the good news is today’s lesson is relatively short (but a bit more technical).

Now that you have that mound of raw keywords, we need to do a bit of sifting before we can go grab all the rest of the data we need.

Keyword research at its core boils down to a process of discovery and refinement; first you want to find all words that are both relevant and being searched for. Then you need to cull your list and prioritize.

Finding Your Priorities

I find it best to prioritize based on time versus value. Keywords that have a very large search volume, and are extremely competitive (which is how that tends to work), shouldn’t be your core focus out of the gate.

I find it’s best to design a strategy that supports those keywords, without focusing a lot of resources on them at any one time, and instead look for quick wins. Keyword research and building a sustainable SEO strategy is not about quick wins, but it doesn’t mean they don’t exist.

What today’s lesson is going to focus on is getting your keyword data in a place where you can start using it create your roadmap – your keyword priority list.

So today we are going to focus on making the “first cut” to cull down this list before we start collecting all the data we will need to start dialing priorities.

Quick Wins in SEO

These tend to be small but can add up to something tangible. An example might be a long- tail keyword with only 10 searches per month, but a 50% conversion rate – meaning if you rank #1, that’s an extra 2 sales each and every month.

Better still is that when you find these kinds of little “SEO gems” they tend to be pretty easy to rank for; create a page in the right place on the right site, and change some internal anchors to point at it, go grab 1-2 mid-DA links and boom, you have a new sales channel.

That’s really what keywords are – individual sales channels.

Making The First Cut

The first thing we need to do is get your CSV formatted for the process. It’s a bunch of small things you’ll click in Excel and should only take about 5 minutes, so I’m going to just run through them:

1. Open your CSV in Excel and save it as an Excel workbook
2. Select row 1 and then from the header menu click Data > Filter
3. Now click View > Freeze Panes > Freeze top row
4. Now select the column titled “Avg Monthly Searches” and sort “Largest to Smallest”
5. Select Column C, right click and “Insert”
6. Name that column “Best”

You should now have a file that looks like this:

Simple, right?

Now you have a shiny new “Best” column, and here comes the human part of keyword research; the first cut.

So I have good news and bad news; bad news first: you are going to be manually reviewing all of the keywords in this file. Now the good news, and there’s 2 pieces of good news:

1. You don’t have to be perfect; just try to think logically about the potential relevance or potential piece of content you might be able to use to target more abstract keywords as you review them.
2. This is the most manual work you will have to do in this process AND you likely have less than 1,000 keywords right now (oh yeah – we’re going to go get a whole bunch more still).

I realize this is an arduous process AND you probably thought this was going to be all clicking buttons, uploading, downloading, and all that fancy automated stuff, which is great and all, but this step is going to pay off in the end – I promise you.

Don’t tag every keyword, be picky as to what’s really relevant for your business.

Do not worry about intent at this point, regardless of whether it’s a very generic (informational) query or a juicy commercial intent or transactional query, just think in terms of relevance.

Pay specific attention as you start to get down to keywords with under 100 searches per month; this is usually where the money terms hang out.

To tag a keyword, just place a “y” (for yes) in the best column, no need to tag the “no’s.”

Go through the whole file and tag all your best keywords.

Quick Wins in Paid Search

You’re going to add another column. Select column F (Competition), right click and insert.

Name this column PPC.

We’re going to move onto one other area in this file real quick to grab some paid search opportunities. Head over to the “Suggested Bid” column and sort from smallest to largest.

Now start from the top of the “Avg. Monthly Searches” column and tag all of the relevant keywords that have a low suggested bid AND search volume (any search volume), especially since a lot of these keywords will have a minimum bid of $0.05.

Off to the Data Mines

For the last part of today’s lesson I’m going to introduce you to one of the most powerful tools in my arsenal, TermExplorer.

Term explorer (TE) is a keyword research engine on crack.

But to get the most out of all of the data we are going to be using TE to get, you need to start with the best data possible, otherwise you end up with GIGO (garbage in – garbage out).

So once you’ve finished tagging all of your best keywords, click on your “Best” column and select “Sort Z to A” to bring all of your “Best” keywords to the top of the pile.

This is going to be your seed list for TE, and speaking of TE, head on over there and fire up an account.

Once you’re logged in, go to the “Bulk Keyword Tool,” then “Start a Bulk Keyword Tool” Job, and give this data run a name (I try to always make them as obvious as possible for later). For this exercise (and just to get started) we’ll stick with a “Tiny Job” of 1,000 keywords, but usually as a bare minimum I go with a “Small Job” which will generate 10,000 keywords.

See the part where you’re supposed to enter your keywords?

Remember those “Best” keywords you just got done tagging?

Copy and paste them in here, and start your job. Keep this tab open and don’t close down your computer until it completes (it shouldn’t take too long).

Great Job!

You’ve just completed the 3rd lesson. In tomorrow’s lesson we’re going to maul over all the shiny new keyword data that TE is going to give us, I’m going to show you how to quickly filter through all of this data right IN TE, and then we’re going to run a report to grab even more data.

Lesson 4 – Keyword Sorting & Conditional Analysis

Let’s dive into TermExplorer and pick up right where we left off yesterday.

Once you’ve logged back in, go to Bulk Keyword Tool > View Saved Bulk Keyword Tool Job and open up your saved job from yesterday.

You should now be looking at a screen like this:

In today’s lesson we’re going to use some of the scrubbing tools in TE to narrow down our list to just the best targets, but first – we want to capitalize on all of the search volume data that TE went out and got for us on this initial set – so you want to Export the current CSV (before we start chopping it up), BUT FIRST (and this is very important), scroll all the way to the bottom left-side of your screen and click the “Display” dropdown and select “All,” otherwise it will only export the data on the screen.

For today’s exercise we’re going to focus on boy and head terms, so we want to first grab

all of the lower search volume (long tail keywords) and save these down into a separate file. So using the filters box on the left-side of your screen, type “30” into the “Less than Local Searches” field like so:

Now check and make sure that the “Display” is still set to “All,” and Export the CSV, save it, and set it aside for later.

Now we’re going to start work on pruning down this list, so you’re going to delete the “30” from the “Less than” field and now type it into the “Greater than” field (it’s just the next one up).

So after applying the minimum 30 searches per month filter my keyword list went from 1,246 to 1,055.

Next we’re going to start building our list of negative keywords to filter this list against. Negative keywords are specifically words that make a keyword irrelevant to your business or product.

So for this exercise I’m using keywords related to SEO, so reading through some of the suggested keywords in TE, I see the following words that are not going to be relevant to my blog and want to filter them out:

• software
• montreal
• vancouver
• guarantee
• cms
• cheap
• matt cutts
• denver
• checker
• nashville
• black hat
• dallas
• houston
• chicago
• st louis
• quake

It should look something like this:

*PLEASE NOTE*

Term Explorer is a fantastic tool, but running queries in a web app is harder than it may seem – so occasionally I’ll be in the process of building my negative keyword list and the data will go to 0 and show “your search has not yet returned any keywords.”

Don’t Panic. The best thing to do is to copy and paste your list of negative keywords into somewhere easily accessible (Google Doc, email, notepad, whatever), then click the green button to “Clear Filters.”

Once the data reloads, enter the number 30 back into the correct field, paste in your negative keywords, and click the button to “Apply Filters.”

Keep Going

Keep filtering down your list until you start seeing only highly relevant keywords. For this specific list I would recommend trying to get it down to under 500, but as long as it’s under 800 you’ll be good.

Ready for the best part?

Gathering Competitive SERP Intelligence Data

Now that you’ve got your list tuned to a specific set of highly relevant keywords all with a base of 30 searches per month, it’s time to have TE go gather the rest of the data we need.

Click “Select All” then click “Send Checked keywords to Keyword Analyzer:”

You should now be looking at this screen:

Give this file a name (Tip: make it the same as the bulk keyword job but add the word “Analysis”).

Then click the blue “Start Project” button.

Now TE will go out and begin aggregating all of the competitive intelligence data we are going to need. This is a very resource intensive process (you’ll see why when all the data comes back) so it can take a few hours for this to complete depending on how many jobs are concurrently hitting the server.

Get Master Keyword Research – Nick Eubanks , Only Price $37

Once this is completed you will see a screen like this:

Looks interesting right? The best part is this is only the very surface of all of the data that TE actually just went and got for you. To get to all of the data we want (and are going to need for this lesson), you will need to Download the CSV (don’t forget to check and make sure the “Display” is set to “All”).

Open your shiny new CSV file and save it down as an Excel workbook.

Now we’re going to go do a whole bunch of color coding to make this file into a useable document.

First though you will want to do a bit of spot checking to make sure all of your data is clean (TE tends to mess up alignment on at least ~5% of rows due to strange breaks and characters in some of the URL’s.

The fastest way to clean this up is to turn on filters (Data > Filter), go to the furthest right columns with data in them, then go one more over (it will most likely be Column W) and sort from Z to A; it will look something like this:

Then when you find all of these rows, highlight them yellow (this is just for temporary reference), and scroll back to the far left side of the columns.

99% of the time the problem is some funky URL’s, so we’re going to go delete all the URL funk (don’t worry we’re not losing anything important here) and have the rest of the cell data shift left so it lines up which the correct column headers”

Then once we delete and shift cells left, we have a nice clean data set to work with:

Now we go to work.

Getting Started with Conditional Formatting

The first thing we’re going to do with our new mountain of competitive SERP data is to color code all of the columns to make it easier to visually scan for outliers (opportunities).

First we’re going to start with ‘Global Searches’ and sort from the highest to lowest (using the Data > Filter):

This gets our keywords quickly sorted by largest traffic opportunity (not necessarily the *best* opportunity in terms of intent and relevancy; just the largest average monthly search volume).

Now add a bit of formatting to make it easier to visually scan; click the comma button then decrease the decimal’s by 2 places (this is not required, just a personal preference).

Now highlight ‘Column C’ and then navigate to Home > Conditional Formatting > Color Scales > Green – Yellow – Red (the first option):

Now we’re going to run through the rest of the data in the sheet and add the same filters, switching between the ‘Green – Yellow – Red’ and the ‘Red – Yellow – Green’ (the second option in which is the inverse of the first, i.e. the larger the number the more red versus the more green) – this is specifically to highlight where competition is too high ‘at a glance.’

To make this a bit easier to run through and reference quickly, I’m going to use abbreviations for each series; GYR for Green – Yellow – Red, and RYG for Red – Yellow – Green.

Skip columns G through I and S for now, these are logic statements and require different formatting, so we will come back to these.

Here’s a quick cheat sheet for which columns should get which filters:

1. Search volume – GYR
2. Average CPC – RYG & add $
3. Number of Keyword Occurrences on Page – RYG
4. Total Word Count on Page – RYG
5. Number of Outbound Links – GYR
6. Relevancy Score – RYG
7. Page Backlinks – RYG
8. Page PageRank – RYG
9. Domain Age – RYG
10. 10. Domain PageRank – RYG
11. 11. Domain backlinks – RYG
12. 12. Trust Score – RYG
13. 13. Link Strength – RYG
14. 14. Difficulty Score – RYG

Your file should now look like this (I’ve hidden columns G-I & S to highlight conditional colors):

Now onto the columns with 0’s and 1’s: for these we are also going to use conditional formatting, but a different option; ‘Highlight Cell Rules’

Select columns G I, then go to Conditional Formatting > Highlight Cell Rules > Equal to > Format Cells That Are EQUAL To: 1 with Light Red Fill with Dark Red Text. The ‘1’ represents TRUE, and since these are all keyword conditions, if TRUE than they are more competitive.

Now repeat the exact same except set Format Cells That Are EQUAL To: 0 with Green Fill with Dark Green Text.

Now for Column S (Is Domain an EMD; exact match domain), do the same; 1 = TRUE set to RED as these will be likely more difficult to outrank.

Your file should now look like this:

The best part about this is all of these filters are dynamic and will stick to the cells, so in later lessons as we filter through this data set now we have an easy interface to quickly identify opportunities.

That’s it for today, I know it was a data heavy lesson – but you made it, so as my nephew says “Big Job!”

Lesson 5 – Analyzing Keyword Intent

Today we’re going to continue your journey toward mastering keyword research by dissecting some of body parts of keywords to better understand their searcher’s intentions.

Understanding search intent is fundamental for driving visitors through your conversion funnel and knowing how and when to ask for the sale.

Just like there are 4 stages of the conversion funnel (AIDA), there are 4 types of keyword intent. What’s more is that they actually directly mirror the conversion funnel:

The 4 buckets of keyword intent are defined as follows:

Informational – Informational search probably covers the largest bucket of keywords, and is generally representative of users looking for a quick answer. These are people looking for a phone number, directions, or even a piece of recent news, such as a sports score or headline.

Navigational – Navigational search is when a searcher is looking for a pre-determined destination. Think of the people, most likely your parents, who still type a domain name into the search box.

Commercial Investigation – Commercial Investigation is, as Fishkin notes, “straddling the line between research and commercial intent.” These are queries where the searcher is looking to gain information to help them inform a buying decision, even if they do not convert; this is the gathering of information that has the potential to later lead to a sale.

Transactional – Transactional is the obvious one: these are queries where the searcher is looking to make a purchase, find a place to make a purchase, or complete a task. These can range from queries looking to make a purchase online, to looking up the address of a store, to signing up for a service.

In a study conducted in 2007 by Penn State researchers that looked at over a million queries submitted for by several hundred thousand searchers. They uncovered visibility into common intentions and preferred use of search engines.

Findings indicated that approximately 80 percent of the queries classified were informational, with the remaining being split almost equally between navigational and transactional.

My favorite part about the study is that they look at some of the specific characteristics of the informational queries:

• They use question words like “ways to,” “how to,” “what is,” etc.
• They contain informational terms like “list,” “playlist,” “top,” etc.
• The searcher tends to view multiple results pages.

What’s more, the study compared its results to another piece of research where they studied queries of “non-meta search engines,” and found the largest session sizes were actually for searchers running one and three queries, at 53.9% and 29.4% respectively, versus users who ran two queries, making up only 16.6% of the sample.

Example Intent Modifiers

At this stage in SEO evolution, there has been enough data analyzed to uncover some common patterns of search intent among keywords.

Here are some of the most common modifiers to look for when tagging your keywords for search intent:

Informational: The key characteristic to an informational query is that it is non-commercial in nature, and the searcher’s goal is just to gain information – not make a purchase or relinquish any of their information. These are most commonly keywords containing terms such as: info, more information, information, details, details, features, benefits, definition, location, directions, [person] name.

Navigational: Indicators of familiarity with you, your company, or your product(s). These are most often referred to as “brand” terms, as these keywords show an existing level of awareness for your brand or its products. These searchers are simply using search engines for their convenience is finding you. In ecommerce especially, navigational keywords tend to generate the most revenue – hence the importance of ranking well for keywords containing your brand name(s).

Commercial Investigation: These keywords show the searcher is relatively familiar with the product they want, and are now just trying to gain the final bits of intelligence to assist with their decision. The most common keywords that indicate commercial investigation are: small, medium, large, kids (sizes); mens, womens (sex); black, blue, gray, orange, pink, red, green, purple (colors); versus, vs, best (comparative); price, pricing, reviews, deals, accessories.

Transactional: Transactional keywords are what I like to call “credit card in hand” terms, these searchers are ready to buy and just looking for where they can purchase. These are often longer, and much more specific queries such as; buy, purchase, sale, coupon, promo code, discount, online, free shipping.

Example Funnel to Intent Content

The best way to understand how keyword intent impacts the visitors experience is to look at real world examples in the wild of how this content serves its target purpose.

Here are example pages at each stage of the conversion funnel:

Informational

Keyword: backpacking tents

#1 search result: http://www.rei.com/c/backpacking-tents

Now the searcher has been exposed to the REI brand of tents, and if not engaged on the page, may bounce back to Google and use a navigational term.

Navigational

Keyword: REI tents

#1 search result: http://www.rei.com/c/tents

*This term is also a commercial investigation term, but that is going to be the case for most online retailers (Ecommerce websites), if searchers are looking for product queries containing your brand name – they are further down the funnel than searchers using informational terms.

Commercial Investigation

Keyword: gray backpacking tents

#1 search result: http://www.sportsmansguide.com/product/index/hq-issue-backpack-tent- gray?a=1687375

Transactional

Keyword: buy gray backpacking tent

#1 search result: http://www.amazon.com/HQ-ISSUE-Backpack-Tent- Gray/dp/B00JN905HC

*OBSERVATION*

I chose this example to highlight something that I see more and more online retailers doing, and personally it makes me nervous (and is not something we do at Traffic Safety Store), being outranked by Amazon for our own product for the transactional keyword.

Here’s the Amazon page:

It’s the exact same product as the results page for “gray backpacking tent” from Sportsman’s Guide; the HQ ISSUE backpack tent in gray.

Now I will give credit to Amazon, they do tend to crush rankings for transactional terms – what’s funny in this specific example is look at the retailers page (the one that ranks #1 for the commercial investigation keyword):

Notice anything? The price is exactly the same as Amazon unless you’re a Buyer’s Club member (in which case it’s $4 cheaper).

Where the Money Is

Visitors are most likely to make purchases in the latter half of the funnel, commercial investigation (they are seeking further reinforcement before making a purchase) and transactional (they have their credit card out or are logged into Paypal and ready to pull the trigger on the sale).

Tagging For Searcher Intent

As part of prioritizing your keyword list and mapping them to your content strategy and editorial calendars – you should tag for intent; and make sure you are ranking your informational (top of the funnel) content for informational keywords, then category pages for commercial investigation keywords, and product detail (or conversion focused pages) for transactional keywords.

The way I like to do this is to create a new column in the Excel file titled “Intent.”

For smaller sets of keywords you can do this manually, but for larger keyword lists (those over 1,000 keywords) I’ve written an Excel macro that with a little bit of configuration you can use to automatically do all the tagging for you.

Here’s a link for a free copy of the macro template.

There are instructions on the Gumroad purchase page for how to use it – it’s pretty straightforward and has saved me a TON of time.

Here’s a tutorial on how to save the macro to be used on any file across all your Excel workbooks.

*NOTE: Semantic.io does this all automatically for you

I hope you have a much better understanding of keyword intent – that’s all for today’s lesson. In the next lesson we’re going to bring this all together and I will step you through the process for identifying low-hanging fruit keywords, short-term and long-term priorities, and backing in how to find the most valuable, long-term targets.

Lesson 6 – Keyword Prioritization & Creating Your Target List

Hopefully you spent some time going through your file yesterday and familiarizing yourself with search intent. I added in my Excel macro to my file and ran it, so I’m all tagged up and ready to start sorting and filtering through all this organized data to identify priorities and slice out different types of targets.

The gist of today’s exercise is to find the cross sections between high value (high volume and/or high intent) with low competition.

In today’s lesson we’re going to cover a range of exercises:

1. Identify opportunities to crack page 1 rankings for high volume / high value terms
2. Identify opportunities SERP’s that can be easily attained – potentially including Top 5 rankings
3. Identify opportunities to gain instant high value / low cost traffic with PPC

Opportunities to Crack Page 1 for High Volume / High Value Terms

What we’re looking for here is bands of green across the row (remember how I told you all that conditional tagging was going to be useful?) for URL’s that are currently ranked at #10 for high volume keywords.

First thing you need to do is sort ‘Global Searches’ from ‘Largest to Smallest.’

Next you need to sort Position (Column A) by Number Filters > Top 10

Your file should look something like this:

Now highlight all of this data starting with the header row (pictured in Row 6 in my screenshot above) and copy and paste this into a new sheet (make sure you use the “paste option” for “values only” so you only bring over the filtered data); this is your high volume PAGE ONE list.

Now we’re going to do the exact same thing for ‘High Value’ terms on page one, so first sort your ‘Intent’ column for ‘Commercial Investigation’ and ‘Transactional’ by unchecking the checkboxes in the sorting dropdown for the other 2 intent types.

Your file should look something like this:

Notice the URL column I highlighted? This is the best part; you can now completely dissect

these specific URL’s to figure out the minimum amount of work needed to crack page one for these keywords; and as you scroll to the right – look for rows with more green tinted cells, as they will be the easiest keywords to rank for.

Just like last time, copy and paste this data into a new sheet – these are your ‘High Value’ page one targets.

Low Authority SERP’s

Next we’re going to filter for SERP’s that have the lowest barrier to rank for – what we’re looking for specifically is low domain authority and a low number of page backlinks.

So first you need to clear the ‘Top 10’ filter from Column A (Position) and the ‘Intent’ filter (I used Column C) – since these are perpetual filters (meaning they will stay on until you turn them off).

Now sort your ‘Domain PageRank’ column from ‘Smallest to Largest,’ and then filter your ‘Page Backlinks’ column by ‘Number Filter’ > ‘Less Than,’ and enter a number based on your confidence in building/acquiring/manufacturing links (I chose 30).

Your file should look something like this:

Select all of the data from Domain PageRank 0 to Domain PageRank 3, and just like before, copy and paste this data into a new sheet – these are your low-hanging fruit terms. In addition, you can add more perpetual filters such as Intent (for high value terms) or global/local volume over 50 for searches that have a reasonable amount of searches and are still high intent (value).

Opportunities to Gain Immediate High Value / Low Cost Traffic via PPC

Since the data is all at our fingertips at this point, I want to show you a really easy way to find high intent keywords that you can use to get high intent visitors to your website for less than $1 each (or $0.50, $0.30, or even $0.10).

First thing – clear ALL THE FILTERS from you column headers.

Go back to your ‘Intent’ Column and select the checkboxes for “Commercial Investigation” and “Transactional.”

Set a filter on your ‘Global Searches’ Column for ‘Number Filters’ > ‘Greater Than’ > 50

Set a filter on your ‘Average CPC’ Column for ‘Number Filters’ > ‘Less Than’ > Enter a cost figure that you’re comfortable with, I chose 1.00.

Your file should look something like this:

Notice anything? The top of your list is literally filled with keywords that are high intent, have search volume, and have NO MINIMUM BID; this means for as little as $0.05 per click you can get high value traffic right to your conversion pages.

Pretty cool right?

Keep playing with all of these filters now that you have ALL THE DATA’S and see what you can come up with – if you find anything particularly cool, please reply to this email and let me know. I’ll include the tip in a round-up post I’m working on for SEONick.net (and of course give you credit and a link).

In the next lesson we are going to take all of these keyword lists and build out an opportunity model to figure out the relative costs and returns from SEO.

Final Lesson – Finding Keyword Opportunities

You made it!

Welcome to Your Final Lesson, I had to resist making a really bad Star Wars joke there, but I bet you can guess what it was. Anyway – you’ve made it to lesson 7 – nice job.

But we’re not done yet, and today’s lesson is a BIG one, both in terms of value and also literally; this is the biggest lesson in the course, so grab a glass of water, stretch, and get ready for some serious keyword analysis.

In today’s lesson we’re going to take all of the hard work you’ve put into building out your pile of keywords data, and turn it into an actionable list of opportunities.

By the end of today’s lesson you will have a roadmap for which keywords you should be targeting immediately, what kinds of content you should be creating to establish the right kinds of rankings for keywords at each stage of your conversion funnel, and finally – how to spread your energy and resources out for maximum impact.

A Quick Disclaimer

Before we dive into the model I’ve developed to project potential returns from organic search, I want to first clarify that this is *not* an exact science – with well over 200 signaling attributes for rankings AND more than anything, with the constantly changing interfaces Google is creating for different types of queries; not all keywords and not all SERP’s are the same.

Furthermore, webmasters leveraging search as a traffic acquisition channel are continuing to see a divergence in the CTR’s between desktop and mobile SERP’s. There will never be a “good” model for projecting CTR across a range of SERP’s, but you can begin to dial in potential CTR on a SERP by SERP basis – so please keep that in mind.

With all that said, the most recent study I’ve seen that had 1) a reasonable sample size

(over 2 billion impressions from over 2.2 million keywords and over 2.68 million clicks) and 2) adjusted their CTR curve for both desktop and mobile, was done in late October by seoClarity.

The data graphs:

My Keyword Opportunity Model

All this model attempts to do is give you the methodology from which to customize for your own keywords, bringing in your own historical data from previous rankings, the traffic acquired, and Google Webmaster Tools will allow you to get as close as possible to accurate projections.

Moving on – the first thing we need to do is establish some assumptions that we will use to evaluate potential opportunities.

The key assumptions we need to make are:

• Rank Click-through Rate – the average click-through rate (or percentage of average monthly search volume) that is expected from a given search engine ranking.
• Conversion Rate – the average rate at which a visitor to a web page will convert on your offer (this could be a purchase, form submittal, additional click-through, email sign-up, etc.). This will differ dependent on the intent of the query, and we will come back to that later this lesson.
• Average Revenue Per Conversion – For the purposes of actually projecting revenue per keyword we are going to assume that “Conversions” here equal money. The average revenue per conversion will represent that amount of revenue realized per sale, averaged over the population of sales. For an Ecommerce store this would be represented by average order value.
• Average Cost Per Page – This is a blended figure and needs to contain your average cost to have a new page created (written, designed, and coded), published (added to your website, given a URL, added to your sitemap and navigation), and indexed.
• Average Cost per Link – This represents the average amount you spend to acquire a link from a new linking root domain for your new URL. If you have an agency building 30 links for you for $1,500 per month, your average cost per link is $50.If you own and operate your own network, this is the cost of your time averaged down to how many links you are building per hour at your hourly rate (to factor against opportunity cost). For the purposes of this lesson I am counting all links as equals (I realize this is a large abstraction from the actual power of links, but I need to normalize this somewhere to make it generally applicable).

One More Note on Links

For the purposes of this exercise, I’m using the term link to represent a link with a minimum Domain Authority (DA) of 25 or Domain PageRank of 2, Page Authority (PA) of 30 or Page PageRank of 1, and Trust Flow (TF) or Citation Flow (CF) of 15, from a new linking root domain (not additional links added from domains that already link to your target domain).

Also, link count numbers are likely to change, and often. It’s important to complete this

analysis in a timely manner (like 1 week) so the data is as accurate as possible at least during your analysis – moving forward it’s important to use tools like Ahrefs, majestic, and even MOZ’s fresh index to stay on top of new links coming in and old links dropping off.

The formula for calculating potential keyword value from SEO is actually pretty simple:

The formula for calculating the projected cost for gaining the rankings to reap this value; is not.

So far the best approximation I’ve come up with is to evaluate the authority metrics per keyword at the SERP level.

So for example, using my keyword file for the term “seo services” the averages look like this (down in the bottom in the row I highlighted with bright yellow):

A Quick Analysis of This Data

So based on these metrics, unless I have either 1) a very authoritative domain (PR5 or higher) or 2) I’m able to create a very authoritative page, likely through a mix of very high DA links with varying anchors around words and phrases including and contextually related to “seo services,” I should probably stay away from this keyword as a target – at least globally.

But here’s my favorite part about this data set that we have now put together; 2 things jump out at me quickly: a URL ranking #2 with only 40 page backlinks (but a domain PR of 7) and URL with a domain PR of 2 with 1,467 links.

That actually seems potentially doable – let’s grab that URL and take a quick look; https://managedadmin.com/solutions/phoenix-seo/

The content on the page is thin at best:

And the links are, well… I’m really not into outing so I’ll just leave these screenshots here quick from some of the most powerful links they have, and let you draw your own conclusions:

Example linking site #2

Not to stray too far afield, but after looking at a quick sample of links from this site – I would include this keyword on my 6 to 12 month roadmap, since it has both commercial investigation intent and nice search volume with an average of 8,800 global searches per month.

And moving on…

Go to Your Low Authority SERP Sheet (from Lesson 6)

We are going to use this data to cull our short-term priorities list into 2 parts:

1. Priority terms for 1-3 months, and
2. Priority terms for months 4 through 6.

The first thing we need to do at this point in the process is to sort this list into the 2 buckets I just mentioned so we can chop out all of the additional authority metrics since we won’t need them anymore.

I realize this is counter-intuitive since we worked so hard to get them, but you still have your master sheet that you will be able to use as a master index to reference all the metrics; and once the analysis is done and decisions are made it all becomes noise.

One Quick Note for Your Sanity

The process we are about to run-through, configuring and formatting a file to calculate approximate keyword development costs and potential returns is a bit intense for anyone whose day job is not being a financial analyst.

I have every confidence that this walk-through is straight-forward enough that you can complete the process, but for the purposes of doing this exercise together – and you not getting completely burned out, I’m going to run through building out one model using your Low Authority SERP data. Tomorrow (or perhaps even the next day) you will need to run back through this process on the other 2 priority sheets we carved out yesterday.

Each sheet will be its own set of opportunities; this first one is your 6 month roadmap, broken up into 2 chunks; short-term (1-3 month priorities) and mid-term (4-6 month priorities).

Remember this course is designed to teach you the methodology and give you the understanding of the process and the “why” so you can replicate this over and over again for your websites or your client’s websites.

Back to the Data

Before we start axing existing rows – we need to add one (that won’t be chopped out). So to the right of your intent column add a new column, and name it “Priority.”

Now turn Data > Filter back on, select the “Intent” Column dropdown, and uncheck all the boxes except for “Informational.”

For this exercise we are not going to look at brand keywords since these are generally the easiest to rank for, so our priority scale will be from 1 to 3, with informational intent keywords being last on that list. So enter in the number “3” in the top cell of the Priority column and use the little black square at the bottom right corner and drag it down to fill all of your informational keywords with a Priority of 3:

Now do the same for “Commercial Investigation” but use the number 2, and then again for “Transactional” but use the number 1.

Go back to your “Intent” dropdown > Select All > OK. Select your “Priority” Dropdown > Sort Smallest to Largest, and your file should now look something like this:

And now it’s time to start slicing. Select Column B (Keyword) > Data > Remove Duplicates:

When the dialog boxes pops up asking if you want to “Expand your selection” click “Remove Duplicates” > Unselect All > Check ONLY “Keyword” > OK.

You should now be left with a unique list of keywords:

Time to break out the Axe; delete (or hide if that simply scares you too much) the following columns:

• Position
• URL
• Number of Outbound Links
• Relevancy Score
• Page Backlinks
• Domain Age
• Domain EMD
• Trust Score
• Difficulty Score

And you should now be left with a file that resembles this:

The goal now is to get this list down to 100 keywords or less (but ideally right around 100), which will eventually be split into 2 lists; short-term and mid-term.

There’s also some clean-up that I like to take care of at this point, but in the future you can do it sooner in the process if you prefer.

I sort “Global Searches” from “Largest to Smallest” and then add in a quick formula into the “Priority Column” that will fill in Priority as I tag intent on keywords that may have been missed as we used bulk processes.

The formula is a simple string of IF statements;

=IF(B6=”Informational”,3,IF(B6=”Commercial Investigation”,2,IF(B6=”Transactional”,1,”N/A”)))

Then drag that formula down into all the rest of the “Priority” column cells.

Now I filter intent for all keywords that aren’t currently tagged with intent by unchecking all the intent types, and I run through and manually tag intent. Do your best – it doesn’t have to be perfect, but this is an important manual step so you don’t miss any golden nuggets that may be hiding.

This quickly took my count of n/a keywords down from 147 to 80, and helped identify a ton of additional commercial investigation terms and a few transactional keywords.

Cut out as many irrelevant keywords as you can; for me in my example data set it’s all the localized queries outside of my local area (Philadelphia).

Now We’re Getting Somewhere

We need to add in a couple of rows up top to house the assumption data I mentioned earlier.

Use the following columns for the following assumption data:

• Column A Title: Revenue Drivers
• Column B: Actual Data
• Column C Title: Average Costs
• Column D: Actual Data
• Column E Title: SERP CTR
• Column F: Actual Data

In Column A enter the following:

• Conversion Rate (this is a sub-heading)
• Brand
• Info
• CommInv
• Trans
• Avg $ per Conversion

So for conversion rates you can choose to go 1 of 2 routes;

Enter in a gross average conversion rate next to the sub-heading, likely a figure between 1 and 2%, or

Enter in individual conversion rates based on query intent, for instance on one of my ecommerce sites I would use the following:

• Brand:22%
• Info: 2.2%
• CommInv: 3.5%
• Trans: 4.8%

And for Average $ per Conversion I’m going to use an actual average order value figure from this same website: $280.

All of these figures should be entered into the Actual Data column, so they can be used to drive computations down in the data and be adjusted in only one place.

Also, to clean up the initial development of the file, hide the binary keyword columns (G-J), your file should now look like this:

Calculating Average Costs

Like everything else in SEO, this can also go a number of different directions; the more simplistic route (if you prefer) or the complex.

The simple approach is to try to calculate an average production cost and assign a nice round number per link. The more complex approach is to back in your actual employee or contractor’s hourly rates, the average rate of production, and then blend the cost to have a piece of content written, assets designed, your content manager (or web person) to publish, and then you (or your SEO person) add it into the sitemap, navigation, tweet it out, and submit it to Google.

Then you have your link costs; I’m going to use a mix of placed and partner links – represented by a blended rate of $75 each. Again, I’m assuming these links meet the criteria I specified earlier in the lesson.

For the purposes of this exercise I’m going to walk-through some basic cost elements but then use a simple approach within my file.

In my file under Column C I’m going to list out:

• Content Cost (~1,000 words)
• Asset Cost (graphics)
• Production Cost (includes all steps to get it live on the site and added into all the appropriate places)
• Link Cost
• Total

Time to Add More Columns

Depending on which conversion rate route you’ve taken, you will need to add in a “Conversion Rate” Column, which I like to abbreviate as CR, as either a static value column (linked directly to your CR assumption), or as a variable which I’ll explain below.

Conversion Rate

If you chose to go with option 2 (which I did) then you need to add this column AND add in another simple IF formula so it pulls the right conversion rate (based on the keyword’s intent).

The formula needs to reference the relative location of the row variable (the intent) and then the static conversion rate you defined in your assumptions above, to do this use anchor cells, i.e. instead of B9, to anchor the cell reference you would use $B$9 with this formula:

=IF(B9=”Informational”,$B$4,IF(B9=”Commercial Investigation”,$B$5,IF(B9=”Transactional”,$B$6,$B$3)))

Then drag this formula down through the rest of the CR cells. This formula also defaults “non-matches” to brand conversion rates, so if you didn’t finish tagging all of your intent then you will need to set a more reasonable (likely lower) conversion rate assumption for this intent type.

Revenue

Next to your Conversion rate column add another new column titled “Revenue.”

This column will be a summation column using the formula mentioned above; =(Global or Local Searches * CTR)*(Average $ per Conversion * CR) where CTR is SERP Click- through rate.

You can base the click-through rate on a very conservative value (say Desktop #10 = 2.2%), on a #5 ranking, or as I like to at this stage in the game; an average of the top 5 desktop CTR’s = 9.54%.

If your columns are all laid out the same as mine this formula, with variable and anchor references, is:

=(D9*$F$7)*($B$7*Q9) then drag that formula down to all of the revenue columns underneath.

Cost

For the cost portion of this formula, specifically for this exercise, we are going to use an over-simplification; URL development cost (the sum of all your per URL costs) plus the number of links of the ranking URL (+1) multiplied by your per link acquisition cost.

So =$D$5+((M9+1)*$D$6)

If you want to dive deep in the weeds on how to approach cost approximation, check out my post that goes into this specifically.

Breakeven as ROI

For our final piece of this pie, and since the revenue cell in each row is showing you your monthly revenue from this keyword, we are going to calculate how long it will take until you break-even on your per-keyword investment.

To do this divide your cost by your revenue and the result will be the number of months that it will take until your revenue equals and then exceeds your investment.

Sorting for Opportunities

You can probably guess what comes next; now we sort our Break-even Column from smallest to largest, and separate at above 3 and below 3.

Above 3 but less than 6 are your mid-term priorities and anything less than 3 is a short-term priority.

At this point your file should look like this (I threw in a conditional color for some flare):

Creating Content Based on Intent

The last step to putting this into action is to start thinking about which pages are best suited to rank for your different keywords – and not they keywords themselves per se, but the keyword types.

For informational content, resource pages are going to generally be a good fit – usually the more evergreen the better.

For your brand searches you want to make sure your homepage ranks for the more general terms and that your product pages absolutely rank for any brand query containing a product reference; this is why it’s important to have your brand name in all your page titles; to create passive relevance site-wide.

For commercial investigation keywords it depends on the site’s purpose; for Ecommerce these are best served by ranking category or sub-category pages. For software or service content you want to rank more informative content that paints your offering in a positive light, such as comparison pages, benefit pages, or case studies.

And lastly, for transactional keywords you want to rank your detail pages, product sales pages, landing pages or any page where a conversion can occur directly, not a doorway  page. IF you manage to rank a page that is one funnel step above your transaction page for a transactional keyword – consider re-directing that page to your transaction page and creating a new page at a new URL to fill its place.

Wundebar!

I hope you found this course valuable and you continue to practice with these lessons until you build enough muscle memory that they become second nature.

For more continuing SEO education make sure you follow me on twitter.

What’s Next?

Now that you’re left with a prioritized list of keywords to go after, you need to design a strategy focused on building rankings. If you found this guide useful, I recommend you check out my guide on how to implement a keyword strategy.

Looking For Additional Support?

I’ve teamed up with Matt Barby (Director of Acquisition at HubSpot) and Ian Howells (Director of SEO at LendingTree) to launch Traffic Think Tank, the #1 private SEO training community in the world. Join for personal, hands-on support for your SEO projects (or client projects) and instant access to over 300 hours of member exclusive content.

Get Master Keyword Research – Nick Eubanks , Only Price $37

---

Tag: Master Keyword Research – Nick Eubanks  Review. Master Keyword Research – Nick Eubanks  download. Master Keyword Research – Nick Eubanks  discount.

Unlock Lifetime Learning with the Master Keyword Research – Nick Eubanks Course at CoursesGB!

Invest in your future with the Master Keyword Research – Nick Eubanks course at CoursesGB and gain lifetime access to expertly crafted content designed to boost your career and personal growth.

• Lifetime Access: Enjoy unlimited access to your purchased courses.
• Secure Payments: Shop with confidence using our secure payment methods (PayPal, Stripe).
• Practical Knowledge: Empower your career with actionable, real-world skills.
• Instant Access (Most Courses): Start learning immediately after purchase via your account dashboard or email.
• Learn Anywhere: Access your courses on any device (desktop, mobile, tablet).

Start your learning journey today with CoursesGB!