Automated Search
Query Report

By Philip Jones

Information:

This script will create an SQR for each specified account within an MCC. It will create a Google Sheet with a separate tab for each account. Once the export is completed an email will be sent including the export link. Instructions:
  • Copy the code into MCC level Script centre
  • Add account Ids into the config – (code line 5)
  • Add in email address to send email – (code line 6)
Upgrades:
Single Account Version
//** Script Developed by PMJ Digital **//
//** Visit pmjdigital.co.uk for more free Google Ads Scripts **//
var config = {
accountIDs: ['CID 1, CID 2'],
EMAIL_ADDRESS: '',
TIME_FRAME: "LAST_30_DAYS"
}
}
function main() {
var now = new Date();
var today = JSON.stringify(new Date(now.getTime()));
//creates spreadsheet
var spreadsheet = SpreadsheetApp.create("MCC Level SQR " + today.substr(1, 10));
//Gets all the accounts from MCC with selected account IDs
var accountIterator = AdsManagerApp.accounts()
.withIds(config.accountIDs)
.get();
//Iterates through accounts
while (accountIterator.hasNext()) {
var account = accountIterator.next();
AdsManagerApp.select(account);
var accountName = account.getName();
//Logs account name
Logger.log('Running Search Query Report For ' + accountName)
var report = AdsApp.report("SELECT CampaignName, AdGroupName, KeywordTextMatchingQuery, Query, QueryMatchTypeWithVariant, Impressions, Clicks, Ctr, Cost, Conversions" +
" FROM SEARCH_QUERY_PERFORMANCE_REPORT" +
' DURING ' + config.TIME_FRAME);
spreadsheet.insertSheet(accountName)
report.exportToSheet(spreadsheet.getActiveSheet());
}
var sheet = spreadsheet.getSheetByName('Sheet1');
spreadsheet.deleteSheet(sheet);
spreadsheet.addEditors([config.EMAIL_ADDRESS])
//Builds email
var emailHeader = [];
var emailFooter = [];
var emailBody = [];
emailHeader.push('<html>',
'<head></head>',
'<body>');
emailFooter.push('</body>',
'</html>');
emailBody.push("SQR Report available at " + spreadsheet.getUrl())
//sends email
MailApp.sendEmail({
to: config.EMAIL_ADDRESS,
name: 'AdwordsScript',
subject: "Mcc SQR report ready for the " + config.TIME_FRAME,
htmlBody: emailHeader.join('\n') + emailBody.join('\n') + emailFooter.join('\n')
});
Logger.log("Email sent with export")
}
view raw SQR report script.js hosted with ❤ by GitHub
Youtube Linkedin-in Facebook Twitter