Analysis of customer behaviour, retention and lifetime value for an e-commerce company to improve customer retention and maximise revenue.
Dataset: This analysis uses the Contoso BI Demo dataset from Microsoft.
- Lifetime Value (LTV): The total revenue a customer generates throughout their entire relationship with the business
- Retention: The percentage of customers who continue to make purchases over time
- Churn: The percentage of customers who stop making purchases and become inactive
- 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?
SQL Query: Create_View
- Aggregated sales and customer data into revenue metrics
- Calculated the first purchase dates for cohort analysis
- Created view combining transactions and customer details
- Categorised customers based on total lifetime value (LTV)
- Assigned customers to High, Mid, and Low-value segments
- Calculated key metrics like total revenue
SQL Query: Customer_Segmentation
Visualisation:
Pie chart showing the proportion of revenue generated by each customer segment
Key Findings:
- The high-value segment, representing 25% of the customer base (12,372 customers), generates 66% of total revenue ($135.4M)
- The mid-value segment comprises 50% of customers and contributes 32% of revenue ($66.6M)
- The low-value segment accounts for the remaining 25% of customers but only 2% of revenue ($4.3M)
- This distribution demonstrates a significant concentration of revenue within a relatively small portion of the customer base
Business Insights:
- High-Value customers require dedicated attention: These 12,372 customers represent the foundation of the business's revenue. Consider developing premium membership programmes with enhanced benefits, personalised service, and exclusive offerings to strengthen these valuable relationships
- Mid-Value customers present growth opportunities: This segment has demonstrated consistent purchasing behaviour and may respond well to targeted engagement strategies. Personalised promotions, loyalty rewards, and curated product recommendations could help elevate their spending patterns
- Low-Value customers benefit from efficient engagement: Focus on cost-effective, automated marketing approaches such as email campaigns and targeted promotions to encourage increased purchase frequency whilst maintaining efficient resource allocation
- Tracked revenue and customer count per cohort
- Cohorts were grouped by year of first purchase
- Analysed customer revenue at a cohort level to understand spending patterns over time
SQL Query: Cohort_Analysis
Visualisation:
Bar graph showing average customer revenue by cohort, adjusted for time in market using the first purchase date
Bar graph of monthly net revenue and total customers, with 3-month moving average trend lines showing how both metrics change over time
Key Findings:
- Average customer revenue has decreased from approximately $2,800 in earlier cohorts (2016-2018) to approximately $1,970 in the 2024 cohort, indicating evolving customer purchasing patterns
- Revenue and customer acquisition reached their highest levels during 2022-2023, with a noticeable decline beginning in 2024
- The data shows notable fluctuations, including significant dips in 2020 and 2024, suggesting sensitivity to external factors or seasonal patterns
- This trend analysis reveals opportunities to understand what drove success in peak periods and address recent challenges
Business Insights:
- Recent cohorts would benefit from enhanced early engagement: Customers acquired in 2022-2024 show different purchasing patterns compared to earlier cohorts. Implementing comprehensive onboarding programmes, educational content, and early purchase incentives during the first 90 days could help establish stronger customer relationships
- Revenue stability can be improved through recurring models: The fluctuations observed suggest an opportunity to introduce subscription services, membership programmes, or bundled offerings that provide more predictable revenue streams whilst adding customer value
- Learning from successful periods: The 2016-2018 cohorts demonstrated strong performance. Analysing the factors that contributed to their success—such as product offerings, pricing strategies, or market conditions—can provide valuable insights for engaging current and future customers
- Identified customers at risk of churning
- Analysed last purchase patterns to determine customer status
- Calculated customer-specific metrics to track retention over time
SQL Query: Customer_retention
Visualisation:
Stacked bar plot showing the percentage of retained versus churned customers by their cohort year
Key Findings:
- Customer churn stabilises at approximately 90% after 2-3 years across all cohorts, representing a consistent pattern in customer lifecycle behaviour
- Retention rates remain steady at 8-10% across different cohort years, suggesting this is a systemic pattern rather than year-specific
- Newer cohorts (2022-2023) are following similar retention trajectories as earlier cohorts, indicating that current patterns are likely to continue without strategic intervention
- Understanding these patterns provides an opportunity to develop targeted retention strategies at key points in the customer journey
Business Insights:
- Early customer experience is critical: The first 1-2 years represent a crucial period in the customer relationship. Enhancing the onboarding experience with welcome programmes, educational resources, loyalty incentives, and milestone recognition can help build stronger, longer-lasting customer relationships
- Re-engagement strategies for valuable customers: Customers who previously demonstrated high purchase value represent significant opportunities for reactivation. Targeted win-back campaigns with personalised offers and exclusive incentives may prove more effective than broad-based retention efforts
- Proactive customer care approach: Developing early warning indicators based on purchase patterns and engagement metrics allows for timely intervention. When customers show signs of decreased engagement, proactive outreach with personalised offers or satisfaction surveys can help address concerns before customers become inactive
Recommended Actions:
- Develop a comprehensive VIP programme for the 12,372 high-value customers, offering enhanced benefits, personalised service, and exclusive access to new products or services
- Create automated customer journey pathways for mid-value customers, incorporating personalised recommendations and reward-based milestones to encourage increased engagement
- Implement efficient, automated marketing campaigns for the low-value segment, focusing on regular touchpoints and promotional offers to increase purchase frequency
Potential Impact: These initiatives could help protect revenue from high-value customers whilst providing growth pathways for mid and low-value segments, with an estimated opportunity to increase mid-value segment contribution
Recommended Actions:
- Design an enhanced first-90-days customer experience programme, including welcome communications, product education, and early purchase incentives to establish positive engagement patterns
- Explore subscription or membership models that provide ongoing value to customers whilst creating more predictable revenue streams
- Conduct detailed analysis of the high-performing 2016-2018 cohorts to identify successful strategies and practices that can be adapted for current customer acquisition and engagement
Potential Impact: Improving early customer experience and implementing recurring revenue models could help stabilise monthly revenue patterns and increase customer lifetime value for newer cohorts
Recommended Actions:
- Develop predictive analytics to identify customers showing early signs of disengagement, enabling proactive intervention through personalised outreach and targeted offers
- Create specialised win-back campaigns focused on previously high-value customers, incorporating personalised messaging and compelling reactivation incentives
- Establish regular customer feedback mechanisms during the critical first two years, allowing the business to address concerns and strengthen relationships proactively
Potential Impact: Improving retention rates, even modestly, could result in significant revenue protection and reduced customer acquisition costs, as retaining existing customers is typically more cost-effective than acquiring new ones
- Database: PostgreSQL
- Analysis Tools: PostgreSQL
- Visualisation: Python, Jupyter Notebook