How to Segment New vs. Returning Customers and Calculate Cohort LTV

## Introduction Distinguishing new from returning customers is foundational for any e-commerce analytics framework. It enables precise measurement of acquisition effectiveness and retention health. Cohort LTV (Lifetime Value) bridges this gap by grouping customers based on their first purchase period, allowing you to track how value accumulates over time. This guide provides a step-by-step, evergreen method to segment customers, build cohorts, and compute LTV. No specific year or tool version is required, ensuring long-term relevance. ## Defining New vs. Returning Customers A customer is considered **new** if they made their first-ever purchase during the period of interest. Otherwise, they are **returning**. The key is to identify each customer's initial order date. ### Implementation in Data Assume you have an `orders` table with at least `customer_id`, `order_date`, and `revenue`. First, calculate the first purchase date: ```sql SELECT customer_id, MIN(order_date) AS first_order_date FROM orders GROUP BY customer_id; ``` Join this back to the orders table to tag each order: ```sql WITH first_orders AS ( SELECT customer_id, MIN(order_date) AS first_order_date FROM orders GROUP BY customer_id ) SELECT o.*, CASE WHEN o.order_date = fo.first_order_date THEN 'New' ELSE 'Returning' END AS customer_type FROM orders o JOIN first_orders fo ON o.customer_id = fo.customer_id; ``` This logic works for any time range and can be adapted to define "new" relative to a rolling window (e.g., first purchase in the last 12 months). ## Creating Cohorts A cohort groups customers by their acquisition period (typically the month of first purchase). This allows you to observe behavioral trends independent of calendar time. ### Cohort Assignment Using the first-order date, assign a cohort label such as `YYYY-MM`: ```sql SELECT customer_id, DATE_TRUNC('month', first_order_date) AS cohort_month FROM first_orders; ``` Now every customer belongs to a single cohort. When analyzing repeat purchases, you can join orders using `customer_id` and compute the order's relative month (e.g., month 0, 1, 2, ... since cohort entry). ## Calculating Cohort Metrics To compute LTV, you need cohort size, cumulative revenue, and optionally retention rates over time. The following example uses a 12-month window. ### Step 1: Aggregate Revenue by Cohort and Period ```sql WITH cohort_orders AS ( SELECT co.customer_id, co.cohort_month, o.order_date, o.revenue, (DATE_PART('year', o.order_date) - DATE_PART('year', co.first_order_date)) * 12 + (DATE_PART('month', o.order_date) - DATE_PART('month', co.first_order_date)) AS period FROM orders o JOIN ( SELECT customer_id, MIN(order_date) AS first_order_date, DATE_TRUNC('month', MIN(order_date)) AS cohort_month FROM orders GROUP BY customer_id ) co ON o.customer_id = co.customer_id ) SELECT cohort_month, period, COUNT(DISTINCT customer_id) AS active_customers, SUM(revenue) AS total_revenue FROM cohort_orders GROUP BY 1, 2; ``` ### Step 2: Calculate Cumulative Revenue per Customer For each cohort, divide the running total of revenue by the initial cohort size. ```sql WITH cohort_metrics AS ( -- use the previous query as a CTE ), cohort_size AS ( SELECT cohort_month, COUNT(DISTINCT customer_id) AS cust_count FROM ... GROUP BY 1 ) SELECT cm.cohort_month, cm.period, SUM(cm.total_revenue) OVER (PARTITION BY cm.cohort_month ORDER BY cm.period) AS cum_revenue, cs.cust_count, SUM(cm.total_revenue) OVER (PARTITION BY cm.cohort_month ORDER BY cm.period) / cs.cust_count AS cum_ltv FROM cohort_metrics cm JOIN cohort_size cs ON cm.cohort_month = cs.cohort_month ORDER BY cm.cohort_month, cm.period; ``` This yields a table where each row shows the cumulative LTV per customer for that cohort up to a given period. ## Interpreting New vs. Returning LTV You can extend the analysis to compare the LTV of customers who were first-time buyers in a period vs. those who returned. One approach: - For each cohort, split customers into two groups based on whether the cohort period was their first purchase ever (New) or they had purchased before (Returning). - Re-calculate cumulative revenue per customer for each group. This answers: *Do newly acquired customers generate higher long-term value than those who are reactivated?* Alternatively, you can define cohorts by order sequence number (e.g., all customers making their nth purchase in a given month) to see how LTV evolves with repeat buying. ## Practical Considerations - **Cohort size**: Smaller cohorts may yield noisy LTV. Aggregate cohorts quarterly if monthly data is sparse. - **Projection**: For long-term LTV, fit a retention curve (e.g., exponential decay) to historical cohort data and project forward. - **Customer recognition**: Ensure you have a reliable customer ID across channels and devices to avoid inflating new counts. - **Time zone**: Standardize order dates to a single time zone to avoid boundary issues. ## Conclusion Segmenting new vs. returning customers and computing cohort LTV transforms raw transaction data into actionable retention metrics. By following the SQL templates and conceptual framework above, you can build a robust, timeless analytics layer that drives better marketing investment decisions. Regularly update your models as data accumulates, but the logic remains independent of any particular year or toolset.