1┌──────────────────────────────────────────────────────────┐
2│               CAPSTONE PROJECT STRUCTURE                  │
3├──────────────────────────────────────────────────────────┤
4│                                                           │
5│  🎯 OBJECTIVE                                            │
6│  Build a complete data analysis project that             │
7│  demonstrates your skills to potential employers         │
8│                                                           │
9│  📋 DELIVERABLES                                         │
10│  1. Jupyter Notebook (analysis)                          │
11│  2. Executive Report (PDF/HTML)                          │
12│  3. Interactive Dashboard                                │
13│  4. Automated Report Script                              │
14│  5. GitHub Repository with documentation                 │
15│                                                           │
16│  ⏱️ ESTIMATED TIME: 8-12 hours                           │
17│                                                           │
18│  🛠️ SKILLS DEMONSTRATED                                  │
19│  • Python (Pandas, NumPy, Matplotlib)                    │
20│  • SQL (Window functions, CTEs, Optimization)            │
21│  • Data Storytelling & Visualization                     │
22│  • Business Analysis (Cohort, Funnel, Time Series)       │
23│  • Automated Reporting                                   │
24│                                                           │
25└──────────────────────────────────────────────────────────┘

1OPTION A: E-COMMERCE ANALYTICS
2─────────────────────────────────────────────────────────
3• Transactions, customers, products data
4• Analysis: Revenue trends, cohort retention, RFM
5• Business questions: Customer lifetime value, churn prediction
6 
7OPTION B: SAAS METRICS
8─────────────────────────────────────────────────────────
9• Subscription data, usage events, support tickets
10• Analysis: MRR trends, feature adoption, churn
11• Business questions: Product stickiness, expansion revenue
12 
13OPTION C: MARKETING CAMPAIGN
14─────────────────────────────────────────────────────────
15• Campaign performance, leads, conversions
16• Analysis: Funnel analysis, attribution, ROI
17• Business questions: Channel optimization, CAC/LTV
18 
19OPTION D: YOUR OWN DATA
20─────────────────────────────────────────────────────────
21• Any dataset you have access to
22• Real-world data preferred
23• Must have at least 3 tables and 10,000+ rows

1my-data-analysis-portfolio/
2│
3├── README.md                 # Project overview
4├── requirements.txt          # Dependencies
5├── .gitignore
6│
7├── data/
8│   ├── raw/                  # Original data
9│   ├── processed/            # Cleaned data
10│   └── external/             # External reference data
11│
12├── notebooks/
13│   ├── 01_data_exploration.ipynb
14│   ├── 02_data_cleaning.ipynb
15│   ├── 03_analysis.ipynb
16│   └── 04_modeling.ipynb
17│
18├── src/
19│   ├── __init__.py
20│   ├── data_loader.py        # Data loading utilities
21│   ├── analysis.py           # Analysis functions
22│   ├── visualization.py      # Chart functions
23│   └── reporting.py          # Report generation
24│
25├── reports/
26│   ├── executive_summary.pdf
27│   ├── detailed_analysis.html
28│   └── figures/
29│
30├── dashboard/
31│   ├── app.py               # Streamlit/Dash app
32│   └── assets/
33│
34└── tests/
35    └── test_analysis.py

