Google Ads | Missed Search/Shopping Query Script

Intro

As a PPC professional, you may spend a lot of time reviewing search terms in your campaigns, checking Google's recommendations tab for keyword ideas or using a keyword planning tool to look for queries you may be missing.

If you're managing both search and shopping campaigns, there's an additional avenue for boosting traffic that you might be overlooking - analyzing whether you're serving shopping ads on queries without corresponding search ads, and vice versa.

Google (& Bing) may serve a mix of search and shopping ads for a given query, or just one of these types of ad, so there's likely to be queries in your account that only appear on one ad type.

Benefits

When using this on an account, I found a whole stream of shopping terms that weren't appearing in search campaigns and was able to build out more keywords to boost my search campaign traffic.

Given the nature of shopping campaigns, it was harder to get more queries for the campaign, but it did provide insights into the types of missing terms which were incorporated into the shopping feed to increase search terms.

Because shopping campaigns target a broader range of queries, high impression shopping terms that don't appear in search campaigns often fell into one of two categories:

1. Good queries - Add them as keywords to search campaigns
2. Bad queries - Add as negative keywords to avoid wasted spend

Script Details

This script provides comprehensive data in four tabs within a Google Sheet:

1. Shopping Campaigns - Search Query Report (SQR) of Shopping campaigns as defined by the script filters
2. Search Campaigns - SQR of Search campaigns as defined by the script filters
3. Unique Search Terms - A compilation of queries triggering search ads but not shopping ads.
4. Unique Shopping Terms - A compilation of queries triggering shopping ads but not search ads.

In order to use the script, follow these instructions:

1. Copy the script into a new Google Ads script
• If you don't know how to do this, follow the Google documentation, then return and follow the below
2. Update the config
• Update the value in var spreadsheetUrl to a spreadsheet you have created
• Update the campaign name contains filters
• Update any sheet outputs you don't want to update in the next run
• Update the number of days and impression filter you want to set for your report
3. Run the script

Notes:

• For an account looking at 25,000 search and 24,000 shopping queries, the script took around 7 minutes to run
• If the script times out, update the output filters in the config to run the sheets one at a time (by setting all other sheets to FALSE

If you have any questions, issues or requests with the script, put a message at the bottom of this page. I hope it helps!

The Script

// ----- CONFIG START ----- //

var spreadsheetUrl = 'ENTER-YOUR-URL-HERE'; // Replace with your spreadsheet URL

// Use campaign name filters

var searchCampaignNameContains = 'Search'; // Campaign name contains filter for search campaigns

var shoppingCampaignNameContains = 'Shopping'; // Campaign name contains filter for shopping campaigns

// Run sheets individually if your script times out.

var outputSearchSheet = 'TRUE'; // If TRUE, this populates 'Search Campaigns' tab

var outputShoppingSheet = 'TRUE'; // If TRUE, this populates 'Shopping Campaigns' tab

var outputUniqueSearchTermsSheet = 'TRUE'; // If TRUE, this populates 'Unique Search Terms' tab

var outputUniqueShoppingTermsSheet = 'TRUE'; // If TRUE, this populates 'Unique Shopping Terms' tab

var numberOfDays = 30; // Change this to the desired number of days to review

var minImpressions = 0; // Change this to the desired minimum number of impressions (0 = all impr). 

// ----- CONFIG START ----- //

//v1

function main() {

  

  var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);

  var shoppingSheet = spreadsheet.getSheetByName('Shopping Campaigns') || spreadsheet.insertSheet('Shopping Campaigns');

  var searchSheet = spreadsheet.getSheetByName('Search Campaigns') || spreadsheet.insertSheet('Search Campaigns');

  var uniqueSearchTermsSheet = spreadsheet.getSheetByName('Unique Search Terms') || spreadsheet.insertSheet('Unique Search Terms');

  var uniqueShoppingTermsSheet = spreadsheet.getSheetByName('Unique Shopping Terms') || spreadsheet.insertSheet('Unique Shopping Terms');

  

  

  Logger.log("Start getting Campaigns");

  var shoppingCampaigns = getCampaigns('SHOPPING', shoppingCampaignNameContains);

  var searchCampaigns = getCampaigns('SEARCH', searchCampaignNameContains);

  Logger.log("Campaigns retrieved");

  var shoppingTerms = getSearchTerms(shoppingCampaigns,shoppingSheet,outputShoppingSheet);

  Logger.log("shoppingTerms.length: " + shoppingTerms.length);

  var searchTerms = getSearchTerms(searchCampaigns,searchSheet,outputSearchSheet);

  Logger.log("searchTerms.length: " + searchTerms.length);

  

  //------------Start analysing terms only found in search campaigns---------//

  if (outputUniqueSearchTermsSheet === 'TRUE') {

    // Find unique search terms in search campaigns

    Logger.log("Start analysing unique search terms");

    var uniqueSearchTerms = searchTerms.filter(function(term) {

      return shoppingTerms.every(function(shoppingTerm) {

        return shoppingTerm[2] !== term[2];

      });

    });

    Logger.log("uniqueSearchTerms.length: " + uniqueSearchTerms.length);

    // Build array with unique search terms data

     var outputData = [['Campaign', 'Ad Group', 'Unique Search Terms', 'Impressions', 'Clicks', 'Cost', 'Conversions']];

     uniqueSearchTerms.forEach(function(term) {

       outputData.push(term);

     });

    // Write unique search terms in one go

    uniqueSearchTermsSheet.clear();

    uniqueSearchTermsSheet.getRange(1, 1, outputData.length, outputData[0].length).setValues(outputData);

    Logger.log("Unique search terms finished outputting.");

  } else {

    Logger.log("Terms only found in search campaigns not analysed");

  }

  //------------Stop analysing terms only found in search campaigns---------//

  

  

  

  //------------Start analysing terms only found in shopping campaigns---------//

  if (outputUniqueShoppingTermsSheet === 'TRUE') {

    // Find unique search terms in shopping campaigns

    var uniqueShoppingTerms = shoppingTerms.filter(function(term) {

      return searchTerms.every(function(searchTerm) {

        return searchTerm[2] !== term[2];

      });

    });

    // Build array with unique shopping terms data

     var outputDataShopping = [['Campaign', 'Ad Group', 'Unique Search Terms', 'Impressions', 'Clicks', 'Cost', 'Conversions']];

     uniqueShoppingTerms.forEach(function(term) {

       outputDataShopping.push(term);

     });

    // Write unique shopping terms in one go

    uniqueShoppingTermsSheet.clear();

    uniqueShoppingTermsSheet.getRange(1, 1, outputDataShopping.length, outputDataShopping[0].length).setValues(outputDataShopping);

    Logger.log("Unique shopping terms finished outputting.");

  } else {

    Logger.log("Terms only found in shopping campaigns not analysed");

  }

  //------------Stop analysing terms only found in shopping campaigns---------//

  

}

