Clean, Transform, and Load Data with Data Prep Classic

Use a recipe to clean and combine data from multiple datasets or connected objects. Add bucket and formula fields, filter rows, transform field values, convert field types, and standardize date formats. You can then output the results to a new target dataset.

Watch a Demo: Get Started with the Recipe Editor (English Only)

When you’re ready, follow these steps to create a recipe.

• Should I Use Data Prep Classic or Data Prep?
  CRM Analytics provides two versions of Data Prep: Data Prep Classic and Data Prep. Data Prep is the latest version that replaces the previous version, Data Prep Classic. Recipes built in Data Prep can’t be opened in Data Prep Classic. When you open a Data Prep Classic recipe, it is upgraded to and opens in Data Prep. As of the Winter ’21 release, you can no longer create recipes in Data Prep Classic.
• Create a Recipe with Data Prep Classic
  Use a Data Prep Classic recipe to transform data before loading it into a dataset. Only Government Cloud users can create recipes with Data Prep Classic. All other users, including those using Government Cloud Plus, must use Data Prep to create recipes.
• Set Up the Preview in a Recipe
  A recipe displays a preview of your data on the Preview tab as you work. You can set the columns and number of rows that appear in this preview. If you are working with a large set of data, reducing the size of the preview speeds up the data refresh as you add steps to your recipe.
• Add More Data in a Recipe
  You can add columns of data from related objects to existing data in a recipe. Depending on how you want to combine the new data, use one of the following methods: lookup, left join, right join, inner join, or full outer join.
• Add Rows in a Recipe with Append
  Use append to add rows to the data in your recipe from another dataset or connected object. Fields are mapped automatically, or you can map them manually.
• Clean and Prepare Data Intelligently with Column Profiles and Smart Suggestions
  The column profile gives you key insights into the quality of your data and suggests additional transformations to help you clean and prepare it. This profile is especially useful when you are combining data from different sources, where inconsistencies are often introduced.
• Add a Filter in a Recipe
  Filter data in a recipe to remove rows that you don’t need in your target dataset.
• Bucket a Measure Field in a Recipe
  Add a field to a recipe to bucket values in a specified measure field.
• Bucket a Dimension Field in a Recipe
  Add a field to a recipe to bucket values in a specified dimension field.
• Bucket a Date Field in a Recipe
  Add a field to a recipe to bucket values in a specified date field.
• Add a Formula Field in a Recipe
  Add a formula field to calculate new values from measures and dimensions in your recipe.
• Aggregate and Group Data to a Different Grain
  Large datasets can be hard to digest due to the amount of information and low-grain details. Aggregation allows these datasets to be rolled up to a higher granularity, thus allowing users to create recurring summary statistics and join datasets with different granularities. You can also aggregate data to perform calculations on grouped records without aggregating the measures. For instance, group by website session IDs and then calculate the average time on each page and total number of clicks.
• Transform Fields in a Recipe
  Data is not always consistent, especially when you combine data from different sources. In a recipe, you can transform fields to ensure that values are consistent in your target dataset. Change case, split values to get just the parts you need, and replace incorrect values.
• Standardize Date Formats
  If a dimension field contains dates in different formats, use the Format Dates recipe transformation to standardize the format for all values in the field. A consistent format enables you to correctly filter and group records by date, including filtering by date component, such as month. It also ensures that you can successfully convert the field type from dimension to date.
• Convert Field Types in a Recipe
  The type assigned to a dataset field determines how you can query that field’s data. For example, you can filter and group by a dimension or date field, or perform math calculations on a measure field. When you load data into a dataset, CRM Analytics sometimes tags a dataset field with the wrong type. If needed, convert fields to the correct types.
• Predict Missing Values in Dimension Columns
  When a dataset or connected object has missing values in a dimension column, CRM Analytics can fill in missing values to complete your data. CRM Analytics intelligently predicts values based on values in other strongly correlated columns in your data.
• Navigate Columns in a Recipe
  As you add data and transform fields, the number of columns in your recipe preview increases, which makes it harder to find the columns that you want to work with. Use the column view to quickly find the columns that you need, and hide the ones you don't.
• Navigate and Edit Recipe Steps
  As you prepare your data, each change you make appears as a recipe step on the left. Think of these steps as your recipe history. You can move back and forward through this history to see how the data looks at different stages of the recipe. If you don’t like what you see, you can edit or remove any step.
• Save a Recipe
  If you’re not ready to create the target dataset, save the recipe and come back to it later. That saves your steps without you having to create the dataset.