Google E-Tablolar ve GSC API ile Çoklu Site Raporlamasını Otomatikleştirin

SEO'da çalışmak, eminim hepinizin bir noktada karşılaştığı ilginç zorluklara yol açar.

Esneklik ve sıkıcı görevleri yönetme konusunda uzmansınız. Son zamanlarda kendimi 100'den fazla üst düzey siteyle uğraşırken buldum.

Küresel şirketlerle çalışmak oldukça karmaşıktır:

• 100'den fazla site için verileri tartışın.
• Her sitenin performansını takip edin.

Ve bu sitelerden bazıları Google'ın ilk sayfasında birbirleriyle rekabet ettiğinden, Site 1'in trafiğinin düşmesi ancak Site 2'nin bu kaybı karşılaması oldukça olasıdır.

Bir sitenin Google Arama Konsolunu (GSC) kontrol etmek kolaydır, ancak küresel ölçekte yüzlerce sitede bu işlem yoğundur.

Ne yapabilirsin?

Küresel raporlamayı günler veya haftalar alabilen zorlu bir görevden birkaç dakika süren bir göreve dönüştürmek için GSC'nin API'sine bağlanan bir Google E-Tablolar Uygulama Komut Dosyası tasarladım.

Komut dosyasını oluşturduktan sonra kolayca bir tarih aralığı girip her sitenin bilgilerini alabilirim:

• Tıklamalar ve gösterimler.
• Anahtar kelimeler.
• Ortalama sıralamalar.
• Vesaire.

Yüzlerce siteyi yönettiğimiz için, yukarıda belirtildiği gibi kullanıcıların sitelerimizden birine giderek alışveriş yapmaları alışılmadık bir durum değil.

Genel olarak bakıldığında, büyük resim, tek bir sitenin performansından daha önemlidir.

Sana göstereceğim şey benim 10 adımlı süreç çeken bir komut dosyası oluşturmak için tıklamalar ve gösterimler ve ardından bunu tüm yıl boyunca (Yıllık Yıl) karşılaştırır.

Yüzlerce Siteyi Raporlamak İçin Google E-Tablolar Uygulama Komut Dosyası Oluşturmak İçin 10 Adımlı Süreç

1. Adım: Google E-Tablolarınızı Oluşturma

İlk adımınız orijinal Google E-Tablolar dosyanızı oluşturmaktır. Bunu şu adımları izleyerek yapabilirsiniz:

• Google Drive'a gidin.
• Dosyaları yerleştirmek istediğiniz klasöre gidin.
• Sağ tık arka planda
• > Google E-Tablolar > Boş E-Tablo'yu seçin.

Dosyayı yeniden adlandırmak isteyeceksiniz. Benimkini “Global Search Console Raporlaması” olarak adlandırdım.

Artık dosyanız kuruldu ve bir sonraki adıma hazırsınız.

2. Adım: Google E-Tablonuzu Ayarlama

1. Satıra bazı başlıklar ekleyene kadar boş bir sayfa kullanışlı değildir ve kullanıcılar için bir anlam ifade etmeyecektir. Bu sırayla ve kalın harflerle eklemenizi önerdiğim başlıklar şunlardır:

• İnternet sitesi.
• Niş.
• Tıklamalar.
• Gösterimler.
• Yıllık Tıklama Sayısı.
• Yıllık Gösterimler.
• Tıklama Yüzdesi Farkı.
• Gösterim Yüzdesi Farkı.

Dosyanız artık şöyle görünmelidir:

Bir sonraki adımınız, oldukça basit ve anlaşılır olan bir Google Cloud Projesi oluşturmaktır.

3. Adım: Google Cloud Console Veri Projesi Oluşturun

Google, platformunu denemek için 300 ABD doları kredi sağladığından projenizi oluşturmak ücretsiz olmalıdır. Google Cloud'u kullanmadıysanız https://console.cloud.google.com/ adresinde bulabilirsiniz.

Artık şu adımları takip edebilirsiniz:

• Musluk Proje > Yeni Proje'yi seçin.
• Proje Adını girin (örnek: “GSC Veri Projem”).
• Musluk Yaratmak.

• Tıklamak Proje Seçiniz.
• Projenizi seçin.

• En üste tıklayın Aramak çubuk.
• “Google Arama Konsolu API'sı” yazın.”
• Seçme “Google Arama Konsolu API'sı.”
• Tıklamak Olanak vermek.

4. Adım: Google E-Tablolar'da Uygulama Komut Dosyaları Oluşturun

