İçeriğe geç

N-Gram Script

Google Ads Negatif Kelimeler

Merhaba, Google Ads reklamlarında hesap yöneticilerinin genellikle unuttuğu / atladığı bir çalışma olan anahtar kelime negatifleme bir hesap yönetimi için olmazsa olmaz unsurlardan birisidir.

Anahtar kelime negatifleme olmadan her ne kadar BMM (Broad Match Modifiers) (Geniş eşleme düzenleyici) kullanmış olsanız bile bir çok istemediğini arama terimini çekeceksiniz. Hesabınızın aldığı gösterim ve tıklamaya da bağlı olarak tercihen ilk günlerde günlük, daha sonra haftalığa kadar uzatabileceğiniz bir sürede devamlı surette anahtar kelime negatifleme çalışması yapmanız gerekmektedir.

Arama Terimleri Raporu

Negatif kelimeleri bulmak için Google Ads arama terimleri raporundan faydalanacaksınız. Ancak büyük data ile çalışırken size yardımcı olacak bir script olsa hiçte fena olmaz.

N-Gram nasıl kullanılır ?

N-Gram scriptini çalıştırdıktan sonra, script içinde çalıştığı Google Ads hesabında aşağıda detaylarını verdiğim ayarlamalar ile istediğimiz gibi kullanabiliriz.

Script çalıştıktan sonra, çalışmasını istediğiniz Google sheette güncellemeler yapacak Google Ads hesabınızdaki arama terimlerini 1 kelime, 2 kelime ve istediğiniz kadar kelimeli versiyonlarına ayıracak.

N-Gram 2.2 scripti Google Ads Kampanya ve Reklam grubu olarak ayrı sheetlerde detaylı bir rapor hazırlamaktadır.

Google Ads G-Gram Sheet Görseli
N-Gram Sheetleri

Örnek vermek gerekirse “en ucuz uçak bileti” arama terimi 1-Gram sheetinde en, ucuz, uçak, bileti şeklinde 4 farklı satırda görünecek ve bu kelimelerin geçtiği arama terimlerinin istatistik bilgilerini (tıklama, gösterim vb) görebileceksiniz.

2-Gram sheetinde ise aynı örnekten gidecek olursak “en ucuz”, “ucuz uçak”, “uçak bileti” kelimelerini istatistik bilgilerini göreceksiniz.

Aşağıdaki fotoğraflarda kampanya 1-Gram ve 2-Gram raporlarını görebilirsiniz.

1-Gram örneği
1-Gram örnek rapor
2-Gram örneği
2-Gram örnek rapor

1-Gram raporunda partikül ve filtresi kelimeleri ayrı ayrı raporlanıp detayları görünürken 2-Gram raporunda partikül filtre kelimesini beraber görebiliyoruz

Partikül kelimesi başka arama metinlerinde de geçtiğinden dolayı toplam 43 arama teriminde bu kelime geçmiş ve 71.63 TL bu kelimenin geçtiği aramalara ödeme yapmışız. Sıralı eşleme olan partikül filtresi kelimesi 24 kez arama teriminde geçmiş ve bize maliyeti 36,87 TL harcama yapmışız.

Kampanyanıza göre dönüşüm, CPA, ROAS gibi metrikleri de kullanarak yorum yaparak kampanyanıza yeni kelimeler ekleyebilir veya negatif kelime çalışması yapabilirsiniz.

Bu bilgiler ışığında kampanyanıza yeni kelimeler eklemek istediğiniz gibi bir araya geldiğinde negatif anlamlar içeren kelimeleride tespit edebileceksiniz.

N-Gram Kullanarak Negatif Kelime Bulmak

Negatif kelime bulmak için önemli kriterlerden biri genellikle dönüşüm yapan kelimeleri filtre ile kapatmaktır, dönüşüm yapan kelimeler bizim içini değerli kelimeler olacak yorumlanabilir, bundan sonraki ikinci adım kelimeleri maliyetlerine göre dizerek en çok para harcayan kelimeleri tesbit etmek olabilir, buradan çok para harcaması yapmış ancak dönüşüm getirmemiş kelimeleri negatiflemeyi düşünebilirsiniz.

N-Gram Scripti İndirmek

N-Gram scripti Brainlans isimli bir İngiliz dijital medya ajansının ürettiği kodlardan biri. Firmanın üretip paylaştığı bir çok ücretsiz Google Ads scripti bulunuyor, bu linkten inceleyebilirsiniz.
Yazımıza konu olan N-Gram scriptine bu linkten ulaşabilirsiniz.

N-Gram Scriptinin Google Ads’e Kurulumu

  • N-Gram Scriptini bir tab de açık tutun.
  • Google Ads de Araçlar > Komut Dosyaları na gidin.
  • Büyük + tuşuna basarak yeni bir script ekranı açın.
  • Komut Dosyası Adı bölümüne N-Gram yazın (herhangi bir şey olabilir)
  • Kopyaladığınız N-Gram kodunu code.gs nin içerisine yapıştırın.
  • Google Sheet’den boş bir sheet oluşturun ve URL sini kopyalayın (kurumsal bir gmail kullanıyorsanız bu aşamadı paylaş diyerek linke sahip kişi düzenleme yapabiliri seçerek bu linki kopyalayın)
  • Versiyon 2.2 için 48. satırdaki https://docs.google.com/YXXR-SPRXXDSHXXT-XRL-HXRX yazan bölümü kendi sheet adresiniz ile değiştirin.
  • Aşağıdan Önizleye basın.
  • Google Ads sizden bir yetki isteyecek gerekli yetkiyi verin.
  • Tekrar önizleye basın ve programın çalışmasını izleyin.
  • Çalışma bittikten sonra linkini verdiğiniz Sheetinizi kontrol edin, oluşmuş olan Sheetleri inceleyin.

