Skip to content

Instantly share code, notes, and snippets.

@derekmartinla
Last active February 17, 2020 00:09
Show Gist options
  • Save derekmartinla/ed6e3f5856006d8b4410 to your computer and use it in GitHub Desktop.
Save derekmartinla/ed6e3f5856006d8b4410 to your computer and use it in GitHub Desktop.
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