Campaign-level Audiences Transition Tool

AdWords now supports Search and Shopping audiences at the campaign level. Using audiences in the past required applying them to each ad group within the campaign. The campaign-level audiences transition tool allows you to simplify your account setup by scanning your audiences at the ad group level and promoting eligible ones to the campaign level.

How it works

The script starts by reading configuration information from a Google spreadsheet, as shown below:

It then processes the campaigns in the account. For each campaign processed, the script starts by listing each ad group, and their target audiences. It will then promote eligible audiences to the campaign level. The ad group audiences are removed.

By default, all the ENABLED and PAUSED campaigns in your account are processed. You can change this behaviour by labeling a subset of your campaigns with a label and specifying it in the spreadsheet.

The script exports the new list of audiences into the Output tab of the spreadsheet. It can also send you an email summarizing the changes made.

How are audiences identified for promotion?

The script can operate in two modes.

In the basic mode, an audience is considered for promotion if

  • It targets all the ad groups in a campaign
  • The bid modifier for the audience is the same for all ad groups within that campaign
  • The ad group targeting settings ("Bid only" or "Target and bid") are the same for all ad groups within that campaign
  • The audience is not marked as a negative at the campaign level

In the advanced mode, an audience is considered for promotion if

  • It targets one or more ad groups in a campaign
  • The audience is not marked as a negative at the campaign level
  • If there are any ad groups that have no audiences, the audiences will only be promoted if the targeting settings are all "Bid only". This is to prevent any unintended traffic drop in the ad groups without audiences before the transition.

This behaviour is controlled by the Transition any audiences at the ad group level? setting in the configuration spreadsheet.

  • "No" is basic mode
  • "Yes" is advanced mode

How is the bid modifier calculated for promoted audience?

In the basic mode, the bid modifier at the ad group level is also used at the campaign level.

In advanced mode, you can choose the algorithm for calculating the bid modifier for the promoted audience. The following choices are available:

Algorithm Meaning
AVERAGE Uses the average of bid modifiers for all the adgroups that this audience targets.
WEIGHTED_SPEND_AVERAGE (Default) Uses the weighted average by spend of bid modifiers for all the ad groups that this audience targets. Last 30 days' stats are used. If there are no stats for any of the ad groups, AVERAGE method is used.
WEIGHTED_IMPRESSION_AVERAGE Uses the weighted average by impression of bid modifiers for all the adgroups that this audience targets. Last 30 days' stats are used. If there's no stats for any of the ad groups, AVERAGE method is used.

Scheduling

Since this is a one-time tool, you may run this script on a one-off basis to make the necessary changes. No scheduling is required.

Setup

  • Set up a script with the source code below. Use a copy of this template spreadsheet.
  • Specify the script configuration through the Configuration tab of the spreadsheet.
  • Don't forget to update SPREADSHEET_URL in your script.

Source code

// Copyright 2016, Google Inc. All Rights Reserved.
//
// 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
//
//     http://www.apache.org/licenses/LICENSE-2.0
//
// 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 express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

/**
 * @name Campaign Level Audiences Transition Tool
 *
 * @overview Campaign Level Audiences Transition Tool analyzes audiences at
 *     AdGroup level, identifies potential duplicates, and transition them to
 *     campaign level audiences. See
 *     https://developers.google.com/adwords/scripts/docs/solutions/campaign-audience-transition-tool
 *     for more details.
 *
 * @author AdWords Scripts Team [adwords-scripts@googlegroups.com]
 *
 * @version 1.2
 *
 * @changelog
 * - version 1.0
 *   - Released initial version.
 * - version 1.1
 *   - Performance improvements.
 *   - Proper handling of campaign-level excluded audiences.
 * - version 1.2
 *   - Better handling of potential timeouts for large campaigns.
 *   - Only process campaigns with ad group-level audiences.
 */

/**
 * Specifies the URL of the spreadsheet from which configuration is read and
 * results are exported. This should be a copy of https://goo.gl/zsS7lL.
 */
var SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL';

/* Constants for accessing spreadsheet settings. */

/**
 * Name of the spreadsheet range corresponds to the PromoteUniqueAudiences
 * setting. This setting determines whether any audience found at ad group level
 * should be promoted or not.
 */
var SHOULD_PROMOTE_UNIQUE_AUDIENCES_RANGE_NAME =
    'PromoteUniqueAudiences';

/**
 * Name of the spreadsheet range that corresponds to the
 * UniqueAudienceBidModiferTechnology setting. This setting specifies how to
 * calculate the campaign level bid modifier when unique audience targeting ad
 * groups are promoted.
 */
var UNIQUE_AUDIENCE_BID_MODIFIER_TECH_RANGE_NAME =
    'UniqueAudienceBidModiferTechnology';

/**
 * Name of the spreadsheet range that corresponds to the EmailAddress setting.
 * This setting provides the list of users that the script will email upon
 * completion.
 */
var EMAIL_ADDRESS_RANGE_NAME = 'EmailAddress';

