Pivotar datos de keywords con PIVOT

SQL genérico Reportes recurrentes Avanzado

Transforma datos de keywords organizados por filas en una tabla pivotada con una columna por mes. Permite visualizar la evolución mensual de cada keyword en formato de reporte horizontal.

pivot-keywords.sql 
-- Pivotar clics por keyword y mes usando PIVOT de BigQuery
-- Convierte filas de meses en columnas para formato de reporte
WITH datos_mensuales AS (
  SELECT
    query AS keyword,
    FORMAT_DATE('%Y_%m', data_date) AS mes,
    SUM(clicks) AS clics
  FROM
    `your-project.your_dataset.search_data`
  WHERE
    data_date BETWEEN '2026-01-01' AND '2026-04-30'
  GROUP BY
    keyword, mes
  HAVING
    clics >= 5
)
SELECT *
FROM
  datos_mensuales
  PIVOT (
    SUM(clics) AS clics
    FOR mes IN (
      '2026_01' AS ene,
      '2026_02' AS feb,
      '2026_03' AS mar,
      '2026_04' AS abr
    )
  )
ORDER BY
  IFNULL(clics_abr, 0) DESC
LIMIT 50

Explicación paso a paso

  • 3 El CTE prepara los datos con keyword, mes formateado como string y clics agregados.
  • 6 FORMAT_DATE convierte la fecha a formato año_mes para usarlo como identificador de columna en el PIVOT.
  • 14 Filtra keywords con al menos 5 clics por mes para evitar ruido.
  • 20 PIVOT transforma los valores de la columna 'mes' en columnas individuales.
  • 21 SUM(clics) define la función de agregación aplicada a cada celda de la tabla pivotada.
  • 22 FOR mes IN lista explícitamente los valores que se convertirán en columnas. En BigQuery, estos valores deben ser literales.
  • 30 Ordena por el mes más reciente para ver las keywords con más tráfico actual.

Ejemplo de resultado esperado

keywordclics_eneclics_febclics_marclics_abr
consultas sql seo234287312456
bigquery tutorial187198210345
seo analytics145156134234
google analytics sqlnull2387187

Variaciones y adaptaciones

Para pivotar por semana en lugar de mes, usar FORMAT_DATE('%Y_W%V', data_date) y ajustar los valores del FOR IN. Para incluir impresiones como segunda métrica, añadir otra columna pivotada o crear un segundo PIVOT. Para hacer el PIVOT dinámico (sin listar meses manualmente), es necesario usar SQL dinámico con EXECUTE IMMEDIATE en BigQuery.

Queries relacionadas

Blended data: combinar GSC y GA4 en un solo gráfico

Looker Studio Avanzado

Comparativa orgánico vs otros canales (mes actual vs anterior)

GA4 en BigQuery Avanzado

Comparativa periodo actual vs anterior por URL

GSC en BigQuery Avanzado

Deduplicar registros en una tabla de keywords

SQL genérico Principiante