히트맵 시각화

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
 
# 한글 폰트 설정 (폰트가 없을 경우 기본값 사용)
plt.rcParams['font.family'] = 'sans-serif' 
plt.rcParams['axes.unicode_minus'] = False
 
# Load Data
orders = pd.read_csv('src_orders.csv', parse_dates=['created_at'])
items = pd.read_csv('src_order_items.csv')
products = pd.read_csv('src_products.csv')
 
# Merge for Heatmap Analysis
df = orders.merge(items, on='order_id').merge(products, on='product_id')
 
# Ensure datetime conversion
df['created_at'] = pd.to_datetime(df['created_at'], format='mixed')

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
 
# 한글 폰트 설정
plt.rcParams['font.family'] = 'NanumGothic'  # Linux
# plt.rcParams['font.family'] = 'AppleGothic'  # macOS
# plt.rcParams['font.family'] = 'Malgun Gothic'  # Windows
plt.rcParams['axes.unicode_minus'] = False
 
# 기본 스타일
plt.style.use('seaborn-v0_8-whitegrid')

SELECT
    EXTRACT(DAYOFWEEK FROM created_at) as day_of_week,
    EXTRACT(HOUR FROM created_at) as hour_of_day,
    COUNT(*) as order_count
FROM `project.dataset.src_orders`
WHERE DATE(created_at) >= '2023-01-01'
GROUP BY day_of_week, hour_of_day
ORDER BY day_of_week, hour_of_day

# datetime 추출
df['day_of_week'] = df['created_at'].dt.dayofweek + 1  # 1=월요일
df['hour_of_day'] = df['created_at'].dt.hour
 
# 그룹화
hourly = df.groupby(['day_of_week', 'hour_of_day']).size().reset_index(name='order_count')

# 피벗 테이블 생성 (요일 × 시간)
heatmap_data = hourly.pivot(
    index='day_of_week',
    columns='hour_of_day',
    values='order_count'
)
heatmap_data = heatmap_data.fillna(0).astype(float)
 
# 요일 라벨 매핑 (English Labels)
day_labels = ['Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat']
heatmap_data.index = [day_labels[int(i)-1] for i in heatmap_data.index]
 
# 히트맵 그리기
plt.figure(figsize=(16, 6))
sns.heatmap(
    heatmap_data,
    annot=True,              # 값 표시
    fmt='.0f',               # 정수 형식
    cmap='YlOrRd',           # 색상 팔레트
    cbar_kws={'label': 'Order Count'},
    linewidths=0.5           # 셀 구분선
)
plt.title('Orders by Day and Hour', fontsize=16, fontweight='bold')
plt.xlabel('Hour of Day', fontsize=12)
plt.ylabel('Day of Week', fontsize=12)
plt.tight_layout()
plt.show()
 
# 인사이트
print(f"📊 Peak Time: {heatmap_data.max().idxmax()}h")
print(f"📊 Peak Day: {heatmap_data.max(axis=1).idxmax()}")

SELECT
    p.category,
    EXTRACT(MONTH FROM DATE(o.created_at)) as month,
    SUM(oi.sale_price) as revenue
FROM src_orders o
JOIN src_order_items oi ON o.order_id = oi.order_id
JOIN src_products p ON oi.product_id = p.product_id
WHERE EXTRACT(YEAR FROM o.created_at) = 2023
GROUP BY category, month
ORDER BY category, month

# Month & Revenue Calculation
df['month'] = df['created_at'].dt.month
df['revenue'] = df['sale_price']
 
# Group by Category & Month
monthly_cat = df.groupby(['category', 'month'])['revenue'].sum().reset_index()
 
# Pivot Table
heatmap_data = monthly_cat.pivot(index='category', columns='month', values='revenue')
heatmap_data = heatmap_data.fillna(0)
 
# Month Labels (English)
month_labels = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun',
                'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']
heatmap_data.columns = [month_labels[int(i)-1] for i in heatmap_data.columns]
 
# Top 10 Categories
top_categories = heatmap_data.sum(axis=1).nlargest(10).index
heatmap_data_top = heatmap_data.loc[top_categories]
 
# Heatmap
plt.figure(figsize=(14, 8))
sns.heatmap(heatmap_data_top, annot=True, fmt='.0f', cmap='Blues',
            cbar_kws={'label': 'Revenue ($)'}, linewidths=0.5)