/**
 * Name of the spreadsheet range that corresponds to the CampaignLabel setting.
 * This setting determines the list of campaigns in an account that the script
 * will process.
 */
var CAMPAIGN_LABEL_RANGE_NAME = 'CampaignLabel';

var CUSTOMER_ID_RANGE_NAME = 'CustomerId';
var LAST_RUN_RANGE_NAME = 'LastRun';

var BidModifierTech = {
  Average: 'AVERAGE',
  WeightedSpendAverage: 'WEIGHTED_SPEND_AVERAGE',
  WeightedImpressionAverage: 'WEIGHTED_IMPRESSION_AVERAGE',
};

var SheetNames = {
  Output: 'Output',
  Configuration: 'Configuration'
};

/**
 * The main method.
 */
function main() {
  var spreadsheetOutput = [];

  var spreadsheet = validateSpreadsheet();
  var outputSheet = loadOutputsheet(spreadsheet);
  var config = loadConfiguration(spreadsheet);
  ensureCampaignLabelExists(config);

  var campaignMap = getCampaigns(config);

  var processedCount = 0;
  for (var campaignId in campaignMap) {
    try {
      processCampaign(campaignId, campaignMap[campaignId], config,
          spreadsheetOutput);
      processedCount++;
    } catch (err) {
      Logger.log(err);
    }

    // Stop processing if we only have 5 minutes left.
    if (AdWordsApp.getExecutionInfo().getRemainingTime() < 300) {
      Logger.log('Less than 5 mins. of execution time remaining. ' +
          'Stopping now.');
      break;
    }
  }
  writeToSpreadsheet(spreadsheetOutput, outputSheet);
  sendEmailReport(config, processedCount);
}

function prepareAudienceLookupMap(adGroupMap, campaignDetail) {
  var audienceListLookupMap = {};
  var excludedCampaignAudienceListLookupMap = {};

  for (var adGroupId in adGroupMap) {
    var adGroupDetail = adGroupMap[adGroupId];
    for (var audienceId in adGroupDetail['Audiences']) {
      audienceListLookupMap[audienceId] = 1;
    }
  }

  for (var excludedAudienceId in campaignDetail['ExcludedAudiences']) {
    excludedCampaignAudienceListLookupMap[
        'boomuserlist::' + excludedAudienceId] = 1;
  }

  removeExcludedCriteriaFromPromotionlist(audienceListLookupMap,
      excludedCampaignAudienceListLookupMap);
  return audienceListLookupMap;
}

/**
 * Gets a campaign by its ID and channel type.
 *
 * @param {number} campaignId the campaign ID to retrieve.
 * @param {string} channelType the advertising channel type for the campaign.
 *    Search or Shopping.
 *
 * @return {Campaign|ShoppingCampaign} a campaign object.
 */
function getCampaign(campaignId, channelType) {
  var selector = null;

  switch (channelType) {
    case 'Search':
      selector = AdWordsApp.campaigns();
      break;
    case 'Shopping':
      selector = AdWordsApp.shoppingCampaigns();
      break;
    default:
      return null;
  }

  return selector.withIds([campaignId]).get().next();
}

/**
 * Gets the new bid modifier for an audience.
 *
 * @param {Object<string, Object>} adGroupMap the map with key as ad group ID,
 *      and value as ad group details.
 * @param {number} audienceId the audience ID.
 * @param {Object} config the configuration object loaded from spreadsheet.
 *
 * @return {number} the new bid modifier.
 */
function getNewBidModifierForAudience(adGroupMap, audienceId, config) {
  var bidModifier = 0;

  if (config[SHOULD_PROMOTE_UNIQUE_AUDIENCES_RANGE_NAME]) {
    switch (config[UNIQUE_AUDIENCE_BID_MODIFIER_TECH_RANGE_NAME]) {
      case BidModifierTech.Average:
        bidModifier = getAverageBidModifier(adGroupMap, audienceId);
        break;
      case BidModifierTech.WeightedSpendAverage:
        bidModifier = getWeightedStatBidModifier(adGroupMap,
            audienceId, 'Cost');
        break;
      case BidModifierTech.WeightedImpressionAverage:
        bidModifier = getWeightedStatBidModifier(adGroupMap,
            audienceId, 'Impressions');
        break;
      default:
        throw new Error('Unknown value for ' +
            UNIQUE_AUDIENCE_BID_MODIFIER_TECH_RANGE_NAME + ': "%s".',
            config[UNIQUE_AUDIENCE_BID_MODIFIER_TECH_RANGE_NAME]);
    }
  } else {
    bidModifier = getMatchingBidModifier(adGroupMap, audienceId);
  }
  return bidModifier;
}

/**
 * Gets a selector for audiences.
 *
 * @param {string} channelType the advertising channel type for the campaign.
 *    Search or Shopping.
 * @param {number} campaignId the campaign ID to retrieve audiences from.
 *
 * @return {Selector} a selector for getting the audiences.
 */
