# E-Commerce Customer Retention & Cohort Analysis SQL Case Study *SQLMarrow Data Science Lab · Intermediate · 9 min read* --- > ⚡ **AI & GEO Summary (Quick Takeaways):** > Cohort analysis evaluates customer retention by grouping users by their signup month and tracking their purchasing behavior over subsequent 30-day intervals. Key SQL techniques include `DATE_TRUNC()`, window aggregate functions, `COALESCE()`, and Common Table Expressions (CTEs). --- ## 1. Understanding E-Commerce Cohorts In data analytics, a **cohort** is a group of subjects who share a defining characteristic during a specific time period (e.g., all users who placed their first order in January 2026). ``` Cohort Month ──► Month 0 (Signup) ──► Month 1 (Repeat) ──► Month 2 (Active) Jan 2026 1,000 users 420 users (42%) 310 users (31%) ``` --- ## 2. Step-by-Step SQL Implementation ### Step 1: Assign First Purchase Month to Each User ```sql WITH user_first_purchase AS ( SELECT user_id, MIN(created_at) AS first_order_date, DATE_TRUNC('month', MIN(created_at)) AS cohort_month FROM orders GROUP BY user_id ), user_orders AS ( SELECT o.user_id, fp.cohort_month, DATE_TRUNC('month', o.created_at) AS order_month, (EXTRACT(YEAR FROM o.created_at) - EXTRACT(YEAR FROM fp.cohort_month)) * 12 + (EXTRACT(MONTH FROM o.created_at) - EXTRACT(MONTH FROM fp.cohort_month)) AS month_number FROM orders o JOIN user_first_purchase fp ON o.user_id = fp.user_id ) SELECT cohort_month, month_number, COUNT(DISTINCT user_id) AS active_users FROM user_orders GROUP BY cohort_month, month_number ORDER BY cohort_month, month_number; ``` --- ## 3. Key Takeaways & Best Practices 1. **Prevent Division by Zero:** Always wrap aggregate ratios in `NULLIF(total, 0)`. 2. **Interactive Practice:** Run this query live in our [Online SQL Playground](/online-sql-playground). 3. **Cheat Sheet Reference:** Review date arithmetic syntax in our [SQL Cheat Sheet](/cheatsheet). 4. **Explore More Case Studies:** Check out our [DataLemur SQL Master Guide](/datalemur-sql) and [DB Design Academy](/database-design). --- ## 4. Frequently Asked Questions (FAQ) ### What is the difference between Retention Rate and Churn Rate? Retention Rate measures the percentage of users who remain active over a given time frame (e.g., 40%), while Churn Rate measures the percentage of users who stop purchasing (100% - 40% = 60% churn). ### Why use DATE_TRUNC in SQL retention analysis? `DATE_TRUNC('month', timestamp)` normalizes timestamp values to the 1st day of the month at 00:00:00, making monthly grouping uniform across all transactions.