Heatmap Visualization

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
 
# Font settings (use default if font is not available)
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
 
# Korean font settings
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
 
# Default style
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 extraction
df['day_of_week'] = df['created_at'].dt.dayofweek + 1  # 1=Monday
df['hour_of_day'] = df['created_at'].dt.hour
 
# Grouping
hourly = df.groupby(['day_of_week', 'hour_of_day']).size().reset_index(name='order_count')

# Create pivot table (day of week × hour)
heatmap_data = hourly.pivot(
    index='day_of_week',
    columns='hour_of_day',
    values='order_count'
)
heatmap_data = heatmap_data.fillna(0).astype(float)
 
# Day of week label mapping (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]
 
# Draw heatmap
plt.figure(figsize=(16, 6))
sns.heatmap(
    heatmap_data,
    annot=True,              # Show values
    fmt='.0f',               # Integer format
    cmap='YlOrRd',           # Color palette
    cbar_kws={'label': 'Order Count'},
    linewidths=0.5           # Cell divider
)
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()
 
# Insights
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()

# Create upper triangle mask
mask = np.triu(np.ones_like(corr_matrix, dtype=bool))
 
plt.figure(figsize=(10, 8))
sns.heatmap(
    corr_matrix,
    mask=mask,               # Apply mask
    annot=True,
    fmt='.2f',
    cmap='RdYlBu_r',
    vmin=-1, vmax=1,
    center=0,
    square=True
)
plt.title('Correlation Heatmap (Lower Triangle)', 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. Calculate first purchase month (Cohort) per user
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. Aggregate monthly activity
cohort_data = df.groupby(['cohort', 'order_month'])['user_id'].nunique().reset_index()
cohort_data.columns = ['cohort', 'order_month', 'active_users']
 
# 3. Calculate months elapsed
cohort_data['months_since_first'] = (cohort_data['order_month'] - cohort_data['cohort']).apply(lambda x: x.n)
 
# Create pivot table
cohort_pivot = cohort_data.pivot(
    index='cohort',
    columns='months_since_first',
    values='active_users'
)
 
# Calculate retention rate based on first month
cohort_retention = cohort_pivot.div(cohort_pivot[0], axis=0) * 100
 
# Heatmap
plt.figure(figsize=(14, 8))
sns.heatmap(
    cohort_retention,
    annot=True,
    fmt='.1f',
    cmap='YlGnBu',
    cbar_kws={'label': 'Retention Rate (%)'},
    linewidths=0.5
)
plt.title('Monthly Retention Rate by Cohort', fontsize=16, fontweight='bold')
plt.xlabel('Months Since Signup', fontsize=12)
plt.ylabel('Cohort (Signup Month)', fontsize=12)
plt.tight_layout()
plt.show()
 
# Insights
print("📊 Retention Analysis:")
print(f"- Average retention after 1 month: {cohort_retention[1].mean():.1f}%")
print(f"- Average retention after 3 months: {cohort_retention[3].mean():.1f}%")
print(f"- Average retention after 6 months: {cohort_retention[6].mean():.1f}%")