코호트 분석 방법과 리텐션 계산 기준 · 엑셀로 만드는 순서

코호트 분석은 고객을 가입 시기별로 묶어, 각 묶음이 시간이 지나며 얼마나 남는지를 나란히 보는 방법이다. 전체 평균으로는 보이지 않는 것이 보인다. 이번 달 전체 재구매율이 작년과 같아도, 최근에 들어온 고객만 유독 빨리 빠져나가고 있을 수 있다. 평균은 그걸 가려준다.

이 글은 도구 없이 스프레드시트로 코호트 표를 만드는 순서와, 리텐션율·재구매율·LTV를 실제로 어떻게 계산하는지를 정리한다. SQL이나 파이썬은 쓰지 않는다.

코호트 분석은 무엇을 보는 것인가

코호트는 같은 시기에 시작한 집단을 뜻한다. 1월에 가입한 고객, 2월에 가입한 고객처럼 나누고, 각 집단이 1개월 뒤·2개월 뒤에 얼마나 남아 있는지를 표로 놓는다.

위 숫자는 형식을 보여주기 위한 예시다. 중요한 것은 읽는 방법이다.

세로로 읽으면 추세가 보인다. +1개월 열이 48 → 45 → 38 → 31로 계속 떨어지고 있다. 신규 고객이 점점 빨리 이탈하고 있다는 뜻이고, 이건 전체 평균으로는 잡히지 않는다. 인원이 늘고 있어서 총 활성 고객 수는 오히려 증가하는 것처럼 보이기 때문이다.

가로로 읽으면 이탈 시점이 보인다. 1월 코호트는 첫 달에 52%가 빠지고 그 뒤로는 완만하다. 이탈이 초기에 몰려 있다는 뜻이므로, 손댈 곳은 온보딩이지 6개월차 고객 관리가 아니다.

평균만 보면 무엇을 놓치는가

전체 리텐션율 하나로는 두 가지를 구분할 수 없다.

첫째, 신규 고객의 질이 나빠지는 상황. 유입이 늘면서 총량은 유지되지만 최근 코호트일수록 빨리 빠진다. 광고 채널을 바꿨거나 할인으로 끌어온 고객이 많을 때 나타난다.

둘째, 오래된 고객이 빠지는 상황. 초기 코호트의 +12개월 열이 무너지고 있는데 신규는 멀쩡하다. 제품이 정체됐거나 경쟁사로 옮겨간 경우다.

두 상황의 처방이 정반대다. 앞은 유입 채널과 온보딩을 봐야 하고, 뒤는 기존 고객 대상 가치를 다시 만들어야 한다. 평균만 보면 어느 쪽인지 모른 채 둘 다 시도하게 된다.

스프레드시트로 어떻게 만드는가

필요한 데이터는 두 열뿐이다. 고객 식별자와 거래(또는 활동) 날짜.

1단계. 고객별로 첫 거래일을 찾는다. 고객 식별자 기준으로 최소 날짜를 구하면 된다. 이것이 그 고객의 코호트가 된다.

2단계. 각 거래에 대해 "첫 거래로부터 몇 개월째인가"를 계산한다. 거래월에서 코호트월을 뺀 값이다. 첫 달은 0, 다음 달은 1이 된다.

3단계. 코호트월을 행, 경과 개월을 열로 놓고 고유 고객 수를 센다. 피벗 테이블이 이 일을 한다.

4단계. 각 칸을 그 행의 0개월 값으로 나눈다. 비율로 바꿔야 코호트 간 크기가 달라도 비교된다.

이게 전부다. 도구를 도입하기 전에 이 표를 한 번 손으로 만들어 보는 편이 낫다. 무엇을 세고 있는지 직접 정의하게 되기 때문이다.

무엇을 "남아 있다"로 셀 것인가

여기서 결과가 갈린다. 정의를 정하지 않고 시작하면 나중에 숫자를 못 믿게 된다.

