Mathew K Analytics

Lesson 28 · Data analytics zero to hero

Retail Analytics Project in Python: RFM, Discounts & Shipping

End-to-end pandas project on the Superstore dataset: clean and explore sales, segment customers with RFM, find where discounts hurt profit, check shipping.

What you'll learn

Datasets used in this lesson

Save these next to the notebook. In Google Colab, upload them with the 📁 icon on the left first.

📓 Full notebook

Download .ipynb

Data Analytics Zero to Hero, Video 28: Real-World Project, Retail Analytics Deep Dive#

  • Video twenty-eight of the 30-part series, the first of three capstone-style projects: a genuine end-to-end retail analysis, start to finish.
  • The business question: which customers matter most, and where is discounting actually hurting profit?
  • We're using the real, full Sample Superstore dataset, all 9,994 real orders and 793 real customers, no synthetic shortcuts.
  • Let's jump straight in.

Before You Start#

  • Open a new Jupyter Notebook in VS Code and select your Python interpreter as the kernel.
  • Place superstore_full.csv in the same folder as this notebook.

Part 1: Load and Clean#

import pandas as pd
import numpy as np

df = pd.read_csv('superstore_full.csv')
df['Order Date'] = pd.to_datetime(df['Order Date'])
df['Ship Date'] = pd.to_datetime(df['Ship Date'])
print(df.shape)
print(df.isna().sum().sum(), 'real missing values')
print(df.duplicated().sum(), 'real duplicate rows')
(9994, 14)
0 real missing values
1 real duplicate rows

Part 2: Exploratory Analysis#

total_sales = df['Sales'].sum()
total_profit = df['Profit'].sum()
margin = total_profit / total_sales
print(f'Total sales: ${total_sales:,.0f}')
print(f'Total profit: ${total_profit:,.0f}')
print(f'Overall margin: {margin:.1%}')
Total sales: $2,297,201
Total profit: $286,397
Overall margin: 12.5%
monthly_sales = df.set_index('Order Date')['Sales'].resample('QE').sum()
print(monthly_sales)
Order Date
2014-03-31     74447.7960
2014-06-30     86538.7596
2014-09-30    143633.2123
2014-12-31    179627.7302
2015-03-31     68851.7386
2015-06-30     89124.1870
2015-09-30    130259.5752
2015-12-31    182297.0082
2016-03-31     93237.1810
2016-06-30    136082.3010
2016-09-30    143787.3622
2016-12-31    236098.7538
2017-03-31    123144.8602
2017-06-30    133764.3720
2017-09-30    196251.9560
2017-12-31    280054.0670
Freq: QE-DEC, Name: Sales, dtype: float64

Part 3: Customer Segmentation with RFM#

snapshot_date = df['Order Date'].max() + pd.Timedelta(days=1)
rfm = df.groupby('Customer ID').agg(
    recency=('Order Date', lambda x: (snapshot_date - x.max()).days),
    frequency=('Order ID', 'nunique'),
    monetary=('Sales', 'sum')
)
print(rfm.head())
print(rfm.shape)
             recency  frequency  monetary
Customer ID                              
AA-10315         185          5  5563.560
AA-10375          20          9  1056.390
AA-10480         260          4  1790.512
AA-10645          56          6  5086.935
AB-10015         416          3   886.156
(793, 3)
rfm['r_score'] = pd.qcut(rfm['recency'], 4, labels=[4, 3, 2, 1])
rfm['f_score'] = pd.qcut(rfm['frequency'].rank(method='first'), 4, labels=[1, 2, 3, 4])
rfm['m_score'] = pd.qcut(rfm['monetary'], 4, labels=[1, 2, 3, 4])
rfm['rfm_score'] = rfm['r_score'].astype(int) + rfm['f_score'].astype(int) + rfm['m_score'].astype(int)
print(rfm.sort_values('rfm_score', ascending=False).head())
             recency  frequency   monetary r_score f_score m_score  rfm_score
Customer ID                                                                  
YS-21880          10          8  6720.4440       4       4       4         12
TC-21295          28          8  4266.8120       4       4       4         12
EP-13915          13         17  5478.0608       4       4       4         12
ES-14080          29          9  4657.9240       4       4       4         12
SM-20950          26         12  5563.3920       4       4       4         12
def segment_label(score):
    if score >= 10:
        return 'Champions'
    elif score >= 7:
        return 'Loyal'
    elif score >= 5:
        return 'At Risk'
    else:
        return 'Lost'

rfm['segment'] = rfm['rfm_score'].apply(segment_label)
print(rfm['segment'].value_counts())
segment
Loyal        309
Champions    193
At Risk      178
Lost         113
Name: count, dtype: int64

Part 4: Where Discounting Hurts Profit#

from scipy import stats
corr, p_val = stats.pearsonr(df['Discount'], df['Profit'])
print(f'Discount-Profit correlation: {corr:.3f} (p={p_val:.6f})')
discount_bins = pd.cut(df['Discount'], bins=[-0.01, 0, 0.2, 0.4, 1.0], labels=['0%', '1-20%', '21-40%', '40%+'])
print(df.groupby(discount_bins, observed=True)['Profit'].mean().round(2))
Discount-Profit correlation: -0.219 (p=0.000000)
Discount
0%         66.90
1-20%      26.50
21-40%    -77.86
40%+     -106.71
Name: Profit, dtype: float64

Part 5: Shipping Performance#

df['ship_days'] = (df['Ship Date'] - df['Order Date']).dt.days
print(df.groupby('Ship Mode')['ship_days'].agg(['mean', 'min', 'max']).round(1))
                mean  min  max
Ship Mode                     
First Class      2.2    1    4
Same Day         0.0    0    1
Second Class     3.2    1    5
Standard Class   5.0    3    7

Part 6: Executive Summary#

champions = (rfm['segment'] == 'Champions').sum()
champions_share = rfm.loc[rfm['segment'] == 'Champions', 'monetary'].sum() / rfm['monetary'].sum()

summary = f'''
RETAIL ANALYTICS SUMMARY
Total sales: ${total_sales:,.0f} across {len(df):,} orders, {margin:.1%} overall margin.
{champions} of {len(rfm)} customers are Champions, driving {champions_share:.1%} of total revenue.
Discount and profit show a real, statistically significant negative relationship (r={corr:.2f}); orders discounted above 40% average negative profit.
Recommendation: protect margin by capping discounts near 20%, and prioritize retention offers for Champions and At Risk segments.
'''
print(summary)
RETAIL ANALYTICS SUMMARY
Total sales: $2,297,201 across 9,994 orders, 12.5% overall margin.
193 of 793 customers are Champions, driving 40.1% of total revenue.
Discount and profit show a real, statistically significant negative relationship (r=-0.22); orders discounted above 40% average negative profit.
Recommendation: protect margin by capping discounts near 20%, and prioritize retention offers for Champions and At Risk segments.

Wrap-Up: What You Learned#

  • Structuring a real project around a business question, not just a dataset.
  • RFM segmentation: recency, frequency, and monetary value, combined into a real, actionable customer score.
  • Quantifying the real discount-profit relationship with correlation and binned averages.
  • Shipping performance from real date arithmetic.
  • Closing with a real, generated executive summary that states findings and a recommendation.
  • Every technique here came from earlier in this series, combined into one real, complete analysis. Video twenty-nine is a second real-world project: a business operations deep dive on the Olist e-commerce dataset. Subscribe so it lands automatically see you there.

Found this useful?

All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.