Multi Bidder - Single Account

Bidding icon

Optimizing keyword bids is one of the most important activities when managing Google Ads accounts. Keyword bids have a direct impact on the placement of an ad (its rank in Google Search) as well as its cost. You often need to develop your own bidding strategies to meet your goals.

Automated Rules is a Google Ads feature that lets you modify keyword bids based on set criteria, and do so on a schedule. If you have a large number of Automated Rules, they can become difficult to manage.

Multi Bidder offers functionality similar to that of Automated Rules based on a spreadsheet. Each row in a spreadsheet is in effect equivalent to an entire Automated Rule. Managing these rules in a spreadsheet is easier than managing them in Google Ads.

Screenshot of multi bidder spreadsheet

The spreadsheet above demonstrates a single rule that does the following:

  • Looks at statistics for THIS_WEEK_SUN_TODAY.
  • Finds all keywords in Campaign #1 that received more than 1 impression and whose CTR is greater than 25%.
  • Increases their bids by 10%, while not exceeding $1.40.

How it works

The following columns in the spreadsheet must be present:

  • Action
  • Argument
  • Stop limit

Other columns can be added or removed as needed. The following are all valid examples of column names and corresponding cell values:

ColumnCell value
CampaignName STARTS_WITH '?'Indonesia_
Conversions >= ?4
Status IN [?]'ENABLED', 'PAUSED'

The ? symbol in the column name gets replaced with the value in the corresponding row. Check the documentation for KeywordSelector for a complete set of supported columns.

Action

Action is one of the following:

  • Multiply by: Multiplies the keyword bid by the Argument. 1.1 increases the bid by 10%. 0.8 decreases it by 20%.
  • Add: Adds the Argument to the keyword bid. 0.3 increases the bid by $0.30 (assuming the account is in USD). -0.14 decreases it by $0.14.
  • Set to First Page Cpc: Sets the keyword bid to the first page CPC. Argument is ignored.
  • Set to Top of Page Cpc: Sets the keyword bid to the top-of-page CPC. Argument is ignored.

Stop limit

Stop Limit is used to cap the bid changes that the script is making. If the changes to the bid is positive, the bid won't become larger than Stop Limit. If the change is negative, the bid won't become smaller than Stop Limit. For instance, if the keyword bid is presently $2.00, and you're increasing it by 25% with a Stop Limit of $3.00, the bid will become $2.50. If, however, the Stop Limit is set at $2.30, the bid will become $2.30 as well.

Results

Results is auto-generated by the script. It contains any errors an execution encountered, or an indication of the number of keywords the script fetched and attempted to change.

Note that "attempted change" might not mean actual change occurred, for instance, giving a keyword a negative bid won't work. Make sure to check the execution logs to see exactly what the script did.

The Results column is the last column in the spreadsheet that is read. Should you add any conditions after the Results column, they won't apply.

Scheduling

The most commonly used scheduling options for bidding rules are Daily and Weekly.

Be mindful of how the schedule frequency might affect the statistics date ranges you're using. Since Google Ads statistics can be delayed by up to 3 hours, avoid scheduling your script Hourly.

You could also not schedule the script at all if that makes the most sense.

Setup

  • Setup a spreadsheet-based script with the source code below. Use the Multi Bidder template spreadsheet.
  • Don't forget to update SPREADSHEET_URL in the code.
  • Make sure to preview the script before executing it.
  • Consider whether to schedule the script.

Source code

// Copyright 2015, 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 Multi Bidder
 *
 * @overview The Multi Bidder script offers functionality similar to that of
 *     Automated Rules based on a spreadsheet. See
 *     https://developers.google.com/google-ads/scripts/docs/solutions/multi-bidder
 *     for more details.
 *
 * @author Google Ads Scripts Team [adwords-scripts@googlegroups.com]
 *
 * @version 2.0
 *
 * @changelog
 * - version 2.0
 *   - Updated to use new Google Ads scripts features.
 * - version 1.0.3
 *   - Replaced deprecated keyword.getMaxCpc() and keyword.setMaxCpc().
 * - version 1.0.2
 *   - Added validation of user settings.
 * - version 1.0.1
 *   - Improvements to time zone handling.
 * - version 1.0
 *   - Released initial version.
 */

const SPREADSHEET_URL = 'YOUR_SPREADSHEET_URL';

