[SQL] 이탈고객의 재사용률 분석 by 이성관::티엘엑스(TLX)

저는 TLX에서 PM 과 Business Analyst로 일하고 있습니다. TLX의 서비스인 TLX Pass는 하나의 멤버십으로 여러가지 운동종목을 이용할 수 있는 서비스입니다.

대부분 사람들이 운동이라는게 항상 심리적 부채로 가지고있고, 연초나 여름시즌을 앞두고 헬스장을 등록하며 그 부채를 갚으려 합니다.

TLX Pass 사용자들도 그러한 패턴이 있는지 분석하려고, 각 월별로 이탈한 사용자가, 어느시점에 다시 돌아오는지, 돌아오는 비율이 어느정도 되는지 분석해보았습니다.

이 글에서는 SQL을 이용해서 이탈고객(churned user)에 대한 재사용률(return rate)을 분석하는 방법에 대해서 설명하려고 합니다.

Part 1. 월별 churned user 리스트

당월 churned user(이탈회원)의 정의를 다음과 같이 정합니다.
- 전월 멤버십을 가진 사용자 중에서 당월 멤버십이 없는 사용자

Churned user 리스트는 여집합의 형태로 당월 retained user (유지회원)을 우선 구하고, 전월 멤버십 회원 리스트에서 빼는 형태로 만듭니다.

[1-a] 월별 멤버십 회원 리스트

멤버십의 사용시작일과 종료일을 기준으로 user_id를 가져온다.
아래 sql은 2016년 9월 멤버십 회원 리스트를 가져오는 query이다.

//[QUERY 1-a] 월별 멤버십 회원 리스트
select 
memberships.user_id
from 
memberships
where 
left(date_start, 7) <= "2016-09" 
and left(date_end, 7) >= "2016-09"

[1-b] 월별 유지회원 리스트

[1-a 월별 멤버십 리스트]에서 구한 회원들을 대상으로 1개월 뒤에도 멤버십을 가진 사용자 리스트를 구합니다.

아래는 2016년 9월에 멤버십을 가진 회원중에서, 2016년 10월에도 멤버십을 가지고 있는 회원의 user_id 를 가져오는 SQL입니다.

//[QUERY 1-b] 월별 retained user 리스트
select 
current.user_id
from 
memberships current
where 
current.user_id IN ( [QUERY 1-a] )
and left(current.date_start, 7) <= "2016-10" and     
left(current.date_end, 7) >= "2016-10"

[1-c] 월별 churned user 리스트

여집합으로 구하기 위해 [1-a 월별 멤버십 리스트]와 [1-b 월별 유지회원 리스트] 리스트를 left join으로 합치면, 당월에 멤버십이 없는 회원은 future.user_id 값이 NULL 이 됩니다.

아래와 같이 future.user_id가 NULL 인 것만 모으면 바로 churned user 리스트가 됩니다.

//[QUERY 1-c] 월별 churend user 리스트
select 
distinct current.user_id
from 
( [QUERY 1-a] ) as current        //전월 멤버십 회원 리스트
left join 
( [QUERY 1-b) as future        //당월 retained user 리스트
on current.user_id = future.user_id
where
future.user_id is null         //당월 멤버십이 없는 조건

[1-d] 월별 churned user 리스트를 `temporary table` 에 저장하기

이제 churned user를 분석하기 위해서 위에서 구한 churned user 리스트를 query에서 계속 사용해야 한다. 이럴땐 with 구문이나 with 구문이 지원되지 않는 db라면 temporary table 로 저장해 두면 편리하기도 하고 query가 가벼워져서 데이터를 뽑는 속도도 좋아집니다.

Temporary table (임시 테이블)
- 임시로 db에 생성하는 table로, db connection이 끊어지면 자동으로 삭제됩니다.
- 임시라는 것 외엔 테이블과 동일한 SQL을 이용하면 됩니다.

아래는 [1-c 월별 churned user 리스트] query 결과를 oct_16_churned_user_ids 라는 임시 테이블에 넣는 SQL입니다.

create temporary table oct_16_churned_user_ids (
user_id varchar(20)
)

insert into oct_16_churned_user_ids (
[QUERY 1-c]
)

// select * from oct_16_churned_user_ids

PART2. Churned period 별로 return rate 분석하기

churned user 를 대상으로, churned 시점보다 이후에 만들어진 멤버십을 멤버십 시작월 기준으로 그룹핑을 시킵니다.

select 
date_format(membership.date_start, '%Y-%m')
, count(distinct membership.user_id)
from membership
where
user_id IN ( select * from oct_16_churned_user_ids ) 
left(membership.date_start, 7) > '2016-09' // churned 시점 이후
group by
date_format(membership.date_start, '%Y-%m') // 멤버십 시작월로 그루핑

이 데이터를 월별로 모아서, Excel의 pivot table을 이용하면,
아래와 같이 월별로 churned user가 언제 다시 돌아오는지, 돌아오는 비율은 어떻게 되는지 분석할 수 있습니다.

뭔가 pivot table을 만드는 부분에서 휘리릭 끝내는 감이 있네요 ㅎㅎ
코멘트를 언제나 환영입니다. :)

#티엘엑스 #TLX #비즈니스애널리스트 #BA #BusinessAnalyst #업무 #꿀팁 #인사이트

[SQL] 이탈고객의 재사용률 분석