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

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

-- FUENTE 1: Custom Query de GSC para Looker Studio
-- Configurar como fuente de datos independiente
-- SELECT para la fuente GSC:
SELECT
  url AS url_pagina,
  data_date AS fecha,
  SUM(clicks) AS clics_gsc,
  SUM(impressions) AS impresiones_gsc,
  ROUND(SAFE_DIVIDE(SUM(clicks), SUM(impressions)) * 100, 2) AS ctr_gsc,
  ROUND(SUM(sum_top_position + impressions) / SUM(impressions), 2) AS posicion_media
FROM
  `your-project.searchconsole.searchdata_url_impression`
WHERE
  data_date BETWEEN @DS_START_DATE AND @DS_END_DATE
  AND search_type = 'WEB'
GROUP BY
  url_pagina, fecha;

-- FUENTE 2: Custom Query de GA4 para Looker Studio
-- Configurar como segunda fuente de datos independiente
-- SELECT para la fuente GA4:
SELECT
  REGEXP_EXTRACT(
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'),
    r'(https?://[^?#]+)'
  ) AS url_pagina,
  PARSE_DATE('%Y%m%d', event_date) AS fecha,
  COUNT(
    DISTINCT CONCAT(
      user_pseudo_id, '.',
      CAST(
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')
      AS STRING)
    )
  ) AS sesiones_ga4,
  COUNTIF(
    (SELECT value.string_value FROM UNNEST(event_params)
      WHERE key = 'session_engaged') = '1'
  ) AS sesiones_engaged,
  COUNTIF(
    event_name IN ('generate_lead', 'purchase', 'sign_up')
  ) AS conversiones_ga4
FROM
  `your-project.analytics_XXXXXXXXX.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', @DS_START_DATE)
    AND FORMAT_DATE('%Y%m%d', @DS_END_DATE)
  AND traffic_source.medium = 'organic'
GROUP BY
  url_pagina, fecha