Snippets

Frederick Vallaeys Ad Text Report With Keyword and QS Data

Created by Frederick Vallaeys
View revision

File snippet.js Added

  • Ignore whitespace
  • Hide word diff 
+  var DEBUG = 0;
+
+function main() {
+  
+
+  
+  var spreadsheetUrl = "";
+  var spreadsheetName = "KW Ad Data";
+  var accountManagers = "example@example.com";
+  var overWriteOldData = 1;
+  var sheetNames = ["data"];
+  
+  var destinationSpreadsheet = setUpReportInGoogleSheets(spreadsheetUrl, spreadsheetName, accountManagers, overWriteOldData, sheetNames);
+  var spreadsheet = destinationSpreadsheet.spreadsheet;
+  var isSpreadsheetNew = destinationSpreadsheet.isNew;
+  var spreadsheetUrl = destinationSpreadsheet.url;
+
+  var keywordIdMap = buildKeywordIdMap();
+  var adGroupMap = buildAdGroupMap();
+  
+  var spreadsheetOutput = new Array();
+  spreadsheet.appendRow(["keywordText", "keywordMatchType", "campaignName", "adGroupName", "headline", "description1", "description2", "displayUrl", "devicePreference", "clicks", "impressions", "ctr", "qualityScore", "creativeQualityScore", "postClickQualityScore", "searchPredictedCtr" ]);
+  for(var uniqueId in adGroupMap) {
+    var spreadsheetLine = new Array();
+    var adGroup = adGroupMap[uniqueId];
+    var campaignName = adGroup.campaignName;
+    var adGroupName = adGroup.adGroupName;
+    for(var adId in adGroup.ads) {
+      var ad = adGroup.ads[adId];
+      var headline = ad.headline;
+      var description1 = ad.description1;
+      var description2 = ad.description2;
+      var devicePreference = ad.devicePreference;
+      var displayUrl = ad.displayUrl;
+      for(var keywordId in ad.keywords) {
+        var keyword = ad.keywords[keywordId];
+        var clicks = keyword.clicks;
+        var impressions = keyword.impressions;
+        var ctr = keyword.ctr;
+        try {
+          var keywordText = keywordIdMap[keywordId].keywordText;
+          var keywordMatchType = keywordIdMap[keywordId].keywordMatchType;
+          var qualityScore = keywordIdMap[keywordId].qualityScore;
+          var creativeQualityScore = keywordIdMap[keywordId].creativeQualityScore;
+          var postClickQualityScore = keywordIdMap[keywordId].postClickQualityScore;
+          var searchPredictedCtr = keywordIdMap[keywordId].searchPredictedCtr;
+          if(keywordText[0] == "+") keywordText = "'" + keywordText;
+        } catch (e) {
+          var keywordText = "N/A";
+          var keywordMatchType = "N/A";
+          var qualityScore = "--";
+          var creativeQualityScore = "--";
+          var postClickQualityScore = "--";
+          var searchPredictedCtr = "--";
+        }
+      }
+    }
+  }
+}
+
+function buildAdGroupMap() {
+  var adGroupMap = new Array();
+  var report = AdWordsApp.report(
+    'SELECT Id, CampaignName, AdGroupName, KeywordId, DevicePreference, Headline, Description1, Description2, DisplayUrl, Clicks, Impressions, Ctr ' +
+    'FROM   AD_PERFORMANCE_REPORT ' +
+    'WHERE  Impressions > 10 ' +
+    'AND AdType = "TEXT_AD" ' +
+    'DURING LAST_30_DAYS');
+  
+  var rows = report.rows();
+  while (rows.hasNext()) {
+    var row = rows.next();
+    var adId = row['Id'];
+    var campaignName = row['CampaignName'];
+    var adGroupName = row['AdGroupName'];
+    var keywordId = row['KeywordId'];
+    var devicePreference = row['DevicePreference'];
+    
+    var uniqueId = campaignName + " " + adGroupName;
+    if(!adGroupMap[uniqueId]) {
+      adGroupMap[uniqueId] = new Object();
+      adGroupMap[uniqueId].ads = new Array();
+      adGroupMap[uniqueId].campaignName = campaignName;
+      adGroupMap[uniqueId].adGroupName = adGroupName;
+    }
+    if(!adGroupMap[uniqueId].ads[adId]){
+      adGroupMap[uniqueId].ads[adId] = new Object();
+      adGroupMap[uniqueId].ads[adId].devicePreference = devicePreference;
+      adGroupMap[uniqueId].ads[adId].keywords = new Array();
+    }
+    if(!adGroupMap[uniqueId].ads[adId].keywords[keywordId]) {
+      adGroupMap[uniqueId].ads[adId].keywords[keywordId] = new Object();
+    }
+    adGroupMap[uniqueId].ads[adId].headline = row['Headline'];
+    adGroupMap[uniqueId].ads[adId].description1 = row['Description1'];
+    adGroupMap[uniqueId].ads[adId].description2 = row['Description2'];
+    adGroupMap[uniqueId].ads[adId].displayUrl = row['DisplayUrl'];
+    
+    
+    adGroupMap[uniqueId].ads[adId].keywords[keywordId].keywordId = keywordId;
+    adGroupMap[uniqueId].ads[adId].keywords[keywordId].clicks = row['Clicks'];
+    adGroupMap[uniqueId].ads[adId].keywords[keywordId].impressions = row['Impressions'];
+    adGroupMap[uniqueId].ads[adId].keywords[keywordId].ctr = row['Ctr'];
+  }
+  Logger.log("Ad Group Map Done");
+  return(adGroupMap);
+}
+
+function buildKeywordIdMap() {
+  
+  var keywordIdMap = new Array();
+  
+  var report = AdWordsApp.report(
+    'SELECT Id, KeywordMatchType, Criteria, CreativeQualityScore, PostClickQualityScore, SearchPredictedCtr, QualityScore ' +
+    'FROM   KEYWORDS_PERFORMANCE_REPORT '+
+    'WHERE Impressions > 0 ' +
+    'DURING LAST_30_DAYS'
+  );
+  
+  var rows = report.rows();
+  while (rows.hasNext()) {
+    var row = rows.next();
+    keywordId = row['Id'];
+    keywordText = row['Criteria'];
+    keywordMatchType = row['KeywordMatchType'];
+    
+    if(!keywordIdMap[keywordId]) {
+      keywordIdMap[keywordId] = new Object();
+      keywordIdMap[keywordId].keywordText = keywordText;
+      keywordIdMap[keywordId].keywordMatchType = keywordMatchType;
+      keywordIdMap[keywordId].qualityScore = row['QualityScore'];
+      keywordIdMap[keywordId].postClickQualityScore = row['PostClickQualityScore'];
+      keywordIdMap[keywordId].searchPredictedCtr = row['SearchPredictedCtr'];
+      keywordIdMap[keywordId].creativeQualityScore = row['CreativeQualityScore'];
+      //Logger.log("keywordId: " + keywordId + " keywordText: " + keywordText + " keywordMatchType: " + keywordMatchType);
+    }
+  }
+  Logger.log("KW ID Map Done");
+  return keywordIdMap;
+}
+
+
+  function setUpReportInGoogleSheets(spreadsheetUrl, spreadsheetName, accountManagers, overWriteOldData, sheetNames) {
+    
+    var destinationSpreadsheet = new Object();
+    
+    destinationSpreadsheet.overWrite = overWriteOldData;
+    
+    if(!spreadsheetUrl || spreadsheetUrl == "" || spreadsheetUrl == " " || spreadsheetUrl.toLowerCase().indexOf("new") != -1) var isNew = 1;
+    destinationSpreadsheet.isNew = isNew;
+    
+    if(!sheetNames || !sheetNames[0]) {
+      var sheetNames = new Array();
+      sheetNames[0] = "Sheet 1";
+    }
+    
+    if(isNew)
+    {
+      var spreadsheet = SpreadsheetApp.create(spreadsheetName);
+      var spreadsheetUrl = spreadsheet.getUrl();
+    } 
+    var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
+    destinationSpreadsheet.spreadsheet = spreadsheet;
+    destinationSpreadsheet.url = spreadsheet.getUrl();
+    
+    // IF NEW -> REMOVE ALL SHEETS, THEN CREATE ALL SHEETS
+    if(isNew){
+      var allSheets = spreadsheet.getSheets(); 
+      
+      // remove
+      for(var i=1,len=allSheets.length;i<len;i++){
+        spreadsheet.deleteSheet(allSheets[i]);
+      }
+      
+      // create
+      allSheets[0].setName(sheetNames[0]);
+      for(var sheetCounter = 1; sheetCounter < sheetNames.length; sheetCounter++) {
+        var sheetName = sheetNames[sheetCounter];
+        if(DEBUG == 1) Logger.log("sheet name: " + sheetName);
+        spreadsheet.insertSheet(sheetName);
+      }
+    } else {
+      // IF NOT NEW, MAKE SURE RIGHT SHEETS EXIST
+      for(var sheetCounter = 0; sheetCounter < sheetNames.length; sheetCounter++) {
+        var sheetName = sheetNames[sheetCounter];
+        if(DEBUG == 1) Logger.log("checking if sheet with name exists: " + sheetName);
+        try {
+          var thisSheet = spreadsheet.getSheetByName(sheetName);
+          if(!thisSheet) spreadsheet.insertSheet(sheetName);
+        } catch (e) {
+          Logger.log(e);
+        }
+      }
+    }
+    
+    
+    // ADD ACCOUNT MANAGERS
+    if(accountManagers && accountManagers!=""){
+      var accountManagersArray = accountManagers.replace(/\s/g, "").split(",");
+      spreadsheet.addEditors(accountManagersArray);
+    }
+    
+    // IF OVERWRITE, CLEAR SHEETS
+    if(overWriteOldData) {
+      for(var sheetCounter = 0; sheetCounter < sheetNames.length; sheetCounter++) {
+        var sheetName = sheetNames[sheetCounter];
+        if(DEBUG == 1) Logger.log("sheet name: " + sheetName);
+        try {
+          var thisSheet = spreadsheet.getSheetByName(sheetName);
+          if(thisSheet) thisSheet.clear();
+        } catch (e) {
+          Logger.log(e);
+        }
+      }
+    }
+    return(destinationSpreadsheet);
+  }
  2. 1
  3. 2
HTTPS SSH

You can clone a snippet to your computer for local editing. Learn more.