데이터베이스/SQL

[Big Query] RFM Segmentation Project

jpocket 2025. 4. 17. 23:28
반응형

1. 프로젝트 개요

🎯 목표

고객 세분화 및 타깃 마케팅을 위한 RFM 분석 수행

🛠️ 사용한 도구

BigQuery

💻 사용한 언어

SQL

🧾 분석 대상

고객의 구매 데이터

📊 분석 방법

RFM 분석 (Recency, Frequency, Monetary)

 

 

 

2. RFM 분석이란?

  • Recency (최근성): 고객이 마지막으로 구매한 시점으로부터 얼마나 시간이 지났는지
  • Frequency (빈도): 고객이 얼마나 자주 구매했는지
  • Monetary (금액): 고객이 얼마나 많은 금액을 지출했는지

세 가지 요소를 바탕으로 고객을 세분화하여, 각 그룹에 맞는 마케팅 전략을 세울 수 있도록 돕는 방법이다.

 

 

 

3. 학습한 내용

  • BigQuery 사용법: 대용량 데이터 처리 및 SQL 쿼리 최적화 기법을 학습하였다.
  • RFM 분석: 고객의 구매 행동을 기반으로 세분화하는 기법을 적용하여 실질적인 마케팅 전략에 활용할 수 있다.

 

 

 

4. 분석 방법

  1. 데이터 전처리:
    • 결측치 처리: 분석에 방해가 되는 결측치를 제거하거나 적절히 대체하여 데이터를 정리하였다.
    • 중복값 처리: 중복된 데이터가 분석에 영향을 미치지 않도록 제거하였다.
    • 오류값 처리: 잘못된 값이나 형식 오류를 수정하여 분석에 적합한 데이터로 정리하였다.
  2. RFM 분석을 통한 추가 Feature 추출:
    • 구매하는 제품의 다양성: 고객이 구매한 다양한 제품의 수를 측정하여, 고객이 여러 제품군을 구매하는지 확인하였다.
    • 평균 구매 주기: 고객이 평균적으로 얼마나 자주 구매하는지 계산하여, 재구매 주기를 파악하였다.
    • 구매 취소 경향성: 고객의 취소 패턴을 분석하여, 취소 빈도와 비율을 측정하였다. 이를 통해 고객의 충성도나 반응을 예측할 수 있었다.

 

 

 

5. 코드 개선 포인트 🔎

내 코드)

delete from `white-might-456101-t9.modulabs_project.data`
where Description = 'Next Day Carriage' or Description = 'High Resolution Image';

 

예시 답안)

DELETE
FROM project_name.modulabs_project.data
WHERE
Description LIKE '%Next Day Carriage%'
OR Description LIKE '%High Resolution Image%';

✅ 와일드카드 문자 %를 사용하는 것이 적절하다.

문제에서 요구한 것은 지정된 문자열이 아닌 해당 문자열을 포함하는 모든 데이터를 제거하는 코드였다.

 

 

 

 

 

 

내 코드)

SELECT MAX(DATE(InvoiceDate)) AS most_recent_date
FROM `white-might-456101-t9.modulabs_project.data`;

 

예시 답안)

SELECT 
    MAX(DATE(InvoiceDate)) OVER () AS most_recent_date,
    DATE(InvoiceDate) AS InvoiceDay,
    *
FROM project_name.modulabs_project.data;

✅ OVER() 구문이 없다고 해서 잘못된 코드는 아니다.

OVER() 구문의 유무는 분석 목적에 따라 달라지며, 둘 다 올바른 문법이다.

 

 

 

 

 

 

내 코드)

CREATE OR REPLACE TABLE`white-might-456101-t9.modulabs_project.user_data` AS
WITH TransactionInfo AS (
  SELECT
    CustomerID,
count(case when InvoiceNo LIKE 'C%' then 1 end) AS cancel_frequency,
COUNT(InvoiceNo) AS total_transactions
  FROM `white-might-456101-t9.modulabs_project.data`
group by CustomerID
)

SELECT u.*, t.* EXCEPT(CustomerID), round( (t.cancel_frequency / t.total_transactions)*100, 2) AS cancel_rate
FROM `white-might-456101-t9.modulabs_project.user_data` AS u
LEFT JOIN TransactionInfo AS t
ON u.CustomerID = t.CustomerID;

 

예시 답안)

 

CREATE OR REPLACE TABLE `project_name.modulabs_project.user_data` AS

WITH TransactionInfo AS (
  SELECT
    CustomerID,
    COUNT(DISTINCT InvoiceNo) AS total_transactions,
    COUNT(DISTINCT CASE WHEN InvoiceNo LIKE 'C%' THEN InvoiceNo END) AS cancel_frequency
  FROM `project_name.modulabs_project.data`
  GROUP BY CustomerID
)

SELECT u.*, t.* EXCEPT(CustomerID), ROUND(cancel_frequency/total_transactions, 2) AS cancel_rate
FROM `project_name.modulabs_project.user_data` AS u
LEFT JOIN TransactionInfo AS t
ON u.CustomerID = t.CustomerID;

✅ 올바른 방식은 InvoiceNo 자체를 기준으로 중복 제거 후 카운트하는 것이다.

C로 시작하는 InvoiceNo는 1이 되어, 전체 행 중에서 1인 행만 카운트하여 거래 수를 셀 수 있다고 판단하였다.

이는 거래 수가 아니라 거래한 상품 단위로 세는 것이 되어버린다.

따라서 1이 아닌 InvoiceNo로, distinct를 붙여 중복을 제거해주어야 한다.

 

반응형