AdWords Script: New Keyword Suggestions Based on Shopping Search Terms

Google Shopping is enjoying more and more popularity, and it can also be useful for classic text ad campaigns: The valuable search queries that Shopping provides can be used to profitably develop text ad campaigns. Unfortunately, it still requires a considerable amount of manual effort to determine which Shopping search queries should be included in text ad campaigns.

For this reason, we’ve developed an MCC-level AdWords script which allows users to regularly search up to 25 accounts for valuable search queries from Shopping campaigns and check these against existing keywords in their text ad accounts. The necessary settings for each account can be adjusted using a simple Google Spreadsheet.

Today, I’d like to introduce this script and make it available to you.

What’s This Script Supposed to Do?

Before going into step-by-step instructions for how to install the script, here’s a quick summary of the functions that the script performs:

  1. It reads all of the settings out of a Google Spreadsheet.
  2. It carries out steps 3 through 9 for all accounts to which it’s applied.
  3. It selects all shopping campaigns in an account which have had impressions over the last few days.
  4. Based on the selected time period and KPI filters, it gathers all search queries for these campaigns via the search terms report tool.
  5. If the report contains more than 10,000 search queries, it only uses the first 10,000 (limit for the keyword selector).
  6. Using the selector AdWordsApp.keywords(), it retrieves all keywords which are already in the account.
  7. It compares the results and deletes the overlap.
  8. It returns suggestions for new keywords in the corresponding spreadsheet tab.
  9. Finally, the script informs users via e-mail when new suggestions are available.

Sounds pretty good, right? Let’s take a look at the installation process.

Step-by-Step Setup

In order for the script to function correctly, the Shopping campaigns and text ad campaigns must be in the same account.

Step 1: Create a Copy of the Spreadsheet

First, simply make a copy of the following spreadsheet:

Rename the spreadsheet however you’d like. Sharing for the AdWords Script should be enabled for whoever authorized it and for all colleagues who would like to work with it.

Step 2: Enter Account Details into the Spreadsheet

For the script to work, information for at least one account should be entered into the spreadsheet’s “Settings” tab. Fill in the following information for each account: the account ID, a unique name (of your choice), a time frame for the search query report, relevant recipients’ e-mail addresses, and the desired filter options.


Be sure to select reasonable KPIs: on the one hand, this reduces the likelihood of exceeding the maximum runtime; on the other, it yields the most relevant possible results. “Conversions > 0” is an ideal constraint.

Since the script’s runtime is limited, it’s important to keep an eye on the runtimes of the individual accounts. If the runtimes present a problem, simply adjust the constraints on the search queries, for example, by increasing the number of required clicks.

For an especially large account, it may be a good idea to save a separate copy of the spreadsheet and use it only for that account.

Step 3: Apply the AdWords Script

Scripts can be accessed on the MCC-Level in AdWords under “Bulk Operations” (NOT to be confused with scripts on the account level!).


After applying the new script, delete all of the default example code, then copy and paste the following code in its place:


* MCC-Script - Shopping SQRs for Search using Google Spreadsheet



* Please edit! Change SPREADSHEET_URL to the url of your copy!



// Include script

var URL = "";


