Search Console Verisi BigQuery'ye Nasıl Aktarılır?

Search Console toplu veri dışa aktarma (bulk data export), bir mülkün performans verisini her gün Google BigQuery'deki tablolara yazan bir Search Console özelliğidir. BigQuery, Google Cloud'un yapılandırılmış sorgu dili (SQL) ile sorgulanan veri ambarıdır. Aktarılan tablolar, anonimleştirilmiş sorguların metni dışında mülk için Search Console'da bulunan performans verisinin tamamını içerir.

Performans raporunun arayüzde nasıl okunduğunu kurucusu olduğum rankZup'ın blogundaki Search Console kullanım rehberinde anlattık. Bu yazı aynı verinin BigQuery'ye aktarılmasını, ardından SQL ile sorgulanmasını adım adım gösterir.

Toplu veri dışa aktarma arayüzden ve API'den nasıl ayrılır?

Toplu veri dışa aktarma, arayüzden ve uygulama programlama arayüzünden (API) satır sınırı, veri geçmişi ile sorgulama biçimi yönünden ayrılır. Performans raporunun tablosu en fazla 1.000 satır gösterir. Search Analytics API, mülk başına her arama türü için günde en fazla 50.000 satır döndürür. Google iki sınırı performans verisi sınırları yazısında açıklar. Arayüzdeki tarih filtresi en fazla son 16 ayı gösterir.

Aşağıdaki tablo üç erişim yolunu dört özellikte karşılaştırır.

Toplu dışa aktarma kurulum gününden önceki veriyi getirmez. Geçmiş dönem için Google, API'yi ya da raporları kullanmanızı söyler.

BigQuery aktarımı kimler için gerekli, kimler için gereksizdir?

BigQuery aktarımı en çok on binlerce sayfası olan ya da günde on binlerce sorgudan trafik alan sitelerin işine yarar. Google, Şubat 2023'teki duyurusunda bu iki site tipini andı. Küçük ve orta büyüklükteki siteler aynı duyuruya göre verinin tamamına arayüzden, Data Studio bağlayıcısından ya da API'den ulaşır.

Aktarımı kurmanızı gerektiren dört durum şunlardır.

• Arayüzdeki 1.000 satır ya da API'deki günlük 50.000 satır sınırına takılıyorsunuz.
• 16 aydan eski veriye ihtiyacınız var.
• Search Console verisini site taraması ya da satış verisi gibi başka kaynaklarla birleştireceksiniz.
• Uzun kuyruklu sorguları analiz edeceksiniz.

Dört durumdan hiçbiri sizde yoksa arayüz ile API yeterlidir. Farklı araçların verisini tek tabloda birleştirmeyi SEO'da veri analizi yazımda anlattım.

Search Console verisi BigQuery'ye nasıl aktarılır?

Search Console verisi BigQuery'ye iki aşamada aktarılır: önce Google Cloud projesi hazırlanır, sonra Search Console'da hedef tanımlanır. Kurulumu yalnız mülk sahibi yapabilir. Aşağıdaki yedi adım Google'ın toplu veri dışa aktarma kurulum belgesindeki sırayı izler. Menü ile rol adları belgenin Türkçe sürümündeki gibidir.

1. Google Cloud Console'u açın, faturalandırması etkin bir proje oluşturun ya da verinin aktarılacağı projeye geçin.
2. Kenar çubuğundan API'ler ve Hizmetler > Etkin API'ler ve Hizmetler sayfasına gidin. BigQuery etkin değilse + API'LERİ VE HİZMETLERİ ETKİNLEŞTİR düğmesiyle BigQuery API ile BigQuery Storage API'yi etkinleştirin.
3. Kenar çubuğundan IAM ve Yönetici sayfasını açın, + İZİN VER düğmesine tıklayın.
4. Yeni Ana Hesaplar alanına search-console-data-export@system.gserviceaccount.com hizmet hesabını yapıştırın.
5. Hesaba iki rol verin: BigQuery İş Kullanıcısı (bigquery.jobUser) ile BigQuery Veri Düzenleyicisi (bigquery.dataEditor). Kaydet'e tıklayın.
6. Search Console'da Ayarlar > Toplu veri dışa aktarma sayfasını açın. Cloud proje kimliği alanına proje numarasını değil proje kimliğini yazın.
7. Veri kümesi adını ve konumunu seçip Devam'a tıklayın. Varsayılan ad searchconsole olur, konum dışa aktarma başladıktan sonra kolayca değiştirilemez.

