Calculated fields avanzados para métricas SEO custom

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 45 46 47 48

-- Query base para Looker Studio con datos brutos para calculated fields
-- Los campos calculados se crean en Looker Studio > Resource > Manage added data sources > Add a field
SELECT
  url,
  REGEXP_EXTRACT(url, r'https?://[^/]+/([^/]+)') AS seccion,
  query AS keyword,
  data_date AS fecha,
  SUM(clicks) AS clics,
  SUM(impressions) AS impresiones,
  ROUND(SUM(sum_top_position + impressions) / SUM(impressions), 2) AS posicion_media,
  -- Datos adicionales para calculated fields en Looker Studio
  COUNT(DISTINCT query) AS keywords_url,
  SUM(CASE WHEN device = 'MOBILE' THEN clicks ELSE 0 END) AS clics_mobile,
  SUM(CASE WHEN device = 'DESKTOP' THEN clicks ELSE 0 END) AS clics_desktop
FROM
  `your-project.searchconsole.searchdata_url_impression`
WHERE
  data_date BETWEEN @DS_START_DATE AND @DS_END_DATE
  AND search_type = 'WEB'
  AND is_anonymized_query = FALSE
GROUP BY
  url, seccion, keyword, fecha
HAVING
  clics >= 1;

-- Calculated Fields para crear en Looker Studio:
--
-- 1. CTR (%):
--    clics / impresiones * 100
--
-- 2. SEO Score (0-100): combina posición, CTR y volumen
--    (CASE
--      WHEN posicion_media <= 3 THEN 40
--      WHEN posicion_media <= 10 THEN 25
--      WHEN posicion_media <= 20 THEN 10
--      ELSE 0
--    END)
--    + (clics / impresiones * 100) * 2
--    + LEAST(clics / 10, 20)
--
-- 3. Indice de oportunidad: impresiones sin clic
--    impresiones - clics
--
-- 4. Ratio mobile/desktop:
--    clics_mobile / (clics_mobile + clics_desktop) * 100
--
-- 5. Eficiencia de contenido: clics por keyword
--    clics / keywords_url