Perform Identity Resolution in BigQuery

The standard PII resolution process (without deconfliction configured) passes the data through a privacy filter which removes the PII and reswizzles the table. Because of this, any attributes you need to keep associated with the identifier need to be included in the input table. For more information, see the "Privacy Filter" section below.

You can include up to 4 name element columns and 7 address element columns in your PII resolution input table.

These column names cannot be used in the input table for PII resolution:

See the table below for a list of the suggested input table columns and descriptions for PII resolution.

Here are some examples of BigQuery SQL for creating this table:

// Example SQL query to create an input table for PII resolution

CREATE OR REPLACE TABLE  <dataset>.<input_table_name> (
first_name text,
middle_name text,
last_name text,
address_1 text,
address_2 text,
city text,
state text,
zip text,
email text,
phone text,
attribute_1 text
);

// Or setup against an existing table

CREATE OR REPLACE VIEW <dataset>.<view_name> as 
SELECT 
d.first_name as first_name,
d.middle_name as middle_name,
d.last_name as last_name,
d.address_1 as address_1,
d.address_2 as address_2,
d.city as city,
d.state as state,
d.zip as zip,
d.email as email,
d.phone as phone,
d.attribute_1 as attribute_1
FROM my_bq_table d;


// Example SQL query to insert a row into the input table for PII resolution

INSERT INTO <dataset>.<view_name> values (
	‘John’,
	‘Farmer’,
	‘Smith’,
	‘123 Test Dr,
	‘Apt 321’,
	‘San Francisco’,
	‘CA’,
	‘94112’,
	‘test@gmail.com’,
	‘123-123-1234’,
	‘attribute_1_value’

);

The email-only resolution process operates similarly to PII resolution. Any attributes you need to keep associated with the identifier need to be included in the input table. For more information, see the "Privacy Filter" section below.

See the table below for a list of the suggested input table columns and descriptions for email-only resolution.

Here are some examples of BigQuery SQL for creating this table:

// Example SQL query to create an input table for email resolution

CREATE OR REPLACE TABLE  <dataset>.<input_table_name> (
HASHED_EMAIL STRING, 
ATTRIBUTE_1 STRING, 
ATTRIBUTE_12 STRING
);

// Or setup against an existing table

CREATE OR REPLACE VIEW <dataset>.<view_name> as 
SELECT 
d.hashed_email as HASHED_EMAIL, 
d.gender as ATTRIBUTE_1, 
d.name as ATTRIBUTE_2 
FROM my_bq_table d;
// Example SQL query to insert a row into the input table for email resolution
INSERT INTO <dataset>.<input_table> values ('some_ramp_id', '1234', 'RAMPID');

The device ID resolution operation can be used for the following purposes:

See the tables below for a list of the suggested input table columns and descriptions for these device ID resolution options.

See the table below for the suggested input table column and description for translating device identifiers.

Here are some examples of BigQuery SQL for creating this table:

// Example SQL query to create an input table for device resolution

CREATE OR REPLACE TABLE  <dataset>.<input_table_name> (
DEVICE_IDENTIFIER STRING
);

// Or setup against an existing table

CREATE OR REPLACE VIEW <dataset>.<view_name> as 
SELECT 
d.device_identifier as DEVICE_IDENTIFIER
FROM my_bq_table d;

// Example SQL query to insert a row into the input table for device resolution

INSERT INTO <dataset>.<input_table> values ('some_device_id');

See the table below for the suggested input table column and description for translating individual RampIDs into their associated household RampIDs.

Here are some examples of BigQuery SQL for creating this table:

// Example SQL query to create an input table for translating individual RampIDs into household RampIDs

CREATE OR REPLACE TABLE  <dataset>.<input_table_name> (
RAMPID STRING;

// Or setup against an existing table

CREATE OR REPLACE VIEW <dataset>.<view_name> as 
SELECT 
d.rampid as RAMPID
FROM my_bq_table d;

// Example SQL query to insert a row into the input table for translating individual RampIDs into household RampIDs

INSERT INTO <dataset>.<input_table> values ('some_ramp_id');

See the table below for the suggested input table column and description for CID matching. This process enables the retrieval of an existing CID to RampID mapping, hosted by LiveRamp.

Here are some examples of BigQuery SQL for creating this table:

// Example SQL query to create an input table for CID resolution

CREATE OR REPLACE TABLE  <dataset>.<input_table_name> (
CID STRING
);

// Or setup against an existing table

CREATE OR REPLACE VIEW <dataset>.<view_name> as 
SELECT 
d.cid as CID
FROM my_bq_table d;

// Example SQL query to insert a row into the input table for CID resolution

INSERT INTO <dataset>.<input_table> values ('some_cid');

To create a metadata table for PII resolution, include the following columns:

Here are some examples of BigQuery SQL for creating this table:

-- FOR PII Update the parameters here for the metadata table
-- Not all PII types need to be present, remove unused entries from the target_columns JSON
create or replace table identifier($customer_meta_table_name) as
select
    '<client_id>' as client_id,
    '<client_secret>' as client_secret,
    'resolution' as execution_mode,
    'pii' as execution_type,
    '{"name":["<up to 4 name column names>"],"streetAddress":["<up to 7 address column names>"],"city":"<city column>","state":"<state column>","zipCode":"zipcode column","phone":"<phone column>","email":"<email column>"}'
 as target_columns,
    1 as limit_val;

-- FOR PII Update the parameters here for the metadata table
-- Not all PII types need to be present, remove unused entries from the target_columns JSON
create or replace table identifier($customer_meta_table_name) as
select
    'liveramp_client' as client_id,
    '84159be2-ab93-4bf8-24c9-2g123ef08815' as client_secret,
    'resolution' as execution_mode,
    'pii' as execution_type,
    '{"name":["FIRSTNAME","LASTNAME"],"streetAddress":["ADDRESSLINE","ADDRESSLINE2"],"city":"CITY","state":"STATE","zipCode":"ZIPCODE","phone":"PHONE","email":"EMAIL"}' as target_columns,
    1 as limit_val;

To create a metadata table for email resolution, include the following columns:

Here are some examples of BigQuery SQL for creating this table:

-- email
-- FOR EMAIL Update the parameters here for the metadata table
create or replace table identifier($customer_meta_table_name) as
select
    '<client_id>' as client_id,
    '<client_secret>' as client_secret,
    'resolution' as execution_mode,
    'email' as execution_type,
    '<column to be resolved>' as target_column,
    1 as limit_val;

To create a metadata table for device ID resolution, include the following columns:

Here are some examples of BigQuery SQL for creating this table:

--FOR DEVICE OR CID RESOLUTION Update the parameters here for the metadata table
create or replace table identifier($customer_meta_table_name) as
select
    '<client_id>' as client_id,
    '<client_secret>' as client_secret,
    'resolution' as execution_mode,
    '<execution_type>' as execution_type,
    '<column to be resolved>' as target_column;

To create a metadata table for CID resolution, include the following columns:

Here are some examples of BigQuery SQL for creating this table:

--FOR DEVICE OR CID RESOLUTION Update the parameters here for the metadata table
create or replace table identifier($customer_meta_table_name) as
select
    '<client_id>' as client_id,
    '<client_secret>' as client_secret,
    'resolution' as execution_mode,
    '<execution_type>' as execution_type,
    '<column to be resolved>' as target_column;

You can use a parameterized and repeatable share script that selects the appropriate data to share with LiveRamp afterwards. See the code below for an example.

BEGIN
  -------------------------------------------------------------------------------
  -- UPDATE VARIABLES WITH CORRECT FULLY QUALIFIED TABLE & DATASET IDENTIFIERS --
  -------------------------------------------------------------------------------
  -- input_table is table that has ramp_ids to transcode from, target_domain, and type
  DECLARE input_table STRING DEFAULT '<dataset_name>.<input_table_name>';
  -- input_metadata table has arguments for your run including client_id / client_secret
  DECLARE input_metadata STRING DEFAULT '<dataset_name>.<metadata_table_name>';
  -- output_dataset is an empty dataset you created to share to LiveRamp to write outputs to.
  DECLARE output_dataset STRING DEFAULT '<output_dataset_name>';
  -- project_name is the project in which your input tables and output_dataset lives.
  DECLARE project_name STRING DEFAULT '<project_name>';
  -------------------------------------------------------------------------------
  -- END VARIABLES DO NOT EDIT BELOW THIS
  -------------------------------------------------------------------------------
  DECLARE liveramp_iam_principle STRING DEFAULT 'serviceAccount:serviceAccount@liveramp.com';

  -- GRANT Read of Input Tables
  EXECUTE IMMEDIATE "GRANT `roles/bigquery.dataViewer` ON TABLE " || input_table || " TO '" || liveramp_iam_principle || "';";
  EXECUTE IMMEDIATE "GRANT `roles/bigquery.dataViewer` ON TABLE " || input_metadata || " TO '" || liveramp_iam_principle || "';";

  -- GRANT Write for Output Tables
  EXECUTE IMMEDIATE "GRANT `roles/bigquery.dataEditor` ON SCHEMA " || output_dataset || " TO '" || liveramp_iam_principle || "';";

  with fqdns AS
    (
      SELECT STRUCT("input_table" as key, project_name || "." || input_table as fqdn) as item
      UNION ALL
      SELECT STRUCT("input_metadata" as key, project_name || "." || input_metadata as fqdn) as item
      UNION ALL
      SELECT STRUCT("output_dataset" as key, project_name || "." || output_dataset as fqdn) as item
    )
  SELECT f.item.*
  FROM fqdns f;

END

The PII resolution process passes the input table through a privacy filter which removes the PII and reswizzles the table (in addition to other operations). Because of this, any attributes you need to keep associated with the identifier need to be included in the input table. For more information, see the "Privacy Filter" section below.

Identity resolution of PII provides supplemental match metadata for additional insight into customer data that can provide powerful signals for making decisions based on RampIDs.

For PII resolution, the output table includes the fields shown in the table below.

The email-only resolution process operates similarly to PII resolution. Any attributes you need to keep associated with the identifier need to be included in the input table. For more information, see the "Privacy Filter" section below.

For email-only resolution, the results end up in the output table in the same database, with the following fields (as shown below):

For device identifier resolution, the output table includes the fields shown in the table below:

For resolving individual RampIDs to household RampIDs, the output table includes the fields shown in the table below:

For CID matching against an existing CID sync with LiveRamp, the output table includes the fields shown in the table below: