This script looks at all search terms in campaigns to ensure that overpriced search terms are automatically excluded. Because the desired CPA can vary per campaign, we work with labels in this script. By applying a label to a campaign and mentioning the desired CPA in it, too expensive keywords are found.

When a search term in a campaign is the same as the corresponding keyword, the search term can not be excluded, but the search term is paused. In addition, it can naturally occur that keywords are longer than 10 words, so that they can not be excluded exactly. That is why search terms longer than 10 words are shortened to 10 words and excluded as the phrase.

Settings

There is little to set for these scripts except for the following:

  • LOG: Specify whether the script should report the intermediate steps by adjusting the value to 'true'.
  • THRESHOLD_MULTIPLIER: This variable applies to search terms without conversions. When these search terms have costs that are higher than (desired CPA * this variable) they are excluded. This way you can give search terms the chance to spend more than the desired CPA to still achieve a conversion.
Script
// Copyright 2019. Increase BV. All Rights Reserved.
//
// Created By: Tibbe van Asten
// for Increase B.V.
// 
// Last update: 03-01-2019
//
// ABOUT THE SCRIPT
// With this script you can exclude search queries with  
// a high CPA. Each search queries must have at least
// one conversion to be excluded. You can set the 
// threshold multiplier yourself. 
//
////////////////////////////////////////////////////////////////////

var config = {

	LOG : false,
  
  	// Search queries will only be excluded when the CPA is at least [target CPA] * THRESHOLD_MULTIPLIER. 
  	// For example: When your target CPA is €5 and you set the threshold multiplier to 1.5,
  	// the search query will only be excluded when the CPA is over €5 * 1.5 = €7.5
	THRESHOLD_MULTIPLIER : 2

}

////////////////////////////////////////////////////////////////////

function main() {
  
  var campaignIterator = AdsApp
    .campaigns()
  	.withCondition("Status = ENABLED")
  	.get();

  while (campaignIterator.hasNext()) {
    var campaign = campaignIterator.next();
    findQueries(campaign);
  }
  
  var shoppingCampaignIterator = AdsApp
    .shoppingCampaigns()
  	.withCondition("Status = ENABLED")
  	.get();

  while (shoppingCampaignIterator.hasNext()) {
    var campaign = shoppingCampaignIterator.next();
    findQueries(campaign);
  }  
  
  Logger.log("Thanks for using this custom script. Visit https://increase.nl for more information.");
  
} // function main()

////////////////////////////////////////////////////////////////////

function findQueries(campaign) {  
    
    var labelIterator = campaign
    	.labels()
    	.withCondition("Name CONTAINS 'CPA'")
    	.withCondition("Name DOES_NOT_CONTAIN '_'")
    	.get();
  
    while (labelIterator.hasNext()) {
      var label = labelIterator.next();
      var targetCpa = label.getName().split(" ")[1];

      var THRESHOLD_CPA = (Math.round((targetCpa * config.THRESHOLD_MULTIPLIER) * 100) / 100) * 1000000;

      var report = AdsApp.report(
      "SELECT Query, Conversions, CostPerConversion, KeywordTextMatchingQuery, AdGroupId, AdGroupName, KeywordId, Cost " +
      "FROM SEARCH_QUERY_PERFORMANCE_REPORT " +
      "WHERE CampaignStatus = ENABLED AND CampaignId = " + campaign.getId() +
      " AND AdGroupStatus = ENABLED" +
      " AND QueryTargetingStatus = NONE" +
      " AND Cost > " + THRESHOLD_CPA
      );

        var rows = report.rows();
        while (rows.hasNext()) {
          var row = rows.next();

          if(row["Conversions"] == 0 && row["Cost"] > (targetCpa * config.THRESHOLD_MULTIPLIER) || row["Conversions"] > 0.99 && row["CostPerConversion"] > (targetCpa * config.THRESHOLD_MULTIPLIER)) {
            
            Logger.log("-----");
            Logger.log(campaign.getName());
            Logger.log("Target CPA: " + targetCpa);
            Logger.log("-----");

            var query = "";

              if (row["Query"].split(" ").length < 10) {
                query = "[" + row["Query"] + "]";
              } else {
                for (var i = 0; (i < row["Query"].split(" ").length) && (i < 10); i++){ 
                  query += row["Query"].split(" ")[i] + " ";                 
                }
                query = '"' + query.replace(/\s+$/,'') + '"';
              }

				if(config.LOG === true){
                  Logger.log("Query: " + row["Query"]); 
                  Logger.log("Clean Query: " + query); 
                  Logger.log("Keyword: " + row["KeywordTextMatchingQuery"]); 
                  Logger.log("KeywordId: " + row["KeywordId"]); 
                }

            if (row["Query"] == row["KeywordTextMatchingQuery"]) {   
              	var ids = [];
              	var adgroupId = row["AdGroupId"];
              	var keywordId = row["KeywordId"];
              	ids.push([adgroupId, keywordId]);
                var keywordIterator = AdsApp
                  .keywords()
                  .withIds(ids)
                  .withCondition("Status = ENABLED")
                  .get();

                while (keywordIterator.hasNext()) {
                  var keyword = keywordIterator.next();
                  keyword.pause();
                    Logger.log("Conversions: " + row["Conversions"]); 
                    Logger.log("Cost: " + row["Cost"]); 
                    Logger.log("CostPerConversion: " + row["CostPerConversion"]);   
                    Logger.log("Keyword paused: " + row["KeywordTextMatchingQuery"]);
                  	Logger.log(" ");
                }                
            } 
            
            else{    
              var ids = [row["AdGroupId"]];
              var adGroupIterator = AdsApp
                .adGroups()
                .withIds(ids)
                .get();

              while (adGroupIterator.hasNext()) {
                var adGroup = adGroupIterator.next();
                adGroup.createNegativeKeyword(query);
                  Logger.log("Conversions: " + row["Conversions"]); 
                  Logger.log("Cost: " + row["Cost"]); 
                  Logger.log("CostPerConversion: " + row["CostPerConversion"]);   
                  Logger.log("Query excluded: " + query + " in " + row["AdGroupName"]);
                  Logger.log(" ");
              }              
            } 

          } // row selector
        } // row iterator
    } // label iterator
  
} // function findQueries(campaign)

Sharing knowledge

Adsscripts.com is all about sharing knowledge. In the current market, PPC specialists like to keep their knowledge and experience to themselves. We're convinced that sharing knowledge can ensure that everyone gets better at their work. We want to change this by sharing our knowledge about scripts with everyone.

Do you also want to contribute? We are open to new ideas and feedback on everything you find on Adsscripts.com.

Contact us

Nils Rooijmans
Google Ads Scripter
Water Cooler Topics
Nils Rooijmans, Google Ads Scripter
Bas Baudoin
Teamleader PPC
Happy Leads
Bas Baudoin, Teamleader PPC
Martijn Kraan
Freelance PPC Specialist
Brightstep
Martijn Kraan, Freelance PPC Specialist
Tibbe van Asten
PPC Specialist
Founder Adsscripts
Tibbe van Asten, Senior PPC Automation Specialist