Customer segmentation and lifetime value

Wadi Market, the Riyadh-based online retailer, has grown for three years running, but its marketing is still mostly "one email to everybody". The CRM team has a budget for next year's retention programme and wants to stop spending it evenly. Before anyone designs a campaign, they need a clear, reproducible picture of who the customers are in value terms: who buys often and recently, who used to be valuable and has gone quiet, and who bought once and never came back.

You will build that picture from the operational database (customers, orders, order_items, returns). The analysis is fixed at 1 January 2026, the day after the last order in the data. Only orders that were actually paid for count (status paid, shipped or delivered); cancelled, pending and fully returned orders are excluded. Revenue means item value after line discounts, without shipping.

The method is the classic RFM model (Recency, Frequency, Monetary), scored in quintiles with NTILE(5) and explicit tie-breakers so that every analyst gets the same scores. On top of it you will measure historical customer lifetime value (net of refunds), how value builds up in the first year after a customer's first order, and whether customers who joined through a referral (customers.referred_by_customer_id, a self-reference) behave differently. The project ends with a physical table CRM can load into its campaign tool and a one-screen summary for the head of marketing.

The data is fictional and generated; the patterns are realistic but the numbers describe no real business.