Blog

Transcript

Hello, SEMrush users, and welcome back to another Weekly Wisdom. Today, we have got a very exciting one. Now, how many times have you seen your site ranking for multiple things in the same search? The first thing you might think is, “Oh, this is amazing, we have got multiple rankings showing for the same keyword; we are dominating page one, page two,” whatever it is. But then you look at your traffic, and it is like, “Oh, we are actually getting much, much less traffic; that is because of keyword cannibalization.

In this tutorial, I am gonna show you how to find keyword cannibalization on your site and get rid of it, and it is gonna take you about 10 seconds. Let's get into it. For me, growing up, excess was always a little bit of a theme. When your mom would say things to you like, “Ross, don't eat too many sweets because you will ruin your appetite for dinner. Don't sit too close to the TV because you will get squid eyes. Don't run too fast; you will end up falling over.”

Then, of course, you become a teenager, and it is like, “Turn that music down; it is too loud. Get a haircut; your hair is too long.” They give you other advice too, which is mostly ignored. But the point is that too many opportunities create inertia in your life. If you are in the middle of a room, imagine a circular room with lots of doors around the edges, and they are all open, and they are pulling you all out in the different directions. Because they are all pulling you out, the force of each of them actually cancels each other out, and create complete inertia.

Keyword Cannibalization

Keyword cannibalization is very similar in that you have got too many URLs that rank for the same term. So what we need to do is start closing these doors, or in this case URLs, or differentiating them — getting them out of the index or optimizing them. So let me quickly just give you a real-world example first and foremost. A client of ours came to me and said, “Hey Ross, can you find some cannibalization on our big hero term?” I'm like, “Yeah, sure, no worries.”

I went to Google Search Console, went into Performance Report, then into Queries, I then filtered by keyword, and I just clicked on pages. And boom, there you go. Sent it to the client, they were like, “Yep, great. Thank you.” No worries.

They Wanted More Data

Then they came back, and they were like, “Actually, can we get it for all keywords, URLs, countries, languages, and devices?” I was like, “Oh God. Yep, sure, no worries at all.” And the reason why I was a bit nervous about it was because there were half a million URLs in the client's site, and the more data you have got, the harder it is to get anything meaningful from it.

Supermetrics

So we need to find some sort of programmatic way to make this happen and to do that we are gonna use Supermetrics, which is a Google Sheets add-on. Essentially, what it is gonna do is it is gonna allow us to pull the URL and the search query side by side. In the interface, it doesn't let you do that; it only lets you do it page-by-page or query-by-query should I say. But this will actually pull out all of the duplications for you, so we can really quickly see what is going on.

All right, so this is what we wanna have it look like, we want the landing page, then the search query, and then all the information. And you will notice that there is lots of duplication in the search query, and that is because, you guessed it, those keywords are cannibalized.

Keyword Map

So we need to get this keyword map done. Before, I was saying that the client was asking me for some data on their main hero term, which is the term IPA. They are getting 81,000 people looking for it, but only getting 43 clicks, which is obviously a terrible click-through rate, and the reason being is because 11 URLs are competing for the exact same ranking.

What we wanna do is get the raw Google Search Console data, cross-reference it with the Google Analytics data, and then we wanna see what is actually going on. And ultimately we wanna get to a place like this.

I want to see the search query in column A, the count of how many times that query actually shows up; then I want to see impressions because that is the relative opportunity, how much revenue that is going through those URLs that are being cannibalized, and then I want to see the actual URLs themselves — that is gonna be really important for us.

We want a map like this so I can quickly jump in and say, “Hey, the term IPA, I know you cared about IPA, you also cared about Ale, but IPA makes you considerably more money than Ale, so let's focus on that and let's get that optimized first; I can make your business case.

How to Prepare All This Data

So, how are we gonna prepare all this data? First and foremost, how do we get everything in nice and neat there? If we have pulled everything through Supermetrics, and we have got all the URLs and all the duplicated queries, I need to get the original queries. Another way to say it, I need the unique query.

So I am going to use this here, it is =unique, and going to select the entire column where all your keywords are.

And then after that, what I need to do is I need to actually go in and actually to count up all of the queries that are actually in there. So now I have got the uniques; I need to see how many times they are being duplicated. So, nice and simple, we are gonna use the countif formula, which allows you to select the column you want to compare, and then the actual thing you want to be counting.

After that, we want to get all of these URLs in. Apologies for this being so blurred out, again, it is client data, so it is commercially sensitive. I want to get all the URLs out so I can actually see per query, URL by URL, where the problems lie. To do this, we are gonna use query, we have spoken about this before; query is a pseudo-SQL language.

So essentially you can do computation inside of a query, which is pretty amazing. In this case, we are going to just very simply say “select column A where B equals our keyword.” If you see my cannibalized keyword, pull in all of the URLs, really nice and simple. The exact formula I use is down in the white bar there, so you can just copy and paste that into your sheet.

Query Learning Resources

If you are really interested in learning more about this, I recommend these resources: Codingisforlosers.com, a phenomenal website, the guy over there does a great job with query, and he is more of an SEO guy than anything else. The G Suite YouTube channel is phenomenal; it teaches you about natural language processing, about using query, all sorts of great stuff. And Ben Collins is actually the main G Suite guy. If you Google around you will see him coming up everywhere; he has got tons of really great tutorials on there as well.

SEMrush On-Page SEO Checker

All right, so assuming we have now got our big list of URLs and keywords that we want to fix, did you know that you can actually upload a list of keywords and URLs side by side to the SEMrush on-page SEO checker? Well, once you do that it is going to give you this output. It does a kind of TF-IDF analysis, which stands for term frequency versus inverse document frequency.

What it does is it Googles the keywords you are trying to rank for, in this case, it is craft beer, and then it pulls down the top 20 results, analyzes the content they have got on the page, and then compares it to your own. So, you can get some really quick and dirty optimization tactics in order to go and fix your cannibalization.

It does a bunch of other interesting stuff because there is a site audit built in. It is also gonna be looking at things like on-page elements you need to fix, so literally, you can get this done in, what, two minutes? Three minutes? And then start spitting out these reports and give it to your content team.

Thanks for Watching

So all that is left to do now is actually just go and fix it. And that is everything. I really hope you enjoyed this information.

If you have got any questions about how to do any of this, or if you want access to the sheets that we mentioned, please comment down below, and I would be happy to jump into the comments and have a conversation with you about it. 

Innovative SEO services

SEO is a patience game; no secret there. We`ll work with you to develop a Search strategy focused on producing increased traffic rankings in as early as 3-months.

A proven Allinclusive. SEO services for measuring, executing, and optimizing for Search Engine success. We say what we do and do what we say.

Our company as Semrush Agency Partner has designed a search engine optimization service that is both ethical and result-driven. We use the latest tools, strategies, and trends to help you move up in the search engines for the right keywords to get noticed by the right audience.

Today, you can schedule a Discovery call with us about your company needs.

Source: