{"id":288,"date":"2016-02-18T21:42:43","date_gmt":"2016-02-18T20:42:43","guid":{"rendered":"https:\/\/ppc-scripts.eu\/en\/?p=288"},"modified":"2016-02-18T21:44:32","modified_gmt":"2016-02-18T20:44:32","slug":"new-columns-in-awql","status":"publish","type":"post","link":"https:\/\/ppc-scripts.eu\/en\/new-columns-in-awql\/","title":{"rendered":"New columns in AWQL reports based on Adwords API v201601"},"content":{"rendered":"<p>At the end of January Google <a href=\"https:\/\/developers.google.com\/adwords\/api\/docs\/reference\/#v201601\">released a new version of Adwords API<\/a>, 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.<\/p>\n<p><strong>Let\u2019s take a look at them:<\/strong><\/p>\n<ol style=\"list-style-type: decimal;\">\n<li><strong>FirstPositionCpc<\/strong> \u2013 Estimate of the amount you might need to pay for your ad to be displayed in the first position<\/li>\n<li><strong>EstimatedAddClicksAtFirstPositionCpc<\/strong> \u2013 Estimate of how many clicks per week you might get by changing your keyword bid to the value in FirstPositionCpc.<\/li>\n<li><strong>EstimatedAddCostAtFirstPositionCpc<\/strong> \u2013 Estimate of how your cost per week might change when changing your keyword bid to the value in FirstPositionCpc.<\/li>\n<li><strong>CreativeQualityScore<\/strong> \u2013 The quality score of the ad.\n<ol style=\"list-style-type: lower-alpha;\">\n<li>Not applicable.<\/li>\n<li>Below average.<\/li>\n<li>Average.<\/li>\n<li>Above average.<\/li>\n<\/ol>\n<\/li>\n<li><strong>PostClickQualityScore<\/strong> \u2013 The quality score of the landing page.\n<ol style=\"list-style-type: lower-alpha;\">\n<li>Not applicable.<\/li>\n<li>Below average.<\/li>\n<li>Average.<\/li>\n<li>Above average.<\/li>\n<\/ol>\n<\/li>\n<li><strong>SearchPredictedCtr<\/strong> \u2013 Clickthrough rate compared to that of other advertisers.\n<ol style=\"list-style-type: lower-alpha;\">\n<li>Not applicable.<\/li>\n<li>Below average.<\/li>\n<li>Average.<\/li>\n<li>Above average.<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<p><!--more--><\/p>\n<h2>What you can do with these new columns?<\/h2>\n<ul style=\"list-style-type: disc;\">\n<li>Automatically find \u00a0keywords with bad CTR compared to the other advertisers.<\/li>\n<li>Quickly find keywords with bad landing page.<\/li>\n<li>Find keywords with bad ad based on Google algorithm.<\/li>\n<li>Know how many clicks you can gain for the keyword on top position. That\u2019s great for getting maximum juice from keyword.<\/li>\n<li>And more.<\/li>\n<\/ul>\n<p>I prepared script for you, which will automatically download keywords report with new columns available in Adwords scripts.<\/p>\n<h2>Source code of the script<\/h2>\n<pre class=\"wrap:false minimize:false lang:js decode:true \" title=\"Report kl\u00ed\u010dov\u00fdch slov s nov\u00fdmi metrikami\">\/\/SETTINGS - Just paste new spreadsheet URL bellow\/\/\r\nvar spreadsheet = SpreadsheetApp.openByUrl('');\r\n \r\nfunction main(){\r\n \r\nspreadsheet.setSpreadsheetLocale('en');\r\nvar sheetSettings = createOrGetSheet(spreadsheet, 'Settings', 0);\r\nvar settingRange = sheetSettings.getRange(1,1,1,2);\r\n\r\nif(sheetSettings.getRange(1,2).isBlank()) {\r\n  settingRange.setValues([['How many days back you want to report data?',90]]);\r\n  sheetSettings.setColumnWidth(1, 300);\r\n}\r\nvar daysBack = sheetSettings.getRange(1,2).getValue();\r\nvar sheet = createOrGetSheet(spreadsheet, 'KW report', 1);\r\n \r\nvar to = lynt_get_date(1);\r\nvar from = lynt_get_date(daysBack);\r\nvar dateRange = from + ',' + to;\r\n \r\n \r\nvar report = AdWordsApp.report(\r\n  \" SELECT Criteria,AdGroupName, CampaignName, Impressions, Clicks, Cost,AveragePosition, QualityScore, AverageCpc, AllConversionValue, CostPerAllConversion, AllConversions, AveragePosition, SearchImpressionShare, FirstPageCpc, TopOfPageCpc, CreativeQualityScore, SearchPredictedCtr, PostClickQualityScore, EstimatedAddCostAtFirstPositionCpc, EstimatedAddClicksAtFirstPositionCpc, FirstPositionCpc\" + \r\n  \" FROM KEYWORDS_PERFORMANCE_REPORT\"+\r\n  \" WHERE Impressions &gt; 10\"+\r\n  \" AND AdGroupStatus = ENABLED\" + \r\n  \" AND CampaignStatus = ENABLED\" + \r\n  \" AND Status = ENABLED\" +\r\n  \" DURING \" + dateRange);\r\n  \r\nreport.exportToSheet(sheet);\r\n}\r\n \r\n \r\nfunction lynt_DST(datum, offset){\r\n  var yr = datum.getFullYear();\r\n  var dst_start = new Date(\"March 14, \" + yr +\" 02:00:00\");\r\n  var dst_end = new Date(\"November 07, \" + yr +\" 02:00:00\");\r\n  var day = dst_start.getDay();\r\n  dst_start.setDate(14-day);\r\n  day = dst_end.getDay();\r\n  dst_end.setDate(7-day);\r\n  if (datum &gt;= dst_start &amp;&amp; datum &lt; dst_end){\r\n    return offset+1;\r\n  } else {\r\n  return offset;\r\n  }\r\n}\r\n \r\n\/\/Creating or returning selected sheet \r\nfunction createOrGetSheet(reportSpreadsheet, name, position) {\r\n  var sheet = reportSpreadsheet.getSheetByName(name);\r\n  if(sheet == null) {\r\n    return reportSpreadsheet.insertSheet(name,position);\r\n  } else {\r\n    return sheet;\r\n  }\r\n}\r\n \r\n\/\/Getting data for x days back in right format for AWQL query\r\nfunction lynt_get_date(num_of_days) {\r\n  var before = new Date(); \r\n  \/\/kdyz je letni cas, tak o hodinu dele (GMT+1)\r\n  var offset = lynt_DST(before,1);\r\n  before.setTime(before.getTime() - (1000 * 60 * 60 * (24) * num_of_days) + offset);\r\n  return lynt_format_awql_date(before);\r\n}\r\n \r\n\/\/Getting always last sunday with right formats for AWQL\r\nfunction lynt_get_sunday() {\r\n  var before = new Date();\r\n  \/\/kdyz je letni cas, tak o hodinu dele (GMT+1)\r\n  var offset = lynt_DST(before,1);\r\n  before.setTime(before.getTime() - (1000 * 60 * 60 * (24) * (before.getDay())) + offset);\r\n  return lynt_format_awql_date(before);\r\n}\r\n \r\n\/\/Getting x days from last sunday right formated for AWQL\r\nfunction lynt_get_xdays_back_from_sunday(num_of_days) {\r\n  var before = new Date();\r\n  \/\/kdyz je letni cas, tak o hodinu dele (GMT+1)\r\n  var offset = lynt_DST(before,1);\r\n  before.setTime(before.getTime() - (1000 * 60 * 60 * (24) * num_of_days) - (1000 * 60 * 60 * 24 * (before.getDay()))+ offset);\r\n  return lynt_format_awql_date(before);\r\n}\r\n \r\n\/\/Format date for AWQL\r\nfunction lynt_format_awql_date(date){\r\n  return date.getUTCFullYear()+(\"0\"+(date.getUTCMonth()+1)).slice(-2)+(\"0\"+date.getUTCDate()).slice(-2);\r\n}\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>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\u2019s take a look at them: FirstPositionCpc \u2013 Estimate of the [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_genesis_hide_title":false,"_genesis_hide_breadcrumbs":false,"_genesis_hide_singular_image":false,"_genesis_hide_footer_widgets":false,"_genesis_custom_body_class":"","_genesis_custom_post_class":"","_genesis_layout":""},"categories":[11],"tags":[10],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v17.0 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>New columns in AWQL reports based on Adwords API v201601 - PPC Scripts<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/ppc-scripts.eu\/en\/new-columns-in-awql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"New columns in AWQL reports based on Adwords API v201601 - PPC Scripts\" \/>\n<meta property=\"og:description\" content=\"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\u2019s take a look at them: FirstPositionCpc \u2013 Estimate of the [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/ppc-scripts.eu\/en\/new-columns-in-awql\/\" \/>\n<meta property=\"og:site_name\" content=\"PPC Scripts\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/LyntServices\" \/>\n<meta property=\"article:published_time\" content=\"2016-02-18T20:42:43+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2016-02-18T20:44:32+00:00\" \/>\n<meta name=\"twitter:card\" content=\"summary\" \/>\n<meta name=\"twitter:creator\" content=\"@JKasparu\" \/>\n<meta name=\"twitter:site\" content=\"@JKasparu\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Jakub Ka\u0161par\u016f\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebSite\",\"@id\":\"https:\/\/ppc-scripts.eu\/#website\",\"url\":\"https:\/\/ppc-scripts.eu\/\",\"name\":\"PPC Scripts\",\"description\":\"by Lynt services\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/ppc-scripts.eu\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/ppc-scripts.eu\/en\/new-columns-in-awql\/#webpage\",\"url\":\"https:\/\/ppc-scripts.eu\/en\/new-columns-in-awql\/\",\"name\":\"New columns in AWQL reports based on Adwords API v201601 - PPC Scripts\",\"isPartOf\":{\"@id\":\"https:\/\/ppc-scripts.eu\/#website\"},\"datePublished\":\"2016-02-18T20:42:43+00:00\",\"dateModified\":\"2016-02-18T20:44:32+00:00\",\"author\":{\"@id\":\"https:\/\/ppc-scripts.eu\/#\/schema\/person\/9ea7ad2ac6d3dbecb6e5510875f97e1d\"},\"breadcrumb\":{\"@id\":\"https:\/\/ppc-scripts.eu\/en\/new-columns-in-awql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/ppc-scripts.eu\/en\/new-columns-in-awql\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/ppc-scripts.eu\/en\/new-columns-in-awql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/ppc-scripts.eu\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"New columns in AWQL reports based on Adwords API v201601\"}]},{\"@type\":\"Person\",\"@id\":\"https:\/\/ppc-scripts.eu\/#\/schema\/person\/9ea7ad2ac6d3dbecb6e5510875f97e1d\",\"name\":\"Jakub Ka\\u0161par\\u016f\",\"image\":{\"@type\":\"ImageObject\",\"@id\":\"https:\/\/ppc-scripts.eu\/#personlogo\",\"inLanguage\":\"en-US\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/36e28826d2e1787626a73ae42c796dca?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/36e28826d2e1787626a73ae42c796dca?s=96&d=mm&r=g\",\"caption\":\"Jakub Ka\\u0161par\\u016f\"},\"description\":\"\\u010c\\u00edm v\\u00edc dat v Excelu m\\u00e1\\u0161, tolikr\\u00e1t si Linkbuilder\",\"sameAs\":[\"http:\/\/lynt.cz\",\"https:\/\/twitter.com\/JKasparu\"],\"url\":\"https:\/\/ppc-scripts.eu\/en\/author\/jakub\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"New columns in AWQL reports based on Adwords API v201601 - PPC Scripts","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/ppc-scripts.eu\/en\/new-columns-in-awql\/","og_locale":"en_US","og_type":"article","og_title":"New columns in AWQL reports based on Adwords API v201601 - PPC Scripts","og_description":"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\u2019s take a look at them: FirstPositionCpc \u2013 Estimate of the [&hellip;]","og_url":"https:\/\/ppc-scripts.eu\/en\/new-columns-in-awql\/","og_site_name":"PPC Scripts","article_publisher":"https:\/\/www.facebook.com\/LyntServices","article_published_time":"2016-02-18T20:42:43+00:00","article_modified_time":"2016-02-18T20:44:32+00:00","twitter_card":"summary","twitter_creator":"@JKasparu","twitter_site":"@JKasparu","twitter_misc":{"Written by":"Jakub Ka\u0161par\u016f","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebSite","@id":"https:\/\/ppc-scripts.eu\/#website","url":"https:\/\/ppc-scripts.eu\/","name":"PPC Scripts","description":"by Lynt services","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/ppc-scripts.eu\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/ppc-scripts.eu\/en\/new-columns-in-awql\/#webpage","url":"https:\/\/ppc-scripts.eu\/en\/new-columns-in-awql\/","name":"New columns in AWQL reports based on Adwords API v201601 - PPC Scripts","isPartOf":{"@id":"https:\/\/ppc-scripts.eu\/#website"},"datePublished":"2016-02-18T20:42:43+00:00","dateModified":"2016-02-18T20:44:32+00:00","author":{"@id":"https:\/\/ppc-scripts.eu\/#\/schema\/person\/9ea7ad2ac6d3dbecb6e5510875f97e1d"},"breadcrumb":{"@id":"https:\/\/ppc-scripts.eu\/en\/new-columns-in-awql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/ppc-scripts.eu\/en\/new-columns-in-awql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/ppc-scripts.eu\/en\/new-columns-in-awql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/ppc-scripts.eu\/"},{"@type":"ListItem","position":2,"name":"New columns in AWQL reports based on Adwords API v201601"}]},{"@type":"Person","@id":"https:\/\/ppc-scripts.eu\/#\/schema\/person\/9ea7ad2ac6d3dbecb6e5510875f97e1d","name":"Jakub Ka\u0161par\u016f","image":{"@type":"ImageObject","@id":"https:\/\/ppc-scripts.eu\/#personlogo","inLanguage":"en-US","url":"https:\/\/secure.gravatar.com\/avatar\/36e28826d2e1787626a73ae42c796dca?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/36e28826d2e1787626a73ae42c796dca?s=96&d=mm&r=g","caption":"Jakub Ka\u0161par\u016f"},"description":"\u010c\u00edm v\u00edc dat v Excelu m\u00e1\u0161, tolikr\u00e1t si Linkbuilder","sameAs":["http:\/\/lynt.cz","https:\/\/twitter.com\/JKasparu"],"url":"https:\/\/ppc-scripts.eu\/en\/author\/jakub\/"}]}},"_links":{"self":[{"href":"https:\/\/ppc-scripts.eu\/en\/wp-json\/wp\/v2\/posts\/288"}],"collection":[{"href":"https:\/\/ppc-scripts.eu\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ppc-scripts.eu\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ppc-scripts.eu\/en\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/ppc-scripts.eu\/en\/wp-json\/wp\/v2\/comments?post=288"}],"version-history":[{"count":3,"href":"https:\/\/ppc-scripts.eu\/en\/wp-json\/wp\/v2\/posts\/288\/revisions"}],"predecessor-version":[{"id":291,"href":"https:\/\/ppc-scripts.eu\/en\/wp-json\/wp\/v2\/posts\/288\/revisions\/291"}],"wp:attachment":[{"href":"https:\/\/ppc-scripts.eu\/en\/wp-json\/wp\/v2\/media?parent=288"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ppc-scripts.eu\/en\/wp-json\/wp\/v2\/categories?post=288"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ppc-scripts.eu\/en\/wp-json\/wp\/v2\/tags?post=288"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}