Customer Segmentation with RFM Analysis

Introduction

30 Second Summary

Every time you get a "We miss you!" email from a brand you haven't bought from in months, that's customer segmentation at work. A data team somewhere scored your purchase history and decided you were "at risk" of leaving.

In this project, you will build a complete customer segmentation pipeline that transforms over one million real e-commerce transactions into five actionable marketing segments using SQL analytics, RFM Analysis, and K-Means Clustering.

What You'll Build

A portfolio-ready notebook you can hand to a hiring manager that starts with messy transaction data, runs analytical SQL queries on a SQLite database, and ends with a clear visualization of five customer segments, each mapped to a specific marketing action and revenue impact estimate.

By the end of this project, you'll have:

• A working SQL analytics layer where you can query a database to answer business questions like "which countries drive the most revenue?" and "who are the top 10 customers by spend?".
• A clustering pipeline that labels every customer as a Champion, Loyal, Promising, At Risk, or Lost, with concrete marketing recommendations for each group.
• Clear visualizations proving that a small percentage of customers drive most revenue (the Pareto effect), with exact percentages to back it up.
• Secret Mission: Add a Customer Lifetime Value projection to estimate the 12-month revenue potential of each segment.

Are there any prerequisites?

You should be comfortable with Python and pandas. Familiarity with matplotlib and seaborn for plotting is helpful, along with a basic understanding of SQL syntax.

Everything runs in Google Colab in your browser. No local installs are needed.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters for your portfolio.

Set Up and Load the Data

Every customer segmentation project starts the same way: you need real transaction data before you can find patterns in it.

In this step, you will load over one million real e-commerce transactions from a UK retailer into a Google Colab notebook and clean the data so it is ready for analysis.

In this step, get ready to:

• Create a Colab notebook and import all required libraries.
• Fetch the UCI Online Retail II dataset (1M+ transactions).
• Clean the raw data by removing nulls, cancellations, and invalid rows.

Create your notebook and install libraries

Google Colab gives you a free Python notebook in the browser with most data science libraries pre-installed. You only need to install one extra package: ucimlrepo, which fetches datasets from the UCI Machine Learning Repository.

• Open colab.research.google.com in your browser.
• Click New notebook to create a blank notebook.
• Rename the notebook to Customer Segmentation Pipeline by clicking the title at the top left.

Each code block you add in this project goes into a new Colab cell. Paste the following into the first cell to install ucimlrepo and import your libraries:

# Cell 1: Setup and Imports
!pip install -q ucimlrepo

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import sqlite3
from sklearn.preprocessing import StandardScaler
from sklearn.cluster import KMeans
from ucimlrepo import fetch_ucirepo

sns.set_style('whitegrid')
plt.rcParams['figure.figsize'] = (10, 6)

What does this code do?

The !pip install -q line installs the ucimlrepo package quietly (the -q flag suppresses most output).

The imports bring in your core toolkit: pandas for data manipulation, numpy for math, matplotlib and seaborn for visualization, sqlite3 for SQL queries, and scikit-learn for clustering.

The last two lines set a clean visual style and a default chart size for all plots in the notebook.

• Run the cell by pressing Shift+Enter.
• You should see a brief install message, then nothing else. No errors means all imports succeeded.

Seeing a ModuleNotFoundError?

If you see an error mentioning ucimlrepo, make sure the !pip install -q ucimlrepo line is in the same cell as the imports and above them.

If you see an error about sklearn, try restarting the runtime from Runtime > Restart runtime and running the cell again.

Help me fix my import errors in Google Colab

Fetch the Online Retail II dataset

The UCI Online Retail II dataset contains real transactions from a UK-based online retailer between December 2009 and December 2011. It has over one million rows of messy, real-world purchase data.

• Create a new code cell below your first one by clicking the + Code button.
• Paste the following code to fetch the dataset and inspect its structure:

# Cell 2: Load Data
dataset = fetch_ucirepo(id=502)
df = pd.concat([dataset.data.features, dataset.data.targets], axis=1)

print(f"Dataset shape: {df.shape}")
print(f"\nColumn names: {df.columns.tolist()}")
print(f"\nMissing values:\n{df.isnull().sum()}")
df.head()

What does this code do?

The fetch_ucirepo(id=502) call downloads the Online Retail II dataset directly from the UCI Machine Learning Repository.

The dataset splits its columns into features and targets, so pd.concat combines them into a single DataFrame for easier analysis. The print statements reveal the shape, column names, and where data is missing.

Before you run this cell, predict: how many rows do you think a real retailer generates over two years of online sales?

• Run the cell with Shift+Enter. This may take 10-20 seconds while the dataset downloads.

You should see Dataset shape: (1067371, 8) confirming 1,067,371 rows and 8 columns: Invoice, StockCode, Description, Quantity, InvoiceDate, Price, Customer ID, and Country.

Why are there so many missing values?

Notice that Customer ID has roughly 25% null values. These are transactions where no customer account was recorded (e.g., guest checkouts). You cannot segment customers you cannot identify, so these rows will be removed in the next substep.

Dataset fetch taking too long or failing?

The download depends on the UCI repository being reachable. If the cell hangs for over 60 seconds or throws a connection error, try running it again. Colab's network can occasionally be slow on initial requests.

Help me troubleshoot the UCI dataset download

Clean the raw transaction data

Real data is never ready to use straight out of the box. This dataset has missing customer IDs, cancelled orders, and rows with impossible negative quantities or prices.

• Create a new code cell and paste the following data cleaning logic:

# Cell 3: Data Cleaning
print(f"Original shape: {df.shape}")

# Drop rows without Customer ID
df = df.dropna(subset=['Customer ID'])

# Convert Invoice to string for filtering
df['Invoice'] = df['Invoice'].astype(str)

# Remove cancelled orders (Invoice starts with 'C')
df = df[~df['Invoice'].str.startswith('C')]

