Master Keyword Research – Nick Eubanks

How To Master Keyword ResearchThis 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 NoteTo 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 WebsitesTools 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 WebsiteWhen performing keyword research for an existing website you have a few beacons of intelligence that offer you some light on which path to take:Analytics dataRankings dataContextual dataEach of these data sources offers its own unique value when used to start the keyword research process.Using Analytics DataFor 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 DataLooking 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 SEMRushHead 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 DataIn 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 WebsiteBack 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 BarometerHere 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.comThat’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 StartedIs 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 ResultsAs 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 DataNow 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.Click “Search for new keyword and Ad Group Ideas”Enter the URL into the “Your landing page” field.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 ListAfter 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:Conversion Keywords – Anywhere there is a direct cost.Traffic Keywords – Anywhere they can edit their anchor text.Engagement Keywords – Anywhere they have control over their description and search visibility is important.Conversion KeywordsThe quickest way to identify conversion focused keywords is to analyze what are being paid for.PPCUse this SEMRush Report to see specifically which keywords they’re bidding on.ReTargetingThis 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:www.linkedin.comwww.facebook.comwww.mashable.comDo 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 KeywordsTraffic 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 pagesBlog post titlesGuest 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 KeywordsEngagement 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.ioThe 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:You can start running it and just let it run in the background, continuing to gather gobs of keywords.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 ToolThis 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 MiningTools you need for this lesson: TermExplorerI 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 PrioritiesI 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 SEOThese 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 CutThe 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:Open your CSV in Excel and save it as an Excel workbookSelect row 1 and then from the header menu click Data > FilterNow click View > Freeze Panes > Freeze top rowNow select the column titled “Avg Monthly Searches” and sort “Largest to Smallest”Select Column C, right click and “Insert”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: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.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 SearchYou’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 MinesFor 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 AnalysisLet’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 graball 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:softwaremontrealvancouverguaranteecmscheapmatt cuttsdenvercheckernashvilleblack hatdallashoustonchicagost louisquakeIt 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 GoingKeep 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 DataNow 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 $37Once 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 FormattingThe 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:Search volume – GYRAverage CPC – RYG & add $Number of Keyword Occurrences on Page – RYGTotal Word Count on Page – RYGNumber of Outbound Links – GYRRelevancy Score – RYGPage Backlinks – RYGPage PageRank – RYGDomain Age – RYG10. Domain PageRank – RYG11. Do