CartDNA — Retail Customer Intelligence 🧬🛒

Project Overview

End-to-end retail analytics project that decodes the DNA of customer shopping behaviour using real-world transaction data. Analyzes customer purchasing patterns, sales trends, and segment performance across 263 stores.

Business Problem

"Which customer segments drive the most revenue, and how can we optimize our chip category strategy?"

Dataset

File	Rows	Description
QVI_transaction_data.xlsx	2,64,836	Store transactions
QVI_purchase_behaviour.csv	72,637	Customer segments

Project Structure

CartDNA/
│
├── 📁 data/
│   ├── 📁 raw/
│   │   ├── QVI_purchase_behaviour.csv
│   │   └── QVI_transaction_data.xlsx
│   ├── 📁 processed/
│   │   ├── merged_transactions.csv
│   │   └── rfm_customer_segments.csv
│   └── 📄 data_dictionary.md
│
├── 📁 notebooks/
│   ├── 01_data_preparation.ipynb
│   ├── 02_eda_hypothesis_testing.ipynb
│   ├── 03_customer_clustering.ipynb
│   └── 04_tableau_data_export.ipynb
│
├── 📁 src/
│   ├── __init__.py
│   ├── data_cleaning.py
│   ├── hypothesis_tests.py
│   └── customer_segmentation.py
│
├── 📁 dashboards/
│   └── dashboard_screenshots/
│       ├── executive_summary.png
│       ├── customer_analytics.png
│       └── store_performance.png
│
├── 📁 reports/
│   ├── Executive_Summary.pdf
│   └── Business_Recommendations.pdf
│
├── 📁 images/
├── 📁 outputs/
│   ├── kpi_summary.csv
│   ├── cluster_performance.csv
│   └── tableau_ready.csv
│
├── 📄 README.md
├── 📄 requirements.txt
├── 📄 analysis_pipeline.md
└── 📄 .gitignore

Tech Stack

• Python — Pandas, NumPy, Scikit-learn, Matplotlib, Seaborn, SciPy
• Tableau — Interactive dashboards + Story
• Jupyter — Analysis notebooks

How to Run

git clone https://github.com/trbor/CartDNA
cd CartDNA
pip install -r requirements.txt
jupyter notebook notebooks/01_data_preparation.ipynb

Tableau Dashboards

Dashboard	Link
📊 Executive Summary	View Dashboard
👥 Customer Analytics	View Dashboard
🏪 Store Performance	View Dashboard

Key Insights

• 🏆 Top Brand : KETTLE (AUD 390,239 revenue)
• 📈 Peak Month : December 2018 (AUD 167,913)
• 👥 Top Segment : VIP/Champions (AUD 64.27 avg spend)
• 🛒 Most Popular Size : 175g pack (64,928 transactions)
• 💰 Total Revenue : AUD 1,933,109
• 🏪 Top Store : Store 226 (AUD 17,605)
• 🔬 Key Finding : Older Families spend 2x more than Young Singles (Cohen's D=0.875)

Methodology

Phase 1 — Data Preparation

• 264,836 transactions cleaned and merged
• Removed duplicates, outliers, fixed DATE format
• Extracted BRAND and PACK_SIZE from PROD_NAME

Phase 2 — Exploratory Data Analysis

• Univariate, Bivariate, Time Series Analysis
• Outlier detection using IQR Method
• Store performance analysis (272 stores)

Phase 3 — A/B Hypothesis Testing

• Independent T-test on customer segments
• Cohen's D for practical significance
• 2 hypothesis tests conducted

Phase 4 — RFM Analysis + K-Means Clustering

• RFM calculated for 72,636 customers
  • Recency : Days since last purchase
  • Frequency : Total transactions
  • Monetary : Total spend (AUD)
• StandardScaler applied for normalization
• Optimal K selected using:
  • Elbow Method
  • Silhouette Score (K=4 selected)
• K-Means Clustering (K=4, random_state=42)
• 4 Segments identified:
  • VIP/Champions → High value, frequent buyers
  • Loyal Customers → Regular, moderate spenders
  • At Risk → Declining engagement
  • Lost Customers → Inactive, low value

Phase 5 — Tableau Dashboard

• 16 sheets created
• 3 interactive dashboards
• 6-point data story
• Published on Tableau Public

Key Results — Customer Segments

Segment	Customers	Avg Spend	Total Revenue
🏆 VIP/Champions	9,387	AUD 64.27	AUD 603,316
💚 Loyal Customers	20,536	AUD 38.64	AUD 793,437
⚠️ At Risk	25,950	AUD 14.10	AUD 365,942
❌ Lost Customers	16,763	AUD 10.17	AUD 170,412

Hypothesis Testing Results

Test	Groups	p-value	Cohen's D	Conclusion
Test 1	Premium vs Mainstream	0.0000	0.054 (Negligible)	Statistically significant but not practical
Test 2	Older Families vs Young Singles	0.0000	0.875 (Large)	Highly significant and practical ✅

Business Recommendations

1. 🏆 Retain VIP Customers — Loyalty rewards + premium offers
2. ⚠️ Convert At Risk → Loyal — Win-back campaigns + discounts
3. 📦 Stock 175g Packs — Most popular size across all segments
4. 📅 Focus on December — Maximum inventory + promotions
5. 🏪 Review Bottom Stores — Store 211, 76 need urgent attention

Author

Tarun Kumar Malviya