Amazon Marketing Cloud Export Integration

Copy

• Copy for LLM

  Copy page as Markdown for LLMs
• View as Markdown

  Open this page as Markdown
• Open in ChatGPT

  Get insights from ChatGPT
• Open in Claude

  Get insights from Claude
• Connect to Cursor

  Install MCP server on Cursor
• Connect to VS Code

  Install MCP server on VS Code

Introduction Video

Overview

Integrating Amazon Marketing Cloud (AMC) and Treasure Data CDP allows advertisers to use Amazon Ads to pass strategic CDP segments from Treasure Data in a pseudonymized form to AMC. Advertisers can then combine the CDP inputs with Amazon Ads signals and obtain unique insights on topics such as media impact, audience segmentation, segment overlap, and customer journeys in a privacy-safe manner. Learn more about how integrating Treasure Data CDP with AMC can drive better Ad campaigns.

This Amazon Marketing Cloud Export Integration lets you write job results from Treasure Data and upload pseudonymized audience datasets directly to Amazon Marketing Cloud.

What can you do with this Integration?

• Upload pseudonymized audiences dataset to Amazon Marketing Cloud.
• Delete identities in all existing datasets.
• Create a rule-based audience and activate it directly in Amazon DSP.

Prerequisites

• Basic knowledge of Treasure Data, including TD Toolbelt.
• Amazon Marketing Cloud account.
• S3 bucket that grant access to an Amazon Marketing Cloud account.
• Amazon DSP account that was invited into the AMC instance.
• (Optional) Create the dataset definition before using the Amazon Marketing Cloud Export Integration. Visit Treasure Boxes to see an example of dataset creation workflow. This integration supports creating a dataset the first time the activation runs.

Requirements and Limitations

• Query columns must be specified with the exact column names (case insensitive) and data type.
• It is recommended not to use the existing S3 bucket tied to the AMC instance. Create a new S3 bucket for data upload.
• An Amazon DSP minimum audience size is 2,000 identities.

Static IP Address of Treasure Data Integration

If your security policy requires IP whitelisting, you must add Treasure Data's IP addresses to your allowlist to ensure a successful connection.

Please find the complete list of static IP addresses, organized by region, at the following link:
https://api-docs.treasuredata.com/en/overview/ip-addresses-integrations-result-workers/

Get Amazon Marketing Cloud info

Login into https://advertising.amazon.com/marketing-cloud.

Obtain Amazon Marketing Cloud Instance ID and Account ID

After logging into an Amazon Marketing Cloud instance, perform the following steps to obtain the Amazon Marketing Cloud Instance ID, Account ID,and Data upload AWS account ID information.

1. Obtain the Amazon Marketing Cloud Instance ID from the Instance list.
2. View the Amazon Marketing Cloud Account ID assigned to parameter entityId.

3. You can get the Data upload AWS account ID from the Instance Info page.

Prepare Dataset as Upload Target

The following are examples of dataset definitions. If your target dataset exists, you can skip this section.

Example of a simple Dimension dataset without any hashed PII columns

In this dataset, there are two columns: product_asin  and product_sku. Note that if the schema from your query doesn't match this, the upload will result in an error.

{ "dataSet": {"columns":[{"name": "product_asin","columnType": "DIMENSION","dataType": "STRING"},{"name": "product_sku","columnType": "METRIC","dataType": "STRING"}],"dataSetId": "mydemosimledimensionds","description": "my demo dimension dataset"}}

Example of a fact dataset with email as hashed PII and TCF as consent type

In this example, there are three columns: email (as hashedPII), record_date (isMainEventTime = True indicates this is a fact dataset), and tcf_string (is tcf string of consent).

{"dataSet": {"dataSetId": "mydemofactdswithidentity","columns": [{"columnType": "DIMENSION","dataType": "STRING","externalUserIdType": {"hashedPii": "EMAIL"},"name": "email"},{"columnType": "DIMENSION","dataType": "DATE","isMainEventTime": true,"name": "record_date"},{"columnType": "DIMENSION","dataType": "STRING","name": "tcf_string","consentType": "TCF"}],"countryCode": "US"}}

Obtain Dataset ID and Dataset Fields Name

1. From the Amazon Query editor page, you can obtain the Dataset ID and Dataset Fields Name.

2. Select the info icon to obtain each filename's data type.

Config S3 Bucket to allow access from Data upload AWS account ID

1. Log in to S3, navigate to your bucket > permission tab, and select Edit beneath the Bucket policy.

