Linee guida per le query ed esempi di query

Best practice per le query sull'esportazione collettiva dei dati

Utilizzare sempre le funzioni di aggregazione

Non è garantito che i dati nelle tabelle vengano raggruppati per data, URL, sito o qualsiasi combinazione di chiavi. Pertanto, devi sempre aggregare i campi (SUM, COUNT e così via) per garantire che i valori delle righe vengano raggruppati. Ad esempio, una tabella potrebbe avere più righe con la query "gatti buffi":

query     date       search_type   impressions ...
gatti buffi 10-10-2022      WEB          1000
gatti buffi 10-10-2022      WEB           200
gatti buffi 10-10-2022    IMAGE          5000

La semplice ricerca della query "gatti buffi", anche per una data e un tipo di ricerca specifici, può risultare in più righe. Dovresti quindi aggregare i dati come segue per vedere la somma delle impressioni:

/* Il modo SBAGLIATO per vedere la query più cercata: genera solo 1 riga per query */
SELECT query, impressions FROM searchconsole.searchdata_site_impression
ORDER BY 2 DESC LIMIT 1;

Risultato:
gatti buffi   5000

/* Il modo GIUSTO per vedere la query più cercata */
SELECT query, SUM(impressions) FROM searchconsole.searchdata_url_impression
WHERE query != ''  /* Ometti le query anonimizzate */
GROUP BY query ORDER BY 2 DESC LIMIT 1;

Risultato:
gatti buffi   6200

Limitare le query per data per risparmiare sui costi di elaborazione

Ricorda che quando esegui una query su BigQuery ti verrà addebitato un costo e le tabelle potranno diventare molto grandi. Le tabelle esportate sono partizionate in base alla data, il che consente di limitare in modo efficiente la scansione di input ai soli giorni di interesse. Per i siti di grandi dimensioni, questo può tradursi in risparmi reali nei costi delle query. Un buon modo per ridurre al minimo i costi delle query è utilizzare una clausola WHERE per limitare l'intervallo di date nella tabella partizionata.

Omettere le stringhe di query con valore null

Le query anonimizzate costituiscono spesso la query più comune di un sito. Una query anonimizzata viene riportata come stringa di lunghezza zero nella tabella. Se vuoi vedere la query più cercata per criteri specifici, probabilmente devi omettere i valori delle query di lunghezza zero dalla query SQL.

Query di esempio

Di seguito sono riportati alcuni esempi di query che consentono di iniziare a utilizzare i dati.

Risultati per sito

Tutte le statistiche web, per giorno (ultime due settimane)

SELECT
  data_date AS date,
  sum(impressions) AS impressions,
  sum(clicks) as clicks,
  sum(clicks) / sum(impressions) AS ctr,
  /* Aggiunto uno di seguito, perché la posizione è basata su zero */
  ((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

Principali query web mobile per Stati Uniti per clic (ultime due settimane)

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

Risultati per URL

Principali URL Discover per clic (in un giorno specifico)

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

Statistiche totali delle query e degli URL per i risultati avanzati di domande frequenti (ultime due settimane)

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

Tutto il traffico per URL (ultime due settimane)

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

Impressioni per le query contenenti "your_brand" (ultime due settimane)

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