Analysing Retail Store Performance

import pandas as pd
import numpy as np

# Build the Meridian Retail dataset
df = pd.DataFrame({
    "store_id":        ["ST01","ST02","ST03","ST04","ST05","ST06",
                        "ST07","ST08","ST09","ST10","ST11","ST12"],
    "region":          ["North","South","East","West","North","South",
                        "East","West","North","South","East","West"],
    "store_type":      ["Large","Medium","Small","Large","Medium","Small",
                        "Large","Medium","Small","Large","Medium","Small"],
    "monthly_revenue": [48200,31500,18900,55600,27300,15800,
                        42100,38700,21400,61200,24600,19800],
    "footfall":        [4100,3800,1200,5200,2900,1800,
                        3600,4500,1600,5800,2400,1400],
    "transactions":    [820,570,390,940,480,240,
                        710,720,350,1050,380,280],
    "staff_count":     [14,9,5,16,8,5,12,11,6,18,7,5]
})

# Inspect
print("Shape:", df.shape)
print("\nMissing values:", df.isnull().sum().sum(), "total")

# Compute derived efficiency metrics
# Revenue per visitor — how much revenue each person who enters generates on average
df["rev_per_visitor"]   = (df["monthly_revenue"] / df["footfall"]).round(2)

# Conversion rate — what % of visitors actually buy something
df["conversion_rate"]   = (df["transactions"] / df["footfall"] * 100).round(1)

# Average transaction value — how much each buying customer spends
df["avg_transaction"]   = (df["monthly_revenue"] / df["transactions"]).round(2)

# Revenue per staff — labour efficiency metric
df["rev_per_staff"]     = (df["monthly_revenue"] / df["staff_count"]).round(0)

print("\nSummary of derived metrics:")
print(df[["store_id","rev_per_visitor","conversion_rate",
          "avg_transaction","rev_per_staff"]].to_string(index=False))

# Rank stores by total revenue (the director's instinct)
revenue_rank = df.sort_values("monthly_revenue", ascending=False)[
    ["store_id","region","monthly_revenue"]
].reset_index(drop=True)
revenue_rank.index += 1  # start ranking at 1

print("Revenue ranking:")
print(revenue_rank.to_string())

# Rank stores by revenue per visitor (the efficiency view)
efficiency_rank = df.sort_values("rev_per_visitor", ascending=False)[
    ["store_id","region","rev_per_visitor","conversion_rate"]
].reset_index(drop=True)
efficiency_rank.index += 1

print("\nEfficiency ranking (revenue per visitor):")
print(efficiency_rank.to_string())

# Which stores are top-3 in revenue but NOT top-3 in efficiency?
top3_rev = set(df.nlargest(3, "monthly_revenue")["store_id"])
top3_eff = set(df.nlargest(3, "rev_per_visitor")["store_id"])
misleading = top3_rev - top3_eff

print(f"\nTop 3 by revenue:    {top3_rev}")
print(f"Top 3 by efficiency: {top3_eff}")
print(f"In revenue top-3 but NOT efficiency top-3: {misleading}")

# Compute network-wide averages for footfall and conversion
avg_footfall    = df["footfall"].mean()
avg_conversion  = df["conversion_rate"].mean()
avg_atv         = df["avg_transaction"].mean()

print(f"Network avg footfall:    {avg_footfall:.0f} visitors")
print(f"Network avg conversion:  {avg_conversion:.1f}%")
print(f"Network avg ATV:         ${avg_atv:.2f}")

# Flag stores that are above average footfall but below average conversion
# These have the traffic — they just aren't converting it
high_footfall_low_conv = df[
    (df["footfall"]         > avg_footfall) &
    (df["conversion_rate"]  < avg_conversion)
][["store_id","region","footfall","conversion_rate","avg_transaction"]].sort_values(
    "conversion_rate"
)
print("\nHigh footfall, low conversion stores (priority intervention):")
print(high_footfall_low_conv.to_string(index=False))

# Dual-flag: both low conversion AND low ATV vs network averages
dual_flag = df[
    (df["conversion_rate"] < avg_conversion) &
    (df["avg_transaction"] < avg_atv)
][["store_id","region","conversion_rate","avg_transaction"]]
print("\nDual-flag stores (low conversion AND low ATV — highest priority):")
print(dual_flag.to_string(index=False))

# Aggregate by region — compute mean efficiency metrics per region
regional = df.groupby("region").agg(
    stores           = ("store_id",        "count"),
    total_revenue    = ("monthly_revenue", "sum"),
    avg_rev_visitor  = ("rev_per_visitor", "mean"),
    avg_conversion   = ("conversion_rate", "mean"),
    avg_atv          = ("avg_transaction", "mean"),
    avg_rev_staff    = ("rev_per_staff",   "mean")
).round(2).reset_index()

regional = regional.sort_values("avg_rev_visitor", ascending=False)

print("Regional performance summary:")
print(regional.to_string(index=False))

# Which region has the highest conversion rate?
best_conv_region = regional.loc[regional["avg_conversion"].idxmax(), "region"]
print(f"\nBest conversion region: {best_conv_region}")

# Revenue contribution by region
regional["rev_share_pct"] = (
    regional["total_revenue"] / regional["total_revenue"].sum() * 100
).round(1)
print("\nRevenue share by region:")
print(regional[["region","total_revenue","rev_share_pct"]].to_string(index=False))