24.01.17 기초프로젝트

예진·2024년 1월 17일
0

TIL

목록 보기
20/68

  • 데이터 전처리

    1. 이상치 탐지 및 처리

      🔥 데이터가 잘 정제되어 있어 별도의 결측치 처리와 이상치 처리는 필요하지 않았음

    2. 데이터 정규화 또는 표준화

      🔥 - orders 테이블에서 order_date와 deliver_date를 비교하여 ‘배달 소요 기간(deliver_time)을 추출

    • 배달소요기간을 1-5/6-10/11-15/16-20/21-25/25-27 6단계로 범주화

    • 주문 데이터(order_id)와 등록되니 고객 데이터(customer_id)를 비교하여 고객별 주문 건수를 측정한 후, 주문건수 1회의 경우 재주문x(re_order=0), 주문건수 2회 이상(2회-6회)의 경우 재주문o(re_order=1)로 데이터 변환함

  • 데이터 분석 Part.1

    1. 가설 : “배송 기간”이 짧을수록 “재주문”이 많을 것이다.

    2. 데이터 : orders, customers

    3. 분석 방법 : Python을 활용한 상관 분석, 선형 회귀 분석

    4. 분석 과정 :

      • SQL을 통해 상관분석을 위한 파일 생성
        #order3
   SELECT 
    o.order_id,
    c.customer_id,
    c.age,
    CASE 
        WHEN c.gender = 'Female' THEN 0
        WHEN c.gender = 'Male' THEN 1
        WHEN c.gender = 'Bigender' THEN 2
        WHEN c.gender = 'Polygender' THEN 3
        WHEN c.gender = 'Agender' THEN 4
        WHEN c.gender = 'Genderfluid' THEN 5
        WHEN c.gender = 'Genderqueer' THEN 6
        ELSE 7 
    END as num_gender,
    DATEDIFF(o.delivery_date, o.order_date) as delivery_time,
    o.payment,
    CASE 
        WHEN COUNT(o.order_id) OVER (PARTITION BY c.customer_id) = 1 THEN 0
        ELSE 1
    END as reorder
FROM 
    orders o 
INNER JOIN 
    customers c ON o.customer_id = c.customer_id 
ORDER BY 
    o.customer_id;
        • 원활한 분석 진행을 위해 ‘gender’ 칼럼의 칼럼명을 숫자형 데이터로 변경
        • ’재주문 여부’ 컬럼을 생성해 재주문을 안 한 고객은 0, 재주문을 한 고객은 1로 표기
        • ‘order_date’와 ‘delivery_date’ 칼럼을 활용해 배달 소요기간 숫자형 데이터로 전환
      • Python을 통해 상관분석 진행
        !pip install pandas matplotlib seaborn
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns

order3 = pd.read_table('/content/_order3.csv',sep=',')

order3.head()

print(order3.isnull().sum())

order3 = order3.dropna()

corr=order3.corr(method='pearson')

corr

        • 상관 분석 진행 코드

          import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
reorder = pd.read_csv('/content/rereorder.csv')
reorder.head()

# # 몇가지 변수만 복사하기
correlation_df = reorder[['re_order', 'payment', 'age','delivery_time_category']].copy()

# correlation_df['re_order'] = correlation_df['re_order'].map({1: 1, 0: 0})

correlation_matrix = correlation_df.corr()

#재주문과 나이, 배달소요일자의 상관관계
correlation_matrix['re_order'] 

#주문 금액과 나이, 배달소요일자의 상관관계
# correlation_matrix['payment']

plt.figure(figsize=(8, 6))
sns.heatmap(correlation_matrix, annot=True, cmap='coolwarm', fmt=".2f")

plt.title('Correlation Matrix of Selected Variables')
plt.xticks(rotation=45)
plt.yticks(rotation=0)

plt.show()

        • 히트맵 분석 코드

      • SQL을 통해 선형회귀분석을 위한 파일 생성
        #주문횟수에 따른 고유번호와 배달기간
SELECT *
FROM orders o 
order by customer_id 

