Automatisches ausschließen von Keywords in Anzeigengruppen [Google Ads Script]

AdWords accounts with a BROAD MATCH/EXACT MATCH bidding strategy can be very labor-intensive. In addition to the normal work of an AdWords campaign, new EXACT Match keywords must be excluded from the BROAD Match campaign. This painstaking work is rewarded with better CPCs because it doesn’t create competition of its own. To minimize this work, we’ve developed a script that takes care of this task.

The script checks campaigns with [Campaign Label], collects all keywords from the ad group(s) with the [Source Label], and adds them as negative keywords to the ad group with [Target Label]. In doing so, all existing negative keywords are deleted at the ad group level.

Setup of the Auto Exclude AdWords Script

1. Place script at the MCC level.
2. Open Google Docs Spreadsheet.
3. Copy spreadsheet id into the script.
4. Authorize the script.
5. Set prefillSpreadsheet to “true”, save and run preview.
6. Assign label names for Campaign Label, Source Label (Exact Match ad group(s)) and Target Label (Broad Match ad group(s)) in the spreadsheet.
7. Assign corresponding labels to campaigns and ad groups.
8. “Run preview”
9. If the test is satisfactory, run the script and set it to daily execution.

Notes / Limitations / Remarks

• The current version of the script has only been tested at the MCC level.
• The script is strongly inspired by this DSA Script. The original script also runs at the account level.
• The current version has not been tested for error tolerance. Please comment on any errors in this post.
• Further development for additional requirements is possible. Please comment!

//------------------------------------------------
// This Script aims to Auto-Exclude Keywords  
// from source labeled AdGroups and place them as
// negative keywords within another AdGroup within  
// the same campaign
// This Script was built for Campaigns split in
// BROAD MATCH and EXACT MATCH Keywords AdGroups.
// More info: https://webmasterei-prange.de/adwords-script-automatisches-auslassen-von-keywords-in-anzeigengruppen
// Strongly inspired by: Remko van der Zwaag & PDDS see: http://remkovanderzwaag.nl/blog/updated-adwords-script-auto-add-negative-keywords-dsa
//------------------------------------------------

var spreadsheetId = 'ENTER SPREADSHEET ID';
var prefillSpreadsheet = false; // When set to true, gets all accounts from the MCC account
  // and automagically adds their name and id to the spreadsheet
  // Use once, doesn't check for existing records
  // switch back to false after use
  // PREFERABLY RUN USING PREVIEW (true), CHANGE TO false AND SAVE

// The label a camaign is watched
// * Default - overwritable from spreadsheet
var campaign_label = 'BROAD | EXACT';

// The label on the source campaign(s)
// * Default - overwritable from spreadsheet
var adgroup_source_label = 'EXACT';

// The label on the target group
// * Default - overwritable from spreadsheet
var adgroup_target_label = 'BROAD';


function mapRowToInfo(row) {
  return {
  custId: row[1].trim(),
  cust: row[0],
  campaign_label: row[2],
  adgroup_source_label: row[3],
  adgroup_target_label: row[4]
  };
}


function main() {
  try {
  if (prefillSpreadsheet) {
  MccApp.accounts()
  .withLimit(50)
  .executeInParallel("getSSAccountInfo","saveSSAccountInfo");
  } else {
  var ids = getSpreadsheetIds();
  if (ids.length > 0) {
  MccApp.accounts()
  .withIds(ids)
  .withLimit(50)
  .executeInParallel("processAccount");
  }
  }
  } catch (e) {
  processAccount();
  }
}

function getSSAccountInfo() {
  var result = {
  custId: AdWordsApp.currentAccount().getCustomerId(),
  cust: AdWordsApp.currentAccount().getName()
  };
  Logger.log(result);
  return JSON.stringify(result);
}

function saveSSAccountInfo(response) {
  var ss;
  try {
  ss = SpreadsheetApp.openById(spreadsheetId);
  } catch (e) {
  }
  ss = ss.getSheets()[0];
  ss.appendRow(["Account Name", "Account ID", "Campaign Label", "Source Label", "Target Label"]);
  for (var i in response) {
  if(!response[i].getReturnValue()) { continue; }
  var rep = JSON.parse(response[i].getReturnValue());
  Logger.log(rep);
  ss.appendRow([rep.cust, rep.custId]);
  }
}

function getSpreadsheetIds() {
  var ids = [],
  ss,
  reAWId = /^([0-9]{3})-([0-9]{3})-([0-9]{4})$/;

  try {
  ss = SpreadsheetApp.openById(spreadsheetId);
  } catch (e) {
  return ids;
  }
  ss = ss.getSheets()[0];
  var rows = parseInt(ss.getLastRow());
  var range = ss.getRange("A1:Z" + rows).getValues();
  for (var i = 0; i  <  rows; i++) {
  var account = mapRowToInfo(range[i]);
  if (!reAWId.test(account.custId) || account.skip) {
  continue;
  }
  ids.push(account.custId);
  }
  return ids;
}

