src_for_web_session.sql

Select  
  user_id, user_phone, user_email,
  clientid, date,/* sessionid , visitnumber,newvisits,
  device_category, device_browser, device_browser_version,
  device_operating_system, device_operating_system_version,
  device_mobile_device_info, device_ip, device_user_agent, device_language
  geonetwork_country, geonetwork_country_iso_code, geonetwork_region,
  geonetwork_region_iso_code, geonetwork_city,geonetwork_latitude, geonetwork_longitude,
  trafficsource_channelgrouping, trafficsource_source, trafficsource_medium, 
  trafficsource_campaign, trafficsource_keyword, trafficsource_adcontent, trafficsource_adgroup,
  trafficsource_keywordmatchtype, --trafficSource.adwordsClickInfo as trafficSource_adwordsClickInfo, --trafficSource.adMatchedQuery as trafficSource_adMatchedQuery,
  trafficsource_gclid, trafficsource_dclid, trafficsource_istruedirect, trafficsource_adcost,
  trafficsource_attributedadcost,
  totals_hits, totals_events, totals_transactions, totals_pageviews, totals_screenviews, 
  totals_isinteraction, totals_visits,
  totalsstreaming_hits, totalsstreaming_events, totalsstreaming_transactions, totalsstreaming_pageviews,
  totalsstreaming_screenviews, totalsstreaming_isinteraction,*/
  JSON_QUERY(t.hits,'$[*].hour') as hour_info,
  JSON_QUERY(t.hits,'$[*].product[*].productSku') as productSku
from {{source('bq','`bq_owoxbi_sessions`')}} as t