WITH RankedOrders AS (
    SELECT 
        order_id,
        customer_id,
        payment,
        order_date,
        delivery_date,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) - 1 as reorder_num,
        DATEDIFF(delivery_date, order_date) as delivery_time
    FROM 
        orders
)
SELECT 
    reorder_num,
    delivery_time
FROM 
    RankedOrders
ORDER BY 
    customer_id, order_date;
        • 재주문 횟수와 배달 시간의 관계를 알아보기 위해 두 칼럼만 있는 파일 생성
        • 재주문을 안 한 (첫 번 째 주문) 고객은 0, 재주문 횟수에 따라 1~5까지 표기
        • ‘order_date’와 ‘delivery_date’ 칼럼을 활용해 배달 소요기간 숫자형 데이터로 전환
      • Python을 활용하여 선형회귀분석을 진행
        from sklearn.linear_model import LinearRegression
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

df = pd.read_csv("/content/ordernum_deliverytine.csv")
df.head()

X = df["delivery_time"]
y = df["reorder_num"]
plt.plot(X, y, 'o')
plt.show()
        • 선형회귀 분석 코드
        • 선형회귀분석 결과 산점도
        • 선형회귀분석 결과 그래프의 기울기

  • 분석 결과 해석 Part.1

    분석 과정 : Python을 통한 상관 분석과 선형 회귀 분석

    • 상관분석 결과 : 각 요인별 상관계수가 최소 0.006, 최대 0.06 사이이므로 상관 관계를 확인할 수 없다.

    • 재주문 상관분석 히트맵(Heat Map)

      결론

      🔥 재주문에 가장 큰 영향을 미치는 요소 : ‘age’ 나이 상관계수 -0.04로 나이가 어릴 수록 재주문 가능성 있다. 배달 소요 일자가 짧을 수록 지불 금액이 높은 경향성을 보이지만 유의미하지 않다.

  • 데이터 분석 Part.2

    개요 : RFM을 통한 고객 그룹화, 기초 통계량 분석

    데이터 : orders, customers, sales, products

    분석 과정 : SQL을 통한 RFM 분석, 기초 통계량 분석

    • SQL을 통한 RFM 분석

      결측치 확인 자료

      # 4개 테이블 left join
SELECT count(*)
from customers c left join orders o on c.customer_id = o.customer_id 
left join sales s on o.order_id = s.order_id 
left join products p on s.product_id = p.product_id
where o.order_id is not null
      orders 테이블에 조인되지 않는 값 7개 존재 (join X : ordes → sales)
  
  order_id = (227, 332, 536, 598, 618, 823, 1000)
  
  최종으로는 Null값 없는 5,000개 Row가 존재.
  
  ### **RFM 분석**
  
  ```sql
  # 2021-10-30 기준
  # 지표 생성
  SELECT customer_id
  	 , MAX(order_date) as recent_date
  	 , DATEDIFF('2021-10-30', MAX(order_date)) as recency
  	 , count(customer_id) frequency
  	 , sum(payment) monetary
  from orders
  GROUP BY customer_id
  ORDER BY customer_id;
  ```
  
  
  ### RFM 점수 계산
  
  ```sql
  # RFM 점수 계산 
  with rfm AS (
  SELECT customer_id
  	 , MAX(order_date) as recent_date
  	 , DATEDIFF('2021-10-30', MAX(order_date)) as recency
  	 , count(customer_id) frequency
  	 , sum(payment) monetary
  from orders
  GROUP BY customer_id
  ORDER BY customer_id
  ),
  rfm_score AS (
  SELECT customer_id
       , recent_date
       , recency
       , frequency
       , monetary
       , CASE WHEN recency <= 30 THEN 4
              WHEN recency <= 60 THEN 3
              WHEN recency <= 90 THEN 2
              ELSE 1 END AS R
  	 , CASE WHEN frequency >= 4 THEN 3
  	 		WHEN frequency >= 2 THEN 2
              ELSE 1 END AS F
  	 , CASE WHEN monetary >= 200000 THEN 5
  		 	WHEN monetary >= 150000 THEN 4
              WHEN monetary >= 100000 THEN 3
              WHEN monetary >= 50000 THEN 2
              ELSE 1 END AS M
  FROM rfm
  )
  
  SELECT *
       , R+F+M RFM
  FROM rfm_score
  order by RFM desc;
  ```
  
  
  R**ecency** : 최근 구매
  
  - 1달 / 2달 / 3달 / 3달 이상
  
  F**requency** : 구매 빈도
  
  - 4회 이상 / 2회 이상 / 1회(나머지)
  
  M**onetary** : 주문 총액
  
  - 200,000$ 이상 / 150,000$ 이상 / 100,000$ 이상 / 50,000$ 이상 / 나머지

    • 연령

      연령대별 등록 고객 수

      SELECT case when age between 19 and 29 then 20
