目前有 234 位成員在線上
目前有 234 位成員在線上
AdWords 廣告精選學習資源
star_border

[經驗分享] Adwords Script - 帳戶品質分數/位置分佈報表 QS/Position Performance Report

新接觸的先參考PART 1: Adwords Script 介紹 - 誰都懂的半API的帳戶自動化及新式報表

 

今次的script是每星期才運行, 它讓我們知道不同QS(1-10)關鍵字及不同位置下得到的數據

這個會跟Adwords Script - 帳戶品質分數每日報表分享 (Account QS Change Trend)有很大的關係*

 

 

這段script分開兩部份, 第一是品質分數/位置分佈下的數據(1)

qspostition report.jpg

 

第二部份是把每星期的關鍵字數目按QS記錄下來(2)

qspostition report2.jpg

 

 

 

帳戶品質分數/位置分佈報表能給我們甚麼新角度?

系統沒有類似報表(1), 雖然可以用EXCEL做但費時, 好處有:

1. 加了轉換數據可以知道大概知道那個位置會有更有利的轉換

2. 那些QS的字有較高的轉換

 

系統只能提供現時的QS, 這報表(2)能追蹤QS的改變甚至游走方向

例如我在8月7日看到有大部份的低QS關鍵字, 我做了優化, 之後就看到少了QS1的字, QS9及10的字也多了

 

甚麼action point?

1. 加了轉換數據可以知道大概知道那個位置會有更有利的轉換

> 有點似top vs others報表, 但更細緻的分佈, 可相對的調教

2. 那些QS的字有較高的轉換

> 留意那一些字有轉換但低QS, 著手優化提升QS, 可降低CPC以提高ROI

 

 

 

ADWORDS SCRIPT:

我保留了原創者的註解, 我的註解會用中文

留意紅色的是要轉為的自己的

--------------------------------------------------------------------------------------------------------------------------------------

// Comma-separated list of recipients.

var RECIPIENT_EMAIL = 'abc@abc.com';

// Spreadsheet template.

var SPREADSHEET_URL = 'https://docs.google.com/spreadsheet/ccc?key=0Ahntcoqpd7CbdGxsWDlnRzRmandRbHZrck00d01jVlE&usp=sharing... // 只是取一個template, 沒太大意思, 可參考原文 https://developers.google.com/adwords/scripts/docs/solutions/keyword-performance

 

/**

* This script computes a keyword performance report

* and outputs it to a Google spreadsheet. The spreadsheet

* url is logged and emailed.

*/

function main()

{

 var spreadsheet = copySpreadsheet(SPREADSHEET_URL);  

 var sheet = spreadsheet.getSheetByName('Report');

 outputQualityScoreData(sheet);

 outputPositionData(sheet);

 Logger.log('Keyword performance report - ' + spreadsheet.getUrl());

 MailApp.sendEmail(

   RECIPIENT_EMAIL, 'New Report is ready.', spreadsheet.getUrl());

}

 

/**

* Retrieves the spreadsheet identified by the URL.

* @param {string} spreadsheetUrl The URL of the spreadsheet.

* @return {SpreadSheet} The spreadsheet.

*/

function copySpreadsheet(spreadsheetUrl)

{

 return SpreadsheetApp.openByUrl(spreadsheetUrl).copy(

   'Keyword Performance Report ' + new Date());

}

 

/**

* Outputs Quality score related data to the spreadsheet

* @param {Sheet} sheet The sheet to output to.

*/

function outputQualityScoreData(sheet)

{

 // Output header row

 var header = [

   'Quality Score',

   'Num Keywords',

   'Impressions',

   'Clicks',

   'CTR (%)',

   'Cost',

   'Conv',

   'Cost/Conv'

 ];

 sheet.getRange(1, 1, 1, 8).setValues([header]);

 

 // Initialize

 var qualityScoreMap = [];

 for (i = 1; i <= 10; i++) {

   qualityScoreMap[i] = {

     numKeywords: 0,

     totalImpressions: 0,

     totalClicks: 0,

     totalCost: 0.0,

     totalConv: 0,

   }

 }

 

 // Compute data

 var keywordIterator = AdWordsApp.keywords()

     .forDateRange('LAST_WEEK')  // 利用前7日的數據, 可按你要求改變

     .withCondition('Impressions > 0')

     .get();

 while (keywordIterator.hasNext())

 {

   var keyword = keywordIterator.next();

   var stats = keyword.getStatsFor('LAST_WEEK');

   var data = qualityScoreMap[keyword.getQualityScore()];

   if (data)

   {

     data.numKeywords++;

     data.totalImpressions += stats.getImpressions();

     data.totalClicks += stats.getClicks();

     data.totalCost += stats.getCost();

     data.totalConv +=stats.getConversions();

   }

 }

 

 // Output data to spreadsheet

 var rows = [];

 for (var key in qualityScoreMap)

 {

   var ctr = 0;

   var cost = 0.0;

   

   var costconv = 0.0;    

   costconv = (qualityScoreMap[key].totalCost / qualityScoreMap[key].totalConv);

   

   if (qualityScoreMap[key].numKeywords > 0)

   {

     ctr = (qualityScoreMap[key].totalClicks /

       qualityScoreMap[key].totalImpressions) * 100;

   }

   var row = [

     key,

     qualityScoreMap[key].numKeywords,

     qualityScoreMap[key].totalImpressions,

     qualityScoreMap[key].totalClicks,

     ctr.toFixed(2),

     qualityScoreMap[key].totalCost,

     qualityScoreMap[key].totalConv,

     costconv.toFixed(2)

   ];

     

   rows.push(row);

 }

 sheet.getRange(2, 1, rows.length, 8).setValues(rows);

 

 var date = new Date();

 var spreadsheetUrl2 = "https://docs.google.com/spreadsheet/ccc?key=0Ahntcoqpd7CbdEVvWDV3ckFES19pRFZqNkVxaGtvWGc&usp=sharing"; // 建議用回上一次帳戶品質分數每日報表分享spreadsheet的文件, 將資料放入它的sheet2*

 var qualityScoreSheet2 = SpreadsheetApp.openByUrl(spreadsheetUrl2).getSheetByName('Sheet2');

 qualityScoreSheet2.appendRow([date, qualityScoreMap[1].numKeywords,qualityScoreMap[2].numKeywords,qualityScoreMap[3].numKeywords,qualityScoreMap[4].numKeywords,qualityScoreMap[5].numKeywords,qualityScoreMap[6].numKeywords,qualityScoreMap[7].numKeywords,qualityScoreMap[8].numKeywords,qualityScoreMap[9].numKeywords,qualityScoreMap[10].numKeywords]);

 

}

 

