프로젝트 요약
•
프로젝트 명 : C 페이 오픈 후 9개월, 우리 서비스 잘 되고 있을까?
•
프로젝트 설명 : 2019년 6월 서비스가 시작된 C 페이의 2019년 8월 ~ 2020년 3월 결제 데이터를 통해, 분석 시점을 2020년 4월로 가정하고 서비스 운영 현황 분석 및 개선점 제시
활용 데이터
EDA
성별, 연령대 별
•
고객 수
1.
1980년대생 여성
2.
1990년대생 여성
3.
1980년대생 남성
•
결제 건 수
1.
1980년대생 여성
2.
1990년대생 여성
3.
1970년대생 여성
•
평균 결제 금액
1.
2000년대생 남성
2.
1990년대생 남성
3.
1980년대생 남성
고객 수, 결제 건수는 여성 고객이 많으나 평균 결제 금액은 남성 고객이 높은 편
푸시 허용 여부 별
•
고객 수
•
결제 건 수
•
평균 결제 금액
결제 건 수 는 푸시 허용 고객이 높으나 평균 결제 금액은 푸시 차단 고객이 높은 편
그 외
고객 수
결제 건
결제 금액
데이터 추출 및 현황 파악
지표 1. 가입자 수
결제 데이터를 활용했기에 가입 후 결제를 하지 않은 고객이 있을 경우 데이터에 포함되어 있지 않으나, 분석을 위해 모든 가입자가 1회 이상 결제했다고 가정하고 분석을 진행
월 별 누적 가입자 수
•
지속적으로 상승 중
전월 대비 가입자 수 변화
•
2019년 8월 이후 변화율이 급격히 하락하며 2020년 3월에는 3% 대로 하락
지표 2. MAU (Monthly Active Users)
MAU
•
큰 변화 없이 유지
누적 고객 수 대비 MAU 비율
•
2019년 8월 74% 에서 2020년 3월 36% 까지 지속 하락
지표 3. Retention
꾸준한 사용이 중요한 결제 데이터로, 클래식 리텐션이 더 적합하다고 판단
•
클래식 리텐션 계산 쿼리
SELECT first_pay_month
, COUNT(DISTINCT customer_id) AS month0
, COUNT(DISTINCT CASE WHEN DATE_ADD(first_pay_month, INTERVAL 1 MONTH) = pay_month THEN customer_id END) AS month1
, COUNT(DISTINCT CASE WHEN DATE_ADD(first_pay_month, INTERVAL 2 MONTH) = pay_month THEN customer_id END) AS month2
, COUNT(DISTINCT CASE WHEN DATE_ADD(first_pay_month, INTERVAL 3 MONTH) = pay_month THEN customer_id END) AS month3
, COUNT(DISTINCT CASE WHEN DATE_ADD(first_pay_month, INTERVAL 4 MONTH) = pay_month THEN customer_id END) AS month4
, COUNT(DISTINCT CASE WHEN DATE_ADD(first_pay_month, INTERVAL 5 MONTH) = pay_month THEN customer_id END) AS month5
, COUNT(DISTINCT CASE WHEN DATE_ADD(first_pay_month, INTERVAL 6 MONTH) = pay_month THEN customer_id END) AS month6
, COUNT(DISTINCT CASE WHEN DATE_ADD(first_pay_month, INTERVAL 7 MONTH) = pay_month THEN customer_id END) AS month7
FROM customer_pay_records
GROUP BY first_pay_month
ORDER BY first_pay_month
SQL
복사
C 페이 Retention
•
7개월 리텐션 확인 시 36.3% 로 총 고객의 3 분의 1 정도 잔류
카카오페이 리텐션
•
동일한 간편결제 사업자 중 업계 상위로 손꼽을 수 있는 카카오페이의 2019년, 2020년 가입자 연간 리텐션은 80% 이상인데 비해, C 페이 리텐션은 7개월 리텐션임에도 36.3% 에 머무르고 있음
월간 가입자 수, Active Users Rate, Retention 모두 하락하고 있어 지표 하락의 원인 분석 및 해결 방안 도출 필요
가설 설정 및 검증
가설 1. 체리피커 고객으로 인해 Retention이 낮을 것이다
체리 피커 (Cherry Picker) : 기업의 제품 구매, 서비스 이용 실적은 좋지 않으면서 관련 혜택과 같은 실속만을 챙기는데 관심을 두는 소비자
Step 1. Boxplot 활용 체리피커 기준 설정
•
혜택 금액은 정상 범위 이상이지만 결제 건수는 정상 범위 이내인 고객
⇒ 가입 월 별 결제 건 수, 결제 금액 Boxplot 으로 이상치 탐색
•
가입 월 별 결제 건 수 Boxplot
가입연월 | MAX | 3QR | MEDIAN | 1QR | MIN |
2019년 5월 | 39 | 26.5 | 8 | 3 | 1 |
2019년 6월 | 57 | 25 | 9 | 3 | 1 |
2019년 7월 | 49 | 21 | 8 | 2 | 1 |
2019년 8월 | 25 | 11 | 4 | 1 | 1 |
2019년 9월 | 25 | 11 | 4 | 1 | 1 |
2019년 10월 | 15 | 7 | 3 | 1 | 1 |
2019년 11월 | 15 | 7 | 3 | 1 | 1 |
2019년 12월 | 13 | 6 | 2 | 1 | 1 |
2020년 1월 | 8 | 4 | 2 | 1 | 1 |
2020년 2월 | 5 | 3 | 2 | 1 | 1 |
2020년 3월 | 1 | 1 | 1 | 1 | 1 |
•
가입 월 별 결제 금액 Boxplot
가입연월 | MAX | 3QR | MEDIAN | 1QR | MIN |
2019년 5월 | 85540 | 54525 | 17892 | 11386 | 2000 |
2019년 6월 | 81469 | 35589 | 13845 | 5000 | 0 |
2019년 7월 | 71664 | 31481 | 12334 | 4690 | 0 |
2019년 8월 | 43787 | 20518 | 9333 | 5000 | 0 |
2019년 9월 | 38173 | 17383 | 7991 | 3500 | 0 |
2019년 10월 | 29129 | 13453 | 6093 | 3000 | 0 |
2019년 11월 | 25050 | 13025 | 7000 | 5000 | 0 |
2019년 12월 | 19496 | 9000 | 5000 | 2000 | 0 |
2020년 1월 | 13176 | 6474 | 3000 | 2000 | 0 |
2020년 2월 | 6997 | 4000 | 2000 | 2000 | 0 |
2020년 3월 | 4661 | 2000 | 2000 | 225 | 0 |
Step 2. 체리피커 찾기
•
쿼리
SELECT COUNT(*), SUM(pay_count)
FROM (SELECT customer_id, COUNT(*) AS pay_count
FROM chai
# 가입연월 필터링
WHERE YEAR(sign_up_date) = 2019
AND MONTH(sign_up_date) = 5
GROUP BY customer_id
# 체리피킹 조건 설정
HAVING COUNT(*) <= 39
AND SUM(total_promotion) > 85540) AS cherry_picker
SQL
복사
•
고객 수
가입연월 | 총 가입 고객 수 | 체리피커 | 체리피커 비율 |
2019년 5월 | 24 | 1 | 4.17% |
2019년 6월 | 74646 | 1032 | 1.38% |
2019년 7월 | 78063 | 1190 | 1.52% |
2019년 8월 | 110997 | 2122 | 1.91% |
2019년 9월 | 36177 | 702 | 1.94% |
2019년 10월 | 48319 | 1862 | 3.85% |
2019년 11월 | 40086 | 974 | 2.43% |
2019년 12월 | 51732 | 1024 | 1.98% |
2020년 1월 | 56205 | 1739 | 3.09% |
2020년 2월 | 32098 | 698 | 2.17% |
2020년 3월 | 16574 | 147 | 0.89% |
합계 | 544921 | 11491 | 2.11% |
•
결제 건 수
가입연월 | 총 결제 건수 | 체리피커 결제건 | 체리피커 결제율 |
2019년 5월 | 356 | 35 | 9.83% |
2019년 6월 | 1501250 | 46606 | 3.10% |
2019년 7월 | 1383454 | 44324 | 3.20% |
2019년 8월 | 1140401 | 36863 | 3.23% |
2019년 9월 | 368511 | 11820 | 3.21% |
2019년 10월 | 331636 | 11114 | 3.35% |
2019년 11월 | 271082 | 8168 | 3.01% |
2019년 12월 | 286858 | 7307 | 2.55% |
2020년 1월 | 226043 | 6863 | 3.04% |
2020년 2월 | 87801 | 2578 | 2.94% |
2020년 3월 | 26418 | 147 | 0.56% |
합계 | 5623810 | 175825 | 3.13% |
결론
체리피킹 고객으로 분류할 수 있는 고객은 11,491명 (전체 고객의 2%) 으로 전체 리텐션에 영향을 줄 만큼의 수치는 아닌 것으로 보임
가설 2. 비우량 고객이 많아 Rentention 이 낮을 것이다.
Step 1. RFM 분석을 위한 지표 설정
- Recency : 얼마나 최근에 결제 했는지
- Frequency : 얼마나 결제를 여러번 했는지
- Monetary : 얼마나 많은 금액을 결제했는지
⇒ 각 지표 별 6단계로 나누어 설정
* 지표 Condition 설정 시 참고 자료 : 그로스해킹 (양승희 저)
•
Recency : 2020년 4월 1일 기준으로 계산
◦
가장 최근 결제 : 1일 전
◦
가장 오래 결제 : 244일 전
Grade | Condition | Customer | score |
1 | 1일 이내 | 14624 | 10 |
2 | 3일 이내 | 26254 | 8 |
3 | 7일 이내 | 33957 | 6 |
4 | 14일 이내 | 43015 | 4 |
5 | 28일 이내 | 61430 | 2 |
6 | 28일 초과 | 365551 | 0 |
◦
고객 수 계산 쿼리
-- RFM / Recency
WITH recent_pay_record AS (
SELECT customer_id, DATE(MAX(created)) AS recent_pay_date, DATEDIFF('2020-04-01', DATE(MAX(created))) AS recent_diff
FROM chai
GROUP BY customer_id)
SELECT COUNT(DISTINCT CASE WHEN recent_diff = 1 THEN customer_id END) AS diff_1d
, COUNT(DISTINCT CASE WHEN recent_diff BETWEEN 2 AND 3 THEN customer_id END) AS diff_3d
, COUNT(DISTINCT CASE WHEN recent_diff BETWEEN 4 AND 7 THEN customer_id END) AS diff_7d
, COUNT(DISTINCT CASE WHEN recent_diff BETWEEN 8 AND 14 THEN customer_id END) AS diff_14d
, COUNT(DISTINCT CASE WHEN recent_diff BETWEEN 15 AND 28 THEN customer_id END) AS diff_28d
, COUNT(DISTINCT CASE WHEN recent_diff > 28 THEN customer_id END) AS diff_upper_28d
FROM recent_pay_record
SQL
복사
•
Frequency : Recency 지표의 Condition 활용하여 설정
◦
가장 적은 결제 횟수 : 1회
◦
가장 많은 결제 횟수 : 2705회
Grade | Condition | Customer | score |
1 | 244회 이상 (하루 1회 이상) | 253 | 10 |
2 | 81회 이상 (3일에 1번 이상) | 8289 | 8 |
3 | 27회 이상 (7일에 1번 이상) | 46073 | 6 |
4 | 9회 이상 (14일에 1번 이상) | 101028 | 4 |
5 | 3회 이상 (28일에 1번 이상) | 15913 | 2 |
6 | 3회 미만 (그 이상) | 236365 | 0 |
◦
고객 수 계산 쿼리
-- RFM / frequency
WITH frequency AS (
SELECT customer_id, COUNT(*) AS pay_count
FROM chai
GROUP BY customer_id)
SELECT COUNT(DISTINCT CASE WHEN pay_count >= 244 THEN customer_id END) AS grade_1
, COUNT(DISTINCT CASE WHEN pay_count BETWEEN 81 AND 243 THEN customer_id END) AS grade_2
, COUNT(DISTINCT CASE WHEN pay_count BETWEEN 27 AND 80 THEN customer_id END) AS grade_3
, COUNT(DISTINCT CASE WHEN pay_count BETWEEN 9 AND 26 THEN customer_id END) AS grade_4
, COUNT(DISTINCT CASE WHEN pay_count BETWEEN 3 AND 8 THEN customer_id END) AS grade_5
, COUNT(DISTINCT CASE WHEN pay_count < 3 THEN customer_id END) AS grade_6
FROM frequency
SQL
복사
•
Monetary : 평균 결제 금액을 반올림하 단위 금액으로 설정
◦
가장 적은 결제 금액 : 0원
◦
가장 많은 결제 금액 : 112,273,800원
◦
고객 당 평균 결제 금액 : 196,438원
Grade | Condition | Customer | score |
1 | 100만원 초과 | 19101 | 10 |
2 | 80만원 ~ 100만원 | 9589 | 8 |
3 | 60만원 ~ 80만원 | 13756 | 6 |
4 | 40만원 ~ 60만원 | 26932 | 4 |
5 | 20만원 ~ 40만원 | 61935 | 2 |
6 | 20만원 미만 | 413609 | 0 |
◦
고객 수 계산 쿼리
-- RFM / Monetary
WITH monetary AS(
SELECT customer_id, SUM(post_discount) as pay_amount
FROM chai
GROUP BY customer_id)
SELECT COUNT(DISTINCT CASE WHEN pay_amount < 200000 THEN customer_id END) AS grade_1
, COUNT(DISTINCT CASE WHEN pay_amount BETWEEN 200000 AND 400000 THEN customer_id END) AS grade_2
, COUNT(DISTINCT CASE WHEN pay_amount BETWEEN 400001 AND 600000 THEN customer_id END) AS grade_3
, COUNT(DISTINCT CASE WHEN pay_amount BETWEEN 600000 AND 800000 THEN customer_id END) AS grade_4
, COUNT(DISTINCT CASE WHEN pay_amount BETWEEN 800001 AND 1000000 THEN customer_id END) AS grade_5
, COUNT(DISTINCT CASE WHEN pay_amount > 1000000 THEN customer_id END) AS grade_6
FROM monetary
SQL
복사
Step 2. RFM Score 를 활용한 고객 군 설정
참고 자료 : 데이터 분석을 위한 SQL 레시피 (가사키 나가토 저)
Step 3. 고객 군 별 고객 수 확인
•
쿼리
SELECT COUNT(DISTINCT customer_id) AS '총 고객 수'
, COUNT(DISTINCT CASE WHEN recency >= 8 AND frequency >= 8 AND monetary >= 8 THEN customer_id END) AS '우량 고객'
, COUNT(DISTINCT CASE WHEN recency >= 8 AND frequency <= 2 AND monetary >= 8 THEN customer_id END) AS '신규 우량 고객'
, COUNT(DISTINCT CASE WHEN recency >= 6 AND frequency >= 6 AND monetary >= 6 THEN customer_id END) AS '안정 고객'
, COUNT(DISTINCT CASE WHEN recency >= 8 AND frequency <= 2 AND monetary <= 2 THEN customer_id END) AS '신규 고객'
, COUNT(DISTINCT CASE WHEN recency <= 2 AND frequency >= 8 AND monetary >= 8 THEN customer_id END) AS '이탈 고객'
, COUNT(DISTINCT CASE WHEN recency <= 2 AND frequency <= 2 AND monetary <= 2 THEN customer_id END) AS '비우량 고객'
FROM customer_info_with_rfm
SQL
복사
고객 군 | 고객 수 |
우량 고객 | 4,221명 |
신규 우량 고객 | 88명 |
안정 고객 | 17,693명 |
신규 고객 | 11,962명 |
이탈 고객 | 1,185명 |
비우량 고객 | 339,042명 |
미분류 | 170,730명 |
Step 4. 비우량 고객 군 Retention 측정
•
비우량 고객 군 Retention
•
C 페이 전체 Retention
⇒ 전반적으로 전체 리텐션 대비 절반 이하의 Retention 수치를 보이며, 특히 7개월 Retention에서는 5% 대의 수치로 전체 Retention 대비 -31% 수치를 보임
결론
낮은 Retention을 보이는 비우량 고객이 많아 전반적인 Retention 이 하락한 것으로 보임
가설 3. 푸시를 허용한 고객이 더 많은 결제를 할 것이다.
Push Permission 참 : 푸시 허용
Push Permission 거짓 : 푸시 차단
Step 1. 푸시 허용 / 차단 고객 수 확인
•
푸시 허용 / 차단 고객 수
⇒ 푸시 허용 41.86% (228,121명), 차단 58.14% (316,800명) 으로 푸시 차단 고객 수가 더 많음
Step 2. 푸시 허용 여부 별 결제 건 수, 결제 금액, 혜택 금액 확인
•
결제 건 수
•
결제 금액
•
혜택 금액
결론
푸시 허용 여부는 결제 건 수, 결제 금액, 혜택 금액에 영향을 미치지 않음
그러나, 마케팅 비용이 발생하지 않는 푸시를 허용했음에도 결제 건이나 혜택 금액이 푸시 차단 고객 대비 높지 않다면 더 효율적인 마케팅이 필요할 것으로 판단됨
최종 결론 및 액션 아이템
가설 1. 체리피커 고객으로 인해 Retention 이 낮을 것이다 ⇒ 영향을 주지 않음
가설 2. 비우량 고객이 많아 Retention 이 낮을 것이다 ⇒ 5% 대의 낮은 Retention을 보이는 비우량 고객이 전체의 62% 로, 전체 Retention 에 영향을 미침
•
비우량 고객의 73%를 차지하는 8090 고객을 대상으로 복귀 유저 이벤트 진행
가설 3. 푸시를 허용한 고객이 더 많은 결제를 할 것이다 ⇒ 영향은 주지 않으나 개선 필요
•
푸시 허용 고객 대상 프로모션 진행
ex) 푸시 수신 후 n분 이내 결제 시 추가 혜택 제공 등
프로젝트 회고
•
Merchant_id 컬럼 값에 대한 정보를 알 수 없어 데이터를 활용하지 못 함
⇒ 컬럼에 대한 정보가 있었다면 Merchant_id 별 주 소비 고객 군 분석을 통해 고객 군 별 맞춤 프로모션 제안이 가능했을 것
•
RFM 분석 시 낮은 RFM Score 에 고객이 다수 포진됨
⇒ 각 지표 별 Condition 을 조정하여 Score 별 고객 수가 일정했다면 다른 결과 값이 나왔을 수 있을 듯