Skip to main content

Match data with the Translate app

Translate allows you to securely match data to a private dentsu.ID to ensure customer information can be safely shared and queried in a safe and compliant way. Supported customer data types include:

Match and load your data

Below are examples of querying data and retrieving the results, which can be displayed in the terminal or stored locally in a table.

Run the following statements as <INSTALLER_ROLE> or <USER_ROLE> as referenced in the post-installation setup. Dimensions for each template are the authorized output field names for each template.

SELECT *
FROM <application_name>.cleanroom.templates;

The following parameters are available for each template.

  • dimensions: Required and specifies field names to return from the authorized output fields for the template. Dimensions must be prefixed with p. to indicate they originate from the provider.
  • where_clause: Optional and filters the authorized dimensions.
  • setting_profile: Optional and provides a configuration profile from the user_settings table, defaulting to DEFAULT if not specified.
  • tgt_dentsu_client_key: Optional and provides the Client Key for generating client-specific dentsu.IDs. Defaults to consumer_dentsu_client_key in cleanroom.client_id_map_vw if not specified.

Also, the following are optional runtime parameters that can be passed as an alternative to configuring the user_settings table. When provided, they take precedence over user settings (not applicable to waterfall_match):

  • consumer_resource is optional and specifies the fully qualified location of the consumer's input dataset, e.g., my_db.my_schema.my_table.
  • consumer_join_field is optional and specifies the column name of the join field in the consumer dataset.
  • consumer_passthrough_field_1 is optional and specifies the column name of the first pass-through field.
  • consumer_passthrough_field_2 is optional and specifies the column name of the second pass-through field. 

Clear email

Accepts clear emails within a consumer's dataset as input and outputs corresponding matches, including associated dentsu.IDs.

SELECT query: clear_email_match

Instantly get query results (in case the request is approved).

CALL <application_name>.allowed_sprocs.run(
  'clear_email_match',
  PARSE_JSON(
    '{
      "dimensions": [
        "p.clear_email",
        "p.hmid"
      ],
      
      -- Optional parameters below:
      "where_clause": <consumer_defined_where_clause>,
      "setting_profile": <consumer_defined_profile>,
      "tgt_dentsu_client_key": <consumer_defined_dentsu_client_key>,
      "consumer_resource": <consumer_defined_fully_qualified_dataset_location>,
      "consumer_join_field": <consumer_defined_column_name>,
      "consumer_passthrough_field_1": <consumer_defined_column_name>,
      "consumer_passthrough_field_2": <consumer_defined_column_name>
    }'
  )
);

INSERT query: clear_email_match

Instantly get query results in a table within the application (in case the request is approved).

CALL <application_name>.allowed_sprocs.run(
  'clear_email_match',
  PARSE_JSON(
    '{
      "dimensions": [
        "p.clear_email",
        "p.hmid"
      ],
      
      -- Optional parameters below:
      "where_clause": <consumer_defined_where_clause>,
      "setting_profile": <consumer_defined_profile>,
      "tgt_dentsu_client_key": <consumer_defined_dentsu_client_key>,
      "consumer_resource": <consumer_defined_fully_qualified_dataset_location>,
      "consumer_join_field": <consumer_defined_column_name>,
      "consumer_passthrough_field_1": <consumer_defined_column_name>,
      "consumer_passthrough_field_2": <consumer_defined_column_name>
    }'
  )
);

Capture the request_id_from_response above and display the results.

SELECT
  request_id,
  ts,
  result:match_status_flag::BOOLEAN AS match_status_flag,
  result:clear_email::STRING AS clear_email,
  result:hmid::STRING AS hmid
FROM <application_name>.results.clear_email_match
WHERE request_id = '<request_id_from_response>'; -- Paste from above

Device ID

Accepts device IDs within a consumer's dataset as input and outputs corresponding matches, including associated dentsu.IDs.

SELECT query: device_id_match

Instantly get query results (in case the request is approved).

CALL <application_name>.allowed_sprocs.run(
  'device_id_match',
  PARSE_JSON(
    '{
      "dimensions": [
        "p.device_id",
        "p.device_type",
        "p.hmid"
      ],
      "where_clause": "p.device_type = $$AAID$$",
      
      -- Optional parameters below:
      "setting_profile": "<consumer_defined_profile>",
      "tgt_dentsu_client_key": "<consumer_defined_dentsu_client_key>",
      "consumer_resource": "<consumer_defined_fully_qualified_dataset_location>",
      "consumer_join_field": "<consumer_defined_column_name>",
      "consumer_passthrough_field_1": "<consumer_defined_column_name>",
      "consumer_passthrough_field_2": "<consumer_defined_column_name>"
    }'
  )
);

