Configure the Translate app

The configuration script lets users create profiles that store the locations of input data for the application.

The application requires at least one profile per matching function (aka "template"). Users may create multiple profiles per template to satisfy different use cases.

Review the Post intallation steps for additional guidance.

Configure Translate

Run the following statements as INSTALLER_ROLE or USER_ROLE as referenced in the dentsu.Connect Help Center’s Post-installation set-up and verification.

DO NOT RUN AS-IS. Fill in the required settings marked as application_name and consumer_defined.

Allow the application to access the consumer data required to perform matching. For each consumer-defined database and schema set in the USER_SETTINGS, run the following statements. This can be modified depending on the particular scenario (e.g., object types, schema vs. object-level permissions, etc.).

PLEASE NOTE: This action is needed for all new tables added after initial execution.

USE ROLE <CONSUMER_DEFINED_ROLE>;

USE WAREHOUSE <WAREHOUSE_NAME>;

-- Grant statement example
GRANT USAGE
  ON DATABASE <consumer_defined_db>
  TO APPLICATION <consumer_defined_application>;

GRANT USAGE
  ON SCHEMA <consumer_defined_db>.<consumer_defined_schema>
  TO APPLICATION <consumer_defined_application>;

GRANT SELECT
  ON ALL TABLES IN SCHEMA <consumer_defined_db>.<consumer_defined_schema>
  TO APPLICATION <consumer_defined_application>;

Set locations of consumer-defined resources

PLEASE NOTE: The setting_profile is important because it allows you to maintain multiple configuration sets for each template. For instance, you can have various profiles for clear_email_match, each storing different dataset locations to use with this template, such as TEST, DEV, DEFAULT, etc. You can then pass the profile as a parameter when calling the match template, as detailed in Match data with the Translate app. 

Pass-through fields (consumer_*_passthrough_field_1 and consumer_*_passthrough_field_2) are optional and allow additional columns from the consumer dataset to be included in the output alongside matched IDs. Pass-through fields must also be specified in the request dimensions using the c. prefix (e.g. c.my_field). 

Consumer-defined settings can alternatively be passed as runtime parameters directly in the match request; runtime parameters always take precedence over user settings. See the runtime parameters section below for a list of supported parameter names.

If the passthrough parameters are set in the DEFAULT setting profile, the respective function will use the default setting if the setting_profile parameter is not provided, even if it is not included in the parameters list during execution.

clear_email_match

MERGE INTO <application_name>.local.user_settings settings
USING (
  SELECT *
  FROM (
    VALUES
      ('consumer_clear_email_db', '<consumer_defined_db>', 'DEFAULT'),
      ('consumer_clear_email_schema', '<consumer_defined_schema>', 'DEFAULT'),
      ('consumer_clear_email_table', '<consumer_defined_table>', 'DEFAULT'),
      ('consumer_clear_email_join_field', '<consumer_defined_column_name>', 'DEFAULT'),
      ('consumer_clear_email_passthrough_field_1', '<consumer_defined_column_name>', 'DEFAULT'),
      ('consumer_clear_email_passthrough_field_2', '<consumer_defined_column_name>', 'DEFAULT')
  ) src (setting_name, setting_value, setting_profile)
) input
ON settings.setting_name = input.setting_name
 AND IFNULL(settings.setting_profile, '') = IFNULL(input.setting_profile, '')
WHEN NOT MATCHED THEN
  INSERT (setting_name, setting_value, setting_profile)
  VALUES (input.setting_name, input.setting_value, input.setting_profile)
WHEN MATCHED THEN
  UPDATE SET settings.setting_value = input.setting_value;

device_id_match

MERGE INTO <application_name>.local.user_settings settings
USING (
  SELECT *
  FROM (
    VALUES
      ('consumer_devices_db', '<consumer_defined_db>', 'DEFAULT'),
      ('consumer_devices_schema', '<consumer_defined_schema>', 'DEFAULT'),
      ('consumer_devices_table', '<consumer_defined_table>', 'DEFAULT'),
      ('consumer_devices_join_field', '<consumer_defined_column_name>', 'DEFAULT'),
      ('consumer_devices_passthrough_field_1', '<consumer_defined_column_name>', 'DEFAULT'),
      ('consumer_devices_passthrough_field_2', '<consumer_defined_column_name>', 'DEFAULT')
  ) src (setting_name, setting_value, setting_profile)
) input
ON settings.setting_name = input.setting_name
 AND IFNULL(settings.setting_profile, '') = IFNULL(input.setting_profile, '')
WHEN NOT MATCHED THEN
  INSERT (setting_name, setting_value, setting_profile)
  VALUES (input.setting_name, input.setting_value, input.setting_profile)
WHEN MATCHED THEN
  UPDATE SET settings.setting_value = input.setting_value;

hashed_email_match

MERGE INTO <application_name>.local.user_settings settings
USING (
  SELECT *
  FROM (
    VALUES
      ('consumer_devices_db', '<consumer_defined_db>', 'DEFAULT'),
      ('consumer_devices_schema', '<consumer_defined_schema>', 'DEFAULT'),
      ('consumer_devices_table', '<consumer_defined_table>', 'DEFAULT'),
      ('consumer_devices_join_field', '<consumer_defined_column_name>', 'DEFAULT'),
      ('consumer_devices_passthrough_field_1', '<consumer_defined_column_name>', 'DEFAULT'),
      ('consumer_devices_passthrough_field_2', '<consumer_defined_column_name>', 'DEFAULT')
  ) src (setting_name, setting_value, setting_profile)
) input
ON settings.setting_name = input.setting_name
 AND IFNULL(settings.setting_profile, '') = IFNULL(input.setting_profile, '')
