THE FUTURE OF ECOMMERCE
Beim digitalen Event dabei sein und von angesehenen E-Commerce-Experten erfahren, was die Zukunft bringt.
THE FUTURE OF ECOMMERCE
Beim digitalen Event dabei sein und von angesehenen E-Commerce-Experten erfahren, was die Zukunft bringt.

Search Query Illuminator

Verstehen Sie den Einfluss unbekannter Suchanfragen auf Ihre KPIs

Search Query Illuminator illustration

Verstehen, wie die Änderungen im Bericht zu Suchanfragen Ihre KPIs beeinflussen?

Search Query Illuminator – unser Google Ads Script – hilft Ihnen dabei, den Umfang und Einfluss des Google Updates beim Bericht zu Suchanfragen (September 2020) besser zu verstehen. Sie verschaffen sich ein deutlicheres Bild darüber, wie hoch der Anteil an Kunden oder Kosten ist, den Sie unbekannten Suchanfragen zuschreiben können. Das Script liest Impressionen, Klicks, Conversions und Kosten von Ihrem Google Ads Account aus, vergleicht diese miteinander und erstellt automatisch übersichtliche Diagramme für Sie. Also, warum probieren Sie es nicht aus?

Eine detaillierte Beschreibung des Search Query Illuminator-Scripts und eine kurze Anleitung zur Anwendung, finden Sie in unserem Blogartikel: Google Ads Update beim Bericht zu Suchbegriffen: Ein Script schafft Abhilfe

Work smarter, not harder!

Formular ausfüllen und kostenloses Script herunterladen.

Success image

Vielen Dank!

Nach unten scrollen und Script Code erhalten

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); 
}

Der Code wurde erfolgreich in deine Zwischenablage kopiert.

Beliebte 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

Jetzt unverbindliche Demo anfordern

Erfahren Sie was Whoop! für Sie leisten kann