AdWords Search Query Analyse Script

AdWords, Online Marketing Schlagwörter: ,

Eine Problematik bei wachsenden AdWords Accounts ist, den Überblick über die Fülle an Keywords zu behalten, die z.B. Onlinehändler eingebucht haben. Bei granularen Steuerungen der Accounts kann es sehr viel Zeit in Anspruch nehmen, die gebuchten Keywords zu bewerten und aus den Suchanfragen neue Keyword Buchungen zu erstellen. Wir haben den Weg ein Stück weit automatisiert mit unserem Search Query Script.

Manuelle Auswertung von Search Queries zur Erstellung neuer Keywords

Der manuelle Weg dies zu tun, bedeutet die Suchanfragen nach erfolgreichen Keywords zu durchsuchen (z.B. alle Suchanfragen, die zu einem Verkauf geführt haben) und anschließend diese Kampagne für Kampagne durch zu gehen und in die entsprechenden Anzeigengruppen einfügen. Dabei erwischt man natürlich immer wieder Keywords, die bereits eingebucht sind. Desto feiner der AdWords Account bereits aufgestellt ist, desto schwieriger wird das Finden von Suchanfragen die noch nicht als Keyword gebucht sind. Dies kann sehr Zeitaufwendig sein.

Vorsortierung per Search Query Script

Wir haben ein AdWords Script weiter entwickelt, welches einem den mühseligen Teil der Arbeit, den Abgleich zwischen vorhandenen und nicht vorhandenen Keywords abnimmt.

Das Original, von Derek Martin gibt es hier. Wir haben das vorhandene AdWords Script nach unseren Wünschen ausgebaut und daraus eine Variante gemacht, die wir täglich in unseren Agentur-Workflow nutzen.

Was macht das AdWords Search Query Script?

  1. Das Script analysiert die angegebenen Google AdWords Accounts auf Suchanfragen, die mit „Conversions > 0“ übereinstimmen und führt diese mit den ausgewählten Accounts aus.
  2. Wird ein Keyword nicht gefunden, wird das Keyword mit den relevanten Performance-Daten in das AdWords-Konto aufgenommen.

Ergebnis

Hier ein Beispiel, des Spreadsheets, den das Script ausgibt. In Spalte 1 befindet sich die Search Query. Die Spalten Campaign und AdGroup enthalten die Quelle, die das Keyword auslöste. Weitere Performance-Daten befinden sich dahinter.

Anhand dieser Daten lassen sich dann schnell und einfach neue AdGroups finden, oder vorhandene ausbauen. Gehört ein Keyword nicht zu denen, die man einfügen will, so kann ein Kommentar geschrieben werden, der auch nach der nächsten Ausführung des Scripts erhalten bleibt.

Wie installiere ich das Script?

  1. Das Script installieren
    1. adwords-search-query-report.js auf AdWords Kontoebene
    2. adwords-search-query-report-MCC.js auf AdWords MCC-Ebene
  2. Nur für das MCC-Script müssen auf Kontoebene Label mit dem Namen „Search Query Conversion Report“ gesetzt werden. Dies entfällt bei der AdWords Account Version des Scripts.
  3. Im Script den Betrachtungszeitraum einstellen
  4. Im Script die ID eines Google Spreadsheets setzen in dem die Daten angelegt werden sollen.
  5. Das Script speichern und Authorisieren
  6. Führen Sie das Script aus. Nach wenigen Minuten sollten die Ergebnisse vorliegen

AdWords MCC – Search Query Script

/******************************************************************************************
 * Company: Webmasterei Prange
 * Author:  Bernhard Prange
 * Email:   bernhard@webmasterei-prange.de
 * Web:     https://webmasterei-prange.de
 *
 * Credits for the Basis & Inspiration of the Script go to Derek Martin
 * https://gist.github.com/derekmartinla and his Script:
 * https://gist.github.com/derekmartinla/ed6e3f5856006d8b4410
 *
 *
*******************************************************************************************/


