Cohort Analysis with Excel 📊

Dataset

Source: Online Retail Dataset

Description:
Transactional data containing all transactions occurring between December 1, 2010 and December 9, 2011 for a UK-based non-store online retail company.

Methodology

Cohort Analysis - Grouping customers by their first purchase date and tracking their behavior over time.

Key Metrics Analyzed

1. Retention Rate

Percentage of customers who return to make additional purchases in subsequent months after their first transaction.

2. Net Revenue by Cohort

Total revenue generated by each customer cohort over their lifecycle.

3. Cumulative Lifetime Revenue

Running total of revenue generated by each cohort from their first purchase through subsequent periods.

4. Customer Lifetime Value (CLV)

Average revenue generated per customer within each cohort over their entire relationship with the business.

Tools Used

• Microsoft Excel
• Pivot Tables

Key Insights

• Identified customer retention patterns across different acquisition periods
• Measured revenue contribution by cohort age
• Calculated average customer lifetime value by acquisition month
• Discovered seasonal trends in customer retention and spending behavior

Business Applications

• Optimize marketing spend based on high-value cohorts
• Improve customer retention strategies
• Forecast future revenue based on cohort performance
• Identify at-risk customer segments for targeted re-engagement campaigns