Main Quest06_Project_Tableau 태블로 시각화 6-2

1. 운영팀: Sales 분석 대시보드

목적: 일별/월별 매출, 주문/반품 현황, 전체 비즈니스 흐름 파악

주요 분석 내용

• 전체 매출 및 순매출 (반품 제외)

• 주문 수, 반품 수, 반품률

• 일/주/월별 매출 트렌드

• 사용자당 평균 주문 수 & 평균 매출 (AOV)

• 성별/연령대별 매출 분포

필요한 테이블 및 컬럼

• orders.csv

• order_id, user_id, status, created_at, returned_at, delivered_at, num_of_item

• order_items.csv

• order_id, inventory_item_id, returned_at, status

• inventory_items.csv

• cost, product_retail_price

• users.csv

• age, gender

---

2. 마케팅팀: 고객 구매행동 및 리텐션 분석

목적: 유입 경로별 전환율 및 리텐션 분석, 마케팅 성과 파악

주요 분석 내용

• 트래픽 소스별 전환율 (가입 → 구매)

• 채널별 고객 수 및 평균 구매 수

• 첫 구매까지 소요 시간

• 재구매율 & LTV

• 이벤트 기반 퍼널 분석 (page_view → checkout → purchase)

필요한 테이블 및 컬럼

• users.csv

• id, traffic_source, created_at

• orders.csv

• user_id, created_at, returned_at, delivered_at

• order_items.csv

• user_id, order_id

• events.csv

• user_id, event_type, created_at, session_id, traffic_source

---

3. 판매팀: 상품 및 카테고리 성과분석

목적: 어떤 상품/카테고리가 잘 팔리는지 파악해 재고관리 및 가격전략에 반영

주요 분석 내용

• 상품별/카테고리별 판매량 및 매출

• 상품별 반품률

• 브랜드별 가격 vs 판매량 분석

• 재고소진 속도 분석 (회전율)

필요한 테이블 및 컬럼

• order_items.csv

• product_id, inventory_item_id, returned_at

• inventory_items.csv

• product_id, sold_at, cost, product_category, product_name, product_brand, product_retail_price

• products.csv

• id, name, category, retail_price, brand, sku

---

4. 물류팀: 물류현황 및 배송 성과 분석

목적: 배송 지연, 반품 이슈 파악 및 물류센터별 성과 관리

주요 분석 내용

• 평균 배송 시간 (주문 → 배송 → 도착)

• 지역별 배송 시간 & 클레임 분석

• 물류센터별 배송 처리량

• 배송 지연율, 반품으로 이어지는 배송지연 비율

필요한 테이블 및 컬럼

• order_items.csv

• created_at, shipped_at, delivered_at, returned_at, product_id

• inventory_items.csv

• product_distribution_center_id

• distribution_centers.csv

• id, name, latitude, longitude

• users.csv

• postal_code, state, city, latitude, longitude

---

필요 테이블
order_items, orders, users, inventory_items, products, distribution_centers

필요컬럼
created_at, order_items, orders, shipped_at, delivered_at, order_id, user_id, returned_at, users.state, users.city, users.country, inventory_item_id, product_distribution_center_id, distribution_centers.name, status, product_category, product_name, product_brand, num_of_item

delivered_at 컬럼이 여러 테이블에 존재하면서 merge 후 이름이 바뀌었기 때문입니다. 이를 해결하고 다시 분석용 테이블을 생성. ​​

delivered_at 컬럼이 order_items과 orders 두 테이블에 존재해, 병합 후 각각 delivered_at_item, delivered_at_order로 바뀜.

배송 도착 시간으로는 order_items의 delivered_at_item을 사용하는 것이 적절하므로, 이를 기준으로 다시 분석을 수행. ​​

또 다른 충돌이 있네요. returned_at 컬럼 역시 병합 시 이름이 변경되었습니다. 해당 컬럼도 어떤 이름으로 존재하는지 먼저 확인한 후 다시 처리. ​​

returned_at 컬럼도 order_items과 orders 테이블에 존재해, 현재는 각각 returned_at_item, returned_at_order로 되어 있다.

반품 발생 여부는 품목 단위의 정보가 더 정밀하므로 returned_at_item을 기준으로 다시 처리. ​​

센터별 배송 요약 및 SPI 지수

 

delay_to_return_rateon_time_ratespi_score

0.30395913154533843	0.6446562287270252	0.7435897435897436
0.28284854563691075	0.6431639226914818	0.7455261274158913
0.2868609317745594	0.647886458589334	0.748328827721799
0.2791302292602222	0.6452883970489605	0.7475687458081824

 