구독은 쉽다. 결제 상태가 곧 답이다. 어려운 것은 이커머스와 오프라인이다. 3개월에 한 번 사는 것이 정상인 상품인데 월 단위로 재구매율을 재면 대부분의 달이 0이 되어 아무것도 보이지 않는다.

구매 주기가 긴 상품은 주기를 그 상품에 맞춰야 한다. 자사 데이터에서 두 번째 구매까지 걸린 기간의 중앙값을 구하고, 그 언저리를 단위로 잡으면 된다.

앱과 서비스는 "핵심 행동"을 무엇으로 볼지가 관건이다. 로그인만으로 활성이라고 하면 숫자는 좋아 보이지만 의미가 없다. 그 제품에서 가치를 실제로 얻는 행동이어야 한다.

리텐션율은 어떻게 계산하는가

가장 단순한 형태는 이렇다.

특정 코호트의 N개월차 리텐션율 = (N개월차에 활성인 고객 수) ÷ (그 코호트의 최초 고객 수)

주의할 점이 하나 있다. 중간에 쉬었다가 돌아온 고객을 어떻게 셀 것인가. 2개월차에 활동이 없다가 3개월차에 돌아온 고객이 있다면, 3개월차 칸에 포함하는 것이 일반적이다. 이 경우 리텐션 곡선이 중간에 올라갈 수 있는데, 오류가 아니라 복귀가 있었다는 뜻이다.

기간 단위 이탈률도 함께 보면 좋다.

이탈률 = 1 − (기간 말 유지 고객 수 ÷ 기간 초 고객 수)

이 둘은 다른 것을 말한다. 리텐션 곡선은 코호트가 시간에 따라 어떻게 되는지를 보고, 이탈률은 특정 기간에 얼마나 빠졌는지를 본다. 월별 이탈률이 5%라고 해서 20개월 뒤에 0이 되는 것은 아니다. 빨리 빠질 고객이 먼저 빠지고 남은 고객은 더 오래 남기 때문에 곡선은 완만해진다.

재구매율과 LTV는 어떻게 이어지는가

세 지표는 따로 노는 것이 아니라 하나로 이어진다.

LTV는 이 세 개를 곱한 값이므로, 어느 하나가 움직이면 전체가 움직인다. 그리고 리텐션이 가장 크게 움직인다. 평균 구매액을 10% 올리는 것보다 유지 기간을 10% 늘리는 쪽이 대개 쉽고 효과가 크다.

여기에 획득 비용을 놓으면 판단이 된다. LTV가 획득 비용보다 충분히 크지 않으면 그 채널로 고객을 더 데려올수록 손해다. 코호트별로 LTV를 계산하면 어느 시기·어느 채널의 고객이 실제로 남는지 보인다.

계산에서 자주 나오는 실수

숫자가 이상할 때 대개 아래 중 하나다. 분석 방법이 아니라 집계 방식의 문제다.

중복 계산. 한 고객이 한 달에 세 번 구매했는데 세 명으로 세는 경우다. 코호트 칸에는 고유 고객 수를 넣어야 한다. 피벗에서 개수 대신 고유 개수를 쓰는지 확인한다.

미완성 코호트를 함께 보는 것. 이번 달에 가입한 코호트는 아직 +1개월 데이터가 없다. 그 칸을 0으로 채우면 최근 코호트가 급락한 것처럼 보인다. 데이터가 없는 칸은 비워 둬야 한다. 위의 예시 표에서 —로 둔 이유다.

분모를 매번 바꾸는 것. +2개월 리텐션을 계산할 때 분모를 +1개월 생존자로 잡으면 코호트 간 비교가 안 된다. 분모는 항상 그 코호트의 최초 인원으로 고정한다.

환불과 취소를 빼지 않는 것. 결제 후 환불한 고객이 활성으로 남아 있으면 초기 리텐션이 실제보다 좋게 나온다. 구독이라면 특히 첫 달에 영향이 크다.

