import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
clean = pd.read_csv('online_retail_clean.csv', parse_dates=['InvoiceDate'])
clean['CustomerID'] = clean['CustomerID'].astype(int)
clean.shape

n_customers = clean['CustomerID'].nunique()
n_invoices = clean['InvoiceNo'].nunique()
total_revenue = clean['Revenue'].sum()
print(f'{n_customers} real customers, {n_invoices} real invoices, {total_revenue:,.0f} GBP total real revenue.')

snapshot_date = clean['InvoiceDate'].max() + pd.Timedelta(days=1)
rfm = clean.groupby('CustomerID').agg(Recency=('InvoiceDate', lambda s: (snapshot_date - s.max()).days), Frequency=('InvoiceNo', 'nunique'), Monetary=('Revenue', 'sum'))
rfm['R_score'] = pd.qcut(rfm['Recency'], 4, labels=[4, 3, 2, 1]).astype(int)
rfm['F_score'] = pd.qcut(rfm['Frequency'].rank(method='first'), 4, labels=[1, 2, 3, 4]).astype(int)
champions = rfm[(rfm['R_score'] >= 3) & (rfm['F_score'] >= 3)]
print(f'{len(champions)} real Champion customers, worth {champions["Monetary"].sum():,.0f} GBP in real total historical revenue.')

uk = clean[clean['Country'] == 'United Kingdom']
top_products = uk.groupby('Description')['InvoiceNo'].nunique().sort_values(ascending=False).head(30)
baskets = uk[uk['Description'].isin(top_products.index)].groupby('InvoiceNo')['Description'].apply(set)
baskets = baskets[baskets.apply(len) >= 2]
print(f'{len(baskets)} real multi-item baskets found among the real top thirty products.')

clean['InvoiceMonth'] = clean['InvoiceDate'].values.astype('datetime64[M]')
first_purchase = clean.groupby('CustomerID')['InvoiceMonth'].min()
merged = clean.merge(first_purchase.rename('CohortMonth'), on='CustomerID')
merged['CohortIndex'] = (merged['InvoiceMonth'].dt.year - merged['CohortMonth'].dt.year) * 12 + (merged['InvoiceMonth'].dt.month - merged['CohortMonth'].dt.month)
cohort_pivot = merged.groupby(['CohortMonth', 'CohortIndex'])['CustomerID'].nunique().unstack()
retention = cohort_pivot.divide(cohort_pivot[0], axis=0)
avg_month1_retention = retention[1].mean()
print(f'Average real month-one retention across all cohorts: {round(avg_month1_retention * 100, 1)}%.')

from statsmodels.tsa.holtwinters import ExponentialSmoothing
daily = clean.groupby(clean['InvoiceDate'].dt.date)['Revenue'].sum()
daily.index = pd.to_datetime(daily.index)
weekly = daily.resample('W').sum().iloc[1:-1]
forecast_model = ExponentialSmoothing(weekly, trend='add', seasonal=None).fit()
next_week_forecast = forecast_model.forecast(1).iloc[0]
print(f'Next week real forecasted revenue: {next_week_forecast:,.0f} GBP.')

qty_by_sc = clean.groupby('StockCode')['Quantity'].sum()
price_var_by_sc = clean.groupby('StockCode')['UnitPrice'].nunique()
candidates = price_var_by_sc[(price_var_by_sc >= 4) & (qty_by_sc > 500)].index
elastic_count = 0
for sc in candidates:
    bp = clean[clean['StockCode'] == sc].groupby('UnitPrice')['Quantity'].sum()
    bp = bp[bp > 0]
    if len(bp) < 4 or bp.index.to_series().std() == 0:
        continue
    e, _ = np.polyfit(np.log(bp.index.values.astype(float)), np.log(bp.values.astype(float)), 1)
    elastic_count += 1 if e < -1 else 0
print(f'{elastic_count} of {len(candidates)} real qualifying products are genuinely elastic, good discounting candidates.')

cust_orders = clean.groupby('CustomerID').agg(TotalRevenue=('Revenue', 'sum'), Orders=('InvoiceNo', 'nunique'))
cust_orders['AvgOrderValue'] = cust_orders['TotalRevenue'] / cust_orders['Orders']
total_days = (clean['InvoiceDate'].max() - clean['InvoiceDate'].min()).days
cust_orders['FreqPerMonth'] = cust_orders['Orders'] / (total_days / 30)
cust_orders['PredictedCLV'] = cust_orders['AvgOrderValue'] * cust_orders['FreqPerMonth'] * 12
total_predicted_clv = cust_orders['PredictedCLV'].sum()
print(f'Total real predicted twelve-month CLV across the customer base: {total_predicted_clv:,.0f} GBP.')

product_revenue = clean.groupby('StockCode')['Revenue'].sum().sort_values(ascending=False)
cumulative_pct = product_revenue.cumsum() / product_revenue.sum() * 100
n_tier_a = (cumulative_pct <= 80).sum()
pct_tier_a = n_tier_a / len(product_revenue) * 100
print(f'{round(pct_tier_a, 1)}% of products drive 80% of total real revenue.')

