Customer Segmentation: RFM Scoring, Personas & k-Means by Hand

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Turn 24 raw orders into the RFM base table: last order date, days since the last order, and orders and revenue in the last 12 months for each customer.
• Score 20 customers from 1 to 5 on recency, frequency and monetary with data-driven quintiles, the way most RFM tools do it.
• Turn three RFM scores into a cell code, an FM score and a segment name from a 5 × 5 segment map, then size the win-back campaign.
• Size eight RFM segments, work out their share of customers and of revenue, and chart both side by side.
• Standardise recency, frequency and revenue so that no variable dominates the distance, and see how much the distance between two customers changes.
• Run one k-means iteration on the 12 standardised customers: distances in three dimensions, the nearest centroid, new centroids with AVERAGEIF and the cluster sizes.
• Measure how tight the best k = 3 solution is (WCSS), compare k = 1 to 8 and draw the elbow chart that tells you how many segments to keep.
• Profile five segments against the average customer and decide which ones get the autumn mailer, based on the net profit each segment is expected to earn.