Kyselyohjeet ja esimerkkikyselyt

Datan joukkoviennin parhaat käytännöt

Käytä aina koontitoimintoja

Ei ole taattua, että taulukoiden data yhdistetään päivämäärän, URL-osoitteen, sivuston tai muiden tietojen perusteella. Siksi kentät kannattaa aina koota (SUM, COUNT jne.), jotta varmistat, että rivien arvot yhdistetään. Taulukossa voi esimerkiksi olla useita rivejä, joissa on kysely "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

Jos haet vain kyselyä "fat cats" vaikka kyselyllä olisi tietty päivämäärä ja hakutyyppi, tuloksissa voi olla useita rivejä. Jotta näet näyttökertojen kokonaismäärän, kokoaminen kannattaa tehdä seuraavasti:

/* HUONO tapa nähdä suosituin kysely: johtaa vain yhteen riviin kyselyä kohden */
SELECT query, impressions FROM searchconsole.searchdata_site_impression
ORDER BY 2 DESC LIMIT 1;

Result:
fat cats   5000

/* OIKEA tapa nähdä suosituin kysely */
SELECT query, SUM(impressions) FROM searchconsole.searchdata_url_impression
WHERE query != ''  /* Filter out anonymized queries */
GROUP BY query ORDER BY 2 DESC LIMIT 1;

Result:
fat cats   6200

Kyselyjen rajoittaminen päivämäärän mukaan käsittelykulujen alentamiseksi

Muista, että kun suoritat kyselyn BigQueryssa, siitä veloitetaan ja taulukoista voi tulla suuria. Viedyt taulukot on ositeltu päivämäärän mukaan, joten syöte voidaan rajata koskemaan vain haluttuja päiviä. Suurilla sivustoilla tämä voi johtaa suuriin säästöihin kyselykuluissa. Hyvä tapa minimoida kyselykulut on määrittää ajanjakso päivämäärän mukaan ositellussa taulukossa WHERE-lausekkeella.

Tyhjien kyselymerkkijonojen suodattaminen pois

Anonymisoidut kyselyt ovat usein sivuston yleisin kysely. Anonymisoitu kysely raportoidaan taulukossa merkkijonona, jonka pituus on nolla. Jos haluat nähdä suosituimman kyselyn tiettyjen ehtojen puitteissa, sinun kannattaa todennäköisesti suodattaa nolla merkkiä pitkät kyselyarvot pois SQL-kyselystä.

Esimerkkikyselyt

Alla on esimerkkikyselyjä, joiden avulla voit aloittaa datan käytön.

Tulokset sivuston mukaan

Kaikki verkkotilastot päiväkohtaisesti (kaksi viime viikkoa)

SELECT
  data_date AS date,
  sum(impressions) AS impressions,
  sum(clicks) as clicks,
  sum(clicks) / sum(impressions) AS ctr,
  /* Alla oleva lisätty, koska sijoitus on nollapohjainen */
  ((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

Suosituimmat mobiilikyselyt Yhdysvalloissa klikkausten mukaan (kaksi viime viikkoa)

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

Tulokset URL-osoitteen mukaan

Discoverin suosituimmat URL-osoitteet klikkausten mukaan (tiettynä päivänä)

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

Usein kysyttyä ‑rich-tulosten kyselyiden ja URL-osoitteiden kokonaistilastot (kaksi viime viikkoa)

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

Kaikki liikenne URL-osoitteen mukaan (kaksi viime viikkoa)

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

Impressiot hauille, jotka sisältävät tämän: your_brand (kaksi viime viikkoa)

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