# Remove non-positive Quantity and Price
df = df[(df['Quantity'] > 0) & (df['Price'] > 0)]

# Create TotalPrice
df['TotalPrice'] = df['Quantity'] * df['Price']

# Parse InvoiceDate
df['InvoiceDate'] = pd.to_datetime(df['InvoiceDate'])

print(f"Cleaned shape: {df.shape}")
print(f"Null check:\n{df[['Customer ID', 'Invoice', 'TotalPrice']].isnull().sum()}")

What does each cleaning step do?

• dropna(subset=['Customer ID']) removes rows where no customer is identified. You cannot segment anonymous buyers.
• Filtering out invoices starting with C removes cancelled orders that would skew spending totals.
• Removing non-positive Quantity and Price eliminates refund adjustments and data entry errors.
• TotalPrice (Quantity times Price) gives you the actual revenue per line item for monetary analysis.
• Parsing InvoiceDate to datetime enables time-based calculations like recency.

• Run the cell with Shift+Enter.

You should see the original shape printed first, then a smaller cleaned shape (roughly 400,000-420,000 rows remaining). The null check should show 0 nulls across Customer ID, Invoice, and TotalPrice.

Still seeing nulls in the output?

Make sure you ran Cell 2 before Cell 3. If you ran Cell 3 first, df does not exist yet. Go back to Cell 2, run it, then re-run Cell 3.

Also check that you did not accidentally run Cell 3 twice. Running it a second time on an already-cleaned DataFrame can cause unexpected results since the filters have already been applied.

Help me debug my data cleaning output

✔️ Awesome, I've got everything!

Great. Double check you have saved your notebook (Ctrl+S or Cmd+S) before moving on.

ⓧ I'd like to double check the full code

Scroll up and confirm you have these three cells in order, each producing output when run:

• Cell 1: Setup and Imports (installs ucimlrepo, imports all libraries, sets plot style).
• Cell 2: Load Data (fetches dataset id=502, prints shape showing 1,067,371 rows, displays df.head()).
• Cell 3: Data Cleaning (drops nulls, removes cancellations and invalid rows, creates TotalPrice, prints cleaned shape with 0 nulls).

If any cell is missing or out of order, add it now and re-run from Cell 1 downward.

Your data is loaded and cleaned. Next up, you will load this data into a SQL database and write analytical queries to uncover revenue patterns before building the segmentation model.

Run SQL Analytics on the Transaction Data

You have a clean DataFrame with hundreds of thousands of valid transactions. But raw rows don't answer business questions like "which countries generate the most revenue?" or "who are our best customers?"

In this step, you'll load the data into a SQLite database and write SQL queries that surface real marketing insights. This proves SQL fluency and gives you context for the segmentation model you'll build next.

In this step, get ready to:

• Load your cleaned DataFrame into a SQLite in-memory database.
• Write SQL queries that reveal revenue by country, top customers, and monthly trends.
• Summarize the business context that frames the segmentation problem.

Load the transaction data into SQLite

Python's sqlite3 module lets you create an in-memory database with zero setup. This is perfect for demonstrating SQL skills in a portfolio notebook without needing a cloud database.

• Create a new code cell in your notebook. Load the cleaned DataFrame into SQLite by pasting this code and running the cell:

# Cell 4: SQL Analytics - Load into SQLite
conn = sqlite3.connect(':memory:')
df.to_sql('transactions', conn, index=False, if_exists='replace')

cursor = conn.execute("SELECT COUNT(*) FROM transactions")
print(f"Rows loaded into SQLite: {cursor.fetchone()[0]}")

What does this code do?

• sqlite3.connect(':memory:') creates a temporary database that lives in RAM. It disappears when the notebook session ends.
• df.to_sql('transactions', conn, ...) writes every row of your cleaned DataFrame into a SQL table called transactions.
• The SELECT COUNT(*) verification query confirms the exact number of rows that made it into the database.

You should see a print statement showing the row count (matching your cleaned DataFrame size).

Seeing a NameError for conn or df?

• Make sure you ran Cells 1-3 first. The sqlite3 import and the cleaned df must already exist in memory.
• If you restarted the runtime, re-run all previous cells from the top before running Cell 4.

Help me debug a NameError when loading data into SQLite in my Colab notebook.

Query revenue, top customers, and monthly trends

With your data in SQLite, you can now write real SQL queries that answer the kind of business questions marketing teams ask every day.

Before you run the first query, think about this: which country do you expect to generate the most revenue for a UK-based online retailer?

• Create a new code cell. Find the top 10 countries by total revenue by running this query:

# Cell 5: SQL Query - Revenue by Country (Top 10)
query_revenue_country = """
SELECT Country,
       ROUND(SUM(TotalPrice), 2) AS TotalRevenue,
       COUNT(DISTINCT "Customer ID") AS UniqueCustomers
FROM transactions
GROUP BY Country
ORDER BY TotalRevenue DESC
LIMIT 10;
"""
revenue_by_country = pd.read_sql(query_revenue_country, conn)
print("Top 10 Countries by Revenue:")
print(revenue_by_country.to_string(index=False))

You see United Kingdom dominating at the top with the vast majority of revenue. Netherlands, EIRE, Germany, and France follow.

What does this query do?

• GROUP BY Country aggregates all transactions for each country into a single row.
• ROUND(SUM(TotalPrice), 2) adds up every transaction's value and rounds to 2 decimal places.
• COUNT(DISTINCT "Customer ID") counts unique buyers per country. This shows whether revenue comes from many customers or a few big spenders.

Getting an OperationalError?

• Check that "Customer ID" uses double quotes. SQLite requires them because the column name has a space.
• Verify you haven't accidentally closed conn already. The connection must stay open for all queries.

Why is my SQL query failing with an OperationalError in SQLite?

• Create a new code cell. Identify the top 10 customers by total spend by running this query:

# Cell 6: SQL Query - Top 10 Customers by Spend
query_top_customers = """
SELECT "Customer ID",
       ROUND(SUM(TotalPrice), 2) AS TotalSpend,
       COUNT(DISTINCT Invoice) AS NumOrders
FROM transactions
GROUP BY "Customer ID"
ORDER BY TotalSpend DESC
LIMIT 10;
"""
top_customers = pd.read_sql(query_top_customers, conn)
print("\nTop 10 Customers by Total Spend:")
print(top_customers.to_string(index=False))

You should see 10 rows showing customer IDs, their total spend, and how many orders they placed.

What does this query reveal?

This query shows the concentration of spending. Notice how the top customer's total spend is dramatically higher than the 10th customer. That gap hints at the Pareto principle (a small number of customers driving most revenue) which the segmentation model will quantify later.

• Create a new code cell. Reveal the monthly revenue trend by running this query:

# Cell 7: SQL Query - Monthly Revenue Trend
query_monthly = """
SELECT strftime('%Y-%m', InvoiceDate) AS Month,
       ROUND(SUM(TotalPrice), 2) AS MonthlyRevenue,
       COUNT(DISTINCT Invoice) AS NumOrders
FROM transactions
GROUP BY Month
ORDER BY Month;
"""
monthly_revenue = pd.read_sql(query_monthly, conn)
print("\nMonthly Revenue Trend:")
print(monthly_revenue.to_string(index=False))

You should see monthly rows from late 2009 through 2011, with revenue typically spiking towards the end of each year (holiday seasonality).

What does strftime do here?

SQLite's strftime('%Y-%m', InvoiceDate) extracts just the year and month from each timestamp. This collapses daily transactions into monthly buckets so you can spot seasonal patterns at a glance.

Seeing NULL in the Month column?

• This happens if InvoiceDate wasn't stored as a recognizable date format. Confirm that Cell 3 includes pd.to_datetime(df['InvoiceDate']) before loading into SQLite.
• If you added the date parsing after loading into SQLite, drop and reload the table by re-running Cell 4.

Help me fix NULL values appearing in my SQLite strftime query results.

Summarize the business context

Before building a segmentation model, you need to know the overall shape of the business. How many customers are there? What is the total revenue? What time period does the data cover?

• Create a new code cell. Calculate and print the business summary by running this code:

# Cell 8: SQL Query - Business Context Summary
query_summary = """
SELECT COUNT(DISTINCT "Customer ID") AS TotalCustomers,
       COUNT(DISTINCT Invoice) AS TotalTransactions,
       ROUND(AVG(TotalPrice), 2) AS AvgItemRevenue,
       ROUND(SUM(TotalPrice), 2) AS TotalRevenue,
       MIN(InvoiceDate) AS FirstDate,
       MAX(InvoiceDate) AS LastDate
FROM transactions;
"""
summary = pd.read_sql(query_summary, conn)
print("\nBusiness Context:")
print(f"  Total Customers: {summary['TotalCustomers'].iloc[0]:,}")
print(f"  Total Transactions: {summary['TotalTransactions'].iloc[0]:,}")
print(f"  Total Revenue: £{summary['TotalRevenue'].iloc[0]:,.2f}")
print(f"  Date Range: {summary['FirstDate'].iloc[0]} to {summary['LastDate'].iloc[0]}")

conn.close()

What does this code do?

• The query calculates five key metrics in a single pass: total unique customers, total transactions, average item revenue, total revenue, and the date range covered.
• The formatted print statements present these metrics in a human-readable way. This is what you'd show a stakeholder to frame the segmentation problem.
• conn.close() shuts down the database connection. You've extracted all the SQL insights you need.

You should see the total number of unique customers (in the thousands), total transactions, total revenue in pounds, and a date range spanning roughly 2009 to 2011.

Getting a ProgrammingError about a closed database?

• If you ran this cell twice, the first run closed conn. Re-run Cell 4 to reopen the connection, then re-run Cells 5-8 in order.
• Make sure conn.close() is the very last line in this cell. If it appears before the print statements, the query will fail.

Help me fix a ProgrammingError about operating on a closed SQLite database.

✔️ Awesome, I've got everything!

Great. Double check you've saved your notebook (Colab auto-saves, but you can press Ctrl+S to be sure).

ⓧ I'd like to double check the full code

Your notebook should now contain these cells in order. Scroll up and confirm each is present:

• Cell 4: SQL Analytics - Load into SQLite (creates conn, loads data, prints row count).
• Cell 5: SQL Query - Revenue by Country (Top 10) (defines query_revenue_country, prints table).
• Cell 6: SQL Query - Top 10 Customers by Spend (defines query_top_customers, prints table).
• Cell 7: SQL Query - Monthly Revenue Trend (defines query_monthly, prints table).
• Cell 8: SQL Query - Business Context Summary (defines query_summary, prints formatted metrics, closes conn).

You now have SQL-powered business insights loaded and ready. Next, you'll engineer RFM features that compress these thousands of transactions into three numbers per customer, giving the clustering algorithm meaningful signal to work with.

Engineer RFM Features

Your SQL queries revealed key business insights, but you still have thousands of transaction rows per customer. K-Means clustering needs a single row per customer with meaningful numeric features to find natural groupings.

You need to compress each customer's entire purchase history into exactly 3 numbers that capture their behavior. RFM Analysis gives you Recency (how recently they bought), Frequency (how often they buy), and Monetary (how much they spend).

In this step, get ready to:

• Calculate Recency, Frequency, and Monetary values for each customer.
• Explore RFM distributions and apply log transformation to reduce skew.
• Score each dimension into quintiles and build a composite RFM score.

Calculate RFM metrics for each customer

To measure recency, you need a fixed reference point. Setting the reference date to one day after the last transaction in the dataset gives every customer a recency score relative to the same moment.

