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.
  • Products: Removed all rows where 'Price' is missing. There was not enough data to compute the missing prices, and the number of entries with missing prices was insignificant relative to total number of entries.

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)

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

Campaigns Dataset

1. Assigning Random Product IDs and Prices: Product IDs and Prices are randomly sampled from our Product dataset and aligned to the campaigns.

• Diversity: Random assignment ensures diverse product-campaign pairings, minimizing biases that can occur if the same few products were repeatedly assigned to campaigns. This diversity helps prevent overfitting during model training by ensuring broad product representation.
• Reproducibility: Using a fixed random_state enables reproducibility of the sampling process, ensuring consistent results during testing and evaluation
• Industry Alignment: Though this process primarily introduces variation, it aligns with market practices where product offerings vary across campaigns.

2. Generating Synthetic Data for Product Costs: To calculate ROI, we generated data on product costs.

• Process: We generated the product_cost column by applying a random markup reduction within predefined bounds (30%-50%) to the product_price.
• Realism: Applying a random markup assumes natural variability in markup percentages, reflecting real-world pricing strategies.
• Research-Backed Markup Range: The 30%-50% markup range is based on typical industry practices for pricing products (Carney, 2023). Aligning these percentages with market standards ensures the synthetic data remains plausible.

3. Generating Synthetic Data for Percentage of Recipients Purchasing a Product: To calculate ROI, we generated the percentage_purchased column based on each channel. This feature directly measures campaign success probabilities across channels, providing strategic insights for marketing optimizations.

• Process: We transformed channel data into a quantitative percentage_purchased feature based on researched conversion rates for different channels. For multichannel campaigns where all three channels (mobile_push, email, sms) are used, we assumed the highest individual channel conversion rate (35% from email) is augmented by a 10% booster effect, resulting in an enhanced conversion rate of 38.5%. This booster effect is grounded in the theoretical synergy of using multiple channels simultaneously.
• Variability: The process involves random selection within researched conversion rate ranges for each channel (except fixed values for mobile_push and multichannel), introducing realistic variability in consumer behavior. This accounts for unpredictable response differences across campaigns, enhancing the dataset's robustness.
• Empirical Grounding: Conversion rate ranges such as 25%-35% for email, 11%-20% for sms, or fixed values for channels like mobile_push (28%) and multichannel (38.5%) are derived from credible industry reports and studies. This ensures that the synthetic data respects historical and empirical consumer interaction patterns, making the modeling process not only robust but also reflective of real-world marketing dynamics.

4. Calculating Channel Costs: Understanding the financial impact of each marketing channel is crucial in addition to analyzing consumer behavior insights. We engineered features related to the costs associated with different marketing channels, drawing on comprehensive research and pricing models from OneSignal (Onesignal, n.d.) and Twilio (specifically for SMS per recipient cost) (Twilio, n.d.).

• Channel-Specific Pricing:
  • Mobile Push: Calculated per recipient, with an additional a flat fee.
  • Email: Pricing is either a flat fee or tiered based on total recipient count.
  • SMS: Involves a per recipient cost combined with a base fee.
  • Multichannel: Utilizes a combined cost structure that reflects the simultaneous use of email, SMS, and mobile_push.

Products Dataset

1. Generating Synthetic Prices for Competitor's Pricing:

• Process: We generated the Competitor Price column by applying a random price differential of 30% to the product price.
• Reasoning: The difference between the two lowest prices averages 23 per cent when two firms list prices (Baye et al., 2004). To allow for some flexibility and noise, we chose the price difference to differ in a range of 30%.

2. Generating Synthetic Data for Stock Levels:

• Process: We generated stock levels by selecting a random integer from 0 to 100 to represent the stock levels.
• Reasoning: This method simulates realistic variability in stock availability, accounting for situations where certain products may be in surplus, low supply, or even temporarily out of stock. Generating these values randomly allows for diversity in data, which can be useful in testing inventory models and algorithms, as well as in understanding demand-supply impacts without requiring real stock data.

3. Generating Synthetic Data for Restock Frequency:

• Process: We generated restock frequency be selecting a random frequency from 10, 20, or 30 days.
• Reasoning: Choosing a random restock frequency from a set of 10, 20, or 30 days provides a realistic and flexible simulation for synthetic data. These intervals represent common inventory restocking schedules, capturing the diversity in how often products might be restocked. For example, items with high sales may require more frequent restocks (10 days), while low sales items might be replenished less often (20 or 30 days).

4. Generating Synthetic Data for Historical Price:

• Process: We generated old prices (for analysis of Price Elasticity) by applying a random price differential of 20% to the product price.
• Reasoning: This creates variability in historical pricing, enabling a realistic assessment of price elasticity. This range reflects typical adjustments due to seasonal sales, promotions, or market fluctuations, which companies often implement to optimise revenue.

5. Generating Synthetic Data for Historical Sales:

• Process: We calculated historical total sales by using a formula that scales the average sales according to the ratio of the average price to each product's historical price. A small random integer adjustment between 0 and 4 was added to introduce minor variability and noise.
• Reasoning: This method creates historical total sales values that reflect realistic variations in response to past price changes. By adjusting sales based on historical prices, we can simulate how price changes may have impacted sales volume.

6. Distributing Supplier IDs Based on Product Grouping:

