Pokyny k dotazům a ukázkové dotazy

Doporučené postupy pro dotazy k hromadnému exportu dat

Vždy používejte agregační funkce

Není zaručeno, že data v tabulkách budou konsolidována podle data, adresy URL, webu nebo jakékoli kombinace klíčů. Abyste zajistili konsolidaci hodnot řádků, měli byste pole vždy agregovat (pomocí funkcí SUM, COUNT apod.). V tabulce například může být několik řádků s dotazem „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

Pouhé vyhledání dotazu „fat cats“ (třeba i pro konkrétní datum a typ vyhledávání) může vést k výsledku s několika řádky. Pokud chcete získat celkový počet zobrazení, je výsledky potřeba takto agregovat:

/* NESPRÁVNÝ způsob zobrazení nejpopulárnějšího dotazu: pro každý dotaz vrátí pouze jeden řádek */
SELECT query, impressions FROM searchconsole.searchdata_site_impression
ORDER BY 2 DESC LIMIT 1;

Výsledek:
fat cats   5000

/* SPRÁVNÝ způsob zobrazení nejpopulárnějšího dotazu */
SELECT query, SUM(impressions) FROM searchconsole.searchdata_url_impression
WHERE query != ''  /* Odfiltruje anonymizované dotazy */
GROUP BY query ORDER BY 2 DESC LIMIT 1;

Výsledek:
fat cats   6200

Omezte dotazy podle data, abyste ušetřili na nákladech za zpracování

Mějte na paměti, že když v BigQuery spustíte dotaz, budou vám za to účtovány poplatky, a že vaše tabulky mohou být poměrně velké. Exportované tabulky jsou rozděleny na části podle kalendářních dat, což umožňuje omezit prohledávání vstupních dat pouze na dny, které vás zajímají. U velkých webů to může představovat výraznou úsporu nákladů na dotazy. Dobrý způsob, jak minimalizovat náklady na dotazy, je omezit tabulku rozdělenou podle kalendářních dat na konkrétní období pomocí klauzule WHERE.

Odfiltrujte prázdné řetězce dotazů

Nejběžnějším dotazem pro určitý web jsou často anonymizované dotazy. Anonymizovaný dotaz je v tabulce uveden jako řetězec s nulovou délkou. Pokud chcete zjistit nejpopulárnější dotaz odpovídající určitým kritériím, pravděpodobně z dotazu SQL budete chtít odfiltrovat hodnoty pro dotazy s nulovou délkou.

Ukázkové dotazy

Níže uvádíme ukázkové dotazy, které vám pomohou se s daty seznámit.

Výsledky podle webu

Denní statistiky veškerého vyhledávání webu po dnech (poslední dva týdny)

SELECT
  data_date AS date,
  sum(impressions) AS impressions,
  sum(clicks) as clicks,
  sum(clicks) / sum(impressions) AS ctr,
  /* Níže se přičítá 1, protože pozice začínají nulou */
  ((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

Nejčastější dotazy na mobilním webu v USA podle kliknutí (za poslední dva týdny)

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

Výsledky podle adresy URL

Adresy URL ve feedu Objevit s největším počtem kliknutí (v konkrétní den)

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

Celkové statistiky dotazů a adres URL pro rozšířené výsledky s častými dotazy (poslední dva týdny)

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

Veškerá návštěvnost podle adresy URL (poslední dva týdny)

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

Zobrazení pro dotazy, které obsahují výraz „your_brand“ (poslední dva týdny)

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