Project⚡️/Team Project
[Final 데이터 분석] 쿼리문 정리⚡️
파카파오
2024. 7. 28. 17:18
✅ 1. 고객 평점 조사
주문 상태가 '배송완료' 인 것들 중에서 리뷰 점수가 5인 주문의 갯수와 비중을 나타내기
WITH cte AS (
SELECT date_format(review_creation_date, '%Y-%m') AS yearmonth,
count(DISTINCT CASE WHEN review_score = 5 THEN review_id ELSE null END) AS score_5,
count(DISTINCT order_id) AS count_all,
round(
count(DISTINCT CASE WHEN review_score = 5 THEN review_id ELSE null END)*100.0
/ count(DISTINCT order_id), 2
) AS percentage_score5
FROM olist_df_fine
WHERE date_format(review_creation_date, '%Y-%m') IS NOT NULL
AND order_status = 'delivered'
GROUP BY date_format(review_creation_date, '%Y-%m')
)
SELECT *
FROM cte
ORDER BY yearmonth
olist에서 한 고객이 여러번 주문을 하게 만들었다
따라서 중복제거 후 고객의 수는 3만9천명
고객의 수에 대해서 조사하려면 distinct customer_id를 사용해야 하고
주문수에 대해서 조사하려면 distinct order_id를 사용해야 한다
distinct order id가 필요한 이유
만약 사용하지 않는다면 1건의 주문에서 여러개의 물건을 산 것을 따로따로의 주문으로 인식하게 될 수 있다
✅ 파이썬에서 날짜 형식 관련 함수
# 날짜 형식으로 바꾸기
만약 안바꿔질 경우
# to_datetime 사용 시 각 컬럼의 형식에 맞게 처리
olist_df['review_creation_date'] = pd.to_datetime(olist_df['review_creation_date'], errors='coerce', infer_datetime_format=True)
# to_datetime 사용 시 각 컬럼의 형식에 맞게 처리
olist_df2['review_creation_date'] = pd.to_datetime(olist_df2['review_creation_date'], errors='coerce', infer_datetime_format=True)
# datetime 변환 = 8개
olist_df['order_approved_at'] = pd.to_datetime(olist_df['order_approved_at'])
olist_df['order_delivered_carrier_date'] = pd.to_datetime(olist_df['order_delivered_carrier_date'])
olist_df['order_delivered_customer_date'] = pd.to_datetime(olist_df['order_delivered_customer_date'])
olist_df['order_estimated_delivery_date'] = pd.to_datetime(olist_df['order_estimated_delivery_date'])
olist_df['order_purchase_timestamp'] = pd.to_datetime(olist_df['order_purchase_timestamp'])
olist_df['shipping_limit_date'] = pd.to_datetime(olist_df['shipping_limit_date'])
olist_df['review_answer_timestamp'] = pd.to_datetime(olist_df['review_answer_timestamp'])
olist_df['review_creation_date'] = pd.to_datetime(olist_df['review_creation_date'])
# 배송시간까지 걸린 컬럼 생성
df_orders['delivery_time'] = (df_orders['order_delivered_customer_date'] - df_orders['order_purchase_timestamp']).dt.total_seconds() / (3600*24)
# 년 / 월 / 요일로 나누기
df_orders['year'] = df_orders['order_purchase_timestamp'].dt.year
df_orders['month'] = df_orders['order_purchase_timestamp'].dt.month
df_orders['day_of_week'] = df_orders['order_purchase_timestamp'].dt.day_name()
# 회원별 주문수 내림차순으로 정렬하기
orders_old_new = df_orders.groupby('customer_id')['order_id'].count().sort_values(ascending=False).reset_index()
✅ 2. 각 월별 주문수
# 각 월별 주문수 구하기
SELECT date_format(order_purchase_timestamp, '%Y-%m') AS Date,
count(DISTINCT order_id) AS order_num
FROM olist_df_fine
GROUP BY date_format(order_purchase_timestamp, '%Y-%m')
# 1월~12월별 2016,2017,2018년도의 주문수 구하기
중복제외
WITH cte AS (
SELECT order_purchase_timestamp,
date_format(order_purchase_timestamp, '%Y') AS Year,
date_format(order_purchase_timestamp, '%m') AS Month,
customer_id,
order_id
FROM olist_df_fine
)
SELECT CASE WHEN Month = 1 THEN 'Jan'
WHEN Month = 2 THEN 'Feb'
WHEN Month = 3 THEN 'Mar'
WHEN Month = 4 THEN 'Apr'
WHEN Month = 5 THEN 'May'
WHEN Month = 6 THEN 'Jun'
WHEN Month = 7 THEN 'Jul'
WHEN Month = 8 THEN 'Aug'
WHEN Month = 9 THEN 'Sep'
WHEN Month = 10 THEN 'Oct'
WHEN Month = 11 THEN 'Nov'
WHEN Month = 12 THEN 'Dec'
ELSE 0 END AS MONTH,
count(DISTINCT CASE WHEN YEAR = 2016 THEN order_id ELSE NULL END) AS order_2016_num,
count(DISTINCT CASE WHEN YEAR = 2017 THEN order_id ELSE NULL END) AS order_2017_num,
count(DISTINCT CASE WHEN YEAR = 2018 THEN order_id ELSE NULL END) AS order_2018_num
FROM cte
GROUP BY Month
✅ 3. 각 월별 고객수
# 각 월별 고객수 구하기
SELECT date_format(order_purchase_timestamp, '%Y-%m') AS Date,
count(DISTINCT customer_id) AS order_num
FROM olist_df_fine
GROUP BY date_format(order_purchase_timestamp, '%Y-%m')
✅ 4. 월별 고객의 재구매 리텐션 비율 구하기 (숫자로)
WITH cte AS (
SELECT customer_id,
order_id,
str_to_date(order_purchase_timestamp, '%Y-%m-%d') AS Date,
min(str_to_date(order_purchase_timestamp, '%Y-%m-%d')) OVER (PARTITION BY customer_id ORDER BY str_to_date(order_purchase_timestamp, '%Y-%m-%d'))
AS first_order_date
FROM olist_df_fine
), mon AS (
SELECT customer_id,
order_id,
Date,
date_format(Date, '%Y-%m-%01') AS order_month,
date_format(first_order_date, '%Y-%m-%01') AS first_order_month
FROM cte
)
SELECT first_order_month,
count(DISTINCT customer_id) AS month0,
count(DISTINCT CASE WHEN date_add(first_order_month, INTERVAL 1 month) = order_month THEN customer_id ELSE NULL END) AS month1,
count(DISTINCT CASE WHEN date_add(first_order_month, INTERVAL 2 month) = order_month THEN customer_id ELSE NULL END) AS month2,
count(DISTINCT CASE WHEN date_add(first_order_month, INTERVAL 3 month) = order_month THEN customer_id ELSE NULL END) AS month3,
count(DISTINCT CASE WHEN date_add(first_order_month, INTERVAL 4 month) = order_month THEN customer_id ELSE NULL END) AS month4,
count(DISTINCT CASE WHEN date_add(first_order_month, INTERVAL 5 month) = order_month THEN customer_id ELSE NULL END) AS month5,
count(DISTINCT CASE WHEN date_add(first_order_month, INTERVAL 6 month) = order_month THEN customer_id ELSE NULL END) AS month6,
count(DISTINCT CASE WHEN date_add(first_order_month, INTERVAL 7 month) = order_month THEN customer_id ELSE NULL END) AS month7,
count(DISTINCT CASE WHEN date_add(first_order_month, INTERVAL 8 month) = order_month THEN customer_id ELSE NULL END) AS month8,
count(DISTINCT CASE WHEN date_add(first_order_month, INTERVAL 9 month) = order_month THEN customer_id ELSE NULL END) AS month9,
count(DISTINCT CASE WHEN date_add(first_order_month, INTERVAL 10 month) = order_month THEN customer_id ELSE NULL END) AS month10,
count(DISTINCT CASE WHEN date_add(first_order_month, INTERVAL 11 month) = order_month THEN customer_id ELSE NULL END) AS month11
FROM mon
GROUP BY first_order_month
ORDER BY first_order_month
✅ 5. 월별 고객의 재구매 리텐션 비율 구하기 (비율로)
WITH cte AS (
SELECT customer_id,
order_id,
str_to_date(order_purchase_timestamp, '%Y-%m-%d') AS Date,
min(str_to_date(order_purchase_timestamp, '%Y-%m-%d')) OVER (PARTITION BY customer_id ORDER BY str_to_date(order_purchase_timestamp, '%Y-%m-%d'))
AS first_order_date
FROM olist_df_fine
), mon AS (
SELECT customer_id,
order_id,
Date,
date_format(Date, '%Y-%m-%01') AS order_month,
date_format(first_order_date, '%Y-%m-%01') AS first_order_month
FROM cte
)
SELECT first_order_month,
round(
count(DISTINCT customer_id)*100.0
/count(DISTINCT customer_id), 2) AS month0,
round(
count(DISTINCT CASE WHEN date_add(first_order_month, INTERVAL 1 month) = order_month THEN customer_id ELSE NULL END)
*100.0 / count(DISTINCT customer_id),2) AS month1,
round(
count(DISTINCT CASE WHEN date_add(first_order_month, INTERVAL 2 month) = order_month THEN customer_id ELSE NULL END)
*100.0 / count(DISTINCT customer_id),2) AS month2,
round(
count(DISTINCT CASE WHEN date_add(first_order_month, INTERVAL 3 month) = order_month THEN customer_id ELSE NULL END)
*100.0 / count(DISTINCT customer_id),2) AS month3,
round(
count(DISTINCT CASE WHEN date_add(first_order_month, INTERVAL 4 month) = order_month THEN customer_id ELSE NULL END)
*100.0 / count(DISTINCT customer_id),2) AS month4,
round(
count(DISTINCT CASE WHEN date_add(first_order_month, INTERVAL 5 month) = order_month THEN customer_id ELSE NULL END)
*100.0 / count(DISTINCT customer_id),2) AS month5,
round(
count(DISTINCT CASE WHEN date_add(first_order_month, INTERVAL 6 month) = order_month THEN customer_id ELSE NULL END)
*100.0 / count(DISTINCT customer_id),2) AS month6,
round(
count(DISTINCT CASE WHEN date_add(first_order_month, INTERVAL 7 month) = order_month THEN customer_id ELSE NULL END)
*100.0 / count(DISTINCT customer_id),2) AS month7,
round(
count(DISTINCT CASE WHEN date_add(first_order_month, INTERVAL 8 month) = order_month THEN customer_id ELSE NULL END)
*100.0 / count(DISTINCT customer_id),2) AS month8,
round(
count(DISTINCT CASE WHEN date_add(first_order_month, INTERVAL 9 month) = order_month THEN customer_id ELSE NULL END)
*100.0 / count(DISTINCT customer_id),2) AS month9,
round(
count(DISTINCT CASE WHEN date_add(first_order_month, INTERVAL 10 month) = order_month THEN customer_id ELSE NULL END)
*100.0 / count(DISTINCT customer_id),2) AS month10,
round(
count(DISTINCT CASE WHEN date_add(first_order_month, INTERVAL 11 month) = order_month THEN customer_id ELSE NULL END)
*100.0 / count(DISTINCT customer_id),2) AS month11
FROM mon
GROUP BY first_order_month
ORDER BY first_order_month
✅ 파이썬으로 고객의 재구매 리텐션 비율 구하기
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
# 데이터 로드
data = olist_cohort
data['order_purchase_timestamp'] = pd.to_datetime(data['order_purchase_timestamp'])
# 주문 날짜의 월을 추출합니다.
data['order_purchase_month'] = data['order_purchase_timestamp'].dt.to_period('M')
# 첫 구매 월별로 코호트를 만듭니다.
data['cohort_month'] = data.groupby('customer_id')['order_purchase_month'].transform('min')
# 월별로 인덱스를 생성합니다.
data['cohort_index'] = (data['order_purchase_month'] - data['cohort_month']).apply(lambda x: x.n)
# 코호트별로 고객 수를 계산합니다.
cohort_data = data.groupby(['cohort_month', 'cohort_index']).agg({
'customer_id': pd.Series.nunique
}).reset_index()
# 피벗 테이블을 생성하여 코호트 차트를 만듭니다.
cohort_pivot = cohort_data.pivot_table(index='cohort_month', columns='cohort_index', values='customer_id')
# 퍼센트 기준으로 변환합니다.
cohort_size = cohort_pivot.iloc[:, 0]
retention_matrix = cohort_pivot.divide(cohort_size, axis=0)
# 시각화를 위해 NaN 값을 0으로 채웁니다.
retention_matrix = retention_matrix.fillna(0)
# 코호트 차트를 그립니다.
plt.figure(figsize=(12, 8))
sns.heatmap(data=retention_matrix, annot=True, fmt='.0%', cmap='Blues')
plt.title('Cohort Analysis - Customer Retention')
plt.ylabel('Cohort Month')
plt.xlabel('Cohort Index')
plt.show()
# 핵심 개념
1. 주문날짜의 월 추출
dt.to_period('M') 은 월을 추출한다
# 주문 날짜의 월을 추출합니다.
data['order_purchase_month'] = data['order_purchase_timestamp'].dt.to_period('M')
2. 첫 구매 월별로 코호트 만들기
# 첫 구매 월별로 코호트를 만듭니다.
data['cohort_month'] = data.groupby('customer_id')['order_purchase_month'].transform('min')
※ 여기서 잠깐
# transform('min') vs. apply('min')
transform('min')
- transform은 그룹화된 데이터프레임에서 각 그룹의 결과를 원래 데이터프레임의 동일한 크기와 인덱스로 반환합니다.
- 각 그룹 내에서 min 값을 계산하고, 그 값을 그룹 내 모든 행에 적용합니다.
- 결과적으로, 원래 데이터프레임과 같은 크기의 시리즈를 반환합니다.
apply('min')
- apply는 그룹화된 데이터프레임에서 함수를 적용한 결과를 그룹 단위로 반환합니다.
- 일반적으로 그룹별로 집계된 결과를 얻을 때 사용됩니다.
- 원래 데이터프레임과 동일한 크기의 결과를 반환하지 않습니다. 대신 그룹별로 집계된 값을 반환합니다.
3. 월별로 인덱스 생성하기
# 월별로 인덱스를 생성합니다.
data['cohort_index'] = (data['order_purchase_month'] - data['cohort_month']).apply(lambda x: x.n)
4. 코호트별로 고객수 계산하기
# 코호트별로 고객 수를 계산합니다.
cohort_data = data.groupby(['cohort_month', 'cohort_index'])['customer_id'].nunique().reset_index()
만약 2개의 함수를 사용하고 싶다면 .agg(['count', 'nunique'])
# 코호트별로 고객 수를 계산합니다.
cohort_data = data.groupby(['cohort_month', 'cohort_index'])['customer_id'].agg(['nunique', 'count']).reset_index()
5. 피벗 테이블을 이용하여 코호트 차트 만들기
# 5. 피벗 테이블을 생성하여 코호트 차트를 만듭니다.
cohort_pivot = cohort_data.pivot_table(index='cohort_month', columns='cohort_index', values='customer_id')
6. 퍼센트 기준으로 변환합니다.
# 6. 퍼센트 기준으로 변환합니다.
cohort_size = cohort_pivot.iloc[:, 0]
retention_matrix = cohort_pivot.divide(cohort_size, axis=0)
7. 시각화를 위해 NaN 값을 0으로 채웁니다.
# 7. 시각화를 위해 NaN 값을 0으로 채웁니다.
retention_matrix = retention_matrix.fillna(0)
8. 코호트 차트를 그립니다.
# 8. 코호트 차트를 그립니다.
plt.figure(figsize=(12, 8))
sns.heatmap(data=retention_matrix, annot=True, fmt='.0%', cmap='Blues')
전체 코드
# 8. 코호트 차트를 그립니다.
plt.figure(figsize=(12, 8))
sns.heatmap(data=retention_matrix, annot=True, fmt='.0%', cmap='Blues')
plt.title('Cohort Analysis - Customer Retention')
plt.ylabel('Cohort Month')
plt.xlabel('Cohort Index')
plt.show()
