Mastering Data Preparation with Salesforce CRM Analytics Recipes

Introduction

While Data Analytics has been a longstanding practice, the intricacies and challenges associated with manually analyzing large datasets have been a historical impediment. However, with the continuous advancement of technology and the emergence of Business Intelligence (BI) tools in the market, the landscape of data analytics has undergone a transformative shift, making it more efficient and accessible.

In this blog, we aim to delve into the evolution of data analytics, highlighting the historical challenges faced during manual analysis of extensive datasets. Specifically, we will unravel the process of data preparation using CRM Analytics Recipes within the Salesforce platform.

CRM Analytics:

• CRM Analytics plays a pivotal role as a vital cloud-based analytics and business intelligence (BI) solution, seamlessly integrated into the robust Salesforce platform. By leveraging the CRM Analytics solution one can integrate Salesforce data with external data and provides the potential to analyze large datasets while implementing the predictive analytics.  
• CRM Analytics was formerly known as Wave/Einstein Analytics/Tableau CRM.
• CRM analytics is a dominant analytics platform that magnifies the reporting capabilities within your Salesforce org.

Datasets:

• A CRM Analytics dataset is a collection of related data that can be viewed in a tabular format or graph. The data may arrive from multiple sources, including Salesforce objects, external data sources, and even other datasets.
• When you bring in the data into analytics, the imported data is computed and then it is saved in CRM Analytics as datasets

Recipes:

• Recipe is a tool in CRM analytics which is used to extract and transform data from Salesforce or other connected data sources.
• Once processed, this data is registered (stored) in a new CRM Analytics dataset.

Permissions in CRM Analytics

To manage a Recipe, a user will need to have the “Edit Analytics Recipe” and “Edit Analytics Dataflow” permissions which can be found under the system permissions for CRM Analytics Plus permission sets, and the organization will need Data Sync and Connections enabled in Analytics settings.

Preparing data using Recipes

• To create a recipe ->Navigate to Analytics Studio -> Click on Create -> Dataset ->Salesforce Data or Navigate to Analytics Studio -> Data Manager -> Manage Dataflows ->Dataflows & Recipes -> Click on Create Recipe under Recipe column -> Give your recipe a name.
• In Recipes, we have multiple nodes to perform different transformations and each node symbolizes a transformation in the recipe.

We always start with adding data/dataset by adding input data.

Nodes in Recipe:

We have 8 different nodes in Recipe:

1. Add Dataset
2. Transform
3. Filter
4. Aggregate
5. Discovery Predict
6. Join
7. Append
8. Output

Add Dataset: This permits us to add any dataset that exists in CRM Analytics. It can come from Salesforce Synced Objects or other datasets that exist in CRM Analytics either from csv or other dataflows and recipes or even due to connections from other non-Salesforce databases.

Transform: Transform node allows us to add a new formula column to the dataset (ComputeExpression in a dataflow)! Allowing us to do numerous relative row calculations (ComputeRelative in a dataflow). We can even retitle an API or label, bucket, flatten, clustering, sentimental analysis, pivots and a lot more!

Filter: This is used to filter records.

Aggregate: Use an Aggregate node in CRM Analytics to roll up data. You can aggregate results to a higher level of graininess or roll up hierarchical data. 

Discovery Predict: Use the Discovery Predict transformation in Data Prep recipes to populate CRM Analytics datasets with predictions, top predictors and improvements.

Append: Use an Append node in a Data Prep recipe in CRM Analytics to pile rows coming from multiple sets of input data into one dataset.  

Output: Output is leveraged to save the transformed data either as a CSV file or as a dataset.

Join: Use a join node in CRM Analytics to add columns of data from related objects to operating data in a recipe. 

There are six types of joins in CRM Analytics:

