Richtlijnen voor zoekopdrachten en voorbeeldzoekopdrachten

Best practices voor zoekopdrachten voor bulksgewijze gegevensexports

Gebruik altijd de aggregatiefuncties

De gegevens in de tabellen kunnen niet gegarandeerd worden geconsolideerd op basis van datum, URL, site of een combinatie van sleutels. Voeg daarom altijd de velden (zoals SUM of COUNT) samen om te zorgen dat je rijwaarden worden geconsolideerd. Een tabel kan bijvoorbeeld meerdere rijen hebben met de zoekopdracht '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

Je kunt al meerdere rijen krijgen als je alleen maar zoekt op 'fat cats', zelfs op een specifieke datum en met een specifiek zoektype. Verzamel als volgt om de som van je vertoningen weer te geven:

/* GEEN goede manier om de populairste zoekopdracht weer te geven: levert maar 1 rij per zoekopdracht op */
SELECT query, impressions FROM searchconsole.searchdata_site_impression
ORDER BY 2 DESC LIMIT 1;

Result:
fat cats   5000

/* De JUISTE manier om de populairste zoekopdracht weer te geven */
SELECT query, SUM(impressions) FROM searchconsole.searchdata_url_impression
WHERE query != ''  /* Filter geanonimiseerde zoekopdrachten weg */
GROUP BY query ORDER BY 2 DESC LIMIT 1;

Result:
fat cats   6200

Beperk zoekopdrachten op datum om verwerkingskosten te besparen

Als je een zoekopdracht uitvoert in BigQuery, worden hiervoor kosten in rekening gebracht. De tabellen kunnen vrij groot worden. De geëxporteerde tabellen zijn gepartitioneerd op basis van datums. Hierdoor kunnen de invoerscans efficiënt worden beperkt tot alleen interessante dagen. Voor grote sites kan dit veel kosten besparen. Een goede manier om de zoekopdrachtkosten te minimaliseren is door een WHERE-clausule te gebruiken om de periode in de op datum gepartitioneerde tabel te beperken.

Filter lege tekenreeksen voor zoekopdrachten weg

Geanonimiseerde zoekopdrachten zijn vaak de meest voorkomende zoekopdracht voor een site. Een geanonimiseerde zoekopdracht staat als een tekenreeks van 0 tekens in de tabel. Als je wilt weten wat de populairste zoekopdracht voor specifieke criteria is, wil je waarschijnlijk zoekopdrachtwaarden van 0 tekens uit je SQL-zoekopdracht filteren.

Voorbeelden van zoekopdrachten

Hieronder staan enkele voorbeelden van zoekopdrachten om aan de slag te gaan met de gegevens.

Resultaten per site

Alle webstatistieken, per dag (afgelopen 2 weken)

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

Meestvoorkomende zoekopdrachten voor mobiel internet in de VS op basis van klikken (afgelopen 2 weken)

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

Resultaten per URL

Beste Discover-URL's op basis van klikken (op een specifieke dag)

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

Totale statistieken voor zoekopdrachten + URL's voor uitgebreide resultaten voor veelgestelde vragen (afgelopen 2 weken)

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

Al het verkeer per URL (afgelopen 2 weken)

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

Vertoningen voor zoekopdrachten die 'je_merk' bevatten (afgelopen 2 weken)

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