Skip to main content

Prepare Your Combined Data in a Recipe

Prepare Data with Data Prep

You identified inconsistencies in the US and Canada sales data and that Canadian amounts are in the wrong currency. Data prep has all the tools you need to fix these issues and is smart enough to do a lot of the work for you. Being able to preview the data as you clean it up ensures that you don’t miss a bit.

Load Exchange Rate Data

Your Finance team has provided a CSV file with the exchange rates that the company uses for currency conversions. They update these rates monthly. Upload this file in CRM Analytics to use for currency conversion in your recipe.

  1. Download the ExchangeRates.csv file to your desktop from here.
  2. In Data Manager, click the Data Assets tab.
  3. Click New Dataset.
  4. Click Upload Files.
  5. Navigate to the ExchangeRates.csv file that you downloaded in step 1, select it, and then click Open.
  6. Click Next.
  7. Click Next.
  8. Click Next. (Phew.)
  9. Click Close.
  10. Confirm that the file upload was successful on the Jobs Monitor. If needed, refresh the view to see the latest upload job status.

Add Exchange Rate Data to the US and CA Sales Data

Now you’re ready to start creating the recipe and use the exchange rate data to convert Canadian dollar values to US dollars. 

  1. Click the Recipes tab.
  2. Click the North America Sales recipe to open it in data prep.
  3. Hover over Append 0 and click Branch.
  4. Click Join.
  5. Select the ExchangeRates dataset and click Next.
  6. Click the preselected values under Join Keys and update to:
    • Append 0: BillingCountry
    • ExchangeRates: Country
  7. Click Add.
  8. Click the Columns tab and uncheck Country. Although it's needed for the lookup, you don't need to include it in your final dataset. We already have the other BillingCountry column with this same information.
  9. Click the Preview tab, and then scroll to the right to confirm that the Rate column is included.
  10. Click Apply. The node Join 4 is added to the recipe.
  11. Click Save.

Now that you have exchange rate data, use a formula to convert Canadian dollar values to US dollars. To add a Formula transformation, you must first add a Transform node.

  1. Click + next to the Join 4 node and select Transform.
  2. In the Preview tab, click the Amount column.
  3. In the Transform toolbar, selectFormula and select Custom Formula.
  4. Change the Transformation Name to Convert Currency.
  5. To convert to US dollars, enter the following formula."Amount"/"ExchangeRa.Rate"
  6. In the Output Type field, select Numeric.
  7. Verify that the precision is 10.
  8. Set Scale to 2 so the column will use 2 decimal places.
  9. In the Show Results In field, notice that the results will be written to a new column. Also, the original column will be kept with the original values.
  10. In the Column Label field, change the label of the new column to USD Amount.
  11. In the API Name field, change the value to USD_Amount.Custom formula node configuration.
  12. Click Apply to add the Formula transformation as a step in the Transform node.

Clean the Stage Data

Your recipe has data from different sources and this can lead to inconsistencies and dirty data. For example, you know that opportunity stages in the Canada org are slightly different than stages in the US org. (You know because you changed them right after you signed up for the Canada org.) Use the Replace transformation to fix the inconsistencies in the StageName column.

  1. In the Preview tab, click the StageName column, and then click Replace in the Transform toolbar to open the Replace transformation. StageName selected and Replace highlighted
  2. Change the Transformation Name to Value Proposition Cleanup.
  3. Enter ValueProposition in the Find field and Value Proposition in the Replace field.
  4. Looking at the Show Results In field, notice the new value will overwrite the existing values in the original column.
  5. Click Apply.
  6. Repeat steps 1-5 to replace Negotiation & Review with Negotiation/Review in the StageName column. Use the transformation name Negotiation/Review Cleanup.
  7. Click Save.

So you’ve cleaned up the stage data. Great! Next, we're going to clean up some columns. For example, you no longer need the Amount and Rate columns that you used to convert amounts to US dollars in the new USD Amount column. Let’s tidy those up next.

Drop Columns and Change Labels

Your recipe has columns that you don’t need. For example, it’s unlikely that any one would ever want to analyze account IDs. And many columns that you might want to keep have unfriendly or unclear names. How do your users choose from the three Name columns?

Use the Columns tab for an uncluttered place to drop columns and change labels.

  1. Click the Columns tab. Let's drop some columns from the recipe so they don't appear in the final dataset.
  2. Select the AccountId column and click [alt text: Drop Columns].
  3. In the Select Columns to Drop field, add these columns also.
    • OwnerId
    • Id (with the API Name CAAccount.Id)
    • Id (with the API Name User.Id)
    • Amount
    • Rate
    • Symbol
    • Tip: Instead of manually entering multiple columns in the Drop Columns transformation, you can select multiple columns on the Columns tab using Cmd+click for MacOS or Ctrl+click for Windows, and then apply the Drop Columns transformation.
  4. Click Apply. The columns are dropped from the recipe. Now let's clean up some column labels.
  5. In the Columns tab, select the Name entry with the API name Name.
  6. Click Edit Attributes to edit attributes, change the label to Account Name, and click Apply.
  7. Repeat steps 5 and 6 to change these column labels as well.
    • BillingCountry to Account Country
    • Name (with API Name User.Name) to Owner
    • BillingCity to Account City
    • BillingState to Account State/Province
    • CloseDate to Close Date
    • Type (with API Name CAAccount.Type) to Account Type
    • LeadSource to Lead Source
    • Title (with API Name User.Title) to Owner Title
    • CreatedDate to Created Date
    • StageName to Stage
  8. Click Save twice to save our latest recipe changes.
  9. Click the Preview tab.

Ahh, that’s better. Your data preparation tasks are done. All that’s left to do is specify where to write the results and then run the recipe to write the results to the specified target.

Create the Dataset and Explore

It's time to add an Output node, which indicates where to write the recipe results. You want to write the results to a dataset. After you specify the dataset details, run the recipe to create and view the final dataset.

  1. To view the recipe graph, click Collapse button.
  2. Click + next to the Transform node and select Output.
  3. In the Dataset Display Label field, enter North America Sales.
  4. In the Dataset API Name field, enter North_America_Sales.
  5. Although you can change the app that stores the dataset, leave the default app My Private App in the App Location field.
  6. Click Apply.
  7. Click Save and Run to save and run the recipe.
  8. To see the results, click Back to Data Manager.
  9. In the Jobs Monitor tab, check that the status of the North America Sales_recipe job shows Successful. If the status shows as Queued or Running, click Refresh to refresh the view until the recipe job finishes.
  10. After the recipe completes successfully, click the Data Assets tab.
  11. Click on the North America Sales dataset.
  12. Click Explore.
  13. Under Bars, click the Add a Group button (+) and select Account Country.
  14. Under Bars, click the Add a Group button (+) and select Stage. Opportunities from both countries now have the same stage names.
  15. Under Bar Length, click Count of Rows, click Sum, and then click USD Amount. All values are now in US dollars.

Wrap Up

Congratulations! You’ve created a North America Sales dataset with local US Salesforce data and remote Canada data, using a connection, data sync, and recipe. To give your VP of North America Sales fresh data each day, schedule these data jobs to run daily. Now you’re ready to take on the rest of the world!

Keep learning for
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