PPC Scripts

by Lynt services

You are here: Home / Adwords scripts / New columns in AWQL reports based on Adwords API v201601

New columns in AWQL reports based on Adwords API v201601

od 5 Comments

At the end of January Google released a new version of Adwords API, which brings, besides API changes, new columns (metrics) to the Keywords Performance report and Criteria Performance report. If these columns are in API reports, we can access them through Adwords scripts.

Let’s take a look at them:

  1. FirstPositionCpc – Estimate of the amount you might need to pay for your ad to be displayed in the first position
  2. EstimatedAddClicksAtFirstPositionCpc – Estimate of how many clicks per week you might get by changing your keyword bid to the value in FirstPositionCpc.
  3. EstimatedAddCostAtFirstPositionCpc – Estimate of how your cost per week might change when changing your keyword bid to the value in FirstPositionCpc.
  4. CreativeQualityScore – The quality score of the ad.
    1. Not applicable.
    2. Below average.
    3. Average.
    4. Above average.
  5. PostClickQualityScore – The quality score of the landing page.
    1. Not applicable.
    2. Below average.
    3. Average.
    4. Above average.
  6. SearchPredictedCtr – Clickthrough rate compared to that of other advertisers.
    1. Not applicable.
    2. Below average.
    3. Average.
    4. Above average.

What you can do with these new columns?

  • Automatically find  keywords with bad CTR compared to the other advertisers.
  • Quickly find keywords with bad landing page.
  • Find keywords with bad ad based on Google algorithm.
  • Know how many clicks you can gain for the keyword on top position. That’s great for getting maximum juice from keyword.
  • And more.

I prepared script for you, which will automatically download keywords report with new columns available in Adwords scripts.

Source code of the script

Report klíčových slov s novými metrikami
JavaScript
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
//SETTINGS - Just paste new spreadsheet URL bellow//
var spreadsheet = SpreadsheetApp.openByUrl('');
function main(){
spreadsheet.setSpreadsheetLocale('en');
var sheetSettings = createOrGetSheet(spreadsheet, 'Settings', 0);
var settingRange = sheetSettings.getRange(1,1,1,2);
 
if(sheetSettings.getRange(1,2).isBlank()) {
  settingRange.setValues([['How many days back you want to report data?',90]]);
  sheetSettings.setColumnWidth(1, 300);
}
var daysBack = sheetSettings.getRange(1,2).getValue();
var sheet = createOrGetSheet(spreadsheet, 'KW report', 1);
var to = lynt_get_date(1);
var from = lynt_get_date(daysBack);
var dateRange = from + ',' + to;
var report = AdWordsApp.report(
  " SELECT Criteria,AdGroupName, CampaignName, Impressions, Clicks, Cost,AveragePosition, QualityScore, AverageCpc, AllConversionValue, CostPerAllConversion, AllConversions, AveragePosition, SearchImpressionShare, FirstPageCpc, TopOfPageCpc, CreativeQualityScore, SearchPredictedCtr, PostClickQualityScore, EstimatedAddCostAtFirstPositionCpc, EstimatedAddClicksAtFirstPositionCpc, FirstPositionCpc" +
  " FROM KEYWORDS_PERFORMANCE_REPORT"+
  " WHERE Impressions > 10"+
  " AND AdGroupStatus = ENABLED" +
  " AND CampaignStatus = ENABLED" +
  " AND Status = ENABLED" +
  " DURING " + dateRange);
  
report.exportToSheet(sheet);
}
function lynt_DST(datum, offset){
  var yr = datum.getFullYear();
  var dst_start = new Date("March 14, " + yr +" 02:00:00");
  var dst_end = new Date("November 07, " + yr +" 02:00:00");
  var day = dst_start.getDay();
  dst_start.setDate(14-day);
  day = dst_end.getDay();
  dst_end.setDate(7-day);
  if (datum >= dst_start && datum < dst_end){
    return offset+1;
  } else {
  return offset;
  }
}
//Creating or returning selected sheet
function createOrGetSheet(reportSpreadsheet, name, position) {
  var sheet = reportSpreadsheet.getSheetByName(name);
  if(sheet == null) {
    return reportSpreadsheet.insertSheet(name,position);
  } else {
    return sheet;
  }
}
//Getting data for x days back in right format for AWQL query
function lynt_get_date(num_of_days) {
  var before = new Date();
  //kdyz je letni cas, tak o hodinu dele (GMT+1)
  var offset = lynt_DST(before,1);
  before.setTime(before.getTime() - (1000 * 60 * 60 * (24) * num_of_days) + offset);
  return lynt_format_awql_date(before);
}
//Getting always last sunday with right formats for AWQL
function lynt_get_sunday() {
  var before = new Date();
  //kdyz je letni cas, tak o hodinu dele (GMT+1)
  var offset = lynt_DST(before,1);
  before.setTime(before.getTime() - (1000 * 60 * 60 * (24) * (before.getDay())) + offset);
  return lynt_format_awql_date(before);
}
//Getting x days from last sunday right formated for AWQL
function lynt_get_xdays_back_from_sunday(num_of_days) {
  var before = new Date();
  //kdyz je letni cas, tak o hodinu dele (GMT+1)
  var offset = lynt_DST(before,1);
  before.setTime(before.getTime() - (1000 * 60 * 60 * (24) * num_of_days) - (1000 * 60 * 60 * 24 * (before.getDay()))+ offset);
  return lynt_format_awql_date(before);
}
//Format date for AWQL
function lynt_format_awql_date(date){
  return date.getUTCFullYear()+("0"+(date.getUTCMonth()+1)).slice(-2)+("0"+date.getUTCDate()).slice(-2);
}

Reader Interactions

Comments

  3. Yong says

    Hello there! Would you mind if I share your blog with my facebook group? There’s a lot of folks that I think would really appreciate your content. Please let me know. Many thanks

    Reply

  4. movie download sites says

    I personally came right here from a different web page on free hd movies online and thought I might check out this page. I like the things I see thus now I am following you. Looking forward to exploring the blog all over again.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *