How to combine data from 2 data extensions with or without using SQL
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.
Data extension 101: How to combine data extensions in Salesforce Marketing Cloud using SQL
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.
Combine any data extension in Salesforce Marketing Cloud using DESelect
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 solution 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 any data extension 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.
A+ Marketing Cloud Strategies for HigherEd Success: A Fireside Chat with Cornell and UMGC (Transcript)Anthony Lamot: Hello, and welcome everyone to another webinar by DESelect. Today. We have a really exciting topic getting an a plus with Marketing Cloud strategies for hired success today. The fireside chat. And I’m very excited to have our customers from Cornell University as well as University of Maryland Global Campus here. So let’s […]February 26, 2024
Webinar | State of Marketing Optimization: Top Trends to Get the Most Out of Your Personalization and AI in 2024Anthony Lamot: Hi, everyone, and welcome to this webinar organized by DESelect. Today we’re going to be talking about the state of marketing. Thank you all for joining our webinar, as we are going to take you through some top trends to get the most out of your personalization. And AI for this year for […]February 7, 2024What Is Salesforce Marketing Cloud? A Quickstart Guide Never heard of Salesforce Marketing Cloud or simply don’t know enough? You have come to the right place. Today, we will be discussing the platform in a little more detail, along with the benefits of investing in SFMC for the long-term strategic future. As we embark on […]January 30, 2024Company Culture Meet Our Team: Customer Success Manager, Alex Divrioti Meet Alex, our newest member of the Customer Success Team, who left sunny Greece behind and moved to Belgium to work at DESelect. Get her take on her DESelect journey → Alex has a passion for innovation, change and exponential growth. That passion makes her […]January 24, 2024