Directrices de consultas y consultas de ejemplo

Prácticas recomendadas para consultas de exportación de datos en bloque

Usar siempre funciones de agregación

No se garantiza que los datos de las tablas se consoliden por fecha, URL, sitio o cualquier combinación de teclas. Por lo tanto, siempre debes agregar los campos (SUM, COUNT, etc.) para asegurarte de que los valores de la fila estén consolidados. Por ejemplo, una tabla podría tener varias filas con la consulta "gatos gordos":

query     date       search_type   impressions ...
gatos gordos 10-10-2022      WEB          1000
gatos gordos 10-10-2022      WEB           200
gatos gordos 10-10-2022    IMAGE          5000

Si buscas solo la consulta "gatos gordos", incluso por una fecha y un tipo de búsqueda específicos, se pueden obtener varias filas. Por lo tanto, debes agregar datos como se indica a continuación para ver la suma de tus impresiones:

/* Forma ERRÓNEA de ver la consulta más popular: solo se obtiene 1 fila por consulta */
SELECT query, impressions FROM searchconsole.searchdata_site_impression
ORDER BY 2 DESC LIMIT 1;

Resultado:
gatos gordos   5000

/* Forma CORRECTA de ver la consulta más popular */
SELECT query, SUM(impressions) FROM searchconsole.searchdata_url_impression
WHERE query != ''  /* Filtra las consultas anonimizadas */
GROUP BY query ORDER BY 2 DESC LIMIT 1;

Resultado:
gatos gordos   6200

Limitar consultas por fecha para ahorrar costes de procesamiento

Recuerda que ejecutar una consulta en BigQuery te supondrá cargos que pueden llegar a ser bastante elevados. Las tablas exportadas se dividen por fecha, lo que permite limitar de forma eficiente la búsqueda de entrada únicamente a los días de interés. En el caso de los sitios de gran tamaño, puede suponer un ahorro real en el coste de las consultas. Una buena forma de minimizar los costes de consulta es usar una cláusula WHERE para limitar el intervalo de fechas en la tabla con particiones por fecha.

Excluir cadenas de consulta nulas

Las consultas anonimizadas suelen ser la única consulta más común de un sitio. Las consultas anonimizadas se muestran como una cadena de longitud cero en la tabla. Si quieres ver la consulta más popular de criterios específicos, probablemente te interese filtrar los valores de longitud cero de tu consulta de SQL.

Consultas de ejemplo

A continuación, se incluyen algunas consultas de ejemplo que te ayudarán a empezar a utilizar los datos.

Resultados por sitio

Todas las estadísticas web, por día (últimas dos semanas)

SELECT
  data_date AS date,
  sum(impressions) AS impressions,
  sum(clicks) as clicks,
  sum(clicks) / sum(impressions) AS ctr,
  /* Se añade abajo, ya que la posición es de base 0 */
  ((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

Principales consultas web para móviles de EE. UU. por clics (últimas dos 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

Principales URLs de Discover por clics (en un día concreto)

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 estadísticas de consultas y URLs de resultados enriquecidos de preguntas frecuentes (últimas dos 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 el tráfico por URL (últimas dos 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

Impresiones de las consultas que contienen "tu_marca" (últimas dos semanas)

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