/**

* Outputs average position related data to the spreadsheet.

* @param {Sheet} sheet The sheet to output to.

*/

function outputPositionData(sheet)

{

 // Output header row

 headerRow = [];

 var header = [

   'Avg Position',

   'Num Keywords',

   'Impressions',

   'Clicks',

   'CTR (%)',

   'Cost',

   'Conv',

   'Cost/Conv'    

 ];

 headerRow.push(header);

 sheet.getRange(14, 1, 1, 8).setValues(headerRow);

 

 // Initialize

 var positionMap = [];

 for (i = 1; i <= 12; i++)

 {

   positionMap[i] = {

     numKeywords: 0,

     totalImpressions: 0,

     totalClicks: 0,

     totalCost: 0.0,

     totalConv: 0,      

   };

 }

 

 // Compute data

 var keywordIterator = AdWordsApp.keywords()

     .forDateRange('LAST_WEEK')

     .withCondition('Impressions > 0')

     .get();

 while (keywordIterator.hasNext())

 {

   var keyword = keywordIterator.next();

   var stats = keyword.getStatsFor('LAST_WEEK');

   if (stats.getAveragePosition() <= 11)

   {

     var data = positionMap[Math.ceil(stats.getAveragePosition())];

   } else {

     // All positions greater than 11

     var data = positionMap[12];

   }

   data.numKeywords++;

   data.totalImpressions += stats.getImpressions();

   data.totalClicks += stats.getClicks();

   data.totalCost += stats.getCost();

   data.totalConv += stats.getConversions();    

 }

 

 // Output data to spreadsheet

 var rows = [];

 for (var key in positionMap)

 {

   var ctr = 0;

   var cost = 0.0;

   

   var costconv = 0.0;    

   costconv = positionMap[key].totalCost / positionMap[key].totalConv;

   

   if (positionMap[key].numKeywords > 0)

   {

     ctr = (positionMap[key].totalClicks /

       positionMap[key].totalImpressions) * 100;

   }

   var row = [

     key <= 11 ? key - 1 + ' to ' + key : '>11',

     positionMap[key].numKeywords,

     positionMap[key].totalImpressions,

     positionMap[key].totalClicks,

     ctr.toFixed(2),

     positionMap[key].totalCost,

     positionMap[key].totalConv,

     costconv.toFixed(2)

   ];

   rows.push(row);

 }

 sheet.getRange(15, 1, rows.length, 8).setValues(rows);

}

 

簡介 Stampede Tong

自2007年以來,我一直從事在Adwords和網絡廣告代理。由於與Google的分銷商計劃,它讓我在領先的地位學習Adwords和了解最新的功能。我想給予更多實用的解決方案給使用者,因為經驗實踐跟理論一樣的重要。在社群交流幫助也是一種樂趣,因為它總是給你不止是一個答案,還有靈感!

評論
修訂者 九文鱼
‎2013-09-23 12:27

Hi Stampede,

 

这种js本身对账户是没有影响的吧?

修訂者 解答王 Stampede 解答王
‎2013-09-23 13:39

沒有的, 這只是類似利用KPI去拿後台裡的數據而已

有些script可控制adwords似是automatic rules, 但不是這段

修訂者 九文鱼
‎2013-09-23 14:11

嗯 好的

这里的数据很丰富 一会拿个账户测试下

修訂者 解答王 Stampede 解答王
‎2013-09-24 12:25 - 已編輯 ‎2013-09-24 12:28

是的, 這個報表很特別

從中找到的action point(insights)未必很多

但作為優化後的檢討這是很直接的參考

 

我會在spreadsheet上加上運算: QS4-10關鍵字數目減QS1-3的關鍵字數目作為指標