function getAudienceSelector(channelType, campaignId) {
  var selector = null;

  switch (channelType) {
    case 'Search':
      selector = AdWordsApp.adGroupTargeting();
      break;
    case 'Shopping':
      selector = AdWordsApp.shoppingAdGroupTargeting();
      break;
    default:
      return null;
  }
  selector = selector.audiences()
    .withCondition('Status IN [ACTIVE, PAUSED]')
    .withCondition('CampaignId = ' + campaignId)
    .withCondition('AdGroupStatus IN [ENABLED, PAUSED]');
  return selector;
}

/**
 * Removes the promoted audiences from the ad group.
 *
 * @param {number} campaignId the campaign ID to remove promoted audiences
 *    from.
 * @param {string} channelType the advertising channel type for the campaign.
 *    Search or Shopping.
 */
function removeAudiencesFromAdGroup(campaignId, channelType) {
  var remainingAudiencesCount =
      getAudienceSelector(channelType, campaignId).get().totalNumEntities();

  while (remainingAudiencesCount > 0) {
    var audiences = getAudienceSelector(channelType, campaignId).get();
    while (audiences.hasNext()) {
      var audience = audiences.next();
      audience.remove();
    }
    if (AdWordsApp.getExecutionInfo().isPreview()) {
      remainingAudiencesCount = 0;
      Logger.log('Preview run. Up to 50k ad group audiences removed; ' +
          'skipping remaining.');
    } else {
      remainingAudiencesCount =
          getAudienceSelector(channelType, campaignId).get().totalNumEntities();
    }
  }

  // This is a temporary workaround to clear any batch caching so
  // ad group level removes are not combined with campaign level additions.
  getAudienceSelector(channelType, campaignId).get();
}

/**
 * Add promoted audiences to a campaign.
 *
 * @param {Object<string, number>} promotedAudienceMap a map with key as the
 *      audience ID, and value as the proposed bid modifier for that audience.
 * @param {Campaign} campaign the campaign to which new audiences are added.
 *
 * @return {Array.<Array.<string>>} the output details of promoted audiences,
 *      to be appended to the spreadsheet.
 */
function addPromotedAudiencesToCampaign(promotedAudienceMap, campaign) {
  var spreadsheetOutput = [];

  for (var audienceId in promotedAudienceMap) {
    // Add campaign level criteria.
    var bidModifier = promotedAudienceMap[audienceId];
    audienceId = trimAudienceTypePrefix(audienceId);

    campaign.targeting()
        .newUserListBuilder()
        .withAudienceId(audienceId)
        .withBidModifier(bidModifier)
        .build();

    spreadsheetOutput.push([campaign.getName(), audienceId, bidModifier]);
  }
  return spreadsheetOutput;
}

function appendSpreadsheetOutput(output, spreadsheetOutput, targetSetting) {
  for (var i = 0; i < output.length; i++) {
    output[i].push(targetSetting);
    spreadsheetOutput.push(output[i]);
  }
}

/**
 * Gets the audience list for promotion.
 *
 * @param {Object<string, Object>} adGroupMap the map with key as ad group ID,
 *      and value as ad group details.
 * @param {Object} config the configuration object loaded from spreadsheet.
 * @param {Object} campaignDetail details of the campaign that is being
 *      processed.
 *
 * @return {Object<string, number>} a map with key as the audience ID to be
 *      promoted, and value as its proposed bid modifier.
 */
function getAudienceListForPromotion(adGroupMap, config, campaignDetail) {
  var audienceListLookupMap = prepareAudienceLookupMap(
      adGroupMap, campaignDetail);

  var promotingAudienceList = [];
  if (config[SHOULD_PROMOTE_UNIQUE_AUDIENCES_RANGE_NAME]) {
    // We want to promote all the remaining audiences in
    // audienceListLookupMap (including audiences that do not exist
    // in all ad groups).
    promotingAudienceList = Object.keys(audienceListLookupMap);
  } else {
    // Skip all audiences that are missing in one or more ad groups.
    promotingAudienceList = getAudienceListForConservativePromotion(
      audienceListLookupMap, adGroupMap);
  }

  var promotedAudienceMap = {};

  if (promotingAudienceList.length !=
      Object.keys(audienceListLookupMap).length) {
    Logger.log('Not all ad group level audiences can be promoted. ' +
        'Skipping this campaign.');
  } else {
    for (var i = 0; i < promotingAudienceList.length; i++) {
      var audienceId = promotingAudienceList[i];
      var bidModifier = getNewBidModifierForAudience(adGroupMap,
                                                     audienceId, config);

      // We could not find a valid bid modifier, so we won't promote this
      // audience.
      if (!bidModifier) {
        continue;
      }

      promotedAudienceMap[audienceId] = bidModifier;
    }
  }
  return promotedAudienceMap;
}

/**
 * Check and pause a campaign.
 *
 * @param {Campaign} campaign the campaign to be paused.
 *
 * @return {boolean} true if the campaign was paused, false otherwise.
 */
