URLs con buen CTR en GSC pero bajo engagement en GA4

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 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67

-- URLs con buen CTR en GSC pero bajo engagement en GA4
-- Detecta desajuste entre snippet y contenido
WITH gsc_data AS (
  SELECT
    url,
    SUM(clicks) AS clics_gsc,
    SUM(impressions) AS impresiones,
    ROUND(SAFE_DIVIDE(SUM(clicks), SUM(impressions)) * 100, 2) AS ctr
  FROM
    `your-project.searchconsole.searchdata_url_impression`
  WHERE
    data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY) AND CURRENT_DATE()
    AND search_type = 'WEB'
  GROUP BY
    url
  HAVING
    clics_gsc >= 20
    AND ctr >= 5.0
),
ga4_engagement AS (
  SELECT
    REGEXP_EXTRACT(
      (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'),
      r'(https?://[^?#]+)'
    ) AS url_normalizada,
    COUNT(
      DISTINCT CONCAT(
        user_pseudo_id, '.',
        CAST(
          (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')
        AS STRING)
      )
    ) AS sesiones,
    COUNTIF(
      (SELECT value.string_value FROM UNNEST(event_params)
        WHERE key = 'session_engaged') = '1'
    ) AS sesiones_engaged,
    ROUND(AVG(
      (SELECT value.int_value FROM UNNEST(event_params)
        WHERE key = 'engagement_time_msec')
    ) / 1000, 2) AS tiempo_medio_seg
  FROM
    `your-project.analytics_XXXXXXXXX.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY))
      AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
    AND traffic_source.medium = 'organic'
    AND event_name IN ('session_start', 'user_engagement')
  GROUP BY
    url_normalizada
)
SELECT
  g.url,
  g.clics_gsc,
  g.ctr AS ctr_gsc,
  e.sesiones AS sesiones_ga4,
  ROUND(SAFE_DIVIDE(e.sesiones_engaged, e.sesiones) * 100, 2) AS engagement_rate,
  e.tiempo_medio_seg
FROM
  gsc_data g
  INNER JOIN ga4_engagement e ON g.url = e.url_normalizada
WHERE
  -- Engagement rate por debajo del 40% indica bajo engagement
  SAFE_DIVIDE(e.sesiones_engaged, e.sesiones) < 0.40
ORDER BY
  g.clics_gsc DESC
LIMIT 30