Shared Negative Keywords Manager in Google Sheets

A new script is out! Managing shared negative keyword lists is still a clunky experience in Google Ads. Today, I’m releasing a new script which will ease some of your pains.

These are the problems the script is solving:

  1. You can fit all negative keywords lists onto one screen (=one sheet in Google Sheets).
  2. You can see the rules for assigning the negative lists to campaigns on one screen.
  3. The lists are assigned based on rules, not based on humans (which tend to make errors).
  4. You don’t have to click like crazy in Google Ads UI when going through the search terms and adding new negative keywords to shared lists.

This is the setup process:

  1. The script is managed mostly through a config spreadsheet with these sheets: “user_config”, “search_terms_script”, “neg_kws_list”, “do_not_touch_search_terms_new_neg_kws”.
  2. Go to user_config sheet and set it up:
    0 - Config Sheet
  3. You create the lists of negative keywords by filling out the columns on the “neg_kws_lists” sheet. Every 2 columns = 1 negative list. Every 3rd column is a separator.
    0 - Neg Lists
  4. You set a name for the lists in row 2 and also “filter regex”. The filter regex will be then used for assigning the list to the campaigns. Here are a few samples of the regexes:
    “.+” – assign to all campaigns
    “brand|competitor” – assign to all campaigns which contain “brand” OR “competitor”
    “generic.*high” – assign to all campaigns which contain “generic” AND “high”.
  5. You can add as many lists as you want as long as you follow the column structure and fill out neg KWs, list names and regexes. The black column is a separator. The config spreadsheet is currently ready for 10 negative lists (but you can simply add more by adding new columns to the right).
  6. Then you run the script and schedule it (daily cadence is probably enough).
  7. The script creates new shared negative keyword lists (if they don’t exist yet) in Google Ads.
  8. The script adds all the keywords from your spreadsheet into the lists. If the negative list already exists, the existing negative KWs will be deleted and replaced with negative keywords from your spreadsheet => the spreadsheet is the only truth which matters. If you make changes directly in Google Ads, they will be wiped out during the next run of the script! It’s only Google Sheet => Google Ads sync, not the other way around. If you want to have a list managed from Google Ads UI, do not add the list into the config spreadsheet. The script only touches the lists which are on the “neg_kws_lists” sheet.
  9. The script assigns the lists to the campaigns based on the regex filters.

DOWNLOAD FREE VERSION

Adding New Negative Keywords to the List

You have 2 options:

  1. You can add them directly to the “neg_kws_list” sheet as described above.
  2. During each run of the script, the script also adds search query data onto “search_terms_script”. You can go to that sheet and locate the last 2 columns. If you don’t like a term, you can “assign it” to a list by selecting the list name in “Select Negative KW List” column and also by selecting it’s match type next to it. During the next run, the script will look for these new negative keywords, distribute them to “neg_kws_lists” sheet and then push them to Google Ads:
    0 - Adding New Neg KWs
    So basically, anytime you visit this document, you can quickly run through the search terms, assign the bad terms to negative lists and wait for the script to do the hard work during its next run. 🙂
    When the script runs the next time, it will clear all the selections in “Select Negative KW List” and “Select Negative KW Match Type” columns. It also runs the search query report again to get the latest data.

Sample Run

Watch the video of the script in action in my sample account:

I am not showing the search query report since the sample is running in an account with no performance.

The Code of The Script

Most of the code is hosted remotely so I can make changes to script in case errors appear in future. That’s why the script is so short.

Limits

Keep in mind that Google Ads Scripts can run only for 30 minutes. If you have many keywords to push to Google Ads and many campaign assignments, the script may time out. I’ve tested this on an account where I was pushing about 500 negative keywords in 10 lists with about 1500 campaign assignments (1 list assigned to 1 campaign = 1 assignment). This took about 20 minutes to complete. 90% of the time is spent on the campaign assignments.

Conclusion

This script may not be the best fit for everyone (especially when you still want to make change in Google Ads for most of the lists in your account) but it can help you a lot where you frequently need to review negative KWs or when you have complex rules behind assigning the lists to campaigns.

DOWNLOAD FREE VERSION

 

Leave a Reply

Your email address will not be published. Required fields are marked *