Step 5 -Clicks with metadata for known users

This query has same exact structure as the prior query and is used with clicks tables instead of impressions table.

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,
  "Display" AS Channel,
  "Ad - Click" AS Activity_Type,
  "Ad - Click" AS Activity_Sub_Type,
  COALESCE(cmp.Campaign, "Undefined") Campaign,
  COALESCE(plc.Placement, "Undefined") Placement,
  COALESCE(ads.Ad, "Undefined") Ad
FROM
  `MRKL_DCM.p_click_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
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_click_known_users