动态再营销-轮播广告 操作步骤

回复

白熊 发起了问题 • 1 人关注 • 0 个回复 • 52 次浏览 • 2017-04-25 10:38 • 来自相关话题

一种高竞争词PPC排名策略

白熊 发表了文章 • 0 个评论 • 192 次浏览 • 2017-01-09 14:32 • 来自相关话题

本文授权转载自孑尊科技,如图片无法显请查看原文:一种高竞争词PPC排名策略
或者微信添加孑尊科技(jiezuntech) 获取文章。
 
上期我们通过几个简单的公式,重新介绍了CPC:
点击查看上期文章:《重新认识CPC》

我们在文章最后,提到了三个结论,我只说了两个
尽可能提高QS,参考公式一,分子越大,数字越小你的出价(Max.bid/Max.CPC)并不能直接决定你的ActualCPC,中间是有一个QS在里面的


那么第三个结论是什么,我们就是我们这期要讲的


既然要谈到策略,一定是现有概念的,我们先引出几个基本的概念

计算QS Quality Score




QS是什么,怎么查看?不用我多说了,看上面的图就知道。


但是如果我让你根据QS的相关因子的质量能推导出QS是多少分,相信没有几个人能答得上来。

我们来看下面的一张图:




其中,CTR(点击率),Ad (广告,或者说广告质量),LP(Landing Page)有+ 、0、 - 三种状态,分别代表,高过平均值,平均值,低于平均值,上表就是根据AdWords后台提供的三个数值的三种不同状态得到一共3*3*3=27种状态,分别对应了QS的1-10分,好,现在有人要说,这些数据后台都能看,你整理出来没什么用啊。




真没用?

先不看下面,给你2分钟时间想想,这些数据到底有什么作用
















      想到了么?不管想到没有,我们接着往下看。




2.  QS权重比例


      如果我们把上表三个要素的权重统计一下的话,会得到下面一个数据







是的,LP,预期点击率,Ad相关性占QS的权重分别是 39%, 39%,22%,不是1:1:1,那么我们可以通过这个得到什么呢?





3. 信息对称  Vs. 信息不对称 



我们把知识分为两类:

一类是科学知识,即被组织起来的知识由专家所掌握,在理论和书籍中可以得到

一类是特定时间和地点的知识,为处于当时和当地的人所拥有。

上述两种知识,我们把它换成信息来看,就可以分为信息对称知识和信息不对称知识,今天你在上面阅读到的所有内容,都是有依可循,能查到出处的,这些是信息对称,而下面我要说的,以及我通过对称的信息推倒出来的信息或者知识,对于你我来说,是不对称的信息,而当你读完这篇文章后,对你我来说,这些信息又是对称的了。

有点绕口。

我们把上面的几个要素分为两块的话,哪些是信息对称的,哪些是信息不对称的呢?请看下图:





高竞争词PPC这个前提下,意味着信息对称的知识,所有广告主一定是花了大功夫大精力去做的,竞争对手的Landing Page 和 Ad你是直接能看到的,相关的优化方法和知识,要多少有多少,最不济通过A/B Test,也是可以慢慢提升的。


因此,对于你和竞争对手而言,上述信息不对称的,只有CTR。


4.排名和CTR

CTR和排名的关系,很多人已经有研究,为了方便下面讲下去,我们需要引用一些数据支撑,注意这些只是理论数据,实际情况是更复杂的。

大家都知道,广告的排名越高,理论上CTR会越高,根据一些第三方的数据,CTR的值是这样的:


数据来源:Accuracast

我们把它统计一下,于是有了下面这张图:



左侧是排名,中间是CTR,最右侧是后面的排名CTR占第一名CTR的比例,这三个数字都不难理解。


我在第二节提到的,QS影响因子所占权重占比,我们把它列出来



接下来,便于我们理解后面的内容,我们假设第一名和后面的竞争对手,Ad & LP 都是一样的,只有CTR不一样,根据第三方的比例,我们得出的不同排名CTR如下图红圈所示:



加上昨天的两个公式和我们QS的计算方法:


公式1:

公式2:AdRank=Max Bid*Quality Score

公式3:QS=CTR+Ad+LP


我们得出下面这个表:



不够直观,我们把它换成图像:



看出什么了么?

是的,第二名的Max.Bid 非常高,而在一个范围中,无论Max.Bid 出到多高,对你的Actual CPC几乎没有影响。

结论是有违直觉的,但是却又是真实的。

第二名通过出到极高的CPC,稳定了排名,同时只要你的Ad Rank 高过第三名,你的CPC往往之比第三名高一点点。

因此Google AdWords高竞争词排名策略,也就是我们的第三个结论,一句话就可以概括:

3.其他都做好的前提下,想要更低的CPC,提高出价,提高排名。

有人想说,你这个说的太理论化了,实际情况不是这样的。

是的,我这些都是纯理论,涉及到实际的投放,又是完全不一样的。

因为实际投放,第二名的Actual应该更低。

为什么?因为真实情况更接近这张图:



竞争对手的 Ad、LP 并不是一样的,因为如果所有人都Above Average,那么所有人都是 Average。

而Ad、LP 严重影响了Ad Rank。

而Ad 和 Ad Rank又影响了CTR。

所有因素都是相互牵制,相互关联的。

因此真实情况,第二名的Actual CPC 比后面几名都低。

并且同时拥有更高的ROI和更低的CPA。

如果我不打破你脑子里: 高 Max.CPC=高 Actual CPC的概念,在上述的例子中,你是打死不会出到超过3美金的出价的。

而同时你确丧失了高竞争词PPC的展示机会。

上面提到的是策略,是在高竞争词下面的一个非常有效的策略,
 
但是实际操作过程中,你一定不是简单提高出价就能做得好
 
什么时候提高出价,提高后产生较高的ActualCPC怎么办?
 
这种面对实际情况的从容应对的操作技巧,才是真技术,但这不是我们这次讨论的范围。
 
下期见。 查看全部
本文授权转载自孑尊科技,如图片无法显请查看原文:一种高竞争词PPC排名策略
或者微信添加孑尊科技(jiezuntech) 获取文章。
 
上期我们通过几个简单的公式,重新介绍了CPC:
点击查看上期文章:《重新认识CPC》

我们在文章最后,提到了三个结论,我只说了两个
  1. 尽可能提高QS,参考公式一,分子越大,数字越小
  2. 你的出价(Max.bid/Max.CPC)并不能直接决定你的ActualCPC,中间是有一个QS在里面的



那么第三个结论是什么,我们就是我们这期要讲的


既然要谈到策略,一定是现有概念的,我们先引出几个基本的概念

  1. 计算QS Quality Score





QS是什么,怎么查看?不用我多说了,看上面的图就知道。


但是如果我让你根据QS的相关因子的质量能推导出QS是多少分,相信没有几个人能答得上来。

我们来看下面的一张图:




其中,CTR(点击率),Ad (广告,或者说广告质量),LP(Landing Page)有+ 、0、 - 三种状态,分别代表,高过平均值,平均值,低于平均值,上表就是根据AdWords后台提供的三个数值的三种不同状态得到一共3*3*3=27种状态,分别对应了QS的1-10分,好,现在有人要说,这些数据后台都能看,你整理出来没什么用啊。




真没用?

先不看下面,给你2分钟时间想想,这些数据到底有什么作用
















      想到了么?不管想到没有,我们接着往下看。




2.  QS权重比例


      如果我们把上表三个要素的权重统计一下的话,会得到下面一个数据







是的,LP,预期点击率,Ad相关性占QS的权重分别是 39%, 39%,22%,不是1:1:1,那么我们可以通过这个得到什么呢?





3. 信息对称  Vs. 信息不对称 



我们把知识分为两类:

一类是科学知识,即被组织起来的知识由专家所掌握,在理论和书籍中可以得到

一类是特定时间和地点的知识,为处于当时和当地的人所拥有。

上述两种知识,我们把它换成信息来看,就可以分为信息对称知识和信息不对称知识,今天你在上面阅读到的所有内容,都是有依可循,能查到出处的,这些是信息对称,而下面我要说的,以及我通过对称的信息推倒出来的信息或者知识,对于你我来说,是不对称的信息,而当你读完这篇文章后,对你我来说,这些信息又是对称的了。

有点绕口。

我们把上面的几个要素分为两块的话,哪些是信息对称的,哪些是信息不对称的呢?请看下图:





高竞争词PPC这个前提下,意味着信息对称的知识,所有广告主一定是花了大功夫大精力去做的,竞争对手的Landing Page 和 Ad你是直接能看到的,相关的优化方法和知识,要多少有多少,最不济通过A/B Test,也是可以慢慢提升的。


因此,对于你和竞争对手而言,上述信息不对称的,只有CTR。


4.排名和CTR

CTR和排名的关系,很多人已经有研究,为了方便下面讲下去,我们需要引用一些数据支撑,注意这些只是理论数据,实际情况是更复杂的。

大家都知道,广告的排名越高,理论上CTR会越高,根据一些第三方的数据,CTR的值是这样的:


数据来源:Accuracast

我们把它统计一下,于是有了下面这张图:



左侧是排名,中间是CTR,最右侧是后面的排名CTR占第一名CTR的比例,这三个数字都不难理解。


