Construcción de un Modelo de Atribución Multi-Touch sobre Eventos Raw de GA4 en BigQuery

-- Extracción y normalización de puntos de contacto de marketing
WITH parsed_events AS (
  SELECT
    user_pseudo_id,
    event_timestamp,
    TIMESTAMP_MICROS(event_timestamp) AS event_time,
    event_name,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
    COALESCE((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'source'), traffic_source.source) AS utm_source,
    COALESCE((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'medium'), traffic_source.medium) AS utm_medium,
    COALESCE((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'campaign'), traffic_source.name) AS utm_campaign
  FROM `tu_proyecto.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260801' AND '20260831'
),
sessions_summary AS (
  SELECT
    user_pseudo_id,
    session_id,
    MIN(event_time) AS session_start,
    ARRAY_AGG(utm_source IGNORE NULLS ORDER BY event_time ASC LIMIT 1)[OFFSET(0)] AS session_source,
    ARRAY_AGG(utm_medium IGNORE NULLS ORDER BY event_time ASC LIMIT 1)[OFFSET(0)] AS session_medium,
    ARRAY_AGG(utm_campaign IGNORE NULLS ORDER BY event_time ASC LIMIT 1)[OFFSET(0)] AS session_campaign,
    LOGICAL_OR(event_name = 'generate_lead') AS has_lead_conversion,
    LOGICAL_OR(event_name = 'opportunity_created') AS has_deal_conversion
  FROM parsed_events
  WHERE session_id IS NOT NULL
  GROUP BY 1, 2
)
SELECT * FROM sessions_summary;

-- Cálculo de pesos W-Shaped por punto de contacto
WITH user_journey AS (
  SELECT
    user_pseudo_id,
    session_id,
    session_start,
    CONCAT(COALESCE(session_source, '(direct)'), ' / ', COALESCE(session_medium, '(none)')) AS channel_grouping,
    has_lead_conversion,
    has_deal_conversion,
    ROW_NUMBER() OVER(PARTITION BY user_pseudo_id ORDER BY session_start ASC) AS touch_rank_asc,
    ROW_NUMBER() OVER(PARTITION BY user_pseudo_id ORDER BY session_start DESC) AS touch_rank_desc,
    COUNT(*) OVER(PARTITION BY user_pseudo_id) AS total_touches
  FROM sessions_summary
)
SELECT
  user_pseudo_id,
  session_id,
  channel_grouping,
  session_start,
  CASE
    -- Viaje de 1 solo toque recibe el 100%
    WHEN total_touches = 1 THEN 1.0
    -- Primer toque (First Touch)
    WHEN touch_rank_asc = 1 THEN 0.30
    -- Hito de conversión / Oportunidad final
    WHEN touch_rank_desc = 1 THEN 0.30
    -- Toque de creación de Lead
    WHEN has_lead_conversion THEN 0.30
    -- Toques intermedios se reparten el 10% restante
    ELSE 0.10 / NULLIF(total_touches - 3, 0)
  END AS attribution_weight
FROM user_journey;