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.

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.

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.

(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

• 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

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 in [https://github.com/nltyh/DSA3101-Project/blob/main/Customer%20Behaviour/create_messages_database.py].

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.
   • Key Column: CustomerID
   • Columns:
     • CustomerID (int64): Unique identifier for each customer.
     • 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.
   • Row Count: 100,000 rows

2. Campaigns Table

3. Messages table | Column | Data Type | Constraints | |-------------------|-------------|----------------------| | message_id | TEXT | PRIMARY KEY | | campaign_id | INTEGER | | | message_type | TEXT | | | client_id | INTEGER | | | channel | TEXT | | | stream | TEXT | | | date | DATETIME | | | sent_at | DATETIME | | | is_opened | BOOLEAN | | | is_clicked | BOOLEAN | | | is_unsubscribed | BOOLEAN | | | is_complained | BOOLEAN | | | is_blocked | BOOLEAN | | | is_purchased | BOOLEAN | |

Data Relationships