How to reliably detect keyword cannibalization for free (using Looker Studio)

by  • 
We know why keyword cannibalization is bad for your rankings, but did you know you can detect it for free? Usually we see keyword cannibalization tutorials using Ahrefs or SEMrush. But, after just two minutes of work you can have a list of pages that are cannibalizing each other’s keywords.
  1. Go to https://datastudio.google.com/ and login with your Google account
  2. You should end up on the Looker Studio homepage
    looker studio homepage
  3. Click Blank Report.
  4. You will be taken a page of data sources that you can connect to. Scroll down and click on Search Console
    Google search console connector
  5. You will be presented by a list of websites. Select the website you want to explore and click URL impression in the Tables column.
  6. Click on Web in the “Search type” column. Then click Add.
  7. You will be taken to the report page. On the right side, in Chart column under Dimension click on Add dimension.
  8. Click on Landing Page. Click on Add dimension again and add Query.
  9. In the Chart column scroll down to Default date range.
  10. Click on Custom, then click on Last 28 days and change it longer time period. I would go for at least 3 months to be safe.
  11. On the left side, you will see a table with a list of pages and queries they ranked for in the last 3 months.
    keyword cannibalization metrics
  12. To highlight all the duplicate queries, it would be easier to export the table to Excel, or to use Google Sheets.
  13. Right click on the table and select Export, then choose your favourite spreadsheet (I used Excel this time).
  14. In Excel load the file you just exported and click on the Query column, then Data tab, then A-Z under Sort.
  15. Click on the Query column again, then Home tab, then Conditional Formatting, then Highlight Cell Rules, then Duplicate values and press OK.

Highlighted in red will be queries that are the same and next to them will be URLS that showed up for those queries. This will give you a nice list of pages that are competing with each other. Usually you would want to pick the less important page and merge it into the more important page (or even redirect it), but I will cover that in another tutorial.