Diretrizes e exemplos de consultas

Práticas recomendadas para consultas de exportação de dados em massa

Sempre usar funções de agregação

Não há garantia de que os dados nas tabelas serão consolidados por data, URL, site ou qualquer combinação de chaves. Portanto, sempre agregue os campos (SUM, COUNT e assim por diante) para garantir a consolidação dos valores das linhas. Por exemplo, uma tabela pode ter várias linhas com a consulta "fat cats" (gatos gordos):

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

Simplesmente pesquisar a consulta "fat cats", mesmo para uma data e um tipo de pesquisa específicos, pode resultar em várias linhas. Portanto, agregue da seguinte maneira para ver a soma das impressões:

/* Maneira INCORRETA de ver a principal consulta: verá apenas 1 linha por consulta */
SELECT query, impressions FROM searchconsole.searchdata_site_impression
ORDER BY 2 DESC LIMIT 1;

Result:
fat cats   5000

/* Maneira CORRETA de ver a principal consulta */
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

Limitar as consultas por data para reduzir os custos de processamento

Não se esqueça de que, ao executar uma consulta no BigQuery, você vai receber uma cobrança por ela, e as tabelas podem ficar bastante grandes. As tabelas exportadas são particionadas por data, o que permite a limitação eficiente da verificação de entrada para apenas dias de interesse. Para sites grandes, isso pode gerar uma economia real no custo de consulta. Uma boa maneira de minimizar os custos de consulta é usar uma cláusula WHERE para limitar o período na tabela particionada por data.

Filtrar strings de consulta nulas

As consultas anônimas geralmente são a consulta mais comum de um site. Uma consulta anônima é relatada como uma string de tamanho zero na tabela. Se você quiser ver a principal consulta por critérios específicos, deve filtrar valores de consulta de tamanho zero da consulta SQL.

Exemplos de consultas

Veja abaixo alguns exemplos de consultas para começar a trabalhar com os dados.

Resultados por site

Todas as estatísticas da Web por dia (últimas duas semanas)

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

Principais consultas da Web para dispositivos móveis nos EUA por cliques (últimas duas semanas)

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

Resultados por URL

Principais URLs do Discover por cliques (em um dia específico)

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

Total de estatísticas de consulta + URL da pesquisa aprimorada de perguntas frequentes (últimas duas semanas)

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

Todo o tráfego por URL (últimas duas semanas)

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

Impressões para consultas que têm "your_brand" (últimas duas semanas)

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

Isso foi útil?

Como podemos melhorá-lo?