馃攳
How to Find Keyword Cannibalization Issues with Ahrefs and Google Sheets [AMS-07 by Joshua Hardwick] - YouTube
Channel: Ahrefs
[0]
In today's video, I'm going to show you
[2]
how to identify keyword cannibalization issues
[5]
on your website using a combination
of Ahrefs and Google Sheets.
[10]
[Music]
[16]
Hey guys, Joshua Hardwick here from Ahrefs.
[19]
Before I dive into the process, let's talk a little bit about
[22]
what keyword cannibalization is and why it
[24]
might be an issue on your website.
[26]
So in short, keyword cannibalization
is when the same website ranks in the search
[32]
results with multiple pages for a certain keyword.
[35]
So to view an example of this, try Googling: SEO SSL.
[40]
On the first page of the results, you'll see that Ahrefs
[43]
is ranking for this keyword. However, on
the second page of the results you'll see
[47]
once again, that Ahrefs ranks, this time,
with a different blog post.
[52]
This is essentially a keyword cannibalization issue.
[56]
We've got two pages ranking for the same keyword.
[59]
So why is this an issue?
[60]
Well, some SEOs believe that because
[63]
these two pages are similar,
it actually confuses Google.
[66]
So, because they're so similar, Google ends up
not being able to figure out which of these pages,
[71]
if any, should actually rank for
this keyword and the result is that
[75]
neither of these pages really rank in a
particularly high position, for a target keyword.
[80]
Now, Google's never confirm this,
nor denied it, but it's common thought
[84]
amongst SEOs in the community that this is true.
[87]
But regardless of whether or not this is actually true,
[90]
there are still other issues with keyword cannibalization
[93]
which you can read more about in
the full blog post on the Ahrefs blog.
[97]
However, this video is all about
identifying those issues and to do that,
[102]
would now use a mixture of Ahrefs
and Google sheets, which we can use to
[106]
effectively automate a lot of this process.
[109]
So, to get started go to Ahrefs Site Explorer
[113]
and enter your domain into the search bar.
[115]
For this example, I'm just gonna use the Ahrefs blog.
[118]
Hit search, and you'll see the overview.
[121]
I'm sure you're all familiar with this report.
[123]
It shows you basic metrics for
the domain, but for this example,
[127]
we're gonna need the organic keywords report.
[130]
So on the left hand menu, under
organic search, hit organic keywords.
[134]
This will take you to a report
that looks something like this.
[138]
Here, you can see every keyword that your
[141]
website currently ranks for in the search results.
[144]
You can also see the positions that your ranking and the
exact URLs that actually ranked in those positions.
[150]
For the Ahrefs blog, you can see
that we've got more than 22,000 organic
[154]
keywords that we're ranking for
in the Google search results.
[158]
What you could do at this stage is sift through
this report manually looking for keyword
[163]
cannibalization issues, ie. multiple web
pages ranking for the same keyword.
[168]
But as you can probably guess, with 22,000
plus keywords, this is going to take a long time.
[174]
It's also going to be pretty boring and pretty mundane.
[177]
So what we can actually do, is combine the power of
Ahrefs with Google sheets, which we can use
[183]
to automatically pluck out any keyword
cannibalization issues from this report.
[188]
To do this, you need to export the report. Now, I
recommend doing a full export, but you
[193]
can go for a custom number of rows if
you don't want to use so many credits on
[197]
your Ahrefs account. So, for this
example, let's just go for 10,000 rows.
[203]
Hit start export, and Ahrefs will begin
preparing that file for download.
[208]
Within a few seconds, you should be able to
download that file from the downloads section.
[212]
Do that, and save it onto your computer.
[215]
Next, you need to make a copy of
[217]
the keyword cannibalization Google spreadsheet.
[220]
You can find a download link to this spreadsheet
in the full blog post on the Ahrefs blog.
[225]
Just click through, and then go to file, make a copy to save your copy of the spreadsheet on your Google Drive.
[232]
Once you've done that, you need
to import the CSV that we just
[237]
exported from Ahrefs into Google sheets.
[240]
To do this, select the first cell of the spreadsheet,
[243]
cell A1, then go to file, import,
and hit the upload option.
[249]
You can then simply drag in that CSV file and
Google Sheets will begin uploading that file.
[256]
It's then going to ask exactly
where you want to import this file to.
[260]
For the most part you can leave these
settings exactly as they are, however,
[264]
for the first option, the import location
option, you need to change this
[269]
to "append to current sheet."
[271]
Once you've done that, hit import data.
[273]
Within a few seconds, Google will actually import
all of the data from that CSV file into Google Sheets.
[280]
And you should see it appear in Google sheets like so.
[285]
Once it's done this, you'll see that Google Sheets
actually begins calculating a few formulas.
[290]
This is indicated by the
calculate in formulas bar in the upper
[294]
right hand corner of the Google sheet.
[297]
Once it's finished doing this, go to the results tab
[300]
of the spreadsheet and you should
see something that looks like this.
[305]
This is basically every keyword
cannibalization issue on your website.
[310]
You can see that for each of these keywords,
there are multiple pages ranking in the
[314]
search results for that keyword.
[316]
So, what you can then do is sift through this
[319]
report, which is much quicker than
sifting through the entire organic
[322]
keywords report, and figure out what
you're gonna do about these issues.
[326]
Now, there are multiple solutions which you
can again read about in the full blog
[330]
post on the Ahrefs blog and sometimes you
don't even need to solve this problem.
[334]
Again, more detail about this in the blog
post on the Ahrefs blog. So go through
[339]
this spreadsheet fix the issues that
need fixing and hopefully you should see
[344]
a nice boost in organic traffic from
fixing these cannibalization issues.
Most Recent Videos:
You can go back to the homepage right here: Homepage