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.

Required services:

• Google Sheets (free)
• Keyword in Sheets (At current rates, $30 worth of credit is enough to track one competitor for approximately 20 months)
• Zapier (Starter Plan costs £18 per month)
• Gmail account (free)

Instructions

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:

• Your URL: The full domain name of your website.
• Competitor’s URL: The domain name (or sub-folder) of the competitor’s site you wish to track.
• Country: Select the relevant country version of Google.
• Competitor’s Brand Name: Enter the brand/business name of your competitor. This will exclude their branded keywords from your reports.
• Email for Report: Enter the email address to which reports should be sent.
• Lookup Key: Do not change the value “123” in this field. It will be used by the Zapier automation.
• URL of this sheet: Copy the full URL of your copy of my Google Sheet into this cell.

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:

• Navigate to the sheet named ‘Competitors KWs’
• Press Ctrl + A on your keyboard to select all of the data including the column headings.
• Navigate to the sheet named ‘Competitors KW Log’.
• Make sure that the cursor is in cell A1 of the sheet and then paste the data you just copied using Paste Special > Values Only or Ctrl + Shift + V

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.

Zapier Integration

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.

• Click on ‘Try this Zap’ try this zap

  

• Click ‘Get Started’
• On the ‘Day of the Month’ page leave the date unchanged and then click ‘Next’
• On the ‘Time of Day’ page, choose the time at which you want the email to be sent. Then click ‘Next’
• On the ‘Connect your Google Sheets account’ link Zapier to your own Google Sheets account and then click ‘Next’.
• On the ‘Drive’ page, select your Google Drive and then click ‘Next’.
• On the ‘Spreadsheet’ page select your copy of my Google Sheet and then click ‘Next’.
• On the ‘Worksheet’ page make sure that the ‘Zapier Log’ sheet is selected and then click ‘Next’.
• On the ‘Map fields from Schedule by Zapier into Google Sheets’ page leave the field value as ‘Pretty Date’ unchanged and then click ‘Next’.
• On the ‘Let’s test out this Zap’ page click ‘Sent test’.

  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.

• Back in Zapier, click ‘Next’ and then ‘Turn on Zap’

Configuring the Zap, ‘Send Content Gap KWs’

Access the ‘Send Content Gap KWs’ Zap sharing page.

• Click on ‘Try this Zap’
   
  
• Edit the Zap trigger, ‘Get New Keyword Opp’ and link your own Google Account.
• In the ‘Spreadsheet’ field, select your copy of my Google Sheet.
• In the ‘Worksheet’ field, select ‘New KW Opps’.
• Click ‘Continue’ to test the trigger.
• Assuming the test was successful, click ‘Continue’.
• Edit the Action, ‘Only continue if…”
• Leave all settings unchanged and click ‘Continue’.
• If you chose to “Ignore” or “Actioned” all of the new keyword opportunities in the Google Sheet, you may now see a message, “Your Zap would not have continued”. Don’t worry if you do see this message, just click ‘Continue’.
• Edit the Action, ‘Get Settings Data’.
• Select your own Google Sheet account and click ‘Continue’.
• In the ‘Drive’ field, select your own Google Drive.
• In the ‘Spreadsheet’ field, select your copy of my Google Sheet.
• In the ‘Worksheet’ field, select the ‘Settings’ sheet.
• In the ‘Lookup Column’ field, select ‘Lookup Key.
• Leave all other fields unchanged and click ‘Continue’.
• Click ‘Test & continue’
• Edit the Action, ‘Send Email in Gmail’.
• Link your own Gmail account and click ‘Continue’.
• Leave all fields unchanged in the ‘Set up action’ section and click ‘Continue’.
• Click ‘Test & continue’.
• Click ‘Publish Zap’.

Phew! That’s setup completed.

How does it work?

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:

• The new keyword opportunity discovered.
• The average monthly search volume for that keyword in the country you specified.
• The latest month’s search volume for the keyword in the country you specified.
• The average search volume for the last three months in the country you specified.
• The average growth rate (%) in search volume from the first month of data (max 12 months) to the most recent month of data.
• Your competitor’s ranking position on Google for the keyword in the country you specified.
  The ranking URL of your competitor’s page for this keyword.
• Your closest matches – A link to a Google site: search showing the most relevant existing page on your site for the keyword.
• IGNORE OR ACTION THIS KEYWORD – This is a direct link back to the Google Sheet allowing you to mark the keyword as either “Ignore” or “Actioned” in the ‘New KW Opps” sheet. Adding a value of “TRUE” to either of these columns prevents you being notified about the same keyword in subsequent months.

How can I use this data?

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.