การวิเคราะห์และการคำนวณ KPI

การโหลดข้อมูลพร้อมสำหรับการวิเคราะห์

import pandas as pd

df = pd.read_parquet('output/analysis_ready.parquet')
print(f'Loaded: {df.shape}')
print('KPIs to compute:')
print('  1. Monthly cohort retention matrix')
print('  2. Category revenue and margin ranking')
print('  3. Rolling 30-day active users per region')

KPI 1: รายได้รายเดือนแยกตามหมวดหมู่

import pandas as pd

df = pd.read_parquet('output/analysis_ready.parquet')

# Monthly revenue by category
monthly_cat = (
    df.groupby(['year_month', 'category'])['revenue']
    .sum()
    .reset_index()
    .pivot(index='year_month', columns='category', values='revenue')
    .fillna(0)
    .sort_index()
)

# 3-month rolling average
monthly_cat_smooth = monthly_cat.rolling(3, min_periods=1).mean()

print('Monthly revenue by category (head):')
print(monthly_cat.round(0).head())
print('Shape:', monthly_cat.shape)

KPI 2: ภูมิภาคที่มีรายได้รวมสูงสุด

import pandas as pd

df = pd.read_parquet('output/analysis_ready.parquet')

region_kpi = (
    df.groupby('region')
    .agg(
        total_revenue=('revenue', 'sum'),
        order_count=('order_id', 'nunique'),
        unique_customers=('customer_id', 'nunique'),
        avg_order_value=('revenue', 'mean')
    )
    .reset_index()
    .sort_values('total_revenue', ascending=False)
)

total = region_kpi['total_revenue'].sum()
region_kpi['revenue_share'] = (region_kpi['total_revenue'] / total).round(3)

print(region_kpi.head(10).to_string(index=False))

KPI 3: เมทริกซ์การรักษาลูกค้าตามกลุ่ม

import pandas as pd

df = pd.read_parquet('output/analysis_ready.parquet')

# Compute months since cohort start
df['cohort_period'] = (
    (df['order_date'].dt.to_period('M') -
     pd.PeriodIndex(df['cohort_month'], freq='M'))
    .apply(lambda x: x.n)
)

cohort_data = (
    df.groupby(['cohort_month', 'cohort_period'])['customer_id']
    .nunique()
    .reset_index()
    .rename(columns={'customer_id': 'customers'})
)

# Pivot: rows=cohort, columns=period
cohort_pivot = cohort_data.pivot(
    index='cohort_month', columns='cohort_period', values='customers'
)

cohort_sizes = cohort_pivot[0]
retention = cohort_pivot.divide(cohort_sizes, axis=0).round(3)
print('Retention matrix (first 3 cohorts):')
print(retention.head(3))

KPI 4: ผู้ใช้งานที่ใช้งานอยู่ในช่วง 30 วันแบบสะสม

import pandas as pd

df = pd.read_parquet('output/analysis_ready.parquet')

# Daily unique purchasers
daily = (
    df.groupby('order_date')['customer_id']
    .nunique()
    .reset_index()
    .rename(columns={'customer_id': 'daily_unique_customers'})
    .set_index('order_date')
    .sort_index()
)

# 30-day rolling sum (approximation: sum of daily unique purchasers)
# True rolling unique requires custom logic
daily['rolling_30d'] = daily['daily_unique_customers'].rolling('30D').sum()

print('Rolling 30-day active customers:')
print(daily.tail())

การจัดอันดับรายได้ระดับผลิตภัณฑ์

import pandas as pd

df = pd.read_parquet('output/analysis_ready.parquet')

product_rev = (
    df.groupby('product_id')['revenue']
    .sum()
    .sort_values(ascending=False)
    .reset_index()
)

total_rev = product_rev['revenue'].sum()
product_rev['rev_share'] = product_rev['revenue'] / total_rev
product_rev['cumulative_share'] = product_rev['rev_share'].cumsum()

pct80_cutoff = (product_rev['cumulative_share'] <= 0.80).sum()
total_products = len(product_rev)
print(f'Top {pct80_cutoff} of {total_products} products ({pct80_cutoff/total_products:.0%}) '
      f'generate 80% of revenue (Pareto)')