我在第二节提到的,QS影响因子所占权重占比,我们把它列出来



接下来,便于我们理解后面的内容,我们假设第一名和后面的竞争对手,Ad & LP 都是一样的,只有CTR不一样,根据第三方的比例,我们得出的不同排名CTR如下图红圈所示:



加上昨天的两个公式和我们QS的计算方法:


公式1:

公式2:AdRank=Max Bid*Quality Score

公式3:QS=CTR+Ad+LP


我们得出下面这个表:



不够直观,我们把它换成图像:



看出什么了么?

是的,第二名的Max.Bid 非常高,而在一个范围中,无论Max.Bid 出到多高,对你的Actual CPC几乎没有影响。

结论是有违直觉的,但是却又是真实的。

第二名通过出到极高的CPC,稳定了排名,同时只要你的Ad Rank 高过第三名,你的CPC往往之比第三名高一点点。

因此Google AdWords高竞争词排名策略,也就是我们的第三个结论,一句话就可以概括:

3.其他都做好的前提下,想要更低的CPC,提高出价,提高排名。

有人想说,你这个说的太理论化了,实际情况不是这样的。

是的,我这些都是纯理论,涉及到实际的投放,又是完全不一样的。

因为实际投放,第二名的Actual应该更低。

为什么?因为真实情况更接近这张图:



竞争对手的 Ad、LP 并不是一样的,因为如果所有人都Above Average,那么所有人都是 Average。

而Ad、LP 严重影响了Ad Rank。

而Ad 和 Ad Rank又影响了CTR。

所有因素都是相互牵制,相互关联的。

因此真实情况,第二名的Actual CPC 比后面几名都低。

并且同时拥有更高的ROI和更低的CPA。

如果我不打破你脑子里: 高 Max.CPC=高 Actual CPC的概念,在上述的例子中,你是打死不会出到超过3美金的出价的。

而同时你确丧失了高竞争词PPC的展示机会。

上面提到的是策略,是在高竞争词下面的一个非常有效的策略,
 
但是实际操作过程中,你一定不是简单提高出价就能做得好
 
什么时候提高出价,提高后产生较高的ActualCPC怎么办?
 
这种面对实际情况的从容应对的操作技巧,才是真技术,但这不是我们这次讨论的范围。
 
下期见。

Quality Score Tracker v3.0

白熊 发表了文章 • 2 个评论 • 91 次浏览 • 2017-01-04 17:07 • 来自相关话题

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Quality Score Tracker v3.0

function main(){
/*
* The following preferences can be changed to customize this script.
* Most of options can be set by using 1 for yes or 0 for no.
* You don't have to change anything here. The script will do fine with the defaults.
*/
var config = {
/*
* Which of the following charts should be displayed on the dashboard?
* The "per QS" charts are column charts. They show the current state compared to a previous one (see next option).
* "Average" and "weighted" charts are line charts, showing changes over time.
*/
"chartsToDisplay" : {
"Keywords per QS" : 0,
"Average QS" : 0,
"Keywords with Impressions per QS" : 1,
"Average QS for Keywords with Impressions" : 0,
"Impressions per QS" : 0,
"Impression weighted QS" : 1,
"Clicks per QS" : 0,
"Click weighted QS" : 0,
"Conversions per QS" : 0,
"Conversion weighted QS" : 0,
"Conversion value per QS" : 0,
"Conversion value weighted QS" : 0,
},
/*
* Column charts can show a former date for comparison. Set the number of steps you want to go back for this.
* Note that the date you're comparing this to will depend on how often you've run the script in the past.
* Example: If the setting is 30 and you ran the script daily, your comparison will be with the values from 30 days before. If you ran it hourly, it will be with values from 30 hours before.
* If you haven't run the script often enough, the comparison will go as far back as possible.
* Put 0 to disable the comparison.
*/
"chartsCompareStepsBack" : 30,
/*
* When stats are taken into account (like impressions per QS, or impression weighted QS), this timeframe is used.
* Note that this affects the values to be tracked and stored. Past values that are already stored won't be affected.
* Use one of the following: TODAY, YESTERDAY, LAST_7_DAYS, THIS_WEEK_SUN_TODAY, THIS_WEEK_MON_TODAY, LAST_WEEK, LAST_14_DAYS, LAST_30_DAYS, LAST_BUSINESS_WEEK, LAST_WEEK_SUN_SAT, THIS_MONTH
*/
"statsTimeframe" : "LAST_30_DAYS",
/*
* Whether to only look at stats from Google (e.g. for impression weighted QS).
* Recommended. Quality Score itself only reflects data from Google, so weighting should only take Google into account and leave out search partners.
* Note that this affects the values to be tracked and stored. Past values that are already stored won't be affected.
*/
"googleOnly" : 1,
/*
* Whether to only track active keywords. This means that the keyword, the adgroup, and the campaign have to be enabled.
* Recommended. Otherwise inactive keywords with meaningless Quality Scores might skew your data.
*/
"activeKeywordsOnly" : 1,
/*
* Set to 1 if you want your dates (in charts, table headers, and file names) to contain hours and minutes as well.
* Do this if you want to run the script hourly.
*/
"useHours" : 0,
/*
* Use this option to not keep track of individual keywords' Quality Scores and only save data to the dashboard file.
* This makes sense if you have more than 400,000 keywords. Note that you don't have to change this: The script will notice on its own and log a message otherwise.
*/
"skipIndividualKeywords" : 0,
/*
* The name of the file where dashboard and summarized data are stored.
*/
"summaryFileName" : "Dashboard + Summary",
/*
* The base folder for all Quality Score Tracker files.
*/
"baseFolder" : "Quality Score Tracker/",
/*
* Whether to add a client folder in the base folder (resulting in a folder like "Quality Score Tracker/CLIENT_NAME (123-456-7890)/")
* The folder's name is not important, as long as the Adwords client id remains in it.
* This is useful if you want to track multiple accounts with this script.
*/
"useClientFolder" : 1,
}

trackQS(config);
}