when age BETWEEN 30 and 39 then 30
when age between 40 and 49 then 40
when age BETWEEN 50 and 59 then 50
when age BETWEEN 60 and 69 then 60
else '70+' END as age_category , count(customer_id) as sum_population_by_age
from customers c 
group by 1
order by 1

      연령대 별 주문수

      SELECT case when c.age between 19 and 29 then 20
when c.age  BETWEEN 30 and 39 then 30
when c.age between 40 and 49 then 40
when c.age BETWEEN 50 and 59 then 50
when c.age BETWEEN 60 and 69 then 60
else '70+' END as age_category , count(o.order_id) as sum_order_by_age
from customers c join orders o on c.customer_id =o.customer_id 
group by 1
order by 1

    • 연령 + 성별

      연령+성별 분류 중 가장 많이 지출한 고객층

      SELECT age_gender_category, sum(payment)
FROM 
(SELECT  DISTINCT o.order_id ,
case when c.age between 19 and 29 and c.gender='female' then '20female'
when c.age between 19 and 29 and c.gender='male' then '20male'
when c.age  BETWEEN 30 and 39 and c.gender ='female' then '30female'
when c.age  BETWEEN 30 and 39 and c.gender ='male' then '30male'
when c.age between 40 and 49 and c.gender ='female' then '40female'
when c.age between 40 and 49 and c.gender ='male' then '40male'
when c.age BETWEEN 50 and 59 and c.gender='female' then '50female'
when c.age BETWEEN 50 and 59 and c.gender='male' then '50male'
when c.age BETWEEN 60 and 69 and c.gender ='female' then '60female'
when c.age BETWEEN 60 and 69 and c.gender ='male' then '60male'
WHEN c.age>69 and c.gender='female' then '70+female' 
WHEN c.age>69 and c.gender='male' then '70+male' 
END as age_gender_category, 
o.payment 
FROM customers c  INNER JOIN orders o  ON c.customer_id =o.customer_id  
INNER JOIN sales s  ON o.order_id  = s.order_id)a 
where age_gender_category is not NULL 
group by 1
order by 1

      연령대별 고객수

      ‘geder’ 컬럼에 성별 구분이 7개 이므로 그 중 신체적 성별만 확인할 수 있는 두 컬럼만 활용.

      지출이 가장 많은 집단, 가장 적은 집단별 구매 품목

      SELECT  s.product_id, p.product_name, p.product_type ,p.colour ,p.size
FROM customers c  INNER JOIN orders o  ON c.customer_id =o.customer_id  
INNER JOIN sales s  ON o.order_id  = s.order_id
inner join products p on s.product_id =p.product_ID 
where c.age BETWEEN 50 and 59 and c.gender='female'

SELECT  s.product_id, p.product_name, p.product_type ,p.colour ,p.size
FROM customers c  INNER JOIN orders o  ON c.customer_id =o.customer_id  
INNER JOIN sales s  ON o.order_id  = s.order_id
inner join products p on s.product_id =p.product_ID 
where c.age BETWEEN 30 and 39 and c.gender='male'

      집단별 구매횟수 총계

      집단별 구매횟수 평균

    • 미끼상품

      제품별 판매 수량과 각 제품가격 파악 후 많이 팔리고 비교적 적은 금액의 상품 선정

      # 제품별 판매 수량,유닛가격 파악