Cloud Console'u İngilizce kullanıyorsanız aynı öğeler belgede APIs & Services, IAM and Admin, Grant access, BigQuery Job User, BigQuery Data Editor adlarıyla geçer.

BigQuery'de hangi tablolar ve alanlar oluşur?

BigQuery'de seçtiğiniz veri kümesinin altında üç tablo oluşur: searchdata_site_impression, searchdata_url_impression, ExportLog. İlk tablo performans verisini mülke göre, ikinci tablo URL'ye göre toplanmış olarak tutar. ExportLog her başarılı dışa aktarmanın kaydıdır. Alanların tam listesi Google'ın tablo yönergeleri ve referansı belgesindedir.

Aşağıdaki tablo bu yazıdaki sorguların kullandığı on iki alanı listeler.

URL tablosunda arama görünümü türünü gösteren is_amp_top_stories gibi mantıksal alanlar da bulunur. Google, performans verisinin artımlı biriktiğini, tablolarda aynı anahtarı taşıyan satırlar oluştuğunu belirtir. Her sorguda metrikleri SUM ile toplamanızın nedeni budur.

Anonimleştirilmiş sorgular BigQuery toplamlarını nasıl etkiler?

Anonimleştirilmiş sorgular BigQuery'de sorgu metni boş satırlar olarak yer alır, tıklama ile gösterimleri toplamlara dahildir. Google, iki üç aylık dönemde birkaç düzineden fazla kullanıcının yapmadığı sorguları anonimleştirir. Anonim satırlarda is_anonymized_query alanı true, query alanı sıfır uzunlukta dizedir.

BigQuery toplamları arayüzdeki rakamlardan üç nedenle farklı çıkabilir.

• Arayüzdeki sorgu tablosu anonimleştirilmiş sorguları göstermez, grafik toplamı onları içerir.
• Arayüz tablosu 1.000 satırda durur, grafik toplamı durmaz.
• Site tablosu mülke göre, URL tablosu sayfaya göre sayar. Aynı sorguda iki sayfanız görünürse mülk için tek gösterim, sayfalar için iki gösterim yazılır.

Aşağıdaki örnek sorgu anonim satırların payını gösterir.

SELECT
  is_anonymized_query,
  SUM(clicks) AS total_clicks,
  SUM(impressions) AS total_impressions
FROM `PROJE_KIMLIGI.VERI_KUMESI.searchdata_site_impression`
WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY)
                    AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
  AND search_type = 'WEB'
GROUP BY is_anonymized_query;

Arayüzdeki grafiği site tablosuyla karşılaştırın, çünkü ikisi de mülke göre toplanır.

BigQuery aktarımının maliyeti neye göre oluşur?

BigQuery aktarımının maliyeti iki kalemden oluşur: tablolarda saklanan veri ile sorguların işlediği veri. Google ücretsiz bir kullanım düzeyi tanımlar, bu düzeyi aşan depolama ile sorgu kullanımı ücretlendirilir. İsteğe bağlı fiyatlandırmada BigQuery bir sorguyu okuduğu bayt sayısına göre faturalar.

Depolamayı bölüm (partition) geçerlilik süresi belirler. Tablolar tarihe göre bölümlenir, süre tanımlamazsanız veri süresiz birikir. Google süreyi tabloya değil bölüme koymanızı önerir, çünkü tablo süresi dolduğunda bütün veri silinir. Bölüm süresi 14 gün ya da daha uzun olmalıdır. Sütun eklemek gibi bir şema değişikliği dışa aktarmayı başarısız kılar.

Bölüm süresi Cloud Console arayüzünden güncellenemez, SQL ile ayarlanır. Aşağıdaki örnek komut URL tablosundaki her bölümü 480 gün sonra siler. Yeni süreden eski olan bölümlerin süresi komut çalışınca hemen dolar.

ALTER TABLE `PROJE_KIMLIGI.VERI_KUMESI.searchdata_url_impression`
SET OPTIONS (partition_expiration_days = 480);