function trackQS(config){
var version = "3.0";

if(config['useHours']) var dateString = Utilities.formatDate(new Date(), AdWordsApp.currentAccount().getTimeZone(), "yyyy-MM-dd HH:mm");
else var dateString = Utilities.formatDate(new Date(), AdWordsApp.currentAccount().getTimeZone(), "yyyy-MM-dd");

var folder = getOrCreateFolder(config['baseFolder']);
if(config['useClientFolder']) folder = getOrCreateClientFolder(folder);

// Find the latest report file in the folder.
var maxFileNumber = 0;
var reportFile;
var summaryFile;
var fileIterator = folder.getFiles();
while(fileIterator.hasNext()){
var file = fileIterator.next();
var matches = new RegExp(' #([0-9]+) ').exec(file.getName());
if(matches && parseInt(matches[1]) > maxFileNumber){
maxFileNumber = parseInt(matches[1]);
reportFile = file;
}else if(file.getName() == config['summaryFileName']) summaryFile = file;
}

// No report file found? Add a new one.
if(maxFileNumber == 0){
reportFile = addReportFile(folder, "QS Report #1 (" + dateString + ")");
maxFileNumber = 1;
}
// No summary file found? Add a new one.
if(!summaryFile) summaryFile = addSummaryFile(folder, config['summaryFileName']);

Logger.log("All files are stored at");
Logger.log(folder.getUrl());
Logger.log("The dashboard is here:");
Logger.log(summaryFile.getUrl());

var spreadsheet = SpreadsheetApp.open(reportFile);
var sheet = spreadsheet.getActiveSheet();
var idColumnValues = sheet.getRange(1, 4, sheet.getLastRow(), 1).getValues();
var summarySpreadsheet = SpreadsheetApp.open(summaryFile);
updateInfo(summarySpreadsheet, version);
var sheetCharts = summarySpreadsheet.getSheetByName("Dashboard");

summarySpreadsheet.setActiveSheet(sheetCharts);
summarySpreadsheet.moveActiveSheet(1);

// Track an event in Google Analytics.
trackInAnalytics(version);

// Remember the line number for every keyword.
var lineNumbers = {};
var lastRowNumber = sheet.getLastRow();
for(var i = 1; i < lastRowNumber; i++){
lineNumbers[idColumnValues[i][0]] = i;
}

// qsValues represents the new column that will go right next to the others.
var qsValues = new Array(sheet.getLastRow());
qsValues[0] = [ dateString ];
// Initialize everything with an empty string.
var qsValuesLength = qsValues.length;
for(var i = 1; i < qsValuesLength; i++) qsValues[i] = [""];

// In case new keywords are found, they'll be added as new rows below the rest (campaign, adgroup, keyword, id string).
var newRows = [];

// All aggregated data goes in this variable.
var qsStats = {
"Keywords" : {},
"Keywords with impressions" : {},
"Impressions" : {},
"Clicks" : {},
"Conversions" : {},
"Conversion value" : {}
};
// Initialize the arrays so that everything can be added up later. Index 0 is for totals, 1-10 for Quality Scores.
for(var key in qsStats){
for(var i = 0; i <= 10; i++){
qsStats[key][i] = 0;
}
}

// Get the data from AdWords.
var awql = "SELECT Id, Criteria, KeywordMatchType, CampaignId, CampaignName, AdGroupId, AdGroupName, QualityScore, Impressions, Clicks, Conversions, ConversionValue FROM KEYWORDS_PERFORMANCE_REPORT WHERE Id NOT_IN [3000000, 3000006] AND Status = 'ENABLED' AND AdGroupStatus = 'ENABLED' AND CampaignStatus = 'ENABLED'";
if(config['googleOnly']) awql += " AND AdNetworkType2 = 'SEARCH'";
if(config['activeKeywordsOnly']) awql += " AND CampaignStatus = 'ENABLED' AND AdGroupStatus = 'ENABLED' AND Status = 'ENABLED'";
awql += " DURING " + config['statsTimeframe'];
var report = AdWordsApp.report(awql);
var reportRows = report.rows();

// Go through the report and count Quality Scores.
while(reportRows.hasNext()){
var row = reportRows.next();
// Save the aggregated data.
qsStats['Keywords'][row['QualityScore']]++;
if(row['Impressions'] > 0) qsStats['Keywords with impressions'][row['QualityScore']]++;
qsStats['Impressions'][row['QualityScore']] += parseInt(row['Impressions']);
qsStats['Clicks'][row['QualityScore']] += parseInt(row['Clicks']);
qsStats['Conversions'][row['QualityScore']] += parseInt(row['Conversions']);
qsStats['Conversion value'][row['QualityScore']] += parseInt(row['ConversionValue']);

// Save the individual keyword's Quality Score.
if(!config['skipIndividualKeywords']){
var id = row['CampaignId']+"_"+row['AdGroupId']+"_"+row['Id'];
// Check if there is already a line for this keyword
if(lineNumbers[id]) var line_number = lineNumbers[id];
else{
// There is no line for this keyword yet. Create a new one and add the line headers.
line_number = qsValues.length;
if(row['KeywordMatchType'] == "Exact") var keyword = '[' + row['Criteria'] + ']';
else if(row['KeywordMatchType'] == "Phrase") var keyword = '"' + row['Criteria'] + '"';
else var keyword = row['Criteria'];

newRows.push([row['CampaignName'], row['AdGroupName'], keyword, id]);
}

qsValues[line_number] = [row['QualityScore']];
}
}

// Check if everything fits.
if(!config['skipIndividualKeywords']){
// A spreadsheet can hold up to 2 million cells. Calculate if the new data will fit in with the rest.
// With four rows needed for every keyword, plus one for every tracking run, this won't fit if there are more than 400,000 rows (header + 399,999 keywords).
if(qsValues.length >= 400000){
Logger.log("There are too many keywords to be tracked (" + qsValues.length + "). This tool can only track up to 399,999 keywords.");
Logger.log("A summary will be logged, but individual keyword quality scores cannot be stored.");
skipIndividualKeywords = true;
}else if(qsValues.length * (sheet.getLastColumn() + 1) > 2000000){
// This spreadsheet is full, a new one is needed.
// Add new file.
maxFileNumber++;
reportFile = addReportFile(folder, "QS Report #" + maxFileNumber + " (" + dateString + ")");
var newSpreadsheet = SpreadsheetApp.open(reportFile);
var newSheet = newSpreadsheet.getActiveSheet();
// Copy the first columns from the old sheet to the new one.
newSheet.getRange(1, 1, sheet.getLastRow(), 4).setValues(sheet.getRange(1, 1, sheet.getLastRow(), 4).getValues());
// From now on, work with the new sheet and spreadsheet.
spreadsheet = newSpreadsheet;
sheet = newSheet;
}
}

// Store the keyword data in the spreadsheet.
if(!config['skipIndividualKeywords']){
// If there are new rows, add their line headers beneath the others.
if(newRows.length > 0){
var sheetLastRow = sheet.getLastRow();
sheet.insertRowsAfter(sheetLastRow, newRows.length).getRange(sheetLastRow + 1, 1, newRows.length, 4).setValues(newRows);
sheet.autoResizeColumn(1).autoResizeColumn(2).autoResizeColumn(3);
}
// Add a new column with the tracked data.
var sheetLastColumn = sheet.getLastColumn();
sheet.insertColumnAfter(sheetLastColumn);
sheet.getRange(1, sheetLastColumn + 1, qsValues.length, 1).setValues(qsValues);
sheet.autoResizeColumn(sheetLastColumn + 1);

// Change file name to reflect the new date.
// Find out which dates are currently noted in the file's name.
var matches = /\((.*?)( - (.*))?\)/.exec(reportFile.getName());
if(matches && matches[1]){
if(matches[2]){
// There's a start date and an end date.
var startDate = matches[1];
var endDate = matches[3];
if(endDate != dateString){
var newFileName = reportFile.getName().replace(endDate, dateString);
reportFile.setName(newFileName);
}
}else{
// There's just a start date.
var startDate = matches[1];
if(startDate != dateString){
var newFileName = reportFile.getName().replace(startDate, startDate + " - " + dateString);
reportFile.setName(newFileName);
}
}
}else{
Logger.log("Could not recognize dates in file name " + reportFile.getName() +". File name remains unchanged.");
}
}

// Now take care of the summary file.

// Get the total numbers.
for(var key in qsStats){
for(var i = 1; i <= 10; i++){
qsStats[key][0] += qsStats[key][i];
}
}

// Prepare a new column for the Percentages data sheet.
var newValues = [];
var newValuesNumberFormats = [];

for(var key in qsStats){
newValues.push([dateString]);
newValuesNumberFormats.push(["@STRING@"]);
for(var i = 1; i <= 10; i++){
if(qsStats[key][0]) newValues.push([qsStats[key][i] / qsStats[key][0]]); else newValues.push([0]);
newValuesNumberFormats.push(["0.00%"]);
}
newValues.push([qsStats[key][0]]);
newValuesNumberFormats.push(["0.##"]);
}

var sheetPercentages = summarySpreadsheet.getSheetByName("Percentages");
var sheetAverages = summarySpreadsheet.getSheetByName("Averages");

var lastCol = sheetPercentages.getLastColumn() + 1;
var lastRow = sheetAverages.getLastRow() + 1;

// Add the data to the Percentages sheet.
sheetPercentages.insertColumnAfter(lastCol - 1);
sheetPercentages.getRange(1, lastCol, 72, 1).setNumberFormats(newValuesNumberFormats).setValues(newValues);
sheetPercentages.autoResizeColumn(lastCol);

// Add a new row with formulas to the Averages sheet.
sheetAverages.appendRow([""]);
sheetAverages.getRange(lastRow, 1, 1, 1).setValue(dateString);
sheetAverages.getRange(lastRow, 2, 1, 6).setFormulasR1C1([[
"=SUMPRODUCT(Percentages!R2C1:R11C1; Percentages!R2C" + lastCol + ":R11C" + lastCol + ")",
"=SUMPRODUCT(Percentages!R14C1:R23C1; Percentages!R14C" + lastCol + ":R23C" + lastCol + ")",
"=SUMPRODUCT(Percentages!R26C1:R35C1; Percentages!R26C" + lastCol + ":R35C" + lastCol + ")",
"=SUMPRODUCT(Percentages!R38C1:R47C1; Percentages!R38C" + lastCol + ":R47C" + lastCol + ")",
"=SUMPRODUCT(Percentages!R50C1:R59C1; Percentages!R50C" + lastCol + ":R59C" + lastCol + ")",
"=SUMPRODUCT(Percentages!R62C1:R71C1; Percentages!R62C" + lastCol + ":R71C" + lastCol + ")"
]]);


// The properties for the charts. This is not meant to be reconfigured.
var chartsProperties = {
"Keywords per QS" : {
"type" : "column",
"vCol" : 2,
},
"Average QS" : {
"type" : "line",
"vCol" : 2,
},
"Keywords with Impressions per QS" : {
"type" : "column",
"vCol" : 3,
},
"Average QS for Keywords with Impressions" : {
"type" : "line",
"vCol" : 3,
},
"Impressions per QS" : {
"type" : "column",
"vCol" : 4,
},
"Impression weighted QS" : {
"type" : "line",
"vCol" : 4,
},
"Clicks per QS" : {
"type" : "column",
"vCol" : 5,
},
"Click weighted QS" : {
"type" : "line",
"vCol" : 5,
},
"Conversions per QS" : {
"type" : "column",
"vCol" : 6,
},
"Conversion weighted QS" : {
"type" : "line",
"vCol" : 6,
},
"Conversion value per QS" : {
"type" : "column",
"vCol" : 7,
},
"Conversion value weighted QS" : {
"type" : "line",
"vCol" : 7,
},
};

var row = 1;
var col = 1;
var summarySheets = {
"dataH": sheetPercentages,
"dataV": sheetAverages,
"charts": sheetCharts,
}

// Add charts to the dashboard.
for(var chartName in config['chartsToDisplay']){
// Skip all charts that are not set to be displayed.
if(!config['chartsToDisplay'][chartName]) continue;

addChartToDashboard(chartName, chartsProperties[chartName]['type'], summarySheets, row, col, lastRow, lastCol, chartsProperties[chartName]['vCol'], config['chartsCompareStepsBack']);

// Add the "Average QS" cells.
sheetCharts.setRowHeight(row, 60).setRowHeight(row + 1, 20).setRowHeight(row + 2, 270);
sheetCharts.getRange(row, 2).setValue("Average QS").setFontWeight("bold").setFontSize(24).setBorder(true, true, false, true, null, null);
sheetCharts.getRange(row + 2, 2).setFontWeight("bold").setFontSize(24).setNumberFormat("0.00").setBorder(false, true, false, true, null, null);
sheetCharts.getRange(row + 1, 2, 2, 1).setFormulasR1C1(
[
["=LOWER(Averages!R1C" + chartsProperties[chartName]['vCol'] + ")"], ["=Averages!R" + lastRow + "C" + chartsProperties[chartName]['vCol']]
]).setBorder(false, true, true, true, null, null);
sheetCharts.autoResizeColumn(2);
row += 3;
}
}