plt.title('Monthly Revenue by Category (Top 10)', fontsize=16, fontweight='bold')
plt.xlabel('Month', fontsize=12)
plt.ylabel('Category', fontsize=12)
plt.tight_layout()
plt.show()

# Calculate Derived Metrics
df['profit'] = df['sale_price'] - df['cost']
df['profit_margin'] = (df['profit'] / df['sale_price']).fillna(0)
 
# Select Numeric Columns
numeric_cols = ['retail_price', 'cost', 'sale_price', 'profit', 'profit_margin']
corr_data = df[numeric_cols]
 
# Correlation Matrix
corr_matrix = corr_data.corr()
 
# Heatmap
plt.figure(figsize=(10, 8))
sns.heatmap(
    corr_matrix,
    annot=True,              # Show values
    fmt='.2f',               # 2 decimals
    cmap='RdYlBu_r',         # Palette
    vmin=-1, vmax=1,         # Range
    center=0,                # Center
    square=True,             # Square cells
    linewidths=0.5
)
plt.title('Correlation Heatmap', fontsize=16, fontweight='bold')
plt.tight_layout()
plt.show()

# 상삼각 마스크 생성
mask = np.triu(np.ones_like(corr_matrix, dtype=bool))
 
plt.figure(figsize=(10, 8))
sns.heatmap(
    corr_matrix,
    mask=mask,               # 마스크 적용
    annot=True,
    fmt='.2f',
    cmap='RdYlBu_r',
    vmin=-1, vmax=1,
    center=0,
    square=True
)
plt.title('상관관계 히트맵 (하삼각)', fontsize=16, fontweight='bold')
plt.tight_layout()
plt.show()

WITH user_cohorts AS (
    SELECT
        user_id,
        DATE_TRUNC(MIN(DATE(created_at)), MONTH) as cohort_month
    FROM src_orders
    GROUP BY user_id
),
user_activities AS (
    SELECT
        o.user_id,
        DATE_TRUNC(DATE(o.created_at), MONTH) as activity_month
    FROM src_orders o
    GROUP BY o.user_id, DATE_TRUNC(DATE(o.created_at), MONTH)
)
SELECT
    FORMAT_DATE('%Y-%m', c.cohort_month) as cohort,
    DATE_DIFF(a.activity_month, c.cohort_month, MONTH) as months_since_first,
    COUNT(DISTINCT a.user_id) as active_users
FROM user_cohorts c
JOIN user_activities a ON c.user_id = a.user_id
WHERE c.cohort_month >= '2023-01-01'
GROUP BY cohort, months_since_first
ORDER BY cohort, months_since_first

# 1. 유저별 최초 구매월(Cohort) 계산
df['order_month'] = df['created_at'].dt.to_period('M')
user_cohort = df.groupby('user_id')['order_month'].min().rename('cohort')
df = df.merge(user_cohort, on='user_id')
 
# 2. 월별 활동 집계
cohort_data = df.groupby(['cohort', 'order_month'])['user_id'].nunique().reset_index()
cohort_data.columns = ['cohort', 'order_month', 'active_users']
 
# 3. 경과 월 계산
cohort_data['months_since_first'] = (cohort_data['order_month'] - cohort_data['cohort']).apply(lambda x: x.n)
 
# 피벗 테이블 생성
cohort_pivot = cohort_data.pivot(
    index='cohort',
    columns='months_since_first',
    values='active_users'
)
 
# 첫 달 기준 리텐션율 계산
cohort_retention = cohort_pivot.div(cohort_pivot[0], axis=0) * 100
 
# 히트맵
plt.figure(figsize=(14, 8))
sns.heatmap(
    cohort_retention,
    annot=True,
    fmt='.1f',
    cmap='YlGnBu',
    cbar_kws={'label': '리텐션율 (%)'},
    linewidths=0.5
)
plt.title('코호트별 월별 리텐션율', fontsize=16, fontweight='bold')
plt.xlabel('가입 후 경과 월', fontsize=12)
plt.ylabel('코호트 (가입 월)', fontsize=12)
plt.tight_layout()
plt.show()
 
# 인사이트
print("📊 리텐션 분석:")
print(f"- 1개월 후 평균 리텐션: {cohort_retention[1].mean():.1f}%")
print(f"- 3개월 후 평균 리텐션: {cohort_retention[3].mean():.1f}%")
print(f"- 6개월 후 평균 리텐션: {cohort_retention[6].mean():.1f}%")