Google Ads Script - Search Terms With Conversions

/******************************************
* 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 Guys, sorry if this is a stupid question but do I change the date within the script? Thanks
Gordon

Hi Gordon, the way how the script works, it takes the current date:
var currentDate = new Date();
and then it subtract the amount of days (7, 30, 90?) you configured in:
var backinTime = 90;
var startDate = new Date(currentDate.getTime() - backinTime * 24 * 60 * 60 * 1000);

What do you need to do? Do you need to specify a particular start and end time?

Hi, Thanks for getting back to me, this is all new to me so I am a bit confused. I have just left var currentDate = new Date();
so like it was in the script and left var backinTime = 90;
I don’t want to specify a particular time I was just wondering if I had set it up correctly because the google sheet just has search term, conversion and campaign without any data at the minute.

Can anyone access the sheet and edit? maybe it's a permissions issue, because I just ran the script again, the exact same code, and it worked.

Are you getting any warnings or errors when running the script on your account?

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.