var GOOGLE_DOC_URL = "SET SPREADSHEET URL HERE";
var TIMESPAN = "90";

var accountLabel = "Search Query Conversion Report";

function main() {
    var accountSelector = MccApp.accounts();
    accountSelector.withCondition("LabelNames CONTAINS '" + accountLabel +"'");
    accountSelector.executeInParallel('processClientAccount', null);
}

function processClientAccount() {

    var results = runQueryReport();
    modifySpreadSheet(results);
}


// check a query for whether the keyword exists in the account
// returns true or false

function keywordExists(keyword) {
    var kw = keyword;
    if (kw != null) {
        try{
            kwIter = AdWordsApp.keywords().withCondition("Text = \'"+kw+"\'").withCondition("Status = ENABLED").get();
            var exists = kwIter.totalNumEntities() > 0 ? true : false;
            return exists;
        }
        catch(err){
            Logger.log(err.message)
        }
    }
}

function runQueryReport() {
    var timespan = getTimespan(TIMESPAN);
    var listOfQueries = [];

    var report = AdWordsApp.report(
        'SELECT Query, CampaignName, AdGroupName, Conversions, ConversionValue, Cost, AverageCpc, Clicks, Impressions, Ctr, ConversionRate ' +
        'FROM SEARCH_QUERY_PERFORMANCE_REPORT ' +
        'WHERE Conversions > 0 ' +
        'DURING ' + timespan["from_date"] +', '+ timespan["to_date"] +' ');

    var rows = report.rows();

    while (rows.hasNext()) {
        var row = rows.next();

        var query = row['Query'];
        var campaign= row['CampaignName'];
        var adgroup = row['AdGroupName'];
        var conversions = row['Conversions'];
        var conversionValue = row['ConversionValue'];
        var cost = row['Cost'];
        var roas = conversionValue / cost;
        var averageCpc = row['AverageCpc'];
        var clicks = row['Clicks'];
        var impressions = row['Impressions'];
        var ctr = row['Ctr'];
        var conversionRate = row['ConversionRate'];
        var keyword_exists = keywordExists(query);

        var queryResult = new queryData(query, campaign, adgroup, conversions, conversionValue,cost, roas,averageCpc, clicks, impressions, ctr, conversionRate, keyword_exists);

        listOfQueries.push(queryResult);

    }  // end of report run

    return listOfQueries;

}

function queryData(query, campaign, adgroup, conversions, conversionValue, cost, roas, averageCpc, clicks, impressions, ctr, conversionRate, exists ) {
    this.query = query;
    this.campaign = campaign;
    this.adgroup = adgroup;
    this.conversions = conversions;
    this.conversionValue = conversionValue;
    this.cost = cost;
    this.roas = roas;
    this.averageCpc = averageCpc;
    this.clicks = clicks;
    this.impressions = impressions;
    this.ctr = ctr;
    this.conversionRate = conversionRate;
    this.exists = exists;
} // end of productData
function getOldComments(sheet){
    // Get Old Comments
    var oldComments = {};
    var lastRow = sheet.getLastRow();
    if(!lastRow){return;}
    var lastCol = sheet.getLastColumn();
    if(!lastCol){return;}
    var range = sheet.getRange(2,1, lastRow, lastCol);
    for (var i = 1; i <= lastRow; i++) {
        var oldQuery = range.getCell(i,1).getValue();
        var oldComment = range.getCell(i,3).getValue();
        if(oldComment){
            oldComments[oldQuery] = oldComment;
        }
    }
    return oldComments;
}