INSERT query: device_id_match

Instantly get the insert query results in a table within the application (in case the request is approved).

CALL <application_name>.allowed_sprocs.run(
  'device_id_match_to_table',
  PARSE_JSON(
    '{
      "dimensions": [
        "p.device_id",
        "p.device_type",
        "p.hmid"
      ],
      "where_clause": "p.device_type = $$AAID$$",
      
      -- Optional parameters below:
      "setting_profile": "<consumer_defined_profile>",
      "tgt_dentsu_client_key": "<consumer_defined_dentsu_client_key>",
      "consumer_resource": "<consumer_defined_fully_qualified_dataset_location>",
      "consumer_join_field": "<consumer_defined_column_name>",
      "consumer_passthrough_field_1": "<consumer_defined_column_name>",
      "consumer_passthrough_field_2": "<consumer_defined_column_name>"
    }'
  )
);

Capture the request_id_from_response above and display the results.

SELECT
  request_id,
  ts,
  result:device_id::STRING AS device_id,
  result:device_type::STRING AS device_type,
  result:hmid::STRING AS hmid
FROM <application_name>.results.device_id_match
WHERE request_id = '<request_id_from_response>'; -- Paste from above

Hashed email

Accepts hashed_emails within a consumer's dataset as input and outputs corresponding matches, including associated dentsu.IDs.

SELECT query: hashed_email_match

Instantly get query results (in case the request is approved).

Note:

  • where_clause is customizable and optional
  • setting_profile is optional and defaults to DEFAULT
  • tgt_dentsu_client_key is optional and currently pass-through only
CALL <application_name>.allowed_sprocs.run(
    'hashed_email_match',
    PARSE_JSON('{
        "dimensions": [
            "p.hashed_email",
            "p.hashed_email_type",
            "p.hmid"
        ],
        "where_clause": "p.hashed_email_type = $$md5$$",

        -- Optional parameters below:
        "setting_profile": "<consumer_defined_profile>",
        "tgt_dentsu_client_key": "<consumer_defined_dentsu_client_key>",
        "consumer_resource": "<consumer_defined_fully_qualified_dataset_location>",
        "consumer_join_field": "<consumer_defined_column_name>",
        "consumer_passthrough_field_1": "<consumer_defined_column_name>",
        "consumer_passthrough_field_2": "<consumer_defined_column_name>"
    }')
);

INSERT query: hashed_email_match

Instantly get the insert query results in a table within the application (in case the request is approved).

CALL <application_name>.allowed_sprocs.run(
    'hashed_email_match_to_table',
    PARSE_JSON('{
        "dimensions": [
            "p.hashed_email",
            "p.hashed_email_type",
            "p.hmid"
        ],
        "where_clause": "p.hashed_email_type = $$md5$$",
        "setting_profile": "<consumer_defined_profile>",
        "tgt_dentsu_client_key": "<consumer_defined_dentsu_client_key>",
        "consumer_resource": "<consumer_defined_fully_qualified_dataset_location>",
        "consumer_join_field": "<consumer_defined_column_name>",
        "consumer_passthrough_field_1": "<consumer_defined_column_name>",
        "consumer_passthrough_field_2": "<consumer_defined_column_name>"
    }')
);

Capture the request_id_from_response above and display the results.

SELECT
    request_id,
    ts,
    result:hashed_email::string AS hashed_email,
    result:hashed_email_type::string AS hashed_email_type,
    result:hmid::string AS hmid
FROM
    <application_name>.results.hashed_email_match
WHERE
    request_id = '<request_id_from_response>'; -- Paste from above

HMID

Accepts HMIDs within a consumer's dataset as input and outputs corresponding matches, including associated dentsu.IDs. Can be used for transcoding across HMID spaces.

SELECT query: hmid_match

Instantly get query results (in case the request is approved).

Reference the approved_transcoding_pairs column in <application_name>.cleanroom.client_id_map_vw for the src_dentsu_client_key and tgt_dentsu_client_key values.

CALL <application_name>.allowed_sprocs.run(
    'hmid_match',
    PARSE_JSON('{
        "dimensions": [
            "p.src_dentsu_client_key",
            "p.src_hmid",
            "p.tgt_dentsu_client_key",
            "p.tgt_hmid"
        ],
        
        -- Optional parameters below:
        "where_clause": "<consumer_defined_where_clause>",
        "setting_profile": "<consumer_defined_profile>",
        "src_dentsu_client_key": "<consumer_defined_dentsu_client_key>",
        "tgt_dentsu_client_key": "<consumer_defined_dentsu_client_key>",
        "consumer_resource": "<consumer_defined_fully_qualified_dataset_location>",
        "consumer_join_field": "<consumer_defined_column_name>",
        "consumer_passthrough_field_1": "<consumer_defined_column_name>",
        "consumer_passthrough_field_2": "<consumer_defined_column_name>"
    }')
);

