BigQuery GA4 WebVitals.js v4 INP and LoAF data extraction

gistfile1.txt

WITH web_vitals_events AS (
  SELECT 
    event_name as metric_name, * EXCEPT(event_name, is_last_received_value) FROM (
    SELECT *, IF (ROW_NUMBER() OVER (
      PARTITION BY (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'metric_id')
      ORDER BY (SELECT COALESCE(value.double_value, value.int_value) FROM UNNEST(event_params) WHERE key = 'metric_value') DESC
    ) = 1, true, false) AS is_last_received_value
    FROM `ilaria-food-and-home.analytics_317802288.events_20240519`
    WHERE event_name in ('INP')
  ) WHERE is_last_received_value
)

# Main query logic
SELECT
  #event_date,
  #event_type,
  target,
  #loaf_script,
  #device_memory,
  #mobile_brand_name,
  #mobile_model_name,
  #browser_version,
  APPROX_QUANTILES(metric_value, 100)[OFFSET(75)] AS p75_inp,
  APPROX_QUANTILES(input_delay, 100)[OFFSET(75)] AS p75_input_delay,
  APPROX_QUANTILES(presentation_delay, 100)[OFFSET(75)] AS p75_presentation_delay,
  APPROX_QUANTILES(processing_duration, 100)[OFFSET(75)] AS p75_processing_duration,
  COUNT(1) as count
FROM (
  SELECT
    event_date,
    device.mobile_brand_name as mobile_brand_name,
    device.mobile_model_name as mobile_model_name,
    #device.web_info.browser_version as browser_version,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = "debug_type") AS event_type,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = "debug_script") AS loaf_script,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = "debug_memory") AS device_memory,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = "debug_input_delay") AS input_delay,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = "debug_presentation_delay") AS presentation_delay,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = "debug_processing_duration") AS processing_duration,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = "debug_target") AS target,
    ROUND((SELECT COALESCE(value.double_value, value.int_value) FROM UNNEST(event_params) WHERE key = "metric_value"), 3) AS metric_value,
    
  FROM web_vitals_events
  WHERE 
    metric_name = 'INP' 
  AND device.category = "mobile" 
  AND
    device.web_info.browser IN (
      "Chrome",
      #"Samsung Internet",
      "Android Webview"
  )
  AND device.operating_system = "Android"
)
GROUP BY 1
ORDER BY count DESC