Nguyên tắc truy vấn và truy vấn mẫu

Các phương pháp hay nhất đối với việc truy vấn xuất dữ liệu hàng loạt

Luôn sử dụng hàm tổng hợp

Search Console không đảm bảo hợp nhất được dữ liệu trong các bảng theo ngày, URL, trang web hoặc bất cứ tổ hợp mã khoá nào. Do đó, bạn luôn phải tổng hợp các trường (bằng hàm SUM, COUNT, v.v.) để đảm bảo việc hợp nhất các giá trị trong hàng. Ví dụ: một bảng có thể có nhiều hàng với cụm từ tìm kiếm "meo beo":

query     date       search_type   impressions ...
meo beo 10-10-2022      WEB          1000
meo beo 10-10-2022      WEB           200
meo beo 10-10-2022    IMAGE          5000

Nếu chỉ tìm kiếm theo cụm từ "meo beo", thì ngay cả đối với một ngày và một thể loại tìm kiếm cụ thể cũng có thể dẫn đến nhiều hàng. Vì vậy, bạn nên tổng hợp như sau để xem tổng số lượt hiển thị của mình:

/* Cách SAI để xem cụm từ tìm kiếm phổ biến nhất: sẽ chỉ nhận được 1 hàng cho mỗi cụm từ tìm kiếm */
SELECT query, impressions FROM searchconsole.searchdata_site_impression
ORDER BY 2 DESC LIMIT 1;

Kết quả:
meo beo   5000

/* Cách ĐÚNG để xem cụm từ tìm kiếm phổ biến nhất: */
SELECT query, SUM(impressions) FROM searchconsole.searchdata_url_impression
WHERE query != ''  /* Lọc bỏ các cụm từ tìm kiếm ẩn danh */
GROUP BY query ORDER BY 2 DESC LIMIT 1;

Kết quả:
meo beo   6200

Giới hạn cụm từ tìm kiếm theo ngày để tiết kiệm chi phí xử lý

Hãy nhớ rằng hệ thống sẽ tính phí cho truy vấn mà bạn chạy trên BigQuery, và các bảng của bạn có thể mở rộng ra khá nhiều. Các bảng đã xuất dữ liệu được phân vùng theo ngày. Điều này cho phép giới hạn việc quét dữ liệu đầu vào một cách hiệu quả: chỉ quét những ngày mà bạn quan tâm. Đối với những trang web lớn, điều này có thể giúp tiết kiệm đáng kể chi phí truy vấn. Một cách hay để giảm thiểu chi phí truy vấn là sử dụng mệnh đề WHERE để giới hạn phạm vi ngày trong bảng được phân vùng theo ngày.

Lọc bỏ chuỗi truy vấn rỗng

Cụm từ tìm kiếm ẩn danh thường là cụm từ tìm kiếm phổ biến nhất đối với một trang web. Cụm từ tìm kiếm ẩn danh được báo cáo dưới dạng chuỗi có độ dài bằng 0 trong bảng. Nếu muốn xem cụm từ tìm kiếm phổ biến nhất theo một số tiêu chí cụ thể, bạn nên lọc bỏ các giá trị truy vấn có độ dài bằng 0 khỏi truy vấn SQL.

Truy vấn mẫu

Dưới đây là một số truy vấn mẫu để giúp bạn thiết lập và sử dụng dữ liệu của mình.

Kết quả theo trang web

Tất cả số liệu thống kê trang web, theo ngày (2 tuần qua)

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

Các cụm từ tìm kiếm hàng đầu trên web dành cho thiết bị di động ở Hoa Kỳ theo số lượt nhấp (2 tuần qua)

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

Kết quả theo URL

URL hàng đầu trên Khám phá theo số lượt nhấp (vào một ngày cụ thể)

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

Số liệu thống kê tổng truy vấn+URL cho kết quả nhiều định dạng Câu hỏi thường gặp (2 tuần qua)

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

Tất cả lưu lượng truy cập của bạn theo URL (2 tuần qua)

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

Số lượt hiển thị đối với cụm từ tìm kiếm có chứa "your_brand" ("thương hiệu của bạn") (2 tuần qua)

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