Valor por keyword combinando clics GSC y revenue 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

-- Valor monetario por keyword: cruce de clics GSC con revenue GA4
-- Requiere que GA4 registre event_value o un parámetro de revenue
WITH gsc_keywords AS (
  SELECT
    query AS keyword,
    url,
    SUM(clicks) AS clics_gsc,
    SUM(impressions) AS impresiones
  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'
    AND is_anonymized_query = FALSE
  GROUP BY
    keyword, url
  HAVING
    clics_gsc >= 5
),
ga4_revenue AS (
  SELECT
    REGEXP_EXTRACT(
      (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'),
      r'(https?://[^?#]+)'
    ) AS url_normalizada,
    COUNT(*) AS conversiones,
    SUM(
      IFNULL(
        (SELECT value.double_value FROM UNNEST(event_params) WHERE key = 'value'),
        0
      )
    ) AS revenue_total
  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 ('purchase', 'in_app_purchase')
  GROUP BY
    url_normalizada
  HAVING
    revenue_total > 0
)
SELECT
  g.keyword,
  g.url,
  g.clics_gsc,
  g.impresiones,
  r.conversiones,
  ROUND(r.revenue_total, 2) AS revenue,
  ROUND(SAFE_DIVIDE(r.revenue_total, g.clics_gsc), 2) AS valor_por_clic,
  ROUND(SAFE_DIVIDE(r.revenue_total, r.conversiones), 2) AS valor_por_conversion
FROM
  gsc_keywords g
  INNER JOIN ga4_revenue r ON g.url = r.url_normalizada
ORDER BY
  revenue DESC
LIMIT 50