Google Ads Script: Track Search Term Trends

The script works with a Google Sheet to provide a clear report that highlights which search terms are becoming more popular and which are less so. By organizing search terms into categories like "New," "Rising," and "Declining," it makes it simple to identify and respond to the latest trends affecting your Google Ads campaigns.

By default, the script is designed to run on a weekly basis as it looks at the last 7 days and compares it to the previous period.

How It Works

The script generates a report that includes:

• New search terms that have started triggering your ads.

• Search terms with increased or decreased impressions over the past week.

• The ability to exclude specific campaigns from the analysis by name or ID.

• Automated population of a Google Sheet template (download copy below) with this data for easy review.

How to Use the Script

First, GET THE GOOGLE SHEET TEMPLATE HERE. This template is pre-configured to work with the script.

Customize the Script

1. Change YOUR_SPREADSHEET_URL to the URL of the Google Sheet template that you can copy above. Ensure the Sheet is shared with the General Access setting set ‘Anyone with the link’ and ‘Editor’ permissions.

2. If you want to exclude specific campaigns from the report, you can do so by ID or name:

   • To exclude by ID, replace EXCLUDE_CAMPAIGN_ID_1, EXCLUDE_CAMPAIGN_ID_2 with the actual campaign IDs. If you have more to add, separate them with commas, like this: ['ID_1', 'ID_2', 'ID_3'].

   • To exclude by name (case sensitive), replace 'Exclude Name 1', 'Exclude Name 2' with the campaign names you want to leave out. Just like with IDs, add more names separated by commas, or remove any you don't need.

   • You can use either method or both at the same time. If you don't want to exclude any campaigns, leave these sections as they are.

3. Set the impressionChangeThreshold to the minimum percentage change in impressions needed for a term to be included in the "Rising" or "Declining" search terms sections of your report. The default is 50.

4. Change the impressionsThreshold to the least number of impressions a search term must have in the last 7 days to be included in your report. The default is 100.

5. Leave the sheet name as Extracted Raw Data to ensure your data is extracted correctly.

Remember, you need to share your Google Sheet with editing rights to connect it with the script. This setup allows the script to write the report directly to your sheet.

How to add the script to Google Ads

This part is for those who are new to Google Ads scripts. This brief explanation will help you implement the script easily. Remember to adjust the script with your specific details where needed.

Copy the Script: Highlight the entire script provided below after making your adjustments and copy it (Ctrl+C or Command+C).

Access Google Ads Script Editor:

• Log in to your Google Ads account.

• Click on 'Tools & Settings'.

• Under 'Bulk Actions', select 'Scripts'.

Add the New Script:

• Click the blue plus button (+) to create a new script.

• In the script editor that opens, clear it and paste (Ctrl+V or Command+V) the copied script.

Save Your Script:

• Click ‘Preview’ to see if it works.

• Once you’re sure it’s working, click 'Save' in the corner of the script editor.

• Name your script for easy identification.

Authorize the Script:

• The first time you save, you'll be asked to authorize the script to access your Google Ads..

• Click 'Authorize', follow the prompts, and allow the necessary permissions.

Set a Schedule:

• Return to the main 'Scripts' page where all your scripts are listed.

• Next to 'Frequency', click the 'Edit' icon (pencil) to set how often your script runs (e.g., daily, weekly). Each time the script runs, it will replace the previous content.

Make sure you have the Google Sheet template and that you replace YOUR_SPREADSHEET_URL in the script with the URL of Google Sheet document (the template). This ensures that the script knows where to send the report.

The script (adjust, copy/paste):