기간 경계 처리. 말일에 가입한 고객과 1일에 가입한 고객이 같은 코호트인데, 경과 개월을 단순 날짜 뺄셈으로 하면 하루 차이로 다른 칸에 들어간다. 월 단위로 잘라서 계산해야 한다.

어느 정도면 괜찮은 수치인가

업종과 사업 형태에 따라 정상 범위가 크게 다르므로, 외부 벤치마크를 그대로 가져다 쓰면 오히려 잘못 판단하게 된다.

기준은 두 가지로 잡는 편이 낫다.

자사의 과거. 지난 분기 코호트와 이번 분기 코호트를 비교한다. 방향이 개선인지 악화인지가 절대 수치보다 중요하다.

곡선의 모양. 리텐션 곡선이 어느 시점에서 평평해지는지를 본다. 평평해지는 구간이 있다는 것은 그 지점을 넘긴 고객은 오래 남는다는 뜻이고, 그 지점까지 데려가는 것이 온보딩의 목표가 된다. 곡선이 계속 0을 향해 내려가기만 한다면 아직 그 지점을 못 찾은 것이다.

코호트를 무엇으로 나눌 것인가

시기만이 기준은 아니다. 같은 데이터를 다르게 묶으면 다른 질문에 답할 수 있다.

유입 채널로 나누면 어느 채널의 고객이 오래 남는지 보인다. 획득 비용이 싼 채널이 리텐션도 나쁘면 실제로는 비싼 채널이다.

첫 구매 상품으로 나누면 어떤 상품이 관계의 시작으로 좋은지 보인다. 할인 상품으로 들어온 고객이 정가 상품으로 들어온 고객보다 훨씬 빨리 빠지는 패턴은 흔하다.

가입 후 첫 주 행동으로 나누면 온보딩에서 무엇이 중요한지 보인다. 특정 기능을 첫 주에 쓴 고객의 곡선이 확연히 높다면, 그 기능까지 데려가는 것이 온보딩 설계의 목표다.

한 번에 하나씩 나눈다. 채널과 상품을 동시에 나누면 각 칸의 인원이 너무 적어져 숫자가 흔들린다.

인원이 적으면 어떻게 하나

작은 사업에서 가장 자주 부딪히는 문제다. 코호트당 20명이면 한 명 차이가 5%p를 움직인다.

세 가지 방법이 있다.

기간을 넓힌다. 월별 대신 분기별로 묶는다. 코호트 수는 줄지만 각 코호트가 커진다.

절대 수를 함께 본다. 비율만 보면 흔들리므로 원래 인원을 표에 같이 적는다. "38%"가 8명 중 3명이라는 것을 알면 해석이 달라진다.

추세만 본다. 개별 숫자를 해석하지 말고 여러 코호트에 걸쳐 같은 방향이 반복되는지만 본다. 한 코호트의 하락은 우연일 수 있지만 세 코호트 연속이면 신호다.

분석 다음에 무엇을 하나

표를 만드는 것이 목적이 아니다. 표는 어디를 손댈지 정하는 도구다.

이탈이 첫 달에 몰려 있다면 온보딩이다. 가입 직후 며칠 안에 핵심 가치를 경험하게 하는 것이 전부다. 무엇을 어떤 순서로 보낼지는 고객 이탈을 막는 운영 방법에 따로 정리했다.

이탈이 특정 시점에 몰려 있다면 그 시점에 무슨 일이 일어나는지 본다. 무료 기간 종료, 첫 갱신, 초기 재고 소진처럼 구조적인 이유가 있는 경우가 많다.

최근 코호트만 나쁘다면 유입 쪽을 본다. 채널 구성이 바뀌었거나 프로모션 조건이 달라졌을 것이다.

전 구간이 완만하게 나쁘다면 제품이나 시장의 문제이고, 콘텐츠나 이메일로 해결되지 않는다.

