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