Funciones de agregación (SUM, COUNT, AVG) con ejemplos SEO

De filas a métricas

Los datos brutos en BigQuery vienen como filas individuales: cada evento de GA4 es una fila, cada impresión de GSC es otra fila. Para convertir esas filas en información útil («cuántas sesiones tuvo esta página», «cuántos clics generó esta keyword») se usan funciones de agregación.

Las funciones de agregación toman un conjunto de valores y devuelven un solo resultado. Siempre se usan en combinación con GROUP BY (que define cómo agrupar las filas) o solas (cuando se quiere un total general).

COUNT: contar filas

COUNT es la función más utilizada en análisis SEO. Tiene dos variantes:

-- Contar todas las filas
SELECT COUNT(*) AS total_eventos
FROM `your-project.analytics_XXXXXXXXX.events_*`
WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', CURRENT_DATE())

-- Contar valores únicos (eliminar duplicados)
SELECT COUNT(DISTINCT user_pseudo_id) AS usuarios_unicos
FROM `your-project.analytics_XXXXXXXXX.events_*`
WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', CURRENT_DATE())

COUNT(*) cuenta todas las filas, incluyendo duplicados. COUNT(DISTINCT columna) cuenta solo valores únicos. En SEO, la diferencia es fundamental: COUNT(*) en eventos cuenta eventos totales (un usuario puede generar muchos), mientras que COUNT(DISTINCT user_pseudo_id) cuenta usuarios reales.

En BigQuery existe también COUNTIF(condicion), que cuenta solo las filas donde se cumple una condición:

-- Contar sesiones engaged y calcular engagement rate
SELECT
  COUNTIF(session_engaged = '1') AS sesiones_engaged,
  COUNT(*) AS sesiones_totales,
  ROUND(COUNTIF(session_engaged = '1') / COUNT(*) * 100, 2) AS engagement_rate
FROM sesiones_organicas

SUM: sumar valores

SUM suma todos los valores numéricos de una columna. Es esencial para métricas acumulativas:

-- Total de clics e impresiones por URL
SELECT
  url,
  SUM(clicks) AS clics_totales,
  SUM(impressions) AS impresiones_totales
FROM `your-project.searchconsole.searchdata_url_impression`
WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY) AND CURRENT_DATE()
GROUP BY url
ORDER BY clics_totales DESC

Un detalle importante: SUM ignora los valores NULL. Si hay filas con NULL en la columna de clics, no afectan al resultado (no se suman como cero ni generan error).

AVG: calcular promedios

AVG calcula el promedio aritmético de una columna. Útil para métricas como el tiempo medio de engagement:

-- Tiempo medio de engagement por landing page orgánica
SELECT
  page_location,
  ROUND(AVG(engagement_time_msec) / 1000, 2) AS tiempo_medio_seg
FROM eventos_organicos
GROUP BY page_location
HAVING COUNT(*) >= 10
ORDER BY tiempo_medio_seg DESC

La cláusula HAVING filtra después de la agregación: en este caso, excluye páginas con menos de 10 registros para evitar promedios engañosos con muestras muy pequeñas.

La posición media de GSC no se calcula con AVG simple. Requiere la fórmula SUM(sum_top_position + impressions) / SUM(impressions) porque las posiciones están ponderadas por impresiones. Este detalle se explica en el artículo de conexión de GSC a BigQuery.

MIN y MAX: extremos

MIN y MAX devuelven el valor mínimo y máximo de una columna:

-- Primera y última fecha de datos disponibles
SELECT
  MIN(data_date) AS fecha_inicio,
  MAX(data_date) AS fecha_fin,
  DATE_DIFF(MAX(data_date), MIN(data_date), DAY) AS dias_disponibles
FROM `your-project.searchconsole.searchdata_site_impression`

Son útiles para validar rangos de datos, encontrar la fecha más reciente de una tabla, o detectar el valor más alto de una métrica.

SAFE_DIVIDE: dividir sin errores

Aunque no es una función de agregación en sentido estricto, SAFE_DIVIDE aparece constantemente junto a ellas. Calcula una división y devuelve NULL (en lugar de un error) si el divisor es cero:

-- CTR seguro (sin error si impresiones = 0)
SELECT
  query,
  SUM(clicks) AS clics,
  SUM(impressions) AS impresiones,
  ROUND(SAFE_DIVIDE(SUM(clicks), SUM(impressions)) * 100, 2) AS ctr
FROM `your-project.searchconsole.searchdata_site_impression`
GROUP BY query

Sin SAFE_DIVIDE, una keyword con cero impresiones provocaría un error de división por cero que detendría toda la consulta.

Combinar funciones de agregación

Las funciones de agregación se combinan libremente en el SELECT. Una query típica de SEO puede usar varias a la vez:

SELECT
  url,
  SUM(clicks) AS clics,
  SUM(impressions) AS impresiones,
  ROUND(SAFE_DIVIDE(SUM(clicks), SUM(impressions)) * 100, 2) AS ctr,
  ROUND(SUM(sum_top_position + impressions) / SUM(impressions), 2) AS posicion_media,
  COUNT(DISTINCT query) AS keywords_distintas
FROM `your-project.searchconsole.searchdata_url_impression`
WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY) AND CURRENT_DATE()
GROUP BY url
HAVING clics >= 10
ORDER BY clics DESC
LIMIT 50

Esta consulta es similar a la query de URLs con más clics en GSC del catálogo: combina SUM, SAFE_DIVIDE, ROUND y COUNT(DISTINCT) para obtener un panorama completo del rendimiento de cada URL.

Consideraciones de rendimiento con funciones de agregación

Las funciones de agregación procesan todas las filas que cumplen las condiciones del WHERE antes de devolver el resultado. Esto significa que, en tablas con millones de registros, una agregación mal filtrada puede consumir una cantidad considerable de recursos. Aplicar filtros de fecha y condiciones específicas antes de agregar es fundamental para mantener los costos bajo control en BigQuery.

También conviene saber que COUNT(DISTINCT columna) es significativamente más costoso que COUNT(columna) o COUNT(*), ya que requiere identificar y eliminar duplicados. En tablas muy grandes, si no se necesita una precisión exacta, BigQuery ofrece la función APPROX_COUNT_DISTINCT(columna), que devuelve un conteo aproximado con un margen de error inferior al 1% pero con un consumo de recursos mucho menor. Para la mayoría de los análisis SEO, esa aproximación es más que suficiente.

Siguiente paso

Las funciones de agregación necesitan GROUP BY para saber cómo agrupar las filas. Sin GROUP BY, las funciones devuelven un solo valor para toda la tabla. Con GROUP BY, devuelven un valor por cada grupo (por URL, por día, por keyword, por dispositivo).