N-Gram Script İçi Ayarları

  • startDate ve endDate çalışmasını istediğiniz tarih aralığı.
  • currencySymbol Para birimi TL olacak güncelleyebilirsiniz.
  • campaignNameContains Scriptin sadece incelemesini istediğiniz kampanyalar varsa onu yazabilirsiniz, mesela Anneler Günü kampanyanız var, sadece onu incelemek istiyorsanız bu alana “Anneler Günü” yazabilirsiniz.
  • campaignNameDoesNotContain Bu bölüme rapora dahil edilmesini istemediğiniz kampanya isimlerini yazabilirsiniz, Brand yada Rakipler olabilir.
  • ignorePausedCampaigns ve ignorePausedAdGroups ikisi birden True olursa bu durumda kodumuz sadece mevcuttaki aktif kampanyaları denetleyecektir. Duraklatılmış kampanya ve reklam gruplarınıda incelemek istiyorsanız True yenine False yazabilirsiniz.
  • eğer checkNegatives True olursa negatif listenizdeki kelimeler bu rapora eklenmez. Bu ayarı False olarak güncellerseniz ilgili tarih aralığındaki tüm arama terimleri rapora girecektir. Eğer çok fazla negatif kelimeniz varsa kod zaman aşımına uğrayabilir.
  • spreadsheetUrl Bu alana Google Sheetden yarattığınız boş sheetin linkini ekleyeceksiniz, bunu atlarsanız kod çalışmayacaktır.
  • minNGramLength ve maxNGramLength kodun kaç kelime uzunluktaki arama terimlerini raporlayacağını belirler örneğin 2 ve 4 yazarsanız 2-Gram, 3-Gram ve 4-Gram için sonuçlar ulaşacaktır. İkisine birden 2 yazarsanız sadece 2-Gram raporu alırsınız.
  • If clearSpreadsheet True olursa kod her çalıştığında mevcut data silinir ve yerine yenisi ekleri False olursa yeni rapor sheetlerin sonuna eklenir.

N-Gram Kod Versiyon 2.2

/**
*
* Search Query Mining Tool
*
* This script calculates the contribution of each word or phrase found in the 
* search query report and outputs a report into a Google Doc spreadsheet.
*
* Version: 2.2
* Updated 2015-09-17: replacing 'KeywordText' with 'Criteria'
* Updated 2016-10-11: replacing 'ConvertedClicks' with 'Conversions'
* Google AdWords Script maintained on brainlabsdigital.com
*
**/

