🔒Открыть пост
и посмотреть медиа
и посмотреть медиа
🚀 SQL Project Series #30: E-Commerce Customer Retention & Cohort Analysis 📊
Perfect project for Data, Product, and CRM Analysts. This one hits both SQL fundamentals + advanced customer analytics.
🎯 Business Objectives
✅ Analyze customer retention
✅ Build monthly customer cohorts
✅ Measure repeat purchases
✅ Calculate customer lifetime value
✅ Identify high-value customers
✅ Analyze customer churn
✅ Compare cohort performance
✅ Measure revenue retention
📂 Step 1: Create Database
CREATE DATABASE cohort_analysis_db;
USE cohort_analysis_db;
📂 Step 2: Create Customers Table
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(100),
signup_date DATE,
city VARCHAR(50)
);
📂 Step 3: Create Orders Table
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
order_amount DECIMAL(10,2),
order_status VARCHAR(20),
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
);
📂 Step 4: Insert Sample Customers
INSERT INTO customers VALUES
(1,'Rahul Sharma','2025-01-05','Mumbai'),
(2,'Priya Verma','2025-01-10','Delhi'),
(3,'Amit Patel','2025-02-15','Pune'),
(4,'Sneha Joshi','2025-02-20','Bangalore'),
(5,'Rohan Gupta','2025-03-05','Hyderabad'),
(6,'Anjali Shah','2025-03-15','Mumbai');
📂 Step 5: Insert Sample Orders
INSERT INTO orders VALUES
(1001,1,'2025-01-10',2500,'Completed'),
(1002,1,'2025-02-15',3200,'Completed'),
(1003,1,'2025-03-20',1800,'Completed'),
(1004,2,'2025-01-20',1500,'Completed'),
(1005,2,'2025-03-05',2800,'Completed'),
(1006,3,'2025-02-20',4200,'Completed'),
(1007,3,'2025-03-25',3500,'Completed'),
(1008,4,'2025-02-25',1800,'Completed'),
(1009,5,'2025-03-10',2200,'Completed'),
(1010,5,'2025-04-15',3100,'Completed'),
(1011,6,'2025-03-20',2700,'Completed');
🧠 SQL Concepts You'll Practice
✔ Date Functions
✔ GROUP BY, Aggregate Functions, JOINs
✔ CTEs, CASE WHEN, Subqueries
✔ Window Functions, LAG(), DENSE_RANK()
✔ COUNT DISTINCT, Cohort Analysis
📊 Business KPIs You Can Build
📈 Monthly Active Customers
📈 New Customers by Month
📈 Repeat Customers, Repeat Purchase Rate
📈 Customer Retention Rate, Churn Rate
📈 Customer Lifetime Value (CLV), AOV
📈 Revenue per Cohort, Cohort Retention Rate
📈 RFM Segmentation, High-Value Customer %
💡 Example Queries
1. Find Customer Cohort
WITH customer_cohort AS (
SELECT
customer_id,
DATE_TRUNC('month', MIN(signup_date)) AS cohort_month
FROM customers
GROUP BY customer_id
)
SELECT * FROM customer_cohort ORDER BY cohort_month;
2. Monthly Customer Activity
SELECT
DATE_TRUNC('month', order_date) AS order_month,
COUNT(DISTINCT customer_id) AS active_customers
FROM orders
WHERE order_status = 'Completed'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY order_month;
3. Repeat Customers
SELECT
customer_id,
COUNT(order_id) AS total_orders
FROM orders
WHERE order_status = 'Completed'
GROUP BY customer_id
HAVING COUNT(order_id) > 1;
4. Customer Lifetime Value
SELECT
customer_id,
SUM(order_amount) AS lifetime_value
FROM orders
WHERE order_status = 'Completed'
GROUP BY customer_id
ORDER BY lifetime_value DESC;
5. Rank Customers by Revenue
WITH customer_revenue AS (
SELECT customer_id, SUM(order_amount) AS revenue
FROM orders WHERE order_status = 'Completed' GROUP BY customer_id
)
SELECT
customer_id,
revenue,
DENSE_RANK() OVER (ORDER BY revenue DESC) AS revenue_rank
FROM customer_revenue;
