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.
Conclusion
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.
Latest Articles
-
The Ultimate Guide to Salesforce Marketing Cloud Advanced Edition
At the Marketing Keynote at Dreamforce 2024, Salesforce announced an exciting expansion to its suite of marketing solutions — the Marketing Cloud Advanced Edition, which builds upon the existing Marketing Cloud Growth Edition and offers even more powerful tools to help small and medium-sized businesses (SMBs) engage their customers in a more personalized and efficient way. This article explores the features, benefits, and impact of Advanced Edition and what it means for SMBs striving to innovate.September 19, 2024Unlocking the Potential of Salesforce Data Cloud: A Comprehensive Guide
Introduction to Salesforce Data Cloud In recent years, Salesforce has been making a strong push into the Customer Data Platform (CDP) space with Salesforce Data Cloud. Data Cloud enables businesses to manage and leverage their customer data across multiple touchpoints, enabling marketers to leverage their data better than ever. By bringing together data from various […]June 13, 2024Zandra Marquez, Senior Customer Success Manager
Company Culture Meet our team: Senior Customer Success Manager, Zandra Marquez We have a superstar in our Customer Success Team and her name is Zandra: a true gem with top-tier Salesforce Marketing Cloud knowledge. Get her take on her DESelect journey → Zandra started at DESelect as a Customer Success Manager, where she quickly […]May 28, 2024Harnessing the Power of Relative Dates in Salesforce Marketing Cloud (SFMC)
The ability to adapt and react with timely precision is key in the marketing world. Marketing Cloud Engagement (MCE), also often referred to as Salesforce Marketing Cloud (SFMC), offers robust tools to facilitate such agility, particularly through the use of relative dates. This feature enables marketers and SFMC admins to automate campaigns with a precision […]April 29, 2024
Join our newsletter to receive updates and helpful SFMC guides.