function modifySpreadSheet(results) {

    var queryResults = results;

    var querySS = SpreadsheetApp.openByUrl(GOOGLE_DOC_URL);
    var account = AdWordsApp.currentAccount();
    var accountName = account.getName();
    var sheet = querySS.getSheetByName(accountName);
    if(!sheet){
        querySS.insertSheet(accountName);
        var sheet = querySS.getSheetByName(accountName);
    }
    var oldComments = getOldComments(sheet);
    sheet.clear();
    var columnNames = ["Query", "In Account", "Kommentar", "Campaign", "Ad Group", "Conversions", "Conversion Value", "Cost", "ROAS", "Average CPC","Clicks", "Impressions", "Ctr", "ConversionRate"];

    var headersRange = sheet.getRange(1, 1, 1, columnNames.length);

    headersRange.setFontWeight("bold");
    headersRange.setFontSize(12);
    headersRange.setBorder(false, false, true, false, false, false);

    for (i = 0; i < queryResults.length; i++) {
        headersRange.setValues([columnNames]);
        if(queryResults[i].exists == false) {
            var query = queryResults[i].query;
            var exists = (queryResults[i].exists == true) ? "Added" : "Not Added";
            var campaign = queryResults[i].campaign;
            var adgroup = queryResults[i].adgroup;
            var conversions  = parseFloat(queryResults[i].conversions);
            var conversionValue = parseFloat(queryResults[i].conversionValue);
            var cost = parseFloat(queryResults[i].cost);
            var roas = (isNaN(queryResults[i].roas)) ? 0.00 : queryResults[i].roas;
            var averageCpc = parseFloat(queryResults[i].averageCpc);
            var clicks = queryResults[i].clicks;
            var impressions = queryResults[i].impressions;
            var ctr = queryResults[i].ctr;
            var conversionRate = queryResults[i].conversionRate;
            if(oldComments[query]){
                var comment = oldComments[query];
            } else {
                var comment = "";
            }
            sheet.appendRow([query, exists, comment, campaign, adgroup, conversions, conversionValue, cost, roas, averageCpc, clicks, impressions, ctr, conversionRate]);
        }
    }

    sheet.getRange("A2:M").setFontSize(10);
    sheet.getRange("F:F").setNumberFormat("0.00");
    sheet.getRange("G:G").setNumberFormat("0.00");
    sheet.getRange("H:H").setNumberFormat("0.00");
    sheet.getRange("I:I").setNumberFormat("0.00");
    sheet.getRange("J:J").setNumberFormat("0.00");
    sheet.getRange("K:K").setNumberFormat("0");
    sheet.getRange("L:L").setNumberFormat("0");

    sheet.getRange("A2:N").sort([{column: 12, ascending: false}, {column: 6, ascending: false}]);

}

// Helper functions
function warn(msg) {
    Logger.log('WARNING: '+msg);
}

function info(msg) {
    Logger.log(msg);
}

function getTimespan(TIMESPAN){
    var timeZone = AdWordsApp.currentAccount().getTimeZone();
    var MILLIS_PER_DAY = 1000 * 60 * 60 * 24;
    var now = new Date();
    var to_date = new Date(now.getTime() - MILLIS_PER_DAY);
    to_date = Utilities.formatDate(to_date, timeZone, 'yyyyMMdd')
    var from_date = new Date(now.getTime() - (MILLIS_PER_DAY * TIMESPAN));
    from_date = Utilities.formatDate(from_date, timeZone, 'yyyyMMdd')
    var timespan = {"to_date":to_date, "from_date":from_date}
    return timespan;
}

AdWords Account – Search Query Script

/******************************************************************************************
 * Company: Webmasterei Prange
 * Author:  Bernhard Prange
 * Email:   bernhard@webmasterei-prange.de
 * Web:     https://webmasterei-prange.de
 *
 * Credits for the Basis & Inspiration of the Script go to Derek Martin
 * https://gist.github.com/derekmartinla and his Script:
 * https://gist.github.com/derekmartinla/ed6e3f5856006d8b4410
 *
 *
*******************************************************************************************/


var GOOGLE_DOC_URL = "INSERT SPREADSHEET URL HERE";
var TIMESPAN = "180";

var accountLabel = "Search Query Conversion Report";

