Skip to main content

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. Once 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 tab.
  2. Click the dropdown to the right of the SFDC_LOCAL connection and select Edit Objects.
  3. From the list of objects, select Account, Opportunity, and User. 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 The refresh button at the top of the Monitor tab 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 Direct Data icon.

The Add Input Data screen, showing the options to select input data type.

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 that relate 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 a single 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. 

To join the Account and Opportunity objects, we'll use a Data Prep recipe. 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 need to 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 click Salesforce Objects.
  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 help 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 Code
    • 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: AccountId
    • 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. Click Salesforce Objects. Select the box next to the User object.
  17. Deselect all columns by clicking the box next to Name twice. Click Name to sort the fields and select:
    • Id
    • Name
    • Title
  18. Click Next.
  19. Again, notice that CRM Analytics has selected the correct values under Join Keys.:
    • Join 0: OwnerId
    • User: Id
  20. Click Apply. With your local opportunity, account, and user data joined together, your recipe will look something like this.

The Recipe editor, showing the recipe joining together the Opportunities, Accounts, and User objects.

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 those. 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 The refresh button at the top of the Monitor tab to refresh the view until the job finishes.
  11. When the recipe has finished running, click the Data Assets tab in the left-hand pane.

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

Keep learning for
free!
Sign up for an account to continue.
What’s in it for you?
  • Get personalized recommendations for your career goals
  • Practice your skills with hands-on challenges and quizzes
  • Track and share your progress with employers
  • Connect to mentorship and career opportunities