Bu adımda Apps Komut Dosyasını daha önce oluşturduğunuz Google E-Tablosuna entegre etmeye çalışacağız. E-Tabloyu açmanız ve şu adımları uygulamanız gerekir:

• Musluk Uzantılar > Uygulama Komut Dosyası.

Komut dosyasının nasıl çalıştığına ilişkin ayrıntılara girmeyeceğim, ancak bu kodu kopyalayabilirsiniz:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Search Console')
    .addItem('Fetch Data', 'menuItem1')
    .addToUi();
}

function menuItem1() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow(); // Find the last row with data in column A

  // Clear cells C2:F151 before processing data
  sheet.getRange("C2:F151").clearContent();

  for (var i = 2; i <= lastRow; i++) { var siteProperty = sheet.getRange(i, 1).getValue(); var startDateValue = sheet.getRange('M1').getValue(); var endDateValue = sheet.getRange('M2').getValue(); var timeZone = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(); var format = "yyyy-MM-dd"; // Calculate dates for last year var lastYearStartDate = new Date(startDateValue); lastYearStartDate.setFullYear(lastYearStartDate.getFullYear() - 1); var lastYearEndDate = new Date(endDateValue); lastYearEndDate.setFullYear(lastYearEndDate.getFullYear() - 1); var startDate = Utilities.formatDate(lastYearStartDate, timeZone, format); var endDate = Utilities.formatDate(lastYearEndDate, timeZone, format); // Fetch data for the previous year var previousYearResponse = requestSearchConsoleAPI(siteProperty, startDate, endDate); // Fetch data for the current year (unchanged) startDate = Utilities.formatDate(new Date(startDateValue), timeZone, format); endDate = Utilities.formatDate(new Date(endDateValue), timeZone, format); var currentYearResponse = requestSearchConsoleAPI(siteProperty, startDate, endDate); // Process and write data for both years processAndWriteData(sheet, i, previousYearResponse, currentYearResponse); } } function processAndWriteData(sheet, row, previousYearResponse, currentYearResponse) { // Check if response is not defined or null and has at least one row if (previousYearResponse && previousYearResponse.length > 0) {
    var previousYearClicks = 0;
    var previousYearImpressions = 0;

    previousYearResponse.forEach(function(row) {
      previousYearClicks += row.clicks;
      previousYearImpressions += row.impressions;
    });

    sheet.getRange(row, 5).setValue(previousYearClicks); // Write to column D (index 5)
    sheet.getRange(row, 6).setValue(previousYearImpressions); // Write to column E (index 6)
  } else {
    Logger.log('No data found for previous year in row: ' + row);
  }

// Process and write data for the current year
  if (currentYearResponse && currentYearResponse.length > 0) {
    var currentYearClicks = 0;
    var currentYearImpressions = 0;

    currentYearResponse.forEach(function(row) {
      currentYearClicks += row.clicks;
      currentYearImpressions += row.impressions;
    });

    sheet.getRange(row, 3).setValue(currentYearClicks); // Write to column C (index 3)
    sheet.getRange(row, 4).setValue(currentYearImpressions); // Write to column D (index 4)
  } else {
    Logger.log('No data found for current year in row: ' + row);
  }
}



function requestSearchConsoleAPI(siteProperty, startDate, endDate) {

  try {
    const oauthToken = ScriptApp.getOAuthToken(); // Correctly call the method
    const siteUrl = siteProperty;
    const url="https://www.googleapis.com/webmasters/v3/sites/" + encodeURIComponent(siteUrl) + '/searchAnalytics/query';
    const payload = {
      startDate: startDate,
      endDate: endDate,
      type: 'web'
    };

    const headers = {
      'Authorization': 'Bearer ' + oauthToken,
      'Content-Type': 'application/json'
    };
    const options = {
      'method': 'post',
      'contentType': 'application/json', // Consistent content type
      'headers': headers,
      'payload': JSON.stringify(payload),
      'muteHttpExceptions': true
    };

    const response = UrlFetchApp.fetch(url, options);
    const responseCode = response.getResponseCode();
    const contentText = response.getContentText(); // Get response text for logging

  Logger.log('Response Code: ${responseCode}'); // Use backticks
  Logger.log('Response Content: ${contentText}'); // Use backticks


  if (responseCode === 200) {
    const json = JSON.parse(contentText);
    Logger.log(json); // This will log the actual JSON response
    return json.rows; // Adjust this line based on the actual structure of your API response
  } else {
    // Correctly use backticks here for template literals
    const errorMessage="Error fetching data: ${responseCode} - ${contentText}";
    Logger.log(errorMessage);
    throw new Error(errorMessage);
  }

  } catch (e) {
    Logger.log('Error: ${e.toString()}');
    return null;
  }
}