곡선의 모양별로 정리하면 이렇다.

마지막 줄이 기회다. 반등 지점에 무엇을 했는지 찾아내면 반복할 수 있는 수단을 하나 확보한 것이다. 대부분의 팀은 그 지점을 그냥 지나친다.

얼마나 자주 보면 되나

매일 볼 필요는 없다. 코호트는 시간이 지나야 채워지는 지표라 자주 본다고 더 알게 되지 않는다.

월 단위 사업이면 한 달에 한 번, 새 코호트가 한 줄 추가될 때 보면 된다. 그때 확인할 것은 두 가지다. 새로 추가된 코호트의 +1개월 값이 직전 코호트들과 비슷한가, 그리고 기존 코호트들의 최신 열이 예상대로 움직였는가.

분기 단위로 보는 사업이라면 분기마다 한 번이면 충분하다. 대신 그 사이에 유입 채널이나 프로모션을 바꿨다면 기록해 둬야 한다. 나중에 코호트 차이가 보일 때 원인을 대조할 수 있다.

바꾼 것을 기록하지 않으면 표는 만들어도 해석이 안 된다. 코호트 간 차이가 보여도 무엇 때문인지 알 수 없기 때문이다.

자주 묻는 질문

코호트 분석과 코호트 연구는 다른 것인가요?

용어의 뿌리는 같지만 쓰이는 곳이 다릅니다. 코호트 연구는 역학에서 특정 집단을 장기간 추적하는 연구 방법이고, 마케팅의 코호트 분석은 그 아이디어를 고객 데이터에 적용한 것입니다. 검색하면 의학 논문이 섞여 나오는 이유입니다.

데이터가 몇 개월치는 있어야 하나요?

최소 3~4개 코호트는 있어야 추세를 볼 수 있습니다. 다만 첫 코호트 하나만으로도 이탈이 초기에 몰리는지 여부는 알 수 있으므로, 데이터가 쌓이기를 기다리기보다 지금 있는 것으로 한 번 그려 보는 편이 낫습니다.

엑셀로도 충분한가요?

고객 수가 수만 명 단위를 넘지 않으면 충분합니다. 피벗 테이블 하나로 만들어집니다. 그 이상이 되거나 매주 자동으로 갱신하고 싶어질 때 도구를 검토하면 됩니다. 처음부터 도구를 도입하면 무엇을 세고 있는지 모른 채 숫자만 보게 되기 쉽습니다.

애널리틱스에 있는 코호트 보고서를 쓰면 되지 않나요?

기본 보고서는 빠르게 보기에 좋지만 활성의 정의를 우리 사업에 맞게 바꾸기 어렵습니다. 특히 구매 주기가 긴 상품은 기본 설정으로 보면 대부분의 칸이 비어 보입니다. 직접 만든 표와 함께 쓰는 것을 권합니다.

리텐션 곡선이 중간에 올라가도 되나요?

됩니다. 쉬었다가 돌아온 고객이 있다는 뜻입니다. 다만 그 시점에 무엇을 했는지 확인해 두세요. 재방문 캠페인이나 시즌 요인이 있었다면 재현 가능한 레버를 하나 찾은 것입니다.

신규 고객이 계속 늘고 있는데도 확인해야 하나요?

그때가 오히려 놓치기 쉬운 상황입니다. 유입이 늘면 총 활성 고객 수는 증가하므로 전체 지표는 좋아 보이지만, 최근 코호트의 리텐션만 조용히 나빠지고 있을 수 있습니다. 유입이 멈추는 순간 그 격차가 한꺼번에 드러납니다.

참고 자료

• 코호트 탐색 분석 — Google 애널리틱스 고객센터 — 코호트를 무엇으로 정의하고 어떤 지표로 볼지에 대한 공식 문서.
• The Value of Keeping the Right Customers — Harvard Business Review — 신규 획득 대비 기존 고객 유지의 경제성을 다룬 글.