• Create a new code cell in your Colab notebook and paste this code to build the RFM DataFrame:

# Cell 9: RFM Feature Engineering
reference_date = df['InvoiceDate'].max() + pd.Timedelta(days=1)

rfm = df.groupby('Customer ID').agg(
    Recency=('InvoiceDate', lambda x: (reference_date - x.max()).days),
    Frequency=('Invoice', 'nunique'),
    Monetary=('TotalPrice', 'sum')
).reset_index()

print(f"RFM DataFrame shape: {rfm.shape}")
print(f"\nRFM Statistics:")
print(rfm[['Recency', 'Frequency', 'Monetary']].describe())

What does this code do?

• reference_date is set to one day after the last transaction, giving a consistent "today" for measuring recency.
• groupby('Customer ID') collapses thousands of transactions into one row per customer.
• Recency counts days since the customer's most recent purchase. Frequency counts unique invoices. Monetary sums total spend.

Before you run this cell, what do you expect the shape of the RFM DataFrame to be? Think about how many unique customers the dataset has.

• Run the cell.
• You should see a DataFrame with one row per unique customer and three numeric columns (Recency, Frequency, Monetary) with no nulls in the describe output.

Seeing a NameError or empty DataFrame?

• If you see NameError: name 'df' is not defined, you need to re-run Cells 1 through 3 first. The df variable only exists while the notebook session is active.
• If the shape shows 0 rows, check that your data cleaning cell (Cell 3) ran successfully and didn't filter out all records.
• Help me debug my RFM feature engineering cell.

Explore distributions and apply log transformation

K-Means performs poorly when features have wildly different scales or heavy skew. Plotting the raw distributions reveals whether Frequency and Monetary need transformation before clustering.

• Create a new cell and paste this code to plot the raw RFM distributions and apply a log transformation:

# Cell 10: RFM Distribution Analysis
fig, axes = plt.subplots(2, 3, figsize=(15, 10))

# Top row: raw distributions
axes[0, 0].hist(rfm['Recency'], bins=50, color='steelblue', edgecolor='white')
axes[0, 0].set_title('Recency Distribution')
axes[0, 0].set_xlabel('Days')

axes[0, 1].hist(rfm['Frequency'], bins=50, color='coral', edgecolor='white')
axes[0, 1].set_title('Frequency Distribution (Raw)')
axes[0, 1].set_xlabel('Number of Orders')

axes[0, 2].hist(rfm['Monetary'], bins=50, color='mediumseagreen', edgecolor='white')
axes[0, 2].set_title('Monetary Distribution (Raw)')
axes[0, 2].set_xlabel('Total Spend (£)')

# Apply log transformation to reduce skew
rfm['Frequency_log'] = np.log1p(rfm['Frequency'])
rfm['Monetary_log'] = np.log1p(rfm['Monetary'])

• Below that code in the same cell, add this to plot the transformed distributions and save the figure:

# Bottom row: after log transformation
axes[1, 0].hist(rfm['Recency'], bins=50, color='steelblue', edgecolor='white')
axes[1, 0].set_title('Recency (No Transform Needed)')
axes[1, 0].set_xlabel('Days')

axes[1, 1].hist(rfm['Frequency_log'], bins=50, color='coral', edgecolor='white')
axes[1, 1].set_title('Frequency Distribution (Log)')
axes[1, 1].set_xlabel('log(Orders)')

axes[1, 2].hist(rfm['Monetary_log'], bins=50, color='mediumseagreen', edgecolor='white')
axes[1, 2].set_title('Monetary Distribution (Log)')
axes[1, 2].set_xlabel('log(Spend)')

plt.tight_layout()
plt.savefig('rfm_distributions.png', dpi=100, bbox_inches='tight')
plt.show()

What does this code do?

• The top row plots raw distributions. You'll notice Frequency and Monetary are heavily right-skewed, meaning most customers cluster near zero while a few outliers stretch the scale far right.
• np.log1p() applies log(1 + x), which compresses the long tail without breaking zero values. This makes the data more suitable for K-Means, which assumes roughly spherical clusters.
• Recency is already roughly uniform, so it doesn't need transformation.

• Run the cell.
• You should see a 2x3 grid of histograms. The top row shows heavy right skew in Frequency and Monetary. The bottom row shows those same columns transformed into more bell-shaped distributions.

Plots not showing or look empty?

• If no plot appears, make sure you didn't accidentally split the code across two cells. Both chunks must be in the same cell for the figure to render correctly.
• If plots appear blank, confirm that the rfm DataFrame has data by running rfm.shape in a separate cell.
• Help me fix my RFM distribution plots.

Score each RFM dimension into quintiles

The log-transformed values prepare data for K-Means, but you also want a human-readable scoring system. Quintile scoring ranks each customer from 1 to 5 on each dimension, making it easy to identify your best and worst customers at a glance.

• Create a new cell and paste this code to score each customer on all three RFM dimensions:

# Cell 11: RFM Scoring
rfm['R_Score'] = pd.qcut(rfm['Recency'], q=5, labels=[5, 4, 3, 2, 1])
rfm['F_Score'] = pd.qcut(rfm['Frequency'].rank(method='first'), q=5, labels=[1, 2, 3, 4, 5])
rfm['M_Score'] = pd.qcut(rfm['Monetary'].rank(method='first'), q=5, labels=[1, 2, 3, 4, 5])

rfm['RFM_Score'] = rfm['R_Score'].astype(str) + rfm['F_Score'].astype(str) + rfm['M_Score'].astype(str)

print("Top RFM Scores (Best Customers):")
print(rfm.nlargest(5, 'Monetary')[['Customer ID', 'Recency', 'Frequency', 'Monetary', 'RFM_Score']])
print("\nBottom RFM Scores (Worst Customers):")
print(rfm.nsmallest(5, 'Monetary')[['Customer ID', 'Recency', 'Frequency', 'Monetary', 'RFM_Score']])

