Prevent Keyword Cannibalization in Your Content Marketing Strategy with this Excel Tool

If you’ve been writing blogs as part of your content marketing strategy, you probably have a long list of focus keywords you’ve already used. When expanding your content marketing plan, you’ll come up with another long list of potential keywords to generate additional content around.It can be hard to keep track of both lists, but having new keywords to address helps ensure you avoid keyword cannibalization – writing content on a keyword you’ve already used. If you’re guilty of this practice, use the following Excel trick when conducting keyword research to highlight keywords you’ve already used and avoid the overlap.Keyword cannibalization occurs when you have multiple pages on your website using the same focus keyword. When someone searches for that keyword, the pages end up fighting each other for ranking. To prevent your site from fighting an internal SEO battle, avoid setting the same focus keywords for multiple pages.To start, you could pick a potential keyword and search for it in your existing keywords list, but that's a pain, and why add an extra step to the process? Let Excel do the work for you.To start, get the list of potential keywords, which can be pulled using a keyword research tool such as Moz Keyword Explorer, in an Excel document or Google Sheet. For this exercise, we’ve put them in column A, but you can do this anywhere in your sheet, just be sure to adjust the formula as needed.

Next, we’ll copy our list of used keyword into their own tab on the same sheet. Name the tab “Used Keywords”.

Back on the potential keyword list page, paste the following formula into the first cell of column B:=IF(ISERROR(VLOOKUP(A2,'Used Keywords'!A:A,1,False)),"Unique","Existing Keyword")

Apply the formula to the rest of the column by either dragging the bottom right corner of the cell down or by double clicking the bottom right corner of that cell when the “+” appears.

All potential keywords that exist in the Used Keywords tab are now tagged as “Existing Keyword”. To highlight the existing keywords even further, highlight column B, go to conditional formatting, and select New Rule.

Set up a rule that formats cells containing “Existing Keyword”.

Here is a breakdown of the formula’s pieces if you would like to adjust it to fit your own needs:

As you create more content and build out your content marketing strategy, be sure to update the used keywords list. This will ensure that the list stays accurate and you can at a glance determine whether or not a new piece of content’s keyword has been used.Google tends to only show one result from a site in response to a search query. If you have multiple pages set for that query, how will it know which page to display? You want to ensure your best page shows up in the search results.A tricky thing about keyword cannibalization is that there isn’t a hard and fast way to detect that it is occurring. There are some signs to look for, such as low rankings for relevant industry keywords. Moz is a great tool for monitoring your keyword rankings. If you’ve noticed a keyword has steadily dropped over time, or if your content marketing strategy hasn’t improved your rankings, then keyword cannibalization may be occurring.There’s also the good old-fashion Google Search. Search for a keyword your site uses, and see what pops up. Is the page you’d expect to show up in the search results, showing up? Do the same search, this time referencing your website name. Now what comes up? If a whole string of content pops up, but your most important link isn’t at the top, it’s time to take a deeper dive into your overall SEO strategy.A good content marketing plan needs unique, high-quality content. Duplicate keywords risk hurting your SEO as your pages fight one another for rankings. Use this Excel trick to stay organized and avoid keyword cannibalization. You worked hard to develop your content – make it as effective as possible by ensuring its focus keyword is unique.