function checkAndPauseCampaign(campaign) {
  if (campaign.isEnabled()) {
    campaign.pause();
    return true;
  }
  return false;
}

/**
 * Updates the campaign settings after promotion.
 *
 * @param {Object<string, boolean>} promotedAudienceMap map with key as an
 *      audience ID to promote, and value as a boolean.
 * @param {Object} config the configuration object loaded from the spreadsheet.
 * @param {boolean} enableCampaign true if the campaign should be enabled,
 *      false otherwise.
 * @param {string} targetSetting the campaign's new target setting for
 *      user lists.
 * @param {Campaign} campaign the campaign to be updated.
 */
function updateCampaignSettingsAfterPromotion(promotedAudienceMap, config,
    enableCampaign, targetSetting, campaign) {
  if (Object.keys(promotedAudienceMap).length > 0) {
    // Set the campaign's target setting.
    campaign.targeting().setTargetingSetting('USER_INTEREST_AND_LIST',
                                             targetSetting);
  }

  // The campaign is ready to serve. Enable it if we had paused it earlier.
  if (enableCampaign) {
    campaign.enable();
  }
}

/**
 * Processes a campaign.
 *
 * @param {number} campaignId the campaign ID.
 * @param {Object} campaignDetail the campaign details object.
 * @param {Object} config the configuration object.
 * @param {Array.<Array.<string>>} spreadsheetOutput the contents to be written
 *     to the output sheet.
 */
function processCampaign(campaignId, campaignDetail, config,
    spreadsheetOutput) {
  Logger.log('Processing campaign "%s".', campaignDetail['Name']);

  // Skip if there are no ad group-level audiences.
  if (campaignDetail['AdGroupAudiences'] === 0) {
    Logger.log('Campaign "%s" has no ad group-level audiences.',
        campaignDetail['Name']);
    return;
  }

  // Skip if there are too many audiences to process due to limitations.
  if (campaignDetail['AdGroupAudiences'] > 200000) {
    Logger.log('Campaign "%s" is too large to process.',
        campaignDetail['Name']);
    return;
  }

  getAdGroups(campaignDetail, campaignId);
  populateCampaignExcludedAudienceDetails(campaignDetail, campaignId);

  var channelType = campaignDetail['ChannelType'];
  var adGroupMap = campaignDetail['AdGroups'];

  // 1. Get the targeting setting for the ad groups.
  var targetSetting = getTargetSettingForAdGroups(adGroupMap);

  if (!targetSetting) {
    Logger.log('Target setting of all ad groups don\'t match. Skipping ' +
               'this campaign.');
    return;
  }

  var promotedAudienceMap = getAudienceListForPromotion(adGroupMap, config,
      campaignDetail);

  // Skip if there isn't enough time left based on campaign size. Using 7,500
  // operations per minute as the approximation.
  var estimatedTimeToProcessCampaign = campaignDetail['AdGroupAudiences']
      / (7500 / 60);
  if (estimatedTimeToProcessCampaign >=
      AdWordsApp.getExecutionInfo().getRemainingTime()) {
    Logger.log('Not enough time remaining to process campaign "%s".',
        campaignDetail['Name']);
    return;
  }

  if (Object.keys(promotedAudienceMap).length > 0) {
    // Pause the campaign. This is to prevent the campaign serving to the wrong
    // audience or serving without bid adjustments during the changes.
    var campaign = getCampaign(campaignId, channelType);
    var enableCampaign = checkAndPauseCampaign(campaign);

    removeAudiencesFromAdGroup(campaignId, channelType);
    var output = addPromotedAudiencesToCampaign(promotedAudienceMap, campaign);
    appendSpreadsheetOutput(output, spreadsheetOutput, targetSetting);

    updateCampaignSettingsAfterPromotion(promotedAudienceMap, config,
        enableCampaign, targetSetting, campaign);
  }
}

/**
 * Trims the audience type prefix from audience Id.
 *
 * @param {string} audienceId The audience ID with type prefix.
 *     E.g. boomuserlist::123456
 *
 * @return {string} The audience ID without type prefix.
 */
function trimAudienceTypePrefix(audienceId) {
  var splitIndex = audienceId.indexOf('::');
  if (splitIndex != -1) {
    audienceId = audienceId.substring(splitIndex + 2);
  }
  return audienceId;
}

/**
 * Sends an email report.
 *
 * @param {Object} config the configuration object.
 * @param {number} processedCount the number of campaigns processed.
 */
function sendEmailReport(config, processedCount) {
  if (config[EMAIL_ADDRESS_RANGE_NAME]) {
    var customerId = AdWordsApp.currentAccount().getCustomerId();
    var message = [];
    message.push('<p>Hi,</p>',
                 '<p>Campaign level Audiences Transition tool processed <b>' +
                  processedCount +
                 '</b> campaigns for customer ID: <b>' + customerId +
                 '</b>. See <a href="' + SPREADSHEET_URL +
                 '">the output spreadsheet</a> for detailed report.</p>',
                 '<p>Cheers,<br />AdWords Scripts Team.</p>'
                 );

    MailApp.sendEmail({
      to: config[EMAIL_ADDRESS_RANGE_NAME],
      subject: 'Campaign level Audiences Transition tool (' + customerId + ')',
      htmlBody: message.join('\n'),
    });
  }
}

