zh
English Español Deutsch Français Italiano Português (Brasil) Русский 中文 日本語
Submit post
Go to Blog

Weekly Wisdom with Ross Tavendale: Keyword Cannibalization

Weekly Wisdom with Ross Tavendale: Keyword Cannibalization

Ross Tavendale

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.”

Weekly Wisdom with Ross Tavendale: Keyword Cannibalization. Image 0

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.

Weekly Wisdom with Ross Tavendale: Keyword Cannibalization. Image 1

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.

Weekly Wisdom with Ross Tavendale: Keyword Cannibalization. Image 2

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.

Weekly Wisdom with Ross Tavendale: Keyword Cannibalization. Image 3

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.

Weekly Wisdom with Ross Tavendale: Keyword Cannibalization. Image 4

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.

Weekly Wisdom with Ross Tavendale: Keyword Cannibalization. Image 5

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.

Weekly Wisdom with Ross Tavendale: Keyword Cannibalization. Image 6

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

Weekly Wisdom with Ross Tavendale: Keyword Cannibalization. Image 7

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.

Weekly Wisdom with Ross Tavendale: Keyword Cannibalization. Image 8

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.

Weekly Wisdom with Ross Tavendale: Keyword Cannibalization. Image 9

Thanks for Watching

More on this Weekly Wisdom with Ross Tavendale: GSC Query Tutorial Post Ross Tavendale Weekly Wisdom with Ross Tavendale: Super Fast Keyword Research Post Ross Tavendale

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. 

Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

Ross is the Managing Director at Type A Media, an independent search agency that work with FTSE250 companies and mid-sized brands to help them find the optimal way to talk to more people online.
When not obsessing over his clients rankings, he hosts the Canonical Chronicle, a weekly web show watched by 100k people every month.
If you want to ask him a direct question you can find him @rtavs on Twitter.
Share this post
or

Comments

2000
Newcomer

Either just recently joined or is too shy to say something.

Hi Ross.

A really nice video. However, it would be nice if you showed how to do this step by step to build the final spreadsheet as you've shown including how to cross-reference GSC and GA. Don't know in which sheets to use all those formulas you've mentioned.
Rubence Ingale
Newcomer

Either just recently joined or is too shy to say something.

Thank you so much for sharing this kind of informative stuff.
Tristam Jarman
Expert

Provides valuable insights and adds depth to the conversation.

Like how you presented this one Ross. More gems you're sharing. Great for people who were not aware of KW Cannibalisation and those. Also awesome stuff on process all this data. I think that's one of the more scary things for people to do. Knowing what you want to do with the data and being able to do it in a time efficient are two different things.

Great piece of advice on the On Page SEO Checker in SEMRsuh too. Still need to get my Supermetrics game on though 🙈
Kristen Vaughn
Helper

An experienced member who is always happy to help.

Great video/post. This is such an important topic right now, especially as we've seen Google's algorithm updates pushing quality over quantity.
Newcomer

Either just recently joined or is too shy to say something.

Thank you for this but Im having trouble following it. Search Console - Performance (its not called Performance Report) has a table with header tabs like "Queries", "Pages", "Countries" etc.. I dont see where I "Filter by keyword". Theres a Filter dropdown icon which offers checkboxes for "Page", "Impressions", "Clicks". You rush over the detail of how to extract the data to use in supermetrics. A bit more step by step please?
Nick Samuel
Pro

Asks great questions and provides brilliant answers.

Thanks for sharing this solid process, Ross. Clients often ask about keyword mapping because in theory it's quite easy, but everyone usually underestimates how many potential queries and competing urls there are whether that's a small site with a few hundred pages to a mid-sized 10,000+ e-commerce site. Nice to see Supermetric getting some love as well!
Cherry Smith
Enthusiast

Occasionally takes part in conversations.

Thank you for sharing this.
so helpful
Liraz Postan
Master

A veteran community member.

Loving it! Thank you for these great posts. Keep them coming.
Liraz Postan
Master

A veteran community member.

Loving it! Thank you for these great posts. Keep them coming.
Michal Lyko
Enthusiast

Occasionally takes part in conversations.

A great method for excel lovers :) as I'm def one of them I need to ask, do you recommend any other quick way to put kw and urls from GSC side by side apart from Searchmetrics?
Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

Michal Lyko
Hi Michael

Yes there is a free plugin for sheets. https://chrome.google.com/webstore/detail/search-analytics-for-shee/ieciiohbljgdndgfhgmdjhjgganlbncj but I am not sure what the functionality is like as we are firmly in the supermetrics camp.
Newcomer

Either just recently joined or is too shy to say something.

Ross Tavendale
Thanks Ross. I figured it out in the end :-)
Peter Mead
Master

A veteran community member.

Ahh mind blown again @rtavs !! wow, everyone should watch this and no longer cannibalize their own keywords!
Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

Peter Mead
Thanks Peter. It's strangely common on ecommerce sites. Even with proper canonicalisation, I see tons of pages competing with one another. The big thing aout using the NL API is that it looks at the page in the context of google reading the text in isolation - really telling how much of googles understanding of a page is shaped by the surrounding content, internal and external links, etc.
Peter Mead
Master

A veteran community member.

Ross Tavendale
Oh yeah it's a decent method you have, very nice :)
Newcomer

Either just recently joined or is too shy to say something.

Another beast post ross . It is on next level.
Deepak Shukla
Guru

A bearer of digital marketing wisdom.

Just passed Supermetrics onto the team. Looking at that level of data at scale is an issue we have with a client recently. This is a really smart way to analyse big data - thank you buddy!
Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

Deepak Shukla
Thanks for stopping by Deepak :-)

Supermetrics with the SEMrush API is a killer combo. If you're about to invest in it you can use the code "ROSS" for 20% off ;-)
Paul Lovell
Master

A veteran community member.

great post/video Ross as usual
Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

Paul Lovell
Thanks Paul. Glad you liked it :-)
Newcomer

Either just recently joined or is too shy to say something.

Another great video Ross. Any chance of sharing the formula you added to the revenue column?
Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

Michael Cain
Sure thing mate. If you email conference@typeamedia.co.uk you'll get the sheet auto sent to you.
Jason Barnard
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

columnist
Great stuff !

Really important to point out to clients that having 2 pages ranking 4th and 5th will pull in A LOT less clicks than 1 url ranking 1st or 2nd

And de-cannabilisation will focus Google's 'mind' and help the best page you choose (that is, we assume, the most pertinent) push up and get those extra clicks (and more pertinent to intention = . better conversion rates :)
Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

Jason Barnard
Cheers Jason. As we get more technical I can see SEOs using more APIs like this to programatically run canibalisation reports on massive sites. The cloud natural language API is fantastic if you've never played around with it https://cloud.google.com/solutions/ai/
Simon Cox
Pro

Asks great questions and provides brilliant answers.

Superb! This has given me at least half-a-days things to do and think about! Getting processes like this down to a few minutes can really add value to client work and cannibalisation can really be a tricky one to strategise if you have many pages trying to rank for the same term. Nice one Ross!
Ross Tavendale
columnist

SEMrush columnists are authors who had proved their expertise in digital marketing and contribute regularly to our community.

Simon Cox
Cheers Simon. As we work a 4 day week, it's essential for us to find these hacks so we can reclaim as much lost time as possible. With the g suite and cloud apps it makes it insanely easy. You do need to know a bit of appscript but its literally a week of classroom learning and you are off to the races.

Send feedback

Your feedback must contain at least 3 words (10 characters).

We will only use this email to respond to you on your feedback. Privacy Policy

Thank you for your feedback!