How would you find subscribers who’ve made no purchases or customers who’ve made orders, if the information is located in separate data extensions in Salesforce Marketing Cloud? For this you need to have a basic understanding of how your data works
In this article, we explain how to combine data from two data extensions, along with the concept of JOINs in Salesforce Marketing Cloud (SFMC). We’ll do this by reviewing how you can combine data using SQL, as well as DESelect.
There are four main ways you can create a relationship between data extensions in SFMC using SQL. These are called JOINs.
(Data relationship using JOINs)
Before creating a JOIN between two data extensions, you need to understand how you want the data to be connected. You can choose to connect two data extensions (DEs) that have the same field. In our example, in the Customers DE it could be a Customer Id, which is a unique number that helps to identify them.
(Customers data extension)
You can also have a DE with Orders, where you can use a Customer Id field. This way you know what customer made what order. However, to send a communication to this customer, we need to combine the information about the order with his personal information, which is stored in another data extension.
(Orders data extension)
Now, let’s go back to the four JOINs. Using the INNER JOIN we can find customers who have made an order. The SQL query for this scenario will look like this.
(SQL query in SFMC)
Since there is a common Customer Id field, it allows us to select only those customers who have made the orders.
For the purposes of this article, we are going to continue using Customers DE and Orders DE as examples. Let’s tackle FULL JOIN now. This obtains all matching records from both data extensions whether the other data extension matches or not. So, if there are fields in Customers DE that do not match in Orders DE, or if there are fields in Orders DE that do not have matches in Customers DE, those fields will be listed regardless.
The LEFT JOIN will bring you all the records from the Customers DE and then the corresponding data from the Orders DE. The RIGHT JOIN will do the same but now with the Orders DE since we chose it second. Now onto the remaining scenarios…
Using our example data extensions, for the LEFT JOIN, you would like to find Customers who have made the Order and those who did not, and for instance, target them with the campaign to encourage their first purchase.
For the example of the RIGHT JOIN let’s take an example of Customers and Subscribers data extensions. Applying the RIGHT JOIN would enable you to identify the subscribers who aren’t yet customers, and target them accordingly.
Understanding data relationships is not the easiest part of segmentation in Marketing Cloud, but then writing code for it can be even less appealing. This is where DESelect comes in.
This alternative allows you to combine data extensions by dragging and dropping them on top of each other. Our tool then lets you combine the DEs using a visual representation of the JOINS.
(Create data relationships in DESelect)
In this video we show you how to create relationships between data extensions in Salesforce Marketing Cloud using both SQL and DESelect.
We hope that this article explained the basic combinations of data extensions in the Salesforce Marketing Cloud. In most cases, more than two data extensions are involved in this process. Imagine writing an SQL query for a relationship between 12 DEs? Yes, that’s right, sounds like a lot of work.
If you’d like to book a live demo to see how DESelect avoids the need for code in your advanced segmentation, one of our experts would love to help you.
Get the eBook and Read it anytime
70+ pages of actionable content and best practices to help you take your Marketing Cloud to the next level.