Sorgu maliyetini tarih filtresi düşürür. WHERE koşulunda data_date aralığı verdiğinizde BigQuery yalnız eşleşen bölümleri tarar, elenen bölümler taranan bayt hesabına girmez. Cloud Console'daki sorgu doğrulayıcı, sorguyu çalıştırmadan önce okunacak bayt miktarını tahmin eder.

Search Console verisi BigQuery'de SQL ile nasıl sorgulanır?

Search Console verisi BigQuery'de üç kuralla sorgulanır: metrikleri SUM ile toplayın, data_date ile tarih aralığı verin, anonim satırları bilerek dahil edin ya da çıkarın. Üç kural Google'ın sorgu yönergeleri ve örnek sorgular belgesinden gelir. Aşağıdaki dört sorgu örnektir, PROJE_KIMLIGI ile VERI_KUMESI yer tutucudur.

Google'ın örnek sorguları arama türünü 'WEB', cihazı 'MOBILE', ülkeyi 'usa' biçiminde yazar. Kendi tablonuzdaki yazımı tablonun Preview sekmesinden sorgu çalıştırmadan kontrol edin.

Son 28 günde sorgu bazında tıklama ve gösterim

Sorgu bazında tıklama ile gösterim için site tablosu kullanılır. Örnek sorgu dünle biten 28 günün web aramalarını kapsar, anonim satırları dışarıda bırakır.

SELECT
  query,
  SUM(clicks) AS total_clicks,
  SUM(impressions) AS total_impressions
FROM `PROJE_KIMLIGI.VERI_KUMESI.searchdata_site_impression`
WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY)
                    AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
  AND search_type = 'WEB'
  AND is_anonymized_query = FALSE
GROUP BY query
ORDER BY total_clicks DESC
LIMIT 1000;

Çıkan sorgu listesini niyete göre gruplamayı kullanıcı niyeti yazımda anlattım.

Ortalama konumun doğru hesaplanması

Ortalama konum için konum toplamı gösterim toplamına bölünür, sonuca 1 eklenir, çünkü tablodaki konum sıfır tabanlıdır. Alanın adı URL tablosunda sum_position, site tablosunda sum_top_position olur. NULLIF sıfıra bölme hatasını önler.

SELECT
  url,
  SUM(impressions) AS total_impressions,
  SUM(sum_position) / NULLIF(SUM(impressions), 0) + 1 AS avg_position
FROM `PROJE_KIMLIGI.VERI_KUMESI.searchdata_url_impression`
WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY)
                    AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
  AND search_type = 'WEB'
GROUP BY url
ORDER BY total_impressions DESC
LIMIT 1000;

Ülke ve cihaz kırılımı

Ülke ile cihaz kırılımı site tablosunu country ile device alanlarına göre gruplar. Kırılımda anonim satırları tutun, çünkü tıklama ile gösterimleri ülke toplamına aittir. ctr sütunu tıklama oranıdır: tıklama sayısının gösterim sayısına bölümü.

SELECT
  country,
  device,
  SUM(clicks) AS total_clicks,
  SUM(impressions) AS total_impressions,
  SUM(clicks) / NULLIF(SUM(impressions), 0) AS ctr
FROM `PROJE_KIMLIGI.VERI_KUMESI.searchdata_site_impression`
WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY)
                    AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
  AND search_type = 'WEB'
GROUP BY country, device
ORDER BY total_clicks DESC
LIMIT 100;

Gösterimi yüksek tıklama oranı düşük sayfalar

Gösterimi yüksek olup tıklama oranı düşük kalan sayfalar URL tablosundan HAVING koşuluyla süzülür. Sorgudaki 1.000 gösterim ile yüzde 1 tıklama oranı eşikleri örnek değerlerdir, kendi sitenize göre değiştirin.

SELECT
  url,
  SUM(impressions) AS total_impressions,
  SUM(clicks) AS total_clicks,
  SUM(clicks) / NULLIF(SUM(impressions), 0) AS ctr,
  SUM(sum_position) / NULLIF(SUM(impressions), 0) + 1 AS avg_position
FROM `PROJE_KIMLIGI.VERI_KUMESI.searchdata_url_impression`
WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY)
                    AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
  AND search_type = 'WEB'
