One-Time or Loyal? New vs Returning Buyer Cohort Retention Using SQL CTEs

Business Context

LoyaltyLoop is a direct-to-consumer e-commerce platform selling across four product categories — Apparel, Home & Garden, Beauty & Personal Care, and Sports & Outdoors — with approximately 140,000 monthly active buyers and $6.1M in monthly GMV. While acquisition campaigns have been performing well, the VP of Growth has flagged a troubling pattern in the monthly P&L: customer acquisition costs are rising steadily while the revenue contribution per acquired customer is declining quarter-over-quarter. The core hypothesis is that too many new buyers are making exactly one purchase and never returning — and the team has no data to confirm or refute this. The goal is to build a cohort-based view of buyer retention for customers acquired in H1 2024 (January – June 2024), tracking whether each monthly acquisition cohort returns to purchase again in the months following their first order.

The SQL Challenge

The database stores all orders in a single orders table. Your task is to identify each customer's first order month (their acquisition cohort using DATE_TRUNC), then track whether they placed any subsequent orders in each of the following three months. This requires a chain of CTEs: one to find the first order date per customer, one to label each customer as new or returning within any given month, and one to aggregate cohort-level retention counts and rates. Window functions (MIN() OVER PARTITION BY) provide an elegant alternative to a subquery for identifying the first order. The final output shows, for each acquisition cohort, how many buyers were acquired and what percentage returned in Month 1, Month 2, and Month 3 after acquisition.

Your Role and Deliverable

You are the data analyst on LoyaltyLoop's Growth Analytics team. The VP of Growth has asked you to produce a cohort retention query covering the H1 2024 acquisition cohorts. The output should show each monthly cohort, the count of newly acquired buyers in that cohort, and the retention rate (% who returned) in each of the three months following their first purchase. Results should be ordered by cohort month. This table will be the centrepiece of the quarterly growth review and will directly inform whether to invest more in acquisition or retention programmes.