function main() {
  var spreadsheetUrl = 'YOUR_SPREADSHEET_URL'; // Replace with your actual Google Sheet URL
  var excludeCampaignIds = ['EXCLUDE_CAMPAIGN_ID_1', 'EXCLUDE_CAMPAIGN_ID_2']; // Specify campaign IDs to EXCLUDE from the report.
  var excludeCampaignNames = ['Exclude Name 1', 'Exclude Name 2']; // Specify partial or full campaign names to EXCLUDE from the report.
  var impressionChangeThreshold = 50; // Percentage change threshold for impressions
  var impressionsThreshold = 100; // Minimum number of impressions in the last 7 days for a search term to be included in the report
  var sheetName = 'Extracted Raw Data'; // The name of the sheet where you want to insert the data

  try {
    var searchCampaignIds = getSearchCampaignIds(excludeCampaignIds, excludeCampaignNames);
    var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
    var sheet = spreadsheet.getSheetByName(sheetName);

    var today = new Date();
    var sevenDaysAgo = Utilities.formatDate(new Date(today.getFullYear(), today.getMonth(), today.getDate() - 7), AdsApp.currentAccount().getTimeZone(), 'yyyyMMdd');
    var fourteenDaysAgo = Utilities.formatDate(new Date(today.getFullYear(), today.getMonth(), today.getDate() - 14), AdsApp.currentAccount().getTimeZone(), 'yyyyMMdd');
    
    var reportForLast7Days = getSearchTermReport(sevenDaysAgo, Utilities.formatDate(today, AdsApp.currentAccount().getTimeZone(), 'yyyyMMdd'), searchCampaignIds);
    var reportForPreviousPeriod = getSearchTermReport(fourteenDaysAgo, sevenDaysAgo, searchCampaignIds);

    var mergedData = mergeAndProcessReports(reportForLast7Days, reportForPreviousPeriod, impressionChangeThreshold, impressionsThreshold);

    sheet.clearContents();
    sheet.appendRow(['Search Term', 'Impressions Last 7 Days', 'Impressions Previous Period', 'Percentage Change', 'Campaign Names', 'Campaign IDs']);

    mergedData.forEach(function(row) {
      sheet.appendRow([row.term, row.last7DaysImpressions, row.previousPeriodImpressions, row.percentageChange.toFixed(2) + '%', row.campaignNames.join(", "), row.campaignIds.join(", ")]);
    });

    sendEmailNotification(spreadsheetUrl); // Include the spreadsheet URL in the email notification
    Logger.log('Report update successful. You can view the report at: ' + spreadsheetUrl);
  } catch (e) {
    Logger.log('An error occurred during the report update: ' + e.toString());
    // Consider sending an error notification email if needed
  }
}

function getSearchCampaignIds(excludeCampaignIds, excludeCampaignNames) {
  var campaignIds = [];
  var campaigns = AdsApp.campaigns()
                         .withCondition('AdvertisingChannelType = SEARCH')
                         .withCondition('Status = ENABLED')
                         .get();
  while (campaigns.hasNext()) {
    var campaign = campaigns.next();
    var campaignId = campaign.getId();
    var campaignName = campaign.getName();

    var excludeById = excludeCampaignIds.includes(campaignId);
    var excludeByName = excludeCampaignNames.some(name => campaignName.includes(name));

    if (!excludeById && !excludeByName) {
      campaignIds.push(campaignId);
    }
  }
  return campaignIds;
}

function getSearchTermReport(startDate, endDate, searchCampaignIds) {
  var campaignFilter = 'AND CampaignId IN [' + searchCampaignIds.join(',') + '] ';
  var query = 'SELECT Query, CampaignName, CampaignId, Impressions ' +
              'FROM SEARCH_QUERY_PERFORMANCE_REPORT ' +
              'WHERE Impressions > 0 ' +
              campaignFilter +
              'DURING ' + startDate + ',' + endDate;

  var report = AdsApp.report(query);
  var rows = report.rows();
  var data = [];
  while (rows.hasNext()) {
    var row = rows.next();
    var term = row['Query'];
    var campaignName = row['CampaignName'];
    var campaignId = row['CampaignId'];
    var impressions = parseInt(row['Impressions'], 10);

    var existingRow = data.find(r => r.term === term);
    if (existingRow) {
      existingRow.impressions += impressions;
      if (!existingRow.campaignNames.includes(campaignName)) {
        existingRow.campaignNames.push(campaignName);
        existingRow.campaignIds.push(campaignId);
      }
    } else {
      data.push({
        term: term,
        impressions: impressions,
        campaignNames: [campaignName],
        campaignIds: [campaignId]
      });
    }
  }
  return data;
}

function mergeAndProcessReports(reportForLast7Days, reportForPreviousPeriod, changeThreshold, impressionsThreshold) {
  return reportForLast7Days.filter(row => row.impressions >= impressionsThreshold).map(currentRow => {
    var previousRow = reportForPreviousPeriod.find(prevRow => prevRow.term === currentRow.term) || { impressions: 0 };
    var percentageChange = previousRow.impressions > 0 ? ((currentRow.impressions - previousRow.impressions) / previousRow.impressions) * 100 : Infinity;
    return {
      term: currentRow.term,
      last7DaysImpressions: currentRow.impressions,
      previousPeriodImpressions: previousRow.impressions,
      percentageChange: percentageChange,
      campaignNames: currentRow.campaignNames,
      campaignIds: currentRow.campaignIds
    };
  }).filter(row => Math.abs(row.percentageChange) >= changeThreshold);
}

function sendEmailNotification(spreadsheetUrl) {
  var recipientEmail = 'your@email.com'; // Replace with the desired email address
  var subject = 'Google Ads Search Term Trend Report Updated';
  var messageBody = 'The Google Ads search term trend report has been successfully updated with terms meeting the impression thresholds and percentage change criteria.\n\n' +
                    'You can view the updated report here: ' + spreadsheetUrl + '\n\n' +
                    'Best Regards,\n' +
                    'Your Automated Reporting System';

  MailApp.sendEmail(recipientEmail, subject, messageBody);
}

/**
 * Script by Three Chapter Media | www.threechaptermedia.com
*/

---

This article was written with the support of A.I. technology.