Skip to main content

Capstone: Retail Sales Intelligence Report

Introduction​

You are a Data Analyst at RetailEdge. The VP of Sales has asked for an Annual Performance Report to present at the board meeting. This capstone applies every skill from the Data section — NumPy for numerical analysis, Pandas for loading, cleaning, transforming, grouping, and summarizing — to RetailEdge_Sales.csv, producing a clean report printed to console. This mirrors a real data-analyst task: load, clean, transform, analyze, and summarize, then answer specific business questions.

Concepts​

Nothing new — this capstone combines NumPy: Arrays, Indexing & Slicing, NumPy: Vectorized Operations, Reshaping & Linear Algebra, Pandas: Series, DataFrames & Selection, Data Cleaning & Transformation with Pandas, and GroupBy, Pivot Tables & Time Series with Pandas into one pipeline, in four phases.

Challenges​

1. Phase 1 — Data ingestion and quality​

  • Load RetailEdge_Sales.csv with proper date parsing.
  • Print a data quality report: row/column counts, date range, missing values, duplicate count, and an outlier summary using the NumPy IQR method.
  • Clean the dataset (missing values, duplicates, type corrections) using the pipeline from the previous chapter.
💡 Show Answer
import numpy as np
import pandas as pd

def data_quality_report(df):
    print('=== DATA QUALITY REPORT ===')
    print(f'Total rows: {len(df)}')
    print(f'Total columns: {df.shape[1]}')
    print(f'Date range: {df.Date.min()} to {df.Date.max()}')
    print('\nMissing values:')
    print(df.isnull().sum()[df.isnull().sum() > 0])
    print(f'\nDuplicates: {df.duplicated().sum()}')

    revenue = df['Revenue'].to_numpy()
    q1, q3 = np.percentile(revenue, [25, 75])
    iqr = q3 - q1
    lower, upper = q1 - 1.5 * iqr, q3 + 1.5 * iqr
    outlier_count = ((revenue < lower) | (revenue > upper)).sum()
    print(f'\nRevenue outliers (IQR method): {outlier_count} ({outlier_count/len(df)*100:.1f}%)')

df = pd.read_csv('RetailEdge_Sales.csv', parse_dates=['Date'])
data_quality_report(df)

df_clean = df.dropna(subset=['Revenue', 'Profit'], how='all')
df_clean['Discount'] = df_clean['Discount'].fillna(0.0)
df_clean['SalesRep'] = df_clean['SalesRep'].fillna('Unknown')
df_clean = df_clean.drop_duplicates(subset=['OrderID'], keep='first')
for col in ['Store', 'Region', 'Category']:
    df_clean[col] = df_clean[col].astype('category')

2. Phase 2 — Business questions​

Answer each of the following with Pandas:

  • Which store generated the highest total revenue in 2023? How does it compare to the company average?
  • What is the overall profit margin? Which category has the best margin?
  • Identify the top 3 and bottom 3 products by profitability.
  • Which quarter had the highest revenue?
  • Which SalesRep closed the highest average order value? The most orders?
  • Are there any stores with negative profit in any month? Identify them.
  • What percentage of total revenue comes from orders with >15% discount?
  • Build a region x category revenue pivot table.
💡 Show Answer
store_revenue = df_clean.groupby('Store')['Revenue'].sum()
print('Top store:', store_revenue.idxmax(), store_revenue.max())
print('Company average per store:', store_revenue.mean())

overall_margin = df_clean['Profit'].sum() / df_clean['Revenue'].sum() * 100
category_margin = df_clean.groupby('Category').apply(lambda g: g['Profit'].sum() / g['Revenue'].sum() * 100)
print('Overall margin:', round(overall_margin, 1), '%')
print('Best category margin:', category_margin.idxmax())

product_profit = df_clean.groupby('Product')['Profit'].sum().sort_values(ascending=False)
print('Top 3 products:', product_profit.head(3))
print('Bottom 3 products:', product_profit.tail(3))

quarter_revenue = df_clean.groupby('Quarter')['Revenue'].sum()
print('Best quarter:', quarter_revenue.idxmax())

