Panduan dan contoh kueri

Praktik terbaik untuk kueri ekspor data massal

Selalu gunakan fungsi agregasi

Data dalam tabel tidak dijamin akan digabungkan berdasarkan tanggal, URL, situs, atau kombinasi kunci apa pun. Oleh karena itu, Anda harus selalu menggabungkan kolom (SUM, COUNT, dan seterusnya) untuk memastikan bahwa nilai barisnya digabungkan. Misalnya, tabel mungkin memiliki beberapa baris dengan kueri "fat cats":

query     date       search_type   impressions ...
fat cats 10-10-2022      WEB          1000
fat cats 10-10-2022      WEB           200
fat cats 10-10-2022    IMAGE          5000

Hanya dengan menelusuri kueri "fat cats", bahkan untuk tanggal dan jenis penelusuran spesifik, beberapa baris dapat dihasilkan. Jadi, Anda harus menggabungkan kolom seperti berikut untuk melihat jumlah tayangan Anda:

/* Cara yang SALAH untuk melihat kueri terpopuler: hanya akan memperoleh 1 baris per kueri */
SELECT query, impressions FROM searchconsole.searchdata_site_impression
ORDER BY 2 DESC LIMIT 1;

Hasil:
fat cats   5000

/* Cara yang BENAR untuk melihat kueri terpopuler */
SELECT query, SUM(impressions) FROM searchconsole.searchdata_url_impression
WHERE query != ''  /* Kecualikan kueri anonim */
GROUP BY query ORDER BY 2 DESC LIMIT 1;

Hasil:
fat cats   6200

Batasi kueri berdasarkan tanggal untuk menghemat biaya pemrosesan

Ingat bahwa Anda akan dikenai biaya saat menjalankan kueri di BigQuery, dan ukuran tabel Anda mungkin bisa menjadi cukup besar. Tabel yang diekspor akan dipartisi menurut tanggal, sehingga memungkinkan pembatasan yang efisien terhadap pemindaian input agar hanya mencakup hari-hari yang diinginkan - bagi situs berukuran besar, hal ini dapat menghemat biaya kueri secara signifikan. Cara yang bagus untuk meminimalkan biaya kueri adalah menggunakan klausa WHERE untuk membatasi rentang tanggal dalam tabel berpartisi tanggal.

Kecualikan string kueri null

Kueri anonim sering kali merupakan satu jenis kueri yang paling umum ditemukan di suatu situs. Kueri anonim dilaporkan sebagai string dengan panjang nol dalam tabel. Jika ingin melihat kueri terpopuler untuk kriteria tertentu, Anda dapat mengecualikan nilai kueri dengan panjang nol dari filter untuk kueri SQL.

Contoh kueri

Berikut beberapa contoh kueri untuk membantu penyiapan dan pemrosesan data Anda.

Hasil berdasarkan situs

Semua statistik web, berdasarkan hari (dua minggu terakhir)

SELECT
  data_date AS date,
  sum(impressions) AS impressions,
  sum(clicks) as clicks,
  sum(clicks) / sum(impressions) AS ctr,
  /* Added one below, because position is zero-based */
  ((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

Kueri web seluler teratas di Amerika Serikat berdasarkan klik (dua minggu terakhir)

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

Hasil berdasarkan URL

URL Discover teratas berdasarkan klik (pada hari tertentu)

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

Total statistik kueri+URL untuk hasil kaya FAQ (dua minggu terakhir)

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

Semua traffic Anda berdasarkan URL (dua minggu terakhir)

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 day) and CURRENT_DATE()
GROUP BY 1,2
ORDER BY 3 desc
LIMIT 1000

Tayangan untuk kueri yang berisi "your_brand" (dua minggu terakhir)

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

Apakah ini membantu?

Bagaimana cara meningkatkannya?