• Process: We assigned Supplier IDs by first creating an initial grouping based on the first word of each product’s name combined with its category. Unique (first word, category) pairs were assigned supplier IDs sequentially. IDs 1-49 were fixed, and the remaining products were assigned new supplier IDs to reach a total of 150, distributed evenly across categories. A minor adjustment was made by incrementally increasing the count for some categories to ensure precisely 150 suppliers.
• Reasoning: This approach ensures that Supplier IDs are logically distributed based on product groupings and categories, allowing for a structured assignment that maintains consistency for the initial 49 suppliers while evenly balancing the remaining suppliers across categories. This method accommodates varying product types and enables flexibility for future additions or adjustments in supplier distribution.

Orders Dataset

1. Assigning random IDs for CustomerID, ProductID:

• Process: CustomerID and ProductID are randomly sampled from the Customer and Products dataset respectively.
• Reasoning: Each order entry reflects a plausible combination of customers and products, simulating a natural purchasing pattern. This technique also retains the diversity of the customer and product base.

2. Generating Synthetic Data for Returns:

• Process: We generated Returns data by randomly selecting 0 or 1 for each order, with a probability distribution of 90% and 10% respectively. 1 indicates the items was returned, and 0 indicates otherwise.
• Reasoning: This reflects typical return rates found in e-commerce. Most orders are successfully fulfilled without returns, with only a small fraction involving returns, often around 10% depending on industry and product type (Hjort, 2010). This distribution reflects real-world customer behaviour.

3. Generating Synthetic Data for Promotional Periods:

• Process: We generated Promotional data by randomly selecting 0 or 1 for each order. 1 indicates the order was placed during a promotional period, and 0 indicates otherwise.
• Reasoning: This method reflects how promotions often only apply to a subset of orders, helping to capture the influence of promotions on customer buying behaviour.

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 Dataset is created by merging the synthetic datasets modelled from Customer Profile and Customer Metric table, resulting in a comprehensive 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 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

Columns dropped:

• Remove columns with information that can be found in separate datasets (e.g. DaySinceLastOrder) as retaining it could lead to data inconsistencies and complicate future analysis.
• Dropped columns we did not have sufficient information on columns like CityTier and WarehouseToHome, which could compromise our analysis.

Synthetic Generation:

• 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

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.

A/B test Dataset

Since real A/B test data is not available, we generate synthetic data for both a control group (customers who did not receive the email) and a treatment group (customers who received the email).

To simulate a realistic A/B test on customers of the ecommerce platform, we:

• Used actual churn, emails opened and purchase rates from our current dataset to ensure the synthetic data reflects actual customer behavior.
• Introduce random perturbations to the treatment group's churn and purchase rates to represent natural variability in how customers interact with the email campaign.

Products Dataset

Price

• Conversion of Prices from Rupees to USD for a more universal interpretation
• Created a new column 'log price' which is the logarithmic transformation of price. This was done due to the right-skewed distribution of prices.

Category Dropped

• Only products under Home Appliances, Electronics and Clothing are kept to be consistent with the product categories across our datasets.

Orders Dataset

Category Dropped

• Only kept relevant columns needed for analysis (Order and Shipping Dates, Actual and Scheduled Days for Shipping, Late Delivery Risk, Discount)

SQL queries 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.
Complain	int64	Any complaints raised in the last month
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. Products Table
   • Description: Contains information about each product.
   • Row Count: 20,642 rows

Column Name	Data Type	Description
ProductID	int64	Unique identifier for each product. Primary Key.
Supplier_ID	int64	Unique identifier for the supplier of each product.
Product Name	object	Full Name of the Product
Category	object	Product Category (Electronics, Home Appliances or Clothing)
Price	float64	Sale price of the product.

5. Orders Table
   • Description: Contains information about every order made.
   • Row Count: 168,946 rows

Column Name	Data Type	Description
OrderID	int64	Unique identifier for each order. Primary Key.
ProductID	int64	Identifier for the product purchased during this order. Foreign Key linking to the products table.
CustomerID	int64	Identifier for the customer who placed the order. Foreign Key linking to the customers table.
Order Date	object	Date when the order was placed.
Shipping Date	object	Date when the product was shipped.
Actual days for shipping	int64	Actual number of days taken to ship the product to the customer.
Scheduled days for shipping	int64	Number of days scheduled for shipping as initially planned.
Late Delivery Risk	int64	Indicator (0 or 1) showing if the product was at risk of late delivery
Discount Percentage	float64	Discount applied on the product during the order, represented as a percentage.
Returns	int64	Indicator (0 or 1) showing if the product was returned.
Promotional Period	int64	Indicator (0 or 1) showing if the product was purchased during a promotional period.

6. Customer Reviews Table

• Description: Contains customers' reviews and ratings of their product bought.
• Row count: 979 rows

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

7. A/B Test Data

• Tables:
  • campaign_treatment
  • campaign_control
• Description: These tables contain A/B test results, with data on customer interactions with marketing campaigns. Each table includes information on whether the customer opened the campaign, made a purchase, and subsequently churned.
• Row count: Each table has 5,000 rows.

Column Name	Type	Description
customer_id	int64	Unique identifier for each customer
group	object	Campaign group designation (treatment or control)
is_opened	int64	Indicates if the customer opened the campaign (1 for opened, 0 for not opened)
is_purchased	int64	Indicates if the customer made a purchase after opening the campaign (1 for purchased, 0 for not purchased)
churned	int64	Specifies if the customer churned after the campaign (1 for churned, 0 for retained)