Last active
October 12, 2023 16:54
-
-
Save Xyborg/9cdcaf4d3980ec559bcc27c442e1c4b2 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/****************************************** | |
* Search Terms With Conversions | |
* @version: 1.2 | |
* @author: Geert Groot (https://www.linkedin.com/in/geertgroot/) | |
* Martin Aberastegue (https://www.linkedin.com/in/aberastegue) | |
* ------------------------------- | |
* This Google Ads script identifies unique, conversion-driving search terms | |
* from the predefined past days. It eliminates duplicates, tracks total conversions | |
* per search term, and compares them against existing account keywords. | |
* The findings are emailed to a recipient and logged in a Google Spreadsheet, | |
* enhancing keyword targeting in Google Ads campaigns. | |
******************************************/ | |
function main() { | |
var emailRecipient = 'YOUR_EMAIL_HERE'; | |
var spreadsheetUrl = 'YOUR_GOOGLE_SPREADSHEET_HERE'; | |
var currentDate = new Date(); | |
/* | |
* I like to use 90 days, but you can decide how many days you want to go back in time. | |
*/ | |
var backinTime = 90; | |
/* | |
* Keep in mind this is the min, and the results will be higher than this. | |
* If you put 1, you won't get the search terms with exactly 1 conv, only the ones with more than that. | |
*/ | |
var minConv = 0; | |
var startDate = new Date(currentDate.getTime() - backinTime * 24 * 60 * 60 * 1000); | |
var reportQuery = 'SELECT Query, KeywordTextMatchingQuery, Conversions, CampaignName ' + | |
'FROM SEARCH_QUERY_PERFORMANCE_REPORT ' + | |
'WHERE Conversions > ' + minConv + | |
'AND Date >= ' + formatDate(startDate) + ' AND Date <= ' + formatDate(currentDate) + ' ' + | |
'ORDER BY Conversions DESC'; | |
var searchQueryReport = AdsApp.report(reportQuery); | |
var searchQueryRows = searchQueryReport.rows(); | |
var existingKeywords = {}; | |
var iterator = AdsApp.keywords().get(); | |
while (iterator.hasNext()) { | |
var keyword = iterator.next(); | |
existingKeywords[keyword.getText().toLowerCase()] = true; | |
} | |
var uniqueQueries = {}; | |
while (searchQueryRows.hasNext()) { | |
var row = searchQueryRows.next(); | |
var query = row['Query'].toLowerCase(); | |
var conversions = parseFloat(row['Conversions']); | |
if (!existingKeywords.hasOwnProperty(query)) { | |
if(uniqueQueries.hasOwnProperty(query)){ | |
uniqueQueries[query].conversions += conversions; | |
} else { | |
uniqueQueries[query] = { | |
conversions: conversions, | |
campaign: row['CampaignName'] | |
} | |
} | |
} | |
} | |
var emailBody = 'Hi there!\n\nHere is a list of unique search terms that have received more than ' + minConv + ' conversions on in the last ' + backinTime + ' days:\n\n'; | |
var newQueries = false; | |
var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl); | |
var sheet = spreadsheet.getSheetByName('Sheet1') || spreadsheet.insertSheet(); | |
sheet.clear(); | |
sheet.appendRow(['Search term', 'Conversions', 'Campaign']); | |
for (var query in uniqueQueries) { | |
emailBody += '- [' + query + '] (' + uniqueQueries[query].conversions + ' conversions)\n'; | |
sheet.appendRow(['[' + query + ']', uniqueQueries[query].conversions, uniqueQueries[query].campaign]); | |
newQueries = true; | |
} | |
if (!newQueries) { | |
emailBody += 'No new search terms with conversions found.'; | |
} | |
emailBody += '\n\nThe search terms have also been added to the following Google Sheet: ' + spreadsheetUrl; | |
emailBody += '\n\nCheers,\nYour Google Ads-script'; | |
MailApp.sendEmail(emailRecipient, 'Search terms with at least ' + minConv + ' conversions (past ' + backinTime + ' days)', emailBody); | |
} | |
function formatDate(date) { | |
var dd = date.getDate(); | |
var mm = date.getMonth() + 1; | |
var yyyy = date.getFullYear(); | |
if (dd < 10) { | |
dd = '0' + dd; | |
} | |
if (mm < 10) { | |
mm = '0' + mm; | |
} | |
return yyyy + mm + dd; | |
} |
Hi. I started getting this error message last week: QueryError.INVALID_DATE_FORMAT: Dates in conditions should be in 'YYYY-MM-DD'. The script worked well before that.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Are you getting any warnings or errors when running the script on your account?