Window functions para ranking de URLs por categoría
Aplica window functions para rankear URLs dentro de cada categoría o sección del sitio. Enseña el uso de RANK, DENSE_RANK, LAG y funciones de ventana acumulativas para análisis SEO avanzado.
-- Window functions: ranking, comparación y acumulados por categoría
-- Cada función de ventana añade una perspectiva analítica diferente
WITH urls_categorias AS (
SELECT
url,
-- Extraer la categoría del primer segmento del path
REGEXP_EXTRACT(url, r'https?://[^/]+/([^/]+)') AS categoria,
SUM(clicks) AS clics,
SUM(impressions) AS impresiones
FROM
`your-project.your_dataset.search_data`
WHERE
data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY) AND CURRENT_DATE()
GROUP BY
url
HAVING
clics >= 5
)
SELECT
url,
categoria,
clics,
-- Ranking dentro de cada categoría
RANK() OVER (PARTITION BY categoria ORDER BY clics DESC) AS ranking,
-- Total de clics de la categoría
SUM(clics) OVER (PARTITION BY categoria) AS clics_categoria,
-- Porcentaje de clics respecto a su categoría
ROUND(clics * 100.0 / SUM(clics) OVER (PARTITION BY categoria), 2) AS pct_categoria,
-- Diferencia con la URL anterior en el ranking
clics - LAG(clics, 1, 0) OVER (
PARTITION BY categoria ORDER BY clics DESC
) AS dif_con_anterior,
-- Acumulado de clics dentro de la categoría
SUM(clics) OVER (
PARTITION BY categoria ORDER BY clics DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS clics_acumulados
FROM
urls_categorias
WHERE
categoria IS NOT NULL
QUALIFY
ranking <= 5
ORDER BY
categoria, rankingExplicación paso a paso
- 7 REGEXP_EXTRACT obtiene el primer segmento del path como categoría (blog, producto, servicio, etc.).
- 24 RANK asigna un ranking por clics dentro de cada categoría. Valores iguales reciben el mismo rango.
- 26 SUM con OVER sin ORDER BY calcula el total de la partición completa (todos los clics de la categoría).
- 28 Calcula el porcentaje de contribución de cada URL respecto al total de su categoría.
- 30 LAG accede al valor de clics de la fila anterior en el ranking para calcular la diferencia.
- 34 SUM con ROWS BETWEEN calcula un acumulado progresivo de clics dentro de la categoría.
- 42 QUALIFY es una cláusula de BigQuery que filtra resultados de window functions sin necesidad de un CTE adicional.
Ejemplo de resultado esperado
| url | categoria | clics | ranking | clics_categoria | pct_categoria | dif_con_anterior | clics_acumulados |
|---|---|---|---|---|---|---|---|
| https://ejemplo.com/blog/guia-seo | blog | 876 | 1 | 2345 | 37.36 | 876 | 876 |
| https://ejemplo.com/blog/tutorial | blog | 543 | 2 | 2345 | 23.16 | -333 | 1419 |
| https://ejemplo.com/tools/analyzer | tools | 654 | 1 | 1234 | 53.00 | 654 | 654 |
| https://ejemplo.com/tools/checker | tools | 345 | 2 | 1234 | 27.96 | -309 | 999 |
Variaciones y adaptaciones
Para usar DENSE_RANK en lugar de RANK (sin saltar posiciones en empates), reemplazar RANK por DENSE_RANK. Para ver más de 5 URLs por categoría, ajustar el valor en QUALIFY ranking <= 5. Para añadir la media móvil de clics, usar AVG(clics) OVER (PARTITION BY categoria ORDER BY clics DESC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW).