Step 6 - Activities with metadata for known users

Activity table has transactions more granular in categories than clicks and impressions. These transaction are categorized as page views but there is additional join to bring in Activity_Sub_Type. Apart from that this follows the same pattern as the above two queries.

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

 #standardSQL
SELECT
  DISTINCT TIMESTAMP_MICROS(A.Event_Time) AS Event_Time_Converted,
  A.User_ID,
  B.prospect_id,
  COALESCE(A.Campaign_ID, "Undefined") Campaign_ID,
  COALESCE(A. Placement_ID, "Undefined") Placement_ID,
  COALESCE(A. Ad_ID, "Undefined") Ad_ID,
  "DCM" AS Source,
  "Site" AS Channel,
  COALESCE(ac.Activity, "Undefined") Activity_Type,
  COALESCE(ac.Activity_Sub_Type, "Undefined") AS Activity_Sub_Type,
  COALESCE(cmp.Campaign, "Undefined") Campaign,
  COALESCE(plc.Placement, "Undefined") Placement,
  COALESCE(ads.Ad, "Undefined") Ad
FROM
  `MRKL_DCM.p_activity_8066` AS A
JOIN
  [your-dataset-name].dcm_identity_map AS B
ON
  A.user_id = b.user_id
LEFT JOIN
  (
      SELECT DISTINCT
          Campaign_ID ,
          Campaign
      FROM `mrkl-workshop-dev.MRKL_DCM.p_match_table_campaigns_8066`
  ) cmp
ON
  cmp.Campaign_ID = A. Campaign_ID
LEFT JOIN
  (
      SELECT DISTINCT
          Placement_ID ,
          Placement
      FROM `mrkl-workshop-dev.MRKL_DCM.p_match_table_placements_8066`
  ) plc
ON
  plc.Placement_ID = A.Placement_ID
LEFT JOIN (
  SELECT
    DISTINCT Ad_ID,
    Ad
  FROM
    `mrkl-workshop-dev.MRKL_DCM.p_match_table_ads_8066`) ads
ON
  ads. Ad_ID = A. Ad_ID
LEFT JOIN (
  SELECT
    DISTINCT Activity_ID,
    Activity,
    Activity_Sub_Type
  FROM
    `mrkl-workshop-dev.MRKL_DCM.p_match_table_activity_cats_8066` ) ac
ON
  ac.Activity_ID = A.Activity_ID
WHERE
  A.Advertiser_ID="5218710"

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

Dataset: [your-dataset-name]

Table ID: dcm_activities_known_users