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 see 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 the 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 Data tab.
- On the right of the US and CA Sales dataset, click and select Create Recipe.
- In the Recipe Name field, enter North America Sales.
- Click Next.
- Click the Add Data button ( ).
- On the Datasets tab of the Select data source dialog, click ExchangeRates.
- Confirm that AccountId.BillingCountry and Country are selected as 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.
- At the bottom of the Columns to Include list, select Rate. By default Analytics includes all columns from the recipe, and only the selected lookup key from the data that you’re adding. So select additional fields to add. We just need the rate.
- Scroll to the right in the lookup results preview to confirm that the Rate column is included. You don’t really need the new Country column as it’s already in the recipe. However, it’s one of the lookup keys so you have to include it for now. You can remove it later.
- Click Done.
Now that you have exchange rate data, use a formula in the recipe to convert Canadian dollar values to US dollars.
- In the Amount column header, click and select Formula. A formula builder opens in the Add Transformation pane. You can start typing in the builder to select from matching fields, functions, and operators. Tip: Click measure columns to add them to the formula.
- In the formula builder, enter round(.
- Click the Amount column to add it to the formula.
- In the formula builder, enter / after [Amount].
- Click the Rate column to add it to the formula.
- In the formula builder, enter ) at the end of the formula. Your formula should look like this: round([Amount]/[Exchang.Rate]).
- Click Add. The new calculated column appears at the end of the preview with the label formula. Let’s change that label.
- Click the formula column and click the Attributes tab in the column profile on the right.
- Change these attributes:
- API Name: USDAmount
- Field Label: USD Amount
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 column profile to look for these inconsistencies in each column, and use smart tools to fix them.
- Click the StageName column and click the Profile tab on the right. The column profile shows you such things as the number of missing values and the frequency of values.
- In the Frequent Values section, click View More. Notice that there are two similar Value Proposition values and Negotiation/Review values. These values are the ones that you changed in the beginning of this project.
- In the Frequent Values section, click the ValueProposition bar and the Value Proposition bar. To select multiple bars, use Ctrl+click for Windows or Cmd+click for macOS. The values are highlighted in the preview.
- In the column profile, click Clusters. Clusters are groupings of similar values presented as a stacked bar. Try hovering over each segment to see the value that it represents and the frequency of the value. Here, Analytics has found similar values for ValueProposition and Negotiation/Review. These values might be genuinely different, but here they represent inconsistencies in your data from the two different sources. Behind the scenes, the replace and bucket transformations do the same thing: changing values in a column to different values. Use replace if you just need to change one value in a column. For example, use replace to change all instances of ValueProposition to Value Proposition. Use bucketing to change multiple values in a column using a single transformation. For example, the Stage field in your recipe has variations of ValueProposition and Negotiation/Review that you want to change. Use bucketing to do this in one go. What’s more, when Analytics identifies clusters, it suggests the Bucket By Clusters transformation, which does most of the work for you.
- In the Suggestion bar at the bottom of the editor, click Bucket By Clusters. The bucket builder opens in the Add Transformation pane. The bucket names and values are entered for you.
- Verify that the bucket names are Value Proposition and Negotiation/Review. You can do more bucketing here if you need to, but we’ll move on.
- Click Add. The bucketed value column appears next to the StageName column. Let’s change that label.
- Click the Attributes tab in the column profile and change these attributes:
- API Name: Stage
- Field Label: Stage
So you’ve cleaned up the stage data, but now you have two stage columns. Transformations that you add in a recipe result in new columns, and you probably don’t need to keep the old columns. For example, you no longer need the Amount and Rate columns that you used to convert amounts to US dollars. Let’s tidy those up next.
Hide Columns and Change Labels
Your recipe now 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 hide columns and change labels.
- At the top of Data Prep, click the Columns tab.
- On the right of AccountId, click and select Hide Column. The column is hidden from the recipe and appears in the Not in Recipe section of the Columns list.
- Repeat step 2 to hide these columns:
- Click AccountId.Name.
- In the Attributes tab of the column profile, change the field label to Account Name.
- Repeat steps 4–5 to change these field labels:
- 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
- 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 to run the recipe to create the North America Sales dataset.
Create the Dataset and Explore
Let’s wrap this up.
- At the top of Data Prep, click Create Dataset.
- Click Continue. You’re asked if you want to schedule the recipe. You want it to run after the dataflow, so let’s set that up.
- Select Yes, and then click Schedule Recipe.
- Schedule the recipe to run every 24 hours at 6:00 AM each weekday by selecting these settings.
- Schedule by: Hour
- Start at: 6:00 am
- Run every: 24 Hours
- Select days: M, Tu, W, Th, F
- Click Schedule and Run.
- Click the X on the North America Sales tab to close Data Prep.
- Click the Monitor tab.
- Check that the status of the North America Sales_recipe job is Successful. If the status shows as Queued or Running, click to refresh the view until the recipe job finishes.
- Click the Data tab.
- On 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. And you’ve scheduled these data jobs to give your VP of North America Sales fresh data each day. Now you’re ready to take on the rest of the world!