서비스의 신뢰도를 수치화 할 수 있을까? | SQL - 커머스편

O2O, 핀테크와 같은 도메인에서는 서비스 신뢰도 지표가 중요합니다. 내 통장에서 돈이 직접 나가야 하거나, 누군가 내 집 문 앞까지 오거나, 모르는 사람의 차를 타야 하기 때문입니다.

 

도메인에 따라 신뢰도를 의미하는 지표가 다를 수 있습니다. 리텐션, CS 문의 내용, 주요 퍼널에서의 이탈률.. 등

여러 관점이 있지만, 오늘은 커머스 도메인을 다룰 예정입니다. 커머스에서 신뢰도라는 것은 충성도와 연관된다고 생각합니다.

 

 

커머스 RFM 분석

오늘 실습은 커머스에서 RFM(최근성, 빈도, 금액) 분석을 통해 우리 서비스의 충성 고객을 찾아볼 것입니다.

이렇게 발굴한 고객에게 맞춤 푸시 알림을 전송할 수도 있고, 각 수치에 가중치를 부여하면 멤버십 제도를 구축할 때 참고할 수도 있습니다.

 

BigQuery에 thelook_ecommerce 데이터셋을 가져옵니다. 우리는 다음 3가지를 도출할 것입니다.

• Recency: 얼마나 최근에 구매했는가?
• Frequency: 얼마나 자주 구매했는가?
• Monetary: 얼마나 많은 금액을 지불했는가?

 

SELECT 
  user_id,
  -- 1. Recency (최근성)
  DATE_DIFF(CURRENT_DATE(), MAX(DATE(created_at)), DAY) AS recency,
  -- 2. Frequency (빈도)
  COUNT(DISTINCT order_id) AS frequency,
  -- 3. Monetary (금액)
  ROUND(SUM(sale_price), 2) AS monetary
FROM `bigquery-public-data.thelook_ecommerce.order_items`
WHERE status = 'Complete' -- 반품/취소를 제외한 정상 구매 건
GROUP BY 1
ORDER BY monetary DESC
LIMIT 10;

 

외부 데이터셋을 참조할 때는 반드시 프로젝트ID.데이터셋.테이블 형식으로 전체 경로를 써주어야 합니다. 그렇지 않으면 Not Found 에러를 마주할 수 있습니다.

 

 

쿼리를 실행시켜보면 다음처럼 확인할 수 있습니다.

(좌)쿼리 실행 결과 / (우)Looker Studio 시각화

 

 

 

커머스 도메인별 활용 방법

생필품 커머스(마켓컬리 등): 재방문이 잦아야 하므로 Frequency(빈도)에 높은 가중치를 둬야합니다.

고가 가전/명품 커머스: 한 번의 결제가 크므로 Monetary(금액)에 가중치를 둬야합니다.

 

이렇게 계산된 점수를 바탕으로 멤버십 등급을 설계할 수 있습니다. 예를 들어, 상위 1% 유저의 평균 결제액이 월 100만 원이라면 VVIP 기준을 100만 원으로 설정하는 등 근거를 마련할 수 있습니다.