SELECT sub_q.product_id,
	   p.product_type,
	   p.product_name,
	   p.size,
	   p.colour,
	   sum_quantity,
	   unit_price
from
(
SELECT product_id , 
	   sum(quantity) as sum_quantity, avg(price_per_unit) as unit_price
FROM sales s 
group by 1
order by 2 desc
) sub_q join products p on sub_q.product_id = p.product_id
order by sum_quantity desc, unit_price;

#판매된 상품들 평균가격
SELECT round(avg(unit_price))
FROM main_view

# 평균가격보다 적은 금액으로 많이 팔린 상품
SELECT *
FROM main_view
where unit_price < 104
limit 10;

    • 매출

      매출로 계산한 상위 10%

      # customer 그룹화 진행 후 1,000명 ->  617명 중 상위 10% (60명) 
CREATE view rn_price AS (
SELECT *
from 
(SELECT customer_id, sum_pay,
	   RANK() over(order by sum_pay desc) as rn
FROM
(SELECT customer_id , sum(payment) as sum_pay
from orders o
group by 1
order by 2 desc) sub) main
where rn <= 60)

CREATE VIEW view_sub as( 
SELECT last_t.customer_id,
	    last_t.sum_pay,
	    rn,
	    s.order_id,
	    product_id
from
(SELECT rn_price.customer_id,
	    rn_price.sum_pay,
	    rn,
	    o.order_id
from rn_price left join orders o on rn_price.customer_id = o.customer_id) last_t join sales s on last_t.order_id = s.order_id)

SELECT *
from view_sub join products p on view_sub.product_id = p.product_ID

      매출액 상위 10% 고객

      매출액 상위 10% 고객 구매 품목

    • 주문건수

      고객별 주문금액

      격차가 큼. 상위를 뜯어보도록.

      주문금액 상위 10%고객의 주문상품

      상위 10%고객의 주문 중 상위 10%에 해당하는 상품

    • 재구매

      # 재구매자 다음 주문까지의 평균 날짜 차이 계산
# 가상테이블 생성
with RN_CREATE as (
SELECT customer_id,
	  str_to_date(order_date, '%Y-%m-%d') as order_date,
	   ROW_NUMBER() over (PARTITION BY customer_id order by order_Date) as RN
FROM orders 
order by customer_id, order_date
),
 DATE_DIFF as (
	SELECT customer_id, 
		   order_date,
		   DATEDIFF(order_date, LAG(order_date) over(partition by customer_id order by order_date)) as ORDER_DIFF
	FROM RN_CREATE 
	),
	DATE_DIFF_main as (
		SELECT *
		from DATE_DIFF
		WHERE ORDER_DIFF is not null)

      재구매자들의 다음 주문까지의 간격

      SELECT * 
FROM DATE_DIFF_main

      재구매한 고객별 재구매 평균 간격

      모든 재구매자들의 평균 재구매 간격

      SELECT ROUND(avg(REORDER_DATEDIFF)) as 'All_Reorder_customer_datediff'
FROM (SELECT customer_id,
	   avg(ORDER_DIFF) as REORDER_DATEDIFF
FROM DATE_DIFF_main
GROUP BY customer_id) sub_q

    • 배송기간

      #고객별 주문건수와 각 주문별 소요시간
SELECT distinct(customer_id), 
count(order_id)over(PARTITION by customer_id) as order_cnt,  
DATEDIFF(delivery_date,order_date) as delivery_time
FROM orders o
      #고객별 주문건수와 평균배달소요 기간
SELECT distinct(customer_id), 
count(order_id)over(PARTITION by customer_id) as order_cnt,  
avg(DATEDIFF(delivery_date,order_date))over(PARTITION by customer_id) as avg_delivery_time
FROM orders o
      #재구매 여부에 따른 평균 배달소요기간
