Wskazówki dotyczące zapytań i przykładowe zapytania

Sprawdzone metody dotyczące zapytań o wyeksportowane zbiorczo dane

Zawsze używaj funkcji agregacji

Nie gwarantujemy, że dane w tabelach zostaną połączone według daty, adresu URL, witryny czy innej kombinacji kluczy. Aby zapewnić łączenie wartości w wierszach, należy zawsze agregować pola (SUM, COUNT itd.). Na przykład tabela może zawierać kilka wierszy z zapytaniem „grube koty”:

zapytanie     data       typ_wyszukiwania   wyświetlenia ...
grube koty 10-10-2022        SIEĆ          1000
grube koty 10-10-2022        SIEĆ           200
grube koty 10-10-2022     GRAFIKA          5000

Wyszukanie samego hasła „grube koty”, nawet w przypadku podania konkretnej daty i typu wyszukiwania, może zwrócić wiele wierszy. Aby zobaczyć łączną liczbę wyświetleń, musisz zagregować dane w ten sposób:

/* NIEWŁAŚCIWY sposób na wyświetlenie najpopularniejszego zapytania: pojawi się tylko jeden wiersz na zapytanie */
SELECT query, impressions FROM searchconsole.searchdata_site_impression
ORDER BY 2 DESC LIMIT 1;

Wynik:
grube koty   5000

/* WŁAŚCIWY sposób na wyświetlenie najpopularniejszego zapytania */
SELECT query, SUM(impressions) FROM searchconsole.searchdata_url_impression
WHERE query != ''  /* odfiltrowanie zanonimizowanych zapytań */
GROUP BY query ORDER BY 2 DESC LIMIT 1;

Wynik:
grube koty   6200

Ogranicz zapytania do konkretnego zakresu dat, aby obniżyć koszty przetwarzania

Pamiętaj, że uruchomienie zapytania w BigQuery spowoduje naliczenie opłaty, a Twoje tabele mogą nabrać sporych rozmiarów. Dane w wyeksportowanych tabelach są podzielone według dat, co pozwala ograniczyć analizę danych wejściowych tylko do wybranych dni. W przypadku dużych witryn może to wyraźnie obniżyć koszty zapytań. Dobrym sposobem na zminimalizowanie kosztów zapytań jest użycie klauzuli WHERE w celu ograniczenia zakresu dat w tabeli.

Odfiltruj puste ciągi zapytań

Zapytania zanonimizowane często składają się na jedno najczęstsze zapytanie dotyczące witryny. Zanonimizowane zapytanie jest uwzględniane w tabeli jako ciąg znaków o zerowej długości. Aby zobaczyć najpopularniejsze zapytanie dla określonych kryteriów, najlepiej odfiltrować z zapytania SQL wartości zapytań o zerowej długości.

Przykładowe zapytania

Poniżej znajdziesz kilka przykładowych zapytań, które ułatwią Ci korzystanie z danych.

Wyniki według witryny

Wszystkie statystyki internetowe według dnia (ostatnie 2 tygodnie).

SELECT
  data_date AS date,
  sum(impressions) AS impressions,
  sum(clicks) as clicks,
  sum(clicks) / sum(impressions) AS ctr,
  /* poniżej dodano 1, ponieważ pozycja jest liczona od 0 */
  ((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

Najpopularniejsze zapytania w internecie mobilnym w USA według kliknięć (ostatnie 2 tygodnie)

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

Wyniki według adresu URL

Najpopularniejsze adresy URL na kartach Discover według kliknięć (w określonym dniu)

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

Łączne statystyki zapytań i adresów URL w przypadku wyników z elementami rozszerzonymi typu FAQ (ostatnie 2 tygodnie)

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

Cały ruch według adresu URL (ostatnie 2 tygodnie)

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

Wyświetlenia w przypadku zapytań zawierających hasło „twoja_marka” (ostatnie 2 tygodnie)

SELECT
  regexp_contains(query, 'twoja_marka') 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