BigQuery Match Table Lookup for DCM Data Transfer

Question

With DCM's Data Transfer v2 you get 3 main tables of data in GCS:

• p_activity_166401
• p_click_166401
• p_impression_166401

Along with a plethora of match tables like:

• p_match_table_advertisers_166401
• p_match_table_campaigns_166401

Table 1: p_activity_166401

Row    | Event_time    | User_ID | Advertiser_ID  | Campaign_ID | 
------ | ------------- | ------- | -------------  | ----------- |
1      | 149423090566  | AMsySZa | 5487307        | 9638421     |
2      | 149424804284  | 2vmdsXS | 5487307        | 10498283    |

Table 2: p_match_table_advertisers_166401

Row    | Advertiser_ID  | Advertiser  | 
------ | -------------  | ----------- |
1      | 5487307        | Company A   |
2      | 5487457        | Company B   |

How do I reference a value from Table 1 in Table 2 and return the value from Table 2 in a query?

I'd like a result like:

Row    | Advertiser  | User_ID     | 
------ | ----------  | ----------- |
1      | Company A   | AMsySZa     |
2      | Company A   | 2vmdsXS     |

Been searching around here and online and I just can't seem to find a clear reference on how to do the lookups across table, apologies in advance is this is a really simple thing I'm missing :)

EDIT So with a nudge in the right direction I have found the JOIN function...

SELECT
  *
FROM
  [dtftv2_sprt.p_activity_166401]
INNER JOIN
  [dtftv2_sprt.p_match_table_advertisers_166401]
ON
  [p_activity_166401.Advertiser_ID] = 
 p_match_table_advertisers_166401.Advertiser_ID]
LIMIT
  100;

Error: Field 'p_activity_166401.Advertiser_ID' not found.

That is definitely a field in the table.

Answer 1

So this query works great in creating a view with all the data in it.

SELECT
  *
FROM
  [dtftv2_sprt.p_activity_166401]
INNER JOIN
  [dtftv2_sprt.p_match_table_advertisers_166401]
ON
  dtftv2_sprt.p_activity_166401.Advertiser_ID = dtftv2_sprt.p_match_table_advertisers_166401.Advertiser_ID;

Using the view I can now run smaller queries to pull the data I want out. Thanks for guiding me in the right direction Mikhail Berlyant.