Step 2 - Parse out u3 Variable for prospect_id

The first step will be to extract/parse out propsectids from the activity table which is hidden in the OtherData field under U3 Variable. (The prospect_id is generated in Pardot, transmitted in certain HTTP Get requests, which show up in DCM; these IDs will be used to tie DCM to Pardot.) In addition to just parsing out the prospect_ID we parse out a few other variables for future exploration and a more thorough analysis of a user's journey

NOTE: Ensure you copy the #standardSQL a the top of the query so that it ensures the correct engine is used

 #standardSQL
SELECT
  _PARTITIONTIME AS pt,
  *,
  REGEXP_EXTRACT(REGEXP_EXTRACT(Other_Data,r'u3=.*?\|;'),r'=([0-9]{1,}?)\|' ) pardot_visitor_id,
  REGEXP_EXTRACT(REGEXP_EXTRACT(Other_Data,r'u3=.*?\|;'),r'\|([0-9]{1,}?)\|' ) prospect_id,
  REGEXP_EXTRACT(REGEXP_EXTRACT(Other_Data,r'u3=.*?\|;'),r'\|([0-9]{1,}?);' ) ON24,
  REGEXP_EXTRACT(REGEXP_EXTRACT(Other_Data,r'oref=http.*'), r'oref=(.*)') oref_url,
  TIMESTAMP_MICROS(Event_Time) AS Event_Time_Converted
FROM
  `mrkl-workshop-dev.MRKL_DCM.p_activity_8066`
WHERE
  REGEXP_CONTAINS(Other_Data, r'u3=(.*?)\|(.*?)\|(.*?);')
  AND Advertiser_ID IN ("5218710")
  AND LENGTH(REGEXP_EXTRACT(REGEXP_EXTRACT(Other_Data,r'u3=.*?\|;'),r'=[0-9]{1,}?\|' ))>=0

Resulting Table: Using the “Save as Table” button in the query results pane, create:

Dataset: [your-dataset-name]

Table ID: tmp_activity_8066_parsed