Intermediate SQL - Sales Analysis

Overview

Analysis of customer behavior, retention, and lifetime value for an e-commerce company to improve customer retention and maximize 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: customer_segmentation.sql

📈 Visualization:

📊 Key Findings:

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

💡 Business Insights

• High-Value (66% revenue): Offer premium membership program to 12,372 VIP customers, as losing one customer significantly impacts revenue
• Mid-Value (32% revenue): Create upgrade paths through personalized promotions, with potential $66.6M → $135.4M revenue opportunity
• Low-Value (2% revenue): Design re-engagement campaigns and price-sensitive promotions to increase purchase frequency

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: cohort_analysis.sql

📈 Visualization:

📊 Key Findings:

• Revenue per customer shows an alarming decreasing trend over time
• 2022-2024 cohorts are consistently performing worse than earlier cohorts

💡 Business Insights

• Value extracted from customers is decreasing over time and needs further investigation.
• In 2023 we saw a drop in number of customers acquired, which is concerning.
• With both lowering LTV and decreasing customer acquisition, the company is facing a potential revenue decline.

3. Customer Retention

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

🖥️ Query: retention_analysis.sql

📈 Visualization:

📊 Key Findings:

• Cohort churn stabilizes at ~90% after 2-3 years, indicating a predictable long-term retention pattern.
• Retention rates are consistently low (8-10%) across all cohorts, suggesting retention issues are systemic rather than specific to certain years.
• Newer cohorts (2022-2023) show similar churn trajectories, signaling that without intervention, future cohorts will follow the same 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. Invest in High-Value Customers

   • Launch a VIP Loyalty Program for top-tier customers who generate 66% of revenue.
   • Provide exclusive perks (early access, premium support, bonus rewards) to drive repeat purchases and long-term loyalty.

2. Boost Mid-Value Segment Engagement

   • Use personalized promotions and dynamic pricing to convert mid-tier customers into high-value ones.
   • Focus marketing campaigns on increasing average order value and purchase frequency.

3. Revive Low-Value & Churned Customers

   • Deploy targeted re-engagement campaigns (discounts, bundles) for price-sensitive customers.
   • Focus win-back efforts on churned high-value users with tailored offers—this group has the highest ROI potential.

4. Address Declining Cohort Performance

   • Investigate why recent cohorts (2022–2024) underperform—analyze changes in marketing, onboarding, and product experience.
   • Implement customer success and feedback loops early in the customer lifecycle to increase value retention.

5. Proactive Retention & Churn Prevention

   • Develop a churn prediction model using behavioral and transactional indicators.
   • Trigger automated interventions (emails, offers, support outreach) when early warning signs are detected.

Technical Details

• Database: PostgreSQL
• Analysis Tools: PostgreSQL, DBeaver, PGadmin
• Visualization: PowerBI