Back to Projects Data Analytics & Business Intelligence

Customer Shopping Behavior Analysis

Transforming 3,900+ retail consumer transactions into clear behavioral segmentation and commercial growth strategies.

Executive Summary

A retail business had thousands of customer transactions but lacked visibility into what drives repeat purchases, which customer age groups generate the highest revenue, and whether discount promotions actually improve margins. Using Python for data cleaning, advanced SQL for demographic segmentation, and Power BI for interactive dashboard visualization, I uncovered critical behavioral patterns—revealing that Clothing ($43K) & Accessories ($31K) drive over 75% of sales, while Young Adults form the primary revenue pillar. These insights were translated into 5 actionable commercial strategies.

Domain Retail / Consumer E-Commerce
Role Data Analyst & BI Developer
Stack Python (Pandas) • SQL • Power BI
Core KPIs AOV: $59.57 • Rating: 3.76/5 • 3.9K Records
Power BI Live View — Customer Behavior & Segmentation Dashboard
Live Project Asset
Customer Behavior Power BI Dashboard

Data Rich, Decision Blind

Why having raw sales records without structured analytics leads to wasted ad spend and missed revenue.

Demographic Blind Spots

The business couldn't quantify which age brackets or customer genders generated the highest Average Order Value (AOV). Marketing budgets were spent universally rather than focused on high-yielding cohorts.

Discount Cannibalization

Promotional discounts were handed out broadly without analyzing whether they attracted profitable high-spend buyers or simply eroded profit margins on price-sensitive bargain hunters.

Lack of Customer Segmentation

There was no systematic classification to separate one-time buyers from returning and high-loyalty shoppers, making it impossible to tailor retention emails or VIP loyalty programs effectively.

From Raw Data to Business Clarity

A structured, reproducible pipeline turning messy retail records into interactive executive dashboards.

Stage 01

Data Cleaning (Python & Pandas)

Cleaned 3,900 raw transaction records: audited missing data, standardized categorical fields, engineered age bracket columns (Young Adult, Adult, Middle-aged, Senior), and exported clean CSVs.

Python + Pandas ETL
Stage 02

Deep SQL Querying & Modeling

Authored 10 specialized SQL scripts using CTEs, Window Functions (ROW_NUMBER), subqueries, and CASE statements to isolate high-value buyers and category leaders.

SQL Server / PostgreSQL
Stage 03

Interactive Power BI Dashboard

Constructed an interactive BI report with dynamic slicers (Subscription, Gender, Category, Shipping Type), KPI cards, and category revenue ranking charts.

Power BI Business Intelligence

Customer Behavior Dashboard Breakdown

A high-level view of customer volume, purchasing power, category distribution, and demographic trends.

Active Analyzed Cohort 1.599K Customers Filtered Target Segment
Average Order Value $59.57 Average Spend Per Order
Customer Satisfaction 3.76 / 5.0 Average Review Score
Top Category Clothing ($43K) 724 Total Items Sold
View: Customer Behavior Executive Dashboard (Power BI) Interactive BI
Customer Behavior Dashboard

Commercial Patterns Revealed

The dashboard enables instant slicing by Subscription Status, Gender (Female vs. Male), Product Category, and Shipping Method to isolate profit drivers.

Category Revenue Dominance: Clothing leads with $43K (724 units) and Accessories with $31K (514 units), while Footwear ($14K) and Outerwear ($7K) represent smaller niche lines.
Demographic Spending Power: Young Adults generate the highest total revenue ($26K+) and transaction volume (400+ orders), followed by Seniors and Adults.
Subscription Growth Runway: The 100% non-subscribed rate in this target cohort highlights an untapped opportunity for a high-retention membership program.

Key Analytical Findings (SQL Insights)

10 SQL queries answered critical business questions regarding customer lifetime value and promotion efficiency.

01

Revenue by Gender Split

Analyzed revenue across gender segments, establishing baseline order values to evaluate whether ad creatives should be split or unified.

02

"Smart Discount" Shoppers

Identified customers who applied discount codes but still maintained an Average Order Value above the $59.57 benchmark—representing high-value shoppers worth VIP perks.

03

Top-Rated Flagship Items

Ranked top 5 products by customer review rating (>4.0 score) to feature as hero items in promotional banners and social proof ads.

04

Express Shipping Correlation

Discovered that shoppers who select Express Shipping demonstrate a significantly higher average cart spend than Standard and Store Pickup users.

05

Subscription Program ROI

Benchmarked transaction volume between subscribed and non-subscribed buyers, validating that loyalty subscribers yield stronger recurring lifetime value.

06

3-Tier Customer Segmentation

Used a SQL CTE to segment the customer base into New (1 purchase), Returning (2–10 purchases), and Loyal (>10 purchases) cohorts for targeted marketing.

07

Age Demographic Hierarchy

Ranked revenue generation by age cohort: Young Adults ($26K+) > Seniors ($24K) > Adults ($23K) > Middle-aged ($22K).

🏆

Category Bestsellers via Window Functions

Applied ROW_NUMBER() OVER (PARTITION BY Category ORDER BY TotalSales DESC) to extract the top 3 best-selling products per department.

5 Actionable Business Recommendations

Data-backed initiatives designed for leadership to optimize retail operations and improve retention.

01

Launch VIP Perks for "Smart Discount" Buyers

Rather than generic discount coupons, reward customers who spend above $60 with exclusive early-access perks and loyalty points to safeguard gross margins.

02

Focus Marketing on Young Adults & Seniors

Concentrate paid social ad budgets on Young Adults (highest volume) and Seniors (consistent high cart values) with tailored product collections.

03

Convert "Returning" Shoppers to "Loyal"

Automate personalized re-engagement emails for shoppers with 2–5 purchases, introducing cross-category product recommendations from Clothing and Accessories.

04

Offer Free Express Shipping Threshold

Since Express shipping buyers spend more, set a free Express Shipping threshold at $75+ to incentivize larger basket sizes and increase AOV.

05

Promote Category Bestsellers on Homepage

Feature the top 3 best-selling products identified in each category on the store homepage and in email marketing to leverage proven social proof.

06 / Professional Takeaway

Turning Transactional Data into Commercial Strategy

This case study demonstrates the core value of a Business-Oriented Data Analyst: taking raw, messy transaction records and translating them through Python cleaning, advanced SQL querying, and Power BI visualization into concrete commercial decisions that business leaders can execute immediately to drive revenue and customer retention.