Limpiar y normalizar una lista de URLs con SQL
Normaliza una lista de URLs eliminando parámetros de tracking, fragmentos, barras finales duplicadas y forzando minúsculas. Es el paso previo imprescindible antes de cualquier análisis de URLs.
-- Limpiar y normalizar una lista de URLs
-- Funciones compatibles con BigQuery Standard SQL
-- Adaptar nombres de tabla y columna según el origen de datos
SELECT
url_original,
-- Paso 1: Convertir a minúsculas
-- Paso 2: Eliminar fragmento (#...)
-- Paso 3: Eliminar query string (?...)
-- Paso 4: Eliminar barra final duplicada
REGEXP_REPLACE(
REGEXP_REPLACE(
REGEXP_REPLACE(
LOWER(url_original),
r'#.*$', ''
),
r'?.*$', ''
),
r'/$', ''
) AS url_limpia
FROM
`your-project.your_dataset.urls_raw`
GROUP BY
url_original
ORDER BY
url_originalExplicación paso a paso
- 10 Anida tres REGEXP_REPLACE para aplicar las transformaciones en orden: primero lowercase, luego limpieza de fragmentos, query strings y barras finales.
- 13 LOWER convierte toda la URL a minúsculas para evitar duplicados por diferencia de capitalización.
- 14 Elimina el fragmento de la URL (todo lo que va después de #).
- 16 Elimina los parámetros de query string (todo lo que va después de ?), incluyendo UTMs y parámetros de tracking.
- 18 Elimina la barra final para normalizar /pagina/ y /pagina como la misma URL.
- 22 GROUP BY elimina duplicados que resultan de la normalización.
Ejemplo de resultado esperado
| url_original | url_limpia |
|---|---|
| https://Ejemplo.com/Guia-SEO/?utm_source=twitter#intro | https://ejemplo.com/guia-seo |
| https://ejemplo.com/guia-seo/ | https://ejemplo.com/guia-seo |
| https://ejemplo.com/Blog/?page=2&sort=date | https://ejemplo.com/blog |
Variaciones y adaptaciones
Para conservar ciertos parámetros (como ?page= en paginación), usar REGEXP_REPLACE con un patrón más selectivo que solo elimine parámetros UTM. Para extraer solo el path sin dominio, añadir REGEXP_EXTRACT(url_limpia, r'https?://[^/]+(.*)') AS path. Para detectar URLs duplicadas después de la limpieza, envolver en un CTE y agrupar por url_limpia con COUNT(*) > 1.