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 Tableau CRM 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. 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.
- Click the Dataflows & Recipes tab and then the Recipes subtab.
- Click Create Recipe.
- Review the Data Prep welcome mat and then close it. The welcome mat appears the first time you open Data Prep.
- Click Add Input 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. Tableau CRM selected these columns because it found values in the AccountId.BillingCountry column that match values in the Country column of the ExchangeRates dataset. This is good news because it’s exactly how you want to match exchange rates to opportunities.
- 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 column 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.
- In the Transform toolbar, select and select Custom Formula.
- To convert to US dollars, enter the following formula.
- In the Output Type field, select Numeric.
- Verify that the precision is 10.
- Set Scale to 2 so the column will use 2 decimal places.
- 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 to open the Replace transformation.
- 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 values in the original column.
- Click Apply.
- Repeat steps 1-4 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 and click .
- In the Select Columns to Drop field, add these columns also.
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.
- Click Apply. The columns are dropped from the recipe. Now let's clean up some column labels.
- In the Columns tab, select the AccountId.Name column, click to edit attributes, 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 twice to save our latest recipe changes.
- 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.
- To view the recipe graph, click the Collapse button ().
- 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, Tableau CRM 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, dataflow, and 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!