rep_avg_order = df_clean.groupby('SalesRep')['Revenue'].mean()
rep_order_count = df_clean.groupby('SalesRep')['OrderID'].count()
print('Highest avg order value rep:', rep_avg_order.idxmax())
print('Most orders rep:', rep_order_count.idxmax())

monthly_store_profit = df_clean.groupby(['Store', 'Month'])['Profit'].sum()
negative_months = monthly_store_profit[monthly_store_profit < 0]
print('Store-months with negative profit:', negative_months)

high_discount_revenue = df_clean[df_clean['Discount'] > 0.15]['Revenue'].sum()
pct_of_total = high_discount_revenue / df_clean['Revenue'].sum() * 100
print(f'% of revenue from >15% discount orders: {pct_of_total:.1f}%')

region_category_pivot = pd.pivot_table(
    df_clean, values='Revenue', index='Region', columns='Category', aggfunc='sum', fill_value=0,
)

3. Phase 3 — NumPy deep dive​

  • Convert Revenue and Profit to NumPy arrays and compute a full statistical profile using only NumPy (mean, median, std, P10, P90).
  • Compute the correlation between Revenue and Profit.
  • Compute a bootstrap 95% confidence interval for mean profit (1,000 resamples).
💡 Show Answer
revenue_arr = df_clean['Revenue'].to_numpy()
profit_arr = df_clean['Profit'].to_numpy()

print('Revenue Distribution:')
print(f'  Mean:   {np.mean(revenue_arr):,.0f}')
print(f'  Median: {np.median(revenue_arr):,.0f}')
print(f'  Std Dev: {np.std(revenue_arr):,.0f}')
print(f'  P10:    {np.percentile(revenue_arr, 10):,.0f}')
print(f'  P90:    {np.percentile(revenue_arr, 90):,.0f}')

corr = np.corrcoef(revenue_arr, profit_arr)[0, 1]
print(f'Revenue-Profit correlation: {corr:.4f}')

np.random.seed(42)
boot_means = np.array([
    np.mean(np.random.choice(profit_arr, size=len(profit_arr), replace=True))
    for _ in range(1000)
])
ci_lower, ci_upper = np.percentile(boot_means, [2.5, 97.5])
print(f'95% CI for mean profit: {ci_lower:,.0f} to {ci_upper:,.0f}')

4. Phase 4 — Executive summary output​

Print a formatted executive summary showing total revenue, total profit, profit margin, order count, average order value, active store count, the top store by revenue, and the top category by profit margin.

💡 Show Answer
def print_executive_summary(df):
    print('=' * 60)
    print('  RETAILEDGE — ANNUAL PERFORMANCE SUMMARY 2023')
    print('=' * 60)
    print(f'  Total Revenue  : {df.Revenue.sum():>15,.0f}')
    print(f'  Total Profit   : {df.Profit.sum():>15,.0f}')
    print(f'  Profit Margin  :     {df.Profit.sum()/df.Revenue.sum()*100:>13.1f}%')
    print(f'  Total Orders   :     {len(df):>15,}')
    print(f'  Avg Order Value: {df.Revenue.mean():>15,.0f}')
    print(f'  Active Stores  :     {df.Store.nunique():>15}')
    print()
    top_store = df.groupby('Store')['Revenue'].sum().idxmax()
    print(f'  Top store by revenue: {top_store}')
    cat_margin = df.groupby('Category').apply(lambda g: g.Profit.sum() / g.Revenue.sum() * 100)
    print(f'  Top category by margin: {cat_margin.idxmax()} ({cat_margin.max():.1f}%)')
    print('=' * 60)

print_executive_summary(df_clean)

Capstone Submission Checklist​

  • Data quality report printed with correct statistics.
  • Cleaning pipeline handles missing values, duplicates, and type issues.
  • All 8 business questions answered with correct Pandas code.
  • NumPy statistical profile matches Pandas .describe() output.
  • Bootstrap confidence interval computed and printed.
  • Executive summary function prints cleanly with proper formatting.
  • Code organized into functions (data_quality_report, clean_data, analyze, summarize).
  • Every DataFrame produced by a groupby has .reset_index() called where a flat table is expected.