[데이터 분석 실무] 고객 리텐션 분석 (Spark SQL)

고객 리텐션 분석을 Spark SQL과 Plotly로 시각화한 내용을 정리합니다.

고객 리텐션 분석

Customer Retention Analysis

• 목적 : 일정기간이 지난 후에 돌아오는 고객의 비율을 측정해 개선 지점, 확대 방안을 파악한다. 기간 간격 설정은 어떤 비즈니스냐에 따라 다르게 정의할 수 있다.

고객 리텐션 분석 & 시각화

• 데이터 셋 기간 2018.04-09
• 분석 : Spark SQL & 시각화 : plotly

4월에 방문한 고객의 리텐션 분석

april_pool = spark.sql(" SELECT distinct clnt_id \
          from ecom \
         where month(date) = 4") 
april_pool.show()
april_pool.createOrReplaceTempView('april_pool')

spark.sql ("select month(date) month,\
              count(distinct e.clnt_id) as number \
           from ecom e where e.clnt_id in (select clnt_id from april_pool)\
           group by 1 order by 1")

재방문 기준 코호트별 리텐션 분석

• NEXT VISIT과의 간격 기준으로 고객 RETENTION분석
  1. visit log
  2. time lapse
  3. time diff
  4. Segment
  5. MoM Retention Rate

visitlog = spark.sql('select distinct clnt_id,month(date) visit_month from ecom group by 1,2 order by 1,2')

time_lapse = spark.sql('select clnt_id, visit_month, lead(visit_month,1) over (partition by clnt_id order by clnt_id, visit_month) next_visit
from visitlog')

segment = spark.sql("with time_lapse as ( 
            select clnt_id, visit_month,lead(visit_month,1) over (partition by clnt_id order by clnt_id, visit_month) next_visit from visitlog
        ), time_diff as( 
          select *, next_visit-visit_month as time_diff from time_lapse 
          ) select clnt_id, visit_month, 
          case when time_diff=1 then 'retained'
              when time_diff >1 then 'lagger' 
              when time_diff is null then 'lost' end  as segment
          from time_diff ")

+-------+-----------+-------+
|clnt_id|visit_month|segment|
+-------+-----------+-------+
|      8|          6|   lost|
|     14|          9|   lost|
|     32|          9|   lost|
|     56|          8|   lost|
|     56|          6| lagger|
|     58|          7|   lost|
|     59|          9|   lost|
|     63|          6| lagger|
|     63|          4| lagger|
|     63|          9|   lost|
+-------+-----------+-------+
only showing top 10 rows

mom_retention = spark.sql("select visit_month,
          sum(case when segment='retained' then 1 else 0 end)/count(clnt_id) as retention
          from segment
          group by 1
          order by 1")

이전 방문과의 간격 기준 코호트별 리텐션 분석

• PREVIOUS VISIT 과의 간격 기준으로 고객 RETENTION분석
  1. visit log
  2. time lapse
  3. time diff
  4. Segment NEW RETAINED RETRURNING 기준으로 고객 분류
     • 3의 TIME DIFF = 1 : RETAINED
     • 3의 TIME DIFF >1 : RETURNING
     • 3의 TIME DIFF IS NULL : NEW
       (데이터 시작이 4월이므로, new customer 잘못된 분류 > 기간 5-9월로 pool 설정)