品質スコアの履歴を AdWords Script を使って管理する方法

3月 1, 2013 | 便利ツール, 品質スコア, 海外情報

品質スコアを最適化するために広告文を変更したりすることはあっても、その前と後の変化をデータを取って検証している人は意外と少ないですし、『品質スコアの履歴があったら良いけど、マニュアルで管理するのも面倒だな〜』と思っている方もいると思います。

そんな時に、AdWords Script を使って品質スコアの履歴を管理することができます。【参照: PPC Hero（英語）】

手順

①. まずは下準備ですが、AdWords にログインした状態で、こちらのスプレッドシートを開き、ご自身のGoogle Drive などに保存します。

②. あらかじめ、Campaign（キャンペーン）AdGroup（グループ）Keyword（キーワード）の項目に、ご自身で運営している項目をそれぞれ反映させておきます。

③. 次に、AdWords アカウントにあるスクリプトを作成の画面に移ります。

スクリプトを作成

④. そこに、下記にあるスクリプトを貼り付けます。【参照: PPC Epiphany（英語）】

var spreadsheet_url = “INSERT_SPREADSHEET_URL_HERE”;
var email_address = “YOUR_EMAIL_HERE”;

function main() {
var matches = new RegExp(‘key=([^&#]*)’).exec(spreadsheet_url);
if (!matches || !matches[1]) throw ‘Invalid spreadsheet URL: ‘ + spreadsheetUrl;
var spreadsheetId = matches[1];
var spreadsheet = SpreadsheetApp.openById(spreadsheetId);
var sheet = spreadsheet.getSheetByName(‘Input Keywords’);
var sheet_values = sheet.getDataRange().getValues();
var result_range = new Array(); // holds the results to write back
var alert_text = new Array();
var history = new Array();
var currentTime = new Date();
var today = (currentTime.getMonth() + 1) + “/” + currentTime.getDate() + “/” + currentTime.getFullYear();

for(i = 1; i < sheet_values.length; i++){ // make sure there is actually some data here if(sheet_values[i][0] == "") continue; result_range[i] = [today, 0]; var campaign_name = sheet_values[i][0]; var adgroup_name = sheet_values[i][1]; // remove single quotes at the beginning of the keyword (Excel sometimes adds them in front of modified broad matches, like: '+keyword) var keyword_text = sheet_values[i][2].replace(/^[']+/g, ""); var latest_check = sheet_values[i][3]; var old_quality_score = sheet_values[i][4]; var keywordIterator = AdWordsApp.keywords() .withCondition("CampaignName = '" + campaign_name + "'") .withCondition("AdGroupName = '" + adgroup_name + "'") // this won't let us filter for phrase or exact matches so we have to remove brackets and quotation marks (broad match modifiers are fine) .withCondition("Text = \"" + keyword_text.replace(/[\[\]\"]/g, "") + "\"") .get(); while(keywordIterator.hasNext()){ var keyword = keywordIterator.next(); // since we couldn't filter phrase or exact matches directly, we have to make sure that this is the right keyword if(keyword.getText() == keyword_text){ var current_quality_score = keyword.getQualityScore(); // save quality score for results result_range[i][1] = current_quality_score; // for the history we also note the change or whether this keyword is new if(old_quality_score > 0) var change = current_quality_score – old_quality_score;
else var change = “NEW”;
var row = [today, campaign_name, adgroup_name, keyword_text, current_quality_score, change];
history.push(row);
// if we have a previously tracked quality score and it’s different from the current one, we make a note to log it and send it via email later
if(old_quality_score > 0 && current_quality_score != old_quality_score){
alert_text.push(current_quality_score + “\t” + old_quality_score + “\t” + change + “\t” + latest_check + “\t” + keyword_text);
}
// we’ve found the keyword we were looking for so we look no further
break;
}
}
}
// write results to spreadsheet
result_range.splice(0,1);
sheet.getRange(2, 4, result_range.length, 2).setValues(result_range);
// write history to spreadsheet
var history_sheet = spreadsheet.getSheetByName(‘QS history’);
history_sheet.getRange(history_sheet.getLastRow()+1, 1, history.length, 6).setValues(history);
// if we’ve made notes for alerts then we send them via email
if(alert_text.length){
var message = “The following quality score changes were discovered:\nNew\tOld\tChange\tPreviously checked\tKeyword\n”;
for(i = 0; i < alert_text.length; i++) message += alert_text[i] + "\n"; // also include a link to the spreadsheet message += "\n" + "Settings and complete history are available at " + spreadsheet_url; // if we have an email address we send out a notification if(email_address && email_address != "YOUR_EMAIL_HERE"){ MailApp.sendEmail(email_address, "AdWords quality score changes detected", message); } // log the message Logger.log(message); } }