bq_sql.sql

SELECT 
  -- meta event info
  JSON_EXTRACT_SCALAR(data, '$.event.event_name') AS event_name,
  JSON_EXTRACT_SCALAR(data, '$.domain') AS domain,
  
  -- event data
  JSON_EXTRACT_SCALAR(data, '$.event.browser.page_title') AS page_title,
  JSON_EXTRACT_SCALAR(data, '$.event.browser.page_url') AS page_url,
  TIMESTAMP(JSON_EXTRACT_SCALAR(data, '$.event.event_time')) AS event_time,
  JSON_EXTRACT_SCALAR(data, '$.event.id') AS event_id,
  JSON_EXTRACT(data, '$.event.order') AS order_details,
  JSON_EXTRACT_SCALAR(data, '$.event.order.id') AS order_id,
  JSON_EXTRACT_SCALAR(data, '$.event.order.name') AS order_number,
  JSON_EXTRACT_SCALAR(data, '$.event.order.number') AS order_name,
  JSON_EXTRACT(data, '$.event.products') AS product_details,
  CAST(JSON_EXTRACT_SCALAR(data, '$.event.values.product_subtotal') AS FLOAT64) AS product_subtotal,
  CAST(JSON_EXTRACT_SCALAR(data, '$.event.values.revenue') AS FLOAT64) AS revenue,
  CAST(JSON_EXTRACT_SCALAR(data, '$.event.values.shipping') AS FLOAT64) AS shipping,
  CAST(JSON_EXTRACT_SCALAR(data, '$.event.values.subtotal') AS FLOAT64) AS subtotal,
  CAST(JSON_EXTRACT_SCALAR(data, '$.event.values.tax') AS FLOAT64) AS tax,
  JSON_EXTRACT_ARRAY(data, '$.event.order.tags') AS order_tags_array,

  -- visitorInfo
  JSON_EXTRACT_SCALAR(data, '$.visitorInfo.user_id') AS user_id,
  JSON_EXTRACT_SCALAR(data, '$.visitorInfo.utm_source') AS utm_source,
  JSON_EXTRACT_SCALAR(data, '$.visitorInfo.utm_medium') AS utm_medium,
  JSON_EXTRACT_SCALAR(data, '$.visitorInfo.utm_campaign') AS utm_campaign,
  JSON_EXTRACT_SCALAR(data, '$.visitorInfo.utm_term') AS utm_term,
  JSON_EXTRACT_SCALAR(data, '$.visitorInfo.utm_content') AS utm_content,
  JSON_EXTRACT_SCALAR(data, '$.visitorInfo.referrer') AS referrer,

  -- landingSite
  JSON_EXTRACT_SCALAR(data, '$.landingSite.utm_source') AS utm_source_fallback,
  JSON_EXTRACT_SCALAR(data, '$.landingSite.utm_medium') AS utm_medium_fallback,
  JSON_EXTRACT_SCALAR(data, '$.landingSite.utm_campaign') AS utm_campaign_fallback,
  JSON_EXTRACT_SCALAR(data, '$.landingSite.utm_term') AS utm_term_fallback,
  JSON_EXTRACT_SCALAR(data, '$.landingSite.utm_content') AS utm_content_content,

  -- customerInfo
  JSON_EXTRACT_SCALAR(data, '$.customerInfo.city') AS city,
  JSON_EXTRACT_SCALAR(data, '$.customerInfo.countryCode') AS countryCode,
  JSON_EXTRACT_SCALAR(data, '$.customerInfo.customerId') AS customerId,
  JSON_EXTRACT_SCALAR(data, '$.customerInfo.email') AS email,
  JSON_EXTRACT_SCALAR(data, '$.customerInfo.firstName') AS firstName,
  JSON_EXTRACT_SCALAR(data, '$.customerInfo.ip') AS ip,
  JSON_EXTRACT_SCALAR(data, '$.customerInfo.lastName') AS lastName,
  JSON_EXTRACT_SCALAR(data, '$.customerInfo.provinceCode') AS provinceCode,
  JSON_EXTRACT_SCALAR(data, '$.customerInfo.street1') AS street1,
  JSON_EXTRACT_SCALAR(data, '$.customerInfo.userAgent') AS userAgent,
  JSON_EXTRACT_SCALAR(data, '$.customerInfo.zip') AS zip,
  JSON_EXTRACT_SCALAR(data, '$.event.customer.type') AS customer_new_or_returning,
  JSON_EXTRACT_SCALAR(data, '$.event.customer.order_count') AS customer_order_count,
  JSON_EXTRACT_SCALAR(data, '$.event.customer.total_spent') AS customer_total_spent,
  
  -- Raw JSON data for further exploration
  JSON_EXTRACT(data, '$.cookies') AS cookies_JSON,
  JSON_EXTRACT(data, '$.event') AS event_JSON,
  JSON_EXTRACT(data, '$.landingSite') AS landing_site_JSON,
  JSON_EXTRACT(data, '$.visitorInfo') AS visitor_info_JSON,
  JSON_EXTRACT(data, '$.customerInfo') AS customer_info_JSON,

FROM `elevar-prototyping.smockingbird_bq_data.event_data`;