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

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 Metrics

• Missing Values:

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

• Duplicates: 0 duplicate records found