const spreadsheetAccess = new SpreadsheetAccess(SPREADSHEET_URL, 'Rules');

let totalColumns;

/**
 * The bids of keywords are modified based on the criterion mentioned
 * in the spreadsheet.
 */
function main() {
  // Make sure the spreadsheet is using the account's timezone.
  spreadsheetAccess.spreadsheet.setSpreadsheetTimeZone(
      AdsApp.currentAccount().getTimeZone());
  spreadsheetAccess.spreadsheet.getRangeByName('account_id').setValue(
      AdsApp.currentAccount().getCustomerId());
  const columns = spreadsheetAccess.sheet.getRange(5, 2, 5, 100).getValues()[0];
  for (let i = 0; i < columns.length; i++) {
    if (columns[i].length == 0 || columns[i] == 'Results') {
      totalColumns = i;
      break;
    }
  }
  if (columns[totalColumns] != 'Results') {
    spreadsheetAccess.sheet.getRange(5, totalColumns + 2, 1, 1).
        setValue('Results');
  }
  // clear the results column
  spreadsheetAccess.sheet.getRange(6, totalColumns + 2, 1000, 1).clear();

  let row = spreadsheetAccess.nextRow();

  while (row != null) {
    let argument;
    let stopLimit;
    try {
      argument = parseArgument(row);
      stopLimit = parseStopLimit(row);
    } catch (ex) {
      logError(ex);
      row = spreadsheetAccess.nextRow();
      continue;
    }
    let selector = AdsApp.keywords();
    for (let i = 3; i < totalColumns; i++) {
      let header = columns[i];
      let value = row[i];
      if (!isNaN(parseFloat(value)) || value.length > 0) {
        if (header.indexOf("'") > 0) {
          value = value.replace(/\'/g, "\\'");
        } else if (header.indexOf('\"') > 0) {
          value = value.replace(/"/g, '\\\"');
        }
        const condition = header.replace('?', value);
        selector.withCondition(condition);
      }
    }
    selector.forDateRange(spreadsheetAccess.spreadsheet.
        getRangeByName('date_range').getValue());

    const keywords = selector.get();

    try {
      keywords.hasNext();
    } catch (ex) {
      logError(ex);
      row = spreadsheetAccess.nextRow();
      continue;
    }

    let fetched = 0;
    let changed = 0;

    for (const keyword of keywords) {
      let oldBid = keyword.bidding().getCpc();
      let action = row[0];
      let newBid;

      fetched++;
      if (action == 'Add') {
        newBid = addToBid(oldBid, argument, stopLimit);
      } else if (action == 'Multiply by') {
        newBid = multiplyBid(oldBid, argument, stopLimit);
      } else if (action == 'Set to First Page Cpc' ||
        action == 'Set to Top of Page Cpc') {
        let newBid = action == 'Set to First Page Cpc' ?
            keyword.getFirstPageCpc() : keyword.getTopOfPageCpc();
        let isPositive = newBid > oldBid;
        newBid = applyStopLimit(newBid, stopLimit, isPositive);
      }
      if (newBid < 0) {
        newBid = 0.01;
      }
      newBid = newBid.toFixed(2);
      if (newBid != oldBid) {
        changed++;
      }
      keyword.bidding().setCpc(parseFloat(newBid));
    }
    logResult('Fetched ' + fetched + '\nChanged ' + changed);

    row = spreadsheetAccess.nextRow();
  }

  spreadsheetAccess.spreadsheet.getRangeByName('last_execution')
      .setValue(new Date());
}

/**
 * Performs addition on oldbid and argument.
 *
 * @param {string} oldBid The old bid value.
 * @param {string} argument The arugument value in the spreadsheet.
 * @param {string} stopLimit The changes made to the bids.
 * @return {string} Applies stop limit to the bid.
 */
function addToBid(oldBid, argument, stopLimit) {
  return applyStopLimit(oldBid + argument, stopLimit, argument > 0);
}

/**
 * Performs multiplication on oldbid and argument.
 *
 * @param {string} oldBid The old bid value.
 * @param {string} argument The arugument value in the spreadsheet.
 * @param {string} stopLimit The changes made to the bids.
 * @return {string} Applies the stop limit to the bid.
 */
function multiplyBid(oldBid, argument, stopLimit) {
  return applyStopLimit(oldBid * argument, stopLimit, argument > 1);
}

/**
 * Applies stop limit to the bid depending on the conditions.
 *
 * @param {string} newBid The calculated bid value as per the action
 *   in the spreadsheet.
 * @param {string} stopLimit The changes made to the bids.
 * @param {boolean} isPositive checks for the value sign.
 * @return {string} assigns stop limit to the bids and returns.
 */
function applyStopLimit(newBid, stopLimit, isPositive) {
  if (stopLimit) {
    if (isPositive && newBid > stopLimit) {
      newBid = stopLimit;
    } else if (!isPositive && newBid < stopLimit) {
      newBid = stopLimit;
    }
  }
  return newBid;
}

/**
 * If the argument is not specified or bad arguments are passed, an error is
 * returned in the result field.
 *
 * @param {!Object} row The row in the spreadsheet.
 * @return {string} Returns error message in the result column.
 */
function parseArgument(row) {
  if (row[1].length == 0 && (row[0] == 'Add' || row[0] == 'Multiply by')) {
    throw ('\"Argument\" must be specified.');
  }
  let argument = parseFloat(row[1]);
  if (isNaN(argument)) {
    throw 'Bad Argument: must be a number.';
  }
  return argument;
}

/**
 * Parses stop limit from the spreadsheet.
 *
 * @param {!Object} row The row in the spreadsheet.
 * @return {string} Returns error message to the Result field in the row
 */
function parseStopLimit(row) {
  if (row[2].length == 0) {
    return null;
  }
  let limit = parseFloat(row[2]);
  if (isNaN(limit)) {
    throw 'Bad Argument: must be a number.';
  }
  return limit;
}

/**
 * Format the error messages in the spreadsheet
 *
 * @param {string} error The error messages.
 */
function logError(error) {
  spreadsheetAccess.sheet.getRange(spreadsheetAccess.currentRow(),
      totalColumns + 2, 1, 1)
  .setValue(error)
  .setFontColor('#c00')
  .setFontSize(8)
  .setFontWeight('bold');
}

/**
 * Formats the result messages in the spreadsheet
 *
 * @param {string} result The result values.
 */
function logResult(result) {
  spreadsheetAccess.sheet.getRange(spreadsheetAccess.currentRow(),
      totalColumns + 2, 1, 1)
  .setValue(result)
  .setFontColor('#444')
  .setFontSize(8)
  .setFontWeight('normal');
}

/**
 * Provides access to the spreadsheet using spreadsheetUrl, sheetName
 * and validate the spreadsheet.
 *
 * @param {string} spreadsheetUrl The spreadsheet url.
 * @param {string} sheetName The spreadsheet name.
 * @return {string} Returns spreadsheet along with rows.
 */
function SpreadsheetAccess(spreadsheetUrl, sheetName) {
  Logger.log('Using spreadsheet - %s.', spreadsheetUrl);
  this.spreadsheet = validateAndGetSpreadsheet(spreadsheetUrl);

  this.sheet = this.spreadsheet.getSheetByName(sheetName);
  this.cells = this.sheet.getRange(6, 2, this.sheet.getMaxRows(),
      this.sheet.getMaxColumns()).getValues();
  this.rowIndex = 0;

  this.nextRow = function() {
    for (; this.rowIndex < this.cells.length; this.rowIndex++) {
      if (this.cells[this.rowIndex][0]) {
        return this.cells[this.rowIndex++];
      }
    }
    return null;
  };
  this.currentRow = function() {
    return this.rowIndex + 5;
  };
}

/**
 * Validates the provided spreadsheet URL
 * to make sure that it's set up properly. Throws a descriptive error message
 * if validation fails.
 *
 * @param {string} spreadsheeturl The URL of the spreadsheet to open.
 * @return {Spreadsheet} The spreadsheet object itself, fetched from the URL.
 * @throws {Error} If the spreadsheet URL hasn't been set
 */
function validateAndGetSpreadsheet(spreadsheeturl) {
  if (spreadsheeturl == 'YOUR_SPREADSHEET_URL') {
    throw new Error('Please specify a valid Spreadsheet URL. You can find' +
        ' a link to a template in the associated guide for this script.');
  }
  return SpreadsheetApp.openByUrl(spreadsheeturl);
}