/********************************************************************************************** * AdWords Account Management -- Review Search Queries & Post Adjustments via Google Docs. * Version 1.0 * Created By: Derek Martin * DerekMartinLA.com & MixedMarketingArtist.com *********************************************************************************************/ var GOOGLE_DOC_URL = "put your url here"; var START_DATE = '20150401'; var END_DATE = '20150415'; function main() { var results = runQueryReport(); modifySpreadSheet(results); } // check a query for whether the keyword exists in the account // returns true or false function keywordExists(keyword) { var kw = keyword; if (kw != null) { kwIter = AdWordsApp.keywords().withCondition("Text = \'"+kw+"\'").withCondition("Status = ENABLED").get(); var exists = kwIter.totalNumEntities() > 0 ? true : false; return exists; } } function runQueryReport() { var listOfQueries = []; var report = AdWordsApp.report( 'SELECT Query, CampaignName, AdGroupName, ConversionsManyPerClick, ConversionValue, Cost, AverageCpc, Clicks, Impressions, Ctr, ConversionRateManyPerClick ' + 'FROM SEARCH_QUERY_PERFORMANCE_REPORT ' + 'WHERE CampaignName does_not_contain Shopping ' + 'DURING ' + START_DATE + ',' + END_DATE +' '); var rows = report.rows(); while (rows.hasNext()) { var row = rows.next(); var query = row['Query']; var campaign= row['CampaignName']; var adgroup = row['AdGroupName']; var conversions = row['ConversionsManyPerClick']; var conversionValue = parseFloat(row['ConversionValue']).toPrecision(2); var cost = parseFloat(row['Cost']).toPrecision(2); var roas = conversionValue / cost; var averageCpc = row['AverageCpc']; var clicks = row['Clicks']; var impressions = row['Impressions']; var ctr = row['Ctr']; var conversionRate = row['ConversionRateManyPerClick']; if (query.length < 20) { var keyword_exists = keywordExists(query); } else { var keyword_exists = false; } var queryResult = new queryData(query, campaign, adgroup, conversions, conversionValue,cost, roas,averageCpc, clicks, impressions, ctr, conversionRate, keyword_exists); listOfQueries.push(queryResult); } // end of report run return listOfQueries; } function queryData(query, campaign, adgroup, conversions, conversionValue, cost, roas, averageCpc, clicks, impressions, ctr, conversionRate, exists ) { this.query = query; this.campaign = campaign; this.adgroup = adgroup; this.conversions = conversions; this.conversionValue = conversionValue; this.cost = cost; this.roas = roas; this.averageCpc = averageCpc; this.clicks = clicks; this.impressions = impressions; this.ctr = ctr; this.conversionRate = conversionRate; this.exists = exists; } // end of productData function modifySpreadSheet(results) { var queryResults = results; var querySS = SpreadsheetApp.openByUrl(GOOGLE_DOC_URL); var sheet = querySS.getActiveSheet(); var columnNames = ["Query", "In Account", "Campaign", "Ad Group", "Conversions", "Conversion Value", "Cost", "ROAS", "Average CPC","Clicks", "Impressions", "Ctr", "ConversionRate"]; var headersRange = sheet.getRange(1, 1, 1, columnNames.length); headersRange.setFontWeight("bold"); headersRange.setFontSize(12); headersRange.setBorder(false, false, true, false, false, false); for (i = 0; i < queryResults.length; i++) { headersRange.setValues([columnNames]); var query = queryResults[i].query; var exists = (queryResults[i].exists == true) ? "Added" : "Not Added"; var campaign = queryResults[i].campaign; var adgroup = queryResults[i].adgroup; var conversions = queryResults[i].conversions; var conversionValue = queryResults[i].conversionValue; var cost = queryResults[i].cost; var roas = (isNaN(queryResults[i].roas)) ? 0.00 : queryResults[i].roas; var averageCpc = queryResults[i].averageCpc; var clicks = queryResults[i].clicks; var impressions = queryResults[i].impressions; var ctr = queryResults[i].ctr; var conversionRate = queryResults[i].conversionRate; sheet.appendRow([query, exists, campaign, adgroup, conversions, conversionValue, cost, roas, averageCpc, clicks, impressions, ctr, conversionRate]); } sheet.getRange("A2:M").setFontSize(12); sheet.getRange("E:E").setNumberFormat("0"); sheet.getRange("F:G").setNumberFormat("$0.00"); sheet.getRange("H:H").setNumberFormat("0.00"); sheet.getRange("I:I").setNumberFormat("$0.00"); sheet.getRange("J:K").setNumberFormat("0.00"); sheet.getRange("A2:M") .sort({column: 5, ascending: false}); sheet.getRange("A:D").setVerticalAlignment("middle"); sheet.getRange("A:D").setHorizontalAlignment("center"); } // Helper functions function warn(msg) { Logger.log('WARNING: '+msg); } function info(msg) { Logger.log(msg); }