Daha sonra Apps Komut Dosyası projenize geri dönün ve aşağıdakileri yapın:

• Basmak CTRL + A Tümünü seçmek için
• Basmak CTRL + V kopyaladığınız kodu yapıştırmak için.
• Musluk TAMAM.
• Tıklamak Projeyi kaydet.
• Musluk Koşmak.

*Not: Google'dan çok fazla yönlendirme içeren bir Kötü İstek hatası alıyorsanız bunun nedeni birden fazla hesaba giriş yapmış olmanızdır. Yalnızca bir Google hesabının giriş yaptığı bir tarayıcıyı deneyin.

Sizden talep edilecek İzinleri inceleyin ve Google Arama Konsolunuzla ilişkili Google Hesabını seçmeniz gerekecektir.

Uygulama doğrulanmadığı için Google size bir uyarı verecektir; bu nedenle, “Gelişmiş” ayarlama ve ardından “Başlıksız projeye git (güvenli değil).”

Son olarak, simgesine dokunarak veya tıklayarak bu adımı tamamlayabilirsiniz. İzin vermek düğme.

Adım 5: Erişim Kimlik Bilgilerini Ayarlayın

E-Tablolar ile Google Cloud Console arasında pek çok gidip gelme olduğunu biliyorum, ancak bu noktada bu talihsiz bir zorunluluktur. Şimdi Google Cloud Console'a geri dönmenizi gerektirecek Erişim Kimlik Bilgilerini ayarlayacağız.

Not: Sahip olmalısın etkinleştirilmiş önceki adımdan Google Search Console API'sini kullanın.

Ekranınız şöyle görünmeli:

Yapmanız gerekenler:

• Musluk Kimlik Bilgileri > Kimlik Bilgileri Oluştur.
• Musluk OAuth istemci kimliği > İzin Ekranını Yapılandır.

• Tıklamak Harici.
• Musluk Yaratmak.
• Girmek “GSC Verilerim” Uygulama adı olarak.
• Ekle Destek e-postası (GSC için kullanılan e-posta adresiniz).
• Ekle Dgeliştirici iletişim bilgileri (GSC için kullandığınız e-posta).
• Musluk Kaydet ve devam Et.
• Musluk KAPSAM EKLE VEYA KALDIR.
• 2 tanesini kontrol edin Google Arama Konsolu API'sı kapsamlar (sayfa 2'de olabilir).

• Tıklamak Güncelleme.
• Tıklamak Kaydet ve devam Et.
• Şimdi tıklayın Kullanıcı Ekle.

• Tercihen GSC'ye erişimi olan birden fazla kullanıcı ekleyebilirsiniz.
• Kaydet ve devam Et.

6. Adım: GSC Verileri İçin Google Cloud Projesini Kurun

Hâlâ Google Cloud Projesindeyken, hamburger simgesi Ve git Buluta genel bakış > Kontrol Paneli:

Seçmeniz gereken “Proje numarası” yazdığını fark edeceksiniz ve Kopyala basarak CTRL + C.

Apps Komut Dosyası sekmenize geri dönün ve simgesine dokunun. Proje ayarları:

başlıklı bölüme gidin Google Bulut Platformu (GCP) Projesiproje numarasını (CTRL + V) metin kutusuna yapıştırın ve Projeyi ayarlayın.

7. Adım: Google Apps Komut Dosyanızı Yeniden Adlandırın

Artık Apps Komut Dosyanızı şu adrese giderek yeniden adlandırmak isteyeceksiniz: Proje Geçmişi bunun gibi:

Daha sonra şunları yapacaksınız:

• Tıklamak Başlıksız proje ekranın üst kısmında.
• Girmek “GSC Veri Proje Komut Dosyam.”
• Tıklamak Yeniden isimlendirmek.

8. Adım: Code.gs Komut Dosyası İçin Google Apps Bildirim Dosyasını Düzenleyin

Hala senaryonun içinde kalıyorsun ve biz de geri döneceğiz. Proje ayarları tıpkı daha önce yaptığımız gibi.

Bu sefer tıklamak isteyeceksiniz Düzenleyicide “appsscript.json” manifest dosyasını göster Yanında bir onay işareti olduğundan emin olmak için.

Sonra, tıklayın Editör ve şuraya gidin: appsscript.jsonaşağıda görebileceğiniz:

appsscript.json dosyasındaki her şeyi silip aşağıdaki komut dosyasına yapıştırmak isteyeceksiniz:

{
  "timeZone": "America/New_York",
  "dependencies": {
  },
  "exceptionLogging": "STACKDRIVER",
  "oauthScopes": [
    "https://www.googleapis.com/auth/webmasters",
    "https://www.googleapis.com/auth/script.external_request",
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/spreadsheets.currentonly"
  ]
}

Kodu ekledikten sonra, Code.gs dosyalayın ve dokunun Kaydetmek, ve daha sonra Koşmak. İzinleri incelemeniz istenecek ve kullanmaya devam etmek için uygun hesabınızı seçmeniz gerekecek.

Birkaç istemin ardından “GSC Verilerim” uygulamanıza izin vermeniz istenecek ve uygulama başlayacaktır.

Adım 9: Web Sitesi Veri Analizi İçin Tarihleri ​​Ayarlayın

Google E-Tablolar dosyasında aşağıdakileri eklemek isteyeceksiniz:

• L1: Başlangıç ​​tarihi.
• L2: Bitiş tarihi.

Not: Başlangıç ​​ve bitiş tarihleri ​​belirtilmelidir. M1 Ve M2. Örneğin şunları girebilirsiniz:

Not: Tarih formatı sistem ayarlarınıza ve konumunuza göre farklılık gösterebilir.

Adım 10: Boş Olmayan Hücreler İçin Koşullu Biçimlendirmeyi Sıfırdan Küçük Olarak Ayarlayın

Her şey ayarlandı, ancak daha iyi görünmesi için bazı koşullu biçimlendirmeler eklemelisiniz. “Tıklama Yüzdesi Farkı” ve “Gösterim Yüzdesi Farkı” sütunlarına odaklanacağız:

“Tıklama Yüzdesi Farkı” ve “Gösterim Yüzdesi Farkı” başlıkları altındaki satırları seçin ve tıklayın. Biçim > Koşullu biçimlendirme. Altında Kuralları biçimlendir, seçmek isteyeceksiniz Daha az.

“Değer veya formül” metin alanına şunları ekleyebilirsiniz: 0.

Bunun yaptığı şey, eğer 0'dan küçükse, negatif olduğu ve trafik kaybolduğu için rengi kırmızıya çevireceğiz. Bunu boya kutusunun üzerine tıklayıp değiştirerek yapabilirsiniz. kırmızı Bitti'ye tıklamadan önce.

Trafikteki pozitif artışı yeşile çevirmek istiyorsanız başka bir kural ekleyeceksiniz: Şundan büyük: ve şunu ekleyin 0 değer.

G2 ve H2'de kullanılacak formüller şunlardır (bunları her satır için çoğaltabilirsiniz; diğer satırlar için tıklayıp aşağı sürüklemeniz yeterlidir):

=IFERROR(IF(AND(C2<>"",E2<>""), (C2-E2)/E2, ""),"")

=IFERROR(IF(AND(D2<>"",F2<>""), (D2-F2)/F2, ""),"")

Artık birden fazla sitede aynı anda rapor çalıştırmanın kolay bir yoluna sahipsiniz.

İşte bu, Küresel Raporunuz Elinizde

A sütununa Google Arama Konsolu özelliklerinizi girin; bu bir alan mülküyse sc-domain:example.com olarak veya bir URL mülkünü https://example.com olarak ekleyin

Raporu çalıştırmak veya yenilemek için özel menüyü kullanın Search Console > Verileri Al:

*Not: Bu komut dosyası yaklaşık 150 alanı destekler, ancak daha fazlasına ihtiyacınız varsa AppScripts dosyanızdaki 14 numaralı satırı ayarlayabilirsiniz:

sheet.getRange("C2:F151").clearContent();

Bu öğreticiyi kullanarak, günlerce süren veri toplama ve rapor çalıştırma işlemlerini birkaç dakikaya kolayca dönüştürebileceksiniz. Hatta diğer hesaplamaları gerçekleştirmek veya raporunuz için daha fazla veri toplamak amacıyla komut dosyalarını genişletebilirsiniz.

ChatGPT'yi Google E-Tablolar ile Entegrasyon hakkındaki diğer eğitimime göz atın.

Raporlarınızı otomatikleştirmek sıkıcı görevleri kolaylaştırmanın harika bir yoludur ve umarım işinizi biraz daha kolaylaştırır.

Daha fazla kaynak:

Öne Çıkan Resim: 200dgr /Shutterstock