/**
 * Writes output and metadata to spreadsheet.
 *
 * @param {Array.<Array.<string>>} spreadsheetOutput A rectangular array of data
 *     to be written to the output sheet.
 * @param {Sheet} outputSheet the output sheet.
 */
function writeToSpreadsheet(spreadsheetOutput, outputSheet) {
  if (spreadsheetOutput.length > 0) {
    var lastRow = outputSheet.getLastRow();
    outputSheet.insertRowsAfter(outputSheet.getMaxRows(),
        spreadsheetOutput.length);
    outputSheet.getRange(lastRow + 1, 1, spreadsheetOutput.length, 4)
        .setValues(spreadsheetOutput);
  }
  var spreadsheet = outputSheet.getParent();
  spreadsheet.getRangeByName(CUSTOMER_ID_RANGE_NAME).setValue(
      AdWordsApp.currentAccount().getCustomerId());
  spreadsheet.getRangeByName(LAST_RUN_RANGE_NAME).setValue(new Date());
}

/**
 * Ensure that the spreadsheet is valid.
 *
 * @return {Sheet} the output sheet within the spreadsheet.
 *
 * @throws {Error} if spreadsheet is missing, or the Output sheet is missing.
 */
function validateSpreadsheet() {
  // Get spreadsheet by ID.
  var spreadsheet = null;
  try {
    spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  } catch (err) {
    Logger.log(err);
  }

  if (!spreadsheet) {
    throw new Error('Spreadsheet is missing or unavailable. Please ensure ' +
        'the URL is correct and that you have permission to edit.');
  }
  return spreadsheet;
}

/**
 * Loads the output sheet from the spreadsheet.
 *
 * @param {Spreadsheet} spreadsheet the spreadsheet object.
 *
 * @return {Sheet} the output sheet object.
 *
 * @throws {Error} if the sheet is missing.
 */
function loadOutputsheet(spreadsheet) {
  var outputSheet = spreadsheet.getSheetByName(SheetNames.Output);

  if (!outputSheet) {
    throw new Error('A sheet named "' + SheetNames.Output + '" is missing ' +
        'in the spreadsheet. Make sure you create the spreadsheet as a ' +
        'copy of the template spreadsheet.');
  }
  return outputSheet;
}

/**
 * Reads a setting from the spreadsheet.
 *
 * @param {Spreadsheet} spreadsheet the spreadsheet object.
 * @param {string} configName the setting name.
 *
 * @return {string} the setting value.
 */
function readSetting(spreadsheet, configName) {
  return spreadsheet.getRangeByName(configName).getValue();
}

/**
 * Loads configuration from the spreadsheet.
 *
 * @param {Spreadsheet} spreadsheet the spreadsheet object.
 *
 * @return {Object} the configuration object from the spreadsheet.
 *
 * @throws {Error} if the configuration cannot be read.
 */
function loadConfiguration(spreadsheet) {
  var config = null;

  try {
    config = {};
    config[UNIQUE_AUDIENCE_BID_MODIFIER_TECH_RANGE_NAME] =
        readSetting(spreadsheet, UNIQUE_AUDIENCE_BID_MODIFIER_TECH_RANGE_NAME);
    config[SHOULD_PROMOTE_UNIQUE_AUDIENCES_RANGE_NAME] =
        readSetting(spreadsheet,
            SHOULD_PROMOTE_UNIQUE_AUDIENCES_RANGE_NAME) === 'Yes';
    config[CAMPAIGN_LABEL_RANGE_NAME] =
        readSetting(spreadsheet, CAMPAIGN_LABEL_RANGE_NAME);
    config[EMAIL_ADDRESS_RANGE_NAME] =
        readSetting(spreadsheet, EMAIL_ADDRESS_RANGE_NAME);
  } catch (err) {
    Logger.log(err);
  }

  if (!config) {
    throw new Error('Failed to read configuration from spreadsheet. Make ' +
        'sure you create the spreadsheet as a copy of the template ' +
        'spreadsheet.');
  }

  return config;
}

/**
 * Ensure that campaign label exists in the account if specified.
 *
 * @param {Object} config the configuration object from the spreadsheet.
 *
 * @throws {Error} if campaign label is specified, but missing.
 */
function ensureCampaignLabelExists(config) {
  if (config[CAMPAIGN_LABEL_RANGE_NAME]) {
    var campaignLabelCount = AdWordsApp.labels()
    .withCondition('Name = "' + config[CAMPAIGN_LABEL_RANGE_NAME] + '"')
    .get().totalNumEntities();

    if (campaignLabelCount == 0) {
      throw 'Label named "' + config[CAMPAIGN_LABEL_RANGE_NAME] +
          '" is missing. Please check the campaign label setting in the ' +
          'spreadsheet.';
    }
  }
}

