Extract Salesforce Data into CRM Analytics

Learning Objectives

After completing this unit, you’ll be able to:

• Explain what the data sync accomplishes.
  
• Create a dataset with Salesforce data using a Data Prep recipe.
  
• Monitor and verify a new dataset in the data manager.
  

Extract Salesforce Data Overview

You just took the first step to get the data you need into CRM Analytics for the sales leadership team. They want to present their performance results when the executive team meets with the new CEO. But the CSV data is just a small part of what they need. Most of the data that interests them is already inside Salesforce—in the Opportunity, Account, and User objects. Now, you bring this Salesforce data into CRM Analytics.

Sync Salesforce Objects with Data Sync

You use the data sync to extract data from Salesforce and remote data into the CRM Analytics data manager. When in the data manager, your collected data is available to work with in recipes as Connected Objects.

In CRM Analytics, click Data Manager.

1. In the Data Manager, click the Connections option.
   
2. Click the dropdown to the right of the SFDC_LOCAL connection and select Edit Objects.
   
3. In the list of objects, make sure that Account, Opportunity, and User are selected. Click Save.
   
4. From the SFDC_LOCAL dropdown, select Run Now to sync the objects. This action runs data sync for all objects under the SFDC_LOCAL connection. (Alternatively, you can search for each object from the All Connections screen and run individual syncs by selecting Run Data Sync from the dropdown next to each object.)
   
5. In the left-hand pane, click Jobs Monitor to check the sync progress. Each object that you selected is an entry in the queue.
   
6. Click  to refresh the view until the jobs finish. The Salesforce objects are now synced as connected objects.
   

Connect Salesforce Objects as Direct Datasets in a Recipe

So far, we’ve seen how data sync lets you pull data into the data manager for further processing in recipes. We’ll look at recipes in more detail in the next section, but for now, know that you can also pull Salesforce objects into recipes as direct data. This option is intended for working with a small number of objects, whereas syncing objects before adding them to recipes is best for performance. You can use direct data in recipes, filter on it, and convert it to connected objects.

To pull an object into a recipe as direct data, when you add an input node to a recipe, select direct data from the list of data options. Direct data appears in the list of input data with this icon .

Create a Recipe with Salesforce Data

Let's take a moment to consider how the Salesforce objects you've extracted are related. As a Salesforce admin, you know that opportunities have lookup relationships to accounts and users. When you create opportunity records in Salesforce, you're entering opportunity field values; you’re also “looking up” field values in the related account and user records.

Similarly, when you create a dataset, instead of records, you create rows. Our first consideration is to define the unit of data in each row, what data professionals call the grain of a dataset. It’s an important concept when you’re joining data together from different sources, as you'll discover later. In our case, each row in our dataset is an opportunity. You can only include objects related to the Opportunity object in the dataset. Here, we're relating the Account and User objects. An account can have multiple opportunities, as can a user, but an opportunity can't have multiple accounts or users attached to it. The one-to-one relationship of opportunities to accounts and users highlights that it is the root of the association. 

We'll use a Data Prep recipe to join the Account and Opportunity objects. You can use a recipe to combine data from multiple data sources—including external data, as we'll see in the next section. You can bucket data, add formula fields, and cleanse data by transforming field values. You can also remove fields and filter rows that you don’t need before you create your target dataset.

To build a recipe with local Salesforce data, we'll first create input nodes for the Account and Opportunity objects. We'll add the User input node as we build the recipe. For each node, we select the fields that we want to include in our combined dataset. For CRM Analytics to add the right values to the right rows in the new dataset, we must join them on the fields that "match" with each other. This matched field is called the join key.

Account and Opportunity share the same data for Account ID, so that's how we'll associate them. For Account, the field is called ID. For Opportunity, it's called Account ID. User and Opportunity share the same Owner ID. For User, the field is User ID. For Opportunity, the field is called Owner ID. 

Here's how to build a recipe with local Salesforce data.

1. From Data Manager, in the left-hand pane, click Recipes.
   
2. Click New Recipe.
   
3. Click Add Input Data.
   
4. In the upper left, uncheck External Connected Objects and CRM Analytics Datasets so that only Salesforce Objects are visible.
   
5. Select the box next to Opportunity. In the right pane, you can choose which fields to include in the recipe. The list of selected columns for Opportunity helps you control which data is included in the recipe. You can hide or remove columns while working in the recipe, but we’ll do it from this screen now.
   
6. Deselect all columns by clicking the box next to Name twice. Click Name to sort the fields and select:
   

• AccountId
  
• Amount
  
• CloseDate
  
• Created Date
  
• Id
  
• Name
  
• OwnerId
  
• StageName
  

7. In the list of datasets on the left, select the box next to Account.
   
8. Deselect all columns by clicking the box next to Name twice. Click Name to sort the fields and select:
   

• BillingCity
  
• BillingCountry
  
• Id
  
• Industry
  
• Name
  
• OwnerId
  
• Sic
  
• Type
  

9. Click Next.
   
10. Drag the plus (+) icon from Account to the Opportunity icon.
    
11. From the Connect Nodes menu, select Join. Dragging the line from Account to Opportunity indicates that Opportunity is the grain of the dataset—the unit of data in each row.
    
12. In the Join Keys section, notice that CRM Analytics has correctly selected the keys to match an account to each opportunity.
    

• Opportunity: Account ID
  
• Account: Account ID
  

13. Click Apply.
    
14. Click the plus (+) icon next to the Join node.
    
15. Click Join.
    
16. The Select Input Data to Join screen opens. Uncheck External Connected Objects and CRM Analytics Datasets, so that only Salesforce Objects are visible. You get a hint from the Related Objects column about objects that can be joined.
    
    
17. Select the box next to the User object.
    
18. Deselect all columns by clicking the box next to Name twice. Click Name to sort the fields and select:
    

• Id
  
• Name
  
• Title
  

19. Click Next.
    
20. Again, notice that CRM Analytics has selected the correct values under Join Keys:
    

• Join 0: Owner ID
  
• User: User ID
  

21. Click Apply. With your local opportunity, account, and user data joined, your recipe looks something like this.
    

Let’s quickly recap what you’ve done here. You selected your root object, Opportunity, and the fields you need. You also selected the related Account and User objects and the fields you need from them. When you create the dataset, each row represents an opportunity, with fields from the related account and owner user records.

To create a dataset from this recipe, click the plus (+) icon next to the Join 1 node and add an output node.

1. Under Write To, select Dataset if not already selected.
   
2. For the Dataset Display Label, enter Opportunities_with_Accounts_and_Users.
   
3. For the Dataset API Name, enter Opportunities_with_Accounts_and_Users.
   
4. Under App Location, select Sales Performance Datasets.
   
5. Click Apply.
   
6. Click Save and Run.
   
7. When prompted, save your recipe as Opportunities_with_Accounts_and_Users.
   
8. Click Back to Data Manager.
   
9. In the left-hand pane, click Jobs Monitor to check in on the recipe progress.
   
10. If the recipe status shows as Queued or Running, click  to refresh the view until the job finishes.
    

Great job! You’ve successfully created two datasets. The SIC Descriptions dataset contains data extracted from a CSV file. The Opportunities_with_Accounts_and_Users dataset has data extracted from objects in your Salesforce org. The next step is to join data from these two datasets. You want all this data available to explore from a dataset. Onwards to the data preparation stage!

Resources

• Pull Data Directly Into Recipes