fig, axes = plt.subplots(2, 2, figsize=(14, 10))
axes[0, 0].plot(weekly.index, weekly.values, color='navy')
axes[0, 0].set_title('Real Weekly Revenue History')
axes[0, 1].plot(retention.columns, retention.mean(), marker='o', color='seagreen')
axes[0, 1].set_title('Real Average Retention Curve')
rank = np.arange(1, len(cumulative_pct) + 1)
axes[1, 0].plot(rank, cumulative_pct.values, color='crimson')
axes[1, 0].axhline(80, color='black', linestyle='--')
axes[1, 0].set_title('Real Revenue Pareto Curve')
segment_sizes = pd.Series({'Champions': len(champions), 'Everyone Else': n_customers - len(champions)})
axes[1, 1].bar(segment_sizes.index, segment_sizes.values, color=['gold', 'gray'])
axes[1, 1].set_title('Real Champions vs Real Everyone Else')
plt.tight_layout()
plt.savefig('capstone_dashboard.png', dpi=120)
plt.close()

summary_lines = []
summary_lines.append('RETAIL ANALYTICS CAPSTONE, EXECUTIVE SUMMARY')
summary_lines.append(f'Dataset: {n_customers} real customers, {n_invoices} real invoices, {total_revenue:,.0f} GBP total revenue')
summary_lines.append(f'Champions segment: {len(champions)} customers, {champions["Monetary"].sum():,.0f} GBP historical revenue')
summary_lines.append(f'Average month-1 retention across cohorts: {round(avg_month1_retention * 100, 1)}%')
summary_lines.append(f'Next week forecasted revenue: {next_week_forecast:,.0f} GBP')
summary_lines.append(f'{elastic_count} of {len(candidates)} qualifying products are price-elastic')
summary_lines.append(f'Total predicted 12-month customer lifetime value: {total_predicted_clv:,.0f} GBP')
summary_lines.append(f'{round(pct_tier_a, 1)}% of products (tier A) drive 80% of total revenue')

with open('capstone_executive_summary.txt', 'w') as f:
    for line in summary_lines:
        f.write(line + chr(10))
print('Real executive summary saved.')

with open('capstone_executive_summary.txt') as f:
    reloaded_lines = f.read().splitlines()
len(reloaded_lines) == len(summary_lines)

metrics = pd.DataFrame({
    'Metric': ['Customers', 'Total Revenue', 'Champions', 'Avg Month1 Retention %', 'Next Week Forecast', 'Elastic Products', 'Total Predicted CLV', 'Tier A Product %'],
    'Value': [n_customers, round(total_revenue, 0), len(champions), round(avg_month1_retention * 100, 1), round(next_week_forecast, 0), elastic_count, round(total_predicted_clv, 0), round(pct_tier_a, 1)]
})
metrics

metrics.to_csv('capstone_metrics_table.csv', index=False)
reloaded_metrics = pd.read_csv('capstone_metrics_table.csv')
reloaded_metrics.shape[0] == metrics.shape[0]

recommendation = f'Focus retention spend on the {len(champions)} real Champions worth {champions["Monetary"].sum():,.0f} GBP, while using the {elastic_count} genuinely elastic products identified as safe discount levers to win back the segments below them.'
print(recommendation)

print('Domain 1, Retail and E-Commerce Analytics: 10 real lessons complete.')

from itertools import combinations
from collections import Counter
pair_counts = Counter()
for basket in baskets:
    for a, b in combinations(sorted(basket), 2):
        pair_counts[(a, b)] = pair_counts.get((a, b), 0) + 1
top_pair = max(pair_counts, key=pair_counts.get)
print(f'Most frequent real pairing: {top_pair[0][:20]} + {top_pair[1][:20]}, seen together in {pair_counts[top_pair]} real baskets.')

residuals = weekly - forecast_model.fittedvalues
round(residuals.mean(), 1)

n_tier_b = ((cumulative_pct > 80) & (cumulative_pct <= 95)).sum()
n_tier_c = (cumulative_pct > 95).sum()
print(f'Tier A: {n_tier_a} products, Tier B: {n_tier_b} products, Tier C: {n_tier_c} products.')

clean.groupby('Country')['Revenue'].sum().sort_values(ascending=False).head(3).round(0)

overall_aov = clean.groupby('InvoiceNo')['Revenue'].sum().mean()
round(overall_aov, 2)

monthly_revenue = clean.groupby(clean['InvoiceDate'].dt.to_period('M'))['Revenue'].sum()
busiest_month = monthly_revenue.idxmax()
print(f'Busiest real month: {busiest_month}, with {monthly_revenue[busiest_month]:,.0f} GBP in real revenue.')

abs(rfm['Monetary'].sum() - total_revenue) < 1

cust_orders.sort_values('PredictedCLV', ascending=False).head(5)[['AvgOrderValue', 'FreqPerMonth', 'PredictedCLV']].round(2)

repeat_rate = (cust_orders['Orders'] > 1).mean()
round(repeat_rate * 100, 1)

clean['StockCode'].nunique()

clean['InvoiceNo'].astype(str).str.startswith('C').sum() == 0

cust_orders['TotalRevenue'].median().round(2)

domain_1_lessons = ['Acquiring & Auditing', 'RFM Segmentation', 'Market Basket', 'Cohort Retention', 'Sales Forecasting', 'Price Elasticity', 'Customer Lifetime Value', 'ABC Analysis', 'Scraping Prices', 'Capstone']
len(domain_1_lessons)