function main() {
  //////////////////////////////////////////////////////////////////////////////
  // Options
  
  var startDate = "2015-07-01";
  var endDate = "2015-07-30";
  // The start and end date of the date range for your search query data
  // Format is yyyy-mm-dd
  
  var currencySymbol = "£";
  // The currency symbol used for formatting. For example "£", "$" or "€".
  
  var campaignNameContains = "";
  // Use this if you only want to look at some campaigns
  // such as campaigns with names containing 'Brand' or 'Shopping'.
  // Leave as "" if not wanted.
  
  var campaignNameDoesNotContain = "";
  // Use this if you want to exclude some campaigns
  // such as campaigns with names containing 'Brand' or 'Shopping'.
  // Leave as "" if not wanted.
  
  var ignorePausedCampaigns = true;
  // Set this to true to only look at currently active campaigns.
  // Set to false to include campaigns that had impressions but are currently paused.
  
  var ignorePausedAdGroups = true;
  // Set this to true to only look at currently active ad groups.
  // Set to false to include ad groups that had impressions but are currently paused.
  
  var checkNegatives = true;
  // Set this to true to remove queries that would be excluded by your negative keywords.
  
  var spreadsheetUrl = "https://docs.google.com/YOUR-SPREADSHEET-URL-HERE";
  // The URL of the Google Doc the results will be put into.
  
  var minNGramLength = 1;
  var maxNGramLength = 2;
  // The word length of phrases to be checked.
  // For example if minNGramLength is 1 and maxNGramLength is 3, 
  // phrases made of 1, 2 and 3 words will be checked.
  // Change both min and max to 1 to just look at single words.
  
  var clearSpreadsheet = true;
  
  
  //////////////////////////////////////////////////////////////////////////////
  // Thresholds
  
  var queryCountThreshold = 0;
  var impressionThreshold = 10;
  var clickThreshold = 0;
  var costThreshold = 0;
  var conversionThreshold = 0;
  // Words will be ignored if their statistics are lower than any of these thresholds
  
  
  //////////////////////////////////////////////////////////////////////////////
  // Check the spreadsheet has been entered, and that it works
  if (spreadsheetUrl.replace(/[AEIOU]/g,"X") == "https://docs.google.com/YXXR-SPRXXDSHXXT-XRL-HXRX") {
    Logger.log("Problem with the spreadsheet URL: make sure you've replaces the default with a valid spreadsheet URL.");
    return;
  }
  try {
    var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
  } catch (e) {
    Logger.log("Problem with the spreadsheet URL: '" + e + "'");
    return;
  }
  
  // Get the IDs of the campaigns to look at
  var dateRange = startDate.replace(/-/g, "") + "," + endDate.replace(/-/g, "");
  var activeCampaignIds = [];
  var whereStatements = "";
  
  if (campaignNameDoesNotContain != "") {
    whereStatements += "AND CampaignName DOES_NOT_CONTAIN_IGNORE_CASE '" + campaignNameDoesNotContain + "' ";
  }
  if (ignorePausedCampaigns) {
    whereStatements += "AND CampaignStatus = ENABLED ";
  } else {
    whereStatements += "AND CampaignStatus IN ['ENABLED','PAUSED'] ";
  }
  
  var campaignReport = AdWordsApp.report(
    "SELECT CampaignName, CampaignId " +
    "FROM   CAMPAIGN_PERFORMANCE_REPORT " +
    "WHERE CampaignName CONTAINS_IGNORE_CASE '" + campaignNameContains + "' " +
    "AND Impressions > 0 " + whereStatements +
    "DURING " + dateRange
  );
  var campaignRows = campaignReport.rows();
  while (campaignRows.hasNext()) {
    var campaignRow = campaignRows.next();
    activeCampaignIds.push(campaignRow["CampaignId"]);
  }//end while
  
  if (activeCampaignIds.length == 0) {
    Logger.log("Could not find any campaigns with impressions and the specified options.");
    return;
  }
  
  var whereAdGroupStatus = "";
  if (ignorePausedAdGroups) {
    var whereAdGroupStatus = "AND AdGroupStatus = ENABLED ";
  } else {
    whereAdGroupStatus += "AND AdGroupStatus IN ['ENABLED','PAUSED'] ";
  }
  
  
  //////////////////////////////////////////////////////////////////////////////
  // Find the negative keywords
  var negativesByGroup = [];
  var negativesByCampaign = [];
  var sharedSetData = [];
  var sharedSetNames = [];
  var sharedSetCampaigns = [];
  
  if (checkNegatives) {
    // Gather ad group level negative keywords
    var keywordReport = AdWordsApp.report(
      "SELECT CampaignId, AdGroupId, Criteria, KeywordMatchType " +
      "FROM   KEYWORDS_PERFORMANCE_REPORT " +
      "WHERE Status = ENABLED AND IsNegative = TRUE " + whereAdGroupStatus +
      "AND CampaignId IN [" + activeCampaignIds.join(",") + "] " +
      "DURING " + dateRange
      );
    
    var keywordRows = keywordReport.rows();
    while (keywordRows.hasNext()) {
      var keywordRow = keywordRows.next();
      
      if (negativesByGroup[keywordRow["AdGroupId"]] == undefined) {
        negativesByGroup[keywordRow["AdGroupId"]] = 
          [[keywordRow["Criteria"].toLowerCase(),keywordRow["KeywordMatchType"].toLowerCase()]];
      } else {
        negativesByGroup[keywordRow["AdGroupId"]].push([keywordRow["Criteria"].toLowerCase(),keywordRow["KeywordMatchType"].toLowerCase()]);
      }
    }
    
    // Gather campaign level negative keywords
    var campaignNegReport = AdWordsApp.report(
      "SELECT CampaignId, Criteria, KeywordMatchType " +
      "FROM   CAMPAIGN_NEGATIVE_KEYWORDS_PERFORMANCE_REPORT " +
      "WHERE  IsNegative = TRUE " +
      "AND CampaignId IN [" + activeCampaignIds.join(",") + "]"
    );
    var campaignNegativeRows = campaignNegReport.rows();
    while (campaignNegativeRows.hasNext()) {
      var campaignNegativeRow = campaignNegativeRows.next();
      if (negativesByCampaign[campaignNegativeRow["CampaignId"]] == undefined) {
        negativesByCampaign[campaignNegativeRow["CampaignId"]] = [[campaignNegativeRow["Criteria"].toLowerCase(),campaignNegativeRow["KeywordMatchType"].toLowerCase()]];
      } else {
        negativesByCampaign[campaignNegativeRow["CampaignId"]].push([campaignNegativeRow["Criteria"].toLowerCase(),campaignNegativeRow["KeywordMatchType"].toLowerCase()]);
      }
    }
    
    // Find which campaigns use shared negative keyword sets
    var campaignSharedReport = AdWordsApp.report(
      "SELECT CampaignName, CampaignId, SharedSetName, SharedSetType, Status " +
      "FROM   CAMPAIGN_SHARED_SET_REPORT " +
      "WHERE SharedSetType = NEGATIVE_KEYWORDS " +
      "AND CampaignId IN [" + activeCampaignIds.join(",") + "]");
    var campaignSharedRows = campaignSharedReport.rows();
    while (campaignSharedRows.hasNext()) {
      var campaignSharedRow = campaignSharedRows.next();
      if (sharedSetCampaigns[campaignSharedRow["SharedSetName"]] == undefined) {
        sharedSetCampaigns[campaignSharedRow["SharedSetName"]] = [campaignSharedRow["CampaignId"]];
      } else {
        sharedSetCampaigns[campaignSharedRow["SharedSetName"]].push(campaignSharedRow["CampaignId"]);
      }
    }
    
    // Map the shared sets' IDs (used in the criteria report below)
    // to their names (used in the campaign report above)
    var sharedSetReport = AdWordsApp.report(
      "SELECT Name, SharedSetId, MemberCount, ReferenceCount, Type " +
      "FROM   SHARED_SET_REPORT " +
      "WHERE ReferenceCount > 0 AND Type = NEGATIVE_KEYWORDS ");
    var sharedSetRows = sharedSetReport.rows();
    while (sharedSetRows.hasNext()) {
      var sharedSetRow = sharedSetRows.next();
      sharedSetNames[sharedSetRow["SharedSetId"]] = sharedSetRow["Name"];
    }
    
    // Collect the negative keyword text from the sets,
    // and record it as a campaign level negative in the campaigns that use the set
    var sharedSetReport = AdWordsApp.report(
      "SELECT SharedSetId, KeywordMatchType, Criteria " +
      "FROM   SHARED_SET_CRITERIA_REPORT ");
    var sharedSetRows = sharedSetReport.rows();
    while (sharedSetRows.hasNext()) {
      var sharedSetRow = sharedSetRows.next();
      var setName = sharedSetNames[sharedSetRow["SharedSetId"]];
      if (sharedSetCampaigns[setName] !== undefined) {
        for (var i=0; i<sharedSetCampaigns[setName].length; i++) {
          var campaignId = sharedSetCampaigns[setName][i];
          if (negativesByCampaign[campaignId] == undefined) {
            negativesByCampaign[campaignId] = 
              [[sharedSetRow["Criteria"].toLowerCase(),sharedSetRow["KeywordMatchType"].toLowerCase()]];
          } else {
            negativesByCampaign[campaignId].push([sharedSetRow["Criteria"].toLowerCase(),sharedSetRow["KeywordMatchType"].toLowerCase()]);
          }
        }
      }
    }
    
    Logger.log("Finished finding negative keywords");
  }// end if
  
  
  //////////////////////////////////////////////////////////////////////////////
  // Define the statistics to download or calculate, and their formatting
  
  var statColumns = ["Clicks", "Impressions", "Cost", "Conversions", "ConversionValue"];
  var calculatedStats = [["CTR","Clicks","Impressions"],
                         ["CPC","Cost","Clicks"],
                         ["Conv. Rate","Conversions","Clicks"],
                         ["Cost / conv.","Cost","Conversions"],
                         ["Conv. value/cost","ConversionValue","Cost"]]
  var currencyFormat = currencySymbol + "#,##0.00";
  var formatting = ["#,##0", "#,##0", "#,##0", currencyFormat, "#,##0", currencyFormat,"0.00%",currencyFormat,"0.00%",currencyFormat,"0.00%"];
  
  
  //////////////////////////////////////////////////////////////////////////////
  // Go through the search query report, remove searches already excluded by negatives
  // record the performance of each word in each remaining query
  
  var queryReport = AdWordsApp.report(
    "SELECT CampaignName, CampaignId, AdGroupId, AdGroupName, Query, " + statColumns.join(", ") + " " +
    "FROM SEARCH_QUERY_PERFORMANCE_REPORT " +
      "WHERE CampaignId IN [" + activeCampaignIds.join(",") + "] " + whereAdGroupStatus +
        "DURING " + dateRange);
  
  var numberOfWords = [];
  var campaignNGrams = {};
  var adGroupNGrams = {};
  var totalNGrams = [];
  
  for (var n=minNGramLength; n<maxNGramLength+1; n++) {
    totalNGrams[n] = {};
  }
  
  var queryRows = queryReport.rows();
  while (queryRows.hasNext()) {
    var queryRow = queryRows.next();
    
    if (checkNegatives) {
      var searchIsExcluded = false;
      
      // Checks if the query is excluded by an ad group level negative
      if (negativesByGroup[queryRow["AdGroupId"]] !== undefined) {
        for (var i=0; i<negativesByGroup[queryRow["AdGroupId"]].length; i++) {
          if ( (negativesByGroup[queryRow["AdGroupId"]][i][1] == "exact" &&
                queryRow["Query"] == negativesByGroup[queryRow["AdGroupId"]][i][0]) ||
              (negativesByGroup[queryRow["AdGroupId"]][i][1] != "exact" &&
              (" "+queryRow["Query"]+" ").indexOf(" "+negativesByGroup[queryRow["AdGroupId"]][i][0]+" ") > -1 )){
            searchIsExcluded = true;
            break;
          }
        }
      }
      
      // Checks if the query is excluded by a campaign level negative
      if (!searchIsExcluded && negativesByCampaign[queryRow["CampaignId"]] !== undefined) {
        for (var i=0; i<negativesByCampaign[queryRow["CampaignId"]].length; i++) {
          if ( (negativesByCampaign[queryRow["CampaignId"]][i][1] == "exact" &&
                queryRow["Query"] == negativesByCampaign[queryRow["CampaignId"]][i][0]) ||
              (negativesByCampaign[queryRow["CampaignId"]][i][1]!= "exact" &&
              (" "+queryRow["Query"]+" ").indexOf(" "+negativesByCampaign[queryRow["CampaignId"]][i][0]+" ") > -1 )){
            searchIsExcluded = true;
            break;
          }
        }
      }
      
      if (searchIsExcluded) {continue;}
    }
    
    var currentWords = queryRow["Query"].split(" ");
    
    if (campaignNGrams[queryRow["CampaignName"]] == undefined) {
      campaignNGrams[queryRow["CampaignName"]] = [];
      adGroupNGrams[queryRow["CampaignName"]] = {};
      
      for (var n=minNGramLength; n<maxNGramLength+1; n++) {
        campaignNGrams[queryRow["CampaignName"]][n] = {};
      }
    }
    
    if (adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]] == undefined) {
      adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]] = [];
      for (var n=minNGramLength; n<maxNGramLength+1; n++) {
        adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]][n] = {};
      }
    }
    
    var stats = [];
    for (var i=0; i<statColumns.length; i++) {
      stats[i] = parseFloat(queryRow[statColumns[i]].replace(/,/g, ""));
    }
    
    var wordLength = currentWords.length;
    if (wordLength > 6) {
      wordLength = "7+";
    }
    if (numberOfWords[wordLength] == undefined) {
      numberOfWords[wordLength] = [];
    }
    for (var i=0; i<statColumns.length; i++) {
      if (numberOfWords[wordLength][statColumns[i]] > 0) {
        numberOfWords[wordLength][statColumns[i]] += stats[i];
      } else {
        numberOfWords[wordLength][statColumns[i]] = stats[i];
      }
    }
    
    // Splits the query into n-grams and records the stats for each
    for (var n=minNGramLength; n<maxNGramLength+1; n++) {
      if (n > currentWords.length) {
        break;
      }
      
      var doneNGrams = [];
      
      for (var w=0; w < currentWords.length - n + 1; w++) {
        var currentNGram = '="' + currentWords.slice(w,w+n).join(" ") + '"';
        
        if (doneNGrams.indexOf(currentNGram) < 0) {
          
          if (campaignNGrams[queryRow["CampaignName"]][n][currentNGram] == undefined) {
            campaignNGrams[queryRow["CampaignName"]][n][currentNGram] = {};
            campaignNGrams[queryRow["CampaignName"]][n][currentNGram]["Query Count"] = 0;
          }
          if (adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]][n][currentNGram] == undefined) {
            adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]][n][currentNGram] = {};
            adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]][n][currentNGram]["Query Count"] = 0;
          }
          if (totalNGrams[n][currentNGram] == undefined) {
            totalNGrams[n][currentNGram] = {};
            totalNGrams[n][currentNGram]["Query Count"] = 0;
          }
          
          campaignNGrams[queryRow["CampaignName"]][n][currentNGram]["Query Count"]++;
          adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]][n][currentNGram]["Query Count"]++;
          totalNGrams[n][currentNGram]["Query Count"]++;
          
          for (var i=0; i<statColumns.length; i++) {
            if (campaignNGrams[queryRow["CampaignName"]][n][currentNGram][statColumns[i]] > 0) {
              campaignNGrams[queryRow["CampaignName"]][n][currentNGram][statColumns[i]] += stats[i];
            } else {
              campaignNGrams[queryRow["CampaignName"]][n][currentNGram][statColumns[i]] = stats[i];
            }
            
            if (adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]][n][currentNGram][statColumns[i]] > 0) {
              adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]][n][currentNGram][statColumns[i]] += stats[i];
            } else {
              adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]][n][currentNGram][statColumns[i]] = stats[i];
            }
            
            if (totalNGrams[n][currentNGram][statColumns[i]] > 0) {
              totalNGrams[n][currentNGram][statColumns[i]] += stats[i];
            } else {
              totalNGrams[n][currentNGram][statColumns[i]] = stats[i];
            }
          }
          
          doneNGrams.push(currentNGram);
        }
      }
    }
  }
  
  Logger.log("Finished analysing queries.");
  
  
  //////////////////////////////////////////////////////////////////////////////
  // Output the data into the spreadsheet
  
  var wordLengthOutput = [];
  var wordLengthFormat = [];
  var outputs = [];
  var formats = [];
  
  for (var n=minNGramLength; n<maxNGramLength+1; n++) {
    outputs[n] = {};
    outputs[n]['campaign'] = [];
    outputs[n]['adgroup'] = [];
    outputs[n]['total'] = [];
    formats[n] = {};
    formats[n]['campaign'] = [];
    formats[n]['adgroup'] = [];
    formats[n]['total'] = [];
  }
  
  // Create headers
  var calcStatNames = [];
  for (var s=0; s<calculatedStats.length; s++) {
    calcStatNames.push(calculatedStats[s][0]);
  }
  var statNames = statColumns.concat(calcStatNames);
  for (var n=minNGramLength; n<maxNGramLength+1; n++) {
    outputs[n]['campaign'].push(["Campaign","Phrase","Query Count"].concat(statNames));
    outputs[n]['adgroup'].push(["Campaign","Ad Group","Phrase","Query Count"].concat(statNames));
    outputs[n]['total'].push(["Phrase","Query Count"].concat(statNames));
  }
  wordLengthOutput.push(["Word count"].concat(statNames));
  
  // Organise the ad group level stats into an array for output
  for (var n=minNGramLength; n<maxNGramLength+1; n++) {
    for (var campaign in adGroupNGrams) {
      for (var adGroup in adGroupNGrams[campaign]) {
        for (var nGram in adGroupNGrams[campaign][adGroup][n]) {
          
          // skips nGrams under the thresholds
          if (adGroupNGrams[campaign][adGroup][n][nGram]["Query Count"] < queryCountThreshold) {continue;}
          if (adGroupNGrams[campaign][adGroup][n][nGram]["Impressions"] < impressionThreshold) {continue;}
          if (adGroupNGrams[campaign][adGroup][n][nGram]["Clicks"] < clickThreshold) {continue;}
          if (adGroupNGrams[campaign][adGroup][n][nGram]["Cost"] < costThreshold) {continue;}
          if (adGroupNGrams[campaign][adGroup][n][nGram]["Conversions"] < conversionThreshold) {continue;}
          
          var printline = [campaign, adGroup, nGram, adGroupNGrams[campaign][adGroup][n][nGram]["Query Count"]];
          
          for (var s=0; s<statColumns.length; s++) {
            printline.push(adGroupNGrams[campaign][adGroup][n][nGram][statColumns[s]]);
          }
          
          for (var s=0; s<calculatedStats.length; s++) {
            var multiplier = calculatedStats[s][1];
            var divisor = calculatedStats[s][2];
            if (adGroupNGrams[campaign][adGroup][n][nGram][divisor] > 0) {
              printline.push(adGroupNGrams[campaign][adGroup][n][nGram][multiplier] / adGroupNGrams[campaign][adGroup][n][nGram][divisor]);
            } else {
              printline.push("-");
            }
          }
          outputs[n]['adgroup'].push(printline);
          formats[n]['adgroup'].push(["0","0","0"].concat(formatting));
        }
      }
    }
  }
  
  // Organise the campaign level stats into an array for output
  for (var n=minNGramLength; n<maxNGramLength+1; n++) {
    for (var campaign in campaignNGrams) {
      for (var nGram in campaignNGrams[campaign][n]) {
        
        // skips nGrams under the thresholds
        if (campaignNGrams[campaign][n][nGram]["Query Count"] < queryCountThreshold) {continue;}
        if (campaignNGrams[campaign][n][nGram]["Impressions"] < impressionThreshold) {continue;}
        if (campaignNGrams[campaign][n][nGram]["Clicks"] < clickThreshold) {continue;}
        if (campaignNGrams[campaign][n][nGram]["Cost"] < costThreshold) {continue;}
        if (campaignNGrams[campaign][n][nGram]["Conversions"] < conversionThreshold) {continue;}
        
        var printline = [campaign, nGram, campaignNGrams[campaign][n][nGram]["Query Count"]];
        
        for (var s=0; s<statColumns.length; s++) {
          printline.push(campaignNGrams[campaign][n][nGram][statColumns[s]]);
        }
        
        for (var s=0; s<calculatedStats.length; s++) {
          var multiplier = calculatedStats[s][1];
          var divisor = calculatedStats[s][2];
          if (campaignNGrams[campaign][n][nGram][divisor] > 0) {
            printline.push(campaignNGrams[campaign][n][nGram][multiplier] / campaignNGrams[campaign][n][nGram][divisor]);
          } else {
            printline.push("-");
          }
        }
        outputs[n]['campaign'].push(printline);
        formats[n]['campaign'].push(["0","0"].concat(formatting));
      }
    }
  }
  
  // Organise the account level stats into an array for output
  for (var n=minNGramLength; n<maxNGramLength+1; n++) {
    for (var nGram in totalNGrams[n]) {
      
      // skips n-grams under the thresholds
      if (totalNGrams[n][nGram]["Query Count"] < queryCountThreshold) {continue;}
      if (totalNGrams[n][nGram]["Impressions"] < impressionThreshold) {continue;}
      if (totalNGrams[n][nGram]["Clicks"] < clickThreshold) {continue;}
      if (totalNGrams[n][nGram]["Cost"] < costThreshold) {continue;}
      if (totalNGrams[n][nGram]["Conversions"] < conversionThreshold) {continue;}
      
      var printline = [nGram, totalNGrams[n][nGram]["Query Count"]];
      
      for (var s=0; s<statColumns.length; s++) {
        printline.push(totalNGrams[n][nGram][statColumns[s]]);
      }
      
      for (var s=0; s<calculatedStats.length; s++) {
        var multiplier = calculatedStats[s][1];
        var divisor = calculatedStats[s][2];
        if (totalNGrams[n][nGram][divisor] > 0) {
          printline.push(totalNGrams[n][nGram][multiplier] / totalNGrams[n][nGram][divisor]);
        } else {
          printline.push("-");
        }
      }
      outputs[n]['total'].push(printline);
      formats[n]['total'].push(["0"].concat(formatting));
    }
  }
  
  // Organise the word count analysis into an array for output
  for (var i = 1; i<8; i++) {
    if (i < 7) {
      var wordLength = i;
    } else {
      var wordLength = "7+";
    }
    
    var printline = [wordLength];
    
    if (numberOfWords[wordLength] == undefined) {
      printline.push([0,0,0,0,"-","-","-","-"]);
    } else {
      for (var s=0; s<statColumns.length; s++) {
        printline.push(numberOfWords[wordLength][statColumns[s]]);
      }
      
      for (var s=0; s<calculatedStats.length; s++) {
        var multiplier = calculatedStats[s][1];
        var divisor = calculatedStats[s][2];
        if (numberOfWords[wordLength][divisor] > 0) {
          printline.push(numberOfWords[wordLength][multiplier] / numberOfWords[wordLength][divisor]);
        } else {
          printline.push("-");
        }
      }
    }
    wordLengthOutput.push(printline);
    wordLengthFormat.push(formatting);
  }
  
  var filterText = "";
  if (ignorePausedAdGroups) {
    filterText = "Active ad groups";
  } else {
    filterText = "All ad groups";
  }
  
  if (ignorePausedCampaigns) {
    filterText += " in active campaigns";
  } else {
    filterText += " in all campaigns";
  }
  
  if (campaignNameContains != "") {
    filterText += " containing '" + campaignNameContains + "'";
    if (campaignNameDoesNotContain != "") {
      filterText += " and not containing '" + campaignNameDoesNotContain + "'";
    }
  } else if (campaignNameDoesNotContain != "") {
    filterText += " not containing '" + campaignNameDoesNotContain + "'";
  }
  
  // Find or create the required sheets
  var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
  var campaignNGramName = [];
  var adGroupNGramName = [];
  var totalNGramName = [];
  var campaignNGramSheet = [];
  var adGroupNGramSheet = [];
  var totalNGramSheet = [];
  
  for (var n=minNGramLength; n<maxNGramLength+1; n++) {
    if (n==1) {
      campaignNGramName[n] = "Campaign Word Analysis";
      adGroupNGramName[n] = "Ad Group Word Analysis";
      totalNGramName[n] = "Account Word Analysis";	  
    } else {
      campaignNGramName[n] = "Campaign " + n + "-Gram Analysis";
      adGroupNGramName[n] = "Ad Group " + n + "-Gram Analysis";
      totalNGramName[n] = "Account " + n + "-Gram Analysis";
    }
    
    campaignNGramSheet[n] = spreadsheet.getSheetByName(campaignNGramName[n]);
    if (campaignNGramSheet[n] == null) {
      campaignNGramSheet[n] = spreadsheet.insertSheet(campaignNGramName[n]);
    }
    
    adGroupNGramSheet[n] = spreadsheet.getSheetByName(adGroupNGramName[n]);
    if (adGroupNGramSheet[n] == null) {
      adGroupNGramSheet[n] = spreadsheet.insertSheet(adGroupNGramName[n]);
    }
    
    totalNGramSheet[n] = spreadsheet.getSheetByName(totalNGramName[n]);
    if (totalNGramSheet[n] == null) {
      totalNGramSheet[n] = spreadsheet.insertSheet(totalNGramName[n]);
    }
  }
  
  var wordCountSheet = spreadsheet.getSheetByName("Word Count Analysis");
  if (wordCountSheet == null) {
    wordCountSheet = spreadsheet.insertSheet("Word Count Analysis");
  }
  
  // Write the output arrays to the spreadsheet
  for (var n=minNGramLength; n<maxNGramLength+1; n++) {
    var nGramName = n + "-Grams";
    if (n == 1) {
      nGramName = "Words";
    }
    
    writeOutput(outputs[n]['campaign'], formats[n]['campaign'], campaignNGramSheet[n], nGramName, "Campaign", filterText, clearSpreadsheet);
    writeOutput(outputs[n]['adgroup'], formats[n]['adgroup'], adGroupNGramSheet[n], nGramName, "Ad Group", filterText, clearSpreadsheet);
    writeOutput(outputs[n]['total'], formats[n]['total'], totalNGramSheet[n], nGramName, "Account", filterText, clearSpreadsheet);
  }
  
  writeOutput(wordLengthOutput, wordLengthFormat, wordCountSheet, "Word Count", "Account", filterText, clearSpreadsheet);
  
  Logger.log("Finished writing to spreadsheet.");
} // end main function


