Prepare Your Combined Data in a Recipe
Choose Your Data Preparation Tool
You’ve identified inconsistencies in the US and Canada sales data and that Canadian amounts are in the wrong currency. The dataflow has all the tools you need to fix these issues, but it requires patience, a steady hand, and a whole bunch of formulas. What’s more, you can’t see the data in the dataflow editor, so it’s like working in the dark.
Better to use a recipe, which gives you the same tools but is smart enough to do a lot of the work for you. And 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 Analytics to use for currency conversion in your recipe.
- Download the ExchangeRates.csv file to your desktop from here.
- In Data Manager, click the Data tab.
- Click Create Dataset.
- Click Select a file or drag it here.
- Navigate to the ExchangeRates.csv file that you downloaded in step 1, select it, and then click Open.
- Click Next.
- Click Next.
- Click Upload File.
- Click the Continue in the background and check progress in the data monitor link.
- On the Jobs subtab of the Monitor tab, confirm that the file upload was successful.
- 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.
- Click the Dataflows & Recipes tab and then the Recipes subtab.
- Click Create Recipe.
- Click Select Data.
- Select the US and CA Sales dataset and click Next. The US and CA Sales input node shows in the recipe graph.
- To lookup the exchange rate for each opportunity, start by clicking + next to the US and CA Sales input node and select Join.
- Select the ExchangeRates dataset and click Next.
- Verify that the Lookup join type is selected.
- Confirm that AccountId.BillingCountry and Country are selected as the lookup keys. Analytics has selected these fields because it found values in the account country field that match values in the country field of the exchange rate dataset. This is good news because it’s exactly how you want to match exchange rates to opportunities.
- In the Columns tab, select Rate at the bottom of the list to add this column to the recipe. By default, Analytics includes all columns from the recipe and only the selected lookup keys from the data that you’re adding.
- Uncheck the Country column. Although it's needed for the lookup, you don't need to include it in your final dataset. We already have the other AccountId.BillingCountry field with this same information.
- Click the Preview tab, and then scroll to the right to confirm that the Rate column is included.
- Click Apply. The Join node is added to the recipe.
- Click Save to save the recipe. An error appears because you haven't specified an Output node (where to write the recipe results). We'll take care of that in a bit.
- Click Continue.
- In the Recipe Name field, enter North America Sales, and then 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.
- Click + next to the Join node and select Transform.
- In the Preview tab, click the Amount column, and then click fx | Custom Formula. The
- In the formula builder, select Round from the list of numeric functions, and then click +. The round function appears along with its required parameters in the formula builder.
Note: If you select Round from the Transform toolbar, the selected field populates in the formula.
- Hover over the Amount column header to get its API name. When you enter a column in a formula, use the column API name, not the label.
- In the formula, replace the parameter field with the column API name USandCASal.Amount and parameter numberOfDigits with the value 2. Your formula should look like this: round(USandCASal.Amount, 2).
- Scroll down in the transformation details and change Output Type to Numeric.
- Leave Precision at 10, but set Decimal Places to 2.
- 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.
- In the Column Label field, change the label of the new column to USD Amount.
- 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.
- In the Preview tab, click the StageName column, and then click in the Transform toolbar.
- Enter ValueProposition in the Find field and Value Proposition in the Replace field.
- Looking at the Show Results In field, notice the new value will overwrite the existing value in the original column.
- Click Apply.
- Repeat steps 1-3 to replace Negotiation/Review with Negotiation & Review in the StageName column.
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 names. AccountId.BillingCountry, anyone?
Use the Columns tab for an uncluttered place to drop columns and change labels.
- Click the Columns tab. Let's drop some columns from the recipe so they don't appear in the final dataset.
- Select the AccountId column, click , and then click Apply. The column is dropped from the recipe.
- Repeat step 2 to drop the following columns
Tip: Instead of creating a separate Drop Columns transformation for each column, use Cmd+click for MacOS or Ctrl+click for Windows to select multiple columns, and then apply a single Drop Columns transformation.
Now let's clean up some column labels.
- Select the AccountId.Name column, click , change the label to Account Name, and click Apply.
- Repeat step 4 to change these column labels as well.
- AccountId.BillingCountry to Account Country
- OwnerId.Name to Owner
- AccountId.BillingCity to Account City
- AccountId.BillingState to Account State/Province
- CloseDate to Close Date
- AccountId.Type to Account Type
- LeadSource to Lead Source
- AccountId.Industry to Industry
- OwnerId.Title to Owner Title
- CreatedDate to Created Date
- StageName to Stage
- Click Save to save our latest recipe changes.
- At the top of Data Prep, 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.
- Click + next to the Transform node and select Output.
- In the Dataset Display Label field, enter North America Sales. When the dataset API name is not specified, Analytics generates the API name based on the label.
- Although you can change the app that stores the dataset, leave the default app My Private App in the App Location field.
- Click Apply.
- Click Save and Run to save and run the recipe.
- To see the results, click Back to Data Manager.
- In the Monitor tab, check that the status of the North America Sales_recipe job shows Successful. If the status shows as Queued or Running, click to refresh the view until the recipe job finishes.
- After the recipe completes successfully, click the Data tab.
- To the right of the new North America Sales dataset, click and select Explore.
- Under Bars, click the Add a Group button (+) and select Account Country.
- Under Bars, click the Add a Group button (+) and select Stage. Opportunities from both countries now have the same stage names.
- Under Bar Length, click Count of Rows, click Sum, and then click USD Amount. All values are now in US dollars.
Congratulations! You’ve created a North America Sales dataset with local US Salesforce data and remote Canada data, using a connection, data sync, a dataflow, and a recipe. To give your VP of North America Sales fresh data each day, schedule these data jobs to run on a daily basis. Now you’re ready to take on the rest of the world!