This video is part of our Weekly Wisdom series which features experts on a variety of topics.
Modified Transcript
Need to find all the keywords that your customers are looking for, as well as everything your competitors are ranking for, and I need it fast?
Thankfully, with a couple of tools, it is easy to get this information very quickly. Okay, let me orientate you with this sheet. Essentially the green tabs here are all of the output tabs you are gonna use to actually get the information all cleaned and ready to use.
For example, we can see here, we have got all the keywords, how difficult they are from a score of zero to one, how many searches every month you're getting, and then some real basic analysis on how much money we think we're gonna make from them every month.
Next up, we've got seasonality so I can see when I need to post my content based on the month. Then, of course, the keyword list itself plus working out if we actually rank for any of these terms or not. For example, this term has got this associated landing page, so we're fine, but you can see all of these terms do not have landing pages, so we probably need to make them.
How It All Works: An Example
The first tab is the setup tab. Here, what we want to do is we want to take all of the major keyword categories that we want to rank for. This is a homewares brand so what I'm gonna do is take some of their top keyword categories and put them in here. The way that I find keyword categories is really easy. First and foremost, I go to Search Console, the Performance Report, go to queries, and then I simply just download their queries.
Once you've got your queries, all you're literally doing is taking the big list of them, copying them, going over to an aggregate tool called Keyword Grouper Pro. All you're doing is putting them in there, giving a minimum group length at the amount of keywords, add any keywords you want to exclude (maybe it's the brand name, for example - it should be put in here), set excluded words, and process.
What we can see here is the top-level groups of all the products that this particular person sells. We have got bedside, floor, lights, pendant lights, etc. I am going to export this, and I'm going to add it into my main sheet.
Now we are going to take those top-level categories from Keyword Grouper Pro and put them in here, and then we are gonna do something slightly magical — we are gonna use Supermetrics, and we are going to click the big "Refresh All" button, and that is going to pull all of the information we need from SEMrush.
Phrase Match
Now that all that is done, let me walk you through the raw data sheets by going first to ph_SEMrush (ph stands for phrase match). This has essentially pooled all the phrase match queries for each of these keywords inside of the keyword categories tab, and it added them into columns here. So, this is just a big, massive dump of the first 5,000 keywords of our phrase match related to the term lamp, sofas, tables, etc.
Related Searches
Inside of the related searches, we can see that we have got all the terms that SEMrush think are related to all of these keywords. Again, the exact same format, top 5,000 just dumped straight into columns, but what we need to do is get it into one single column, so we let's start editing it and cleaning out the data.
Cleaning Up the Data
You will notice here that when you click on this "Error. No data found." it is using a formula called UNIQUE. All UNIQUE essentially does is it goes to each of the phrase and related match, and it pulls it into these columns. If we were to redo this formula, you will notice it has got squiggly brackets, that is because it is an array formula. So, it is UNIQUE({, then we're literally gonna go to our first one and we're gonna select from column A to E, semicolon, and then select column G to K, and we are essentially just gonna rinse and repeat this process for not only the phrase match but also all of the related match data as well.
You can see it has taken all those columns and columns of data and put them into one single column, which makes it much easier to work on. It also attempts to tag. All this is doing is a nested, IF ISNUMBER SEARCH. So, it's kind of like a little auto-tagging thing just to kind of help us along.
What we are gonna do from here is we are literally just going to copy all of that out, and there is an option called "Edit these keywords", which is literally just a clean copy/paste. We are going to delete the old data in there first, and we are going to do Ctrl+Shift+V to get everything pasted in as it should be.
Next step. We have got all of the seasonality data in here, but it is in one column. You need to go Tools, and then to go Data, and split those text into columns. This option will allow you to name them as the different months.
Reducing the Keyword Data Collection
Once we are happy with the general raw data collection, what we now want to do is edit these keywords. Let's say that you see a bunch of stuff that either your client doesn't sell or is really just not related to them. What you want is to take out big chunks and delete the keywords that don't matter, and also tag keywords for organization purposes; this is the only real manual bit of the process that you need to go through. So, go through, get rid of the stuff that doesn't apply to you, and tag the rest of your terms. It is literally as simple as that.
The Final Keyword List and Landing Pages
When we now look at our keyword list, we can see all of the different terms that matter. For the example of houseware, we can see that for the term bedside tables, we already have a landing page, but it is /table-lamps not /bedside-tables. So we could probably make another one that that says bedside tables and perhaps enhance the rankings for this particular phrase. We can see for a bunch of their other keyword phrases that don't actually have any landing pages for them.
Now, you may be thinking, "Well, where am I actually getting all of this landing page information?" Well, it is super simple. Review your SC keywords (SC stands for Search Console). So when we go up to the top of the table in this example, what I have done is pulled it in the data via Supermetrics again.
You can do this by going to Supermetrics, then into the sitebar, then select Search Console as the source. I put my client's website in there, looked at the last 90 days of data, and here is the most important part, "Split to rows by Search query", and the full URL. You will not get this when you just download it from the main interface. I want to see all the queries and all the landing pages side by side.
The reason why I want that is because inside of the keyword list all this is doing is an index match. All it is saying is, look for the keyword football tables, inside of this Search Console dump, and if it exists, put the URL in there. If it does not exist, say "No Landing Page". What you can see here is we are very quickly able to crosswalk all of the keywords that we can see in the marketplace with all of the keywords we are currently ranking for and the related landing page.
And that is it. That is all the information you need to tell you the pages you need to create to rank better inside of search. All you need to do is the hard bit and actually go make the content. Super fast keyword research, done.
