Search Query Illuminator Script

Learn more about how unknown search queries impact your KPIs

Search Query Illuminator Script illustration

Want to understand how search terms report changes affect your account?

The Search Query Illuminator – Google Ads script – helps you understand the scope and impact of the search terms report updates which came into force in September 2020. By running it on your Google Ads account, you gain more clarity about how many customers and/or how much costs you can actually attribute to unknown search queries. It reads metrics such as impressions, clicks, conversions and costs from your Google Ads account, compares them and outputs the result in the form of charts – easy for you to interpret. Why not give it a go?

You can find a detailed description of the Search Query Illuminator and a step-by-step guide on how to run it in our blog article: Google Ads script: How to illuminate the unknown of the search terms report update

Download script and start optimizing

No commitments & no hidden costs — just a great script.

Success image

Thank you!

You can get the script code below

Arrow down icon
/**
 * Search Query Illuminator Script       . * ・ 。゚☆━੧[ ✪ ᗜ ✪ ]⊃
 *
 * Overview: This Google Ads script aims to shed light on the percentages of unknown search queries 
 * that bring you customers and/or generate costs. It reads metrics from your ads account related to 
 * two adjustable periods and outputs charts to compare the two periods. The compared metrics 
 * for the two periods are Clicks, Impressions, Cost and Conversions. The default "BEFORE" and "AFTER" 
 * periods are August 2020 and September 2020, related to Google's update on the search terms report 
 * that will include only search terms searched by a significant number of users. The periods to 
 * compare are fully adjustable in the "Configuration" section of the script. The output of the script 
 * is metrics in the script console and automatically generated Google Spreadsheet with charts.
 *
 * Author: smec [smarter-ecommerce.com]
 *
 * Version: 1.00
 * Changelog:
 * - version 1.00
 *  - Released initial version.
 *
 * Twitter: @smec
 * Email: [email protected]
 * (c) Smarter Ecommerce GmbH. All rights reserved.
 * License: MIT
 */

/** ============================== Attention ===================================
 * This script creates and writes data into a new spreadsheet even when run in preview mode.
 */

/** ============================ Configuration ============================== */

// You may replace the config variables with a fixed date using the format 'YEARMMDD', e.g. '20201031'
var BEFORE_PERIOD_FIRST_DATE = '20200801';
var BEFORE_PERIOD_LAST_DATE = '20200831'; 
var AFTER_PERIOD_FIRST_DATE = '20200901';
var AFTER_PERIOD_LAST_DATE = '20200930'; 

/* ============================== Preferences =============================== */

// Optionally you may adjust the Ad Network Type used. The default is "SEARCH". 
// Other possible options are "CONTENT" (for Display Network), "YOUTUBE_SEARCH", "YOUTUBE_WATCH", "MIXED"
var ADNETWORKTYPE1 = 'SEARCH';

/* =============================== Execution ================================ */  

