Top 100 keywords por clics en los últimos 28 días
Obtiene las 100 keywords con más clics en los últimos 28 días. Permite conocer los términos que generan mayor volumen de tráfico orgánico real al sitio.
-- Top 100 keywords por clics en los últimos 28 días
-- Fuente: searchdata_site_impression (datos a nivel de propiedad, incluye queries anónimas)
SELECT
query AS keyword,
SUM(clicks) AS clics,
SUM(impressions) AS impresiones,
ROUND(SAFE_DIVIDE(SUM(clicks), SUM(impressions)) * 100, 2) AS ctr,
-- Posición media real: sum_top_position es 0-indexed, se suma impressions para convertir a 1-indexed
ROUND(SUM(sum_top_position + impressions) / SUM(impressions), 2) AS posicion_media
FROM
`your-project.searchconsole.searchdata_site_impression`
WHERE
data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY) AND CURRENT_DATE()
AND search_type = 'WEB'
AND is_anonymized_query = FALSE
GROUP BY
keyword
ORDER BY
clics DESC
LIMIT 100Explicación paso a paso
- 4 El campo query contiene el término de búsqueda tal como lo escribió el usuario en Google.
- 7 CTR calculado como clics dividido entre impresiones, usando SAFE_DIVIDE para evitar división por cero.
- 9 La posición media en el export de GSC a BigQuery es 0-indexed. Se suma impressions a sum_top_position para convertir a la escala 1-indexed que muestra la interfaz de GSC.
- 14 Filtra por search_type = 'WEB' para excluir búsquedas de imágenes, videos y noticias.
- 15 Excluye queries anónimas que GSC agrupa cuando el volumen es bajo para proteger privacidad.
Ejemplo de resultado esperado
| keyword | clics | impresiones | ctr | posicion_media |
|---|---|---|---|---|
| consultas sql bigquery | 1243 | 8765 | 14.18 | 3.20 |
| bigquery ga4 tutorial | 987 | 12340 | 8.00 | 5.40 |
| seo sql queries | 876 | 6543 | 13.39 | 2.80 |
| google analytics bigquery | 654 | 9876 | 6.62 | 7.10 |
Variaciones y adaptaciones
Para incluir queries anónimas en el conteo total, eliminar el filtro is_anonymized_query. Para filtrar por un directorio específico, añadir AND url LIKE 'https://ejemplo.com/blog/%' (requiere usar searchdata_url_impression). Para ver solo keywords informacionales, añadir AND query LIKE '%como%' OR query LIKE '%que es%'.