코호트-리텐션 분석(1) - 엑셀을 활용하여 코호트 분석하기

코호트 분석과 리텐션 분석이 뭐가 다른지도 모르던 시절..
멋들어진 그라데이션 차트를 그려보겠다는 일념으로 엑셀로 코호트 차트 그리기에 도전했다. 이 글은 근성의 엑셀 함수를 거쳐 SQL로 코호트 차트를 뽑아내기까지의 과정이다. 머리가 나쁘면 몸이 고생한다..

몸이 좀 고생하면 머리가 나빠도 되겠구나!

그전에 코호트 분석과 리텐션 분석에 대해 짚고 넘어가면

• 코호트 분석: 동질 집단, 즉 특정한 행동양식을 공유하는 사용자 집단 단위의 분석 방법론을 말한다. (→ 분석 수단에 해당)
• 리텐션 분석: 잔존 사용자의 수와 특징, 원인을 파악하는 분석 과정을 말한다. (→ 분석 목적에 해당)
• 엑셀과 SQL: (→ 코호트 분석 도구에 해당)

그렇다면 코호트와 세그먼트는 뭐가 다를까?
코호트 분석은 ‘특정 기간의 동질 집단’ 단위로 분석한다는 점에서 일반적인 세그먼트 분석과 다르다. 다시 말해 고객을 세분화하는데 시간 축을 이용하는 것이라고 볼 수 있다.
따라서 리텐션 분석 뿐만 아니라 시간의 흐름을 기준으로 동질 집단을 정의하는 것이 유의미하다면 퍼널 분석, 이탈률 분석, 매출 분석 등에도 코호트 분석 방법론을 활용할 수 있다.
이제 본격적으로 엑셀과 SQL을 활용하여 월별 서비스 방문 리텐션-코호트 차트를 그려보고 각각의 장단점을 알아보자.

---

1. 엑셀을 활용하여 코호트 분석하기

엑셀을 활용한 코호트 분석은 다음의 블로그 글을 참고했다. 전반적인 엑셀 기능들과 각 단계별 필요한 함수 사용법까지 자세히 기재되어 있어 반드시 원글을 숙지해야 한다!

[Excel] 엑셀 활용 코호트 분석(Cohort Analysis)

원글에서는 캐글의 ‘Retail Transaction Data’ 를 활용했지만 누가, 언제, 무엇을 했는지에 대한 raw 데이터만 있으면 된다.

Step 1. Raw 데이터 준비

• 무엇을 → event

분석 목적에 따라 이벤트 또는 사용자 액션에 대한 정의가 필요하다. 방문, 구매, 전송, 조회, 클릭 등이 있을 수 있다.

• 누가 → User_id

사용자를 식별할 수 있는 데이터를 의미한다.

• 언제 → date

이벤트가 발생한, 또는 사용자가 액션을 취한 시점 데이터를 의미한다. 일반적인 월별/주별 코호트 분석을 위해서는 일자 단위(ex. YYYY-MM-DD)의 데이터이면 충분한 것 같다.

Step 2. 첫 방문일자 추출 → 월별 코호트 구하기

• User_id와 date로 정렬
• First_date = IF(User_idN=User_idM, first_date, date)
• Cohort = Month(first_date)

IF문은 한 사용자가 여러 번 방문할 수 있기 때문에 한 사용자의 로그 중에서도 첫 방문일자가 언제인지 구하는 수식이다.
그리고 월별 리텐션을 구하고 있기 때문에 첫 방문일자의 월을 코호트로 정의할 수 있다.

Step 3. 방문일자별 첫 방문일자와의 차이 추출 → 월별 중복 제거된 방문횟수 구하기

• Age_by_month = month(date) - month(first_date)
• Distinct_key = user_id&”-“&age_by_month
• CHK = IF(Distinct_keyN=Distinct_keyM, 0, 1)

월별 리텐션을 구하고 있기 때문에 동일 월 내에 여러 번 방문한 것은 고려하지 않는다. 따라서 Distinct_key(user_id와 age_by_month의 조합)가 동일한 경우(=방문월이 동일한 경우) 중복으로 집계하지 않는다.

Step 4. 사용자수 추출 → 코호트별 중복 제거된 사용자수 구하기

• END_CHK = IF(user_idN=user_idM, 0, 1)