function writeOutput(outputArray, formatArray, sheet, nGramName, levelName, filterText, clearSpreadsheet) {
  for (var i=0;i<5;i++) {
    try {
      if (clearSpreadsheet) {
        sheet.clear();
      }
      
      if (nGramName == "Word Count") {
        sheet.getRange("R1C1").setValue("Analysis of Search Query Performance by Word Count");
      } else {
        sheet.getRange("R1C1").setValue("Analysis of " + nGramName + " in Search Query Report, By " + levelName);
      }
      
      sheet.getRange("R" + (sheet.getLastRow() + 2) + "C1").setValue(filterText);
      
      var lastRow = sheet.getLastRow();
      
      if (formatArray.length == 0) {
        sheet.getRange("R" + (lastRow + 1) + "C1").setValue("No " + nGramName.toLowerCase() + " found within the thresholds.");
      } else {
        sheet.getRange("R" + (lastRow + 1) + "C1:R" + (lastRow+outputArray.length) + "C" + outputArray[0].length).setValues(outputArray);
        sheet.getRange("R" + (lastRow + 2) + "C1:R" + (lastRow+outputArray.length) + "C" + formatArray[0].length).setNumberFormats(formatArray);
        
        var sortByColumns = [];
        if (outputArray[0][0] == "Campaign" || outputArray[0][0] == "Word count") {
          sortByColumns.push({column: 1, ascending: true});
        }
        if (outputArray[0][1] == "Ad Group") {
          sortByColumns.push({column: 2, ascending: true});
        }
        sortByColumns.push({column: outputArray[0].indexOf("Cost") + 1, ascending: false});
        sortByColumns.push({column: outputArray[0].indexOf("Impressions") + 1, ascending: false});
        sheet.getRange("R" + (lastRow + 2) + "C1:R" + (lastRow+outputArray.length) + "C" + outputArray[0].length).sort(sortByColumns);
      }
      
      break;
      
    } catch (e) {
      if (e == "Exception: This action would increase the number of cells in the worksheet above the limit of 2000000 cells.") {
        Logger.log("Could not output " + levelName + " level " + nGramName.toLowerCase() + ": '" + e + "'");
        try {
          sheet.getRange("R" + (sheet.getLastRow() + 2) + "C1").setValue("Not enough space to write the data - try again in an empty spreadsheet");
        } catch (e2) {
          Logger.log("Error writing 'not enough space' message: " + e2);
        }
        break;
      }
      
      if (i == 4) {
        Logger.log("Could not output " + levelName + " level " + nGramName.toLowerCase() + ": '" + e + "'");
      }
    }
  }
}

