GCP BigQuery Sample Queries

As an example, the below query aims to connect the tables [name]_batch_data and travel_intent_global. These two tables are normally completely independent and find no common ground. The batch data comes from an internal source which collects the email identifier (tokenized) and the intent data is a score coming from Adara that is based on the Token Identity.

In order to make the operation work, a mapping must exist of the tokenized email and the Token Identifier. This is where the token_identity_mapping comes in. It is the ID map that acts as the joining function between any two disparate sources.

Query

SELECT  

b.data_source

, b.id_type 

, a.email_of_traveler AS tokenized_email

, b.identity AS adara_token_identity

, MAX(c.travel_intent_score) AS travel_intent

FROM `[name]-consortium.ingest_1pa.[name]_batch_data` a

LEFT JOIN `[name]-consortium.privacy_tokens.token_identity_mapping` b ON a.email_of_traveler = b.token

LEFT JOIN `[name]-consortium.adara_signals.travel_intent_global` c ON c.identity = b.identity

GROUP BY a.email_of_traveler, b.identity, c.identity

Result

As an output of the above query, you can see the that all rows come from the batch source with different id_types, all of which get have a unique hexdecimal string representing the tokenized version of the original id. The Adara Token Identity confirms that the first two identifiers actually belong to a single identity, which would not have been possible to identify before.