Skip to content

README.md

Latest commit

 

History

History
118 lines (77 loc) · 5.69 KB

README.md

File metadata and controls

118 lines (77 loc) · 5.69 KB
Raw

SQL - Sales Analysis

Overview

As part of my data analytics projects, I conducted a comprehensive analysis of customer behavior, retention, and lifetime value (CLV) for an e-commerce company. The goal was to identify actionable insights to improve customer retention and maximize long-term revenue.

Business Questions

  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?

Analysis Approach

1. Customer Segmentation Analysis

  • Categorized customers based on total lifetime value (LTV)
  • Assigned customers to High, Mid, and Low-value segments
  • Calculated key metrics: total revenue

🖥️ Query: 1_customer_segmentation.sql

📈 Visualization:

📊 Key Findings:

  • High-value segment (25% of customers) drives 66% of revenue ($135.6M)
  • Mid-value segment (50% of customers) generates 32% of revenue ($66.4M)
  • Low-value segment (25% of customers) accounts for 2% of revenue ($4.3M)

💡 Business Insights

1_High-Value Segment (66% of Revenue)

• Target Group: 12,372 VIP customers

• Strategy: Launch a premium membership or loyalty program

• Rationale: These customers drive the majority of revenue; even minor churn in this segment has a significant financial impact

2_Mid-Value Segment (32% of Revenue)

• Opportunity: Increase lifetime value through personalized cross-sell and up-sell campaigns

• Potential Impact: Revenue growth opportunity from $66.6M → $135.4M with successful conversion strategies

3_Low-Value Segment (2% of Revenue)

• Approach: Implement re-engagement campaigns, use price-sensitive offers, and reduce friction in repeat purchases

• Goal: Increase purchase frequency and encourage movement to higher-value tiers

2. Cohort Analysis

  • Tracked revenue and customer count per cohorts
  • Cohorts were grouped by year of first purchase
  • Analyzed customer retention at a cohort level

🖥️ Query: 2_cohort_analysis.sql

📈 Visualization:

Cohort Analysis

📊 Key Findings:

  • Revenue per customer shows an alarming decreasing trend over time
    • Cohorts from 2022–2024 are consistently underperforming compared to earlier cohorts in terms of retention and customer value.
    • NOTE: While net revenue is increasing, this growth is primarily driven by a larger customer base, not by improvements in customer retention or value.

💡 Business Insights

  • Value extracted from customers is decreasing over time and needs further investigation.
  • A notable decline in customer acquisition was observed in 2023, raising concerns about top-of-funnel performance and marketing effectiveness.
  • With both lowering LTV and decreasing customer acquisition, the company is facing a potential revenue decline.

3. Customer Retention

🖥️ Query: 3_retention_analysis.sql

  • Identified customers at risk of churning
  • Analyzed last purchase patterns
  • Calculated customer-specific metrics

📈 Visualization:

Customer Churn by Cohort Year

📊 Key Findings:

• Churn Stabilization: Cohort churn stabilizes at approximately 90% after 2–3 years, revealing a predictable long-term retention pattern. Most customers disengage early in their lifecycle, emphasizing the need for strong early engagement strategies.

• Consistently Low Retention: Retention rates consistently hover between 8–10% across all cohorts, indicating a systemic retention challenge rather than issues isolated to specific years or campaigns.

• Repetitive Churn Patterns in Recent Cohorts: Recent cohorts (2022–2023) are following similar churn trajectories as previous ones, signaling that without strategic interventions, future cohorts are likely to repeat the same low-retention pattern.

💡 Business Insights:

  • Strengthen early engagement strategies to target the first 1-2 years with onboarding incentives, loyalty rewards, and personalized offers to improve long-term retention.
  • Re-engage high-value churned customers by focusing on targeted win-back campaigns rather than broad retention efforts, as reactivating valuable users may yield higher ROI.
  • Predict & preempt churn risk and use customer-specific warning indicators to proactively intervene with at-risk users before they lapse.

Strategic Recommendations

  1. Customer Value Optimization (Customer Segmentation)

    • Launch VIP program for 12,372 high-value customers (66% revenue)
    • Create personalized upgrade paths for mid-value segment ($66.4M → $135.6M opportunity)
    • Design price-sensitive promotions for low-value segment to increase purchase frequency

  2. Cohort Performance Strategy (Customer Revenue by Cohort)

    • Target 2022-2024 cohorts with personalized re-engagement offers
    • Implement loyalty/subscription programs to stabilize revenue fluctuations
    • Apply successful strategies from high-spending 2016-2018 cohorts to newer customers

  3. Retention & Churn Prevention (Customer Retention)

    • Strengthen first 1-2 year engagement with onboarding incentives and loyalty rewards
    • Focus on targeted win-back campaigns for high-value churned customers
    • Implement proactive intervention system for at-risk customers before they lapse

Technical Details

  • Database: PostgreSQL
  • Analysis Tools: PostgreSQL, DBeaver, PGadmin
  • Visualization: Power BI