N-Gram Kendi Sheetini oluşturan başka bir versiyonu

/*
Script to produce a spreadsheet giving key stats for keywords split by match type and the number of words and then for the different length ngrams.
*/
///////////////////////// BEGIN USER EDIT AREA /////////////////////////////
//Enter start and end dates in this format: dd-mm-yyyy
var startDate = "01-01-2019";
var endDate = "31-03-2019"
//Only include enabled keywords?
var enabledOnly = false;
//Only include keywords with conversions?
var withConversionsOnly = false;
//Only include campaigns with this text in the name (leave blank for all)
var campaignNameContains = '';                // e.g. "Brand";
//Exclude campaigns with this text in the name (leave blank if you don't want to exclude any)
var campaignNameDoesNotContain = '';          // e.g. "France"
//Enter here the currency used for the cost
var currency = "TR";
///////////////////////// END USER EDIT AREA ///////////////////////////////
function main() {
  var ids = getIds()
  
  var fields = 'Impressions,Clicks,Conversions,Cost'
  var calculatedFields = 'CTR, Conversion Rate, Cost Per Conversion'
  var ngramMax = 8.0
  
  var campaignIds = getIds()
  
  var temp = getData(fields,startDate,endDate,ngramMax,ids,enabledOnly,withConversionsOnly)
  var matchTypeDict = temp[0]
  var numberWordsDict = temp[1]
  var ngramDict = temp[2]
  
  var accountName = AdWordsApp.currentAccount().getName()
  var ss = SpreadsheetApp.create("Keyword Analysis | "+ accountName);
   
  var ngramHeaders = ['Ngram'].concat(fields.split(',')).concat(calculatedFields.split(','))
  for (var dict in ngramDict) {  
    writeOutDict(ngramDict[dict],ngramHeaders,ss,dict+'-Gram')   
  }
   
  var numberWordsHeader = ['Number Words'].concat(fields.split(',').concat(calculatedFields.split(',')))
  writeOutDict(numberWordsDict,numberWordsHeader,ss,'Number Words - Match Type')
  var matchTypeHeader = ['Match Type'].concat(fields.split(',')).concat(calculatedFields.split(','))
  writeOutDict(matchTypeDict,matchTypeHeader,ss,'Number Words - Match Type',10)
  var spareSheet = ss.getSheetByName('Sheet1')
  if (spareSheet) {
    ss.deleteSheet(spareSheet)
  } 
  Logger.log('Spreadsheet has been created at the following url - '+ ss.getUrl())
}
function getData(fields, startDate,endDate,ngramMax,ids,enabledOnly,withConversionsOnly) {
  var ngramsDict = {}
  var numberWordsDict = {}
  var matchTypeDict = {}
  
  var startDate = convertDate(startDate)
  var endDate = convertDate(endDate)
  
  var headers = ['Campaign','AdGroup','Keyword','Match Type','Number Words'].concat(fields.split(',')) 
  var res = [headers]
  var query = 'SELECT CampaignName, AdGroupName, Criteria, KeywordMatchType, ' + fields + ' FROM KEYWORDS_PERFORMANCE_REPORT '+
                                 'WHERE Cost > 0 '+
                                 'AND AdNetworkType1 = "SEARCH" '+
                                 'AND CampaignId IN [' + ids + '] ' 
  
  if (enabledOnly) {
    query += 'AND Status = ENABLED '
    query += 'AND CampaignStatus = ENABLED '
    query += 'AND AdGroupStatus = ENABLED ' 
  }
  
  if (withConversionsOnly) {
    query += 'AND Conversions > 0 '
  }
  query += 'DURING ' + startDate + ',' + endDate
  
  var report = AdWordsApp.report(query)  
  var rows = report.rows()  
  while (rows.hasNext()) {
   var row = rows.next()
   var campaignName = row['CampaignName']
   var adgroupName = row['AdGroupName']
   var keyword = row['Criteria']
   
   var clicks = toInt(row['Clicks'])
   var impressions = toInt(row['Impressions'])
   var cost = toFloat(row['Cost'])
   var conversions = toInt(row['Conversions'])
   var numberWords = getLength(row['Criteria'])
   var matchType = getMatchType(row['KeywordMatchType'],row['Criteria'])
   var stats = [impressions,clicks,conversions,cost]
   //Logger.log([campaignName,adgroupName,keyword].concat(stats))
   
   addToNgrams(ngramsDict,keyword,stats,ngramMax)
   addToDict(numberWordsDict,numberWords,stats)
   addToDict(matchTypeDict,matchType,stats)
   }
  
  for (var dict in ngramsDict) {   
    addCalculatedMetrics(ngramsDict[dict])   
  }
   
  addCalculatedMetrics(numberWordsDict)
  addCalculatedMetrics(matchTypeDict)
  return [matchTypeDict,numberWordsDict,ngramsDict]
}
function addCalculatedMetrics(dict) {
  for (var key in dict) {
   var stats = dict[key] 
   var ctr = stats[1] > 0.0 ? stats[1] / stats[0] : 0.0
   var convRate = stats[2] > 0.0 ? stats[2] / stats[1] : 0.0
   var costPerConv = stats[2] > 0.0 ? stats[3] / stats[2] : 0.0
   dict[key] = stats.concat([ctr,convRate,costPerConv])
  } 
}
function getLength(criteria) {
  return criteria.length - criteria.replace(/ /g, '').length + 1
}
   
