Sorgu yönergeleri ve örnek sorgular

Toplu veri dışa aktarma sorguları için en iyi uygulamalar

Her zaman toplama işlevlerini kullanın

Tablolardaki verilerin tarihe, URL'ye, siteye veya herhangi bir anahtar kombinasyonuna göre birleştirilmesi garanti edilmez. Bu nedenle, satır değerlerinizin birleştirilmesi için her zaman alanlarınızı (SUM, COUNT vb.) toplamalısınız. Örneğin, bir tabloda "şişman kediler" sorgusunu içeren birkaç satır olabilir:

sorgu          tarih       arama_türü   gösterim sayısı ...
şişman kediler 10-10-2022      web          1000
şişman kediler 10-10-2022      web           200
şişman kediler 10-10-2022    görsel          5000

"Şişman kediler" sorgusunun sadece belirli bir tarih ve arama türü için bile yapılması birden çok satırla sonuçlanabilir. Dolayısıyla, gösterim sayınızın toplamını görmek için aşağıdaki gibi toplama yapmanız gerekir:

/* En popüler sorguyu görmenin KÖTÜ yolu: Sorgu başına yalnızca 1 satır alır */ 
SELECT query, impressions FROM searchconsole.searchdata_site_impression
ORDER BY 2 DESC LIMIT 1;

Sonuç:
şişman kediler   5000

/* En popüler sorguyu görmenin DOĞRU yolu */ 
SELECT query, SUM(impressions) FROM searchconsole.searchdata_url_impression
WHERE query != ''  /* Anonimleştirilmiş sorguları filtreleyerek dışarıda bırakın */
GROUP BY query ORDER BY 2 DESC LIMIT 1;

Sonuç:
şişman kediler   6200

İşleme maliyetlerinden tasarruf etmek için sorguları tarihe göre sınırlama

BigQuery'de bir sorgu çalıştırdığınızda bu sorgu için ücret ödeyeceğinizi ve tablolarınızın oldukça büyüyebileceğini unutmayın. Dışa aktarılan tablolar tarihe göre bölümlere ayrılır. Bu da, giriş taramasının yalnızca ilgili günlerle sınırlandırılmasına olanak tanır. Büyük sitelerde bu, sorgu maliyetinde ciddi düzeyde tasarruflar sağlayabilir. Sorgu maliyetlerini en aza indirmenin iyi bir yolu, tarihe göre bölümlendirilmiş tablodaki tarih aralığını sınırlamak için bir WHERE ifadesi kullanmaktır.

Boş sorgu dizelerini filtreleyerek dışarıda bırakma

Anonimleştirilmiş sorgular genellikle bir site için en yaygın tek sorguyu oluşturur. Anonimleştirilmiş bir sorgu, tabloda sıfır uzunlukta bir dize olarak raporlanır. Belirli ölçütler için en popüler sorguyu görmek isterseniz SQL sorgunuzdan sıfır uzunlukta sorgu değerlerini filtreleyerek dışarıda bırakabilirsiniz.

Örnek sorgular

Aşağıda, verileri kullanmaya başlamanıza yardımcı olacak bazı örnek sorgular verilmiştir.

Siteye göre sonuçlar

Güne göre tüm web istatistikleri (son iki hafta)

SELECT
  data_date AS date,
  sum(impressions) AS impressions,
  sum(clicks) as clicks,
  sum(clicks) / sum(impressions) AS ctr,
  /* Konum sıfır tabanlı olduğundan bir eklendi */
  ((sum(sum_top_position) / sum(impressions)) + 1.0) AS avg_position

FROM searchconsole.searchdata_site_impression
WHERE search_type = 'WEB'
  AND data_date between DATE_SUB(CURRENT_DATE(), INTERVAL 14 day) and CURRENT_DATE()
GROUP BY data_date

ORDER BY date desc
  LIMIT 1000

Tıklama sayısına göre ABD'de en popüler mobil web sorguları (son iki hafta)

SELECT
  query,
  device,
  sum(impressions) AS impressions,
  sum(clicks) AS clicks,
  sum(clicks) / sum(impressions) AS ctr,
  ((sum(sum_top_position) / sum(impressions)) + 1.0) AS avg_position

FROM searchconsole.searchdata_site_impression
WHERE search_type = 'WEB'
    AND country = 'usa'
    AND device = 'MOBILE'
    AND data_date between DATE_SUB(CURRENT_DATE(), INTERVAL 14 day) and CURRENT_DATE()
GROUP BY 1,2
ORDER BY clicks
LIMIT 1000

URL'ye göre sonuçlar

Tıklama sayısına göre en popüler Keşfet URL'leri (belirli bir günde)

SELECT
  url,
  sum(impressions),
  sum(clicks)
  FROM searchconsole.searchdata_url_impression
WHERE search_type = 'DISCOVER'
  AND data_date = DATE(2022, 02, 01)
GROUP BY url
ORDER BY 3
LIMIT 1000

SSS zengin sonuçları için toplam sorgu+url istatistikleri (son iki hafta)

SELECT
  url,
  query,
  sum(impressions) AS impressions,
  sum(clicks) AS clicks,
  sum(clicks) / sum(impressions) AS ctr,
  ((sum(sum_position) / sum(impressions)) + 1.0) AS avg_position
FROM searchconsole.searchdata_url_impression
WHERE search_type = 'WEB'
  AND is_tpf_faq = true
  AND data_date between DATE_SUB(CURRENT_DATE(), INTERVAL 14 day) and CURRENT_DATE()
GROUP BY 1,2 
ORDER BY clicks
LIMIT 1000

URL'ye göre tüm trafiğiniz (son iki hafta)

SELECT
  url,
  search_type,
  sum(impressions) as impressions,
  sum(clicks) as clicks
FROM searchconsole.searchdata_url_impression
WHERE data_date between DATE_SUB(CURRENT_DATE(), INTERVAL 14 gün) and CURRENT_DATE()
GROUP BY 1,2
ORDER BY 3 desc
LIMIT 1000

"markanız" değerini içeren sorgulara ilişkin gösterim sayısı (son iki hafta)

SELECT
  regexp_contains(query, 'your_brand') AS contains_brand,
  sum(impressions) AS impressions,
  sum(clicks) AS clicks
FROM searchconsole.searchdata_url_impression
WHERE search_type = 'WEB'
  AND data_date between DATE_SUB(CURRENT_DATE(), INTERVAL 14 day) and CURRENT_DATE()
GROUP BY 1
ORDER BY 2 desc
LIMIT 1000