function main() {
    var results = runQueryReport();
    modifySpreadSheet(results);
}


// check a query for whether the keyword exists in the account
// returns true or false

function keywordExists(keyword) {
    var kw = keyword;
    if (kw != null) {
        try{
            kwIter = AdWordsApp.keywords().withCondition("Text = \'"+kw+"\'").withCondition("Status = ENABLED").get();
            var exists = kwIter.totalNumEntities() > 0 ? true : false;
            return exists;
        }
        catch(err){
            Logger.log(err.message)
        }
    }
}

function runQueryReport() {
    var timespan = getTimespan(TIMESPAN);
    var listOfQueries = [];

    var report = AdWordsApp.report(
        'SELECT Query, CampaignName, AdGroupName, Conversions, ConversionValue, Cost, AverageCpc, Clicks, Impressions, Ctr, ConversionRate ' +
        'FROM SEARCH_QUERY_PERFORMANCE_REPORT ' +
        'WHERE Conversions > 0 ' +
        'DURING ' + timespan["from_date"] +', '+ timespan["to_date"] +' ');

    var rows = report.rows();

    while (rows.hasNext()) {
        var row = rows.next();

        var query = row['Query'];
        var campaign= row['CampaignName'];
        var adgroup = row['AdGroupName'];
        var conversions = row['Conversions'];
        var conversionValue = row['ConversionValue'];
        var cost = row['Cost'];
        var roas = conversionValue / cost;
        var averageCpc = row['AverageCpc'];
        var clicks = row['Clicks'];
        var impressions = row['Impressions'];
        var ctr = row['Ctr'];
        var conversionRate = row['ConversionRate'];
        var keyword_exists = keywordExists(query);

        var queryResult = new queryData(query, campaign, adgroup, conversions, conversionValue,cost, roas,averageCpc, clicks, impressions, ctr, conversionRate, keyword_exists);

        listOfQueries.push(queryResult);

    }  // end of report run

    return listOfQueries;

}

function queryData(query, campaign, adgroup, conversions, conversionValue, cost, roas, averageCpc, clicks, impressions, ctr, conversionRate, exists ) {
    this.query = query;
    this.campaign = campaign;
    this.adgroup = adgroup;
    this.conversions = conversions;
    this.conversionValue = conversionValue;
    this.cost = cost;
    this.roas = roas;
    this.averageCpc = averageCpc;
    this.clicks = clicks;
    this.impressions = impressions;
    this.ctr = ctr;
    this.conversionRate = conversionRate;
    this.exists = exists;
} // end of productData
function getOldComments(sheet){
    // Get Old Comments
    var oldComments = {};
    var lastRow = sheet.getLastRow();
    if(!lastRow){return;}
    var lastCol = sheet.getLastColumn();
    if(!lastCol){return;}
    var range = sheet.getRange(2,1, lastRow, lastCol);
    for (var i = 1; i <= lastRow; i++) {
        var oldQuery = range.getCell(i,1).getValue();
        var oldComment = range.getCell(i,3).getValue();
        if(oldComment){
            oldComments[oldQuery] = oldComment;
        }
    }
    return oldComments;
}

