総合演習：顧客分析レポート

# === 品質チェック ===
print("=== 購買データ品質 ===")
print(f"レコード数: {len(orders)}")
print(f"欠損値:\n{orders.isnull().sum()}")
print(f"ステータス分布:\n{orders['status'].value_counts()}")

# === クレンジング ===
# 欠損値処理
orders['amount'] = orders['amount'].fillna(orders['amount'].median())
orders['channel'] = orders['channel'].fillna('unknown')

# 顧客マスタの欠損
customers['age'] = customers.groupby('region')['age'].transform(
    lambda x: x.fillna(x.median())
)

# === 結合 ===
df = pd.merge(orders, customers, on='customer_id', how='left')

# === 特徴量追加 ===
df['order_date'] = pd.to_datetime(df['order_date'])
df['year_month'] = df['order_date'].dt.to_period('M')
df['year'] = df['order_date'].dt.year
df['month'] = df['order_date'].dt.month
df['day_of_week'] = df['order_date'].dt.day_name()
df['is_weekend'] = df['order_date'].dt.dayofweek >= 5
df['age_group'] = pd.cut(df['age'], bins=[0, 25, 35, 45, 55, 100],
                          labels=['18-24', '25-34', '35-44', '45-54', '55+'])

# 完了注文のみ
df_completed = df[df['status'] == 'completed'].copy()
print(f"\n分析対象: {len(df_completed)}件（完了注文）")

monthly = df_completed.groupby('year_month').agg(
    revenue=('amount', 'sum'),
    customers=('customer_id', 'nunique'),
    orders=('order_id', 'count'),
    avg_amount=('amount', 'mean')
).reset_index()
monthly['frequency'] = monthly['orders'] / monthly['customers']

fig, axes = plt.subplots(2, 2, figsize=(14, 8))
fig.suptitle('売上構造分解：購買頻度の低下が成長鈍化の主因', fontsize=14, fontweight='bold')

for ax, (col, label, color) in zip(axes.flatten(), [
    ('revenue', '売上（円）', '#1976D2'),
    ('customers', 'ユニーク顧客数', '#388E3C'),
    ('frequency', '購買頻度（回/人）', '#F57C00'),
    ('avg_amount', '客単価（円）', '#7B1FA2')
]):
    ax.plot(range(len(monthly)), monthly[col], marker='o', color=color, linewidth=2)
    ax.set_title(label, fontsize=11)
    ax.grid(axis='y', alpha=0.3)
    ax.spines['top'].set_visible(False)
    ax.spines['right'].set_visible(False)

plt.tight_layout()
plt.show()

ref_date = df_completed['order_date'].max() + pd.Timedelta(days=1)
rfm = df_completed.groupby('customer_id').agg(
    recency=('order_date', lambda x: (ref_date - x.max()).days),
    frequency=('order_id', 'nunique'),
    monetary=('amount', 'sum')
).reset_index()

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)

def assign_segment(row):
    if row['r_score'] >= 3 and row['f_score'] >= 3:
        return 'Champion'
    elif row['r_score'] >= 3 and row['f_score'] <= 2:
        return 'New/Promising'
    elif row['r_score'] <= 2 and row['f_score'] >= 3:
        return 'At Risk'
    else:
        return 'Lost'

rfm['segment'] = rfm.apply(assign_segment, axis=1)

# セグメント別サマリー
seg_summary = rfm.groupby('segment').agg(
    count=('customer_id', 'count'),
    avg_recency=('recency', 'mean'),
    avg_frequency=('frequency', 'mean'),
    avg_monetary=('monetary', 'mean'),
    total_monetary=('monetary', 'sum')
).round(0)
seg_summary['revenue_share'] = (seg_summary['total_monetary'] / seg_summary['total_monetary'].sum() * 100).round(1)

print(seg_summary)

fig, axes = plt.subplots(1, 2, figsize=(14, 5))
colors = {'Champion': '#4CAF50', 'New/Promising': '#2196F3', 'At Risk': '#FF9800', 'Lost': '#F44336'}

# 顧客数
seg_counts = rfm['segment'].value_counts()
axes[0].bar(seg_counts.index, seg_counts.values, color=[colors[s] for s in seg_counts.index])
axes[0].set_title('セグメント別顧客数', fontsize=12, fontweight='bold')
axes[0].set_ylabel('顧客数')

# 売上構成
seg_revenue = rfm.groupby('segment')['monetary'].sum()
axes[1].pie(seg_revenue, labels=seg_revenue.index, autopct='%1.1f%%',
            colors=[colors[s] for s in seg_revenue.index], startangle=90)
axes[1].set_title('セグメント別売上構成', fontsize=12, fontweight='bold')

plt.tight_layout()
plt.show()

# チャネル別KPI
channel_kpi = df_completed.groupby('channel').agg(
    orders=('order_id', 'count'),
    revenue=('amount', 'sum'),
    avg_amount=('amount', 'mean'),
    customers=('customer_id', 'nunique')
).round(0)
channel_kpi['revenue_per_customer'] = (channel_kpi['revenue'] / channel_kpi['customers']).round(0)

# デバイス別KPI
device_kpi = df_completed.groupby('device').agg(
    orders=('order_id', 'count'),
    revenue=('amount', 'sum'),
    avg_amount=('amount', 'mean')
).round(0)

fig, axes = plt.subplots(1, 2, figsize=(14, 5))

# チャネル別
channel_kpi.sort_values('revenue_per_customer', ascending=True).plot(
    kind='barh', y='revenue_per_customer', ax=axes[0], color='#1976D2', legend=False
)
axes[0].set_title('チャネル別 顧客あたり売上', fontsize=12, fontweight='bold')
axes[0].set_xlabel('売上（円/人）')

# デバイス別
device_kpi.plot(kind='bar', y='avg_amount', ax=axes[1], color='#388E3C', legend=False)
axes[1].set_title('デバイス別 平均注文金額', fontsize=12, fontweight='bold')
axes[1].set_ylabel('平均金額（円）')
axes[1].tick_params(axis='x', rotation=0)

plt.tight_layout()
plt.show()

# 統計検定：デスクトップ vs モバイルの注文金額
desktop = df_completed[df_completed['device'] == 'desktop']['amount']
mobile = df_completed[df_completed['device'] == 'mobile']['amount']
t, p = stats.ttest_ind(desktop, mobile, equal_var=False)
print(f"デスクトップ vs モバイル: t={t:.3f}, p={p:.4f}")