Categorizar keywords por intención usando CASE WHEN

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44

-- Categorizar keywords por intención de búsqueda
-- Los patrones LIKE deben adaptarse al idioma y nicho del sitio
SELECT
  query AS keyword,
  SUM(clicks) AS clics,
  SUM(impressions) AS impresiones,
  CASE
    -- Intención transaccional: el usuario quiere comprar o contratar
    WHEN LOWER(query) LIKE '%comprar%'
      OR LOWER(query) LIKE '%precio%'
      OR LOWER(query) LIKE '%contratar%'
      OR LOWER(query) LIKE '%descuento%'
      OR LOWER(query) LIKE '%oferta%'
      THEN 'Transaccional'
    -- Intención comercial: el usuario investiga antes de comprar
    WHEN LOWER(query) LIKE '%mejor%'
      OR LOWER(query) LIKE '%comparar%'
      OR LOWER(query) LIKE '%vs%'
      OR LOWER(query) LIKE '%alternativa%'
      OR LOWER(query) LIKE '%review%'
      THEN 'Comercial'
    -- Intención navegacional: el usuario busca un sitio o marca
    WHEN LOWER(query) LIKE '%login%'
      OR LOWER(query) LIKE '%iniciar sesion%'
      OR LOWER(query) LIKE '%pagina oficial%'
      THEN 'Navegacional'
    -- Intención informacional: el usuario busca aprender
    WHEN LOWER(query) LIKE '%como%'
      OR LOWER(query) LIKE '%que es%'
      OR LOWER(query) LIKE '%tutorial%'
      OR LOWER(query) LIKE '%guia%'
      OR LOWER(query) LIKE '%ejemplo%'
      THEN 'Informacional'
    ELSE 'Sin clasificar'
  END AS intencion
FROM
  `your-project.your_dataset.search_data`
WHERE
  data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY) AND CURRENT_DATE()
GROUP BY
  keyword, intencion
ORDER BY
  clics DESC
LIMIT 100