Step 2 - Merge Pardot Activities with DCM Activities

The final query will combine the Pardot activity rows from the table created in the previous step, with the similarly normalized data from the final DCM table. The query results should be saved into your dataset, with the final table name of program_activity_history, as noted below.

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

 #standardSQL
SELECT
  CAST(prospect_id AS INT64) AS Prospect_ID,
  Event_Time_Converted AS Activity_Date,
  Source AS Source,
  Channel AS Channel,
  COALESCE(Activity_Type, "Unknown") AS Activity_Type,
  Activity_Sub_Type AS Activity_Sub_Type,
  NULL AS Program,
  Placement AS Campaign
FROM
  `mrkl-workshop-dev.[your-dataset-name].dcm_activity_history` UNION All
SELECT
  Prospect_ID,
  Activity_Date,
  Source,
  Channel,
  Activity_Type,
  Activity_Sub_Type,
  Program,
  Campaign
FROM
  `mrkl-workshop-dev.[your-dataset-name].pardot_activity_history`

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

Dataset: [your-dataset-name]

Table ID: program_activity_history

(E.g. ZZ01_Dataset.program_activity_history)