Step 4 - Impressions with metadata for known users

Combining Impressions with users who have an associated prospect ID.

This query combines Impressions table with Placements table, Campaign table and Ads table to bring the respective descriptions associated with the IDs as well as assigns prospect ids to each record

E.g.: Placement ID of 456 belongs to Youtube,
Campaign ID of 123 belongs to recent coffee campaign,
Ad_ID of xyz belongs to size of the ad and where it is the company it is for.

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 - Impression" AS Activity_Type,
  "Ad - Impression" AS Activity_Sub_Type,
  COALESCE(cmp.Campaign, "Undefined") Campaign,
  COALESCE(plc.Placement, "Undefined") Placement,
  COALESCE(ads.Ad, "Undefined") Ad
FROM
  `MRKL_DCM.p_impression_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_impressions_known_users