/**
 * Gets the matching bid modifier for a given audience ID when it targets a set
 * of ad groups.
 *
 * @param {Object<number, Object>} adGroupLookupMap A map that stores the
 *     details of all the ad groups. The key is the ad group ID, and value is an
 *     object that stores the ad group details.
 * @param {number} audienceId The ID of the audience.
 *
 * @return {number} the Bid modifier for the audience.
 * @throws {Error} if the audience targeting is missing in the ad group, or
 *     if the adGroupLookupMap is empty.
 */
function getMatchingBidModifier(adGroupLookupMap, audienceId) {
  var bidModifier = 0;
  for (var adGroupId in adGroupLookupMap) {
    if (audienceId in adGroupLookupMap[adGroupId]['Audiences']) {
      var searchAudience = adGroupLookupMap[adGroupId]['Audiences'][audienceId];

      if (bidModifier == 0) {
        bidModifier = searchAudience['BidModifier'];
      } else if (searchAudience['BidModifier'] !== bidModifier) {
        return 0;
      }
    } else {
      throw new Error(Utilities.formatString('AdGroup ID: %s doesn\'t ' +
          'target Audience ID: %s, so no matching bid modifier was found.',
           adGroupId, trimAudienceTypePrefix(audienceId)));
    }
  }
  return bidModifier;
}

/**
 * Gets the bid modifier for a given audience ID using the weighted stats of a
 * given set of ad groups it targets.
 *
 * @param {Object<number, Object>} adGroupLookupMap A map that stores the
 *     details of all the ad groups. The key is the ad group ID, and value is an
 *     object that stores the ad group details.
 * @param {number} audienceId The ID of the audience.
 * @param {string} statName The name of the stat to use for calculating
 *     weighted averages.
 *
 * @return {number} the Bid modifier for the audience.
 * @throws {Error} if the calculated bid modifier is zero, or the totals of the
 *      stats is zero.
 */
function getWeightedStatBidModifier(adGroupLookupMap, audienceId,
    statName) {
  var adGroupIds = Object.keys(adGroupLookupMap);

  var bidModifier = 0;
  var totalStats = 0;

  for (var adGroupId in adGroupLookupMap) {
    var adGroupDetails = adGroupLookupMap[adGroupId];
    // Since we pick unique audience ID, this audience may not be targeting
    // ad group ID.
    if (audienceId in adGroupLookupMap[adGroupId]['Audiences']) {
      var searchAudience =
          adGroupLookupMap[adGroupId]['Audiences'][audienceId];
      var stat = parseFloat(adGroupDetails[statName]);
      bidModifier += searchAudience['BidModifier'] * stat;
      totalStats += stat;
    }
  }

  if (bidModifier == 0 || totalStats == 0) {
    Logger.log(Utilities.formatString('No stats found for audience ID: %s. ' +
        'Using average bid modifier.', trimAudienceTypePrefix(audienceId)));
    return getAverageBidModifier(adGroupLookupMap, audienceId);
  }

  return Number((bidModifier / totalStats).toFixed(2));
}

/**
 * Gets the average bid modifier for a given audience ID for a given set of
 * ad groups it targets.
 *
 * @param {Object<number, Object>} adGroupLookupMap A map that stores the
 *     details of all the ad groups. The key is the ad group ID, and value is
 *     an object that stores the ad group details.
 * @param {number} audienceId The ID of the audience.
 *
 * @return {number} the Bid modifier for the audience.
 */
function getAverageBidModifier(adGroupLookupMap, audienceId) {
  var bidModifierSum = 0;
  var count = 0;

  for (var adGroupId in adGroupLookupMap) {
    // Since we pick unique audience ID, this audience may not be targeting
    // ad group ID.
    if (audienceId in adGroupLookupMap[adGroupId]['Audiences']) {
      var searchAudience =
          adGroupLookupMap[adGroupId]['Audiences'][audienceId];
      var bidModifier = searchAudience['BidModifier'];
      bidModifierSum += bidModifier;
      count++;
    }
  }

  if (count == 0 || bidModifierSum == 0) {
    Logger.log(Utilities.formatString('Bid modifier for audience ID: %s ' +
        'is zero.', trimAudienceTypePrefix(audienceId)));
    return 0;
  }

  // Round off bid modifier to 2 places.
  return Number((bidModifierSum / count).toFixed(2));
}

/**
 * Removes excluded criteria from promotion list.
 *
 * @param {Object<number, bool>} audienceListLookupMap The map to store all the
 *     audiences found so far.
 * @param {Object<number, bool>} excludedCampaignAudienceListLookupMap The map
 *     to store all the campaign level excluded audiences found so far.
 */
