Skip to content
Tutorials Center
My learning

Topics

Lesson 58 of 64 ยท Projects

Project: Customer Segments with RFM

Build an RFM customer segmentation in pandas: merge orders and customers, compute recency, frequency and monetary value, score with qcut and label segments.

Every shop has a few customers who buy often, spend a lot and bought something last week, and many more who bought once and never came back. Treating them all the same wastes money: the first group deserves a thank-you, the second a gentle reminder or nothing at all. RFM analysis is the classic way to tell them apart using only the order history you already have.

RFM stands for three numbers per customer: Recency (how many days since the last order), Frequency (how many orders) and Monetary value (how much they spent). In this project you will turn two raw tables, customers and orders, into a table with one row per customer, score each measure from 1 to 5, label every customer with a segment such as "Champions" or "At risk", and finish with a summary table and a chart that a marketing team could act on.

You will use merge, groupby with named aggregation, binning with qcut, np.select and plotting.

flowchart LR
    A["orders<br/>(one row per order)"] --> C["groupby customer_id"]
    B["customers<br/>(one row per customer)"] --> D["merge: add country"]
    C --> E["R, F, M features"]
    E --> F["qcut scores 1-5"]
    F --> G["segment labels"]
    G --> D
    D --> H["summary table and chart"]

The pipeline: collapse orders to one row per customer, score each measure, label segments, then summarize.

Step 1: the data and a sanity check

Real projects start with a database export. We will generate a realistic stand-in with a fixed seed so the results never change: 300 customers and 2,000 orders placed during 2025. Some customers are far more active than others, which is what makes segmentation interesting. Before analyzing anything, check the tables: sizes, duplicate ids, orders that point to unknown customers, and what a merge does to the row count.

Python Step 1: build and check the tables

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Two facts matter here. First, only 271 of the 300 customers ever ordered, so 29 customers have no RFM values at all. They are a segment of their own ("never bought") and belong in a different campaign, so we leave them out of the scoring. Second, validate="many_to_one" makes the merge raise an error if customers ever contains a duplicated id, which would silently multiply order rows and inflate revenue. A cheap check like this is worth adding to every merge.

Step 2: compute recency, frequency and monetary value

RFM needs a reference date. Recency is "days since the last order as of some day", and using today's date would make the numbers drift every time you run the notebook. The standard choice is a snapshot date: the day after the newest order in the data.

The three features come from one groupby with named aggregation: the latest order date, the number of orders and the sum of amounts. Recency is then the snapshot minus the last order date, converted to whole days with the .dt accessor.

Python Step 2: one row per customer

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Look at the describe() output. The median customer ordered 5 times, but the busiest ordered 34, and the biggest spender spent about seven times the median. These long right tails are exactly why we do not use raw numbers directly: a customer with 34 orders would dominate any average. Scores based on quantiles are robust to such outliers.

Step 3: score each measure from 1 to 5

pd.qcut cuts a column into groups with (roughly) equal numbers of customers, so a score of 5 always means "top 20%". There is one twist for recency: a low number of days is good, so we reverse the labels ([5, 4, 3, 2, 1]). For frequency, many customers share the same order count, and qcut needs distinct bin edges. Ranking first with method="first" breaks ties by row order and gives every customer a unique rank to cut.

Python Step 3: quintile scores

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The "customers per score" table shows each score holds about 54 or 55 customers, as intended. The last table reveals the price of ranking ties: customers with exactly 4 orders can land in score 2 or 3, because ties are split by their position in the table. If you need identical behavior for identical customers, choose business thresholds with pd.cut instead (for example 1, 2 to 3, 4 to 6, 7 to 12, 13 or more orders). Both approaches are legitimate; what matters is that you know which one you picked.

Scores have a different meaning than the raw numbers. A recency score of 5 does not mean "5 days", it means "among the 20% most recent buyers". Keep both the raw features and the scores in the table so you can always explain a label.

Step 4: label segments, summarize and plot

Three scores give 125 combinations, far too many to act on. A few readable rules over R and F (monetary value tends to follow frequency) reduce them to six segments. np.select takes a list of conditions and a list of labels and returns the label of the first condition that is true for each row, so order the rules from most specific to most general. Rows that match nothing get the default.

Then comes the part the business cares about: a summary per segment with its size, typical recency and frequency, and its share of customers versus share of revenue. A bar chart puts those two shares side by side. We also merge country back from the customers table, a good use of the merge you checked in step 1.

Python Step 4: segments, summary and chart

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The result tells a clear story. "Champions" are about a quarter of the customers but bring roughly half of the revenue, while "Hibernating" customers are a similar head count and contribute under 7%. A marketing plan almost writes itself: protect and reward the Champions, send "we miss you" offers to the At risk group (they used to buy often but have gone quiet), and nurture the New or promising customers toward a second and third order.

Taking it further

  • Keep a history. Run the same code with an earlier snapshot date and compare who moved from Loyal to At risk. Moving between segments is more actionable than a single picture.
  • Use the signup date. Customers who signed up recently cannot have a long history, so "New or promising" could be defined with signup instead of low frequency.
  • Check the thresholds. Change the rules and see how the segment sizes move. Segments are a tool for decisions, not a law of nature.
  • Automate it. Wrap steps 2 to 4 in functions that take and return DataFrames, as in Building Data Pipelines.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why is the recency score labelled in reverse order, [5, 4, 3, 2, 1]?

    Recency counts days since the last order, so smaller is better. Reversing the labels makes a score of 5 mean 'bought very recently', the same direction as frequency and monetary.
  2. 2. What does validate="many_to_one" do in orders.merge(customers, on="customer_id", validate="many_to_one")?

    Many orders may share one customer, but each customer must appear once in the customers table. If it does not, the merge would repeat order rows, and validate stops that with an error.
  3. 3. Why does the code call rank(method="first") before qcut on frequency?

    qcut places edges at quantiles. With heavy ties the edges can repeat, which raises an error or gives uneven groups. Ranking with method='first' breaks ties by row order so every value is unique.
  4. 4. In np.select, what happens when two conditions are true for the same customer?

    np.select goes through the conditions in order and uses the first one that is true. That is why the most specific rules come first and the default catches everything else.

Summary

  • RFM describes each customer with recency, frequency and monetary value, all computed from the order table with one groupby.
  • Use a fixed snapshot date so recency does not change when you re-run the analysis.
  • pd.qcut turns long-tailed numbers into robust 1 to 5 scores; reverse the labels when a low value is good, and rank first when there are many ties.
  • np.select turns score combinations into readable segment labels; order the rules from specific to general.
  • Check merges with validate= and row counts, and report a segment's share of customers next to its share of revenue.

Next, use a pivot table and correlations to recommend movies in Project: Ratings and Recommendations.