코호트별 사용자수를 구하고 있기 때문에 CHK가 1인(월별 중복 제거된) 데이터에서 동일 코호트 내 user_id가 동일한 경우 중복으로 집계하지 않는다.
필요한 데이터가 준비되었으니 이제 차트를 그리면 된다.

코호트별 사용자수 구하기

• Sumif(cohortN:cohortM, cohort1, END_CHKN:END_CHKM)

Sumif문은 코호트 리스트에서 cohort1,2,3…에 해당하는 코호트를 찾아 코호트별 사용자수를 합해주는 수식이다.

시간의 흐름에 따라 변화하는 사용자수 구하기

• Sumifs(CHKN:CHKM, cohortN:cohortM, cohort1, age_by_monthN:age_by_monthM, age_by_month1)

Sumifs문은 코호트 리스트에서 cohort1,2,3…에 해당하는 코호트를 찾고, age_by_month 리스트에서 1,2,3…에 해당하는 값을 찾아 사용자 수를 합해주는 수식이다. 이 때 원글에서는 IF문을 추가하여 Sumifs문의 값이 0면 공백처리 되도록 했다. (이 조건이 추가되어 복잡해 보였던 것…!!)

리텐션 구하기

• Sumifs(CHKN:CHKM, cohortN:cohortM, cohort1, age_by_monthN:age_by_monthM, age_by_month1) / Sumif(cohortN:cohortM, cohort1, END_CHKN:END_CHKM)

리텐션을 구하기 위해 시간의 흐름에 따라 변화하는 사용자수를 코호트별 사용자수로 나눈다. 각 코호트별 사용자수를 100%라고 할 때, 시간의 흐름에 따라 변화하는 사용자수의 비율이 얼마나 되는지 알 수 있다. 이게 익히 알고 있는 리텐션 비율인 것이다.
아래 이미지는 대외비라 블러처리 하였지만 위쪽 테이블이 사용자수, 아래쪽 테이블이 리텐션 비율로 계산한 결과이다. 조건부 서식을 걸어주어 색상처리까지하면 멋들어진 그라데이션 코호트 차트가 완성된다.

코호트-리텐션 분석

엑셀 코호트 분석의 장점

Raw 데이터만 잘 관리되면 접근성도 좋고 직관적이다. 처음에는 저 함수들이 어마무시해 보일 수 있지만 한 번만 수식을 걸어서 대시보드로 만들어두면 이후로는 raw 데이터를 추가만 해주면 된다.

엑셀 코호트 분석의 단점

Raw 데이터를 어디까지 엑셀에서 관리할 수 있을지가 미지수이다. 실제로 엑셀에서 많은 양의 데이터를 한 번에 불러오지 못해 수식의 범위를 다시 지정해줘야 하는 에러를 경험했다. 그리고 엑셀에 익숙하지 않으면 데이터 전처리와 함수의 쓰임을 이해하기까지의 과정이 녹록치 않다.

---

번외

처음 코호트 분석을 할 때 헷갈렸던 것은 “2021년에 처음 서비스를 방문한 사용자를 2022년 코호트 분석에서 어떻게 정의할 것인가” 였다.
첫 방문일자라는 정석적인 관점에서는 2021년 N월 코호트에 반영되어야 한다. 그렇다면 2021년 데이터까지 전부 봐야 하는걸까? 라는 의문을 가졌는데..
2022년의 리텐션을 구하는 것이 목적이기 때문에 2021년 데이터까지 고려할 필요가 없다는 결론을 내렸다. 따라서 이 경우 (2021년 N월에 처음 방문한 사용자이지만) “2022년의 첫 방문일자가 언제일까?” 라는 관점으로 보는게 더 적절하다.
이렇게 판단을 내리니 1월 코호트(1월에 첫 방문한 사용자의 수)의 수가 다른 코호트보다 큰 것이 납득이 되었다!! 기존 사용자들이 1월 코호트로 집계되기 때문인데, 1월에 첫 방문한 순수 사용자수를 알고 싶다면 2021년 12월 데이터까지 봐야할 필요가 있겠다.

SQL을 활용한 코호트 분석은 다음 글에 이어서...

코호트-리텐션 분석(2) - SQL을 활용하여 코호트 분석하기