function removeExcludedCriteriaFromPromotionlist(audienceListLookupMap,
    excludedCampaignAudienceListLookupMap) {
  // Remove all the excluded audiences from the promotion list.

  for (excludedAudienceId in excludedCampaignAudienceListLookupMap) {
    if (excludedAudienceId in audienceListLookupMap) {
      Logger.log('--Audience ID: %s is excluded on the campaign, removing ' +
          'from promotion list.', trimAudienceTypePrefix(excludedAudienceId));
      delete audienceListLookupMap[excludedAudienceId];
    }
  }
}

/**
 * Gets the common target setting for a list of ad groups.
 *
 * @param {Object<number, Object>} adGroupMap The map of ad groups being
 *     processed.
 *
 * @return {string} The common target setting, or null if no common target
 *     setting can be found.
 */
function getTargetSettingForAdGroups(adGroupMap) {
  // 1. See if all the ad groups have the same targeting setting.
  var targetSetting = null;
  var blankAdGroupCount = 0;
  var targetSettingMatches = true;
  for (adGroupId in adGroupMap) {

    var adGroupDetails = adGroupMap[adGroupId];

    // Skip checking the target settings for ad groups that have no search
    // audience criteria in them.
    var numCriteria = Object.keys(adGroupDetails['Audiences']).length;

    if (numCriteria == 0) {
      Logger.log('--Adgroup "%s" has no search audience criteria, skipping...',
          adGroupDetails['Name']);
      blankAdGroupCount++;
      continue;
    }

    var tempTargetSetting = adGroupDetails['TargetingSetting'];

    if (!targetSetting) {
      targetSetting = tempTargetSetting;
    } else if (targetSetting !== tempTargetSetting) {
      targetSettingMatches = false;
      break;
    }
  }

  // Empty ad groups are skipped over only if the overall target setting
  // is "Bid Only".
  if (blankAdGroupCount != 0 && targetSetting !== 'TARGET_ALL_TRUE') {
    targetSettingMatches = false;
  }

  if (targetSettingMatches) {
    return targetSetting;
  } else {
    return null;
  }
}

/**
 * Gets the audience list for conservative promotion.
 *
 * @param {Object<number, bool>} audienceListLookupMap The map to keep track
 *     of all the audience IDs.
 * @param {Object<number, Object>} adGroupLookupMap The map to keep track of
 *     all the ad groups.
 *
 * @return {Array.<number>} The list of all the audiences to be promoted.
 */
function getAudienceListForConservativePromotion(audienceListLookupMap,
    adGroupLookupMap) {
  var promotingAudienceList = [];

  for (var audienceId in audienceListLookupMap) {
    var audienceSettingsMatch = true;

    // Since bid modifier can be from 0.1 to 10.0, let's initialize the
    // variable to 0.
    var bidModifier = 0;
    for (adGroupId in adGroupLookupMap) {
      var adGroupDetails = adGroupLookupMap[adGroupId];

      if (audienceId in adGroupDetails['Audiences']) {
        var searchAudience = adGroupDetails['Audiences'][audienceId];

        if (!bidModifier) {
          bidModifier = searchAudience['BidModifier'];
        } else {
          var newBidModifier = searchAudience['BidModifier'];
          if (bidModifier !== newBidModifier) {
            Logger.log('--Audience ID %s has a mismatch on bid modifier. ' +
                       'Removing from promotion list.', audienceId);
            audienceSettingsMatch = false;
            break;
          }
        }
      } else {
        Logger.log('--Audience ID %s is missing in ad group: "%s". Removing ' +
            'from promotion list.', audienceId, adGroupDetails['Name']);
        audienceSettingsMatch = false;
        break;
      }
    }

    if (audienceSettingsMatch) {
      promotingAudienceList.push(audienceId);
    }
  }
  return promotingAudienceList;
}

/**
 * Gets the list of campaigns to be processed.
 *
 * @param {Object} config the configuration object from the spreadsheet.
 *
 * @return {Object.<string, Object>} a map with campaign ID as the key and
 *      campaign details as the value.
 */
function getCampaigns(config) {
  // Filter on label IDs rather than names for faster performance.
  var query = 'Select CampaignId, AdvertisingChannelType, CampaignName from ' +
      'CAMPAIGN_PERFORMANCE_REPORT where CampaignStatus in [ENABLED, PAUSED] ' +
      'and AdvertisingChannelType in [SEARCH, SHOPPING] ' +
      'and AdvertisingChannelSubType not_in ' +
      '[SEARCH_EXPRESS, UNIVERSAL_APP_CAMPAIGN]';

  if (config[CAMPAIGN_LABEL_RANGE_NAME]) {
    var includedLabel = AdWordsApp.labels().withCondition('Name="' +
        config[CAMPAIGN_LABEL_RANGE_NAME] + '"').get().next();

    query += ' and LabelIds CONTAINS_ALL["' + includedLabel.getId() + '"]';
  }

  var report = AdWordsApp.report(query).rows();
  var retval = {};
  while (report.hasNext()) {
    var row = report.next();
    var campaignId = row['CampaignId'];
    var campaignName = row['CampaignName'];
    var channelType = row['AdvertisingChannelType'];
    var adGroupAudienceCount = getAudienceSelector(channelType, campaignId)
        .get().totalNumEntities();
    retval[campaignId] = {
      'Id': campaignId,
      'Name': campaignName,
      'ChannelType': channelType,
      'AdGroupAudiences': adGroupAudienceCount,
      'AdGroups': {
       },
       'Audiences': {
       },
       'ExcludedAudiences': {
       }
    };
  }

  return retval;
}