WHEN NOT MATCHED THEN
  INSERT (setting_name, setting_value, setting_profile)
  VALUES (input.setting_name, input.setting_value, input.setting_profile)
WHEN MATCHED THEN
  UPDATE SET settings.setting_value = input.setting_value;

hmid_match

MERGE INTO <application_name>.local.user_settings settings
USING (
  SELECT *
  FROM (
    VALUES
      ('consumer_hmid_db', '<consumer_defined_db>', 'DEFAULT'),
      ('consumer_hmid_schema', '<consumer_defined_schema>', 'DEFAULT'),
      ('consumer_hmid_table', '<consumer_defined_table>', 'DEFAULT'),
      ('consumer_hmid_join_field', '<consumer_defined_column_name>', 'DEFAULT'),
      ('consumer_hmid_passthrough_field_1', '<consumer_defined_column_name>', 'DEFAULT'),
      ('consumer_hmid_passthrough_field_2', '<consumer_defined_column_name>', 'DEFAULT')
  ) src (setting_name, setting_value, setting_profile)
) input
ON settings.setting_name = input.setting_name
 AND IFNULL(settings.setting_profile, '') = IFNULL(input.setting_profile, '')
WHEN NOT MATCHED THEN
  INSERT (setting_name, setting_value, setting_profile)
  VALUES (input.setting_name, input.setting_value, input.setting_profile)
WHEN MATCHED THEN
  UPDATE SET settings.setting_value = input.setting_value;

ip_match

MERGE INTO <application_name>.local.user_settings settings
USING (
  SELECT *
  FROM (
    VALUES
      ('consumer_ip_db', '<consumer_defined_db>', 'DEFAULT'),
      ('consumer_ip_schema', '<consumer_defined_schema>', 'DEFAULT'),
      ('consumer_ip_table', '<consumer_defined_table>', 'DEFAULT'),
      ('consumer_ip_join_field', '<consumer_defined_column_name>', 'DEFAULT'),
      ('consumer_ip_passthrough_field_1', '<consumer_defined_column_name>', 'DEFAULT'),
      ('consumer_ip_passthrough_field_2', '<consumer_defined_column_name>', 'DEFAULT')
  ) src (setting_name, setting_value, setting_profile)
) input
ON settings.setting_name = input.setting_name
 AND IFNULL(settings.setting_profile, '') = IFNULL(input.setting_profile, '')
WHEN NOT MATCHED THEN
  INSERT (setting_name, setting_value, setting_profile)
  VALUES (input.setting_name, input.setting_value, input.setting_profile)
WHEN MATCHED THEN
  UPDATE SET settings.setting_value = input.setting_value;

phone_match

MERGE INTO <application_name>.local.user_settings settings
USING (
  SELECT *
  FROM (
    VALUES
      ('consumer_phone_db', '<consumer_defined_db>', 'DEFAULT'),
      ('consumer_phone_schema', '<consumer_defined_schema>', 'DEFAULT'),
      ('consumer_phone_table', '<consumer_defined_table>', 'DEFAULT'),
      ('consumer_phone_join_field', '<consumer_defined_column_name>', 'DEFAULT'),
      ('consumer_phone_passthrough_field_1', '<consumer_defined_column_name>', 'DEFAULT'),
      ('consumer_phone_passthrough_field_2', '<consumer_defined_column_name>', 'DEFAULT')
  ) src (setting_name, setting_value, setting_profile)
) input
ON settings.setting_name = input.setting_name
 AND IFNULL(settings.setting_profile, '') = IFNULL(input.setting_profile, '')
WHEN NOT MATCHED THEN
  INSERT (setting_name, setting_value, setting_profile)
  VALUES (input.setting_name, input.setting_value, input.setting_profile)
WHEN MATCHED THEN
  UPDATE SET settings.setting_value = input.setting_value;

waterfall_match

MERGE INTO <application_name>.local.user_settings settings
USING (
  SELECT *
  FROM (
    VALUES
      ('consumer_waterfall_db', '<consumer_defined_db>', 'DEFAULT'),
      ('consumer_waterfall_schema', '<consumer_defined_schema>', 'DEFAULT'),
      ('consumer_waterfall_table', '<consumer_defined_table>', 'DEFAULT'),
      ('consumer_waterfall_passthrough_field_1', '<consumer_defined_column_name>', 'DEFAULT'),
      ('consumer_waterfall_passthrough_field_2', '<consumer_defined_column_name>', 'DEFAULT'),
      ('consumer_waterfall_hmid_join_field', '<consumer_defined_column_name>', 'DEFAULT'),
      ('consumer_waterfall_device_id_join_field', '<consumer_defined_column_name>', 'DEFAULT'),
      ('consumer_waterfall_ip_join_field', '<consumer_defined_column_name>', 'DEFAULT'),
      ('consumer_waterfall_phone_join_field', '<consumer_defined_column_name>', 'DEFAULT'),
      ('consumer_waterfall_email_join_field', '<consumer_defined_column_name>', 'DEFAULT'),
      ('consumer_waterfall_email_type_field', '<consumer_defined_column_name>', 'DEFAULT')
  ) src (setting_name, setting_value, setting_profile)
) input
ON settings.setting_name = input.setting_name
 AND IFNULL(settings.setting_profile, '') = IFNULL(input.setting_profile, '')
WHEN NOT MATCHED THEN
  INSERT (setting_name, setting_value, setting_profile)
  VALUES (input.setting_name, input.setting_value, input.setting_profile)
WHEN MATCHED THEN
  UPDATE SET settings.setting_value = input.setting_value;