function modifySpreadSheet(results) {

    var queryResults = results;

    var querySS = SpreadsheetApp.openByUrl(GOOGLE_DOC_URL);
    var account = AdWordsApp.currentAccount();
    var accountName = account.getName();
    var sheet = querySS.getSheetByName(accountName);
    if(!sheet){
        querySS.insertSheet(accountName);
        var sheet = querySS.getSheetByName(accountName);
    }
    var oldComments = getOldComments(sheet);
    sheet.clear();
    var columnNames = ["Query", "In Account", "Kommentar", "Campaign", "Ad Group", "Conversions", "Conversion Value", "Cost", "ROAS", "Average CPC","Clicks", "Impressions", "Ctr", "ConversionRate"];

    var headersRange = sheet.getRange(1, 1, 1, columnNames.length);

    headersRange.setFontWeight("bold");
    headersRange.setFontSize(12);
    headersRange.setBorder(false, false, true, false, false, false);

    for (i = 0; i < queryResults.length; i++) {
        headersRange.setValues([columnNames]);
        if(queryResults[i].exists == false) {
            var query = queryResults[i].query;
            var exists = (queryResults[i].exists == true) ? "Added" : "Not Added";
            var campaign = queryResults[i].campaign;
            var adgroup = queryResults[i].adgroup;
            var conversions  = parseFloat(queryResults[i].conversions);
            var conversionValue = parseFloat(queryResults[i].conversionValue);
            var cost = parseFloat(queryResults[i].cost);
            var roas = (isNaN(queryResults[i].roas)) ? 0.00 : queryResults[i].roas;
            var averageCpc = parseFloat(queryResults[i].averageCpc);
            var clicks = queryResults[i].clicks;
            var impressions = queryResults[i].impressions;
            var ctr = queryResults[i].ctr;
            var conversionRate = queryResults[i].conversionRate;
            if(oldComments[query]){
                var comment = oldComments[query];
            } else {
                var comment = "";
            }
            sheet.appendRow([query, exists, comment, campaign, adgroup, conversions, conversionValue, cost, roas, averageCpc, clicks, impressions, ctr, conversionRate]);
        }
    }

    sheet.getRange("A2:M").setFontSize(10);
    sheet.getRange("F:F").setNumberFormat("0.00");
    sheet.getRange("G:G").setNumberFormat("0.00");
    sheet.getRange("H:H").setNumberFormat("0.00");
    sheet.getRange("I:I").setNumberFormat("0.00");
    sheet.getRange("J:J").setNumberFormat("0.00");
    sheet.getRange("K:K").setNumberFormat("0");
    sheet.getRange("L:L").setNumberFormat("0");

    sheet.getRange("A2:N").sort([{column: 12, ascending: false}, {column: 6, ascending: false}]);

}

// Helper functions
function warn(msg) {
    Logger.log('WARNING: '+msg);
}

function info(msg) {
    Logger.log(msg);
}

function getTimespan(TIMESPAN){
    var timeZone = AdWordsApp.currentAccount().getTimeZone();
    var MILLIS_PER_DAY = 1000 * 60 * 60 * 24;
    var now = new Date();
    var to_date = new Date(now.getTime() - MILLIS_PER_DAY);
    to_date = Utilities.formatDate(to_date, timeZone, 'yyyyMMdd')
    var from_date = new Date(now.getTime() - (MILLIS_PER_DAY * TIMESPAN));
    from_date = Utilities.formatDate(from_date, timeZone, 'yyyyMMdd')
    var timespan = {"to_date":to_date, "from_date":from_date}
    return timespan;
}

Bernhard Prange ist seit 2007 im Online Marketing spezialisiert. Neben langjähriger Erfahrung mit Magento, zertifizierter Google Partner mit Spezialisierung auf AdWords Accounts für E-Commerce Shops.

4 Comments

  1. Brendan 3 Monaten Antworten

    Hi Bernard,

    Great script. I’m getting the error:
    TypeError: Cannot read property „SEARCH TERM“ from undefined. (line 153)

    sometimes it’s also on line 160.

    Have you had this issue? Having it with the MCC script as well as the account level script.

    Thanks,
    Brendan

      • remove the unnamed sheet in the Google document
      • run the Script again
      • That happens if there is a secondary document in the spreadsheet which has no name and is active.
        If it works then please give me a little confirmation here.

        Best regards
        Bernhard

  2. Petar 2 Monaten Antworten

    Hi Bernhard,

    Fantastic script!

    I had the same problem as Brendan above. I followed the instructions in your comment and issue has been fixed. Your script works just fine now.

    Regards,

    Petar

    • Thanks for the comment. Thank you notes in shape of a backlink are always welcome!