Veri BigQuery

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

Taha YelkenciTaha Yelkenci
Kurucu, SEO ve GEO Danışmanı
· güncelleme: 9 dk okuma
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. Search Console, sitenin Google arama sonuçlarındaki varlığını izlemeye, sorunlarını gidermeye yarayan ücretsiz bir Google hizmetidir. 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.

ÖzellikPerformans raporu (arayüz)Search Analytics APIToplu veri dışa aktarma
Satır sınırıTablo ile indirilen dosyada 1.000 satırArama türü başına günde 50.000 satırGünlük satır sınırından etkilenmez
Veri geçmişiSon 16 ay16 ayı aşmak için veriyi kendiniz saklarsınızKurulum gününden başlar, süre tanımlamazsanız süresiz saklanır
Anonimleştirilmiş sorgularTabloda yok, grafik toplamına dahilSatır olarak dönmezSorgu metni boş satırlar
Sorgulama biçimiFiltre ve karşılaştırmaFiltre, sıralama, toplama türü. Serbest SQL yokSQL

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 siteler için gereklidir. 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 (dataset) 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.

Veri kümesi adı, özelleştirseniz de her zaman searchconsole ile başlar. Birden çok mülkün verisini aynı projeye aktaracaksanız her mülke ayrı bir veri kümesi adı verin, çünkü iki mülk aynı veri kümesine yazamaz. Ayar sayfasını doğrudan toplu veri dışa aktarma ayarı adresinden de açabilirsiniz. Projede geçerli bir faturalandırma yapılandırması yoksa izinleri verseniz bile yazma başarısız olur.

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.

AlanAnlamı
data_dateVerinin ait olduğu gün (Pasifik saati). İki tablonun bölüm tarihidir.
site_urlMülkün adresi. Alan adı mülkünde sc-domain:example.com biçimindedir.
urlYalnız URL tablosunda. Kullanıcının tıklamadan sonra ulaştığı adres.
queryKullanıcının sorgusu. Anonimleştirilmiş satırda sıfır uzunlukta dizedir.
is_anonymized_queryNadir sorguları işaretleyen mantıksal alan.
countrySorgunun yapıldığı ülke, ISO-3166-1-Alpha-3 biçiminde.
search_typeWeb, görsel, video, haberler, Keşfet ya da Google Haberler.
deviceSorgunun yapıldığı cihaz.
impressions, clicksSatırın gösterim sayısı ile tıklama sayısı.
sum_top_positionYalnız site tablosunda. Sitenin her gösterimdeki en üst konumunun toplamı, sıfır tabanlı.
sum_positionYalnız URL tablosunda. URL'nin en üst konumunun toplamı, sıfır tabanlı.

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. Anonimleştirmenin amacı sorguyu yapan kullanıcıların gizliliğini korumaktır. 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. BigQuery fiyatlandırma sayfasına göre her ay depolamanın ilk 10 GiB'ı ile sorgularda işlenen verinin ilk 1 TiB'ı ücretsizdir. İ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;

Search Console verisi başka kaynaklarla nasıl birleştirilir?

Search Console verisi başka kaynaklarla BigQuery'de JOIN ile birleştirilir, ortak anahtar olarak sayfa adresi ile tarih kullanılır. Google Analytics 4 (GA4), ham etkinlik verisini her gün BigQuery'ye aktarabilir. Google Ads verisi BigQuery Data Transfer Service ile planlı aktarımlarla taşınır. Bir tarama çıktısını ya da sunucu günlüğünü de tablo olarak yükleyip URL tablosuna bağlayabilirsiniz. Googlebot isteklerinin günlükten nasıl okunduğunu sunucu günlüklerinin SEO analizi yazımda anlattım.

Birleştirmeden önce iki farkı giderin. Search Console tablolarındaki data_date Pasifik saatine göredir, GA4'ün event_date alanı ise mülkün saat dilimine göre yazılır. Adreslerin yazımı da eşleşmelidir, çünkü sondaki eğik çizgi ya da parametre farkı olan iki adres JOIN koşulunda ayrı değer sayılır.

BigQuery sorgu sonuçları nasıl kaydedilir?

BigQuery sorgu sonuçları, BigQuery Studio'da sorguyu çalıştırdıktan sonra kaydetme seçeneğiyle (İngilizce arayüzde Save results) kaydedilir. BigQuery Studio, Cloud Console'da SQL yazdığınız sayfadır ve console.cloud.google.com/bigquery adresinden açılır. Üç kayıt yolunun Google belgesindeki sınırları şunlardır.

  • Yerel dosya. Sonuç CSV ya da satır ayrımlı JSON olarak iner, yerel CSV dosyası en fazla 10 MB olur.
  • Google E-Tablolar ya da Google Drive. Drive'a kaydedilen sonuç kümesi en fazla 1 GB olmalıdır.
  • BigQuery tablosu. 1 GB'ı aşan sonuç kalıcı bir tabloya yazılabilir.

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.

Toplu veri dışa aktarma hataları nasıl giderilir?

Toplu veri dışa aktarma hataları, ayar sayfasında görünen hata adına göre giderilir. Hataların tam listesi Google'ın dışa aktarmayı yönetme belgesindedir. Aşağıdaki tablo kurulumdan doğan beş hatanın çözümünü verir.

HataÇözüm
Cloud projesi mevcut değilProje kimliğini IAM ayarları sayfasından ya da proje adresinden kopyalayın.
Cloud projesinde izinler eksikHizmet hesabına iki BigQuery rolünü verin.
Cloud projesinde fatura bilgileri eksikProjeye geçerli bir faturalandırma yapılandırması ekleyin.
Bu hedef veri kümesi için başka bir toplu veri dışa aktarma işlemi mevcutHer mülk için ayrı veri kümesi ya da ayrı proje seçin.
Şema uyuşmazlığıTabloya yanlışlıkla eklenen sütunu silin.

Düzeltmeden sonra ayar sayfasındaki Raporu test et düğmesi proje adını, erişim ayarlarını sınar. Başarılı test yeni bir dışa aktarma başlatmaz.

Toplu veri dışa aktarma nasıl durdurulur?

Toplu veri dışa aktarma, ayar sayfasındaki Dışa aktarma işlemini devre dışı bırak düğmesiyle durdurulur. Aktarım 24 saat içinde durur, bu sürede bir aktarım daha gelebilir. Mevcut tablolar silinmez.

Taha Yelkenci

Taha Yelkenci

SEO'ya 2010 yılında başladı. rankZup kurucusu. Sorunuz mu var? Bana yazın →

Yorumlar

İlk yorumu siz yazın

Yorum yazın

Yorumlar onaylandıktan sonra yayınlanır. Adınız yorumunuzla birlikte görünür, e-posta adresi istenmez.

Adınız ile yorumunuz yalnız yayın için saklanır. IP adresiniz spam koruması için tek yönlü özet olarak tutulur, 90 gün sonra silinir. Bağlantı, reklam ya da konu dışı içerik taşıyan yorumlar yayınlanmaz.