function getCampaigns(channelType, nameContains) {

  var campaignIds = [];

  var query = `SELECT campaign.id, campaign.name ` +

              `FROM campaign ` +

              `WHERE campaign.status = 'ENABLED' ` +

              `AND campaign.advertising_channel_type = '${channelType}' ` +

              `AND campaign.name LIKE '%${nameContains}%'`;

  var report = AdsApp.report(query);

  var rows = report.rows();

  while (rows.hasNext()) {

    var row = rows.next();

    campaignIds.push(row['campaign.id']);

    Logger.log("campaign with name " + row['campaign.name'] + " added to filter for " + channelType);

  }

  return campaignIds;

}

function getSearchTerms(campaignIds,sheet,exportToSheet) {

  var campaignIdsConcat = campaignIds.join(", ")

  

  // Calculate start and end dates

  var endDate = new Date();

  var startDate = new Date();

  startDate.setDate(endDate.getDate() - numberOfDays);

  // Format dates as 'YYYY-MM-DD'

  var formattedStartDate = Utilities.formatDate(startDate, 'UTC', 'yyyy-MM-dd');

  var formattedEndDate = Utilities.formatDate(endDate, 'UTC', 'yyyy-MM-dd');

  

  var query = `SELECT campaign.name, ad_group.name, search_term_view.search_term, metrics.impressions, metrics.clicks, metrics.cost_micros, metrics.conversions ` +

    `FROM search_term_view ` +

    `WHERE campaign.id IN (${campaignIdsConcat}) ` +

    `AND metrics.impressions > ${minImpressions} ` +

    `AND segments.date >= '${formattedStartDate}' AND segments.date <= '${formattedEndDate}'`;

  Logger.log("query: " + query);

  

  var report = AdsApp.report(query);

  

  Logger.log("exportToSheet: " + exportToSheet);

  

  if (exportToSheet === 'TRUE') {

    report.exportToSheet(sheet);

    Logger.log("Exported data to: " + sheet.getName());

  } else {

    Logger.log(sheet.getName() + " not updated")

  }

  

  var searchTerms = [];

    var rows = report.rows();

    while (rows.hasNext()) {

      var row = rows.next();

      searchTerms.push([

        row['campaign.name'],

        row['ad_group.name'],

        row['search_term_view.search_term'],

        row['metrics.impressions'],

        row['metrics.clicks'],

        row['metrics.cost_micros'] / 1000000, // converting micros to currency

        row['metrics.conversions']

      ]);

    }

  return searchTerms;

}