**SELECT DISTINCT (order_over_once_or_not), avg(avg_delivery_time)over(PARTITION by order_over_once_or_not) as avg_deliver_time
from
(SELECT DISTINCT(customer_id) , 
	case when count(order_id)over(PARTITION by customer_id)>1 then '2+ orders'
	else '1 order'
	end as order_over_once_or_not,
	avg(DATEDIFF(delivery_date,order_date))over(PARTITION by customer_id) as avg_delivery_time
from orders o) a**
      SELECT
  SUM(CASE WHEN delivery_time < 15 THEN 1 ELSE 0 END) AS under_15_days,
  SUM(CASE WHEN delivery_time >= 15 THEN 1 ELSE 0 END) AS over_15_days
FROM (
  SELECT
    order_id,
    customer_id,
    payment,
    order_date,
    delivery_date,
    DATEDIFF(delivery_date, order_date) AS delivery_time
  FROM orders
) AS delivery_times;

    • 주문별 상품 개수

      #한 사람 당 구매 개수
SELECT o.order_id,
       COUNT(s.sales_id) AS count_sales
FROM orders o RIGHT JOIN sales s on o.order_id=s.order_id 
GROUP BY order_id
ORDER BY 1
      #고객 별 주문 횟수
SELECT COUNT(DISTINCT(o.order_id)) as count_orders,
       c.customer_id
FROM sales s RIGHT JOIN orders o on s.order_id = o.order_id 
             RIGHT JOIN customers c on o.customer_id = c.customer_id 
GROUP BY customer_id 
ORDER BY customer_id
      #고객 별 주문 별 상품 개수
SELECT c.customer_id,
       c.customer_name,
       o.order_id,
       COUNT(s.product_id) AS CNT_products
FROM customers c JOIN orders o on c.customer_id = o.customer_id 
                 JOIN sales s on o.order_id = s.order_id 
GROUP BY c.customer_id, c.customer_name, o.order_id 
ORDER BY c.customer_id, o.order_id

      #각 고객의 주문횟수 (일회주문, 다회주문)와 구매상품개수(단일상품, 다수상품)
SELECT
    c.customer_id,
    c.customer_name,
    CASE
        WHEN COUNT(DISTINCT o.order_id) >= 2 THEN '2+ orders'
        ELSE '1 order'
    END AS order_count_classification,
    CASE
        WHEN COUNT(s.product_id) >= 2 THEN '2+ products'
        ELSE '1 product'
    END AS product_count_classification
FROM
    customers c
JOIN
    orders o ON c.customer_id = o.customer_id
JOIN
    sales s ON o.order_id = s.order_id
GROUP BY
    c.customer_id, c.customer_name
ORDER BY
    c.customer_id;

    • 고객 분석 Logic Tree

  • 분석 결과 해석 Part.2

    분석 과정 :
    RFM 분석 결과

    • RFM 지수에 따라 회원을 총 4그룹으로 고객을 분류.

      기초 통계량 분석 결과

      경향성이 보이는 요인을 찾기 위해 범위를 좁히고 데이터를 세분화하여 다양한 갈래로 분석을 진행했으나, 모든 분석에서 평이한 결과를 얻음.

- 각 사진별로 비활성화/활성화, 배송기간 15일 미만/이상, 젠더로 고객 분류


- 요일 별 주문 건수

결론 : 

RFM 분석으로 점수를 매긴 고객들은 등급별로 VVIP, VIP, 충성고객, 일반고객으로 분류.

그룹별 등급에 따른 혜택을 다르게 지급하는 방향으로 마케팅 계획.

EDA 진행 결과 약 30가지의 분석이 모두 평이한 결과가 출력.

분석 결과에 특별한 경향성은 없으나

고르게 이루어진 분포에 초점을 맞춰 마케팅 방안 수립 계획
  • 결론 및 액션 도출
비활성 고객 : 
첫 구매를 위한 유인상품(로스리더) 추천
  • 한계점 캐글 데이터의 평이성 데이터의 절대적인 양 부족 부족한 도메인 지식다양한 출처의 데이터셋 활용 가설 설정의 중요성 데이터 분석의 흐름과 방향성 유지
  • 참고자료
profile
예진
Data Analysis / 맨 땅에 헤딩
이전 포스트

24.01.16. 기초프로젝트

다음 포스트

24.01.18 기초 프로젝트 회고록

0개의 댓글