![Screen Shot 2022-01-11 at 6.40.54 PM.png](Cbes8thaSEtA)

In order to blend the datasets from disparate sources, the raw tables require a first pass of data cleansing and blending.

 [Product_Sales_Brand](dataset:Product_Sales_Brand) is the result of adding the market name to the raw sales dataset. After joining these two tables, we execute a prepare recipe for basic data cleaning. An important step here is to process the date column as a Date type, after which we extract the date_year and date_week of sales. We will later use these two columns as a part of the join key with the market outreach data.

Speaking of those data, each of the three outreach datasets, [Web Traffic](dataset:Web_Traffic), [Calls Data](dataset:Calls_data), and [Campaigns](dataset:MKT_Campaign_data), go through their own prepare recipes before being joined together. A similar date parsing and extraction is performed again, but an additional step to unfold columns is added in. 

For example, in the Web Traffic data, each record is an instance of an HCP's visit to a webpage, and information such as the channel, content type, and personalization status of that page. Later we will need to aggregate these records to a larger hospital level, so each categorical variable needs to become a binary category that we can sum over. A similar process is done for the Calls Data, to understand the type of call and the therapeutic area the call is related to. The web traffic data is already at a number level, as it measures the number of campaigns and email engagements. 

After creating binary values for the categorical data, the market outreach can be joined at the product, account, year, and week of year level. This is a left join, meaning that many records will be missing where only one or two types of outreach occurred in a given year/week. 

Finally, the Accounts data is prepared in two different ways. First, categorical information about each HCP (account_id) is turned into binary values similar to the outreach data. Then, we compute the weeks since an HCP received product training. 

With the data cleaned and properly binarized we can move to the next step of aggregating and blending data with our different datasets. 



