BigQuery + GSC: consultas que tu agencia no ejecuta

La interfaz de Search Console te limita a 1.000 filas por reporte y agrupa los datos de forma que oculta mas de lo que muestra. Cuando conectas GSC a BigQuery via Bulk Data Export, el juego cambia: pasas a tener acceso a millones de filas de impresion, query, pagina y dispositivo, sin muestreo. La mayoria de las agencias se queda en el panel por defecto y entrega un informe con grafico de lineas. El problema no es falta de datos, es falta de SQL. Este post trae consultas que corren en produccion y responden preguntas que el panel no puede, del tipo: que paginas estan perdiendo impresiones mes a mes para queries de fondo de embudo?

Antes de cualquier consulta, ajusta el setup. Activa Bulk Data Export en Settings > Bulk Data Export y apunta a un proyecto GCP con billing activo. Las tablas que importan son searchdata_site_impression y searchdata_url_impression. La primera tiene queries anonimizadas filtradas, optima para tendencias agregadas; la segunda tiene granularidad por URL. Costos? Un sitio con 5M de impresiones/mes genera unos 200MB/dia, lo que da menos de un dolar/mes en storage y queries on-demand cuidadosas. Antes de meterte al SQL, garantiza que tu tracking on-page este limpio, como muestra Como auditar SEO on-page sin caer en conjeturas, si no vas a correlacionar basura.

Consulta 1: content decay con ventana movil de 28 dias. Usa LAG() sobre una agregacion semanal por URL para detectar paginas que cayeron mas del 30% en clicks vs la ventana anterior, filtrando por minimo 100 clicks/semana para cortar ruido. El resultado es una lista priorizada de reescritura, infinitamente mejor que abrir el panel y mirar flechas rojas. Cruza con la hoja de Content decay: como identificar posts que estan perdiendo trafico y ya tienes backlog para el trimestre. Para decidir reescribir vs rehacer desde cero, Reescribir o rehacer: la decision basada en datos de SERP tiene el framework por datos de SERP que encaja directo en este pipeline.

Consulta 2: striking distance keywords en posicion 8-20 con CTR por debajo del benchmark. Une searchdata_url_impression con una CTE de benchmark por posicion (usa los numeros de Benchmark de CTR por posicion: datos actualizados de 2026 o calcula de tu propio sitio). Cuando una URL aparece en posicion 11 con CTR de 0,8% y el benchmark es 2,1%, tienes un problema de title o meta description, no de ranking. Esa es la consulta que paga al consultor sola: genera lista de 50-200 paginas donde tocar el title en un sprint se convierte en clicks al mes siguiente. Combina con los patrones de Title tags que convierten: 7 patrones probados en SERPs reales.

Consulta 3: canibalizacion real, no la de Ahrefs. Agrupa por query y cuenta URLs distintas con mas de 10 impresiones en la misma semana. Si una query tiene 3+ URLs apareciendo, tienes canibalizacion verdadera (con senal de demanda), no solo dos paginas con palabra parecida. Exporta a CSV y cruza con canonical tags reales via crawl de Screaming Frog. El veinte por ciento de los casos son canonical mal configurado, ver Canonical tags: errores comunes que sangran trafico organico. El resto es decision editorial: consolidar, redireccionar 301 o reposicionar una de las paginas para otra intencion.

Consulta 4: branded vs non-branded con REGEXP_CONTAINS. Crea una columna calculada clasificando query como brand cuando coincide con tu regex de marca (incluye erratas comunes). Agrupa por mes y veras lo que muchas agencias esconden: 60% del trafico organico de muchos clientes es brand search, o sea, SEO no lo trajo. Esa consulta salva relaciones y justifica inversion honesta, alineado con KPIs de SEO honesto: mas alla del ranking y el trafico y Atribucion SEO: probando ROI sin el ultimo clic. Muestralo al CFO antes que pregunte.

Consulta 5: intent mismatch usando patrones de query. Clasifica queries por intencion (informacional, transaccional, navegacional, comparativa) con CASE WHEN sobre regex simple ("como", "mejor", "precio", "vs", "review"). Cruza con el tipo de pagina (PLP, PDP, blog, home) y descubres paginas de producto rankeando para query informacional o viceversa. Eso es oro para e-commerce, como detalla On-page para e-commerce: PLP vs PDP sin canibalizar. Para fundamentar la clasificacion, Intencion de busqueda: 4 tipos y como mapearla en la SERP tiene la taxonomia que uso.

Consulta 6: page-query fingerprint para detectar cambios de algoritmo. Guarda un snapshot semanal de las top 20 queries por URL. Cuando el conjunto cambia mas del 40% en una semana sin cambios on-page, Google reinterpreto la pagina. Solo descubres eso con BigQuery, el panel nunca te lo dira. Takeaway practico: agenda esas seis consultas en Scheduled Queries de BigQuery, materializa en tablas semanales, conecta a Looker Studio y tienes un observatorio que cuesta menos de 5 dolares/mes y supera cualquier plataforma de 500 dolares. SQL no es lujo de agencia grande, es el piso de quien toma SEO en serio.