INSERT query: hmid_match

Instantly get query results in a table within the application (in case the request is approved).

Reference the approved_transcoding_pairs column in <application_name>.cleanroom.client_id_map_vw for the src_dentsu_client_key and tgt_dentsu_client_key values.

CALL <application_name>.allowed_sprocs.run(
    'hmid_match_to_table',
    PARSE_JSON('{
        "dimensions": [
            "p.src_dentsu_client_key",
            "p.src_hmid",
            "p.tgt_dentsu_client_key",
            "p.tgt_hmid"
        ],
        
        -- Optional parameters below:
        "where_clause": "<consumer_defined_where_clause>",
        "setting_profile": "DEFAULT",
        "src_dentsu_client_key": "<consumer_defined_dentsu_client_key>",
        "tgt_dentsu_client_key": "<consumer_defined_dentsu_client_key>",
        "consumer_resource": "<consumer_defined_fully_qualified_dataset_location>",
        "consumer_join_field": "<consumer_defined_column_name>",
        "consumer_passthrough_field_1": "<consumer_defined_column_name>",
        "consumer_passthrough_field_2": "<consumer_defined_column_name>"
    }')
);
``

Capture the request_id_from_response above and display the results.

SELECT
  request_id,
  ts,
  result:src_dentsu_client_key::string AS src_dentsu_client_key,
  result:src_hmid::string AS src_hmid,
  result:tgt_dentsu_client_key::string AS tgt_dentsu_client_key,
  result:tgt_hmid::string AS tgt_hmid
FROM
  <application_name>.results.hmid_match
WHERE
  request_id = '<request_id_from_response>'; -- Paste from above

IP Address

Accepts IP addresses within a consumer's dataset as input and outputs corresponding matches, including associated dentsu.IDs.

SELECT query: ip_match

Instantly get query results (in case the request is approved).

CALL <application_name>.allowed_sprocs.run(
    'ip_match',
    PARSE_JSON('{
        "dimensions": [
            "p.ip",
            "p.hmid"
        ],
        
        -- Optional parameters below:
        "where_clause": "<consumer_defined_where_clause>",
        "setting_profile": "<consumer_defined_profile>",
        "tgt_dentsu_client_key": "<consumer_defined_dentsu_client_key>",
        "consumer_resource": "<consumer_defined_fully_qualified_dataset_location>",
        "consumer_join_field": "<consumer_defined_column_name>",
        "consumer_passthrough_field_1": "<consumer_defined_column_name>",
        "consumer_passthrough_field_2": "<consumer_defined_column_name>"
    }')
);

INSERT query: ip_match

Instantly get the insert query results in a table within the application (in case the request is approved).

CALL <application_name>.allowed_sprocs.run(
    'ip_match_to_table',
    PARSE_JSON('{
        "dimensions": [
            "p.ip",
            "p.hmid"
        ],
        
        -- Optional parameters below:
        "where_clause": "<consumer_defined_where_clause>",
        "setting_profile": "<consumer_defined_profile>",
        "tgt_dentsu_client_key": "<consumer_defined_dentsu_client_key>",
        "consumer_resource": "<consumer_defined_fully_qualified_dataset_location>",
        "consumer_join_field": "<consumer_defined_column_name>",
        "consumer_passthrough_field_1": "<consumer_defined_column_name>",
        "consumer_passthrough_field_2": "<consumer_defined_column_name>"
    }')
);

Capture the request_id_from_response above and display the results.

SELECT
    request_id,
    ts,
    result:ip::string AS ip,
    result:hmid::string AS hmid
FROM
    <application_name>.results.ip_match
WHERE
    request_id = '<request_id_from_response>'; -- Paste from above

Phone number

Accepts phone numbers within a consumer's dataset as input and outputs corresponding matches, including associated dentsu.IDs.

SELECT query: phone_match

Instantly get query results (in case the request is approved).

CALL <application_name>.allowed_sprocs.run(
    'phone_match',
    PARSE_JSON('{
        "dimensions": [
            "p.phone",
            "p.hmid"
        ],
        
        -- Optional parameters below:
        "where_clause": "<consumer_defined_where_clause>",
        "setting_profile": "<consumer_defined_profile>",
        "tgt_dentsu_client_key": "<consumer_defined_dentsu_client_key>",
        "consumer_resource": "<consumer_defined_fully_qualified_dataset_location>",
        "consumer_join_field": "<consumer_defined_column_name>",
        "consumer_passthrough_field_1": "<consumer_defined_column_name>",
        "consumer_passthrough_field_2": "<consumer_defined_column_name>"
    }')
);

