Are you having problems with keyword cannibalisation in organic search your marketing efforts? Keyword cannibalisation is when two or more different URLs (pages) fight it out against each other for the highest organic position on the exact same search term.
Here is a handy easy to follow tutorial that will help you in the fight against keyword cannibalization. This guide covers how to quickly find the problematic keyword and competing URLs.
Are you familiar with Google Sheets?
Are you familiar with Google Sheets? You are going to need Google Sheets and a couple of free add-ons for this tutorial.
Let jump right in, assuming you’re already familiar with Google Sheets. I’m also assuming you have your website verified with Google Search Console and have a minimum of a month’s worth of data collected. You are going to need to install the following free Sheets add-ons from Google Marketplace.
1, Search Analytics for Sheets: https://gsuite.google.com/marketplace/app/search_analytics_for_sheets/1035646374811
2, Sort Range Plus – https://gsuite.google.com/marketplace/app/sort_range_plus/163075110978
Moving on to Data Retrieval
Have you now done that? Great, let’s move on to the data retrieval from Google Search Console using the Search Analytics for Sheets Add-on (it is basically a free API into Search Console).
Start a new blank sheet in Google Sheets. Navigate to Add-ons > Search Analytics for Sheets > click Open Sidebar
Select which website you’re looking to conduct the test on in the Verified Site section. Select a Date Range that is going to give you a meaningful set of data. In this example, I’m going to retrieve the previous 6 months of query data.
Leave the Search Type as default. In the Group By section, we only need the Query and the Page selecting. Leave the remaining options as default settings.
Now click the Request Data button, if all goes to plan in less than 30 seconds you should have retrieved all the Queries and the Pages triggering impressions data spanning the previous 6 months. You will be greeted with the following six columns: Query, Page, Clicks, Impressions, CTR and Position.
Using Conditional Formatting to Highlight Duplicate Queries
Using conditional formatting we now need to highlight all the duplicate queries in column A. Click on Format > Conditional Formatting, the sidebar will appear.
Set it up in the following formatting; Apply to range A1:A4000 (or the number rows you have in your sheet). Format rules – custom formula is: =countif(A:A,A1)>1
Next up, choose a colour preference for the formatting style. In this example, I used light red.
I will quickly recap on that last section. To find and highlight the duplicate keywords and search queries for your website using Google Sheets and Search Analytics for Sheets.
- Start a new blank sheet in Google Sheets
- Navigate to Add-ons > Search Analytics for Sheets > click Open Sidebar
- Verified Site – pick a website (to perform this experiment on)
- Date Range – choose a date range (minimum 1 month)
- Search Type – leave as default
- Group by – query and page
- The rest of the options can be left as default settings (your preference). Once the data has been retrieved you can move on to the next step
- Freeze the top row and add a filter for the top row
- Click on Format > Conditional Formatting
- Apply to range A1:A4000 (or the preferred number of rows in your sheet)
- Format rules – the custom formula is: =countif(A:A,A1)>1 (the formula finds and flags duplicate keywords and queries)
- Formatting style – Pick a colour (preferred colour to highlight the duplicate search queries)
Sort by colour in Google Sheets
As a standalone product Googles Sheets does not at present allow you to sort and filter by colour. To get around this snag we will use the second add-on that I recommended. It is called Sort Range Plus. This handy little add-on allows you to sort by colour in Google Sheets.
Let’s dive in and take it for a spin. Click and highlight columns A to F headers. Navigate to Add-ons > Sort Range Plus > Sort Range.
Click the checkbox ‘Data has header row’ and then click the sort button.
Congratulations. You now have a list of the cannibalising keywords and search queries. All highlighted in your favourite colour ready to create a plan of action to reducing keyword cannibalisation.
Before I wrap up on this tutorial. There are a few more points to cover off, the first one being near duplicates. The guide above only highlights exact duplicate keywords showing for 2 or more URLs. It does not highlight near-duplicate keywords or close variants.
Sitelinks in the SERP listing
There is a very good chance that a number of the flagged keywords with multiple URLs showing impressions and clicks will be sitelinks for a parent query.
These should be very easy to identify, the will have the same search position.
Canonical directive not being respected
There’s another usage for using this method, finding out if the self-referring canonical URL is being respected. That’s data that you probably don’t see on the face of it using Google Search Console and Google Analytics. As they tend to favour the canonicalised version (or so they say).
Data that you can retrieve using the above method will help determine if Google is respecting self canonical tag.
You can also use tools like SEMRush for this, as James outlines in his review. Something to think about I’m sure?
And finally, did you read my other tutorial on how to generate content ideas using Search Analytics for Sheets? If you found this tutorial useful you may also find that useful too. Here’s the URL: https://sitebee.co.uk/generating-content-ideas-with-search-analytics-for-sheets/