Customer Shopping Behavior Analysis
This project analyzes customer shopping behavior using transactional data from 3,900 purchases. The goal is to uncover insights into spending patterns, customer segments, product preferences, and subscription behavior to guide strategic business decisions.
Project Overview & Dataset Summary
Project Goal
Uncover insights into spending patterns, customer segments, product preferences, and subscription behavior to guide strategic business decisions.
Dataset Size
3,900 rows and 18 columns of transactional data.
Key Features
Customer demographics, purchase details, and shopping behavior metrics.
Missing Data
37 values in the 'Review Rating' column, handled during data preparation.
Exploratory Data Analysis (EDA) with Python
The initial phase of this project involved comprehensive data preparation and cleaning using Python. This ensured data quality and readiness for in-depth analysis.
01
Data Loading & Exploration
Imported dataset with pandas, checked structure with df.info() and summary statistics with .describe().
02
Missing Data Handling
Imputed missing 'Review Rating' values using the median rating per product category.
03
Column Standardization
Renamed columns to snake case for improved readability and documentation.
04
Feature Engineering
Created 'age_group' and 'purchase_frequency_days' columns for richer analysis.
05
Data Consistency Check
Verified and dropped redundant 'promo_code_used' column.
06
Database Integration
Loaded the cleaned DataFrame into SQL SERVER DATABASE for subsequent SQL analysis.
Data Analysis with SQL: Key Business Questions
Structured analysis in SQL Server was performed to answer critical business questions, providing actionable insights into customer behavior.
1
Revenue by Gender
Compared total revenue generated by male vs. female customers.
2
High-Spending Discount Users
Identified customers who used discounts but still spent above average.
3
Top 5 Products by Rating
Found products with the highest average review ratings.
4
Shipping Type Comparison
Compared average purchase amounts between Standard and Express shipping.
5
Subscribers vs. Non-Subscribers
Compared average spend and total revenue across subscription statuses.
SQL Insights: Revenue & Discounts
Revenue by Gender
Female customers generated slightly higher total revenue compared to male customers.
High-Spending Discount Users
Identified customers who utilize discounts but still contribute significantly above the average purchase amount, indicating effective discount targeting.
SQL Insights: Product Performance
Top 5 Products by Rating
Highlighted products with the highest average review ratings, indicating strong customer satisfaction and potential for promotional focus.
Discount-Dependent Products
Identified products that are frequently purchased with discounts, suggesting they may be sensitive to pricing strategies.
SQL Insights: Shipping & Subscriptions
Shipping Type Comparison
Customers using Express shipping tend to have higher average purchase amounts, suggesting a correlation between urgency and spending.
Subscribers vs. Non-Subscribers
Subscribers show higher average spend and total revenue, emphasizing the importance of subscription programs.
SQL Insights: Customer Segmentation
New Customers
First-time buyers, representing growth potential.
Returning Customers
Repeat buyers, indicating initial satisfaction.
Loyal Customers
Frequent purchasers, forming the core customer base.
Customers were classified into New, Returning, and Loyal segments based on their purchase history, allowing for targeted engagement strategies.
SQL Insights: Age & Repeat Buyers
Revenue by Age Group
Calculated total revenue contribution of each age group, identifying key demographic segments for marketing focus.
Repeat Buyers & Subscriptions
Analysis revealed that customers with more than 5 purchases are significantly more likely to subscribe, indicating a strong link between loyalty and subscription uptake.
Dashboard in Power BI
A comprehensive dashboard was created in Power BI to visualize key metrics and insights from the customer shopping behavior analysis, providing an interactive tool for strategic decision-making.
Business Recommendations: Growth & Loyalty
Based on our analysis, we propose actionable strategies to boost subscriptions, enhance customer loyalty, and optimize product positioning.
Boost Subscription
Promote exclusive benefits and value propositions for subscribers to increase sign-ups and retention.
Customer Loyalty Programs
Implement reward programs to incentivize repeat buyers and transition them into the "Loyal" customer segment.
Product Positioning
Highlight top-rated and best-selling products in marketing campaigns to leverage their popularity.
Business Recommendations: Optimization & Targeting
Further recommendations focus on refining discount policies and implementing targeted marketing efforts for maximum impact.
Review Discount Policy
Carefully balance sales boosts with margin control to ensure profitability while attracting customers.
Targeted Marketing
Focus marketing efforts on high-revenue age groups and express-shipping users for optimized campaign effectiveness.
Made with