/*
* Checks if there is a folder with the given name in the Google Drive root folder. If not, the folder is created.
* The folderName can be in the form of a complete path with subfolders, like "QS Reports/123/whatever".
* Returns the folder.
*/
function getOrCreateFolder(folderName){
return getOrCreateFolderFromArray(folderName.toString().split("/"), DriveApp.getRootFolder());
}

/*
* Does the actual work for getOrCreateFolder. Recursive function, based on an array of folder names (to handle paths with subfolders).
*/
function getOrCreateFolderFromArray(folderNameArray, currentFolder){
var folderName = "";
// Skip empty folders (multiple slashes or a slash at the end).
do folderName = folderNameArray.shift(); while(folderName == "" && folderNameArray.length > 0);

if(folderName == "") return currentFolder;

// See if the folder is already there.
var folderIterator = currentFolder.getFoldersByName(folderName);
if(folderIterator.hasNext()){
var folder = folderIterator.next();
}else{
// Create folder.
Logger.log("Creating folder '" + folderName + "'");
var folder = currentFolder.createFolder(folderName);
}

if(folderNameArray.length > 0) return getOrCreateFolderFromArray(folderNameArray, folder);
return folder;
}

/*
* Checks if there is a folder for the current client account in the base folder. If not, the folder is created.
* Existing client folders are recognized by the client id in parentheses. This way, folders can be found again, even if an account has been renamed.
*/
function getOrCreateClientFolder(baseFolder){
var folderIterator = baseFolder.getFolders();
var regExp = new RegExp(AdWordsApp.currentAccount().getCustomerId());
while(folderIterator.hasNext()){
var folder = folderIterator.next();
if(folder.getName().match(regExp)) return folder;
}
// Since no folder has been found: Create one.
var newFolderName = AdWordsApp.currentAccount().getName() + " (" + AdWordsApp.currentAccount().getCustomerId() + ")";
Logger.log("Creating folder '" + newFolderName + "'");
return baseFolder.createFolder(newFolderName);
}

/*
* Creates a spreadsheet for QS tracking.
* Adds headers to the spreadsheet.
* Returns the file.
*/
function addReportFile(folder, name){
var spreadsheet = SpreadsheetApp.create(name, 1, 4);
var sheet = spreadsheet.getActiveSheet();
sheet.setName("QS history");
// Put in the table headings
sheet.getRange(1, 1, 1, 4).setValues([["Campaign", "AdGroup", "Keyword", "ID string"]]);
//sheet.getRange(1, 1, 1, 4).setFontWeight("bold");
sheet.setColumnWidth(4, 1);
var file = DriveApp.getFileById(spreadsheet.getId());
folder.addFile(file);
var parentFolder = file.getParents().next();
parentFolder.removeFile(file);
return folder.getFilesByName(name).next();
}

/*
* Creates a spreadsheet for the summary and stores it in the folder.
* Creates sheets for the Percentages and Averages.
* Populates header rows and columns.
*/
function addSummaryFile(folder, name){
var spreadsheet = SpreadsheetApp.create(name);
var sheetH = spreadsheet.getActiveSheet();
sheetH.setName("Percentages");

// Add the first column for the horizontal data table.
sheetH.getRange(1, 1, 72, 1).setValues(
[["All keywords"], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], ['Total'],
["Keywords with impressions"], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], ['Total'],
["Impression weighted"], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], ['Total'],
["Click weighted"], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], ['Total'],
["Conversion weighted"], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], ['Total'],
["Conversion value weighted"], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], ['Total']
]
);
sheetH.getRange("A:A").setNumberFormat('@STRING@');
sheetH.autoResizeColumn(1);

var sheetV = spreadsheet.insertSheet("Averages");
// Add the first rows for the vertical data table.
sheetV.getRange(1, 1, 4, 7).setValues([
["Date", "Average", "Average for keywords with impressions", "Impression weighted", "Click weighted", "Conversion weighted", "Value weighted"],
["Highest", "", "", "", "", "", ""],
["Lowest", "", "", "", "", "", ""],
["Average", "", "", "", "", "", ""]
]);
// Add some formulas for maximums, minimums, and averages.
sheetV.getRange(2, 2, 3, 6).setFormulas([
["=MAX(B$5:B)", "=MAX(C$5:C)", "=MAX(D$5:D)", "=MAX(E$5:E)", "=MAX(F$5:F)", "=MAX(G$5:G)"],
["=MIN(B$5:B)", "=MIN(C$5:C)", "=MIN(D$5:D)", "=MIN(E$5:E)", "=MIN(F$5:F)", "=MIN(G$5:G)"],
["=AVERAGE(B$5:B)", "=AVERAGE(C$5:C)", "=AVERAGE(D$5:D)", "=AVERAGE(E$5:E)", "=AVERAGE(F$5:F)", "=AVERAGE(G$5:G)"]
]);
sheetV.getRange(1, 1, 1, 7).setFontWeight("bold").setNumberFormat('@STRING@');
sheetV.autoResizeColumn(1);
sheetV.autoResizeColumn(2);
sheetV.autoResizeColumn(3);
sheetV.autoResizeColumn(4);
sheetV.autoResizeColumn(5);
sheetV.autoResizeColumn(6);
sheetV.autoResizeColumn(7);

// Store the spreadsheet.
var file = DriveApp.getFileById(spreadsheet.getId());
folder.addFile(file);
var parentFolder = file.getParents().next();
parentFolder.removeFile(file);
return folder.getFilesByName(name).next();
}