print(product_rev.head())

การบันทึกตาราง KPI ทั้งหมด

import pandas as pd

# Save all KPI tables
monthly_cat.to_parquet('output/kpi_monthly_category_revenue.parquet')
region_kpi.to_parquet('output/kpi_region_summary.parquet', index=False)
retention.to_parquet('output/kpi_cohort_retention.parquet')
daily.to_parquet('output/kpi_rolling_active_users.parquet')
product_rev.to_parquet('output/kpi_product_ranking.parquet', index=False)

# Also a CSV summary for sharing
region_kpi.to_csv('output/region_summary.csv', index=False)

print('All KPI tables saved:')
print('  kpi_monthly_category_revenue.parquet')
print('  kpi_region_summary.parquet')
print('  kpi_cohort_retention.parquet')
print('  kpi_rolling_active_users.parquet')
print('  kpi_product_ranking.parquet')

การตรวจสอบ KPI ทางสถิติ

import pandas as pd
import numpy as np
from scipy import stats

df = pd.read_parquet('output/analysis_ready.parquet')

# Is revenue growth statistically significant?
monthly_total = df.groupby('year_month')['revenue'].sum().sort_index()
month_changes = monthly_total.diff().dropna()
stat, p = stats.ttest_1samp(month_changes, popmean=0)
print(f'Monthly growth test: mean={month_changes.mean():.0f}, p={p:.4f}')
print('Significant positive trend?', month_changes.mean() > 0 and p < 0.05)

# ANOVA: do regions differ significantly?
groups = [g['revenue'].values for _, g in df.groupby('region')]
f, p_anova = stats.f_oneway(*groups)
print(f'Region ANOVA: F={f:.3f}, p={p_anova:.4f}')

การคำนวณอัตราการเติบโตเมื่อเทียบกับเดือนก่อน

import pandas as pd

df = pd.read_parquet('output/analysis_ready.parquet')

monthly_total = (
    df.groupby('year_month')['revenue'].sum().sort_index()
)

growth = monthly_total.pct_change() * 100
trend = growth.rolling(3, min_periods=1).mean()

kpi_growth = pd.DataFrame({
    'revenue': monthly_total,
    'mom_growth_pct': growth.round(2),
    'trend_3m_avg': trend.round(2)
})

print(kpi_growth.tail(6))
print(f'\nBest month: {monthly_total.idxmax()} (${monthly_total.max():,.0f})')
print(f'Worst month: {monthly_total.idxmin()} (${monthly_total.min():,.0f})')

การตรวจจับความผิดปกติแบบอัตโนมัติ

import pandas as pd
import numpy as np

df = pd.read_parquet('output/analysis_ready.parquet')
monthly = df.groupby('year_month')['revenue'].sum().sort_index()

rolling_mean = monthly.rolling(6, min_periods=3).mean()
rolling_std = monthly.rolling(6, min_periods=3).std()

anomalies = monthly[
    (monthly > rolling_mean + 2 * rolling_std) |
    (monthly < rolling_mean - 2 * rolling_std)
]
print('Anomalous months (>2 std from 6-month rolling mean):')
if len(anomalies) > 0:
    print(anomalies.round(0))
else:
    print('None detected')

ตารางสรุป KPI

import pandas as pd

df = pd.read_parquet('output/analysis_ready.parquet')
retention = pd.read_parquet('output/kpi_cohort_retention.parquet')
region_kpi = pd.read_parquet('output/kpi_region_summary.parquet')

summary = {
    'Total Revenue': f'${df["revenue"].sum():,.0f}',
    'Total Orders': f'{df["order_id"].nunique():,}',
    'Unique Customers': f'{df["customer_id"].nunique():,}',
    'Avg Order Value': f'${df["revenue"].mean():.2f}',
    'Best Region': region_kpi.iloc[0]["region"],
    'Best Category': df.groupby("category")["revenue"].sum().idxmax(),
    'Avg Month-3 Retention': f'{retention.get(3, pd.Series([0])).mean():.1%}'
}

for k, v in summary.items():
    print(f'{k:25s}: {v}')

ตรวจสอบความเข้าใจ

ทบทวนบทเรียน