Last Updated in July 2021 by Lukasz Zelezny
Hello again, my name is Lukasz Zelezny and today I wanted to show you how to do better research, in terms of keywords.
Obviously, if we’re talking about keywords we are going to be thinking about Google Search Console. The example that I have today is one that I chose completely at random – the website pimlicoplumbers.com. It’s a big plumbing company based in Pimlico, as you can see – the domain name is Pimlico Plumbers, established in 1979. I checked and the domain name was registered in 2002, so that means it’s a pretty long time and there should be lots of interesting data that we can try to use, for me to share with you as an example.
I have 3 varieties of software here; the first is Semrush, the second is Searchmetrics and the third is Sistrix – three very powerful tools that I use simultaneously, especially for keyword research.
Let’s imagine hypothetically that the owner of Pimlico Plumbers is coming to me and saying, ‘Hey, Lukas, can you give me some advice on how to leverage my organic traffic and increase your organic visibility?’
The first step I would take would be to pull as many keywords as possible. If I don’t have access to Google Search Console, I can easily go to Semrush, Searchmetrics and Sistrix.
Right now, we can see that Sistrix in Google.co.uk is showing 1,745 keywords in the top 100. Searchmetrics is showing 1,829 and Semrush is showing 3,608. So, let’s see the full report on these keywords here with Semrush… you can also see nice growth, natural growth probably – I don’t know if this is natural or not but I can presume that there are people who are proactively working on the performance of this website. You can see here all the keywords, URLs and lots of other data. We’re going to export them to Excel. Meanwhile, I’m going to Searchmetrics and doing something similar – long tail keywords – I’m going to export them straight away, using the export function. You can see that this is a bit different, but at some points similar. Searchmetrics is asking me how many keywords I would like to export, I’ll export all of them with a click and the export will be ready soon.
Meanwhile I’m going to do the same with Sistrix, and like I said, Sistrix is showing 1,745 keywords. We can use the download function and we would still have 50,000 credits available. (The concept of credit for downloads works pretty similarly with Sistrix and Searchmetrics).
Okay! Right now, Searchmetrics is reporting that it is ready, so I’m exporting the keywords from here and from Sistrix. Now we need to open each report and name them Tracker 1 and Tracker 2 – the third one, which is here, we will call Tracker 3.
Something here is not quite right, so let’s do text to columns. Okay, we have pretty much what we need; URL, competition, traffic, cost per click… in this export we don’t have search volume, but it’s alright, it’s not a big deal.
To Tracker 3 we will add Tracker 2, just to show you that each software is collecting data in a different way, and here is Tracker 1. We need to try to make these 3 spreadsheets as similar to each other as possible, so we need Keyword: in all three, Keyword is in column A, so that’s fine. Position, okay, then URL.
‘Previous Position’ we are not interested in, we need Search Volume and that’s pretty much it. I’m just removing everything else that’s here.
So! We have KEYWORD : POSITION : URL : SEARCH VOLUME and the flag which tells us which tracker (software) it was.
On this we need to swap the columns, put POSITION here and SEARCH VOLUME here, the rest we can remove.
This is the third one… on this one I can’t find Search Volume, so we will put N/A and the rest we can remove.
Now you have these in the right format, you can create another spreadsheet where you will add all of the keywords. That’s pretty much it.
The problem we have here is with the URL – the URL from the first tracker (software) is a bit different to Tracker 2 and 3. You can see that there’s HTTP in Tracker 2 and 3, and – actually there’s no HTTP in Tracker 2 so we would need to add this.
We can see that in two of the cases, Tracker 1 and Tracker 3 is using HTTP so we’ll use a quick trick to replace WWW with HTTP//WWW.
The first one we are replacing is creating a duplicate HTTP but that’s not a problem, we will switch this back too. If there are 2 lots of HTTP then it should be replaced with a single HTTP.
So that’s done and you can see that the URLs are now consistent.
Now, the most important part is to remember that there are 7,183 keywords that we have been able to pull using these 3 types of software. WE can presume there will be a lot of duplication so we need to remove those. There are two ways to do this – I will try with this filter… yes, that’s done, so now we can copy this to one fresh spreadsheet and this is it. We have 5573 keywords here, and now, some simple maths: we had one software, Semrush, which was reporting round 3500 keywords, Searchmetrics was reporting 1800, Sistrix something like 1700 – altogether that was 7183, but when we removed the duplication there are 5573. Technically, only around 2000 keywords in this exports have been duplicated across different software, and that way you can clearly see that using 3 different types of software for the same task gives you an amazing view of all of the keywords that are ranking in your website.
The last thing for this part; I can do a simple pivot, insert the pivot and think about this: I can put URLs here and I can put Keywords here and also here, then add Search Volume here. We will collapse this pivot and, if we sort, for example from the highest. Now it works and we can see that Pimlico Plumbers is a URL that has the highest number of keywords. Some others are About Us, Recruitment, Pimlico Apprentice….then below, Service, Electrics and so on…
On the other side you can see the column with the Search Volume which you can also sort, and you can see that obviously that Number Plates is one of the most popular keywords…probably because some of the keywords that we have here in the pivot….yes, Number Plate is there, very very popular.
So, that’s it! Thank you very much for being with me, that was Lukas Zelezny and if you have any comments please don’t forget to use the comment function. If you can share this with your friends on your Facebook or Twitter then I will be very grateful. Once again, thank you very much – see you next time!