ESP - Salesforce Marketing Cloud

Destination table¶

The following are the details about the destination table:

• A tenant can have multiple destination tables.

• Typically, Acquia creates one table per content type that you want to send. You can request Acquia to create additional tables, if required.

• One destination table corresponds to one data extension in SFMC. The name of that destination table is prefixed with A1_ in the corresponding data extension.

• If the data extension already exists, the creation of the destination table fails.

• You must ensure that your destination table contains all the columns that you might need for your campaigns. For example, if you plan to use the destination table to run an abandoned cart campaign, you can get all the usual product-related attributes, such as product sale price, product URL, product image URL, product name, product number, added to the table.

• The following is the format for the column names in the destination table:

  [name of the UDM+ table]_[name of the column in that UDM+ table

  For example, customersummary_email, customersummary_mastercustomerid, product_name, product_sourceproductnumber, product_producturl.

• By default, CDP exports specific columns:

  • campaign_id

  • campaign_executionid

  • campaign_executiondate

  • Any field that ends with email. For example, customersummary_email

  • pitem_limit_count: This column is required if product content is used in campaigns.

• For recurring campaigns, you must define the primary key for the destination table. Acquia recommends that you select PK in the CDP user interface for the following columns:

  • customersummary_mastercustomerid

  • customersummary_email

  • campaign_id

  • campaign_executionid

  • pitem_limit_count (for product-related content).

• You can export both customer and product attributes through the same destination table. However, in that case, customer attributes are duplicated.

• You can request Acquia to set the must-have attributes in your campaign as required fields in the destination table.

Sample use cases:

The following example explains the use cases for exporting customer attributes, product attributes, or both:

• Only customer attributes: Export these attributes to enrich existing customer attributes on SFMC with CDP insights such as segments, ML models, and customer summary attributes. Examples of customer summary attributes are number of purchases and last time purchase date.

• Only product attributes: Export these attributes to provide the list of recommended products, abandoned cart items, and browsed products. This table must include an email address field as the key column to link customers to the Data Extension table.

• customer + product attributes: Export these attributes to have a combination of the preceding use cases.

Creating an API user¶

To create an API user:

1. Log in to the SFMC user interface, access the Admin section, click My Users > Create.

2. Specify the following details:

   • Name: Enter the CDP API user name.

   • Reply Email Address: Enter the CDP mailing list that was created for your account. For example, [email protected].

   • Notification Email Address: Enter the CDP mailing list that was created for your account. For example, [email protected].

   • Username: Enter a unique user name. Acquia recommends that you set this value to be something unequivocal. For example, yourcompanynameagiloneapiuser.

   • Timezone: Select GMT.

   • API User: Select this checkbox.

   • Culture code: Select English.

   • Set the following user permissions:

     • Administrative data management

     • Grant the user access to the web services

     • Manage data extension data and retention policy

   • Set the following values for Marketing Cloud permissions and roles:

     • Applications for user: Email and Automation Studio

     • Marketing Cloud roles for user: Marketing Cloud Administrator

   Note

   This API user must be able to retrieve data, extract data, create data extensions, and push data to data extensions.

3. After the user is created, share the details with Acquia.

Sharing SFMC FTP details with Acquia¶

1. Share SFMC FTP information with Acquia. Your SFMC account is connected to an FTP account. The FTP account is used for data import and export feeds. To connect to CDP, you must have:

   • FTP url

   • FTP username

   • FTP password

Sharing SFMC subscriber key details with Acquia¶

The subscriber key is used to send emails. Each SFMC instance has a unique data extension configuration.

To obtain this information from your SFMC instance:

1. In your SFMC account, click Subscribers > Data Extensions > Create.

2. Obtain the name of the SFMC subscriber key from the drop-down menu after Email Address or Subscriber Key.

Configuring and scheduling file extracts¶

To configure and schedule file extracts:

1. Click Journey Builder > Automation Studio. For more information, see Marketing Cloud Journeys and Automations.

   

2. Click Activities > Create Activity > Data Extract. For more information, see Data Extract Activity and Automation Studio Activities.

   

3. Enter the following details:

   • Name: Enter the name of the data extract activity.

   • Description: Enter the description for the data extract activity.

   • File Naming Pattern: Specify the pattern for the file name. For example, <fileName>_%%Year%%%%Month%%%%Day%%.zip.

   • Extract Type: Select a type of extract. For example, Tracking Extract. For more information, see Extract Types and Configure a Tracking Extract.

   

4. Click Next.

5. In Configuration, select the following options:

   • Extract Conversions

   • Extract Not Sent

   • Extract Send Jobs

   • Extract Subscribers

   • Extract Unsubs

   • Extract Bounces

   • Extract Clicks

   • Extract Opens

   • Extract Sent

   • Extract Survey Responses

   

6. Click Next.

7. Click Finish.

8. Click Activities > Create Activity > File Transfer.

   For more information, see File Transfer.

   

9. Enter the following details:

   • Name: Enter the name of the file transfer activity.

   • File Action: Select Move a File From Safehouse.

   

10. Click Next.

11. In Configuration, specify the same name pattern in File Naming Pattern as you specified earlier.

    

12. Click Next.

13. Click Overview > New Automation.

    

14. In Workflow, drag and drop the data extract and file transfer activities created earlier.

    

    Ensure that you select the activities that you created earlier, and do not create new data extract or file transfer activities.

    

15. Click Schedule and select the time when you want to trigger the automation process.

    

16. Repeat the process for daily incremental extracts.

Creating CDP campaigns¶

1. To get a destination table with specific columns created in CDP, contact Acquia Support.

2. Create a CDP campaign to leverage the newly created destination table with specific columns.

   For more information on the destination table, see Destination table.