Fechas en BigQuery: la parte que todos odian

Por qué las fechas son complicadas en BigQuery

Las fechas en BigQuery son la fuente más frecuente de errores al empezar con SQL para SEO. Hay dos razones principales: GA4 y GSC almacenan las fechas en formatos distintos, y las tablas de GA4 están particionadas por día (con un sufijo que no es un tipo DATE sino un STRING). Conocer las funciones correctas elimina la mayoría de los problemas.

_TABLE_SUFFIX: filtrar tablas por fecha en GA4

Las tablas de GA4 en BigQuery se nombran events_20260415, events_20260416, etc. El comodín events_* permite consultarlas todas, y _TABLE_SUFFIX es la variable que contiene el sufijo de fecha (como string YYYYMMDD).

-- Últimos 30 días
WHERE _TABLE_SUFFIX BETWEEN
  FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
  AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())

-- Un mes específico
WHERE _TABLE_SUFFIX BETWEEN '20260401' AND '20260430'

-- Un solo día (ayer)
WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))

Usar siempre _TABLE_SUFFIX en las queries de GA4 es una de las buenas prácticas para controlar costos. Sin este filtro, BigQuery escanea todas las tablas del dataset (potencialmente meses o años de datos), consumiendo cuota gratuita innecesariamente.

Tipos de datos de fecha

BigQuery tiene tres tipos de dato temporales que es importante distinguir:

• DATE: solo fecha, sin hora. Ejemplo: 2026-04-15. Es el tipo que usa GSC (data_date).
• TIMESTAMP: fecha con hora, minutos, segundos y zona horaria. Ejemplo: 2026-04-15 14:30:00 UTC.
• STRING: texto que parece fecha pero no lo es. Ejemplo: '20260415'. Es el formato de event_date en GA4.

El campo event_date de GA4 es STRING con formato YYYYMMDD. Para usarlo como fecha real, hay que convertirlo con PARSE_DATE.

Funciones esenciales de fecha

PARSE_DATE: convertir string a fecha

-- Convertir event_date de GA4 (string '20260415') a tipo DATE
PARSE_DATE('%Y%m%d', event_date) AS fecha
-- Resultado: 2026-04-15 (tipo DATE)

FORMAT_DATE: convertir fecha a string

-- Convertir DATE a string con formato personalizado
FORMAT_DATE('%Y%m%d', CURRENT_DATE())  -- '20260415'
FORMAT_DATE('%Y-%m', data_date)         -- '2026-04' (útil para agrupar por mes)

DATE_SUB y DATE_ADD: restar o sumar días

-- Hace 30 días
DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)

-- Hace 3 meses
DATE_SUB(CURRENT_DATE(), INTERVAL 3 MONTH)

-- Dentro de 7 días
DATE_ADD(CURRENT_DATE(), INTERVAL 7 DAY)

INTERVAL acepta DAY, WEEK, MONTH, QUARTER y YEAR.

DATE_DIFF: diferencia entre dos fechas

-- Días entre dos fechas
DATE_DIFF(fecha_fin, fecha_inicio, DAY) AS dias

-- Semanas entre dos fechas
DATE_DIFF(fecha_fin, fecha_inicio, WEEK) AS semanas

DATE_TRUNC: truncar a un periodo

-- Agrupar por semana
DATE_TRUNC(data_date, WEEK) AS semana

-- Agrupar por mes
DATE_TRUNC(data_date, MONTH) AS mes

-- Inicio del trimestre
DATE_TRUNC(data_date, QUARTER) AS trimestre

DATE_TRUNC es especialmente útil para agrupar datos temporales. En lugar de tener una fila por día, se puede agrupar por semana o mes para ver tendencias.

EXTRACT: extraer partes de una fecha

-- Extraer el año
EXTRACT(YEAR FROM data_date) AS anio

-- Extraer el mes
EXTRACT(MONTH FROM data_date) AS mes

-- Extraer el día de la semana (1=domingo, 7=sábado)
EXTRACT(DAYOFWEEK FROM data_date) AS dia_semana

Patrones comunes en queries SEO

Últimos N días dinámico

WHERE data_date BETWEEN
  DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY)
  AND CURRENT_DATE()

Mes actual vs mes anterior

CASE
  WHEN data_date >= DATE_TRUNC(CURRENT_DATE(), MONTH) THEN 'mes_actual'
  ELSE 'mes_anterior'
END AS periodo

Este patrón es el que usa la query de comparativa de canales mes a mes.

Agrupar por día en GA4 (convirtiendo string a DATE)

SELECT
  PARSE_DATE('%Y%m%d', event_date) AS fecha,
  COUNT(*) AS eventos
FROM tabla_eventos
GROUP BY fecha
ORDER BY fecha ASC

Zonas horarias: un detalle que importa

BigQuery trabaja por defecto en UTC, pero GA4 almacena las fechas de evento según la zona horaria configurada en la propiedad. Esto puede provocar discrepancias cuando se comparan datos de GA4 con datos de GSC (que también usa UTC) o cuando se cruzan con fuentes externas que usan otra zona horaria. Si los números de un día concreto no coinciden entre fuentes, la zona horaria suele ser la causa.

Para convertir entre zonas horarias en BigQuery, se utiliza la función DATETIME(timestamp, 'zona_horaria'). Por ejemplo, DATETIME(event_timestamp, 'Europe/Madrid') convierte un timestamp UTC a la hora de España peninsular. Tener claro en qué zona horaria están los datos de cada fuente evita errores de interpretación que pueden invalidar un análisis completo.

Errores frecuentes con fechas

• Comparar DATE con STRING: WHERE data_date = '20260415' falla si data_date es tipo DATE. Usar WHERE data_date = '2026-04-15' o WHERE data_date = DATE('2026-04-15').
• Confundir _TABLE_SUFFIX con event_date: _TABLE_SUFFIX es el nombre de la tabla particionada (string YYYYMMDD). event_date es una columna de la tabla (también string YYYYMMDD pero datos distintos). Ambos suelen coincidir, pero no siempre.
• No usar FORMAT_DATE con _TABLE_SUFFIX: _TABLE_SUFFIX es string, así que las fechas deben convertirse a ese formato con FORMAT_DATE('%Y%m%d', ...).
• Olvidar que CURRENT_DATE() cambia cada día: las queries con CURRENT_DATE() devuelven resultados distintos cada día. Si se necesita un rango fijo, usar fechas literales.

Siguiente paso

Con las cláusulas fundamentales y las funciones de fecha dominadas, el último concepto clave antes de abordar queries complejas es entender la diferencia entre subqueries y CTEs: dos formas de estructurar consultas largas que determinan si una query es legible o es un caos indescifrable.