분석용 테이블과 요약 데이터가 성공적으로 생성되었습니다. 아래와 같이 태블로 대시보드 구성에 활용할 수 있다:

아래는 추가로 생성한 데이터

• logistics_analysis.csv: 주문별 배송, 지역, 지연/반품 여부 데이터
• distribution_center_summary.csv: 센터별 배송 성과 요약 + SPI 지수

---

태블로 대시보드 구성 예시

A. 지역별 맵 시각화

• 사용 데이터: logistics_analysis.csv
• 사용 필드: city, state, country, latitude, longitude, delivery_days, delayed, returned
• 표현 방식:
  • delivery_days: 색상 (배송 속도 시각화)
  • delayed, returned: 마크 크기 or 도형 (문제지역 강조)

B. 센터별 KPI 카드

• 사용 데이터: distribution_center_summary.csv
• 필드:
  • total_orders, delay_rate, return_rate, delay_to_return_rate, spi_score
• 표현 방식:
  • KPI 카드 또는 테이블
  • 조건부 서식으로 SPI 지수 색상 차등 표시

C. SPI 지수 트렌드 및 비교

• 사용 데이터: distribution_center_summary.csv
• 필드:
  • name, spi_score, on_time_rate, return_rate
• 표현 방식:
  • 막대 그래프 (센터별 비교)
  • 시간 축이 필요한 경우 logistics_analysis.csv에서 시간 필드를 기반으로 roll-up 필요

---

1. 태블로용 스키마 매핑,

A. logistics_analysis.csv

목적: 주문별 배송 지연, 반품 여부 및 지역 기반 시각화에 활용
레코드 단위: 주문 품목

필드명	데이터 타입	설명	태블로 역할
order_id	String	주문 고유 ID	세부 수준 식별자
user_id	String	사용자 고유 ID	세부 수준 식별자
city	String	도시명	지역 차원 필드
state	String	주/광역시	지역 차원 필드
country	String	국가	지역 필터
name	String	물류센터 이름	범주형 차원
delayed	Boolean	배송 지연 여부 (True/False)	이진 조건 필터
returned	Boolean	반품 여부	이진 조건 필터
delivery_days	Integer	배송 소요 일수	측정값 (연속형)
latitude	Float	사용자 위도	맵 시각화 (지리 필드)
longitude	Float	사용자 경도	맵 시각화 (지리 필드)

B. distribution_center_summary.csv

목적: 센터별 KPI 및 SPI 지수 비교 시각화에 활용
레코드 단위: 물류센터

필드명	데이터 타입	설명	태블로 역할
name	String	물류센터 이름	차원
total_orders	Integer	총 주문 수	KPI 지표
delayed_orders	Integer	지연 배송 수	KPI 지표
returned_orders	Integer	반품 수	KPI 지표
delay_rate	Float	지연율 (0~1)	KPI / 비율 시각화
return_rate	Float	반품율 (0~1)	KPI / 비율 시각화
delay_to_return_rate	Float	지연 중 반품으로 이어진 비율	진단 지표
on_time_rate	Float	정시 배송률	성과 지표
spi_score	Float	SPI 지수 (0~1 사이)	종합 성과 지표 (KPI 카드, 조건부 색상에 활용)

 

2. 태블로 시각화 예시 구성 맵

시각화 제목	사용 데이터	주요 필드	시각화 유형
지역별 배송 성과 맵	logistics_analysis	city, latitude, longitude, delivery_days, delayed	심볼맵 (색상: 배송일수, 모양: 지연 여부)
센터별 KPI 테이블	distribution_center_summary	name, delay_rate, return_rate, spi_score	텍스트 테이블 + 조건부 색상
SPI 지수 비교 그래프	distribution_center_summary	name, spi_score	막대그래프
배송 지연 → 반품 분석	distribution_center_summary	delay_to_return_rate	원형 차트 or 히트맵
배송 트렌드 분석	logistics_analysis	delivery_days, state, delayed, returned	박스플롯 or 선형 그래프 (시간 컬럼 필요시 추가 가능)

3. 태블로에서 사용할 계산 필드 예시

1. 지연 여부 (텍스트 변환)
    
   // 배송 지연 여부 텍스트
   IF [delayed] THEN "지연" ELSE "정상" END
   
   
2. 반품 여부 (텍스트 변환)
   // 반품 여부 텍스트
   IF [returned] THEN "반품" ELSE "정상 배송" END
   
   

필드 계산 공식, 

SPI 시각화 상세 설계,

1. SPI 등급 레벨 (조건부 색상용)
   // SPI 등급
   IF [spi_score] >= 0.8 THEN "매우 우수"
   ELSEIF [spi_score] >= 0.7 THEN "우수"
   ELSEIF [spi_score] >= 0.6 THEN "보통"
   ELSE "개선 필요"
   END
