Automate Profile Attributes Update in Automation Studio

Author: Florin Valean

Profile update use case

As an exercise we'll aim to update a profile attribute in a Salesforce Marketing Cloud account with multiple business units. Let's assume we want the profile attribute to store the ID of the business unit that performs the email send. To achieve this we are going to use a SQL Query, a Data Extract and a Import File activity in Automation Studio.
This scenario can be applied to a Salesforce Marketing Cloud Enterprise 2.0 account and once the profile is populated with the business unit id (member id) it can be used for instance to define a subscriber filter at business unit level.
Check out this page for more details on how to define subscriber filters.


Prerequisites

In a multi-org marketing cloud account (Enterprise 2.0) we are going to create a custom profile attribute called BusinessUnitID. We don't want this attribute to be visible to customers in their profile center therefore we'll set it as hidden. As a best practice it is recommended to define a default value for the custom attribute - in this case the ID of the parent business unit seems fine. We can also define a list of possible values to restrict the values the new attribute can take but this is optional. The profile attribute can be created in Email Studio > Profile Management as shown below:

Profile attribute

A data extension is also needed to store the subscribers that need to be updated. This data extension is going to be populated using a SQL query. For this example let's call this data extension SetProfileAttributes. It includes three fields (attributes) as shown in the table below:

Attribute nameAttribute data type
Subscriber KeyText (50)
Email AddressEmailAddress
BusinessUnitIDNumber

Subscriber Key and Email Address are mandatory fields for subscriber updates via import activities. Notice the space in the field name for Subscriber Key and Email Address - this is to match the attribute names in All Subscribers list so that the import activity can be automatically mapped by header row (this way it doesn't need to be mapped manually).

The next step would be to create the automation in Automation Studio, se the following sections below.


Automation workflow

This automation example for profile attributes update includes five steps as shown below:

Automation workflow

Step 1: SQL Query activity

This SQL code selects all subscribers in a Salesforce Marketing Cloud Enterprise 2.0 account with their email address and the ID of the business unit that initiated the send. It gets the data by joining three different data views in SFMC: _Subscribers to get the Subscriber Key and Email Address, _Sent to get the on-your-behalf account id (the ID of the busniness unit in which the send process was performed) and _EnterpriseAttribute to compare the BusinessUnitID profile attribute with the on-your-behalf account id from _Sent so that this query only selects records that need to be updated.

SELECT sub.SubscriberKey "Subscriber Key", sub.EmailAddress "Email Address", sent.OYBAccountID "BusinessUnitID" FROM _Subscribers sub inner join _Sent sent on sub.SubscriberKey = sent.SubscriberKey inner join _EnterpriseAttribute ea on sub.SubscriberID = ea._SubscriberID WHERE sent.OYBAccountID <> ea.BusinessUnitID

Configure this SQL Query activity to populate the data extension SetProfileAttributes.

A comprehensive and very useful documentation on SFMC data views can be found here.

Step 2: Record count verification

This is only a verification step, if the record count for SetProfileAttributes data extension is equal to 0 (zero) then the automation will stop at this step otherwise it will proceed to step 3.

Step 3: Data extension extract

This Data Extract activity will extract data from the SetProfileAttributes data extension and it will export it into a file on the SFMC safehouse.

Step 4: File transfer

This File Transfer activity will move the file exported in the previous step from the safehouse to the SFMC Enhanced FTP.

Step 5: Import file

This Import File activity will import the content from the file copied to SFMC Enhanced FTP in the previous step. This import activity can be configured to perform an update only action on the All Subscribers list to update the BusinessUnitID profile attribute.

BusinessUnitID profile attribute can now be used to define a subscriber filter at business unit level - check out this page for more details.

Share this page
Stay in touch

Subscribe to the newsletter

p1 p2 p3