2. Copy and paste this configuration after replacing your Data upload AWS account ID and Bucket name and select Save.

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "AWS": "arn:aws:iam::{{Data upload AWS account ID}}:root"
      },
      "Action": [
        "s3:GetObject",
        "s3:GetObjectVersion",
        "s3:ListBucket",
        "s3:PutObject",
        "s3:PutObjectAcl",
        "s3:GetObjectTagging",
        "s3:GetBucketTagging"
      ],
      "Resource": [
        "arn:aws:s3:::{{bucket name}}/*",
        "arn:aws:s3:::{{bucket name}}"
      ]
    }
  ]
}

3. Define tags for your bucket

To define a tag for your instance, perform the following steps:

4. Navigate to your Amazon S3 console and click on the bucket name you want to associate tags.
5. Click Properties and scroll to the Tags section. Click Edit.
6. Click Add tag to define a key for the tag and provide a value for the key. For our purpose, define the key a" "instance"d" and type the identifier of the instance you use to upload data as the value for the key.
7. Click Save.

Visit https://advertising.amazon.com/API/docs/en-us/guides/amazon-marketing-cloud/advertiser-data-upload/advertiser-data-s3-bucket for more details.

Use the TD Console to Create a Connection

Before running your query, you must create and configure the data connection in Treasure Data. As part of the data connection, you provide authentication to access the integration.

Create a New Authentication

1. Open TD Console.
2. Navigate to Integrations Hub > Catalog.
3. Search for Amazon Marketing Cloud and select Amazon Marketing Cloud.

4. Select the Click here link in New Authentication to connect to a new Amazon Account.

5. You will be redirected to the Amazon Marketing Cloud instance, where you can log in using OAuth. Provide the username and password.

6. Select Allow to accept consent screen, which redirects to the TD console.

7. Fill in the required credentials fields.

8. Select Continue.
9. Type a name for your authentication.
10. Select Done.

The following table describes the parameters for configuring the Amazon Marketing Cloud Export Integration.

Define your Query

1. Navigate to Data Workbench > Queries.
2. Select New Query.
3. Run the query to validate the result set.

Specify the Result Export Target

1. Select Export Results

2. Select an existing integration authentication.

3. Define any additional Export Results details. In your export integration content, review the integration parameters.

Upload or Delete Operation

1. Select Upload or Delete for Dataset Operation.

Check o" "Create new dataset if it does not exist "t" to create a new dataset on the fly. Provide the dataset definition described in the section. "Prepare Dataset as upload target."

Create Rule-based Audiences

The following table describes the configuration parameters for the Amazon Marketing Cloud export integration.

Requirements for Uploading Dataset Query

• To upload data to the dataset, your query result must have a column name matching the field name (case-sensitive). All mismatches between query columns and dataset fields are ignored.
• The main event time column is required when we upload data to the FACT dataset.
• All non-nullable fields of the dataset are required in the query.
• If a dataset field is non-nullable, and a row of result queries for this field has a null value, the row is skipped. For example:
• We have the FACT dataset tutorial_off_amazon_purchaseswith purchase_time as the main event time field. All fields are non-nullable.

Sample query:

SELECT
   product_name, 
   product_sku, 
   product_quantity, 
   purchase_time, 
   purchase_value 
FROM 
   table_name;

Alternatively, you can use an alias to match your query column name with dataset fields. Sample query:

SELECT 
  column_a AS product_name,
  column_b AS product_sku,
  column_c AS product_quantity,
  column_d AS purchase_time,
  column_e AS purchase_value
FROM
  table_name

The data type of each column from the query result must be compatible with the dataset field.

Requirements for Deleting Identities from Existing Dataset Query

To delete identities from all existing data sets, your query result must have at least one identity column name. The identity column name should include first_name, last_name, email, phone, address, city, state, zip, andcountry_code**.** Columns with other names are ignored. Sample query:

SELECT 
  first_name,
  last_name,
  email
FROM
  table_name

Requirements for Creating Rule-based Audiences

Creating an Audience doesn't require data from TD. It runs a query from the dataset and creates an audience based on the query result. So, we only use "Select 1" to trigger a job from the TD side. We don't allow any query to return more than one row from the TD side.

Activate a Segment in Audience Studio

You can also send segment data to the target platform by creating an activation in the Audience Studio.

1. Navigate to Audience Studio.
2. Select a parent segment.
3. Open the target segment, right-mouse click, and then select Create Activation.
4. In the Details panel, enter an Activation name and configure the activation according to the previous section on Configuration Parameters.
5. Customize the activation output in the Output Mapping panel.

• Attribute Columns
  • Select Export All Columns to export all columns without making any changes.
  • Select + Add Columns to add specific columns for the export. The Output Column Name pre-populates with the same Source column name. You can update the Output Column Name. Continue to select + Add Columnsto add new columns for your activation output.