What does this scoring logic do?

• R_Score labels are reversed (5, 4, 3, 2, 1) because lower recency is better. A customer who purchased yesterday gets a 5.
• F_Score and M_Score use .rank(method='first') before pd.qcut() to handle tied values. Without ranking, qcut fails when too many customers share the same value.
• RFM_Score concatenates all three scores as a string. A customer scoring "555" is your best. A "111" is your worst.

• Run the cell.
• You should see the top 5 customers by monetary value showing high RFM scores (4s and 5s in each position), and the bottom 5 showing low scores (1s and 2s).

Getting a ValueError from qcut?

• If you see a ValueError about bin edges not being unique, make sure you included .rank(method='first') before pd.qcut() for Frequency and Monetary. This breaks ties that would otherwise make bins collapse.
• If the R_Score labels look backwards (best customers have low scores), check that your labels list is [5, 4, 3, 2, 1] for Recency, not [1, 2, 3, 4, 5].
• Help me fix my RFM quintile scoring.

✔️ Awesome, I've got everything!

Great. Double check you've saved your notebook with all three new cells (Cell 9, Cell 10, and Cell 11) present.

ⓧ I'd like to double check the full code

The full file is too long to show in one block. Scroll through your notebook and confirm these cells are present in order:

• Cells 1-8 from the previous steps (Setup, Load, Clean, SQL Analytics).
• Cell 9: RFM Feature Engineering. Starts with reference_date = df['InvoiceDate'].max() + pd.Timedelta(days=1) and ends with the describe() print statement.
• Cell 10: RFM Distribution Analysis. Starts with fig, axes = plt.subplots(2, 3, figsize=(15, 10)) and ends with plt.show(). Contains the log transform lines in the middle.
• Cell 11: RFM Scoring. Starts with rfm['R_Score'] = pd.qcut(...) and ends with the nsmallest print statement.

Each customer now has a compact numeric profile and a human-readable score. Next up, you'll feed these features into K-Means clustering to discover natural customer segments hidden in the data.

Cluster Customers with K-Means

Your RFM scores give you a rule-based segmentation where you manually defined quintile boundaries. That approach works, but it assumes evenly spaced groups.

K-Means clustering finds natural groupings in the data that may be more nuanced. The catch is that K-Means requires you to choose how many clusters (K) upfront. In this step, you will use the Elbow Method to find the right K, then fit the model and profile each segment.

In this step, get ready to:

• Run the Elbow Method to determine the optimal number of clusters.
• Fit a K-Means model and assign human-readable segment names to each cluster.
• Visualize the segments with scatter plots and revenue-share bar charts.

Run the Elbow Method to find optimal K

K-Means needs all features on the same scale. A customer with Recency of 300 days would dominate a customer with Frequency of 5 orders simply because the number is larger. StandardScaler solves this by centering each feature at 0 with unit variance.

• Create a new code cell in your Colab notebook and paste the following code to standardize your RFM features and run the Elbow Method:

# Cell 12: K-Means - Elbow Method
features_for_clustering = rfm[['Recency', 'Frequency_log', 'Monetary_log']]
scaler = StandardScaler()
features_scaled = scaler.fit_transform(features_for_clustering)

inertias = []
K_range = range(2, 11)

for k in K_range:
    kmeans = KMeans(n_clusters=k, init='k-means++', n_init='auto', random_state=42)
    kmeans.fit(features_scaled)
    inertias.append(kmeans.inertia_)

plt.figure(figsize=(10, 6))
plt.plot(K_range, inertias, 'bo-', linewidth=2, markersize=8)
plt.xlabel('Number of Clusters (K)')
plt.ylabel('Inertia (Within-Cluster Sum of Squares)')
plt.title('Elbow Method for Optimal K')
plt.xticks(K_range)
plt.grid(True, alpha=0.3)
plt.savefig('elbow_method.png', dpi=100, bbox_inches='tight')
plt.show()

What does this code do?

• features_for_clustering selects the three columns K-Means will group on: Recency (raw), Frequency (log-transformed), and Monetary (log-transformed).
• StandardScaler normalizes each column so no single dimension dominates distance calculations.
• The loop fits K-Means for K=2 through K=10. Each run's inertia (total within-cluster distance) goes into a list.
• The plot shows inertia dropping as K increases. The "elbow" is the point where adding more clusters stops giving big improvements.

Before you run this cell, think about what shape you expect the plot to take. Will inertia drop steadily, or will there be a clear bend?

• Run the cell.
• You should see a line chart that drops steeply from K=2 to around K=5, then flattens out. The bend at K=5 is your elbow point.

Plot looks like a straight line with no bend?

Make sure you are using the log-transformed columns (Frequency_log and Monetary_log) instead of the raw Frequency and Monetary. Without the log transform, extreme outliers flatten the elbow.

Also confirm that Cell 10 (where you created the log columns) ran successfully before this cell.

Why isn't my Elbow Method plot showing a clear bend?

Fit K-Means and name the segments

The elbow told you K=5 is optimal. Now you will fit the final model and assign each customer to one of 5 clusters.

• Create a new code cell and paste this code to fit the final K-Means model:

# Cell 13: Fit Final KMeans Model
optimal_k = 5
kmeans_final = KMeans(n_clusters=optimal_k, init='k-means++', n_init='auto', random_state=42)
rfm['Cluster'] = kmeans_final.fit_predict(features_scaled)

print(f"Cluster distribution:")
print(rfm['Cluster'].value_counts().sort_index())

What does this code do?

• optimal_k = 5 locks in the cluster count you identified from the elbow plot.
• fit_predict() trains K-Means on your scaled features and returns a cluster label (0-4) for each customer.
• The label goes straight into a new Cluster column on your rfm DataFrame.

Do you think the clusters will be roughly equal in size, or will some be much larger than others? Make your prediction, then run the cell.