/*
* Replaces the About sheet in the summary spreadsheet with a fresh one from the master sheet. This way, the sheet (including the FAQ) stays up to date.
* Also replaces the Dashboard with a fresh copy (resulting in an empty sheet with the correct conditional formatting).
* If there's a new version, a sheet "New Version Available!" is added.
*/
function updateInfo(summarySpreadsheet, version){
var templateSpreadsheet = SpreadsheetApp.openByUrl("https://docs.google.com/spread ... 6quot;);

var oldSheet = summarySpreadsheet.getSheetByName("Dashboard");
if(oldSheet) summarySpreadsheet.deleteSheet(oldSheet);
templateSpreadsheet.getSheetByName("Dashboard v" + version).copyTo(summarySpreadsheet).setName("Dashboard");

var oldSheet = summarySpreadsheet.getSheetByName("About + FAQ");
if(oldSheet) summarySpreadsheet.deleteSheet(oldSheet);
templateSpreadsheet.getSheetByName("About v" + version).copyTo(summarySpreadsheet).setName("About + FAQ");

var oldSheet = summarySpreadsheet.getSheetByName("New Version Available!");
if(oldSheet) summarySpreadsheet.deleteSheet(oldSheet);

// Check if there is a newer version.
var versionHistory = templateSpreadsheet.getSheetByName("Version History").getDataRange().getValues();
if(versionHistory[0][0] != version){
// There's a new version available (at least one).
// Look for the row which has the info about the current (old) version.
var oldVersionRow = 1;
while(oldVersionRow < versionHistory.length && versionHistory[oldVersionRow][0] != version){
oldVersionRow++;
}

// Copy the entire version history.
var newVersionSheet = templateSpreadsheet.getSheetByName("Version History").copyTo(summarySpreadsheet).setName("New Version Available!");
// Remove everything about the old version.
newVersionSheet.deleteRows(oldVersionRow + 1, versionHistory.length - oldVersionRow);
// Add new Rows at the beginning.
newVersionSheet.insertRows(1, 6);
newVersionSheet.getRange(1, 1, 6, 2).setValues([["Latest version:", versionHistory[0][0]], ["Your version:", version], ["", ""], ["Get the latest version at", "http://www.ppc-epiphany.com/qstracker/latest"], ["", ""], ["Newer Versions", ""]]);
newVersionSheet.getRange(1, 1, 1, 2).setFontWeight("bold");
newVersionSheet.getRange(6, 1, 1, 1).setFontWeight("bold");
newVersionSheet.autoResizeColumn(1);
newVersionSheet.autoResizeColumn(2);
}
}

/*
* Inserts a line or column chart into the dashboard sheet.
* The chart is based on data from the Percentages or Averages sheet.
*/
function addChartToDashboard(name, type, sheets, row, col, lastRow, lastCol, vCol, compareStepsBack){
var chartBuilder = sheets['charts'].newChart();
chartBuilder
.setOption('title', name)
.setOption('width', 800)
.setOption('height', 349)
.setOption('colors', ['#fa9d1c','#00507d'])
.setPosition(row, col, 0, 0);

switch(type){
case "column":
var statsRow = (vCol - 2) * 12 + 1;
// First range for a column chart is always the same column with QS from 1 to 10.
var dataRanges = [sheets['dataH'].getRange(1, 1, 11, 1)];
if(compareStepsBack && lastCol > 2){
// The column for comparison is either the specified number of columns behind lastCol, or 2 (the first column with data).
dataRanges.push(sheets['dataH'].getRange(statsRow, Math.max(2, lastCol - compareStepsBack), 11, 1));
}
dataRanges.push(sheets['dataH'].getRange(statsRow, lastCol, 11, 1));
chartBuilder = chartBuilder.asColumnChart();
break;
case "line":
var dataRanges = [sheets['dataV'].getRange(5, 1, lastRow - 2, 1), sheets['dataV'].getRange(5, vCol, lastRow - 2, 1)];
chartBuilder = chartBuilder.asLineChart();
chartBuilder.setOption("vAxis.maxValue", 10);
chartBuilder.setOption("vAxis.ticks", [0,2,4,6,8,10]);
chartBuilder.setLegendPosition(Charts.Position.NONE);
break;
}

for(var i in dataRanges) chartBuilder.addRange(dataRanges[i]);
sheets['charts'].insertChart(chartBuilder.build());
}

/*
* Tracks the execution of the script as an event in Google Analytics.
* Sends the version number and a random UUID (basically just a random number, required by Analytics).
* Basically tells that somewhere someone ran the script with a certain version.
* Credit for the idea goes to Russel Savage, who posted his version at http://www.freeadwordsscripts. ... html.
*/
function trackInAnalytics(version){
// Create the random UUID from 30 random hex numbers gets them into the format xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx (with y being 8, 9, a, or b).
var uuid = "";
for(var i = 0; i < 30; i++){
uuid += parseInt(Math.random()*16).toString(16);
}
uuid = uuid.substr(0, 8) + "-" + uuid.substr(8, 4) + "-4" + uuid.substr(12, 3) + "-" + parseInt(Math.random() * 4 + 8).toString(16) + uuid.substr(15, 3) + "-" + uuid.substr(18, 12);

var url = "http://www.google-analytics.co ... ot%3B + uuid + "&ds=adwordsscript&an=qstracker&av="
+ version
+ "&ec=AdWords%20Scripts&ea=Script%20Execution&el=QS%20Tracker%20v" + version;
UrlFetchApp.fetch(url);
} 查看全部
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Quality Score Tracker v3.0

function main(){
/*
* The following preferences can be changed to customize this script.
* Most of options can be set by using 1 for yes or 0 for no.
* You don't have to change anything here. The script will do fine with the defaults.
*/
var config = {
/*
* Which of the following charts should be displayed on the dashboard?
* The "per QS" charts are column charts. They show the current state compared to a previous one (see next option).
* "Average" and "weighted" charts are line charts, showing changes over time.
*/
"chartsToDisplay" : {
"Keywords per QS" : 0,
"Average QS" : 0,
"Keywords with Impressions per QS" : 1,
"Average QS for Keywords with Impressions" : 0,
"Impressions per QS" : 0,
"Impression weighted QS" : 1,
"Clicks per QS" : 0,
"Click weighted QS" : 0,
"Conversions per QS" : 0,
"Conversion weighted QS" : 0,
"Conversion value per QS" : 0,
"Conversion value weighted QS" : 0,
},
/*
* Column charts can show a former date for comparison. Set the number of steps you want to go back for this.
* Note that the date you're comparing this to will depend on how often you've run the script in the past.
* Example: If the setting is 30 and you ran the script daily, your comparison will be with the values from 30 days before. If you ran it hourly, it will be with values from 30 hours before.
* If you haven't run the script often enough, the comparison will go as far back as possible.
* Put 0 to disable the comparison.
*/
"chartsCompareStepsBack" : 30,
/*
* When stats are taken into account (like impressions per QS, or impression weighted QS), this timeframe is used.
* Note that this affects the values to be tracked and stored. Past values that are already stored won't be affected.
* Use one of the following: TODAY, YESTERDAY, LAST_7_DAYS, THIS_WEEK_SUN_TODAY, THIS_WEEK_MON_TODAY, LAST_WEEK, LAST_14_DAYS, LAST_30_DAYS, LAST_BUSINESS_WEEK, LAST_WEEK_SUN_SAT, THIS_MONTH
*/
"statsTimeframe" : "LAST_30_DAYS",
/*
* Whether to only look at stats from Google (e.g. for impression weighted QS).
* Recommended. Quality Score itself only reflects data from Google, so weighting should only take Google into account and leave out search partners.
* Note that this affects the values to be tracked and stored. Past values that are already stored won't be affected.
*/
"googleOnly" : 1,
/*
* Whether to only track active keywords. This means that the keyword, the adgroup, and the campaign have to be enabled.
* Recommended. Otherwise inactive keywords with meaningless Quality Scores might skew your data.
*/
"activeKeywordsOnly" : 1,
/*
* Set to 1 if you want your dates (in charts, table headers, and file names) to contain hours and minutes as well.
* Do this if you want to run the script hourly.
*/
"useHours" : 0,
/*
* Use this option to not keep track of individual keywords' Quality Scores and only save data to the dashboard file.
* This makes sense if you have more than 400,000 keywords. Note that you don't have to change this: The script will notice on its own and log a message otherwise.
*/
"skipIndividualKeywords" : 0,
/*
* The name of the file where dashboard and summarized data are stored.
*/
"summaryFileName" : "Dashboard + Summary",
/*
* The base folder for all Quality Score Tracker files.
*/
"baseFolder" : "Quality Score Tracker/",
/*
* Whether to add a client folder in the base folder (resulting in a folder like "Quality Score Tracker/CLIENT_NAME (123-456-7890)/")
* The folder's name is not important, as long as the Adwords client id remains in it.
* This is useful if you want to track multiple accounts with this script.
*/
"useClientFolder" : 1,
}

trackQS(config);
}

function trackQS(config){
var version = "3.0";

if(config['useHours']) var dateString = Utilities.formatDate(new Date(), AdWordsApp.currentAccount().getTimeZone(), "yyyy-MM-dd HH:mm");
else var dateString = Utilities.formatDate(new Date(), AdWordsApp.currentAccount().getTimeZone(), "yyyy-MM-dd");

var folder = getOrCreateFolder(config['baseFolder']);
if(config['useClientFolder']) folder = getOrCreateClientFolder(folder);

// Find the latest report file in the folder.
var maxFileNumber = 0;
var reportFile;
var summaryFile;
var fileIterator = folder.getFiles();
while(fileIterator.hasNext()){
var file = fileIterator.next();
var matches = new RegExp(' #([0-9]+) ').exec(file.getName());
if(matches && parseInt(matches[1]) > maxFileNumber){
maxFileNumber = parseInt(matches[1]);
reportFile = file;
}else if(file.getName() == config['summaryFileName']) summaryFile = file;
}

// No report file found? Add a new one.
if(maxFileNumber == 0){
reportFile = addReportFile(folder, "QS Report #1 (" + dateString + ")");
maxFileNumber = 1;
}
// No summary file found? Add a new one.
if(!summaryFile) summaryFile = addSummaryFile(folder, config['summaryFileName']);

Logger.log("All files are stored at");
Logger.log(folder.getUrl());
Logger.log("The dashboard is here:");
Logger.log(summaryFile.getUrl());

var spreadsheet = SpreadsheetApp.open(reportFile);
var sheet = spreadsheet.getActiveSheet();
var idColumnValues = sheet.getRange(1, 4, sheet.getLastRow(), 1).getValues();
var summarySpreadsheet = SpreadsheetApp.open(summaryFile);
updateInfo(summarySpreadsheet, version);
var sheetCharts = summarySpreadsheet.getSheetByName("Dashboard");

summarySpreadsheet.setActiveSheet(sheetCharts);
summarySpreadsheet.moveActiveSheet(1);

// Track an event in Google Analytics.
trackInAnalytics(version);

// Remember the line number for every keyword.
var lineNumbers = {};
var lastRowNumber = sheet.getLastRow();
for(var i = 1; i < lastRowNumber; i++){
lineNumbers[idColumnValues[i][0]] = i;
}

// qsValues represents the new column that will go right next to the others.
var qsValues = new Array(sheet.getLastRow());
qsValues[0] = [ dateString ];
// Initialize everything with an empty string.
var qsValuesLength = qsValues.length;
for(var i = 1; i < qsValuesLength; i++) qsValues[i] = [""];

// In case new keywords are found, they'll be added as new rows below the rest (campaign, adgroup, keyword, id string).
var newRows = [];

// All aggregated data goes in this variable.
var qsStats = {
"Keywords" : {},
"Keywords with impressions" : {},
"Impressions" : {},
"Clicks" : {},
"Conversions" : {},
"Conversion value" : {}
};
// Initialize the arrays so that everything can be added up later. Index 0 is for totals, 1-10 for Quality Scores.
for(var key in qsStats){
for(var i = 0; i <= 10; i++){
qsStats[key][i] = 0;
}
}

// Get the data from AdWords.
var awql = "SELECT Id, Criteria, KeywordMatchType, CampaignId, CampaignName, AdGroupId, AdGroupName, QualityScore, Impressions, Clicks, Conversions, ConversionValue FROM KEYWORDS_PERFORMANCE_REPORT WHERE Id NOT_IN [3000000, 3000006] AND Status = 'ENABLED' AND AdGroupStatus = 'ENABLED' AND CampaignStatus = 'ENABLED'";
if(config['googleOnly']) awql += " AND AdNetworkType2 = 'SEARCH'";
if(config['activeKeywordsOnly']) awql += " AND CampaignStatus = 'ENABLED' AND AdGroupStatus = 'ENABLED' AND Status = 'ENABLED'";
awql += " DURING " + config['statsTimeframe'];
var report = AdWordsApp.report(awql);
var reportRows = report.rows();

// Go through the report and count Quality Scores.
while(reportRows.hasNext()){
var row = reportRows.next();
// Save the aggregated data.
qsStats['Keywords'][row['QualityScore']]++;
if(row['Impressions'] > 0) qsStats['Keywords with impressions'][row['QualityScore']]++;
qsStats['Impressions'][row['QualityScore']] += parseInt(row['Impressions']);
qsStats['Clicks'][row['QualityScore']] += parseInt(row['Clicks']);
qsStats['Conversions'][row['QualityScore']] += parseInt(row['Conversions']);
qsStats['Conversion value'][row['QualityScore']] += parseInt(row['ConversionValue']);

// Save the individual keyword's Quality Score.
if(!config['skipIndividualKeywords']){
var id = row['CampaignId']+"_"+row['AdGroupId']+"_"+row['Id'];
// Check if there is already a line for this keyword
if(lineNumbers[id]) var line_number = lineNumbers[id];
else{
// There is no line for this keyword yet. Create a new one and add the line headers.
line_number = qsValues.length;
if(row['KeywordMatchType'] == "Exact") var keyword = '[' + row['Criteria'] + ']';
else if(row['KeywordMatchType'] == "Phrase") var keyword = '"' + row['Criteria'] + '"';
else var keyword = row['Criteria'];

newRows.push([row['CampaignName'], row['AdGroupName'], keyword, id]);
}

qsValues[line_number] = [row['QualityScore']];
}
}

// Check if everything fits.
if(!config['skipIndividualKeywords']){
// A spreadsheet can hold up to 2 million cells. Calculate if the new data will fit in with the rest.
// With four rows needed for every keyword, plus one for every tracking run, this won't fit if there are more than 400,000 rows (header + 399,999 keywords).
if(qsValues.length >= 400000){
Logger.log("There are too many keywords to be tracked (" + qsValues.length + "). This tool can only track up to 399,999 keywords.");
Logger.log("A summary will be logged, but individual keyword quality scores cannot be stored.");
skipIndividualKeywords = true;
}else if(qsValues.length * (sheet.getLastColumn() + 1) > 2000000){
// This spreadsheet is full, a new one is needed.
// Add new file.
maxFileNumber++;
reportFile = addReportFile(folder, "QS Report #" + maxFileNumber + " (" + dateString + ")");
var newSpreadsheet = SpreadsheetApp.open(reportFile);
var newSheet = newSpreadsheet.getActiveSheet();
// Copy the first columns from the old sheet to the new one.
newSheet.getRange(1, 1, sheet.getLastRow(), 4).setValues(sheet.getRange(1, 1, sheet.getLastRow(), 4).getValues());
// From now on, work with the new sheet and spreadsheet.
spreadsheet = newSpreadsheet;
sheet = newSheet;
}
}

// Store the keyword data in the spreadsheet.
if(!config['skipIndividualKeywords']){
// If there are new rows, add their line headers beneath the others.
if(newRows.length > 0){
var sheetLastRow = sheet.getLastRow();
sheet.insertRowsAfter(sheetLastRow, newRows.length).getRange(sheetLastRow + 1, 1, newRows.length, 4).setValues(newRows);
sheet.autoResizeColumn(1).autoResizeColumn(2).autoResizeColumn(3);
}
// Add a new column with the tracked data.
var sheetLastColumn = sheet.getLastColumn();
sheet.insertColumnAfter(sheetLastColumn);
sheet.getRange(1, sheetLastColumn + 1, qsValues.length, 1).setValues(qsValues);
sheet.autoResizeColumn(sheetLastColumn + 1);

// Change file name to reflect the new date.
// Find out which dates are currently noted in the file's name.
var matches = /\((.*?)( - (.*))?\)/.exec(reportFile.getName());
if(matches && matches[1]){
if(matches[2]){
// There's a start date and an end date.
var startDate = matches[1];
var endDate = matches[3];
if(endDate != dateString){
var newFileName = reportFile.getName().replace(endDate, dateString);
reportFile.setName(newFileName);
}
}else{
// There's just a start date.
var startDate = matches[1];
if(startDate != dateString){
var newFileName = reportFile.getName().replace(startDate, startDate + " - " + dateString);
reportFile.setName(newFileName);
}
}
}else{
Logger.log("Could not recognize dates in file name " + reportFile.getName() +". File name remains unchanged.");
}
}

// Now take care of the summary file.

// Get the total numbers.
for(var key in qsStats){
for(var i = 1; i <= 10; i++){
qsStats[key][0] += qsStats[key][i];
}
}

// Prepare a new column for the Percentages data sheet.
var newValues = [];
var newValuesNumberFormats = [];

for(var key in qsStats){
newValues.push([dateString]);
newValuesNumberFormats.push(["@STRING@"]);
for(var i = 1; i <= 10; i++){
if(qsStats[key][0]) newValues.push([qsStats[key][i] / qsStats[key][0]]); else newValues.push([0]);
newValuesNumberFormats.push(["0.00%"]);
}
newValues.push([qsStats[key][0]]);
newValuesNumberFormats.push(["0.##"]);
}

var sheetPercentages = summarySpreadsheet.getSheetByName("Percentages");
var sheetAverages = summarySpreadsheet.getSheetByName("Averages");

var lastCol = sheetPercentages.getLastColumn() + 1;
var lastRow = sheetAverages.getLastRow() + 1;

// Add the data to the Percentages sheet.
sheetPercentages.insertColumnAfter(lastCol - 1);
sheetPercentages.getRange(1, lastCol, 72, 1).setNumberFormats(newValuesNumberFormats).setValues(newValues);
sheetPercentages.autoResizeColumn(lastCol);

// Add a new row with formulas to the Averages sheet.
sheetAverages.appendRow([""]);
sheetAverages.getRange(lastRow, 1, 1, 1).setValue(dateString);
sheetAverages.getRange(lastRow, 2, 1, 6).setFormulasR1C1([[
"=SUMPRODUCT(Percentages!R2C1:R11C1; Percentages!R2C" + lastCol + ":R11C" + lastCol + ")",
"=SUMPRODUCT(Percentages!R14C1:R23C1; Percentages!R14C" + lastCol + ":R23C" + lastCol + ")",
"=SUMPRODUCT(Percentages!R26C1:R35C1; Percentages!R26C" + lastCol + ":R35C" + lastCol + ")",
"=SUMPRODUCT(Percentages!R38C1:R47C1; Percentages!R38C" + lastCol + ":R47C" + lastCol + ")",
"=SUMPRODUCT(Percentages!R50C1:R59C1; Percentages!R50C" + lastCol + ":R59C" + lastCol + ")",
"=SUMPRODUCT(Percentages!R62C1:R71C1; Percentages!R62C" + lastCol + ":R71C" + lastCol + ")"
]]);


// The properties for the charts. This is not meant to be reconfigured.
var chartsProperties = {
"Keywords per QS" : {
"type" : "column",
"vCol" : 2,
},
"Average QS" : {
"type" : "line",
"vCol" : 2,
},
"Keywords with Impressions per QS" : {
"type" : "column",
"vCol" : 3,
},
"Average QS for Keywords with Impressions" : {
"type" : "line",
"vCol" : 3,
},
"Impressions per QS" : {
"type" : "column",
"vCol" : 4,
},
"Impression weighted QS" : {
"type" : "line",
"vCol" : 4,
},
"Clicks per QS" : {
"type" : "column",
"vCol" : 5,
},
"Click weighted QS" : {
"type" : "line",
"vCol" : 5,
},
"Conversions per QS" : {
"type" : "column",
"vCol" : 6,
},
"Conversion weighted QS" : {
"type" : "line",
"vCol" : 6,
},
"Conversion value per QS" : {
"type" : "column",
"vCol" : 7,
},
"Conversion value weighted QS" : {
"type" : "line",
"vCol" : 7,
},
};

var row = 1;
var col = 1;
var summarySheets = {
"dataH": sheetPercentages,
"dataV": sheetAverages,
"charts": sheetCharts,
}

// Add charts to the dashboard.
for(var chartName in config['chartsToDisplay']){
// Skip all charts that are not set to be displayed.
if(!config['chartsToDisplay'][chartName]) continue;

addChartToDashboard(chartName, chartsProperties[chartName]['type'], summarySheets, row, col, lastRow, lastCol, chartsProperties[chartName]['vCol'], config['chartsCompareStepsBack']);

// Add the "Average QS" cells.
sheetCharts.setRowHeight(row, 60).setRowHeight(row + 1, 20).setRowHeight(row + 2, 270);
sheetCharts.getRange(row, 2).setValue("Average QS").setFontWeight("bold").setFontSize(24).setBorder(true, true, false, true, null, null);
sheetCharts.getRange(row + 2, 2).setFontWeight("bold").setFontSize(24).setNumberFormat("0.00").setBorder(false, true, false, true, null, null);
sheetCharts.getRange(row + 1, 2, 2, 1).setFormulasR1C1(
[
["=LOWER(Averages!R1C" + chartsProperties[chartName]['vCol'] + ")"], ["=Averages!R" + lastRow + "C" + chartsProperties[chartName]['vCol']]
]).setBorder(false, true, true, true, null, null);
sheetCharts.autoResizeColumn(2);
row += 3;
}
}

/*
* Checks if there is a folder with the given name in the Google Drive root folder. If not, the folder is created.
* The folderName can be in the form of a complete path with subfolders, like "QS Reports/123/whatever".
* Returns the folder.
*/
function getOrCreateFolder(folderName){
return getOrCreateFolderFromArray(folderName.toString().split("/"), DriveApp.getRootFolder());
}

/*
* Does the actual work for getOrCreateFolder. Recursive function, based on an array of folder names (to handle paths with subfolders).
*/
function getOrCreateFolderFromArray(folderNameArray, currentFolder){
var folderName = "";
// Skip empty folders (multiple slashes or a slash at the end).
do folderName = folderNameArray.shift(); while(folderName == "" && folderNameArray.length > 0);

if(folderName == "") return currentFolder;

// See if the folder is already there.
var folderIterator = currentFolder.getFoldersByName(folderName);
if(folderIterator.hasNext()){
var folder = folderIterator.next();
}else{
// Create folder.
Logger.log("Creating folder '" + folderName + "'");
var folder = currentFolder.createFolder(folderName);
}

if(folderNameArray.length > 0) return getOrCreateFolderFromArray(folderNameArray, folder);
return folder;
}

/*
* Checks if there is a folder for the current client account in the base folder. If not, the folder is created.
* Existing client folders are recognized by the client id in parentheses. This way, folders can be found again, even if an account has been renamed.
*/
function getOrCreateClientFolder(baseFolder){
var folderIterator = baseFolder.getFolders();
var regExp = new RegExp(AdWordsApp.currentAccount().getCustomerId());
while(folderIterator.hasNext()){
var folder = folderIterator.next();
if(folder.getName().match(regExp)) return folder;
}
// Since no folder has been found: Create one.
var newFolderName = AdWordsApp.currentAccount().getName() + " (" + AdWordsApp.currentAccount().getCustomerId() + ")";
Logger.log("Creating folder '" + newFolderName + "'");
return baseFolder.createFolder(newFolderName);
}

/*
* Creates a spreadsheet for QS tracking.
* Adds headers to the spreadsheet.
* Returns the file.
*/
function addReportFile(folder, name){
var spreadsheet = SpreadsheetApp.create(name, 1, 4);
var sheet = spreadsheet.getActiveSheet();
sheet.setName("QS history");
// Put in the table headings
sheet.getRange(1, 1, 1, 4).setValues([["Campaign", "AdGroup", "Keyword", "ID string"]]);
//sheet.getRange(1, 1, 1, 4).setFontWeight("bold");
sheet.setColumnWidth(4, 1);
var file = DriveApp.getFileById(spreadsheet.getId());
folder.addFile(file);
var parentFolder = file.getParents().next();
parentFolder.removeFile(file);
return folder.getFilesByName(name).next();
}

/*
* Creates a spreadsheet for the summary and stores it in the folder.
* Creates sheets for the Percentages and Averages.
* Populates header rows and columns.
*/
function addSummaryFile(folder, name){
var spreadsheet = SpreadsheetApp.create(name);
var sheetH = spreadsheet.getActiveSheet();
sheetH.setName("Percentages");

// Add the first column for the horizontal data table.
sheetH.getRange(1, 1, 72, 1).setValues(
[["All keywords"], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], ['Total'],
["Keywords with impressions"], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], ['Total'],
["Impression weighted"], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], ['Total'],
["Click weighted"], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], ['Total'],
["Conversion weighted"], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], ['Total'],
["Conversion value weighted"], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], ['Total']
]
);
sheetH.getRange("A:A").setNumberFormat('@STRING@');
sheetH.autoResizeColumn(1);

