I have created a free Google Sheet that sends you an email every month with new keywords your competitor is ranking for on Google, but your site is not.
This data can be used to generate new content ideas for your site or to help track your competitor’s SEO strategy.
While my sheet is free, you will need some paid (low-cost) tools to implement this yourself.
Download a free copy of my Google Sheet (Content Gap Data V1)
Install the ‘Keywords in Sheets Pro’ add-on‘ and buy $30 worth of credits. Add your license key to the app.
Navigate to the ‘Settings’ tab of the Google sheet and enter the following information:
After entering the settings, you should soon notice the sheets named, ‘Competitors KWs’ and ‘My KWs’ have been automatically populated with keyword data. The data represents the top 1,000 keywords by search volume for both sites together with some other useful metrics.
If, for any reason, your sheet has not been populated with data, first make sure that you have entered the correct information in the ‘Settings’ sheet and the correct license key into the Keywords in Sheets add-on.
Keywords in Sheet is still in beta version and may occasionally produce some odd errors. Its developer, Andrew Charlton a great guy and super-helpful so drop him a message on Twitter should you run into any issues.
Once the data has appeared, do the following:
Important – The data must be pasted “As values only” to prevent the formulas from being copied with the data.
This logs the keywords made on your first request so you will only receive emails about new keywords for which the competing site is ranking.
Now take a look at the tab, ‘Competitors KW Log’ and scroll to columns M & N. Any rows with the value “True” in the ‘KW Match” column are those where your site and your competitor’s site both rank for the same keyword. Any rows containing the value “True” in the ‘Brand Match’ column are those where the keyword includes the brand name of your competitor and may, therefore, be of less interest.
If this is your first time viewing this data, you may wish to note down any keywords of interest as these will not be sent to you by email. You will only be informed about new keywords added to this list.
Next, delete any one row in the ‘Competitors KW Log’ sheet. You should see a row listing the keyword you just deleted appear in the sheet, ‘New KW Opps’. For now, leave this row unchanged as we will need it to test our email reports.
We will now move on to setting up the Zapier automations to update the sheet and notify you of any new keyword gaps.
If you have not already done so, register an account at Zapier.com. You will need the Starter Plan or higher.
We now need to link a copy of my Zaps to your Google account.
Configuring the Zap, ‘Update Content Gap Sheet’
Access the ‘Update Content Gap Sheet’ Zap sharing page.
Assuming the test was successful, you should now see today’s date added as a row to the ‘Zapier Log’ tab of the Google Sheet.
Configuring the Zap, ‘Send Content Gap KWs’
Access the ‘Send Content Gap KWs’ Zap sharing page.
Phew! That’s setup completed.
Once a month, the Zap “Update Content Gap Sheet” will update the Google Sheet to request the latest ranking keywords for your site and your competitor’s site. The only reason we don’t update more frequently than once per month is that the data source is refreshed once only between the 6th to the 9th of each month. Therefore, making more frequent requests will only result in wasted credits.
The Google Sheets formulas look for any new keywords your competitor is ranking for but your site is not. It excludes keywords which include your competitor’s brand name and adds any remaining keywords to the sheet, ‘New KW Opps’.
Whenever the ‘New KW Opps’ sheet is updated, the Zap named, ‘Send Content Gap KWs’ is immediately triggered. This Zap prepares and sends an email to the address specified in the ‘Settings’ sheet. It will not generate an email for any keywords you have chosen to “Ignore” or marked as “Actioned”.
Assuming at least one new keyword has been discovered, you should receive an email sent from your own Gmail account and similar to the one shown below.
The email will include the following information:
Assuming your competitor has an active content strategy in place, you will now be alerted to any new content published on their site that has gained visibility on Google.
You may wish to produce your own content targeting the same or similar keywords to ensure you are not losing visibility to your competitor. At the very least, these notifications will give you an insight into your competitor’s SEO strategy from which you can learn and adapt accordingly.
While tools such as Ahrefs and Moz perform a similar function, they require you to manually run Content Gap reports each month. To the best of my knowledge, none of these tools keeps you automatically updated on new keyword gaps.
Feel free to create more copies of the Google Sheet to track multiple competitors. You are, of course, free to adapt the sheet if you wish to expand on its functionality.
Keywords in Sheets provides a great deal of other useful SEO data including search volumes, related keywords, SERPs reports and many other free and paid add-ons.
I hope you find this script useful. I regret that I am unable to offer technical support for this free script, but please let me know by email if you experience any issues so I can resolve them in future updates.
I regularly release free scripts to assist with SEO and content marketing, so follow me on Twitter to learn about any new releases.