[파이썬 압수] (3) SQL로만 리텐션 구하기

1편 https://taksw222.tistory.com/225
2편 https://taksw222.tistory.com/226
1,2편에 데이터 설명이 있습니다. 3편에서는 리텐션 쿼리 짠 삽질후기만 기록됩니다.

이번에는 월간 리텐션 구하기다.

Retention 구하기

1단계 / 각 유저별로 최초 구매 시점 구하기

script = """
SELECT
    user_id,
    min(substr(event_time, 1, 7)) as first_month
FROM events
WHERE event_type = 'purchase'
GROUP BY user_id
ORDER BY user_id
"""
cur.execute(script)
result = cur.fetchmany(10)
print(result)

 

유저별로 구해야, 나중에 코호트 집단을 만들 수 있을 것이다. 
즉, 최초 구매월이 같은 유저 개수를 세야 하므로 지금은 유저_id로 먼저 묶어야 한다.

2단계 / 실제 구매 데이터에 붙이로 리텐션 계산하기

이벤트 로그 데이터와 유저 최초 구매 시점 데이터를 붙여서 계산한다. 그 뒤 서로 다른 유저 수만 세 주면 그만.
여기서는 앞에서 만든 유저 최초 구매 시점 데이터를 with절을 활용하여 가상 데이터 빼줄 것이다.

script = """
with first_buy AS (SELECT
    user_id,
    min(substr(event_time, 1, 7)) as first_month
FROM events
WHERE event_type = 'purchase'
GROUP BY user_id
ORDER BY user_id)

SELECT
    first_buy.first_month,
    substr(events.event_time,1,7) as buy_month,
    count(distinct events.user_id) as user_count
FROM events left join first_buy on events.user_id = first_buy.user_id
WHERE events.event_type = 'purchase'
GROUP BY first_month, buy_month
ORDER BY first_month, buy_month
"""
cur.execute(script)
result = cur.fetchall()
print(result)

기준월별, 구매월별로 유저 수를 세면 기준월에 해당하는 유저가 해당 월에 몇 명이나 구매했는가를 셀 수 있다.
이를 기반으로 비율 계산을 하면 그만이다.

3단계 / 리텐션 비율 구하기
여기서 원래대로라면 경과 개월수를 구해야 하는데 지금 sqlite3에서는 date관련 자료형이 제공되지 않는다.
굳이 변수를 만들어 복잡하게 가도 되지만 sqlite3을 실전에서 쓸 리가 없으므로 그냥 넘어갔다. 비율만 계산!

다만 문제는 각 기준월별로 코호트 전체 개수를 뽑아서 나눠줘야 하는데, window 함수를 이 때 사용하면 된다. partition by로 해결하면 쉽게 해결.!

최종 코드는 다음과 같다. 검토를 위한 추가 컬럼이 좀 있는 것을 감안하자

script = """
with first_buy AS (SELECT
    user_id,
    min(substr(event_time, 1, 7)) as first_month
FROM events
WHERE event_type = 'purchase'
GROUP BY user_id
ORDER BY user_id)

SELECT
    first_month,
    buy_month,
    user_count,
    max(user_count) over(Partition by first_month) as max_cnt,
    round((user_count+0.00)/(max(user_count) over(Partition by first_month))+0.00 * 100, 2) as ret_ratio
    
FROM(
    SELECT
        first_buy.first_month,
        substr(events.event_time,1,7) as buy_month,
        count(distinct events.user_id) as user_count
    FROM events left join first_buy on events.user_id = first_buy.user_id
    WHERE events.event_type = 'purchase'
    GROUP BY first_month, buy_month
    ORDER BY first_month, buy_month
)
"""
cur.execute(script)
result = cur.fetchall()
print(result)

휴, 해결! 리텐션까지 해냈다....!

Partition by max로 최초 구매월별 유저 수의 최댓값을 가져오면 그것이 코호트 전체 인원 수일 것이므로 나눠주면 리텐션 비율이 나온다. 이렇게 쿼리로 뽑을 수 있고, 나머지는... 알아서 잘 하자(?)

Rolling 리텐션 유저 수 구하기

롤링 리텐션 : ????님 한판 더 해요

쇼핑몰의 경우는 구매 텀이 길 수도 있어서 과연 구매와 구매 사이의 구매 기록이 없는 달을 이탈로 봐야 할 것이냐에 대한 문제도 있어, 롤링 리텐션 케이스도 구해야 한다.

롤링 리텐션을 구할거라면 시작과 끝 사이 모든 것은 했다라고 가정하고 1로 만들면 된다.
즉 유저별로 각 달을 다 구하면 그만이므로, case when! 솔직히 앞에보다 더 쉽다.

script = """
with first_buy AS (SELECT
    user_id,
    min(substr(event_time, 1, 7)) as first_month,
    max(substr(event_time, 1, 7)) as last_month
FROM events
WHERE event_type = 'purchase'
GROUP BY user_id
ORDER BY user_id)

SELECT
    first_month,
    sum(CASE WHEN first_month <= '2020-09' and last_month >= '2020-09' then 1 else 0 end) as buy_2020_09,
    sum(CASE WHEN first_month <= '2020-10' and last_month >= '2020-10' then 1 else 0 end) as buy_2020_10,
    sum(CASE WHEN first_month <= '2020-11' and last_month >= '2020-11' then 1 else 0 end) as buy_2020_11,
    sum(CASE WHEN first_month <= '2020-12' and last_month >= '2020-12' then 1 else 0 end) as buy_2020_12,
    sum(CASE WHEN first_month <= '2021-01' and last_month >= '2021-01' then 1 else 0 end) as buy_2021_01,
    sum(CASE WHEN first_month <= '2021-02' and last_month >= '2021-02' then 1 else 0 end) as buy_2021_02
FROM first_buy
GROUP BY first_month
"""
cur.execute(script)
result = cur.fetchmany(10)
print(result)

실전에서는 날짜형 데이터를 사용할것이므로 날짜 관련 함수로 풀어야 하나, 여기는 어쩔 수 없이 문자열로 풀었다. 날짜만 달라지고, 큰 틀은 전혀 달라지지 않는다.