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

Ujjwal SFMC, SQL Leave a Comment

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/

 

Leave a Reply

Your email address will not be published.