{"id":391,"date":"2017-02-15T22:50:50","date_gmt":"2017-02-15T21:50:50","guid":{"rendered":"https:\/\/ppc-scripts.eu\/?p=391\/"},"modified":"2017-02-17T18:13:17","modified_gmt":"2017-02-17T17:13:17","slug":"application-of-awql-in-real-reports","status":"publish","type":"post","link":"https:\/\/ppc-scripts.eu\/en\/application-of-awql-in-real-reports\/","title":{"rendered":"Application of AWQL in real reports"},"content":{"rendered":"

In this article I would like to show you how you can use the AWQL generator we developed in our company.<\/p>\n

First, you will need to get an AWQL report. You can use our generator, which you can find here<\/a> or, if you prefer, you can copy our template below which already has the AWQL report combined with the script and some additional functions:<\/p>\n

\"\" <\/a><\/p>\n

\"\"<\/a><\/p>\n

\"\"<\/a><\/p>\n

\"\"<\/a><\/p>\n

<\/p>\n

    \n
  1. Duplicate our template<\/a> and insert the URL of your new spreadsheet on line 2 of the script below.<\/li>\n
  2. Then insert the script into your account.<\/li>\n
  3. Launch the script.<\/li>\n
  4. Look at the sheets for insight into your accounts.<\/li>\n<\/ol>\n
    \/\/SETTINGS - Spreadsheet URL\/\/\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\nif(sheetSettings.getRange(1,2).isBlank()) {\r\n  settingRange.setValues([['How many days back for the report',90]]);\r\n}\r\nvar daysBack = sheetSettings.getRange(1,2).getValue();\r\nvar sheet = createOrGetSheet(spreadsheet, 'KW report', 1);\r\nvar sheet2 = createOrGetSheet(spreadsheet, 'Terms report', 2);\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, CampaignName, AdGroupName,Impressions,Clicks,AverageCpc,Ctr,Cost,AllConversions,CostPerAllConversion,AllConversionValue,QualityScore,CpcBid,FirstPageCpc,TopOfPageCpc,AveragePosition,CreativeQualityScore,SearchPredictedCtr, PostClickQualityScore,EstimatedAddCostAtFirstPositionCpc,EstimatedAddClicksAtFirstPositionCpc,FirstPositionCpc,KeywordMatchType, SearchImpressionShare, SearchRankLostImpressionShare \" + \r\n  \" FROM KEYWORDS_PERFORMANCE_REPORT\"+\r\n  \" WHERE Impressions > 10\"+\r\n  \" AND AdGroupStatus = ENABLED\" + \r\n  \" AND CampaignStatus = ENABLED\" + \r\n  \" AND Status = ENABLED\" +\r\n  \" DURING \" + dateRange);\r\n \r\nvar report2 = AdWordsApp.report(\r\n  \" SELECT Query,KeywordTextMatchingQuery,CampaignName,AdGroupName,Impressions,Clicks,AverageCpc,Ctr,Cost,AllConversions,CostPerAllConversion,AllConversionValue \" + \r\n  \" FROM SEARCH_QUERY_PERFORMANCE_REPORT\"+\r\n  \" WHERE Impressions > 0\"+\r\n  \" AND AdGroupStatus = ENABLED\" + \r\n  \" AND CampaignStatus = ENABLED\" +\r\n  \" AND Impressions > 20\" +\r\n  \" DURING \" + dateRange);\r\n  \r\nreport.exportToSheet(sheet);\r\nreport2.exportToSheet(sheet2);\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 >= dst_start && datum < 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(pocet_dni) {\r\n  var minule = new Date(); \r\n  \/\/kdyz je letni cas, tak o hodinu dele (GMT+1)\r\n  var offset = lynt_DST(minule,1);\r\n  minule.setTime(minule.getTime() - (1000 * 60 * 60 * (24) * pocet_dni) + offset);\r\n  return lynt_format_awql_date(minule);\r\n}\r\n \r\n\/\/Getting always last sunday with right formats for AWQL\r\nfunction lynt_get_sunday() {\r\n  var minule = new Date();\r\n  \/\/kdyz je letni cas, tak o hodinu dele (GMT+1)\r\n  var offset = lynt_DST(minule,1);\r\n  minule.setTime(minule.getTime() - (1000 * 60 * 60 * (24) * (minule.getDay())) + offset);\r\n  return lynt_format_awql_date(minule);\r\n}\r\n \r\n\/\/Getting x days from last sunday right formated for AWQL\r\nfunction lynt_get_xdays_back_from_sunday(pocet_dni) {\r\n  var minule = new Date();\r\n  \/\/kdyz je letni cas, tak o hodinu dele (GMT+1)\r\n  var offset = lynt_DST(minule,1);\r\n  minule.setTime(minule.getTime() - (1000 * 60 * 60 * (24) * pocet_dni) - (1000 * 60 * 60 * 24 * (minule.getDay()))+ offset);\r\n  return lynt_format_awql_date(minule);\r\n}\r\n \r\n\/\/Format date for AWQL\r\nfunction lynt_format_awql_date(datum){\r\n  return datum.getUTCFullYear()+(\"0\"+(datum.getUTCMonth()+1)).slice(-2)+(\"0\"+datum.getUTCDate()).slice(-2);\r\n}\r\n<\/pre>\n
     <\/p>\n","protected":false},"excerpt":{"rendered":"
    In this article I would like to show you how you can use the AWQL generator we developed in our company. First, you will need to get an AWQL report. You can use our generator, which you can find here or, if you prefer, you can copy our template below which already has the AWQL […]<\/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":[1],"tags":[],"yoast_head":"\nApplication of AWQL in real reports - 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\/application-of-awql-in-real-reports\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Application of AWQL in real reports - PPC Scripts\" \/>\n<meta property=\"og:description\" content=\"In this article I would like to show you how you can use the AWQL generator we developed in our company. First, you will need to get an AWQL report. You can use our generator, which you can find here or, if you prefer, you can copy our template below which already has the AWQL […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/ppc-scripts.eu\/en\/application-of-awql-in-real-reports\/\" \/>\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=\"2017-02-15T21:50:50+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-02-17T17:13:17+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/ppc-scripts.eu\/wp-content\/uploads\/2017\/02\/Screenshot-2017-02-14-23.37.33-300x174.png\" \/>\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\":\"ImageObject\",\"@id\":\"https:\/\/ppc-scripts.eu\/en\/application-of-awql-in-real-reports\/#primaryimage\",\"inLanguage\":\"en-US\",\"url\":\"https:\/\/ppc-scripts.eu\/wp-content\/uploads\/2017\/02\/Screenshot-2017-02-14-23.37.33.png\",\"contentUrl\":\"https:\/\/ppc-scripts.eu\/wp-content\/uploads\/2017\/02\/Screenshot-2017-02-14-23.37.33.png\",\"width\":2590,\"height\":1500},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/ppc-scripts.eu\/en\/application-of-awql-in-real-reports\/#webpage\",\"url\":\"https:\/\/ppc-scripts.eu\/en\/application-of-awql-in-real-reports\/\",\"name\":\"Application of AWQL in real reports - PPC Scripts\",\"isPartOf\":{\"@id\":\"https:\/\/ppc-scripts.eu\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/ppc-scripts.eu\/en\/application-of-awql-in-real-reports\/#primaryimage\"},\"datePublished\":\"2017-02-15T21:50:50+00:00\",\"dateModified\":\"2017-02-17T17:13:17+00:00\",\"author\":{\"@id\":\"https:\/\/ppc-scripts.eu\/#\/schema\/person\/9ea7ad2ac6d3dbecb6e5510875f97e1d\"},\"breadcrumb\":{\"@id\":\"https:\/\/ppc-scripts.eu\/en\/application-of-awql-in-real-reports\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/ppc-scripts.eu\/en\/application-of-awql-in-real-reports\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/ppc-scripts.eu\/en\/application-of-awql-in-real-reports\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/ppc-scripts.eu\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Application of AWQL in real reports\"}]},{\"@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":"Application of AWQL in real reports - 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\/application-of-awql-in-real-reports\/","og_locale":"en_US","og_type":"article","og_title":"Application of AWQL in real reports - PPC Scripts","og_description":"In this article I would like to show you how you can use the AWQL generator we developed in our company. First, you will need to get an AWQL report. You can use our generator, which you can find here or, if you prefer, you can copy our template below which already has the AWQL […]","og_url":"https:\/\/ppc-scripts.eu\/en\/application-of-awql-in-real-reports\/","og_site_name":"PPC Scripts","article_publisher":"https:\/\/www.facebook.com\/LyntServices","article_published_time":"2017-02-15T21:50:50+00:00","article_modified_time":"2017-02-17T17:13:17+00:00","og_image":[{"url":"https:\/\/ppc-scripts.eu\/wp-content\/uploads\/2017\/02\/Screenshot-2017-02-14-23.37.33-300x174.png"}],"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":"ImageObject","@id":"https:\/\/ppc-scripts.eu\/en\/application-of-awql-in-real-reports\/#primaryimage","inLanguage":"en-US","url":"https:\/\/ppc-scripts.eu\/wp-content\/uploads\/2017\/02\/Screenshot-2017-02-14-23.37.33.png","contentUrl":"https:\/\/ppc-scripts.eu\/wp-content\/uploads\/2017\/02\/Screenshot-2017-02-14-23.37.33.png","width":2590,"height":1500},{"@type":"WebPage","@id":"https:\/\/ppc-scripts.eu\/en\/application-of-awql-in-real-reports\/#webpage","url":"https:\/\/ppc-scripts.eu\/en\/application-of-awql-in-real-reports\/","name":"Application of AWQL in real reports - PPC Scripts","isPartOf":{"@id":"https:\/\/ppc-scripts.eu\/#website"},"primaryImageOfPage":{"@id":"https:\/\/ppc-scripts.eu\/en\/application-of-awql-in-real-reports\/#primaryimage"},"datePublished":"2017-02-15T21:50:50+00:00","dateModified":"2017-02-17T17:13:17+00:00","author":{"@id":"https:\/\/ppc-scripts.eu\/#\/schema\/person\/9ea7ad2ac6d3dbecb6e5510875f97e1d"},"breadcrumb":{"@id":"https:\/\/ppc-scripts.eu\/en\/application-of-awql-in-real-reports\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/ppc-scripts.eu\/en\/application-of-awql-in-real-reports\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/ppc-scripts.eu\/en\/application-of-awql-in-real-reports\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/ppc-scripts.eu\/"},{"@type":"ListItem","position":2,"name":"Application of AWQL in real reports"}]},{"@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\/391"}],"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=391"}],"version-history":[{"count":10,"href":"https:\/\/ppc-scripts.eu\/en\/wp-json\/wp\/v2\/posts\/391\/revisions"}],"predecessor-version":[{"id":448,"href":"https:\/\/ppc-scripts.eu\/en\/wp-json\/wp\/v2\/posts\/391\/revisions\/448"}],"wp:attachment":[{"href":"https:\/\/ppc-scripts.eu\/en\/wp-json\/wp\/v2\/media?parent=391"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ppc-scripts.eu\/en\/wp-json\/wp\/v2\/categories?post=391"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ppc-scripts.eu\/en\/wp-json\/wp\/v2\/tags?post=391"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}