var sheetV = spreadsheet.insertSheet("Averages");
// Add the first rows for the vertical data table.
sheetV.getRange(1, 1, 4, 7).setValues([
["Date", "Average", "Average for keywords with impressions", "Impression weighted", "Click weighted", "Conversion weighted", "Value weighted"],
["Highest", "", "", "", "", "", ""],
["Lowest", "", "", "", "", "", ""],
["Average", "", "", "", "", "", ""]
]);
// Add some formulas for maximums, minimums, and averages.
sheetV.getRange(2, 2, 3, 6).setFormulas([
["=MAX(B$5:B)", "=MAX(C$5:C)", "=MAX(D$5:D)", "=MAX(E$5:E)", "=MAX(F$5:F)", "=MAX(G$5:G)"],
["=MIN(B$5:B)", "=MIN(C$5:C)", "=MIN(D$5:D)", "=MIN(E$5:E)", "=MIN(F$5:F)", "=MIN(G$5:G)"],
["=AVERAGE(B$5:B)", "=AVERAGE(C$5:C)", "=AVERAGE(D$5:D)", "=AVERAGE(E$5:E)", "=AVERAGE(F$5:F)", "=AVERAGE(G$5:G)"]
]);
sheetV.getRange(1, 1, 1, 7).setFontWeight("bold").setNumberFormat('@STRING@');
sheetV.autoResizeColumn(1);
sheetV.autoResizeColumn(2);
sheetV.autoResizeColumn(3);
sheetV.autoResizeColumn(4);
sheetV.autoResizeColumn(5);
sheetV.autoResizeColumn(6);
sheetV.autoResizeColumn(7);