• Run the cell. You should see a count of customers per cluster (0 through 4). The sizes will be uneven because real customer behavior is not evenly distributed.

Cluster numbers (0, 1, 2...) do not mean anything on their own. You need to look at each cluster's average RFM values to understand what kind of customer it represents.

• Create a new code cell and paste this code to profile and name each cluster:

# Cell 14: Cluster Profiling
cluster_profile = rfm.groupby('Cluster').agg(
    Avg_Recency=('Recency', 'mean'),
    Avg_Frequency=('Frequency', 'mean'),
    Avg_Monetary=('Monetary', 'mean'),
    Customer_Count=('Customer ID', 'count')
).round(2)

# Name segments based on profile characteristics
segment_names = {}
cluster_profile_sorted = cluster_profile.sort_values('Avg_Monetary', ascending=False)
labels = ['Champions', 'Loyal', 'Promising', 'At Risk', 'Lost']
for i, idx in enumerate(cluster_profile_sorted.index):
    segment_names[idx] = labels[i]

rfm['Segment'] = rfm['Cluster'].map(segment_names)
cluster_profile['Segment'] = cluster_profile.index.map(segment_names)

print("\nCluster Profile:")
print(cluster_profile[['Segment', 'Avg_Recency', 'Avg_Frequency', 'Avg_Monetary', 'Customer_Count']])

How does the naming logic work?

The code sorts clusters by average monetary value (highest first) and assigns names in descending order: Champions, Loyal, Promising, At Risk, Lost.

This is programmatic instead of hardcoded. Cluster IDs can shift between runs (cluster 0 might be Champions one time and Lost the next). Sorting by spend ensures the right label always lands on the right group.

• Run the cell. You should see a table with five rows showing each segment's name, average recency, average frequency, average monetary value, and customer count.
• Confirm that Champions have the highest Avg_Monetary and lowest Avg_Recency (most recent buyers who spend the most).

Seeing a KeyError or empty profile table?

Make sure Cell 13 ran before this cell. The Cluster column must exist in rfm before you can group by it.

If you see only one or two clusters in the output, check that features_scaled was created from the standardized data in Cell 12 and that you passed it (not the raw DataFrame) to fit_predict().

Help me debug my K-Means cluster profiling code.

Visualize the segments

Numbers in a table are precise but hard to scan. A visual makes the Pareto effect (a small group driving most revenue) immediately obvious to any stakeholder.

• Create a new code cell and paste this code to set up the figure and build the scatter plot:

# Cell 15: Segment Visualizations
fig, axes = plt.subplots(1, 3, figsize=(18, 5))

# Scatter: Recency vs Monetary
colors = sns.color_palette('Set2', n_colors=optimal_k)
for cluster_id in range(optimal_k):
    mask = rfm['Cluster'] == cluster_id
    axes[0].scatter(
        rfm.loc[mask, 'Recency'],
        rfm.loc[mask, 'Monetary'],
        label=segment_names[cluster_id],
        alpha=0.6,
        s=20
    )
axes[0].set_xlabel('Recency (Days)')
axes[0].set_ylabel('Monetary (£)')
axes[0].set_title('Customer Segments: Recency vs Monetary')
axes[0].legend()
axes[0].set_yscale('log')

What does the scatter plot show?

Each dot is one customer, positioned by how recently they bought (x-axis) and how much they spent total (y-axis, log scale). Color separates the five segments. Champions cluster in the top-left (recent, high-spend). Lost customers cluster in the bottom-right (long-ago, low-spend).

• In the same cell, paste this code directly below to add the two bar charts and save the figure:

# Bar charts: customer count and revenue share
segment_counts = rfm['Segment'].value_counts()
axes[1].bar(segment_counts.index, segment_counts.values, color=colors[:len(segment_counts)])
axes[1].set_xlabel('Segment')
axes[1].set_ylabel('Number of Customers')
axes[1].set_title('Customer Count by Segment')
axes[1].tick_params(axis='x', rotation=45)

segment_revenue = rfm.groupby('Segment')['Monetary'].sum()
total_revenue = segment_revenue.sum()
revenue_pct = (segment_revenue / total_revenue * 100).sort_values(ascending=False)
axes[2].bar(revenue_pct.index, revenue_pct.values, color=colors[:len(revenue_pct)])
axes[2].set_xlabel('Segment')
axes[2].set_ylabel('Revenue Share (%)')
axes[2].set_title('Revenue Concentration by Segment (Pareto Effect)')
axes[2].tick_params(axis='x', rotation=45)

plt.tight_layout()
plt.savefig('segment_visualizations.png', dpi=100, bbox_inches='tight')
plt.show()

What do the bar charts reveal?

• The middle chart shows how many customers fall into each segment. Expect the Lost or Promising segments to be the largest groups.
• The right chart shows what percentage of total revenue each segment drives. This is the Pareto effect: Champions may be a small percentage of customers but drive a disproportionately large share of revenue.

• Run the cell. You should see a 1x3 panel of charts: a color-coded scatter plot on the left, a customer-count bar chart in the middle, and a revenue-share bar chart on the right.
• Look at the revenue-share chart. One segment should dominate the others, proving that a small fraction of customers accounts for most of the money.

Seeing overlapping x-axis labels or a blank chart?

If labels overlap, make sure tick_params(axis='x', rotation=45) is present for axes 1 and 2. The 45-degree rotation prevents long segment names from colliding.

If a chart is blank, confirm that both code blocks are in the same cell. Splitting them into separate cells would create a figure in the first cell and try to draw on a closed figure in the second.

My segment visualization charts are not rendering correctly.

✔️ Awesome, I've got everything!

Great. Confirm you have saved your notebook. You should have Cells 12 through 15 producing the elbow plot, cluster assignments, segment names, and a 3-panel visualization.

ⓧ I'd like to double check the full code

Scroll up through your notebook and confirm these cells exist in order, each producing output:

• Cell 12: K-Means - Elbow Method. Output: a line plot of inertia vs K with a bend around K=5.
• Cell 13: Fit Final KMeans Model. Output: a printed cluster distribution showing counts for clusters 0-4.
• Cell 14: Cluster Profiling. Output: a table with columns Segment, Avg_Recency, Avg_Frequency, Avg_Monetary, Customer_Count.
• Cell 15: Segment Visualizations. Output: a 1x3 panel with a scatter plot and two bar charts.

You now have five clearly defined customer segments backed by data. Next up, you will translate these segments into concrete marketing recommendations and quantify the revenue at stake.

Build Marketing Recommendations

Your segments are defined and visualized. You can see which customers are Champions and which are Lost.

Segments without actions are useless to a marketing team. Hiring managers want to see you translate data into business decisions. In this step, you will build a marketing action matrix and quantify the revenue at stake to prove you can bridge the gap between ML output and strategy.

In this step, get ready to:

• Create a marketing action matrix mapping each segment to a recommended channel and action.
• Calculate revenue concentration metrics that quantify business risk.
• Add markdown narrative cells so the notebook reads as a complete portfolio piece.

Create the marketing action matrix

Each segment needs a concrete marketing playbook. A hiring manager reviewing your notebook wants to see that you can connect data science output to real business actions with clear priorities.

• Create a new code cell in your notebook and build the marketing action DataFrame by pasting this code:

# Cell 16: Marketing Recommendations
marketing_actions = pd.DataFrame({
    'Segment': ['Champions', 'Loyal', 'Promising', 'At Risk', 'Lost'],
    'Action': [
        'VIP rewards program, early access to new products',
        'Upsell/cross-sell campaigns, loyalty program tier upgrade',
        'Nurture with personalized recommendations, onboarding sequence',
        'Win-back email campaign, time-limited discount offer',
        'Low-priority: final win-back attempt then suppress from paid channels'
    ],
    'Priority': ['High', 'High', 'Medium', 'High', 'Low'],
    'Channel': ['Email + Direct', 'Email + Ads', 'Email', 'Email + Retargeting', 'Email (one-shot)'],
    'Expected_ROI': ['Very High', 'High', 'Medium', 'Medium-High', 'Low']
})

print("\nMarketing Action Matrix:")
print(marketing_actions.to_string(index=False))

What does this code do?

You are building a DataFrame that maps each of your 5 customer segments to a specific marketing action, a priority level, a recommended channel, and an expected ROI tier.

• Champions get VIP treatment because they already spend the most and buy often.
• At Risk gets aggressive win-back campaigns because they used to spend heavily but have gone quiet.
• Lost customers are suppressed from expensive paid channels because the ROI on reactivating them is too low to justify ad spend.

• Run the cell.
• You should see a formatted table with 5 rows (one per segment) and columns for Segment, Action, Priority, Channel, and Expected_ROI.

Seeing a NameError?

• Make sure all previous cells ran successfully. The marketing_actions DataFrame uses only pandas, which was imported in Cell 1.
• If you restarted your runtime, re-run all cells from the top by clicking Runtime then Run all.

Why isn't my marketing action matrix printing?

Calculate revenue concentration metrics

The action matrix tells the marketing team what to do. Now you need to quantify why it matters by showing how much revenue is concentrated in a few segments and how much is at risk of being lost.

• Create a new code cell below your marketing action matrix and calculate the revenue metrics by pasting this code:

# Cell 17: Revenue Concentration Metrics
champion_revenue = rfm[rfm['Segment'] == 'Champions']['Monetary'].sum()
at_risk_revenue = rfm[rfm['Segment'] == 'At Risk']['Monetary'].sum()
total_rev = rfm['Monetary'].sum()

print("\n" + "="*60)
print("KEY BUSINESS INSIGHTS")
print("="*60)
print(f"\nRevenue Concentration:")
print(f"  Champions represent {rfm[rfm['Segment']=='Champions'].shape[0]/rfm.shape[0]*100:.1f}% of customers")
print(f"  but drive {champion_revenue/total_rev*100:.1f}% of total revenue")
print(f"\nRevenue at Risk:")
print(f"  At Risk segment: {rfm[rfm['Segment']=='At Risk'].shape[0]:,} customers")
print(f"  Revenue at stake: £{at_risk_revenue:,.2f} ({at_risk_revenue/total_rev*100:.1f}% of total)")
print(f"\nRecommendation: Prioritize retention campaigns for At Risk segment")
print(f"  to protect £{at_risk_revenue:,.2f} in historical revenue.")
print("="*60)

What does this code do?

This cell calculates three key values that frame the business case for action.

• champion_revenue sums all historical spending from the Champions segment.
• at_risk_revenue sums historical spending from customers who are drifting away.
• The printed output reveals the Pareto effect: a small percentage of customers drive a disproportionate share of revenue. This is the single most compelling data point for a marketing budget conversation.

• Run the cell.
• You should see a formatted "KEY BUSINESS INSIGHTS" block showing the Champions' customer percentage, their revenue share, the At Risk customer count, and the revenue at stake in pounds.

Seeing KeyError: 'Segment'?

• The Segment column is created in Cell 14 (Cluster Profiling). Make sure that cell ran before this one.
• Check that the string 'Champions' and 'At Risk' match exactly what your segment_names dictionary assigned. Capitalization matters.

Help me fix a KeyError when filtering rfm by Segment.

Add markdown cells for portfolio presentation

A notebook full of code cells and output is not a portfolio piece yet. Hiring managers skim. They need clear section headers and narrative text that tells them what problem you solved and what you found.