function main() {

// This script requires additional rights, do not delete the lines below






The spreadsheet URL from step 1 should be used as “SPREADSHEET_URL” here. Next, give the script an appropriate name, and hit save.

The complete Scala Source Code is available at GitHub: It is compiled to Java Script so that it can be used with AdWords. The latter is available as a Google Doc and is inserted using the Java Script function eval():

Step 4: Authorise the Script

The script should now be operational. Simply click “Preview” to run the script for the first time.  A few questions will pop up about administrative privileges, which must all be confirmed. Once this is done, the Script should create a tab for all of the accounts entered in the spreadsheet, and then add the search queries. For this step, it may be necessary to run the preview once more.

Step 5: Set the Schedule

You can select a frequency for the script in the AdWords Interface so that it runs regularly. We recommend once a week. Individual accounts can be excluded at any time by selecting “no” in the “Activated” column. The script then ignores these accounts the next time it runs.

The Results? New Potential.

Once the script has been installed, you can get started right away. From now on, all returned search queries which are not already present in the text ad campaigns will be saved in a new tab (under the name selected in the settings) and sorted by KPI 1 from highest to lowest. If this tab is not already present in the spreadsheet, it will be automatically created by the template. If the tab already exists, its data will be overwritten.

Finally, the script sends an overview e-mail to all addresses entered for each account with a link to the spreadsheet. This is ideal for PPC managers who have little time and would like to be informed about new potential automatically.

In the near future, we plan to regularly publish scripts based on our company’s internal Scala AdWords Script Editor. This editor allows us to use simple versioning and auto-complete, and it has a compiler on board which minimizes unnecessary testing in the AdWords Interface. The code is added to AdWords using eval(). This also simplifies updates across multiple accounts.

Copyright 2015 crealytics GmbH

Licensed under the Apache License, Version 2.0 (the “License”); you may not use this file except in compliance with the License. You may obtain a copy of the License at

Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an “AS IS” BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either expressed or implied. See the License for the specific language governing permissions and limitations under the License.


Jörn-Heiko Raußendorff

Heiko von Raußendorff works as Product Owner of the Innohub (innovation development team). He has over 10 years working experience in the online industry including PPC.

  • Rachael Moore


    I have tried this but keep getting the error ReferenceError: “MccApp” is not defined. (line 25)

    Can you suggest as to why this may be?


    • Heiko von Raußendorff

      Hi Rachael, thanks for trying.
      Have you created an adwords script within one account? In this case you do not have the functionality of an MCC-script and you will get exactly this error message. Please add it to your MCC (Multi-Client-Center) as described in Step 3.
      If you just have a single adwords account, we may try to solve this by removing the parallel execution of several accounts, catching the error and using the first account entry only
      Hope this helps

      • Rachael Moore

        Ah yes, that’s working fine now – thank you!

        • Heiko von Raußendorff

          Good to hear. Any further feedback is welcome.


  • Kyle Jensen


    I follow the directions for copying the spreadsheet and implemented this into my MCC. I am receiving the following error in my logs when doing a preview:

    key not found: accountId (line 31)

    Line 31: AppsScarippedMain().processAllAccounts(SPREADSHEET_URL);

    I also had to modify the SPREADSHEET_URL and URL variables to replace the beginning and ending quotes ( ” ) with single commas ( ‘ ) in order for the script to run to actually GET the error above, before changing this it was saying I was missing a semi-colon before Line 1, which there is nothing before this.

    Any help would be appreciated! Thanks!

    • Heiko von Raußendorff

      Hi Kyle,

      if you had copied the code directly from this blog post, you had indeed the wrong quotes (they were much prettier, but did not work). This issue should be fixed now.

      Correct quotes:
      var SPREADSHEET_URL = “Your_spreadsheet-url-here”;
      // Include script
      var URL = “”;

      Second Issue:
      key not found: accountId (line 31)
      Two Options:
      1. Did you accidentially delete row 6? This row contains the keys for the range “data”. This row is not visible by default.
      2 By manipulating the spreadsheet the range “data” is not correct any more. It should be Settings!C6:M31 (Original Spreadsheet)

      Hopefully this will help to resolve the issue.


  • Pingback: September 2015 Technical PPC Roundup |

  • Pingback: How to Search Keyword Volume | SEO Tutorial()

  • Pingback: September 2015 Technical PPC Roundup -

  • david sundberg


    Is there a script just like this one but for one account only? Not an MCC that is.
    Thank you!

    • Hi David,

      unfortunately, we don’t have that at the moment.

      I guess, it should only take minor adjustments to also provide you a single-account version of the script. Hope to make this happen soon, but I can’t promise anything.

      We’ll keep you updated!

  • Hi Heiko, thx a lot for the script! Is it possible to select a longer date range? it would be great if i could select last 2 or 3 months. That would give me more reliable data

    • Heiko von Raußendorff

      Hi Ralph,
      thank you for your request. We included just the DateRangeLiterals provided by AWQL. But you can also use a custom date range. It worked fine in a test run.

      Format: yyyymmdd,yyyymmdd (e.g. 20140201,20151120)
      The cells including a date range should be text format.

      Be aware that it makes more sense to choose a shorter period of time, if the assortment of your shop changes a lot (saisonal products, new brands…). In this case your are not risking to include keywords, which are no longer relevant.

      Hope this helps

  • Hello Heiko, great script.

    I’ve run it for several accounts, it works. For some accounts in the MCC however it says: “One of the conditions in the query is invalid.”

    Could it be because they have too little data or something else. Thanks,Gianluigi

    • Heiko von Raußendorff

      Hi Gianluigi,

      Thanks for your question. It is a little bit difficult to debug! Please double-check if your account contains at least one avtivated shopping campaign with at least 100 impressions in the last 30 days. And make sure that your account is not a pure shopping account.

      And of course check the minimum requirements for KPI 1 or KPI 2. May be you are using a wrong format for numbers? Try it with just clicks > 1 or similar.

      If you still got an error, may be you can send me an screenshot of your setting page via email?

      Hope this helps


  • Hi Heiko, second question.

    The search queries it returns are already in the text ads campaigns. Could it be that it looks at all three matchtypes? This because we do not use Broad match right now (only Phrase and Exact). For me that would be the only plausible explenation. Please let me know if I miss something. thanks!

    • Heiko von Raußendorff

      Hi Gianluigi,

      concerning the second question I did a small test in order to be sure that the matchtypes are correctly handled by the script. I think it does in our accounts. A keyword proposal should not show up if it is already in the account in any matchtype.

      But I found a potential issue with keywords containing uppercase characters. We always get shopping search queries in lowercase from google, but google is matching keywords case-sensitive. So if you have a keyword “Adidas Shoes” in your search account and the script gets “adidas shoes” from the search query report of the shopping campaigns, the matching will fail. Could this be the case in your accounts?

      If not, let me know.


  • Stella

    Hey Heiko,
    I’ve seen, you updated the script at GitHub (ConvertedClicks don’t work any more with the Search Qery Report), but the file to “Include Script” ( is not updated, I suppose.
    Are you going to update the file or is there any workaround, I could use?

    Thank you!

    • Heiko von Raußendorff

      Hi Stella,

      thanks for asking. The file should be up-to-date.

      If you have a spreadsheet in place already, please adapt the KPIs as follows:
      ValuePerConvertedClicks => ValuePerConversion
      ConvertedClicks => Conversions

      It should work again.

      We will update the template of the spreadsheet next week.

      If you find any other issues, please let me know.

      Have a nice weekend


  • Heiko von Raußendorff

    Upgraded Version of script and spreadsheet is now available:

    Script and spreadsheet template changed to the new Adwords Report column naming:
    ValuePerConvertedClicks => ValuePerConversion

    If you have the script in place already, you just need to adapt your existing spreadsheet.

  • Cristiano Siqueira Pires


    • Heiko von Raußendorff

      Thanks for using it 🙂