// Store the spreadsheet.
var file = DriveApp.getFileById(spreadsheet.getId());
folder.addFile(file);
var parentFolder = file.getParents().next();
parentFolder.removeFile(file);
return folder.getFilesByName(name).next();
}

/*
* Replaces the About sheet in the summary spreadsheet with a fresh one from the master sheet. This way, the sheet (including the FAQ) stays up to date.
* Also replaces the Dashboard with a fresh copy (resulting in an empty sheet with the correct conditional formatting).
* If there's a new version, a sheet "New Version Available!" is added.
*/
function updateInfo(summarySpreadsheet, version){
var templateSpreadsheet = SpreadsheetApp.openByUrl("https://docs.google.com/spread ... 6quot;);

var oldSheet = summarySpreadsheet.getSheetByName("Dashboard");
if(oldSheet) summarySpreadsheet.deleteSheet(oldSheet);
templateSpreadsheet.getSheetByName("Dashboard v" + version).copyTo(summarySpreadsheet).setName("Dashboard");

var oldSheet = summarySpreadsheet.getSheetByName("About + FAQ");
if(oldSheet) summarySpreadsheet.deleteSheet(oldSheet);
templateSpreadsheet.getSheetByName("About v" + version).copyTo(summarySpreadsheet).setName("About + FAQ");

var oldSheet = summarySpreadsheet.getSheetByName("New Version Available!");
if(oldSheet) summarySpreadsheet.deleteSheet(oldSheet);

// Check if there is a newer version.
var versionHistory = templateSpreadsheet.getSheetByName("Version History").getDataRange().getValues();
if(versionHistory[0][0] != version){
// There's a new version available (at least one).
// Look for the row which has the info about the current (old) version.
var oldVersionRow = 1;
while(oldVersionRow < versionHistory.length && versionHistory[oldVersionRow][0] != version){
oldVersionRow++;
}

// Copy the entire version history.
var newVersionSheet = templateSpreadsheet.getSheetByName("Version History").copyTo(summarySpreadsheet).setName("New Version Available!");
// Remove everything about the old version.
newVersionSheet.deleteRows(oldVersionRow + 1, versionHistory.length - oldVersionRow);
// Add new Rows at the beginning.
newVersionSheet.insertRows(1, 6);
newVersionSheet.getRange(1, 1, 6, 2).setValues([["Latest version:", versionHistory[0][0]], ["Your version:", version], ["", ""], ["Get the latest version at", "http://www.ppc-epiphany.com/qstracker/latest"], ["", ""], ["Newer Versions", ""]]);
newVersionSheet.getRange(1, 1, 1, 2).setFontWeight("bold");
newVersionSheet.getRange(6, 1, 1, 1).setFontWeight("bold");
newVersionSheet.autoResizeColumn(1);
newVersionSheet.autoResizeColumn(2);
}
}

/*
* Inserts a line or column chart into the dashboard sheet.
* The chart is based on data from the Percentages or Averages sheet.
*/
function addChartToDashboard(name, type, sheets, row, col, lastRow, lastCol, vCol, compareStepsBack){
var chartBuilder = sheets['charts'].newChart();
chartBuilder
.setOption('title', name)
.setOption('width', 800)
.setOption('height', 349)
.setOption('colors', ['#fa9d1c','#00507d'])
.setPosition(row, col, 0, 0);

switch(type){
case "column":
var statsRow = (vCol - 2) * 12 + 1;
// First range for a column chart is always the same column with QS from 1 to 10.
var dataRanges = [sheets['dataH'].getRange(1, 1, 11, 1)];
if(compareStepsBack && lastCol > 2){
// The column for comparison is either the specified number of columns behind lastCol, or 2 (the first column with data).
dataRanges.push(sheets['dataH'].getRange(statsRow, Math.max(2, lastCol - compareStepsBack), 11, 1));
}
dataRanges.push(sheets['dataH'].getRange(statsRow, lastCol, 11, 1));
chartBuilder = chartBuilder.asColumnChart();
break;
case "line":
var dataRanges = [sheets['dataV'].getRange(5, 1, lastRow - 2, 1), sheets['dataV'].getRange(5, vCol, lastRow - 2, 1)];
chartBuilder = chartBuilder.asLineChart();
chartBuilder.setOption("vAxis.maxValue", 10);
chartBuilder.setOption("vAxis.ticks", [0,2,4,6,8,10]);
chartBuilder.setLegendPosition(Charts.Position.NONE);
break;
}

for(var i in dataRanges) chartBuilder.addRange(dataRanges[i]);
sheets['charts'].insertChart(chartBuilder.build());
}

/*
* Tracks the execution of the script as an event in Google Analytics.
* Sends the version number and a random UUID (basically just a random number, required by Analytics).
* Basically tells that somewhere someone ran the script with a certain version.
* Credit for the idea goes to Russel Savage, who posted his version at http://www.freeadwordsscripts. ... html.
*/
function trackInAnalytics(version){
// Create the random UUID from 30 random hex numbers gets them into the format xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx (with y being 8, 9, a, or b).
var uuid = "";
for(var i = 0; i < 30; i++){
uuid += parseInt(Math.random()*16).toString(16);
}
uuid = uuid.substr(0, 8) + "-" + uuid.substr(8, 4) + "-4" + uuid.substr(12, 3) + "-" + parseInt(Math.random() * 4 + 8).toString(16) + uuid.substr(15, 3) + "-" + uuid.substr(18, 12);

var url = "http://www.google-analytics.co ... ot%3B + uuid + "&ds=adwordsscript&an=qstracker&av="
+ version
+ "&ec=AdWords%20Scripts&ea=Script%20Execution&el=QS%20Tracker%20v" + version;
UrlFetchApp.fetch(url);
}

重新认识CPC

白熊 发表了文章 • 0 个评论 • 115 次浏览 • 2016-12-29 12:12 • 来自相关话题

文章始发孑尊科技公众号,微信扫描二维码关注孑尊科技:

 





 《重新认识CPC》这个标题,是有歧义的。

关注订阅号的朋友,大多数都是专业人士,看到标题点击来的人应该分三种,一种是“不就是CPC嘛,谁不知道啊,每次点击成本,还用单独说么”,另一种是“是不是要讲PPC/CPC 这个岗位的东西?”,最后一种,应该是不确定这个标题到底想讲哪种,点进来先看看的。


那么现在答案揭晓。


我这篇文章,要讲的不是别的,就是Cost per click 或者说 Pay per click。


CPC这个词,其实本身是有很多歧义的,通常来说,如果一个人跟你说CPC,那么可能有下面几层意思:


1."CPC广告",包括了AdWords,Bing等

2."Max. CPC" 账户里面设置的最大CPC出价

3."Actual CPC" 实际点击的CPC

4."Average CPC" 平均每次点击成本


除了第一点,其他都是我们今天要讨论的,CPC这个概念,在所有的CPC广告里面至关重要,甚至可以说是最重要的一个概念都不为过。


而我见过接触过的PPCer,却有很多有意无意去忽略它,无视它,概念不清,甚至完全是错的,不信?那么下面几种场景,你见过几次?
我的Average CPC已经比谷歌建议的低很多了,我觉得可以了我们有冲量的要求,为了冲量,Max.CPC适当提高点一个新Campaign,Max.CPC比谷歌推荐的要出低一点,谷歌那么贵我可出不起马上要到旺季了,CPC要涨价,不过不用急,到时候只用提高Max.CPC就行了


如果你有过以上任意一个想法,那我告诉你,上面的想法,都是错的。

Actual CPC是什么?

AdWords中,ActualCPC的计算公式如下:

公式1:







上面的公式,非常重要,老司机也跟我们一起复习一下,中文是:

你的实际CPC=竞争对手的广告评级/你的质量得分+0.01

我们再看下面这张图:







看完上面的图,相信不难发现,这个里面有个很关键的数值,AdRank,AdRank 怎么算?


公式2:

AdRank=Max Bid*Quality Score

两个公式合在一起,就是谷歌CPC广告的精髓了,竟然如此简单。

要说世界上最简单的广告系统,恐怕就是谷歌广告系统了。

好了,我理解了上面的公式,那么,上面的公式,我们可以得出什么结论?

这就是我接下来要讲的,就是结论:
尽可能提高QS,参考公式一,分子越大,数字越小你的出价(Max.bid/Max.CPC)并不能直接决定你的ActualCPC,中间是有一个QS在里面的,因此前面提到的场景,都是错的

上面的公式还能得出一个结论,老司机可能已经想到了,这里先卖个关子,我们下期再讲。


下期预告:ActualCPC 可以做到极低,你知道方法么?

在线广告成长社区年底招募成员,点击下面获得详细信息:

成长社群

(如链接失效,请关注后再后台回复“成长”获取) 查看全部


文章始发孑尊科技公众号,微信扫描二维码关注孑尊科技:


 
qrcode_for_gh_f615d3f6af4a_430.jpg


 《重新认识CPC》这个标题,是有歧义的。

关注订阅号的朋友,大多数都是专业人士,看到标题点击来的人应该分三种,一种是“不就是CPC嘛,谁不知道啊,每次点击成本,还用单独说么”,另一种是“是不是要讲PPC/CPC 这个岗位的东西?”,最后一种,应该是不确定这个标题到底想讲哪种,点进来先看看的。


那么现在答案揭晓。


我这篇文章,要讲的不是别的,就是Cost per click 或者说 Pay per click。


CPC这个词,其实本身是有很多歧义的,通常来说,如果一个人跟你说CPC,那么可能有下面几层意思:


1."CPC广告",包括了AdWords,Bing等

2."Max. CPC" 账户里面设置的最大CPC出价

3."Actual CPC" 实际点击的CPC

4."Average CPC" 平均每次点击成本


除了第一点,其他都是我们今天要讨论的,CPC这个概念,在所有的CPC广告里面至关重要,甚至可以说是最重要的一个概念都不为过。


而我见过接触过的PPCer,却有很多有意无意去忽略它,无视它,概念不清,甚至完全是错的,不信?那么下面几种场景,你见过几次?
  1. 我的Average CPC已经比谷歌建议的低很多了,我觉得可以了
  2. 我们有冲量的要求,为了冲量,Max.CPC适当提高点
  3. 一个新Campaign,Max.CPC比谷歌推荐的要出低一点,谷歌那么贵我可出不起
  4. 马上要到旺季了,CPC要涨价,不过不用急,到时候只用提高Max.CPC就行了



如果你有过以上任意一个想法,那我告诉你,上面的想法,都是错的。

Actual CPC是什么?

AdWords中,ActualCPC的计算公式如下:

公式1:
cost-per-click-formula.gif




上面的公式,非常重要,老司机也跟我们一起复习一下,中文是:

你的实际CPC=竞争对手的广告评级/你的质量得分+0.01

我们再看下面这张图:

actual-cpc-example.jpg



看完上面的图,相信不难发现,这个里面有个很关键的数值,AdRank,AdRank 怎么算?


公式2:

AdRank=Max Bid*Quality Score

两个公式合在一起,就是谷歌CPC广告的精髓了,竟然如此简单。

要说世界上最简单的广告系统,恐怕就是谷歌广告系统了。

好了,我理解了上面的公式,那么,上面的公式,我们可以得出什么结论?

这就是我接下来要讲的,就是结论:
  1. 尽可能提高QS,参考公式一,分子越大,数字越小
  2. 你的出价(Max.bid/Max.CPC)并不能直接决定你的ActualCPC,中间是有一个QS在里面的,因此前面提到的场景,都是错的


上面的公式还能得出一个结论,老司机可能已经想到了,这里先卖个关子,我们下期再讲。


下期预告:ActualCPC 可以做到极低,你知道方法么?

在线广告成长社区年底招募成员,点击下面获得详细信息:

成长社群

(如链接失效,请关注后再后台回复“成长”获取)

怎么提高质量得分?

白熊 回复了问题 • 3 人关注 • 1 个回复 • 124 次浏览 • 2016-12-28 17:16 • 来自相关话题

对再营销列表 怎么操作呢 提高出价?针对再营销列表 单独写广告?

白熊 回复了问题 • 2 人关注 • 1 个回复 • 138 次浏览 • 2016-12-28 09:51 • 来自相关话题

关于APP投放电池应用类产品的banner

Mark 回复了问题 • 2 人关注 • 2 个回复 • 126 次浏览 • 2016-12-26 09:55 • 来自相关话题

充电宝批发和定制适合做什么类型的广告,上传什么关键词?

Sarali 回复了问题 • 3 人关注 • 2 个回复 • 187 次浏览 • 2016-12-12 16:26 • 来自相关话题