케이스 스터디 면접

매출 = 방문자 × 전환율 × 객단가

각 요소의 전월 대비 변화 확인

import pandas as pd
import numpy as np
 
DATA_PATH = '/data/'
orders = pd.read_csv(DATA_PATH + 'src_orders.csv', parse_dates=['created_at'])
order_items = pd.read_csv(DATA_PATH + 'src_order_items.csv')
users = pd.read_csv(DATA_PATH + 'src_users.csv')
 
# 1. 일별 매출 추이
daily_revenue = order_items.merge(
    orders[['order_id', 'created_at', 'status']], on='order_id'
).query("status == 'Complete'")
 
daily_revenue['date'] = daily_revenue['created_at'].dt.date
daily_summary = daily_revenue.groupby('date').agg(
    revenue=('sale_price', 'sum'),
    orders=('order_id', 'nunique'),
    aov=('sale_price', lambda x: x.sum() / x.count())
).reset_index()
 
print("=== 일별 매출 추이 ===")
print(daily_summary.tail(60))
 
# 2. 채널별 분해 (가정: traffic_source로)
df = order_items.merge(orders, on='order_id').merge(users, on='user_id')
channel_comparison = df.groupby([
    df['created_at'].dt.to_period('M'),
    'traffic_source'
])['sale_price'].sum().unstack()
 
print("\n=== 채널별 월간 매출 ===")
print(channel_comparison)
 
# 3. 카테고리별 분해
df_products = df.merge(
    pd.read_csv(DATA_PATH + 'src_products.csv')[['product_id', 'category']],
    on='product_id'
)
category_comparison = df_products.groupby([
    df_products['created_at'].dt.to_period('M'),
    'category'
])['sale_price'].sum().unstack()
 
print("\n=== 카테고리별 월간 매출 ===")
print(category_comparison)
 
# 4. 신규 vs 기존 고객
df['is_new'] = df.groupby('user_id')['created_at'].transform('min') == df['created_at']
customer_type = df.groupby([
    df['created_at'].dt.to_period('M'),
    'is_new'
])['sale_price'].sum().unstack()
 
print("\n=== 신규/기존 고객별 매출 ===")
print(customer_type)