SQL - Sales Analysis

Analysis of customer behavior, retention, and lifetime value using the Contoso e-commerce dataset (~100K records, 2015-2024) to identify revenue concentration risks, declining customer quality trends, and systemic churn issues requiring immediate action.

1. Customer Segmentation: Who are our most valuable customers?
2. Cohort Analysis: How do different customer groups generate revenue?
3. Retention Analysis: Which customers haven't purchased recently?

This project is divided into three analytical questions, each aimed at uncovering a different piece of the customer value puzzle.

Segmented customers into Low, Mid, and High-Value groups using quartile-based thresholds (25th and 75th percentiles) of total lifetime value. Calculated revenue contribution, customer count, and average LTV per segment to uncover revenue distribution patterns.

🖥️ Query: 1_customer_segmentation

Customer Segmentation Analysis Bar graph visualizing the customer segmentation by total LTV; Gemini generated this graph from my SQL query results.

📊 Key Findings:

Bottom line: 25% of customers drive 2/3 of revenue — high concentration risk with significant upsell opportunity in the middle tier.

💡 Business Insights

• High-Value (66% revenue): Implement VIP retention program — losing 1 customer = 30x revenue impact vs Low-Value.
• Mid-Value (32% revenue): Target with upgrade campaigns — closing the 8,253ドル LTV gap could potentially double Mid-Value revenue.
• Low-Value (2% revenue): Focus on re-engagement (educational content, not heavy discounts) to activate dormant users.

Analyzed customer acquisition cohorts grouped by first purchase year. Calculated total customers, total revenue, and revenue per customer for each cohort to evaluate whether newer customer groups are becoming more or less valuable over time.

🖥️ Query: 2_cohort_analysis.sql

Cohort Analysis Bar graph visualizing the customer revenue by first purchase year; Gemini generated this graph from my SQL query results.

📊 Key Findings:

Revenue per customer peaked in 2016 at 2,896ドル and has since declined by 32% to 1,972ドル in 2024.

Bottom line: While total revenue grew due to larger cohorts (2018-2019), customer quality is deteriorating. Newer customers generate significantly less value, and acquisition numbers are dropping — a concerning combination.

💡 Business Insights

• Declining LTV: Newer cohorts (2022–2024) consistently underperform older ones — customers are generating less revenue per person despite acquisition efforts.
• Acquisition Drop: New customers dropped 59% from peak (2016: 3,397 → 2024: 1,402), suggesting market saturation or ineffective marketing channels.
• Risk: Combined trend (lower LTV + fewer customers) signals potential revenue decline in the near future if not addressed.

Identified customers at risk of churning (no purchase in last 6 months) and calculated churn vs. active rates for each acquisition cohort. Analyzed whether churn patterns are consistent across all cohorts or vary by acquisition year.

🖥️ Query: 3_retention_analysis.sql

Retention Analysis Bar chart visualizing the distribution of active vs. churned customers by acquisition cohort year; Gemini generated this graph from my SQL query results.

📊 Key Findings:

Churn is alarmingly consistent across all cohorts:

• 90-92% of customers churn within 6 months of their first purchase
• Only 8-10% remain active — this rate hasn't improved from 2015 to 2023
• Newer cohorts (2022-2023) show identical churn patterns to older ones

Bottom line: Churn is systemic, not cohort-specific. Without intervention, future cohorts will follow the same trajectory.

💡 Business Insights

• First 6 months are critical: 90% of customers churn in this window — focus all retention efforts on the onboarding period.
• 67% upside: Increasing retention from 9% to 15% would add ~1,700 active customers (based on 2023 cohort) at zero acquisition cost.
• Root cause investigation needed: Since churn hasn't improved in 9 years, something is fundamentally broken — audit onboarding experience, product value delivery, and post-purchase communication.

• VIP Retention Program: Launch exclusive program for 12,372 High-Value customers (66% of revenue) with dedicated support and early access to new products — losing one High-Value customer = 30x revenue impact of losing a Low-Value customer.
• Mid-Value Upgrade Path: Create personalized upgrade campaigns to close the 8,253ドル LTV gap — moving Mid-Value customers to High-Value tier could potentially double segment revenue from 66ドル.6M to 135ドル.4M.
• Low-Value Activation: Implement educational onboarding sequences and low-cost trial offers instead of heavy discounts to move dormant customers into regular purchasing behavior.

• Re-engage 2022-2024 Cohorts: Target newer cohorts with personalized re-engagement offers — these customers show 32% lower LTV than peak cohorts and need immediate intervention.
• Loyalty Programs: Introduce subscription or loyalty programs to stabilize revenue and increase purchase frequency, especially for cohorts showing declining value.
• Apply Successful Patterns: Analyze buying behavior from high-performing 2016-2018 cohorts and replicate those strategies (marketing channels, product mix, pricing) for newer customer acquisition.

• Onboarding Focus: Since 90% of churn happens in first 6 months, redesign onboarding experience with clear value delivery, welcome sequences, and early-win incentives.
• Win-Back Campaigns: Target high-value churned customers (those with 10ドルk+ LTV who haven't purchased in 6+ months) with personalized reactivation offers — these customers are most cost-effective to recover.
• Early Warning System: Implement automated alerts when customers show churn signals (no purchase in 45 days, decreased engagement) to proactively intervene before they lapse.

SQL | PostgreSQL | CTEs | Window Functions | PERCENTILE_CONT