function main() {
    var aggregatedData = initializeEmptyAggregatedDataObject();  

    if (AFTER_PERIOD_LAST_DATE == 'TODAY') {
        var today = new Date();
        AFTER_PERIOD_LAST_DATE = formatDate(today);
    }
    var BEFORE_DATE_RANGE = BEFORE_PERIOD_FIRST_DATE + "," + BEFORE_PERIOD_LAST_DATE;
    var AFTER_DATE_RANGE = AFTER_PERIOD_FIRST_DATE + "," + AFTER_PERIOD_LAST_DATE;

    //Before period
    var queryBefore1 = 'SELECT Clicks, Impressions, Cost, Conversions ' + 
        'FROM ACCOUNT_PERFORMANCE_REPORT ' + 
        'WHERE AdNetworkType1 = ' + ADNETWORKTYPE1 + ' ' +  
        'DURING ' + BEFORE_DATE_RANGE;
    aggregateDataFromTotals(AdsApp.report(queryBefore1).rows(), "BEFORE");

    var queryBefore2 = 'SELECT Month, Clicks, Impressions, Cost, Conversions ' + 
        'FROM SEARCH_QUERY_PERFORMANCE_REPORT ' + 
        'WHERE Query!="" AND AdNetworkType1 = ' + ADNETWORKTYPE1 + ' ' + 
        'DURING ' + BEFORE_DATE_RANGE;
    aggregateDataFromSearchQueries(AdsApp.report(queryBefore2).rows(), "BEFORE");


    //After period
    var queryAfter1 = 'SELECT Clicks, Impressions, Cost, Conversions ' + 
        'FROM ACCOUNT_PERFORMANCE_REPORT ' + 
        'WHERE AdNetworkType1 = ' + ADNETWORKTYPE1 + ' ' +  
        'DURING ' + AFTER_DATE_RANGE;
    aggregateDataFromTotals(AdsApp.report(queryAfter1).rows(), "AFTER");

    var queryAfter2 = 'SELECT Month, Clicks, Impressions, Cost, Conversions ' + 
        'FROM SEARCH_QUERY_PERFORMANCE_REPORT ' + 
        'WHERE Query!="" AND AdNetworkType1 = ' + ADNETWORKTYPE1 + ' ' + 
        'DURING ' + AFTER_DATE_RANGE;
    aggregateDataFromSearchQueries(AdsApp.report(queryAfter2).rows(), "AFTER");

    var statsOutput = [];
    for(var period in aggregatedData) {
        statsOutput.push([period, 
                        ((aggregatedData[period].totalClicks - aggregatedData[period].clicksFromKnownQueries) / aggregatedData[period].totalClicks), 
                        ((aggregatedData[period].totalImpressions - aggregatedData[period].impressionsFromKnownQueries) / aggregatedData[period].totalImpressions),
                        ((aggregatedData[period].totalCost - aggregatedData[period].costFromKnownQueries) / aggregatedData[period].totalCost).toFixed(2),
                        ((aggregatedData[period].totalConversions - aggregatedData[period].conversionsFromKnownQueries) / aggregatedData[period].totalConversions).toFixed(2)
                    ]);
    }

    if(!statsOutput.length) {
        Logger.log('This account has no data for the selected time range.');
    }

    Logger.log('Report for Ad Network Type ' + ADNETWORKTYPE1);
    Logger.log('--------------------------------');
    Logger.log('Period BEFORE => All clicks: ' + (aggregatedData["BEFORE"].totalClicks) + " | Clicks from known search terms: " + aggregatedData["BEFORE"].clicksFromKnownQueries);        
    Logger.log('Period BEFORE => All impressions: ' + (aggregatedData["BEFORE"].totalImpressions) + " | Impressions from known search terms: " + aggregatedData["BEFORE"].impressionsFromKnownQueries);        
    Logger.log('Period BEFORE => All costs: ' + (aggregatedData["BEFORE"].totalCost.toFixed(2)) + " | Cost from known search terms: " + aggregatedData["BEFORE"].costFromKnownQueries.toFixed(2));        
    Logger.log('Period BEFORE => All conversions: ' + (aggregatedData["BEFORE"].totalConversions.toFixed(2)) + " | Conversions from known search terms: " + aggregatedData["BEFORE"].conversionsFromKnownQueries.toFixed(2));        
    Logger.log('Period AFTER => All clicks: ' + (aggregatedData["AFTER"].totalClicks) + " | Clicks from known search terms: " + aggregatedData["AFTER"].clicksFromKnownQueries);        
    Logger.log('Period AFTER => All impressions: ' + (aggregatedData["AFTER"].totalImpressions) + " | Impressions from known search terms: " + aggregatedData["AFTER"].impressionsFromKnownQueries);        
    Logger.log('Period AFTER => All costs: ' + (aggregatedData["AFTER"].totalCost.toFixed(2)) + " | Cost from known search terms: " + aggregatedData["AFTER"].costFromKnownQueries.toFixed(2));        
    Logger.log('Period AFTER => All conversions: ' + (aggregatedData["AFTER"].totalConversions.toFixed(2)) + " | Conversions from known search terms: " + aggregatedData["AFTER"].conversionsFromKnownQueries.toFixed(2));            
    Logger.log('--------------------------------');  

    var spreadsheet = SpreadsheetApp.create('Google Ads metrics from undefined search queries');

    var sheet = spreadsheet.getSheets()[0];
    sheet.getRange(1,1,1,5).setValues([["Period: \nBefore: "+getDateFromYYYYmmdd(BEFORE_PERIOD_FIRST_DATE)+" - "+getDateFromYYYYmmdd(BEFORE_PERIOD_LAST_DATE)+"\nAfter: "+getDateFromYYYYmmdd(AFTER_PERIOD_FIRST_DATE)+" - "+getDateFromYYYYmmdd(AFTER_PERIOD_LAST_DATE), "Clicks", "Impressions", "Cost", "Conversions"]]);
    sheet.setColumnWidth(1, 200);
    sheet.setColumnWidth(2, 200);
    sheet.setColumnWidth(3, 200);
    sheet.setColumnWidth(4, 200);
    sheet.getRange(2,1,statsOutput.length,statsOutput[0].length).setValues(statsOutput);
    sheet.getRange(2,1,statsOutput.length,statsOutput[0].length).setNumberFormat(".0%");

    buildSmecColumnChart(sheet, "Clicks from unknown search terms", "A2:A3", "B2:B3", 5, 1);
    buildSmecColumnChart(sheet, "Impressions from unknown search terms", "A2:A3", "C2:C3", 5, 3);        
    buildSmecColumnChart(sheet, "Cost from unknown search terms", "A2:A3", "D2:D3", 5, 5);
    buildSmecColumnChart(sheet, "Conversions from unknown search terms", "A2:A3", "E2:E3", 5, 9);  

    Logger.log("See your charts at " + spreadsheet.getUrl());

    function aggregateDataFromTotals(rowIterator, period){
        while(rowIterator.hasNext()) {
            var row = rowIterator.next();
            aggregatedData[period].totalClicks += parseFloat(row['Clicks'].replace(',',''));
            aggregatedData[period].totalImpressions += parseFloat(row['Impressions'].replace(',',''));
            aggregatedData[period].totalCost += parseFloat(row['Cost'].replace(',',''));        
            aggregatedData[period].totalConversions += parseFloat(row['Conversions'].replace(',',''));                        
        }      
    }  
    function aggregateDataFromSearchQueries(rowIterator, period){
        while(rowIterator.hasNext()) {
            var row = rowIterator.next();
            aggregatedData[period].clicksFromKnownQueries += parseFloat(row['Clicks']);
            aggregatedData[period].impressionsFromKnownQueries += parseFloat(row['Impressions']);
            aggregatedData[period].costFromKnownQueries += parseFloat((row['Cost']));
            aggregatedData[period].conversionsFromKnownQueries += parseFloat(row['Conversions']);            
        }      
    }
    function initializeEmptyAggregatedDataObject() {
        var aggregatedData = {};
        ["BEFORE","AFTER"].forEach(function(period){
          aggregatedData[period] = {
              'totalClicks': 0,
              'clicksFromKnownQueries': 0,
              'totalImpressions': 0,
              'impressionsFromKnownQueries': 0,
              'totalCost': 0,
              'costFromKnownQueries': 0,
              'totalConversions': 0,
              'conversionsFromKnownQueries': 0   
          };           
        });
        return aggregatedData;
    }
}
function buildSmecColumnChart(sheet, title, range1, range2, xPos, yPos) {
    var chart = sheet.newChart()
    .setChartType(Charts.ChartType.COLUMN)
    .addRange(sheet.getRange(range1))
    .addRange(sheet.getRange(range2))
    .setOption("title", title)
    .setOption("colors", ['#d8b868','#22323F'])   
    .setOption("width", 300)
    .setPosition(xPos, yPos, 0, 0)
    .build();

    sheet.insertChart(chart);    
}
function formatDate(d) {
    var month = '' + (d.getMonth() + 1),
        day = '' + d.getDate(),
        year = d.getFullYear();

    if (month.length < 2) 
        month = '0' + month;
    if (day.length < 2) 
        day = '0' + day;

    return [year, month, day].join('');
}
function getDateFromYYYYmmdd(dateString) {
    var year        = dateString.substring(0,4);
    var month       = dateString.substring(4,6);
    var day         = dateString.substring(6,8);

    var date = new Date(year, month-1, day); 
    return dateToISOString(date);
}
function dateToISOString(date) {
    return date.toISOString().slice(0, 10); 
}

The code was successfully copied to your Clipboard.

Popular Google Ads Scripts

Martin Röttgerding

“Automation can take your PPC efforts to the next level – if you’re doing it right.”

Martin Röttgerding, Head of SEA – Bloofusion Germany

Maximize your Google Shopping performance with Whoop!

Get in touch to learn more