###################################################
# Chapter 7: RFM Analysis                         #
# Chestnut Ridge Multi-channel Retailer Example   #
###################################################

# This script reproduces the full Chestnut Ridge RFM analysis example in Python:
#   Step 1. Data setup and RFM scores (independent and sequential sort)
#   Step 2. RFM analysis exercise (breakeven rate and purchase rate of each RFM cell)
#   Step 3. Profitability and ROMI (RFM cell tables and targeting results in Table 7.4)
# Run it from top to bottom. Each step prints its result or shows a plot.
# The "# %%" lines split the script into cells you can run one at a time
# in VS Code, Spyder, or PyCharm.

# %%
###################################################
# Step 1: Data Setup                              #
###################################################

## Install Packages (if needed - only installs packages that are missing)
## Each entry is {name used with import: name used with pip install}
import sys, subprocess, importlib.util
packages = {"pandas": "pandas", "numpy": "numpy", "matplotlib": "matplotlib"}
new_packages = [pip_name for import_name, pip_name in packages.items() if importlib.util.find_spec(import_name) is None]
if new_packages: subprocess.check_call([sys.executable, "-m", "pip", "install", *new_packages])

## Load Packages and Set Seed
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
from matplotlib.patches import Patch, Rectangle
from tkinter import Tk, filedialog
np.random.seed(1)
pd.set_option("display.width", 200, "display.max_columns", 20) ## Show all columns when printing

## Function to choose a file with a dialog box
## (the Python version of file.choose() in R - no need to set the working directory)
def file_choose(title="Choose a file"):
    root = Tk()
    root.withdraw()                     # hide the empty Tk window
    root.attributes("-topmost", True)   # bring the dialog to the front
    path = filedialog.askopenfilename(title=title, filetypes=[("CSV files", "*.csv"), ("All files", "*.*")])
    root.destroy()
    if not path:
        raise SystemExit("No file was chosen.")
    return path

## Read in RFM data
rfm = pd.read_csv(file_choose("Choose retail_rfm.csv")) ## Choose retail_rfm.csv file

## Look at the data (see Table 7.3 for variable definitions)
print(rfm.head())
print(rfm.describe())

## How many levels for each
groups = 5 ## This will use quintiles to sort and give 125 total groups

## Function to split customers into n groups of (nearly) equal size.
## Customers are ranked from lowest to highest (ties are broken by the order they
## appear in the data) and then split into n groups: 1 = lowest, n = highest.
def ntile(x, n):
    ranks = x.rank(method="first").astype(int) - 1
    splits = np.array_split(np.arange(len(x)), n)
    bins = np.concatenate([np.full(len(s), i + 1) for i, s in enumerate(splits)])
    return pd.Series(bins[ranks], index=x.index)

## Run RFM Analysis with Independent Sort
## Each of R, F, and M is sorted on its own. Recency is multiplied by -1 so that
## the most recent purchasers (fewest days) get the highest score.
rfm["recency_score_indep"] = ntile(rfm["recency_days"] * -1, groups)
rfm["frequency_score_indep"] = ntile(rfm["number_of_orders"], groups)
rfm["monetary_score_indep"] = ntile(rfm["revenue"], groups)
rfm["rfm_score_indep"] = (rfm["recency_score_indep"] * 100 + rfm["frequency_score_indep"] * 10
                          + rfm["monetary_score_indep"])

## Run RFM Analysis with Sequential Sort
## First sort on R; then sort on F within each R group; then sort on M within each R-F group.
rfm["recency_score_seq"] = ntile(rfm["recency_days"] * -1, groups)
rfm["frequency_score_seq"] = (rfm.groupby("recency_score_seq")["number_of_orders"]
                              .transform(lambda x: ntile(x, groups)))
rfm["monetary_score_seq"] = (rfm.groupby(["recency_score_seq", "frequency_score_seq"])["revenue"]
                             .transform(lambda x: ntile(x, groups)))
rfm["rfm_score_seq"] = (rfm["recency_score_seq"] * 100 + rfm["frequency_score_seq"] * 10
                        + rfm["monetary_score_seq"])

## Look at the RFM results for the first few customers
print(rfm.head())


# %%
###################################################
# Step 2: RFM Analysis Exercise                   #
###################################################

## Campaign economics
avg_purchase = 40.00   ## Average purchase amount from the campaign
avg_cogs = 19.20       ## Average cost of goods sold (48% of purchase)
avg_shipping = 6.00    ## Average cost to ship the product
cost_per_mail = 2.00   ## Average cost of the marketing campaign (catalog) per customer

## Breakeven rate
profit_per_buyer = avg_purchase - avg_cogs - avg_shipping - cost_per_mail
breakeven = cost_per_mail / profit_per_buyer
print(profit_per_buyer)  ## $12.80
print(breakeven)         ## 0.1563 (15.63%)

## Function to summarize each RFM cell: number of customers, number of purchasers, and purchase rate
def summarize_cells(sort):
    cells = (rfm.groupby([f"rfm_score_{sort}", f"recency_score_{sort}",
                          f"frequency_score_{sort}", f"monetary_score_{sort}"])["purchase"]
             .agg(customers="count", purchasers="sum", purchase_rate="mean")
             .reset_index())
    cells.columns = ["rfm_score", "recency", "frequency", "monetary",
                     "customers", "purchasers", "purchase_rate"]
    cells["target"] = cells["purchase_rate"] > breakeven
    return cells

cells_indep = summarize_cells("indep")
cells_seq = summarize_cells("seq")

## Function to plot the purchase rate of each RFM cell (Figures 7.2 and 7.3)
def plot_rfm_bars(cells, title):
    fig, ax = plt.subplots(figsize=(12, 6))
    ax.bar(cells["rfm_score"].astype(str), cells["purchase_rate"], color="0.35")
    ax.axhline(breakeven, color="black", linestyle="--", linewidth=1)
    ax.yaxis.set_major_formatter(plt.matplotlib.ticker.PercentFormatter(1.0))
    ax.set_title(title, loc="left")
    ax.set_xlabel("RFM Cell")
    ax.set_ylabel("Percentage of Purchasers")
    ax.tick_params(axis="x", labelrotation=90, labelsize=6)
    ax.margins(x=0.01)
    for side in ["top", "right"]:
        ax.spines[side].set_visible(False)
    plt.tight_layout()
    plt.show()

## Figure 7.2: Purchasers Using RFM with Independent Sort
plot_rfm_bars(cells_indep, "RFM Score with Independent Sort")

## Figure 7.3: Purchasers Using RFM with Sequential Sort
plot_rfm_bars(cells_seq, "RFM Score with Sequential Sort")

## Cells with the highest purchase rates
print(cells_indep.sort_values("purchase_rate", ascending=False).head(5))  ## e.g., cell 535 has a 60% purchase rate
print(cells_seq.sort_values("purchase_rate", ascending=False).head(5))    ## e.g., cells 555 and 554 have 72.5% and 66.25%

## Number of RFM cells above the breakeven rate
print(cells_indep["target"].sum())  ## 37 of 125 cells
print(cells_seq["target"].sum())    ## 56 of 125 cells


# %%
###################################################
# Step 3: Computing Profitability and ROMI        #
###################################################

## Function to create an RFM cell table (Figures 7.4 to 7.9)
## Rows are Recency and Frequency, columns are Monetary. Cells above the
## breakeven rate are shaded dark. The value shown in each cell can be the
## purchase rate, the number of purchasers, or the number of customers.
def plot_rfm_table(cells, value, title):
    fig, ax = plt.subplots(figsize=(9, 10))
    for _, cell in cells.iterrows():
        x = cell["monetary"] - 1
        y = (cell["recency"] - 1) * (groups + 1) + (cell["frequency"] - 1) ## leave a gap between recency groups
        shade = "#2C5F8A" if cell["target"] else "0.9"
        ax.add_patch(Rectangle((x, y), 1, 1, facecolor=shade, edgecolor="white"))
        label = f"{cell[value]:.4f}" if value == "purchase_rate" else f"{cell[value]:.0f}"
        ax.text(x + 0.5, y + 0.5, label, ha="center", va="center", fontsize=9,
                color="white" if cell["target"] else "black")
    ## Axis labels: monetary across the top, recency and frequency down the side
    ax.set_xlim(0, groups)
    ax.set_ylim(groups * (groups + 1) - 1, 0)
    ax.xaxis.tick_top()
    ax.xaxis.set_label_position("top")
    ax.set_xticks(np.arange(groups) + 0.5, labels=range(1, groups + 1))
    ax.set_xlabel("Monetary Score")
    ax.set_yticks([r * (groups + 1) + f + 0.5 for r in range(groups) for f in range(groups)],
                  labels=[f"R{r + 1}  F{f + 1}" for r in range(groups) for f in range(groups)])
    ax.set_ylabel("Recency Score / Frequency Score")
    ax.tick_params(length=0)
    for side in ax.spines:
        ax.spines[side].set_visible(False)
    ax.legend(handles=[Patch(color="0.9", label="Below breakeven"),
                       Patch(color="#2C5F8A", label="Above breakeven")],
              loc="upper center", bbox_to_anchor=(0.5, -0.01), ncol=2, frameon=False)
    ax.set_title(title, loc="left", pad=35)
    plt.tight_layout()
    plt.show()

## Independent Sort
## Figure 7.4: Purchaser Percentage Table Using RFM with Independent Sort
plot_rfm_table(cells_indep, "purchase_rate", "Percentage of Purchasers in Each Cell Using Independent Sort")

## Figure 7.5: Purchaser Count Table Using RFM with Independent Sort
plot_rfm_table(cells_indep, "purchasers", "Count of Purchasers in Each Cell Using Independent Sort")

## Figure 7.6: Customer Count Table Using RFM with Independent Sort
plot_rfm_table(cells_indep, "customers", "Count of Customers in Each Cell Using Independent Sort")

## Number of purchasers and customers in the cells above the breakeven rate
print(cells_indep.loc[cells_indep["target"], "purchasers"].sum())  ## 1,314 purchasers
print(cells_indep.loc[cells_indep["target"], "customers"].sum())   ## 4,459 customers

## Sequential Sort
## Figure 7.7: Purchaser Percentage Table Using RFM with Sequential Sort
plot_rfm_table(cells_seq, "purchase_rate", "Percentage of Purchasers in Each Cell Using Sequential Sort")

## Figure 7.8: Purchaser Count Table Using RFM with Sequential Sort
plot_rfm_table(cells_seq, "purchasers", "Count of Purchasers in Each Cell Using Sequential Sort")

## Figure 7.9: Customer Count Table Using RFM with Sequential Sort
plot_rfm_table(cells_seq, "customers", "Count of Customers in Each Cell Using Sequential Sort")

## Number of purchasers and customers in the cells above the breakeven rate
print(cells_seq.loc[cells_seq["target"], "purchasers"].sum())  ## 1,306 purchasers
print(cells_seq.loc[cells_seq["target"], "customers"].sum())   ## 4,480 customers

## Function to compute profitability and ROMI for a targeting strategy
def campaign_results(targeted, buyers):
    revenue = buyers * avg_purchase
    cogs = buyers * avg_cogs
    shipping = buyers * avg_shipping
    marketing = targeted * cost_per_mail
    profit = revenue - cogs - shipping - marketing
    return {"customers": len(rfm), "targeted": targeted, "pct_targeted": targeted / len(rfm),
            "buyers": buyers, "response_rate": buyers / targeted, "revenue": revenue, "cogs": cogs,
            "shipping": shipping, "marketing": marketing, "profit": profit, "romi": profit / marketing}

## Table 7.4: RFM Analysis Results
results = pd.DataFrame({
    "Random Targeting": campaign_results(len(rfm), rfm["purchase"].sum()),
    "Targeted: Independent Sort RFM": campaign_results(cells_indep.loc[cells_indep["target"], "customers"].sum(),
                                                       cells_indep.loc[cells_indep["target"], "purchasers"].sum()),
    "Targeted: Sequential Sort RFM": campaign_results(cells_seq.loc[cells_seq["target"], "customers"].sum(),
                                                      cells_seq.loc[cells_seq["target"], "purchasers"].sum())})

## Format the table for easier reading
comma = "{:,.0f}".format
percent = "{:.2%}".format
dollar = "${:,.2f}".format
results_table = pd.DataFrame({
    "Number of Customers": results.loc["customers"].map(comma),
    "Number Targeted": results.loc["targeted"].map(comma),
    "% of Customers Targeted": results.loc["pct_targeted"].map(percent),
    "Number of Buyers": results.loc["buyers"].map(comma),
    "Response Rate": results.loc["response_rate"].map(percent),
    "Revenues": results.loc["revenue"].map(dollar),
    "Cost of Goods Sold": results.loc["cogs"].map(dollar),
    "Shipping": results.loc["shipping"].map(dollar),
    "Targeted Marketing": results.loc["marketing"].map(dollar),
    "Profit": results.loc["profit"].map(dollar),
    "ROMI": results.loc["romi"].map(percent)}).T
results_table.index.name = "Profitability Analysis"
print(results_table.to_string())