2. 배송 정시율 (On-Time Rate)
   // 정시 배송률
   1 - [delay_rate]
   
   
   
3. 지연 → 반품 전환율
   // 지연 중 반품 전환율
   IF [delayed_orders] > 0 THEN
       [returned_orders] / [delayed_orders]
   ELSE
       0
   END
   
   
4. SPI 지수 계산 공식 (커스터마이징 가능)
   // SPI 지수 (정시율 60%, 반품율 40% 반영 예시)
   (1 - [delay_rate]) * 0.6 + (1 - [return_rate]) * 0.4
   원칙:
   SPI는 "정시율이 높고, 반품률이 낮을수록 높아지는 종합 점수"로 설계.
   
   
5. 배송 소요 기간 등급화
   // 배송 소요일 등급
   IF [delivery_days] <= 2 THEN "빠름"
   ELSEIF [delivery_days] <= 4 THEN "보통"
   ELSE "느림"
   END
6. 배송 성공률
   // 배송 성공률 = (전체 - 반품) / 전체
   1 - ([returned_orders] / [total_orders])

---

배송 지연 예측 머신러닝 모델 설계

목표 정의

• 예측 대상(Target): 배송 지연 여부 (delayed)
• 문제 유형: 이진 분류(Binary Classification)
• 사용 목적: 물류팀이 사전에 지연 가능성이 높은 주문을 예측 → 조치
  
  

1. 예측 모델 개요

항목	내용
입력 (Features)	주문 시점, 사용자 위치, 센터, 제품 정보 등
출력 (Target)	delayed (True / False)
모델 종류	Decision Tree, Random Forest, XGBoost (설명력 + 성능 고려)

 

---

2. 특성(Feature) 선정

데이터셋: logistics_analysis.csv + 추가 파생 가능

Feature 이름	설명	타입
delivery_days	정답 기준 생성 시 사용, 예측 시 미사용	제거
city, state, country	지역 정보	범주형
name	물류센터 이름	범주형
user_id	사용자 식별자	제거 (비식별화 목적)
order_id	주문 ID	제거 (모델엔 무의미)
latitude, longitude	위치 정보 (수치형 또는 거리 기반 피처 생성)	수치형
created_at	주문 시각 → 파생: 요일, 시간대	범주형/수치형 파생

+ 파생 특성 추천

• order_hour, order_dayofweek (시간대/요일 효과)
• is_weekend, is_night (주말/야간 여부)
• region_cluster (KMeans로 지역 클러스터링)

---

3. 전처리 설계

1. 범주형 변수 → One-Hot Encoding 또는 Label Encoding
2. 시간 변수 → hour, weekday 등 파생 후 수치화
3. 결측치 처리 → delivered_at, returned_at 제외 후 drop
4. delivery_days, returned 등 후행정보는 학습 시 제거

---

4. 모델 학습 파이프라인

from sklearn.model_selection import train_test_split
from sklearn.ensemble import RandomForestClassifier
from sklearn.metrics import classification_report
import pandas as pd

# 데이터 불러오기
df = pd.read_csv("logistics_analysis.csv")

# 파생변수 생성
df['created_at'] = pd.to_datetime(df['created_at'])
df['order_hour'] = df['created_at'].dt.hour
df['order_weekday'] = df['created_at'].dt.weekday
df['is_weekend'] = df['order_weekday'] >= 5

# 사용 컬럼 선정
X = df[['order_hour', 'order_weekday', 'is_weekend', 'latitude', 'longitude', 'name', 'state']]
y = df['delayed']

# 인코딩
X = pd.get_dummies(X)

# 학습 분할
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42)

# 모델 학습
model = RandomForestClassifier(n_estimators=100, random_state=42)
model.fit(X_train, y_train)

# 평가
y_pred = model.predict(X_test)
print(classification_report(y_test, y_pred))

---

5. 활용 방안

활용 방안 / 설명내

태블로 예측 결과 연결	예측 결과 CSV 저장 → 태블로에서 경고 표시
지연 가능성 높은 주문 자동 알림	특정 threshold 이상 예측된 주문 알림
물류팀 우선 배송 대상 자동 선택	예측값 기반 우선순위 조정 가능

---

6. 추가 추천

• XGBoost, LightGBM 등으로 정밀도 향상
• SHAP 값을 통한 설명 가능한 AI 해석
• state, name 등의 교호작용 항목 추가
• 과거 배송량, 센터별 평균 배송시간 같은 외부 지표도 모델에 결합 가능