function getMatchType(matchType,criteria) {
  if ((matchType == 'Exact') || (matchType == 'Phrase')) {
   return matchType.toUpperCase()
  } else {
    if (criteria.indexOf('+') > -1) {
      return 'BMM'
    } else {
     return 'BROAD' 
    }
  }
}
function addToNgrams(ngramsDict,keyword,stats,ngramMax) {
 var ngrams = getNgrams(keyword,ngramMax)
 for (var i = 0; i < ngrams.length; i++) {
  var ngram = ngrams[i]
  var length = getLength(ngram)
  if (!ngramsDict[length]) {
    ngramsDict[length] = {}
  }
  var relDict = ngramsDict[length]
  addToDict(relDict,ngram,stats)
 }
}
function addToDict(dict,key,stats) {
  if (!dict[key]) {
   dict[key] = [0,0,0,0] 
  }
  var currentStats = dict[key]
  for (var j = 0; j < stats.length; j++) {
   currentStats[j] = currentStats[j] + stats[j] 
  }
  dict[key] = currentStats
}
  
function getNgrams(string,maxNGrams) {
  var string = string.toLowerCase()
  var split = replaceAllPlus(string).split(' ')
  var ngrams = []
  for (var i = 1; i <= maxNGrams; i++) {
    var temp = getSpecificNgrams(split,i)
    ngrams = ngrams.concat(temp)
  }
  return ngrams
}
function getSpecificNgrams(split,len) {
  var res = []
  for (var i = 0; (i + len) <= split.length; i++) {
    var temp = split.slice(i,i+len).join(' ')
    res.push(temp)
  }
  return res
}
function writeOut(data,ss,sheetName,skipRows) {
  if (data.length <= 1) {return}
 var skipRows = skipRows || 0
 var sheet = ss.getSheetByName(sheetName)
 if (!sheet) {
   ss.insertSheet(sheetName,1)
   var sheet = ss.getSheetByName(sheetName)
 }
 if (skipRows == 0) {sheet.clear()}
  
 sheet.getRange(skipRows+1,1,data.length,data[0].length).setValues(data).setBorder(true,true,true,true,false,false)
 sheet.getRange(skipRows+2,1,data.length-1,data[0].length).sort(1)
 sheet.getRange(skipRows+1,1,1,data[0].length).setBackground('#00008B').setFontColor('white').setFontWeight('bold')
 sheet.getRange(skipRows+2,5,data.length-1,1).setNumberFormat(currency+"###,###.#")
 sheet.getRange(skipRows+2,6,data.length-1,1).setNumberFormat("##.#%")
 sheet.getRange(skipRows+2,7,data.length-1,1).setNumberFormat("##.#%")
 sheet.getRange(skipRows+2,8,data.length-1,1).setNumberFormat(currency+"###,###.#")
}
function writeOutDict(dict,headers,ss,sheetName,skipRows) {
  var res = [headers]                                                                                          
  for (var key in dict) {
    var newRow = [key].concat(dict[key])
    res.push(newRow)
  }
 writeOut(res,ss,sheetName,skipRows)
}
function toFloat(value) {
  value = replaceAllCommas(value.toString())
  return parseFloat(value);
}
function toInt(value) {
  value = replaceAllCommas(value.toString())
  return parseInt(value);
}
function replaceAllCommas(string) {
 return string.replace(/,/g, '')
}
function replaceAllPlus(string) {
 return string.replace(/\+/g, '') 
}
function getIds() {
  if (campaignNameDoesNotContain == '') {campaignNameDoesNotContain = 'THIS_IS_A_DUMMY_VAR_USED_IF_DOES_NOT_CONTAIN_IS_NOT_BEING_USED'}
 var campaigns = AdWordsApp.campaigns()
  .withCondition('CampaignName CONTAINS "'+campaignNameContains +'"')
  .withCondition('CampaignName DOES_NOT_CONTAIN "'+campaignNameDoesNotContain +'"')
  .get()
 var ids = []
 //Logger.log('There are '+campaigns.totalNumEntities() + ' campaigns which meet the filters')
 while(campaigns.hasNext()) {
  ids.push(campaigns.next().getId()) 
 }
 return ids.join(',')
}
function convertDate(prettyDate) {
 var sp = prettyDate.split('-')
 return [sp[2],sp[1],sp[0]].join('')
}
Tarih:Google AdsGoogle Scripts

İlk Yorumu Siz Yapın

Bir cevap yazın

E-posta hesabınız yayımlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir