04. Data Preparation

Data Cleaning processes

The data cleaning process is crucial for ensuring our dataset's accuracy and reliability. Below are the key steps we followed in this project:

1. Data Assessment

• Actions: Analyzed the structure and types of data, looking for missing values, duplicates, and inconsistencies.

2. Handling Missing Values

• Actions:
  • Identified columns with missing values.
  • Customer Metrics: Chose to impute missing values with 0 to ensure consistency, especially for features where a lack of data might indicate no applicable information (e.g., new customers).
  • Customer Profile: The Returns column contained 47,382 missing records, representing approximately 19% of the dataset. The rows with missing values in the Returns column were dropped from the dataset to maintain quality. This is becasue Returns is likely a key feature in understanding customer purchase behavior and patterns, and imputing a significant portion of customer data might have introduced arbitrary assumptions about customer behavior.
  • Customer Reviews: Remove observations with missing values in rating. This number is very small – 2, which is merely 0.0014% of all observations. Removal will not impact data analysis significantly.

3. Removing Duplicates

• Actions: Remove duplicate entries to avoid skewed analysis.
  • Customer Profile: The Customer Age and Age columns were found to be duplicates. The Customer Age column was dropped, retaining the Age column.

4. Fixing Data Types

• Actions:
  • Customer Metrics: Change the data type of CouponUsed from float64 to int64 to ensure it only accepts whole numbers.
  • Converted date columns to datetime format.
  • Customer Reviews: Convert actual_price and rating to numeric data types, the float data type. Encode category as a categorical variable, which groups the observations into categories. Order_ID and review_ID are converted to string data types.
  • Messages: Converted is_opened, is_clicked, is_unsubscribed, is_complained, is_blocked, is_purchased columns to boolean values

5. Remove redundant columns

• Actions:
  • Customer Profile & Customer Metrics: Remove columns with information already found in other datasets to prevent data inconsistencies (e.g. OrderCount and Product Price)
  • Customer Metrics: Dropped columns lacking sufficient information (CityTier etc.) or having extremely low correlation with churn to streamline the dataset and focus on the most impactful features for our analysis.
  • Customer Profile: Customer Name was dropped because the Customer ID already serves as a unique identifier.
  • Customer Reviews: We focus on analyzing customer ratings and reviews. We removed many redundant columns that are not relevant to our data analysis, keeping only Category, actual_price, rating, review_id, review_title, and review_content columns.
    • Campaigns:
    • Dropped columns with more than 50% of missing values (e.g., ab_test, position, is_test, hour_limit)
    • Dropped columns not useful for analysis (e.g., started_at, finished_at, warmup_mode)
  • Messages:
    • Dropped columns with more than 50% of missing values (e.g., purchased_at, blocked_at, complained_at, soft_bounced_at, unsubscribed_at, clicked_first_time_at, clicked_last_time_at, hard_bounced_at, platform, opened_first_time_at, opened_last_time_at)
    • Dropped columns not useful analysis (e.g., id, created_at, updated_at, category, email_provider, is_hard_bounced, is_soft_bounced)

(can remove if no one has it)

6. Correcting Inconsistencies

• Objective: Standardize the data for accuracy.
• Actions:
  • (add in if there is)

7. Validation

• Objective: Confirm the accuracy of the cleaned dataset.
• Actions:
  • Validated the dataset to ensure that no new errors were introduced during the cleaning process.
  • Conducted initial analyses to confirm that the data behaved as expected (no duplicate rows etc).

Feature engineering techniques

Data Augmentation

To ensure consistency across our entire database, which comprises datasets sourced from various origins, we decided to generate synthetic data to standardize key attributes, like the number of customers. This standardization process aims to align the datasets and facilitate comprehensive analysis. Additionally, we hope to prevent the issue of small dataset sizes and ensure that our analyses are robust and reliable.

Customer Dataset

Customer Profile Dataset

Category Dropped

• The Books category was removed from the dataset to align it with other datasets that do not contain this category. This ensures greater consistency across different datasets being used in the analysis.

Stratified Sampling

• The dataset is grouped by Gender and Payment Method, which are categorical variables.
• A specified sample size (n_samples = 100001) is selected, proportionally sampling from each group to maintain the original distribution of these categorical variables.

Age Resampling with KDE

• The resample_age function uses Kernel Density Estimation (KDE) to fit the distribution of ages within each group.
• KDE with a Gaussian kernel is applied to the "Age" column, with a bandwidth of 1.0.
• After fitting KDE to the age data in each group, the function generates new age values for the sampled data, maintaining the original age distribution.

Customer Metric Dataset

• CTGAN from SDV package was chosen as the technique for generating synthetic data from Customer Metric Dataset since it is believed that relationships between its features is complicated

Merged synthetic generated Customer Profile & Customer Metric Dataset to get a comprehensive Customer dataset, where

• Age, Gender, and Payment Method are augmented from Customer Profile Dataset
• Churn,Tenure, NumberOfDeviceRegistered,HourSpendOnApp, SatisfactionScore, CouponUsed, and CashbackAmount are augmented from Customer Metric Dataset

Customer Reviews Dataset

• Category Dropped:

Only Electronics and electronic-related products, such as Computers & Accessories are retained in the dataset. Remove observations under other categories that are not central to our project.

• Order_ID:

This dataset has a primary key called Order_ID to establish its relationship with Orders Table, because product IDs and any other identifiers follow very different formats and are redundant to be considered. Order IDs are sourced from Orders Table and randomly assigned without replacement

• Miscellaneous:

Renaming of columns and adjusting actual_price to reflect the price of products in USD.

Campaigns Dataset

SQLqueries used for data transformation (with explanations)

There was no SQL queries use for data transformation. SQL query was only used to create the Messages.db and reading the whole dataset from Messages.db. The code to create the Messages.db, which only contain the messages dataset table, is here.

Final dataset structure

The dataset consists of multiple tables, each designed to represent different aspects of customer behavior and demographic information. The tables are linked by a unique identifier, CustomerID, to allow for efficient data merging and analysis.

Key Tables

1. Customer Dataset
   • Description: Contains information about customers.
   • Row Count: 100,000 rows

Column Name	Data Type	Description
CustomerID	int64	Primary Key.
Age	int64	Age of the customer.
Gender	object	Gender of the customer.
Payment Method	object	The method of payment used by the customer (e.g., credit card, PayPal).
Churn	int64	Churn Flag (binary).
Tenure	float64	Tenure of customer in organization.
NumberOfDeviceRegistered	int64	Total devices registered by the customer.
HourSpendOnApp	float64	Average hours spent on the app weekly.
SatisfactionScore	int64	Satisfaction score of customer on service.
CouponUsed	float64	Total number of coupons used in the last month.
CashbackAmount	float64	Average cashback in the last month.

2. Campaigns Dataset

   • Description: Contains detailed information about various marketing campaigns, their attributes, and associated product information post-cleaning.
   • Row Count: 89 rows
   • Key Column: campaign_type + id
   • | Column Name | Data Type | Description | |-----------------------------------|-----------|------------------------------------------------------------------------------------------| | id | int64 | Campaign ID, unique within each campaign_type. | | campaign_type | object | Specifies the type of campaign (e.g., 'bulk', 'trigger', 'transactional'). | | channel | object | The medium used for the campaign, such as 'email', 'mobile_push', 'web_push', or 'sms'. | | topic | object | Represents the meaning of the campaign. | | total_count | float64 | Total recipients in the campaign. | | subject_length | float64 | The length of the email subject line. | | subject_with_personalization | object | Indicates if the subject contains the recipient's name. | | subject_with_deadline | object | Denotes if the subject includes a deadline. | | subject_with_emoji | object | Indicates if the subject includes emoji symbols. | | subject_with_bonuses | object | Specifies if the subject mentions bonuses for actions. | | subject_with_discount | object | Indicates if the subject mentions a discount. | | subject_with_saleout | object | Denotes if the subject mentions a sale out. | | product_id | int64 | Identifier for the product associated with the campaign. | | product_price | float64 | The selling price of the product. | | product_cost | float64 | The cost incurred for the product. | | percentage_purchased | float64 | The estimated percentage of recipients who made a purchase. |

3. Messages Dataset

   • Description: Contains information about messages sent in marketing campaigns, including user interactions and campaign attributes.
   • Row Count: 20,583,769 entries
   • Key Column: message_id
   • | Column Name | Data Type | Description | |-------------------|--------|-----------------------------------------------------------------------------| | message_id | object | Unique identifier for each message. | | campaign_id | int64 | Identifier for each campaign. | | message_type | object | Campaign type. | | client_id | int64 | Identifier for the client receiving the message. | | channel | object | Message channel (email, web_push, mobile_push, sms). | | stream | object | Additional identifier of data source. | | date | object | Date of the message event. | | sent_at | object | Specific timestamp when the message was sent. | | is_opened | int64 | Binary flag (0 or 1) indicating if the message was opened. | | is_clicked | int64 | Binary flag (0 or 1) indicating if a link in the message was clicked. | | is_unsubscribed | int64 | Binary flag (0 or 1) indicating if the recipient unsubscribed through the message. | | is_complained | int64 | Binary flag (0 or 1) indicating if the recipient complained. | | is_blocked | int64 | Binary flag (0 or 1) indicating if the message was blocked from being delivered. | | is_purchased | int64 | Binary flag (0 or 1) indicating if a purchase was made following the message. |

4. Customer Reviews Table

Column Name	Data Type	Description
category	category	Category the product belongs to
actual_price (USD)	float	Price of the product in USD
rating	float	Rating of the product on Amazon’s Website, on a scale from 0 to 5
review_id	string	Review ID of consumer who purchased the product gave
review_title	object	Summary of the review
review_content	object	Content of the review given by the consumer
Order_ID	string	Order ID of the product sent to customer

Data Relationships