• Click between existing code cells in your notebook to add new markdown cells (click + Text in the Colab toolbar).
• Add a markdown cell at the very top of your notebook with a title and problem statement.
• Add a markdown cell before Cell 4 (SQL Analytics) explaining your methodology for exploring the data.
• Add a markdown cell before Cell 9 (RFM Feature Engineering) explaining why you chose RFM features.
• Add a markdown cell before Cell 16 (Marketing Recommendations) summarizing your key findings from the clustering.
• Add a final markdown cell after Cell 17 with your overall recommendations and conclusion.

What should these markdown cells say?

Each markdown cell serves a different purpose in the narrative.

• Top cell: Name the project, state the business problem ("Which customers should we invest in?"), and mention the dataset source.
• Methodology cells: One sentence explaining why you chose this approach (SQL for exploration, RFM for feature engineering, K-Means for unsupervised grouping).
• Findings cell: Summarize the Pareto effect you discovered and the segment profiles.
• Conclusion cell: State the top recommendation (protect At Risk revenue) and the next step (CLV projection for budget allocation).

Keep markdown cells short

Two to three sentences per markdown cell is ideal. The code and output already tell most of the story. Your narrative just frames it for someone skimming.

Before you do the final verification, predict what you expect to happen. If you click Runtime then Run all, do you think every cell will execute without errors?

• Click Runtime in the Colab menu bar.
• Click Run all.
• Wait for all cells to finish executing (you will see a green checkmark next to each cell).

Every cell should run top-to-bottom without errors. Your final output is the KEY BUSINESS INSIGHTS block showing revenue concentration and the marketing action matrix above it.

A cell failed partway through?

• Check which cell has a red error indicator. The error message tells you the failing line.
• If the error mentions ucimlrepo, make sure Cell 1 (the !pip install -q ucimlrepo line) ran first. Package installs sometimes need a few seconds before the import works.
• If Cell 2 fails with a network timeout, try running it again. The UCI dataset fetch occasionally takes longer than expected on free Colab runtimes.

Help me debug a cell that fails when I Run All in my Colab notebook.

✔️ Awesome, I've got everything!

Great. Double check that you have saved your notebook (Colab auto-saves, but you can press Cmd+S (macOS) or Ctrl+S (Windows) to be sure).

ⓧ I'd like to double check the full code

Your notebook has 17 code cells total. Scroll through and confirm each one is present in this order:

• Cell 1: Setup and Imports (pip install + all imports).
• Cell 2: Load Data (fetch_ucirepo + df.shape).
• Cell 3: Data Cleaning (dropna, filter cancellations, TotalPrice).
• Cell 4: SQL Analytics - Load into SQLite.
• Cell 5: Revenue by Country query.
• Cell 6: Top 10 Customers query.
• Cell 7: Monthly Revenue Trend query.
• Cell 8: Business Context Summary + conn.close().
• Cell 9: RFM Feature Engineering (groupby + agg).
• Cell 10: RFM Distribution Analysis (2x3 histogram grid).
• Cell 11: RFM Scoring (qcut + RFM_Score).
• Cell 12: Elbow Method (KMeans loop K=2 to 10).
• Cell 13: Fit Final KMeans Model (optimal_k=5).
• Cell 14: Cluster Profiling (segment_names mapping).
• Cell 15: Segment Visualizations (3-panel figure).
• Cell 16: Marketing Recommendations (marketing_actions DataFrame).
• Cell 17: Revenue Concentration Metrics (KEY BUSINESS INSIGHTS).

Secret mission

Customer Lifetime Value Projection

Your segmentation tells a hiring manager who your customers are today. But what will they be worth over the next 12 months? Add a CLV projection that estimates forward-looking revenue per segment and translates it into a concrete budget allocation recommendation.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely on Google Colab's free tier with an open-access dataset, so there are no ongoing costs.

Resources you used:

• Google Colab notebook (saved in your Google Drive).
• UCI Online Retail II dataset (fetched at runtime, not permanently stored).
• SQLite in-memory database (already closed, no persistent storage).
• Generated plot images (rfm_distributions.png, elbow_method.png, segment_visualizations.png, clv_projection.png) stored in the Colab runtime filesystem.

Keep everything running

No action needed. Choose this if you plan to revisit the notebook, refine your segments, or extend the analysis further.

• Your Colab notebook stays in Google Drive and is accessible anytime from colab.research.google.com.
• The runtime (and any saved .png files) will disconnect automatically after a period of inactivity. Re-run the cells to regenerate them.
• The dataset is fetched fresh each time you run the notebook, so no local copy lingers.

Pause - I'll come back to this later

Shut down the Colab runtime to free up resources. Your notebook file remains safely in Google Drive.

• In your Colab notebook, click Runtime in the top menu bar.
• Click Disconnect and delete runtime.
• This removes the in-memory data, variables, and generated plot files from the runtime. Your notebook code cells remain intact in Google Drive.
• When you return, re-run all cells from the top to restore the full pipeline.

Delete - I don't want to use this again

Remove the notebook and all associated data completely.

• In your Colab notebook, click Runtime in the top menu bar, then click Disconnect and delete runtime to remove all in-memory data and generated files.
• Open Google Drive at drive.google.com.
• Navigate to the Colab Notebooks folder (or wherever you saved the notebook).
• Right-click your customer segmentation notebook and select Move to trash.
• Empty your Google Drive trash to permanently delete the file.

That's a Wrap!

That's a Wrap!

Nice work! You just built a complete customer segmentation pipeline that transforms over one million raw retail transactions into actionable marketing strategy.

You've learned how to:

• Query a SQLite database with analytical SQL queries to answer real business questions like revenue by country, top customers by spend, and monthly trends.
• Engineer RFM features from raw transaction data and apply K-Means clustering with the Elbow Method to discover natural customer segments.
• Translate data science output into a marketing action matrix with segment-specific recommendations, priority levels, and revenue concentration metrics that prove the Pareto effect.
• Secret Mission: Projected 12-month Customer Lifetime Value per segment to quantify forward-looking revenue potential and inform budget allocation.

Ready to quiz yourself?