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.

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"],
    "tgt_dentsu_client_key": "<consumer_defined_dentsu_client_key>"
  }
  ')
);

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_to_table',
  PARSE_JSON('
  {
    "dimensions": ["p.clear_email", "p.hmid"]
  }
  ')
);

Capture the request_id_from_response above and display the results.

SELECT
  request_id,
  ts,
  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$$",
    "setting_profile": "<consumer_defined>",
    "tgt_dentsu_client_key": "<consumer_defined_dentsu_client_key>"
  }
  ')
);

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$$",
    "tgt_dentsu_client_key": "<consumer_defined_dentsu_client_key>"
  }
  ')
);

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$$",
    "setting_profile": "<consumer_defined>",
    "tgt_dentsu_client_key": "<consumer_defined_dentsu_client_key>"
  }
  ')
);

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$$",
    "tgt_dentsu_client_key": "<consumer_defined_dentsu_client_key>"
  }
  ')
);

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"],
    "src_dentsu_client_key": "<consumer_defined_dentsu_client_key>",
    "tgt_dentsu_client_key": "<consumer_defined_dentsu_key>"
  }
  ')
);

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"],
    "src_dentsu_client_key": "<consumer_defined_dentsu_client_key>",
    "tgt_dentsu_client_key": "<consumer_defined_dentsu_client_key>"
  }
  ')
);

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"],
    "setting_profile": "default",
    "tgt_dentsu_client_key": "<consumer_defined_dentsu_client_key>"
  }
  ')
);

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"],
    "tgt_dentsu_client_key": "<consumer_defined_dentsu_client_key>"
  }
  ')
);

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"],
      "tgt_dentsu_client_key": "<consumer_defined_dentsu_client_key>"
    }
  ')
);

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"]
    }
  ')
);

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"]. 

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"
    ],
    "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"
      }
    ]
  }
  ')
);

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"],
    "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