Skip to content

README.md

Latest commit

 

History

History
146 lines (96 loc) · 4.85 KB

README.md

File metadata and controls

146 lines (96 loc) · 4.85 KB

Intermediate SQL-Sales Analysis

📌 Overview

This project analyzes customer behavior, retention, and lifetime value for an e‑commerce company.
The goal is to uncover actionable insights that improve customer retention and maximize long‑term revenue growth.

❓ Business Questions

  1. 👥 Customer Segmentation: Who are the most valuable customers, and how should they be managed?
  2. 📊 Cohort Analysis: How do different customer groups generate revenue over time?
  3. 🔄 Retention Analysis: Which customers are at risk of churn, and how can retention be improved?

🔎 Analysis Approach

👥 1. Customer Segmentation

  • Categorized customers based on total Lifetime Value.
  • Assigned customers to High, Medium, Low-value segments.
  • Calculated total revenue contribution.

Query:
Customer_segmentation

Visualization:
Customer Segmentation Chart

💡Business Insights

  1. High-Value Customers
  • Generate nearly two-thirds of revenue despite being the smallest segment.
  • Action: Create a VIP loyalty program, assign account managers, and use churn alerts to protect this group.
  1. Medium-Value Customers
  • Contribute almost one-third of revenue due to scale.
  • Action: Run upsell campaigns, cross-sell related products, and offer bundle discounts to raise their average spend.
  1. Low-Value Customers
  • Nearly equal in count to high-value but negligible in revenue.
  • Action: Reduce acquisition spend, move them to automated support, and focus resources on higher-value segments.

📊 2. Cohort Analysis

  • Tracked revenue and customer counts per cohort.
  • Grouped cohorts by first year of purchase.
  • Analyzed retention at cohort level.

Query:
cohort_analysis

Visualization:
Cohort Analysis Chart

🔍 Findings

  1. Later cohorts are less profitable per customer.
  2. 2016–2019 cohorts balanced volume and value.
  3. 2020 shows volatility likely due to external shocks (Covid-19 Pandemic).
  4. Post-2020 cohorts show more customers but lower revenue per head.

💡Business Insights

  1. Retention Programs
  • Action: Launch subscription bundles, personalized onboarding, and loyalty rewards to lift ARPU.
  1. Replicate Strong Cohorts
  • Action: Study 2016–2019 campaigns and pricing, then reapply those tactics.
  1. Post-2019 Decline
  • Action: Improve customer experience, run targeted upselling, and monitor ARPU monthly.
  1. 2024 Red Flag
  • Action: Stabilize both acquisition and retention with combined marketing and product changes.

🔄 3. Retention Analysis

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

Query:
retention_analysis

Visualization:
Retention Analysis Chart

🔍Key Findings

  • Churn is consistently ~90% across cohorts.
  • Retention peaked at 10.4% in 2022.
  • Scaling customer numbers does not solve churn.

💡Business Insights

  1. Retention Programs
  • Action: Run win-back campaigns, send purchase reminders, and reward repeat buyers.
  1. Replicate 2022 Success
  • Action: Identify what changed in 2022 (marketing, product, pricing) and repeat those strategies.
  1. Measure Beyond Acquisition
  • Action: Track CLV and repeat purchase rate weekly, tie KPIs to team performance.

📌 Strategic Recommendations

  1. 👥 Customer Value Optimization (Segmentation)

    • Build a VIP tier for high-value customers.
    • Create upsell and cross-sell playbooks for medium-value customers.
    • Automate support for low-value customers.

  2. 📊 Cohort Performance Strategy (Cohort Analysis)

    • Standardize onboarding and engagement campaigns for new cohorts.
    • Reapply pricing and product bundles from 2016–2019.
    • Monitor ARPU monthly and adjust campaigns quickly.

  3. 🔄 Retention & Churn Prevention (Retention Analysis)

    • Launch dashboards to flag churn risk.
    • Scale 2022’s successful tactics across cohorts.
    • Tie retention KPIs directly to marketing and product teams.

Together, these steps give the business a clear path: protect value, rebuild cohort strength, and reduce churn.

⚙️ Technical Details

  • Database: PostgreSQL
  • Analysis Tools: SQL (PostgreSQL), DBeaver, pgAdmin
  • Visualizations: Microsoft Copilot

🙏 Closing Remarks

Thank you for reviewing this analysis.
It demonstrates technical skill in SQL and visualization, and the ability to turn findings into practical business actions.