Google Ads Impression Auswertung

gistfile1.txt

/******************************************************************************************
 * Company: Die Berater Onine-Marketing
 * Author:  Malte Helmhold
 * Email:   info@dieberater.de
 * Web:     https://www.dieberater.de
 *
*******************************************************************************************/


var GOOGLE_DOC_URL = "";
var TIMESPAN = "10";

var accountLabel = "Search Query Conversion Report";

function main() {
  
  // Step 1 Kampagne raussuchen und iterieren
     
  var camps = AdsApp.campaigns()
  .forDateRange("LAST_MONTH")
  .withCondition("AdvertisingChannelType = SEARCH")
  .withCondition("Status = ENABLED")
  .withCondition("Clicks > 20")
  .get();
   
 
while (camps.hasNext()){
  
var camp = camps.next();
  
  /// vorbereitung und logging
  
  // step query report für die kampagne --- 
var results = suchbegriffeAuswerten(camp);
  
var campname = camp.getName();

  // step 4 schreiben ins sheet
  
schreibenInTabelle(results, campname);   

} // end while loop
  
  Logger.log("ich bin fertig"); 
  }




function suchbegriffeAuswerten(camp) {
  
    var timespan = getTimespan(TIMESPAN);
    	
  
  	var campid = camp.getId();
    var campname = camp.getName();
    
    Logger.log("Hier startet der Report für die Kampagne:" + campname);

    var listOfQueries = [];

    var report = AdWordsApp.report(
        'SELECT Query, CampaignName, AdGroupName, Conversions, ConversionValue, Cost, AverageCpc, Clicks, Impressions, Ctr, ConversionRate ' +
        'FROM SEARCH_QUERY_PERFORMANCE_REPORT ' +
        'WHERE Impressions > 1 ' +
      	'AND Impressions < 4 ' +
      	'AND CampaignId = ' + campid  +
        ' 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 schreibenInTabelle(results, camp) {

    var queryResults = results;
  	var queryCampaign = camp;

    var querySS = SpreadsheetApp.openByUrl(GOOGLE_DOC_URL);
  
    var sheet = querySS.getSheetByName(queryCampaign);
  	
  
    if(!sheet){
        querySS.insertSheet(queryCampaign);
        var sheet = querySS.getSheetByName(queryCampaign);
    }
  
  
    sheet.clear();

    var columnNames = ["Suchbegriff", "Kampagne", "Anzeigengruppe", "Impressions", "entfernen = x"];

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

   // headersRange.setFontWeight("bold");
    //headersRange.setFontSize(12);
    headersRange.setBorder(false, false, true, false, false, false);
  // Erste Zeile fixieren
    sheet.setFrozenRows(1);
  
  

    for (i = 0; i < queryResults.length; i++) {
        headersRange.setValues([columnNames]);
        if(queryResults[i].exists == false) {
            var suchbegriff = queryResults[i].query;
           // var exists = (queryResults[i].exists == true) ? "Added" : "Not Added";
           // var comment = "";
            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;
         	
            
         //   sheet.appendRow([suchbegriff, exists, comment, campaign, adgroup, conversions, conversionValue, cost, roas, averageCpc, clicks, impressions, ctr, conversionRate]);
              sheet.appendRow([suchbegriff, campaign, adgroup, impressions]);
          
        }
  
    }
  

  

   // 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"); // Impressionen
      sheet.getRange("D:D").setNumberFormat("0");


    sheet.getRange("A2:D").sort([{column: 4, ascending: true}, {column: 4, ascending: true}]);
  
    Logger.log("Die Tabelle ist fertig für die Kampagne:" + queryCampaign);


}






// --------------------------------- //

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

// 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 CONTAINS_IGNORE_CASE \'"+kw+"\'").withCondition("Status = ENABLED").get();
            var exists = kwIter.totalNumEntities() > 0 ? true : false;
            return exists;
        }
        catch(err){
            Logger.log(err.message)
        }
    }
}