Multi Bidder - 单一账号

出价图标

在管理 Google Ads 账户时,优化关键字出价是最重要的活动之一。关键字出价会直接影响广告的展示位置(在 Google 搜索中的排名)以及广告的费用。为达成目标,您通常需要制定自己的出价策略。

自动规则是 Google Ads 的一项功能,使您可以根据设定的条件修改关键字出价,然后按计划进行。如果您拥有大量自动规则,则可能会难以管理。

Multi Bidder 基于电子表格提供类似于自动规则的功能。电子表格中的每一行实际上就相当于一条完整的自动规则。在电子表格中管理这些规则比在 Google Ads 中管理这些规则更简单。

多出价方电子表格的屏幕截图

上面的电子表格展示了用于执行以下操作的单条规则:

  • 查看 THIS_WEEK_SUN_TODAY 的统计信息。
  • 查找 Campaign #1 中获得了超过 1 次展示且点击率大于 25% 的所有关键字。
  • 将关键字的出价提高 10%(但不超过 1.40 美元)。

运作方式

电子表格中必须包含以下列:

  • 操作
  • 参数
  • 停车限额

其他列可以根据需要添加或删除。以下是列名称和相应单元格值的有效示例:

单元格值
CampaignName STARTS_WITH '?'Indonesia_
Conversions >= ?4
Status IN [?]'ENABLED', 'PAUSED'

列名称中的 ? 符号会替换为相应行中的值。如需了解所有受支持的列,请参阅 KeywordSelector 的文档。

操作

Action 选择以下任何值:

  • Multiply by:将关键字出价乘以 Argument1.1 可将出价提高 10%。0.8 会减少 20%。
  • Add:向关键字出价添加 Argument0.3 会将出价提高 0.30 美元(假设该帐号使用美元)。-0.14 会将其降低 $0.14。
  • Set to First Page Cpc:将关键字出价设为首页每次点击费用。 Argument 会被忽略。
  • Set to Top of Page Cpc:将关键字出价设为页首每次点击费用。 Argument 会被忽略。

停车时长上限

Stop Limit 用于为脚本的出价更改设置上限。如果出价更改是正值,出价不会高于 Stop Limit。如果更改是负值,出价不会低于 Stop Limit。例如,如果关键字出价目前为 2.00 美元,而您要将其提高 25%,并将 Stop Limit 设为 3.00 美元,则出价将变为 2.50 美元。不过,如果 Stop Limit 设为 2.30 美元,则出价也将是 2.30 美元。

成果

Results 由脚本自动生成。其中包含执行遇到的任何错误,或脚本提取并尝试更改的关键字数量。

请注意,“尝试更改”并不意味着实际发生了更改,例如,为关键字指定负数出价是不起作用的。请务必检查执行日志,以了解脚本的实际操作。

Results 列是电子表格中读取的最后一列。 如果您在 Results 列后添加任何条件,它们将无法应用。

正在安排

出价规则最常用的运行时间设置选项是每日每周

请注意定期运行频率可能会对您所使用的统计信息日期范围产生怎样的影响。Google Ads 统计信息最多可能会有 3 个小时的延迟,因此请避免安排您的脚本每小时运行。

此外,如果这样做最为合理,则完全不能设置脚本的运行时间。

初始设置

  • 使用下面的源代码设置基于电子表格的脚本。使用 Multi Bidder 模板电子表格
  • 切勿忘记更新代码中的 SPREADSHEET_URL
  • 务必在执行前先预览脚本。
  • 考虑是否将脚本设为定期运行。

源代码

// 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);
}