1. Lookup Join: - Includes all rows from the recipe dataset and only matching rows from the dataset/right stream. When multiple record values are found it returns only one record.
2. Left Join: - Includes all rows from the recipe dataset and only matching rows from the filter dataset. When multiple record values are found, it returns all records.
3. Right Join: - Includes all rows from filter dataset and only matching rows from recipe dataset. When multiple record values are found, it returns all records.
4. Inner Join: - Includes only matching records from the filter and recipe dataset. When multiple record values are found, it returns all records.
5. Outer Join: - Includes all rows from both datasets regardless of matching rows. When multiple record values are found, it returns all records.
6. Cross Join: - Cross Join combines unrelated records and includes all rows from the left and right streams. The cross-join pairs every row from one dataset with every row of another dataset, unlike other joins that use keys to find matches.

Scenario:

Bruce Sergio is a System administrator at OnCloud Solutions (OCS). He just started learning CRM analytics and got the following requirements from his business. The business wants to:

1. Create a dataset combining Contact and Account using Account Id (using lookup join).
2. Apply transformation on Billing State field to group the data in following way -> All the states starting with ‘A’ should come under a label ‘States starting with A’. Apply transformation for all the state values.
3. Drop the original Billing State column.
4. Save the recipe result to ContactWithAccount dataset.

To solve Bruce’s business requirement, we are going to perform 8 steps using CRM Analytics Recipe. We must:

• Navigate to Analytics Studio -> Click on Create -> Dataset ->Salesforce Data or Navigate to Analytics Studio -> Data Manager -> Manage Dataflows ->Dataflows & Recipes -> Click on Create Recipe under Recipe column -> Give your recipe a name.

 

 

• Click Add Input Data option and select the check box next to the connected object Contact. On the right area, In the Selected Columns, you can choose columns to include selected input data or all columns to automatically update every available column when the recipe sync succeeds.

• Click Next.
• The following step is to select and add the join node to bring data from the Account object.
  1. Click Add Node and select Join.
  2. Choose the connected object which is Account to join and then choose which columns to include.
  3. Click Next.
  4. Select the join type, i.e., Lookup
  5. Select Account Id as the Join Key.
  6. Utilize the Preview tab to preview columns and their data.
  7. Click on Apply to add the node to the recipe, once it looks good.

• Click Transform. Select Billing State column and then choose Bucket option. Add a label ‘Starting with A’ and select State values starting with A. Click on Apply.

• It’s about time to save the above created recipe result to the dataset.
  1. Click on '+' to Add Node and select Output.
  2. Enter the dataset name – ContactWithAccount and other details.

• Almost there! Click the Save and Run button, once everything looks good.
• Click Save again.

 

 Schedule a Recipe to Run Automatically

• You can schedule your recipes to run automatically or after an event. For instance, you can use time-based scheduling to make certain that new data is available to your dataset. Avail the event-based scheduling option to run a recipe succeeding the sync to ensure that the datasets include the latest data.
• You get multiple options to schedule a recipe to run hourly, weekly, monthly, or on specific days of the week or dates.
• Navigate to the data manager and click the Recipes tab.
• Click the drop-down present on the recipe name you want to run and select Schedule.
• Choose the Scheduled Mode and the time to run the recipe.

Once the job status shows Successful, Traverse to Data Assets tab to access newly created dataset.

Advantages of Recipes over Dataflows:

• Work in an easy-to-use graphical interface.
• By previewing your data and observing its dynamic transformations at each step, you gain real-time insights into the evolving structure and content, allowing for a more informed and iterative approach to refining and optimizing your data analysis process.
• Quickly remove columns or change column labels.
• Ability to analyze the quality of data with column profiles.
• Aggregate and join data.
• Leverage the built-in machine learning for instance detect sentiment, data clustering, and time series forecasting.
• Bucket values without writing complex SAQL expressions.
• Leverage visual formula builder to create calculated columns.
• Using point-and-click interface, easily transform values to ensure data consistency. For instance, you can bucket, trim, split, and replace values without a formula.
• With the help of Version History, review the history of all the changes, and back up or move forward to replay it.
• Transmit the prepared data to other systems with the output connectors.

 

Conclusion

The blog equips you with practical guidance on configuring CRM Analytics, providing a comprehensive understanding of key platform features to empower effective utilization and optimization in your business analytics endeavors.

Blueflame Labs, salesforce implementation partners can help you with understanding CRM Analytics. Get in touch to discuss it with our Salesforce experts.pre