Step 1 - Get Pardot Attributes of Interest

The first query will collect Pardot activity attributes of interest, and then normalize them to the final program_activity_history table schema structure. The query results should be saved into a temporary table in your dataset, 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,
  created_at AS Activity_Date,
  "Pardot" AS Source,
  CASE
    WHEN va2.type_name = "Email" THEN "Email"
    WHEN va2.type_name = "Email Tracker" THEN "Email"
    WHEN va2.type_name = "Form" THEN "Site"
    WHEN va2.type_name = "Custom Redirect" THEN "Click Redirect"
    WHEN va2.type_name = "File" THEN "Site"
    WHEN va2.type_name = "Form" THEN "Site"
    WHEN va2.type_name = "Form Handler" THEN "Site"
    WHEN va2.type_name = "Form Tracker" THEN "Site"
    WHEN va2.type_name = "Landing Page" THEN "Site"
    WHEN va2.type_name = "Page View" THEN "Site"
    WHEN va2.type_name = "Paid Search Ad" THEN "Search"
    WHEN va2.type_name = "Site Search Query" THEN "Site"
    WHEN va2.type_name = "Video" THEN "Site"
    WHEN va2.type_name = "Visit" THEN "Site"
    ELSE "Unknown"
  END AS Channel,
  --COALESCE(tlu.description, "Unknown") AS Activity_Type,
  CASE
    WHEN va2.type_name = "Email" THEN CONCAT("Email - ",tlu.description)
    WHEN va2.type_name = "Email Tracker" THEN CONCAT("Email - ",tlu.description)
    WHEN va2.type_name = "Custom Redirect" THEN tlu.description
    WHEN va2.type_name = "File" THEN CONCAT("File - ",tlu.description)
    WHEN va2.type_name = "Form" THEN CONCAT("Form - ",tlu.description)
    WHEN va2.type_name = "Form Handler" THEN CONCAT("Form - ",tlu.description)
    WHEN va2.type_name = "Form Tracker" THEN CONCAT("Form - ",tlu.description)
    WHEN va2.type_name = "Landing Page" THEN CONCAT("Landing Page - ",tlu.description)
    WHEN va2.type_name = "Page View" THEN "Page View"
    WHEN va2.type_name = "Paid Search Ad" THEN CONCAT("Paid Search - ",tlu.description)
    WHEN va2.type_name = "Site Search Query" THEN "Site Search"
    WHEN va2.type_name = "Video" THEN tlu.description
    WHEN va2.type_name = "Visit" THEN "Site Visit"
    ELSE "Unknown"
  END AS Activity_Type,
  tlu2.description AS Activity_Sub_Type,
  campaign.name AS Program,
  CASE
    WHEN va2.type_name = "Email" THEN email.name
    WHEN va2.type_name = "Email Tracker" THEN email.name
    WHEN va2.type_name = "Form" THEN form.name
    WHEN va2.type_name = "Custom Redirect" THEN details
    ELSE "Unknown"
  END AS Campaign
FROM
  `mrkl-workshop-dev.MRKL_PARDOT.visitor_activities_2017` va2
LEFT OUTER JOIN
  `mrkl-workshop-dev.MRKL_PARDOT.type_lookup` tlu
ON
  tlu.type = va2.type
LEFT OUTER JOIN
  `mrkl-workshop-dev.MRKL_PARDOT.type_lookup` tlu2
ON
  tlu2.type = va2.type

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

Dataset: [your-dataset-name]

Table ID: pardot_activity_history

(E.g. ZZ01_Dataset.pardot_activity_history)