Populating / Updating a Column in a Data Extension from Another DE in SFMC

This is a very common use case in Salesforce Marketing Cloud where you have been provided with a list of contacts (email addresses) and you are required to send a targeted message to those contacts that meet a certain condition. This could be status (engaged, unengaged, subscribed, unsubscribed), category affinity (services/products a contact prefers), last service call, last purchase date etc.

Let us assume in our case, we are a telecom company and we want to send a marketing email to those contacts whose status is unengaged with a discount coupon offer. This status column resides in another data extension (Contact_Master) which has several columns email_address, first_name, last_name, cus_status, last_purchase_date, category affinity. Now, you have been provided with a list of email addresses and the task is to send coupon email to those who status is unengaged in an attempt to make them engaged again. First, let’s create a new DE for this targeted send and name it Coupon_Email. We will create two columns in this new DE – email_address (type email address) and status (type same as cus_status field in Contact_Master DE). Now we have two DE’s as shown below and we need to find and update the status column in Coupon_Email DE.

Contact_Master
email_address	first_name	last_name	cus_status	last_purchase_date	category affinity
email1	fname1	lname1	engaged	01/05/2021 00:00:00	Insurance
email2	fname2	lname2	unengaged	2 May 2021	Travel
email3	fname3	lname3	unsubscribed	3 May 2021	Legal
email4	fname4	lname4	unsubscribed	4 May 2021	Insurance
email5	fname5	lname5	unengaged	5 May 2021	Travel

Coupon_Email
email_address	cus_status
email1
email2
email5
email4
email8

Now, we can find the cus_status value for Coupon_Email DE by matching the email_address fields in two DE’s. This can be done either with a simple SQL query or using Data Relationships for people who do not prefer SQL.

SQL Query:

SELECT email_address FROM [Coupon_Email] CE
JOIN Contact_Master CM
ON CE.email_address = CM.email_address
WHERE cus_status = ‘unengaged’If you run this query in Query Studio, it will provide all email addresses which have cus_status value as Unengaged.

Following query will update cus_status value for all contacts

SELECT email_address, CM.cus_status FROM [Coupon_Email] CE
JOIN Contact_Master CM
ON CE.email_address = CM.email_address
Use this Query in Automation Studio > Query Activities and select the target DE and Action as Update.

 The following query will provide you number of customers with different status values. For example – number of engaged customers, number of unengaged customers, number of unsubscribed etc.

SELECT cus_status, Count(*) AS Total FROM [Coupon_Email] CE
JOIN Contact_Master CM
ON CE.email_address = CM.email_address
Group BY cus_status

Data Relationships

For people who do not prefer using SQL queries, this can easily be achieved using Data Relationships. Data relationships can be used to connect two data extensions if they have a common field. Once the two data extensions are connected, a common field column can be used to match against each other and find/update the value of other fields that are present in another data extension.

1. In Email Studio, bring your cursor on Subscribers and select Data Relationships from the drop-down
2. The next screen will be My Data Relationships. Here click on Create to build a new Data Relationship
3. Give this an appropriate name, external key and description. Click on the dropdown Select left item, and locate and select Coupon_Email DE. In the next step, click on the dropdown Select the right item and locate and select Contact_Master DE. The last step is to click on Add. The system will automatically select email_address from both data extensions as that is the common key. After this click on Save at the top and this will save the Data Relationship.
4. Once the data relationship is saved, you can go to Subscribers > Data extension and select the Coupon_Email DE. Now if you try filtering this, you will see all fields from the Contact_Master DE on the left side. You can drag and drop the field that you want to filter it on. In our case, we will drag and drop the cus_status field from the left and select the filter as equals “Unengaged”.

 Here is a useful video that I found on Combining Data extensions: https://www.youtube.com/watch?v=iG7p8GPrm9Y

Here is a very good resource on SFMC SQL Basics: https://mateuszdabrowski.pl/docs/sql/sfmc-sql-basics/