03. Data Understanding

Data Sources and collection methods

• Customer Profile: Customer Profile: GitHub - Leangonplu/Ecommerce_Customer_Churn_Analysis_and_Prediction: Ecommerce Customer Churn Analysis and Prediction
• Customer Metrics: Customer Metrics: 🛒 E-commerce Customer Data For Behavior Analysis | Kaggle
• E-commerce multichannel direct messaging 2021-2023 (kaggle.com)
• Amazon Sales Dataset. (2023, January 17). Kaggle. https://www.kaggle.com/datasets/karkavelrajaj/amazon-sales-dataset
• Amazon Products Sales Dataset 2023. (2023, March 26). Kaggle. https://www.kaggle.com/datasets/lokeshparab/amazon-products-dataset?select=Clothing.csv

Data Dictionary

Customer Profile Table

Data Dictionary for Customer Profile Table

Column Name	Data Type	Description
Customer ID	int64	Unique identifier for each customer.
Purchase Date	object	The date of the purchase, recorded as a string (yyyy-mm-dd format).
Product Category	object	The category of the purchased product (e.g., Electronics, Clothing).
Product Price	int64	The price of the purchased product, in the local currency.
Quantity	int64	The quantity of the product purchased.
Total Purchase Amount	int64	The total amount spent for the purchase. This is the product of Product Price and Quantity.
Payment Method	object	The payment method used by the customer (e.g., Credit Card, PayPal).
Customer Age	int64	The age of the customer at the time of purchase.
Returns	float64	Indicates if the product was returned (1.0 for return, 0.0 for not returned, or NaN if not applicable).
Customer Name	object	The name of the customer.
Age	int64	The customer's age, potentially derived from their profile information.
Gender	object	The gender of the customer (e.g., Male, Female).
Churn	int64	Indicates whether the customer has churned (1 for churned, 0 for active).

Customer Metrics Table

This table contains key metrics related to customer behavior and engagement, including demographic information, churn status, order history, and feedback indicators.

Column Name	Description	Type
CustomerID	Unique customer ID	int64
Churn	Churn Flag	int64
Tenure	Tenure of customer in organization	float64
PreferredLoginDevice	Preferred login device of customer	object
CityTier	City tier	int64
WarehouseToHome	Distance between warehouse and home of customer	float64
PreferredPaymentMode	Preferred payment method of customer	object
Gender	Gender of customer	object
HourSpendOnApp	Number of hours spent on mobile application or website	float64
NumberOfDeviceRegistered	Total number of devices registered for customer	int64
PreferedOrderCat	Preferred order category of customer in last month	object
SatisfactionScore	Satisfaction score of customer on service	int64
MaritalStatus	Marital status of customer	object
NumberOfAddress	Total number of addresses added for customer	int64
Complain	Any complaints raised in the last month	int64
OrderAmountHikeFromlastYear	Percentage increase in orders from last year	float64
CouponUsed	Total number of coupons used in the last month	float64
OrderCount	Total number of orders placed in the last month	float64
DaySinceLastOrder	Days since the last order by customer	float64
CashbackAmount	Average cashback in the last month	float64

Initial data exploration finds

Customer Metrics

• Correlation with churn only significantly high for some columns
• Some columns could be found in other tables, and should be removed to prevent data inconsistencies
• Churn values are imbalanced, which may require resampling techniques.
• Features with missing values had a skewed distribution and with possible gaps in data collection for new customers, it might be better to replace missing values with 0.

Data Quality assessment

Customer Profile

Initial Data Exploration Findings

The dataset provided contains customer transaction data, including information on purchase behavior, demographics, and churn status. Below are the key observations from the initial exploration:

Data Overview: The dataset consists of 13 columns capturing customer purchase data, demographic attributes, and churn information. The columns include identifiers like Customer ID, transaction-related fields like Purchase Date, Product Price, and Quantity, as well as customer demographic profile fields like Age and Gender.

Data Types: The dataset includes various data types:

Numerical columns: Customer ID, Product Price, Quantity, Total Purchase Amount, Customer Age, Returns, Age, and Churn.

Categorical columns: Product Category, Payment Method, and Gender.

Date-related columns: Purchase Date (stored as string).

Missing Values: The Returns column contains some missing values (NaN), indicating that not all transactions have return information available. These need to be handled appropriately depending on the analysis requirements.

Duplicates: No duplicate entries were observed for the Customer ID, indicating that each customer is uniquely represented in the dataset.

Data Consistency: There are two age-related columns, Customer Age and Age, which may create redundancy or inconsistencies. Further examination is required to determine if they represent different points in time or if one can be removed.

Customer Metrics

• Missing Values:

  • Tenure: 0.05% missing
  • HourSpendOnApp: 0.05% missing
  • CouponUsed: 0.05% missing

• Duplicates: 0 duplicate records found