Perform Search Query Analysis In One Google Doc
/********************************************************************************************** | |
* 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); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment