Nick Partridge
Search Query to Keyword Google Ads Script
Adding converting/profitable queries as keywords is one of those tedious tasks that is nonetheless fairly important to stay on top of. There are a couple of scripts out there that tackle this, but I wanted some extra flexibility in selecting exactly which queries get turned into keywords, in particular I wanted to add queries that are in keeping with the theme of its ad group. As neither of the linked scripts allow for this, I thought I'd make my own.
In addition to allowing for query targeting based on included/excluded query text, you can also refine your targeting based on clicks/impressions/cost/conversions/CPA/ROAS, as well as number of days lookback. You can create Broad, Phrase and/or Exact match keywords, and you can choose to get notifications every time the script adds new keywords. All of these options are managed via the spreadsheet linked within the script.
Individual Account Version
function main() {
//CREATE A COPY OF THIS SPREADSHEET, CHANGE SHARE ACCESS TO 'ANYONE WITH THE LINK' AND CHANGE ROLE TO 'EDITOR'
var spreadsheet = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/11FSTX6fzAsbKc_z3H2cX70uLFiTBSjvAUT0D-c6EpBw/edit#gid=0');
var sheet = spreadsheet.getSheetByName('Add Queries as Keywords');
var sheetRowNumber = Number(sheet.getLastRow());
var today = new Date();
var finalReport = [];
for (var y = 2; y <= sheetRowNumber; y++) {
var campaignName = sheet.getRange(y, 1).getValue();
var adGroupName = sheet.getRange(y, 2).getValue();
var includedText = sheet.getRange(y, 3).getValue().split(',');
var includedTextTwo = sheet.getRange(y, 4).getValue().split(',');
var excludedText = sheet.getRange(y, 5).getValue().split(',');
var minClicks = sheet.getRange(y, 6).getValue();
var minImpressions = sheet.getRange(y, 7).getValue();
var minCost = sheet.getRange(y, 8).getValue();
var minConversions = sheet.getRange(y, 9).getValue();
var maxCpa = sheet.getRange(y, 10).getValue();
var minRoas = sheet.getRange(y, 11).getValue();
var dateRange = 'LAST_' + sheet.getRange(y, 12).getValue() + '_DAYS';
var addBroad = sheet.getRange(y, 13).getValue();
var addPhrase = sheet.getRange(y, 14).getValue();
var addExact = sheet.getRange(y, 15).getValue();
var notificationEmail = sheet.getRange(y, 16).getValue();
var queryConversionsAdjusted = Number(minConversions) - .01;
excludedText = excludedText == '' ? ["!@#$%^&*()"] : excludedText;
minClicks = minClicks == '' ? 0 : minClicks
minImpressions = minImpressions == '' ? 0 : minImpressions
minCost = minCost == '' ? 0 : minCost
queryConversionsAdjusted = queryConversionsAdjusted == '' ? 0 : queryConversionsAdjusted
var targetLevel = adGroupName !== '' ? "AND AdGroupName CONTAINS '" + adGroupName + "'" : campaignName !== '' ? "AND CampaignName CONTAINS '" + campaignName + "'" : '';
function getDateRange(separation) {
if (dateRange.match(/LAST_(.*)_DAYS/)) {
var milliseconds = 86400000;
var days = parseInt(dateRange.match(/LAST_(.*)_DAYS/)[1]);
var today = new Date();
var startDate = Utilities.formatDate(new Date(today.getTime() - (milliseconds * days)), AdsApp.currentAccount().getTimeZone(), "yyyyMMdd");
var endDate = Utilities.formatDate(new Date(today.getTime() - milliseconds), AdsApp.currentAccount().getTimeZone(), "yyyyMMdd");
dateRange = startDate + separation + endDate;
}
return dateRange;
}
var matchedQueries = AdsApp.report(
"SELECT CampaignName, AdGroupName, Query, KeywordTextMatchingQuery, ConversionValue, CostPerConversion, KeywordId, Conversions, AdGroupStatus, CampaignStatus, Impressions, AdGroupId, CampaignId, QueryMatchTypeWithVariant, AverageCpc, Cost, QueryTargetingStatus, Clicks" +
" FROM SEARCH_QUERY_PERFORMANCE_REPORT " +
" WHERE " +
" CampaignStatus = 'ENABLED' " +
" AND AdGroupStatus = 'ENABLED' " +
" AND Clicks >= " + minClicks + "" +
" AND Impressions >= " + minImpressions + "" +
" AND Cost >= " + minCost + "" +
" AND Conversions > " + queryConversionsAdjusted + "" +
targetLevel +
" DURING " + getDateRange(",") + "");
matchedQueries = matchedQueries.rows();
while (matchedQueries.hasNext()) {
var query = matchedQueries.next();
function checkQuery(element, index, array) {
return query.Query.includes(element)
}
if (query.QueryTargetingStatus === "None" && (query.Cost >= minCost || minCost == '') && (query.Clicks >= minClicks || minClicks == '') && (query.Impressions >= minImpressions || minImpressions == '') && (query.Conversions >= minConversions || minConversions == '') && (maxCpa >= query.CostPerConversion || maxCpa == '') && (minRoas <= (query.ConversionValue / query.Cost) || minRoas == '') && excludedText.some(checkQuery) == false && (includedText.some(checkQuery) || includedText == '')) {
if (includedTextTwo.some(checkQuery) || includedTextTwo == '') {
var matchTypes = [];
if (addBroad == 'Yes') {
matchTypes.push('BROAD');
}
if (addPhrase == 'Yes') {
matchTypes.push('PHRASE');
}
if (addExact == 'Yes') {
matchTypes.push('EXACT');
}
for (var j = 0; j < matchTypes.length; j++) {
var keywordText = query.Query;
var matchType = matchTypes[j];
var openingMatch = matchTypes[j] == 'BROAD' ? "" : matchTypes[j] == 'PHRASE' ? '\"' : "[";
var closingMatch = matchTypes[j] == 'BROAD' ? "" : matchTypes[j] == 'PHRASE' ? '\"' : "]";
var campaignNameQuery = campaignName == '' ? query.CampaignName : campaignName
var adGroupNameQuery = adGroupName == '' ? query.AdGroupName : adGroupName
var adGroupIterator = AdsApp.adGroups()
.withCondition('CampaignName = "' + campaignNameQuery + '"')
.withCondition('Name = "' + adGroupNameQuery + '"')
.get();
while (adGroupIterator.hasNext()) {
var adGroup = adGroupIterator.next();
adGroup.newKeywordBuilder()
.withText(openingMatch + keywordText + closingMatch)
.build();
finalReport.push([query.CampaignName, query.AdGroupName, query.Query, matchTypes[j].charAt(0).toUpperCase() + matchTypes[j].slice(1).toLowerCase(), query.Impressions, query.Clicks, query.Cost, query.Conversions, query.CostPerConversion, (query.ConversionValue / query.Cost).toFixed(2), today.toDateString()])
Logger.log("Added '" + keywordText + "' to '" + adGroupNameQuery + "' ad group in '" + campaignNameQuery + "' campaign, in " + matchTypes[j].charAt(0).toUpperCase() + matchTypes[j].slice(1).toLowerCase() + " match type, with " + query.Clicks + " clicks, " + query.Cost + " cost, " + query.Conversions + " conversions, $" + query.CostPerConversion + " CPA, and " + (query.ConversionValue / query.Cost).toFixed(2) + " ROAS over the last " + sheet.getRange(y, 12).getValue() + " days.")
}
}
}
}
}
}
var columns = ['Campaign', 'Ad Group', 'Query', 'Match Type', 'Impressions', 'Clicks', 'Cost', 'Conversions', 'CPA', 'ROAS', 'Date'];
var totalQueries = finalReport.length;
var finalReportSorted = finalReport.sort(function(a, b) {
return b[5] - a[5];
});
var htmlFirstRow = '<b> Total Queries Found: ' + totalQueries + '</b><br>'
var htmlTableHeader = '<table border="0" cellspacing="0" style="border-color: #C0C0C0; border-collapse: collapse; padding: 5px; width: 100%">';
var htmlTableColumns = '<tr><th align="left">' + columns.join('</th><th align="left">') + '</th></tr>';
var htmlTableRows = '';
for (var x = 0; x < finalReport.length; x++) {
htmlTableRows += '<tr style="border-bottom: 1px"><td>' + finalReport[x].join('</td><td>') + '</td><tr>';
}
var final_html = htmlFirstRow + htmlTableHeader + htmlTableColumns + htmlTableRows;
if (totalQueries > 0 && notificationEmail != '') {
MailApp.sendEmail({
to: notificationEmail,
subject: 'Added Queries Report for ' + AdsApp.currentAccount().getName(),
htmlBody: final_html
});
}
}
MCC Account Version
function main() {
//CREATE A COPY OF THIS SPREADSHEET, CHANGE SHARE ACCESS TO 'ANYONE WITH THE LINK' AND CHANGE ROLE TO 'EDITOR'
var spreadsheet = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1Ada6yIvZI7SjDuIf7an7BAH03K-efW30gbA1zXhg30k/edit#gid=0');
var sheet = spreadsheet.getSheetByName('Add Queries as Keywords');
var sheetRowNumber = Number(sheet.getLastRow());
var today = new Date();
for (var y = 2; y <= sheetRowNumber; y++) {
var accountName = sheet.getRange(y, 1).getValue()
if (accountName.length === 12) {
var accountSelector = AdsManagerApp
.accounts().withIds([accountName]);
var accountIterator = accountSelector.get();
while (accountIterator.hasNext()) {
var account = accountIterator.next();
AdsManagerApp.select(account);
var finalReport = [];
var campaignName = sheet.getRange(y, 3).getValue();
var adGroupName = sheet.getRange(y, 4).getValue();
var includedText = sheet.getRange(y, 5).getValue().split(',');
var includedTextTwo = sheet.getRange(y, 6).getValue().split(',');
var excludedText = sheet.getRange(y, 7).getValue().split(',');
var minClicks = sheet.getRange(y, 8).getValue();
var minImpressions = sheet.getRange(y, 9).getValue();
var minCost = sheet.getRange(y, 10).getValue();
var minConversions = sheet.getRange(y, 11).getValue();
var maxCpa = sheet.getRange(y, 12).getValue();
var minRoas = sheet.getRange(y, 13).getValue();
var dateRange = 'LAST_' + sheet.getRange(y, 14).getValue() + '_DAYS';
var addBroad = sheet.getRange(y, 15).getValue();
var addPhrase = sheet.getRange(y, 16).getValue();
var addExact = sheet.getRange(y, 17).getValue();
var notificationEmail = sheet.getRange(y, 18).getValue();
var queryConversionsAdjusted = Number(minConversions) - .01;
var targetLevel = adGroupName !== '' ? "AND AdGroupName CONTAINS '" + adGroupName + "'" : campaignName !== '' ? "AND CampaignName CONTAINS '" + campaignName + "'" : '';
excludedText = excludedText == '' ? ["!@#$%^&*()"] : excludedText;
minClicks = minClicks == '' ? 0 : minClicks
minImpressions = minImpressions == '' ? 0 : minImpressions
minCost = minCost == '' ? 0 : minCost
queryConversionsAdjusted = queryConversionsAdjusted == '' ? 0 : queryConversionsAdjusted
function getDateRange(separation) {
if (dateRange.match(/LAST_(.*)_DAYS/)) {
var milliseconds = 86400000;
var days = parseInt(dateRange.match(/LAST_(.*)_DAYS/)[1]);
var today = new Date();
var startDate = Utilities.formatDate(new Date(today.getTime() - (milliseconds * days)), AdsApp.currentAccount().getTimeZone(), "yyyyMMdd");
var endDate = Utilities.formatDate(new Date(today.getTime() - milliseconds), AdsApp.currentAccount().getTimeZone(), "yyyyMMdd");
dateRange = startDate + separation + endDate;
}
return dateRange;
}
var matchedQueries = AdsApp.report(
"SELECT CampaignName, AdGroupName, Query, KeywordTextMatchingQuery, ConversionValue, CostPerConversion, KeywordId, Conversions, AdGroupStatus, CampaignStatus, Impressions, AdGroupId, CampaignId, QueryMatchTypeWithVariant, AverageCpc, Cost, QueryTargetingStatus, Clicks" +
" FROM SEARCH_QUERY_PERFORMANCE_REPORT " +
" WHERE " +
" CampaignStatus = 'ENABLED' " +
" AND AdGroupStatus = 'ENABLED' " +
" AND Clicks >= " + minClicks + "" +
" AND Impressions >= " + minImpressions + "" +
" AND Cost >= " + minCost + "" +
" AND Conversions > " + queryConversionsAdjusted + "" +
targetLevel +
" DURING " + getDateRange(",") + "");
matchedQueries = matchedQueries.rows();
while (matchedQueries.hasNext()) {
var query = matchedQueries.next();
function checkQuery(element, index, array) {
return query.Query.includes(element)
}
if (query.QueryTargetingStatus === "None" && (query.Cost >= minCost || minCost == '') && (query.Clicks >= minClicks || minClicks == '') && (query.Impressions >= minImpressions || minImpressions == '') && (query.Conversions >= minConversions || minConversions == '') && (maxCpa >= query.CostPerConversion || maxCpa == '') && (minRoas <= (query.ConversionValue / query.Cost) || minRoas == '') && excludedText.some(checkQuery) == false && (includedText.some(checkQuery) || includedText == '')) {
if (includedTextTwo.some(checkQuery) || includedTextTwo == '') {
var matchTypes = [];
if (addBroad == 'Yes') {
matchTypes.push('BROAD');
}
if (addPhrase == 'Yes') {
matchTypes.push('PHRASE');
}
if (addExact == 'Yes') {
matchTypes.push('EXACT');
}
for (var j = 0; j < matchTypes.length; j++) {
var keywordText = query.Query;
var matchType = matchTypes[j];
var openingMatch = matchTypes[j] == 'BROAD' ? "" : matchTypes[j] == 'PHRASE' ? '\"' : "[";
var closingMatch = matchTypes[j] == 'BROAD' ? "" : matchTypes[j] == 'PHRASE' ? '\"' : "]";
var campaignNameQuery = campaignName == '' ? query.CampaignName : campaignName
var adGroupNameQuery = adGroupName == '' ? query.AdGroupName : adGroupName
var adGroupIterator = AdsApp.adGroups()
.withCondition('CampaignName = "' + campaignNameQuery + '"')
.withCondition('Name = "' + adGroupNameQuery + '"')
.get();
while (adGroupIterator.hasNext()) {
var adGroup = adGroupIterator.next();
adGroup.newKeywordBuilder()
.withText(openingMatch + keywordText + closingMatch)
.build();
finalReport.push([account.getName(), query.CampaignName, query.AdGroupName, query.Query, matchTypes[j].charAt(0).toUpperCase() + matchTypes[j].slice(1).toLowerCase(), query.Impressions, query.Clicks, query.Cost, query.Conversions, query.CostPerConversion, (query.ConversionValue / query.Cost).toFixed(2), today.toDateString()])
Logger.log("Added '" + keywordText + "' to '" + adGroupNameQuery + "' ad group in '" + campaignNameQuery + "' campaign, in " + matchTypes[j].charAt(0).toUpperCase() + matchTypes[j].slice(1).toLowerCase() + " match type, with " + query.Clicks + " clicks, " + query.Cost + " cost, " + query.Conversions + " conversions, $" + query.CostPerConversion + " CPA, and " + (query.ConversionValue / query.Cost).toFixed(2) + " ROAS over the last " + sheet.getRange(y, 14).getValue() + " days.")
}
}
}
}
}
}
}
var columns = ['Account', 'Campaign', 'Ad Group', 'Query', 'Match Type', 'Impressions', 'Clicks', 'Cost', 'Conversions', 'CPA', 'ROAS', 'Date'];
var totalQueries = finalReport.length;
var finalReportSorted = finalReport.sort(function(a, b) {
return b[5] - a[5];
});
var htmlFirstRow = '<b> Total Queries Found: ' + totalQueries + '</b><br>'
var htmlTableHeader = '<table border="0" cellspacing="0" style="border-color: #C0C0C0; border-collapse: collapse; padding: 5px; width: 100%">';
var htmlTableColumns = '<tr><th align="left">' + columns.join('</th><th align="left">') + '</th></tr>';
var htmlTableRows = '';
for (var x = 0; x < finalReport.length; x++) {
htmlTableRows += '<tr style="border-bottom: 1px"><td>' + finalReport[x].join('</td><td>') + '</td><tr>';
}
var final_html = htmlFirstRow + htmlTableHeader + htmlTableColumns + htmlTableRows;
if (totalQueries > 0 && notificationEmail != '') {
MailApp.sendEmail({
to: notificationEmail,
subject: 'Added Queries Report for ' + account.getName(),
htmlBody: final_html
});
}
}
}
Suggestions or bugs? Let me know in the comments.