Analysis of customer behaviour, retention and lifetime value for an e-commerce company to improve customer retention and maximize revenue.
- Customer Segmentation: Who are our most valuable customers?
- Cohort Analysis: How do different customer group generate revenue?
- Retention Analysis: Which customers haven't purchased recently?
1.Customer Segmentation Analysis:
- Categorize 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) generates 32% of revenue ($66.6M)
- low_value segment (25% of customer) 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 promotion, with potential $66.6M ➡ $135.4M revenue opportinuty
- low_value (2% revenue): Design re-engagement campaigns and price-sensitive promotions to increase purchase frequency
- Tracked revenue and customer count per cohort
- Cohort 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 overtime
- 2022-2024 cohorts are consistently performing worst than the earlier cohorts
- 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
💻 Query: 3_retention_analysis.sql
- Identify customers at risk of churning
- Analyzed last purchase pattern
- Calculated customer-specific metrics
📈Visualizatoin:
📊Key Findings:
- Cohort churn stabilizes at ~90% after 2-3 years, indecating a predictable long-term retention pattern
- Retention rate are consistently low (8-10%) across all cohort, suggesting retention issues are systemic rather than specific to certain years.
- Newer cohort (2022-2023) shows similar churn trajectories, signaling that without intervention, future cohort will follow the same pattern.
💡Business Insights
- Strenghten early engagement strategies to target the first 1-2 years with emboarding incentive, loyalty rewards, and personalize offer to improve long-term retention
- Re-engage highh-value churned customers by focusing on targeted win-back campaign rather than broad retention efforts, as reactivating valuable usres may yield high ROI
- Predict & preempt churn risk and use customer-specific warning indecators 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 personalize upgrade paths for mid-value segment ($66.6M ➡ $135.4M opportunity)
- Deign price-sensitive promotion for low-value segment to increase purchase frequency
- Cohort performance strategy: (customer revenue by cohort)
- Target 2022-2024 cohort with personalize re-engagement offers
- Implement loyalty/subscription programs to stabilize revenue fluactuations
- Apply successful strategies from high-spending 2016-1018 cohort to newer customers
- Retention & churn prevention: (customer retention)
- Strenghten first 1-2 years engagement with onboarding incentives and loyal rewards
- Focus on targeted win-back campaign for high-value churn customers
- Implement proactive intervention system for at-risk customers before they lapse
- Database: PostgreSQL
- Analysis: PostgreSQL, DBeaver, PgAdmin
- Visualization: Power BI and Excel