1"""
2setup_project.py - Initialize project structure
3"""
4
5import os
6from pathlib import Path
7
8def setup_project(project_name: str):
9    """Create project directory structure"""
10    
11    base_path = Path(project_name)
12    
13    # Define directory structure
14    directories = [
15        'data/raw',
16        'data/processed',
17        'data/external',
18        'notebooks',
19        'src',
20        'reports/figures',
21        'dashboard/assets',
22        'tests'
23    ]
24    
25    # Create directories
26    for dir_path in directories:
27        (base_path / dir_path).mkdir(parents=True, exist_ok=True)
28        print(f"✅ Created: {dir_path}")
29    
30    # Create essential files
31    files = {
32        'README.md': README_TEMPLATE,
33        'requirements.txt': REQUIREMENTS_TEMPLATE,
34        '.gitignore': GITIGNORE_TEMPLATE,
35        'src/__init__.py': '',
36        'tests/__init__.py': ''
37    }
38    
39    for filename, content in files.items():
40        filepath = base_path / filename
41        filepath.write_text(content)
42        print(f"✅ Created: {filename}")
43    
44    print(f"\n🎉 Project '{project_name}' setup complete!")
45    print(f"   cd {project_name}")
46    print(f"   pip install -r requirements.txt")
47
48README_TEMPLATE = """# Data Analysis Portfolio Project
49
50## Overview
51A comprehensive data analysis project demonstrating skills in Python, SQL, 
52data visualization, and business analytics.
53
54## Project Structure
55- `data/` - Raw and processed data files
56- `notebooks/` - Jupyter notebooks with analysis
57- `src/` - Python modules for data processing
58- `reports/` - Generated reports and visualizations
59- `dashboard/` - Interactive dashboard application
60
61## Key Findings
62[To be completed after analysis]
63
64## Skills Demonstrated
65- Data wrangling with Pandas
66- SQL analytics (Window functions, CTEs)
67- Statistical analysis
68- Data visualization
69- Business metrics (Cohort, Funnel, RFM)
70- Automated reporting
71
72## How to Run
73```bash
74pip install -r requirements.txt
75jupyter notebook notebooks/

1---
2 
3## 3. Phase 1: Data Exploration
4 
5### 3.1 Loading and Understanding Data
6 
7```python
8"""
901_data_exploration.ipynb
10"""
11 
12import pandas as pd
13import numpy as np
14import matplotlib.pyplot as plt
15import seaborn as sns
16 
17# Set display options
18pd.set_option('display.max_columns', 50)
19pd.set_option('display.float_format', '{:.2f}'.format)
20 
21# Load data
22orders = pd.read_csv('data/raw/orders.csv')
23customers = pd.read_csv('data/raw/customers.csv')
24products = pd.read_csv('data/raw/products.csv')
25 
26print("=" * 60)
27print("DATA OVERVIEW")
28print("=" * 60)
29 
30datasets = {'orders': orders, 'customers': customers, 'products': products}
31 
32for name, df in datasets.items():
33    print(f"\n📊 {name.upper()}")
34    print(f"   Shape: {df.shape[0]:,} rows × {df.shape[1]} columns")
35    print(f"   Memory: {df.memory_usage(deep=True).sum() / 1024**2:.2f} MB")
36    print(f"   Columns: {', '.join(df.columns[:5])}{'...' if len(df.columns) > 5 else ''}")

1def assess_data_quality(df, name='dataset'):
2    """Comprehensive data quality assessment"""
3    
4    print(f"\n{'='*60}")
5    print(f"DATA QUALITY REPORT: {name.upper()}")
6    print(f"{'='*60}")
7    
8    # Missing values
9    missing = df.isnull().sum()
10    missing_pct = (missing / len(df) * 100).round(2)
11    missing_report = pd.DataFrame({
12        'Missing': missing,
13        'Pct': missing_pct
14    })
15    missing_report = missing_report[missing_report['Missing'] > 0]
16    
17    if len(missing_report) > 0:
18        print("\n⚠️ MISSING VALUES:")
19        print(missing_report.to_string())
20    else:
21        print("\n✅ No missing values")
22    
23    # Data types
24    print("\n📋 DATA TYPES:")
25    print(df.dtypes.value_counts().to_string())
26    
27    # Duplicates
28    duplicates = df.duplicated().sum()
29    print(f"\n🔄 DUPLICATES: {duplicates:,} ({duplicates/len(df)*100:.2f}%)")
30    
31    # Unique values for categorical columns
32    print("\n📊 UNIQUE VALUES (categorical):")
33    for col in df.select_dtypes(include=['object']).columns:
34        unique_count = df[col].nunique()
35        print(f"   {col}: {unique_count:,}")
36    
37    # Numeric statistics
38    print("\n📈 NUMERIC STATISTICS:")
39    numeric_df = df.select_dtypes(include=[np.number])
40    if len(numeric_df.columns) > 0:
41        stats = numeric_df.describe().T[['min', 'max', 'mean', 'std']]
42        print(stats.to_string())
43    
44    return missing_report
45
46# Run assessment
47for name, df in datasets.items():
48    assess_data_quality(df, name)

1def create_exploration_plots(orders, customers, products):
2    """Create initial exploration visualizations"""
3    
4    fig, axes = plt.subplots(2, 3, figsize=(15, 10))
5    fig.suptitle('Data Exploration - Initial Overview', fontsize=14, fontweight='bold')
6    
7    # 1. Order distribution by day
8    ax1 = axes[0, 0]
9    orders['order_date'] = pd.to_datetime(orders['order_date'])
10    orders.set_index('order_date')['order_id'].resample('W').count().plot(ax=ax1)
11    ax1.set_title('Orders Over Time')
12    ax1.set_ylabel('Order Count')
13    
14    # 2. Revenue distribution
15    ax2 = axes[0, 1]
16    orders['order_total'].hist(bins=50, ax=ax2, edgecolor='white')
17    ax2.set_title('Order Value Distribution')
18    ax2.set_xlabel('Order Total ($)')
19    ax2.axvline(orders['order_total'].median(), color='red', linestyle='--', label='Median')
20    ax2.legend()
21    
22    # 3. Top categories
23    ax3 = axes[0, 2]
24    category_revenue = orders.groupby('category')['order_total'].sum().sort_values(ascending=True)
25    category_revenue.plot(kind='barh', ax=ax3)
26    ax3.set_title('Revenue by Category')
27    ax3.set_xlabel('Revenue ($)')
28    
29    # 4. Customer distribution
30    ax4 = axes[1, 0]
31    customer_orders = orders.groupby('customer_id').size()
32    customer_orders.value_counts().head(10).plot(kind='bar', ax=ax4)
33    ax4.set_title('Order Frequency Distribution')
34    ax4.set_xlabel('Number of Orders')
35    ax4.set_ylabel('Number of Customers')
36    
37    # 5. Hourly pattern
38    ax5 = axes[1, 1]
39    orders['hour'] = orders['order_date'].dt.hour
40    orders.groupby('hour')['order_id'].count().plot(ax=ax5, marker='o')
41    ax5.set_title('Orders by Hour')
42    ax5.set_xlabel('Hour of Day')
43    ax5.set_ylabel('Order Count')
44    
45    # 6. Day of week pattern
46    ax6 = axes[1, 2]
47    orders['day_of_week'] = orders['order_date'].dt.day_name()
48    day_order = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']
49    dow_orders = orders.groupby('day_of_week')['order_id'].count().reindex(day_order)
50    dow_orders.plot(kind='bar', ax=ax6)
51    ax6.set_title('Orders by Day of Week')
52    ax6.tick_params(axis='x', rotation=45)
53    
54    plt.tight_layout()
55    plt.savefig('reports/figures/01_data_exploration.png', dpi=150, bbox_inches='tight')
56    plt.show()
57
58# Generate plots
59# create_exploration_plots(orders, customers, products)

1"""
202_data_cleaning.ipynb
3"""
4
5import pandas as pd
6import numpy as np
7
8class DataCleaner:
9    """Data cleaning pipeline for e-commerce data"""
10    
11    def __init__(self):
12        self.cleaning_log = []
13    
14    def log_action(self, action, before, after):
15        """Log cleaning action"""
16        self.cleaning_log.append({
17            'action': action,
18            'rows_before': before,
19            'rows_after': after,
20            'rows_removed': before - after
21        })
22    
23    def clean_orders(self, df):
24        """Clean orders dataframe"""
25        initial_rows = len(df)
26        
27        # 1. Remove duplicates
28        df = df.drop_duplicates(subset=['order_id'])
29        self.log_action('Remove duplicate orders', initial_rows, len(df))
30        
31        # 2. Handle missing values
32        # - Drop rows with missing critical fields
33        critical_cols = ['order_id', 'customer_id', 'order_date', 'order_total']
34        df = df.dropna(subset=critical_cols)
35        self.log_action('Drop missing critical fields', len(self.cleaning_log[-1]) if self.cleaning_log else initial_rows, len(df))
36        
37        # 3. Convert data types
38        df['order_date'] = pd.to_datetime(df['order_date'], errors='coerce')
39        df['order_total'] = pd.to_numeric(df['order_total'], errors='coerce')
40        
41        # 4. Remove invalid dates
42        df = df[df['order_date'].notna()]
43        df = df[df['order_date'] >= '2020-01-01']
44        df = df[df['order_date'] <= pd.Timestamp.now()]
45        self.log_action('Remove invalid dates', len(df), len(df))
46        
47        # 5. Remove negative/zero orders
48        df = df[df['order_total'] > 0]
49        self.log_action('Remove negative/zero orders', len(df), len(df))
50        
51        # 6. Handle outliers (optional - cap at 99th percentile)
52        p99 = df['order_total'].quantile(0.99)
53        df = df[df['order_total'] <= p99]
54        self.log_action('Cap outliers at 99th percentile', len(df), len(df))
55        
56        # 7. Add derived columns
57        df['order_month'] = df['order_date'].dt.to_period('M')
58        df['order_week'] = df['order_date'].dt.to_period('W')
59        df['order_dow'] = df['order_date'].dt.day_name()
60        df['order_hour'] = df['order_date'].dt.hour
61        
62        return df
63    
64    def clean_customers(self, df):
65        """Clean customers dataframe"""
66        initial_rows = len(df)
67        
68        # Remove duplicates
69        df = df.drop_duplicates(subset=['customer_id'])
70        
71        # Standardize categorical fields
72        if 'country' in df.columns:
73            df['country'] = df['country'].str.strip().str.title()
74        
75        if 'segment' in df.columns:
76            df['segment'] = df['segment'].str.strip().str.title()
77        
78        # Convert signup date
79        if 'signup_date' in df.columns:
80            df['signup_date'] = pd.to_datetime(df['signup_date'], errors='coerce')
81        
82        return df
83    
84    def print_cleaning_summary(self):
85        """Print cleaning summary"""
86        print("\n" + "=" * 60)
87        print("DATA CLEANING SUMMARY")
88        print("=" * 60)
89        
90        for log in self.cleaning_log:
91            print(f"\n{log['action']}")
92            print(f"  Rows: {log['rows_before']:,} → {log['rows_after']:,}")
93            print(f"  Removed: {log['rows_removed']:,}")
94        
95        total_removed = sum(log['rows_removed'] for log in self.cleaning_log)
96        print(f"\n{'─' * 60}")
97        print(f"TOTAL ROWS REMOVED: {total_removed:,}")
98
99# Usage
100cleaner = DataCleaner()
101# orders_clean = cleaner.clean_orders(orders)
102# customers_clean = cleaner.clean_customers(customers)
103# cleaner.print_cleaning_summary()
104
105# Save cleaned data
106# orders_clean.to_csv('data/processed/orders_clean.csv', index=False)
107# customers_clean.to_csv('data/processed/customers_clean.csv', index=False)

1"""
203_analysis.ipynb
3"""
4
5import pandas as pd
6import numpy as np
7import matplotlib.pyplot as plt
8
9class RevenueAnalyzer:
10    """Revenue analysis module"""
11    
12    def __init__(self, orders_df):
13        self.orders = orders_df
14    
15    def monthly_revenue(self):
16        """Calculate monthly revenue metrics"""
17        monthly = self.orders.groupby('order_month').agg({
18            'order_total': ['sum', 'mean', 'count'],
19            'customer_id': 'nunique'
20        }).round(2)
21        
22        monthly.columns = ['revenue', 'aov', 'orders', 'customers']
23        monthly['revenue_mom'] = monthly['revenue'].pct_change() * 100
24        
25        return monthly
26    
27    def revenue_by_segment(self, customers_df):
28        """Revenue breakdown by customer segment"""
29        merged = self.orders.merge(customers_df[['customer_id', 'segment']], on='customer_id')
30        
31        segment_metrics = merged.groupby('segment').agg({
32            'order_total': ['sum', 'mean', 'count'],
33            'customer_id': 'nunique'
34        }).round(2)
35        
36        segment_metrics.columns = ['revenue', 'aov', 'orders', 'customers']
37        segment_metrics['rev_per_customer'] = segment_metrics['revenue'] / segment_metrics['customers']
38        
39        return segment_metrics.sort_values('revenue', ascending=False)

1class CohortAnalyzer:
2    """Cohort analysis module"""
3    
4    def __init__(self, orders_df):
5        self.orders = orders_df
6    
7    def build_retention_cohorts(self):
8        """Build monthly retention cohorts"""
9        
10        # Get first purchase month for each customer
11        customer_first = self.orders.groupby('customer_id')['order_date'].min().reset_index()
12        customer_first.columns = ['customer_id', 'first_order_date']
13        customer_first['cohort'] = customer_first['first_order_date'].dt.to_period('M')
14        
15        # Merge with orders
16        cohort_df = self.orders.merge(customer_first[['customer_id', 'cohort']], on='customer_id')
17        cohort_df['order_period'] = cohort_df['order_date'].dt.to_period('M')
18        
19        # Calculate period number
20        cohort_df['period_number'] = (
21            cohort_df['order_period'].astype('int64') - 
22            cohort_df['cohort'].astype('int64')
23        )
24        
25        # Build cohort matrix
26        cohort_matrix = cohort_df.groupby(['cohort', 'period_number'])['customer_id'].nunique().unstack()
27        
28        # Calculate retention rates
29        cohort_sizes = cohort_matrix.iloc[:, 0]
30        retention_matrix = cohort_matrix.divide(cohort_sizes, axis=0) * 100
31        
32        return retention_matrix.round(1)
33    
34    def plot_retention_heatmap(self, retention_matrix):
35        """Plot retention heatmap"""
36        
37        fig, ax = plt.subplots(figsize=(12, 8))
38        
39        sns.heatmap(
40            retention_matrix.iloc[:12, :12],  # First 12 cohorts, 12 months
41            annot=True,
42            fmt='.0f',
43            cmap='YlGnBu',
44            ax=ax,
45            vmin=0,
46            vmax=100,
47            cbar_kws={'label': 'Retention %'}
48        )
49        
50        ax.set_title('Customer Retention by Monthly Cohort', fontsize=14, fontweight='bold')
51        ax.set_xlabel('Months Since First Purchase')
52        ax.set_ylabel('Cohort (First Purchase Month)')
53        
54        plt.tight_layout()
55        plt.savefig('reports/figures/retention_heatmap.png', dpi=150)
56        plt.show()

1class RFMAnalyzer:
2    """RFM segmentation module"""
3    
4    def __init__(self, orders_df, analysis_date=None):
5        self.orders = orders_df
6        self.analysis_date = analysis_date or orders_df['order_date'].max() + pd.Timedelta(days=1)
7    
8    def calculate_rfm(self):
9        """Calculate RFM scores"""
10        
11        # Calculate RFM metrics
12        rfm = self.orders.groupby('customer_id').agg({
13            'order_date': lambda x: (self.analysis_date - x.max()).days,  # Recency
14            'order_id': 'count',  # Frequency
15            'order_total': 'sum'  # Monetary
16        })
17        
18        rfm.columns = ['recency', 'frequency', 'monetary']
19        
20        # Score each metric (1-5, 5 is best)
21        rfm['r_score'] = pd.qcut(rfm['recency'], 5, labels=[5, 4, 3, 2, 1])
22        rfm['f_score'] = pd.qcut(rfm['frequency'].rank(method='first'), 5, labels=[1, 2, 3, 4, 5])
23        rfm['m_score'] = pd.qcut(rfm['monetary'].rank(method='first'), 5, labels=[1, 2, 3, 4, 5])
24        
25        # Combine scores
26        rfm['rfm_score'] = (
27            rfm['r_score'].astype(str) + 
28            rfm['f_score'].astype(str) + 
29            rfm['m_score'].astype(str)
30        )
31        
32        return rfm
33    
34    def segment_customers(self, rfm_df):
35        """Assign customer segments based on RFM"""
36        
37        def get_segment(row):
38            r, f, m = int(row['r_score']), int(row['f_score']), int(row['m_score'])
39            
40            if r >= 4 and f >= 4 and m >= 4:
41                return 'Champions'
42            elif r >= 4 and f >= 3:
43                return 'Loyal Customers'
44            elif r >= 4 and f <= 2:
45                return 'Recent Customers'
46            elif r <= 2 and f >= 4:
47                return 'At Risk'
48            elif r <= 2 and f <= 2 and m >= 3:
49                return 'Hibernating'
50            elif r <= 2 and f <= 2:
51                return 'Lost'
52            else:
53                return 'Potential Loyalists'
54        
55        rfm_df['segment'] = rfm_df.apply(get_segment, axis=1)
56        
57        return rfm_df
58    
59    def get_segment_summary(self, rfm_df):
60        """Generate segment summary"""
61        
62        summary = rfm_df.groupby('segment').agg({
63            'recency': 'mean',
64            'frequency': 'mean',
65            'monetary': ['mean', 'sum'],
66            'rfm_score': 'count'
67        }).round(1)
68        
69        summary.columns = ['avg_recency', 'avg_frequency', 'avg_monetary', 'total_revenue', 'customers']
70        summary['pct_customers'] = (summary['customers'] / summary['customers'].sum() * 100).round(1)
71        summary['pct_revenue'] = (summary['total_revenue'] / summary['total_revenue'].sum() * 100).round(1)
72        
73        return summary.sort_values('total_revenue', ascending=False)