- Customer Segmentation: Who are our most valuable customers?
- Cohort Analysis: How do different customer groups generate revenue?
- Retention Analysis: Which customers haven't purchased recently?
- 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.4M)
- Mid-value segment (50% of customers) contributes 30% 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
- 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:
💾 Key Findings:
- Revenue per customer shows an alarming decreasing trend over time.
- 2022-2024 cohorts are consistently generating less revenue per customer compared to 2019-2021 cohorts.
- 2024 cohort has the lowest revenue per customer, indicating a significant drop in customer value.
- NOTE: Although net revenue is increasing, this is likely due to a larger customer base, which is not reflective of customer value.
💡 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.
- Identified customers at risk of churning
- Analyzed last purchase patterns
- Calculated customer-specific metrics
🖥️ Query: 3_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.
-
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.6M → $135.4M opportunity)
- Design price-sensitive promotions for low-value segment to increase purchase frequency
-
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
-
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
- Database: PostgreSQL
- Analysis Tools: PostgreSQL
- Visualization: ChatGPT