Bigqueryでutm parameterを抽出

with cte as (
SELECT  
  user_pseudo_id,
  event_name,
  (select v.value.int_value from unnest(event_params) as v where v.key = 'ga_session_id') as ga_session_id,
  datetime(timestamp_micros(event_timestamp),'Asia/Tokyo') as event_datetime,
  (select v.value.int_value from unnest(user_properties) as v where v.key='member_id') as member_id,
  (select v.value.string_value from unnest(event_params) as v where v.key = 'page_location') as page_location,
  (select v.value.string_value from unnest(event_params) as v where v.key = 'ga_session_id') as page_referrer,
  session_traffic_source_last_click.manual_campaign.campaign_name as l_campaign,
  session_traffic_source_last_click.manual_campaign.medium as l_medium,
  session_traffic_source_last_click.manual_campaign.source as l_source,
  collected_traffic_source.manual_campaign_id as c_campaign_id,
  collected_traffic_source.manual_campaign_name as c_campaign,
  collected_traffic_source.manual_source as c_source,
  collected_traffic_source.manual_medium as c_medium,
  traffic_source.source as t_source,
  traffic_source.medium as t_medium,
  traffic_source.name as t_campaign_name,


FROM `prooject-id.dataset-id.table-id`  as t
where event_name = 'page_view'
)

select 
  REGEXP_EXTRACT(page_location, r'utm_source=([^&]+)') AS utm_source,
  REGEXP_EXTRACT(page_location, r'utm_medium=([^&]+)') AS utm_medium,
  REGEXP_EXTRACT(page_location, r'utm_campaign=([^&]+)') AS utm_campaign,
  REGEXP_EXTRACT(page_location, r'utm_content=([^&]+)') AS utm_content,
  REGEXP_EXTRACT(page_location, r'utm_term=([^&]+)') AS utm_term
from cte