In the world of Business Intelligence, the ability to combine data from different sources is essential.
Salesforce CRM Analytics (formerly Tableau CRM) offers a powerful feature called “joins” in its Recipe tool. You can think of creating joins as playing the role of a matchmaker for your data. They allow you to bring together data from multiple tables to gain a comprehensive understanding of your business. This blog post will explore the different types of joins available in CRM Analytics (and as a bonus these joins are also applicable in Data Cloud), providing clear explanations and examples to help you effectively leverage this functionality.
So⊠What are Joins?
A Join is an operation that allows you to combine rows of data from multiple tables based on a related column. This related column is known as a join key (denoted by the gold star in the below example). It must exist in both tables and contain a matching value to facilitate the âconnectionâ between rows. The result is a new table that contains additional related columns (denoted by the blue star) based on the join logic that was configured.
What’s the difference between a Lookup and a Join?
It is important to differentiate between Lookups and Joins in CRM Analytics. Lookups are great for simpler relationships when you want to bring over single related values from another table (for example enrich your opportunity data with User data based on the Opportunity Owner field), whereas Joins support more complex data relationships and aggregations: one-to-one, one-to-many, and many-to-many relationships. There are multiple types of Joins and each offers flexibility within the recipe builder to help you solve even the most complex data challenges!
CRM Analytics Recipe Builder: Join Configuration Screen
In the above image, the table that appears first in the âData to Joinâ list is considered your âLeftâ table. The one below it is considered your âRightâ table. In CRM Analytics the tables that show up here depend on how you create the Join node in the recipe builder. This is important to remember when we get into the join types in the next section of this blog post.
Join Keys refer to the related or common column(s) that exist in the two tables you are trying to join together. In the above screenshot under âJoin Keysâ we have AccountId as the primary key (unique identifier) of the âLeftâ Account table. In the Opportunity table we see it also has a field named AccountID which holds the AccountID (foreign key) that the Opportunity is related to. Sometimes you will need to do some detective work (data exploration) to find out which columns can be used to facilitate the join you are trying to build. Finally, the âAPI Name Prefix for Right Columnsâ field is used to change the text that will be appended to the field names that are created on your output table so you know where the data came from.
Below are the raw sample tables used for all of the Join Type explanations in the blog post. The gold stars represent the Primary Key for each table and the Blue star represents the foreign key in the Opportunity table that allows us to build a relationship with the Account table.
What are the Different Types of Joins?
For the following examples we will be using two sample datasets. The Opportunity sample has seven records while the Account sample contains four Account records.
Lookup: The âEssentialsâ Join
This join type lets you grab specific information from another table without changing up your main table at all. If you discovered the âLookup Multiple Valuesâ checkbox at the bottom of the join configuration screen – congratulations! Youâve just turned your Lookup join into a Multi Value lookup. If multiple matches are found in the table you are joining on, matching values will be consolidated into one comma-separated value field in your results like this:
Left Join: The “Keep it All” Join
With a left join you keep all the rows from your âLeftâ table and bring over any matching data from another table. You would use a left join to include all Opportunities from the âLeftâ table, even if CRM Analytics doesnât find a match in your âRightâ table, while still bringing in relevant Account details for matching rows. The resulting output contains all seven Opportunities with related Account details attached as new columns. Note that the Opportunity with Account XYZ345 contains no values for AccountId or AccountName. This is because Account XYZ345 does not exist in our âRightâ table.
Right Join: The “Reversed Left” Join
A right join is very similar to a left join but it is more interesting in the âRightâ table. This join keeps all the rows from the âRightâ table and adds matching data from your âLeftâ table. This time, the resulting output contains only six Opportunities with related Account details attached as new columns. The seventh row contains additional account data for FernCo from the âRightâ Table. The Opportunity columns are null because FernCo has no related opportunities in the âLeftâ Opportunity table. This highlights the âreversedâ nature of a Right join when comparing it to a Left Join.
Inner Join: The “Match Made in Heaven” Join
An inner join only keeps rows that have matching values in both the âRightâ and âLeftâ tables. Itâs like finding that perfect accessory to match your outfit. If multiple matches exist it will return a row for all matching values so itâs important to keep this in mind, as you may end up with duplicate rows. In our results we can see that only six records are returned. This is once again because Account XYZ345 does not exist in our âRightâ table, meaning that the Inner Join excludes it from our results.
Full Outer Join: The “Everyone’s Invited” Join
This join finds matches but includes all rows from both tables regardless if there is a match or not. In our output youâll notice that a row for FernCo is included in our results with null values in all the columns from our âLeftâ table, and a row for Opportunity XYZ345 is included with null values in all columns from our âRightâ table. This is because the Full Outer Join includes each row from both tables, even when there are no matches.
Cross Join: The “Mix & Match” Join
A cross join is a unique join type that doesnât require you to specify a join key. The reason for this is that this join type combines every row of data from each table together, regardless of if a match is found or not. This join explodes our output into 28 records! This is the result of every row from our âLeftâ table being matched with every row in our âRightâ table. For the mathematicians out there, this is known as a Cartesian product but for our purposes the result of a Cross Join will be the product of the number of records in each table multiplied together. In our case 7 Opportunities * 4 Accounts = 28 records in our output!
Conclusion
Understanding the different types of joins in CRM Analytics is crucial for anyone working with data. From the simple Lookup to the comprehensive Full Outer Join, each join type serves a unique purpose in combining and analyzing data from multiple tables. By mastering these join types, you can unlock valuable insights and make data-driven decisions to propel your business forward. Remember that these joins are not limited to CRM Analytics; they are also applicable in Data Cloud and most other data processing tools, expanding their usefulness across multiple platforms.