• String Builder
  • + Add string to create strings for export. Select from the following values:
    • String: Choose any value; use text to create a custom value.
    • Timestamp: The date and time of the export.
    • Segment Id: The segment ID number.
    • Segment Name: The segment name.
    • Audience Id: The parent segment number.

6. Set a Schedule.

• Select the values to define your schedule and optionally include email notifications.

7. Select Create.

(Optional) Schedule Query Export Jobs

You can use Scheduled Jobs with Result Export to periodically write the output result to a target destination that you specify.

Treasure Data's scheduler feature supports periodic query execution to achieve high availability.

When two specifications provide conflicting schedule specifications, the specification requesting to execute more often is followed while the other schedule specification is ignored.

For example, if the cron schedule is '0 0 1 * 1', then the 'day of month' specification and 'day of week' are discordant because the former specification requires it to run every first day of each month at midnight (00:00), while the latter specification requires it to run every Monday at midnight (00:00). The latter specification is followed.

Scheduling your Job Using TD Console

1. Navigate to Data Workbench > Queries

2. Create a new query or select an existing query.

3. Next to Schedule, select None.

4. In the drop-down, select one of the following schedule options:

   Drop-down Value	Description
   Custom cron...	Review Custom cron... details.
   @daily (midnight)	Run once a day at midnight (00:00 am) in the specified time zone.
   @hourly (:00)	Run every hour at 00 minutes.
   None	No schedule.

Custom cron... Details

 *    *    *    *    *
 -    -    -    -    -
 |    |    |    |    |
 |    |    |    |    +----- day of week (0 - 6) (Sunday=0)
 |    |    |    +---------- month (1 - 12)
 |    |    +--------------- day of month (1 - 31)
 |    +-------------------- hour (0 - 23)
 +------------------------- min (0 - 59)

The following named entries can be used:

• Day of Week: sun, mon, tue, wed, thu, fri, sat.
• Month: jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec.

A single space is required between each field. The values for each field can be composed of:

1. (Optional) You can delay the start time of a query by enabling the Delay execution.

Execute the Query

Save the query with a name and run, or just run the query. Upon successful completion of the query, the query result is automatically exported to the specified destination.

(Optional) You can delay the start time of a query by enabling the Delay execution.

(Optional) Configure Export Results in Workflow

Within Treasure Workflow, you can specify the use of this integration to export data.

Learn more about Exporting Data with Parameters.

Example Workflow for uploading a dataset

_export:
  td:
    database: amc_db
 
+amc_task:
  td>: upload.sql
  database: ${td.database}
  result_connection: new_amc_auth
  result_settings:
    type: amazon_marketing_cloud
target: dataset 
operation: upload
dataset_id: dataset_id
bucket: bucket_name
path_prefix: path_prefix/
file_name_prefix: file_name_prefix
wait_until_finish: true
clean_upload_file: true

Example Workflow for creating dataset and upload

_export:  
  td:    
    database: amc_db 


+amc_task:  
  td>: upload.sql  
  database: ${td.database}  
  result_connection: new_amc_auth  
  result_settings:    
    type: amazon_marketing_cloud 
    target: dataset 
    operation: upload
    dataset_id: dataset_id 
    create_dataset: true 
    dataset_definition: dataset_definition_in_json_string
    bucket: bucket_name
    path_prefix: path_prefix
    file_name_prefix: file_name_prefix
    wait_until_finish: true
    clean_upload_file: true 

Example Workflow for deleting a dataset

_export:  
  td:    
    database: amc_db 

+amc_task:  
  td>: delete.sql  
  database: ${td.database}  
  result_connection: new_amc_auth  
  result_settings:    
    type: amazon_marketing_cloud    
    target: dataset    
    operation: delete

Example Workflow for creating rule-based audiences

_export:
  td:
    database: amc_db
 
+amc_task:
  td>: audience.sql
  database: ${td.database}
  result_connection: new_amc_auth
  result_settings:
    type: amazon_marketing_cloud 
    target: rule_based_audiences
    amc_instance_id: amc_instance_id
    amc_account_id: amc_account_id
    audience_name: "test_audience"
    audience_description: "test audience description"
    advertiser_id: 123
    query: "SELECT user_id FROM dsp_impressions"
    time_window_start: "2023-06-25T00:00:00"
    time_window_end: "2023-07-25T00:00:00"
    refresh_rate_days: 1
    time_window_relative: false

External Reference

• Fact vs. Dimension datasets: https://docs.aws.amazon.com/solutions/latest/amazon-marketing-cloud-uploader-from-aws/amc-fact-compared-with-dimension-datasets.html
• Dataset creation guideline: https://advertising.amazon.com/API/docs/en-us/guides/amazon-marketing-cloud/advertiser-data-upload/advertiser-data-sets