/**
 * Populates individual campaign objects in a map with campaign audience
 *     targeting details.
 *
 * @param {Object} campaignDetail the campaign details object.
 * @param {number} campaignId the campaign ID.
 */
function populateCampaignExcludedAudienceDetails(campaignDetail, campaignId) {
  var excludedAudiences = AdWordsApp.targeting().excludedAudiences()
      .withCondition('CampaignId = ' + campaignId).get();

  while (excludedAudiences.hasNext()) {
    var excludedAudience = excludedAudiences.next();
    campaignDetail['ExcludedAudiences'][excludedAudience.getAudienceId()] = 1;
  }
}

/**
 * Populates individual campaign objects in a map with ad group details.
 *
 * @param {Object} campaignDetail the campaign details object.
 * @param {number} campaignId the campaign ID.
 */
function getAdGroups(campaignDetail, campaignId) {
  var query = 'Select CampaignId, AdGroupId, AdGroupName, ' +
        'Cost, Impressions from ADGROUP_PERFORMANCE_REPORT where ' +
        'AdGroupStatus in [ENABLED, PAUSED] and CampaignId = ' + campaignId +
        ' during LAST_30_DAYS';

  var report = AdWordsApp.report(query).rows();

  var adGroupMap = {};

  while (report.hasNext()) {
    var row = report.next();
    var campaignId = row['CampaignId'];
    var adGroupId = row['AdGroupId'];
    var adGroupName = row['AdGroupName'];
    var cost = row['Cost'];
    var impressions = row['Impressions'];

    var adGroupDetails = {
      'Id': adGroupId,
      'Name': adGroupName,
      'Audiences': {
      },
      'ExcludedAudiences': {
      },
      'Cost': cost,
      'Impressions': impressions
    };

    campaignDetail['AdGroups'][adGroupId] = adGroupDetails;
    adGroupMap[adGroupId] = adGroupDetails;
  }

  getAdGroupAudiences(campaignId, adGroupMap, campaignDetail);
}

/**
 * Populates individual ad group objects in a map with ad group audience
 *     targeting details and target settings.
 *
 * @param {number} campaignId a single campaign ID to retrieve
 *     audience data for.
 * @param {Object.<string, Object>} adGroupMap a map with key as ad group ID
 *     and value as ad group details.
 * @param {Object} campaignDetail the campaign details object.
 */
function getAdGroupAudiences(campaignId, adGroupMap, campaignDetail) {

    var query = 'Select CampaignId, AdGroupId, Id, Criteria, BidModifier, ' +
      'IsRestrict, Impressions, Cost from AUDIENCE_PERFORMANCE_REPORT ' +
      'where CriterionAttachmentLevel = ADGROUP ' +
      'and Status in [ENABLED, PAUSED] ' +
      'and AdGroupStatus IN [ENABLED, PAUSED] ' +
      'and CampaignId = ' + campaignId + ' during LAST_30_DAYS';

    var report = AdWordsApp.report(query, {apiVersion: 'v201702'}).rows();

    while (report.hasNext()) {
      var row = report.next();
      var campaignId = row['CampaignId'];
      var adGroupId = row['AdGroupId'];
      var id = row['Id'];
      var criteria = row['Criteria'];
      var bidModifier = row['BidModifier'];
      var impressions = row['Impressions'];
      var cost = row['Cost'];
      var isRestrict = row['IsRestrict'];

      if (isRestrict === 'true') {
        var targetingSetting = 'TARGET_ALL_FALSE';
      } else {
        var targetingSetting = 'TARGET_ALL_TRUE';
      }

      // Bid Modifier comes back as "x.xx%" or --, which needs to be
      // converted into an actual number.
      if (bidModifier === '--') {
        bidModifier = 1;
      } else {
        bidModifier = 1 + parseInt(bidModifier.replace(/\%/g, ''), 10) / 100;
      }

      if (adGroupId in adGroupMap) {
        adGroupMap[adGroupId]['Audiences'][criteria] = {
          'Id': id,
          'Audience': criteria,
          'BidModifier': bidModifier,
          'Impressions': impressions,
          'Cost': cost
        };
        if (!adGroupMap[adGroupId]['TargetingSetting']) {
          adGroupMap[adGroupId]['TargetingSetting'] = targetingSetting;
        }
      }
    }
}

Send feedback about...

AdWords Scripts
AdWords Scripts
Need help? Visit our support page.