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.

***Not the Google Ads script you were looking for? I can also write custom Google Ads scripts to fit your needs. Contact me.***

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

***Not the Google Ads script you were looking for? I can also write custom Google Ads scripts to fit your needs. Contact me.***

14 thoughts on “Shared Negative Keywords Manager in Google Sheets

  1. Hi Daniel,

    Cool script and I’ve been looking for something like this.

    I tried running it and come across the error below:

    Cannot retrieve the next object: iterator has reached the end. (file Code.gs, line 333)

    Any advice?

    1. Are you running it in preview or for real? Preview will fail because at some point, the lists do not exist yet…

    2. Oh please ignore that. It doesn’t run properly under Preview mode.

      It now works like a charm once I hit Run!

  2. Hi,
    Is it possible to include search queries in the “search_terms_script” sheet, that have no clicks but impressions?
    Thank you

    1. Hi Zsuzsa,
      sorry no. Google Ads generally provides only terms which have at least 1 click.

      1. Thank you for your reply. However I can see search terms in Google Ads that had impressions but no clicks.

  3. I don’t know if it is random. An example from last week data: 25% of the queries had no clicks in one of our client’s account. We usually check these queries as well, this is the reason I was asking.

    1. Hmm… Adding impressions there is doable, it would “just” require me to update the script quite a bit and currently don’t have time for it. I am putting down a note and I’ll update it one day.

        1. Hi Zsuzsa,
          I’ve updated the script, now it also shows impressions.

          Sorry it took so long.

          DZ

  4. Hi Daniel,
    I modified the script a bit and am now adding keywords from BMM and Exact campagnes from the Google Ads bigQuery Transfer into your Spreadsheet and push this to Google Ads as negatives to my DSA & DA campagnes. However, I got to many Negative Keyword Lists due to the hundreds of categories on our website (comparison site). Is there a possibility in your script to directly add the negatives to the correct campaigns without using Negative Keyword Lists?

  5. Hi Maikel, no… this would be a completely different script unfortunately – I don’t have one like this. It’s writable but nothing in stock. I’d be concerned with speed though. If you have thousands of neg KWs to add to the campaign level for hundred campaigns, the script could simply time out.

Leave a Reply

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