function getAccountInfo() {
  var ss;
  var reAWId = /^([0-9]{3})-([0-9]{3})-([0-9]{4})$/;
  var protoAccount = {
  custId: AdWordsApp.currentAccount().getCustomerId(),
  cust: AdWordsApp.currentAccount().getName(),
  include_label: campaign_label,
  source_label: adgroup_source_label,
  target_label: adgroup_target_label
  };
  try {
  ss = SpreadsheetApp.openById(spreadsheetId);
  } catch (e) {
  return protoAccount;
  }
  ss = ss.getSheets()[0];
  var rows = parseInt(ss.getLastRow());
  var range = ss.getRange("A1:Z" + rows).getValues();
  var ret_account;

  for (var i = 0; i  <  rows; i++) {
  var account = mapRowToInfo(range[i]);
  if (account.skip) {
  continue;
  }
  if (!reAWId.test(account.custId) || account.custId !== protoAccount.custId) {
  continue;
  }

  for(var key in account) {
  if (account[key] === '' || account[key] === '_ALL_') {
  account[key] = protoAccount[key];
  }
  }
  return account;
  }
  return protoAccount;
}


function processAccount() {
  var account = getAccountInfo();
  try {
  var campaigns = getCampaignsWithLabel(account.campaign_label);
  } catch (ex) {
  return;
  }
  if (campaigns.length === 0) {
  return;
  }
  var include_list = [];
  if (account.campaign_label !== '') {
  try {
  var iterator = getCampaignsWithLabel(account.campaign_label);
  while (iterator.hasNext()) {
  include_list.push(iterator.next().getId());
  }
  } catch (ex) {
  // pass
  }
  }
  while (campaigns.hasNext()) {
  var campaign = campaigns.next();
  var campaignName = campaign.getName()
  var keywords = getSourceAdgroup(adgroup_source_label,campaignName);
  setTargetAdgroupKeywords(adgroup_target_label,keywords,campaignName);
  }
}
 
function getCampaignWithLabel(label) {
  var campaignIterator = getCampaignsWithLabel(label);

  if (campaignIterator.hasNext()) {
  return campaignIterator.next();
  }

  return null;
}

function getCampaignsWithLabel(label) {
  
  return AdWordsApp.campaigns()
  .withCondition("LabelNames CONTAINS_ANY ['" + label + "']")
  .get();
}

function getCampaignsWithLabelArray(label) {
  var iterator = getCampaignsWithLabel(label);
  var campaigns = [];
  while (iterator.hasNext()) {
  campaigns.push(iterator.next());
  }
  return campaigns;
}

function getSourceAdgroup(adgroup_source_label,campaignName){
  var adGroupSelector = AdWordsApp
  .adGroups()
  .withCondition("CampaignName = '" + campaignName + "'")
  .withCondition("LabelNames CONTAINS_ANY ['" + adgroup_source_label + "']")
  .withCondition("Status = ENABLED");
  var adGroupIterator = adGroupSelector.get();
  var keywords = [];
  while (adGroupIterator.hasNext()) {
  var adGroup = adGroupIterator.next();
  keywords = keywords.concat(getKeyWords(adGroup));
  }
  return keywords;
}

function getKeyWords(adGroup){
  var keywords = []
  var keywordSelector = AdWordsApp
  .keywords()
  .withCondition("AdGroupName = '" + adGroup.getName() + "'")
  .withCondition("Status = ENABLED");

  var keywordIterator = keywordSelector.get();
  while(keywordIterator.hasNext()){
  var keyword = keywordIterator.next();
  keyword = keyword.getText();
  keywords.push(keyword)
  }
  return keywords;
}

function setTargetAdgroupKeywords(label,keywords,campaignName){
  var adGroupSelector = AdWordsApp
  .adGroups()
  .withCondition("LabelNames CONTAINS_ANY ['" + label + "']")
  .withCondition("CampaignName = '" + campaignName + "'")
  var adGroupIterator = adGroupSelector.get();
  
  while (adGroupIterator.hasNext()) {
  var adGroup = adGroupIterator.next();
  removeNegativeKeywords(adGroup);
  setNegativeKeywords(keywords,adGroup);
  }
}

function removeNegativeKeywords(targetAdGroup){
  var negativeKeywordSelector = targetAdGroup.negativeKeywords();
  var negativeKeywordIterator = negativeKeywordSelector.get();
  while (negativeKeywordIterator.hasNext()) {
  var negativeKeyword = negativeKeywordIterator.next();
  negativeKeyword.remove();
  }
}

function setNegativeKeywords(keywords,targetAdGroup) {
  //Logger.log(targetAdGroup.getCampaign().getName()+' '+targetAdGroup.getName()+' has AdGroups: '+keywords.length+' Number of Keywords:'+keywords.length);
  for (var i=0;i<  keywords.length; i++) {
  var keyword = keywords[i];
  Logger.log(keyword);
  targetAdGroup.createNegativeKeyword(keyword);
  }
}

autoExcludeAdGroupKeyWords.js hosted with ❤ by GitHub

Changelog:

V 0.01 added (tests only on MCC level)