Investigación de palabras clave SEO

Hello again, my name is Lukasz Zelezny and today I wanted to show you how to do better investigación, in terms of keywords.

Obviously, if we’re talking about keywords we are going to be thinking about Google Buscar en 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 dominio name is Pimlico Plumbers, established in 1979. I checked and the domain name was registered in 2002, so that means it’s a pretty mucho tiempo y debería haber muchos datos interesantes que podemos intentar utilizar, para que los comparta con ustedes como ejemplo.

Hoja de cálculo relacionada

Descargar hoja de instantáneas

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 palabra clave investigación.

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 aumentar 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 Consola de búsqueda, puedo ir fácilmente a Semrush, Searchmetrics y Sistrix.

En este momento, podemos ver que Sistrix en Google.co.uk muestra 1.745 palabras clave en la sección 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 rendimiento de este sitio web. Puede ver aquí todas las palabras clave, 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).

De acuerdo. En este momento, Searchmetrics es informando a 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, que está aquí, vamos a llame a Rastreador 3.

Something here is not quite right, so let’s do text to columns. Okay, we have pretty much qué que necesitamos; URL, concurso, ...tráfico, costo per click… in this export we don’t have volumen de búsqueda, 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.

Así pues, tenemos KEYWORD : POSITION : URL : SEARCH VOLUME y la bandera que nos indica de qué rastreador (software) se trata.

En esto tenemos que intercambiar las columnas, poner POSITION aquí y SEARCH VOLUME aquí, el resto lo podemos eliminar.

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.

Ahora usted tiene estos en el derecho formato, 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.

La última cosa para esta parte; Puedo hacer un pivote simple, insertar el pivote y pensar en esto: Puedo poner URLs aquí y puedo poner palabras clave aquí y también aquí, a continuación, añadir el volumen de búsqueda aquí. Vamos a colapsar este pivote y, si ordenamos, por ejemplo desde el más alto. Ahora funciona y podemos ver que Pimlico Plumbers es una URL que tiene el mayor número de palabras clave. Algunas otras son Sobre Nosotros, Contratación, 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!