groupby, merge and an RFM customer segmentation

#Why it matters

"Customer segmentation" is one of the most common take-home assignments for analyst and data scientist roles in e-commerce, fintech and retail. RFM (recency, frequency, monetary value) is the classic approach because it needs only transactions and is easy to explain to marketing teams. It exercises the pandas you use every day: groupby, agg, merge and binning.

#The concept

• groupby(keys).agg(name=(column, function)) collapses rows into one row per group with named results.

• merge(left, right, on=..., how=...) joins two DataFrames like SQL. Use validate="many_to_one" to make pandas check your assumption about keys.

• pd.qcut(series, q=4, labels=...) splits values into equal-sized groups by rank.

• pivot_table and melt reshape between wide and long formats.

RFM per customer:

• Recency: days since the last purchase (lower is better)

• Frequency: number of distinct invoices

• Monetary: total revenue

#Worked example: RFM on Online Retail II

Continuing from clean in Load, inspect and filter a real sales file:

snapshot = pd.Timestamp("2011-12-10")          # the day after the last invoice

customers = clean.groupby("customer_id").agg(
    last_purchase=("InvoiceDate", "max"),
    frequency=("Invoice", "nunique"),
    monetary=("revenue", "sum"),
    country=("Country", "first"),
)
customers["recency"] = (snapshot - customers["last_purchase"]).dt.days

# Score 1 to 4 (4 is best). Recency is reversed: fewer days is better.
customers["r"] = pd.qcut(customers["recency"], 4, labels=[4, 3, 2, 1]).astype(int)
customers["f"] = pd.qcut(customers["frequency"].rank(method="first"), 4, labels=[1, 2, 3, 4]).astype(int)
customers["m"] = pd.qcut(customers["monetary"], 4, labels=[1, 2, 3, 4]).astype(int)

def segment(row):
    if row.r >= 3 and row.f >= 3:
        return "Champions"
    if row.r >= 3:
        return "Recent"
    if row.f >= 3:
        return "At risk"
    return "Hibernating"

customers["segment"] = customers.apply(segment, axis=1)
summary = customers.groupby("segment").agg(
    customers=("recency", "size"), median_recency=("recency", "median"),
    median_orders=("frequency", "median"), revenue=("monetary", "sum"))
summary["revenue_share"] = summary["revenue"] / summary["revenue"].sum()
print(summary.sort_values("revenue", ascending=False))

A third of customers produce three quarters of revenue. The 650 "At risk" customers used to buy often but have been quiet for about three months; they are the obvious target for a win-back campaign.

Facts you should see in the data along the way:

• 4,339 customers; 34% bought only once.

• The top 10% of customers bring 61% of revenue. The largest, customer 14646 (Netherlands), placed 74 orders worth about 280,206.

• frequency has many ties at 1, which is why it is ranked with method="first" before qcut; otherwise qcut fails on duplicate bin edges.

Then join segments back to lines to see what each segment buys:

lines = clean.merge(customers[["segment"]], left_on="customer_id", right_index=True,
                    how="left", validate="many_to_one")
top = (lines.groupby(["segment", "Description"])["revenue"].sum()
            .groupby(level=0, group_keys=False).nlargest(3))
print(top)

The top "At risk" item list includes a line called "Manual": a manual adjustment code, not a product. Exclude the special stock codes (POST, DOT, M, D, S, AMAZONFEE) before any product analysis.

#Common mistakes

• Counting lines instead of invoices for frequency. Use nunique on the invoice.

• Choosing the snapshot date as today. The data ended in 2011. Recency must be measured from the end of the data.

• Scoring without looking at distributions. Quartiles on heavily skewed revenue put very different customers in the same bin. Check describe() first.

• Merges that multiply rows. validate="many_to_one" turns a silent bug into an error.

#Interview angle

Expect "how would you segment customers?". Explain RFM, why you chose quartiles, what each segment means for marketing, and one limitation (it ignores margins and product mix). Offer a next step such as clustering with k-means on scaled RFM features.

#Practice task

1. Replace the 4-segment rule with the 11 standard RFM segments from the further reading, and compare revenue shares.

2. For "At risk" customers, list their most bought products. Draft a one-line email offer.

3. Rebuild customers without groupby().agg, using pivot_table, and check both give identical results.

#Further reading

• pandas: group by, split-apply-combine

• pandas: merge, join and concatenate

• Wikipedia: RFM (market research)