INSERT query: phone_match

Instantly get the insert query results in a table within the application (in case the request is approved).

CALL <application_name>.allowed_sprocs.run(
    'phone_match_to_table',
    PARSE_JSON('{
        "dimensions": [
            "p.phone",
            "p.hmid"
        ],
        
        -- Optional parameters below:
        "where_clause": "<consumer_defined_where_clause>",
        "setting_profile": "<consumer_defined_profile>",
        "tgt_dentsu_client_key": "<consumer_defined_dentsu_client_key>",
        "consumer_resource": "<consumer_defined_fully_qualified_dataset_location>",
        "consumer_join_field": "<consumer_defined_column_name>",
        "consumer_passthrough_field_1": "<consumer_defined_column_name>",
        "consumer_passthrough_field_2": "<consumer_defined_column_name>"
    }')
);

Capture the request_id_from_response above and display the results.

SELECT
  request_id,
  ts,
  result:phone::string AS phone,
  result:hmid::string AS hmid
FROM
  <application_name>.results.phone_match
WHERE
  request_id = '<request_id_from_response>'; -- Paste from above

Waterfall

Accepts a mix of IDs and consumer datapoints within a consumer's dataset as input and outputs corresponding matches, including associated dentsu.IDs.
The standard waterfall order is: ["hmid", "email", "device_id", "ip", "phone"]. 

See Supported ID types for additional information.  

SELECT query: waterfall_match

Instantly get query results (in case the request is approved).

Include the source and target Client Keys as src_dentsu_client_key and the fully qualified name of your dataset as consumer_resource.

CALL <application_name>.allowed_sprocs.run(
    'waterfall_match',
    PARSE_JSON('{
        "dimensions": [
            "p.source_id",
            "p.matched_id_type",
            "p.hmid"
        ],
        
        
-- Optional parameters below:
        "where_clause": "<consumer_defined_where_clause>",
        "src_dentsu_client_key": "<consumer_defined_dentsu_client_key>",
        "consumer_resource": "<consumer_defined_fully_qualified_dataset_location>",
        
        "src_id_types": [
            { "id_type": "hmid", "join_field": "hmid" },
            { "id_type": "device_id", "join_field": "device_id" },
            { "id_type": "ip", "join_field": "ip" },
            { "id_type": "phone", "join_field": "phone" },
            { "id_type": "email", "join_field": "email", "email_type_field": "email_type" }
        ]
    }')
);

To see details about the usage of this template, execute the following statement:

SELECT
  notes:"description"::varchar
FROM
  <application_name>.cleanroom.templates
WHERE
  template_name = 'waterfall_match';

INSERT query: waterfall_match

Instantly get query results (in case the request is approved).

Note: Include the source Client Key as src_dentsu_client_key and the fully qualified name of your dataset as consumer_resource parameter.

When matching occurs on the HMID ID type, the source and target HMID values in the result set may be in different Client Key ID spaces. This situation applies only to app consumers when transcoding is both requested and authorized. Therefore, transcoding the same entity in different dentsu_client_id spaces will yield different HMID values.

CALL <application_name>.allowed_sprocs.run(
    'waterfall_match_to_table',
    PARSE_JSON('{
        "dimensions": [
            "p.source_id",
            "p.matched_id_type",
            "p.hmid"
        ],
        
        

-- Optional parameter:
        "src_dentsu_client_key": "<consumer_defined_dentsu_client_key>",
        
        "consumer_resource": "<consumer_defined>",
        "src_id_types": [
            { "id_type": "hmid",      "join_field": "hmid" },
            { "id_type": "device_id", "join_field": "device_id" },
            { "id_type": "ip",        "join_field": "ip" },
            { "id_type": "phone",     "join_field": "phone" },
            { "id_type": "email",     "join_field": "email", "email_type_field": "email_type" }
        ]
    }')
);

Capture the request_id_from_response above and display the results.

SELECT
  request_id,
  ts,
  result:source_id::string AS source_id,
  result:matched_id_type::string AS matched_id_type,
  result:hmid::string AS hmid
FROM
  <application_name>.results.waterfall_match
WHERE
  request_id = '<request_id_from_response>'; -- Paste from above

To see details about the usage of this template, execute the following statement:

SELECT
  notes:"description"::varchar
FROM
  <application_name>.cleanroom.templates
WHERE
  template_name = 'waterfall_match_to_table';

Related articles