Skip to content

03. Data Understanding

Jump to bottom
Joshua Ng edited this page Nov 9, 2024 · 32 revisions

Data Sources and collection methods

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

Campaigns Table

All messages are related to some kind of campaigns. campaign_id is unique only for the specific campaign_type. Two campaigns with different campaign_type can have the same campaign_id. So the unique campaign identifier is campaign_type + campaign_id.

Column Name Description Type
id Campaign ID int64
campaign_type Campaign type (bulk, trigger, transactional) object
channel Channel (email, mobile_push, web_push, sms) object
topic Meaning of a campaign object
started_at Bulk campaign start datetime object
finished_at Bulk campaign finish datetime object
total_count Total recipients in bulk campaign float64
ab_test Bulk campaign with A/B test mode object
warmup_mode Bulk campaign with warmup mode object
hour_limit Hour limit for a bulk campaign with warmup mode float64
subject_length Email subject length float64
subject_with_personalization Subject contains recipient's name object
subject_with_deadline Subject has deadline meaning object
subject_with_emoji Subject has emoji symbols object
subject_with_bonuses Subject mentions bonuses for actions object
subject_with_discount Subject mentions a discount object
subject_with_saleout Subject mentions a sale out object
is_test It's a test campaign (bulk campaigns only) object
position Priority of trigger campaigns float64

Messages Table

Column Name Description Type
id Message sequence ID (not useful) int64
message_id Message unique ID object
campaign_id Campaign ID (from campaigns.csv) int64
message_type Campaign type object
client_id Client ID int64
channel Message channel (email, web_push, mobile_push, sms) object
category Category (useless) NULL
platform Device type used to open a message object
email_provider Public email provider (for email messages) object
stream Additional identifier of data source object
date Date of the message event object
sent_at Timestamp when the message was sent object
is_opened Indicator if the message was opened int64
opened_first_time_at Timestamp when the message was first opened object
opened_last_time_at Timestamp when the message was last opened object
is_clicked Indicator if the message was clicked int64
clicked_first_time_at Timestamp when the message was first clicked object
clicked_last_time_at Timestamp when the message was last clicked object
is_unsubscribed Indicator if the recipient unsubscribed int64
unsubscribed_at Timestamp when the recipient unsubscribed object
is_hard_bounced Indicator if the message hard bounced int64
hard_bounced_at Timestamp when the message hard bounced object
is_soft_bounced Indicator if the message soft bounced int64
soft_bounced_at Timestamp when the message soft bounced object
is_complained Indicator if a complaint was made int64
complained_at Timestamp when the complaint was made object
is_blocked Indicator if the message was blocked int64
blocked_at Timestamp when the message was blocked object
is_purchased Indicator if a purchase was made int64
purchased_at Timestamp when the purchase was made object
created_at Timestamp when the record was created object
updated_at Timestamp when the record was last updated object

Initial data exploration finds

Customer Profile

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:

  1. 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.

  2. 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).

  3. 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.

  4. 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

  • 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.

Campaigns

  1. Dataset Overview: The dataset consists of 1,906 rows and 19 columns
  2. Data Types:
    • Numerical Data Types: id, total_count, hour_limit, subject_length and position
    • Categorical Data Types: campaign_type, channel, topic, ab_test, warmup_mode, subject_with_personalization, subject_with_deadline, subject_with_emoji, subject_with_bonuses, subject_with_discount, subject_with_saleout and is_test
    • Datetime Data Types: started_at and finished_at
  3. Missing Values: ab_test, position and is_test columns have high percentages of missing data and will be flagged for potential removal due to sparsity

Messages

  1. Dataset Overview: The dataset consists of 32 columns and an extensive number of rows. Due to its large size, we extracted a portion of the dataset for initial exploration.
  2. Missing Values: category column is entirely null which is not useful for analysis. platform column has 93% null values and email_provider column has 42% null values.
  3. Redundant Column: stream column containsonly one unique value, making it redundant
  4. Data Types: is_clicked,

Data Quality assessment

Customer Profile

Completeness

  • Returns Column: This column has missing values for approximately 19% of the records, with only 202,618 non-null values out of 250,000.
  • Other Columns: All other columns are fully populated, with 250,000 non-null entries.

Consistency

  • Duplicate Columns: The dataset contains both Customer Age and Age columns, which appear to hold identical information.
  • Total Purchase Amount Discrepancy: There is a significant discrepancy between the Total Purchase Amount and the calculated value of Product Price * Quantity. Approximately 99.98% of the records (249,962 rows) do not match this calculation, indicating a potential data quality issue. This inconsistency may result from factors like discounts, additional fees, or data entry errors, but we do not have enough information to conclude.

Validity

  • Date Format: The Purchase Date is currently stored as an object (string) type, and proper date-time formatting should be applied.
  • Numerical Columns: No apparent issues with the data types for columns like Product Price, Quantity, and Total Purchase Amount, which are integers, or Returns, which is a float.

Customer Metrics

  • Missing Values:

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

  • Duplicates: 0 duplicate records found

Campaigns

  1. Missing Values Analysis:
    • ab_test, position and is_test columns have high percentages of missing data above 95%
    • finished_at, started_at, total_count, warmup_mode, topic, subject_length, subject_with_personalization, subject_with_deadline, subject_with_emoji, subject_with_bonuses, subject_with_discount and subject_with_saleout have a majority low percentage of missing data of below 5%
  2. Duplicates: There are no duplicated rows and columns in the dataset
  3. Validity: started_at and finished_at columns should be converted from object to datetime format to ensure accurate chronological operations

Messages

Customer Reviews Table

Column Name Description
product_id Product ID of item purchased
product_name Name of the product
category Category the product belongs to
discounted_price Discounted price of the product
actual_price Actual price of the product
discount_percentage Percentage of the discount of its original price
rating Rating of the product on Amazon’s Website, on a scale from 0 to 5
rating_count Number of people who voted for the rating for that product
about_product A description of the product
user_id User ID of the consumer that purchased the product
user_name The username of the consumer
review_id Review ID of consumer who purchased the product gave
review_title Summary of the review
review_content Content of the review given by the consumer
img_link Link containing an image of the product
product_link Link of the product on the Amazon website

The table has a primary key – Order ID and foreign key – Review ID. The Order ID is a randomly generated unique ID sourced from the Orders Table.

Dataset Overview: The dataset contains 1465 entries with 16 columns. Each feature is a pandas object data type.

Missing values and duplicates:

  • The column rating_count has two missing entries, accounting for 0.0014% of all observations.
  • There are no duplicated observations associated with the data.

Selection of columns:

For this dataset, 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.

Feature Engineering:

  • Transform “actual_price” and “rating” columns to float data types.
  • Rename columns for easier intuition.
  • Encode “category” feature as a categorical variable. Trim its entries for easier intuition.

Cleaning and generation of Order IDs:

  • Remove observations with missing values.
  • Remove observations that do not belong to Electronics and Electronic-related categories, which is central to our main project’s analysis.
  • Order ID, a unique, randomly generated ID, is sourced from Orders Table and assigned to each remaining observation of the Reviews Table.

Clone this wiki locally