Understand the different ways of preparing your data
There are several ways to prepare your data sources. When you create a new audience, you choose one or several sources that feed this audience. Sources are deduped/merge to form a unified table (=the audience).
Most data preparation should be applied at the audience level after sources are unified, but you can also apply some data preparation recipes at the source level, before dedupe.
Audience level - Add no code data prep recipes
How do data prep recipes work?
In your audience view, the right sidebar lists all the data prep recipes applied to your audience. If you click on "Add new recipe", you will see the list of all recipes available, and you can add quickly a new one.
All these recipes are processed in almost real-time each time there is a record created or updated in the source feeding the audience. These recipes are processed following the position of the item in the sidebar. This is why there are some "system recipes" created by default to process first the merge of the various sources, and the dedupe process.
When you add a new recipe, it will be added at the end of the sidebar. You can click on an existing recipe to modify or delete it. For now, you cannot change easily the position of the recipe in the listing, but it will be possible soon.
Please don't forget to click on "Save" after modifying the data prep recipes. After clicking on Save, we will rebuild the audience
What are all the data prep recipes available?
List of all no code dataprep recipes available [Work in progress]
Audience level - Build a full SQL audience
When you create a new audience, you're invited to choose between "No code" or "SQL". The main purpose of SQL-based audiences is to have more flexibility to prepare your data sources.
Some advanced data preparation could be difficult or almost impossible to do within the “no code” audience builder. For example, if I want to create an audience “Consents” based on the columns “Optin Email” / “Optin SMS” of my contacts, it implies transforming columns as new lines, there is no way to do it using the “no code” data preparation recipes.
What should you know to use SQL-based audiences?" - The SQL code should follow Postgres syntax. - We are using “Jinja” templating language to be able to add functions, variables, etc.
Source level - Prepare some columns before dedupe
When you add a new source, in the mapping step, it’s possible to add some preparation rules for each column. The main purpose of source-level data preparation is to prepare columns used for dedupe.
Because the dedupe is processed just after importing the sources, it could be necessary to normalize the columns used in the dedupe at the first step, just after importing the sources files. For example, if you want to dedupe your contacts based on Email x Phone number, you will need to normalize these 2 columns to be sure that “email@example.com x 0660036339” match with “JOHN.firstname.lastname@example.org x +33660036339”.
Most data prep options available at this step are quite easy to understand. For each column, you can choose to put the column value in “lowercase” or to apply a “Find & replace “ function.
Zoom on SQL functions