多重出價工具 - 單一帳戶

出價圖示

管理 Google Ads 帳戶時,最重要的工作之一就是調整關鍵字出價。關鍵字出價會直接影響廣告刊登位置 (在 Google 搜尋中的排名) 和費用。通常你需要製定自己的出價策略來達成目標

自動規則是 Google Ads 提供的一項功能,可讓您根據設定的條件修改關鍵字出價,並依據時間表進行這類變更。如果您有許多自動規則,會很難管理

「多重出價工具」的功能與根據試算表的「自動規則」類似。試算表中的每個資料列效果都等同於整份自動規則。在試算表中管理這些規則比在 Google Ads 中管理更加輕鬆。

多重出價工具試算表的螢幕截圖

上方的試算表只示範了可執行下列操作的單一規則:

  • 查看 THIS_WEEK_SUN_TODAY 的統計資料。
  • 尋找 廣告活動 #1 中,獲得超過 1 次曝光且點閱率大於 25% 的所有關鍵字。
  • 提高出價 10%,但不超過新臺幣 $1.40 元。

運作方式

試算表中必須有下列資料欄:

  • 動態
  • 引數
  • 停止限制

如果有需要,可以加入或移除其他欄位。以下是資料欄名稱和對應儲存格值的有效範例:

資料欄儲存格值
CampaignName STARTS_WITH '?'Indonesia_
Conversions >= ?4
Status IN [?]'ENABLED', 'PAUSED'

資料欄名稱中的 ? 符號會替換為對應資料列中的值。如需支援的完整資料欄組合,請參閱 KeywordSelector 說明文件。

動作

Action 是下列任何一項:

  • 乘上:將關鍵字出價乘以 Argument1.1 將出價提高 10%。0.8 會減少 20%。
  • Add:將 Argument 新增至關鍵字出價。0.3 會將出價提高 $0.30 美元 (假設帳戶幣別是美元)。-0.14 會減少 $0.14 美元。
  • 設為第一頁單次點擊出價:將關鍵字出價設為第一頁單次點擊出價。 系統會忽略 Argument
  • 設為網頁頂端單次點擊出價:將關鍵字出價設為首頁頂端單次點擊出價。 系統會忽略 Argument

停止限制

Stop Limit (停止限制) 可用來限制指令碼執行的出價變更。如果出價變更是正數,出價不會高於 StopLimit。如果變更是負數,出價不會低於停止限制。舉例來說,如果關鍵字出價目前是 $2.00 美元,而您要提高 25%,Stop Limit 是 $3.00 美元,出價就會變成 $2.50 美元。不過,如果 Stop Limit 設為 $2.30 美元,出價也會變成 $2.30 美元。

結果

指令碼會自動產生 Results。這個檔案包含執行作業遇到的任何錯誤,或指出指令碼擷取並嘗試變更的關鍵字數量。

請注意,「嘗試變更」不一定表示實際發生了變化,例如,為關鍵字設定負數出價會無效。請務必查看執行記錄檔,瞭解指令碼實際執行的作業。

「結果」欄是指系統讀取的試算表中的最後一欄。 如果您在 Results 後面加入任何條件,都不會套用這些條件。

排程

最常見的出價排程選項是「每日」和「每週」。

請留意排程頻率可能對您目前使用的統計資料日期範圍造成的影響。由於 Google Ads 統計資料會延遲最多 3 小時,請不要將指令碼設為每小時

不然,您也可以自行安排執行指令碼。

設定

  • 使用以下原始碼,設定適合試算表的指令碼。使用多重出價工具範本試算表
  • 別忘了更新程式碼中的 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);
}