GROUP BY url
HAVING SUM(impressions) >= 1000
   AND SUM(clicks) / NULLIF(SUM(impressions), 0) < 0.01
ORDER BY total_impressions DESC
LIMIT 500;

BigQuery verisi Data Studio'da nasıl görselleştirilir?

BigQuery verisi Data Studio'da BigQuery bağlayıcısıyla görselleştirilir: bağlayıcı bir tabloyu, bir görünümü ya da özel SQL sorgusunu okur. Data Studio, Nisan 2026'ya kadar Looker Studio adını taşıyan raporlama aracıdır. Google Data Studio adına dönüşü Nisan 2026'da duyurdu.

Data Studio'da rapor oluşturun, BigQuery bağlayıcısını seçin, ardından projeyi, veri kümesini, tabloyu belirleyin. Tarihe göre bölümlenmiş tabloda bölüm sütununu ana tarih filtresi yapabilirsiniz. Google'a göre bölümlenmiş tablo grafikleri daha hızlı açar, sorgu maliyetini en aza indirir.

Data Studio'nun doğrudan Search Console bağlayıcısı API ile aynı günlük 50.000 satır sınırına tabidir, BigQuery bağlayıcısı dışa aktarılan tabloları okur. Search Console'un ölçümde kullanılan raporlarını SEO kılavuzumun ölçüm bölümünde listeledim. Toplu dışa aktarma yalnız performans verisini taşır. Yapay zeka cevaplarındaki görünürlüğü yapay zeka görünürlüğü ölçümü yazımda ele aldım.

BigQuery'de en sık yapılan Search Console sorgu hataları nelerdir?

BigQuery'de en sık yapılan Search Console sorgu hataları aşağıdaki beş hatadır.

• Konumun ortalamasının ortalamasını almak. Günlük ya da satır bazındaki ortalamaların ortalaması her satıra eşit ağırlık verir. Konum toplamını gösterim toplamına bölüp 1 ekleyin.
• Tarih filtresi koymamak. data_date koşulu olmayan sorgu bütün bölümleri tarar.
• Anonim satırları hesaba katmamak. Sorgu listesinde bırakılan boş satır ilk sıraya çıkabilir. Toplamdan çıkarılan boş satır tıklama ile gösterimi eksik bırakır.
• Site ile URL tablolarını karıştırmak. İki tablo gösterimi farklı sayar, konum alanlarının adı da farklıdır. Sorgu ve ülke analizinde site tablosunu, sayfa analizinde URL tablosunu kullanın.
• Metrikleri toplamadan okumak. Aynı sorgu, tarih, arama türü birden çok satıra yayılabilir. SUM kullanmayan sorgu toplamı değil tek tek satırları döndürür.

Toplu veri dışa aktarmanın çalıştığı nasıl kontrol edilir?

Toplu veri dışa aktarmanın çalıştığı iki yerden kontrol edilir: Search Console'daki ayar sayfası ile BigQuery'deki ExportLog tablosu. Ayarlar sayfası Toplu veri dışa aktarma ayarının yanında son denemenin durumunu gösterir. İlk dışa aktarma, başarılı yapılandırmadan sonraki 48 saat içinde gerçekleşir ve yapıldığı günün verisini içerir.

ExportLog yalnız başarılı dışa aktarmaları kaydeder, bu yüzden her gün için veri tablosu başına bir satır, toplam iki satır bekleyin. Aşağıdaki örnek sorgu son kayıtları listeler.

SELECT
  namespace,
  data_date,
  epoch_version,
  publish_time
FROM `PROJE_KIMLIGI.VERI_KUMESI.ExportLog`
ORDER BY publish_time DESC
LIMIT 20;

namespace verinin yazıldığı tabloyu, publish_time aktarımın bittiği zamanı gösterir. epoch_version 0 ile başlar, Search Console o günün verisini her güncellediğinde 1 artar.

Başarısız denemeler ExportLog tablosuna yazılmaz. Geçici olmayan bir hatada Search Console mülk sahiplerine ve tam kullanıcılara e-posta gönderir, hatayı ayar sayfasında gösterir. Search Console bir günün verisini yaklaşık bir hafta boyunca yeniden dener. Başarısız denemeler yaklaşık bir ay sürerse dışa aktarmayı tamamen durdurur.