管理 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 是下列任何一項:
- 乘上:將關鍵字出價乘以 Argument。
1.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);
}