무료 코호트 유지율 매트릭스 엑셀 템플릿: 월별 코호트의 고객 유지율과 순매출 유지율 (수식 설명 포함)

구성

통합 문서에는 시트가 5개 있어요. 입력하는 시트는 몇 개뿐이고 나머지는 계산돼요.

시트	용도	입력하는 것	자동 계산되는 것
Start Here	설정과 핵심 유지율 숫자	첫 코호트 월, 데이터가 있는 최근 월	획득·활성 고객 수, 최근 MRR, 3개월·6개월 평균 유지율, 6개월 NDR, 최고 코호트
Customers	고객당 한 행(150명)	고객, 코호트 월, 이탈 월(활성이면 비움), 시작 MRR	상태, 활성 개월 수
MRR Log	고객·월당 한 행(1,000행)	월, 고객, MRR	Customers에서 찾은 코호트
Cohorts	매트릭스 셋: 활성 고객 수, 고객 유지율 %, 순매출 유지율 %	없음	모든 셀, 코호트 크기, 개월별 평균
Checks	데이터 점검 5개	없음	OK 또는 Review

시작하기 전에

결제 시스템에서 둘을 내보내세요. 첫 결제 월과 마지막 결제 월이 있는 고객 목록, 그리고 월별 MRR 이력. 날짜는 매월 1일이어야 해요.
이탈의 기준을 정하세요. 템플릿은 이탈 열의 월부터 고객을 이탈로 봐요. 돌아온 고객은 새 코호트의 새 행이어야 해요.
매트릭스는 코호트 12개와 12개월을 다뤄요. 더 길거나 오래된 이력도 범위를 늘리면 돼요(팁 참고).

단계별 사용법

Start Here에 날짜 둘 설정
B6은 매트릭스에 보이는 첫 코호트 월, B7은 데이터가 있는 최근 월이에요. B7 이후 달의 셀은 오해를 부르는 0 대신 비어 있어요.
Customers 탭 채우기
고객당 한 행: 이름, 코호트 월(첫 결제 월, 매월 1일로), 이탈 월(활성이면 비움), 시작 MRR. 상태와 활성 개월 수는 계산돼요.
MRR Log 탭 채우기
고객·월당 한 행에 그 달 청구한 MRR을 입력하세요. 코호트 열이 Customers 탭에서 각 고객을 찾고, 이름이 틀리면 "Not found"를 표시해요.
Cohorts 탭 읽기
블록 셋. 첫째는 활성 고객 수를 세고, 둘째는 코호트 크기로 나눠 고객 유지율 %를, 셋째는 각 달의 MRR을 코호트의 0개월 MRR로 나눠 순매출 유지율 %를 보여 줘요. 행은 코호트, 열은 가입 후 개월이라 각 행을 왼쪽에서 오른쪽으로 읽으면 그 코호트가 나이 드는 모습이에요.
코호트 비교
히트맵이 빨강(30%)에서 황색(80%)을 거쳐 초록(110%)으로 칠해요. 각 블록 맨 아래 Average 행이 보통의 곡선이에요. 평균 행보다 초록인 코호트 행은 평소보다 잘하고 있는 거예요.
Checks 탭 확인
Customers에 없는 MRR 로그 이름, 중복 고객명, 코호트 월보다 앞선 이탈 월, 1일이 아닌 날짜, 첫 코호트보다 앞선 최근 월을 잡아 줘요.

수식 지도

통합 문서의 모든 수식을 시트와 열별로 정리했어요. 복사된 수식 열은 한 번만 세고, 적용 범위를 함께 표시해요.

시트	셀	함수	계산 내용	읽는 값	쓰이는 곳
Customers	`F5` 154행까지 아래로 복사됨	IF, YEAR, MONTH	활성 개월 수	B5, C5	고객별 간단한 생애 수치예요. 매트릭스는 이 값에 의존하지 않아요.
MRR Log	`D5` 1004행까지 아래로 복사됨	IF, IFERROR, INDEX, MATCH	코호트 조회	B5	NDR 블록이 이 열이 코호트와 같은 MRR을 더해요.
Cohorts	`A7` 49행까지 아래로 복사됨	DATE, YEAR, MONTH	코호트 월 순서	A6	행의 모든 COUNTIFS와 SUMIFS가 이 값을 코호트 키로 써요.
Cohorts	`B6` 49행까지 아래로 복사됨	COUNTIF	코호트 크기	—	고객 유지율 %의 분모예요.
Cohorts	`C6` 17행까지 아래로 복사됨	IF, DATE, YEAR, MONTH, COUNTIFS	특정 개월의 활성 고객 수	—	고객 유지율 블록이 이 수를 코호트 크기로 나눠요.
Cohorts	`C22` C22부터 N33까지 채워짐	IF, OR	고객 유지율 %	—	Start Here가 이 블록의 F열(3개월)과 I열(6개월)을 평균해요.
Cohorts	`C38` 49행까지 아래로 복사됨	IF, DATE, YEAR, MONTH, SUMIFS	순매출 유지율 %	—	Start Here B14가 이 블록의 6개월을 평균해요.
Cohorts	`C34` C34부터 N50까지 채워짐	IFERROR, AVERAGE	개월별 평균	—	각 코호트와 비교할 보통의 유지율 곡선이에요.
Start Here	`B10`	COUNTIFS	현재 활성 고객 수	—	B9의 누적 획득 고객 수와 함께 표시돼요.
Start Here	`B15`	IFERROR, INDEX, MATCH, MAX	3개월 유지율 최고 코호트	—	그 코호트의 공통점(채널, 가격, 온보딩)을 물어볼 가치가 있어요.
Checks	`B6`	IF, SUMPRODUCT, COUNTIF	중복 고객명 없음	—	중복은 모든 매트릭스에서 두 번 세어져요.

모든 수식 설명

아래 수식은 파일에서 그대로 가져온 것이라 여기서 읽는 내용이 셀에 들어 있는 내용과 정확히 같아요. 엑셀에서 셀을 클릭하면 수식 입력줄에서 볼 수 있어요.

활성 개월 수Customers!F5 · 154행까지 아래로 복사됨

=IF(A5="", "", IF(C5="", (YEAR('Start Here'!$B$7)-YEAR(B5))*12+MONTH('Start Here'!$B$7)-MONTH(B5)+1,
    (YEAR(C5)-YEAR(B5))*12+MONTH(C5)-MONTH(B5)))

코호트 월부터 이탈 월까지, 활성 고객은 최근 데이터 월까지 더하기 1. 연·월 산술이라 일수에 의존하지 않아요.

부분별 설명

C5="": 아직 활성: 최근 월(Start Here B7)까지 포함해 세므로 +1.
(YEAR(C5)-YEAR(B5))*12+MONTH(C5)-MONTH(B5): 두 1일 날짜 사이의 온전한 개월 수.

예시 데이터로 보면: B5 = 2025년 11월 1일, C5 = — → F5 결과 12

어떻게 쓰이나: 고객별 간단한 생애 수치예요. 매트릭스는 이 값에 의존하지 않아요.

바꾸는 방법: 바꿀 것 없음.

배우기: 엑셀 텍스트 함수와 날짜 함수 사용법 →

코호트 조회MRR Log!D5 · 1004행까지 아래로 복사됨

=IF(B5="", "", IFERROR(INDEX(Customers!$B$5:$B$154, MATCH(B5, Customers!$A$5:$A$154, 0)),
    "Not found"))

Customers 탭에서 고객을 찾아 코호트 월을 돌려줘요. 그래서 매출 매트릭스가 MRR 행을 코호트별로 묶을 수 있어요.

부분별 설명

MATCH(B5, Customers!$A$5:$A$154, 0): 고객명의 행 위치(정확히 일치).
INDEX(Customers!$B$5:$B$154, …): 그 행의 코호트 월.
IFERROR(…, "Not found"): 철자가 틀린 이름은 #N/A 대신 "Not found"를 표시하고 Checks 탭이 그 수를 세요.

예시 데이터로 보면: B5 = Birch Labs 001 → D5 결과 2025년 11월 1일

어떻게 쓰이나: NDR 블록이 이 열이 코호트와 같은 MRR을 더해요.

바꾸는 방법: 바꿀 것 없음.

배우기: 엑셀 INDEX와 MATCH 함수 사용법 →

코호트 월 순서Cohorts!A7 · 49행까지 아래로 복사됨

=DATE(YEAR(A6), MONTH(A6)+1, 1)

각 행은 이전 행의 다음 달이에요. 첫 행은 Start Here B6에 연결돼요.

부분별 설명

DATE(YEAR(A6), MONTH(A6)+1, 1): DATE가 MONTH()+1을 다음 해로 알아서 넘겨요(13월은 1월이 돼요).

예시 데이터로 보면: A6 = 2025년 11월 1일 → A7 결과 2025년 12월 1일

어떻게 쓰이나: 행의 모든 COUNTIFS와 SUMIFS가 이 값을 코호트 키로 써요.

바꾸는 방법: 바꿀 것 없음.

배우기: 엑셀 텍스트 함수와 날짜 함수 사용법 →

코호트 크기Cohorts!B6 · 49행까지 아래로 복사됨

=COUNTIF(Customers!$B$5:$B$154, $A6)

이 달에 가입한 고객 수.

부분별 설명

Customers!$B$5:$B$154: 코호트 월 열.
$A6: 이 행의 코호트 월.

예시 데이터로 보면: B6 결과 11

어떻게 쓰이나: 고객 유지율 %의 분모예요.

바꾸는 방법: 바꿀 것 없음.

배우기: 엑셀 COUNTIF, COUNTIFS 함수 사용법 →

특정 개월의 활성 고객 수Cohorts!C6 · 17행까지 아래로 복사됨

=IF(DATE(YEAR($A6), MONTH($A6)+0,
    1)>'Start Here'!$B$7, "",
    COUNTIFS(Customers!$B$5:$B$154,
    $A6, Customers!$C$5:$C$154,
    "")+COUNTIFS(Customers!$B$5:$B$154,
    $A6, Customers!$C$5:$C$154,
    ">"&DATE(YEAR($A6), MONTH($A6)+0, 1)))

코호트에서 아직 활성인(이탈 월 없음) 고객과 해당 월 이후에 이탈한 고객의 합이에요. D–N열은 0 대신 1–11의 오프셋으로 같은 수식을 써요.

부분별 설명

DATE(YEAR($A6), MONTH($A6)+0, 1)>'Start Here'!$B$7: 이 셀의 달이 최근 데이터 월보다 뒤면 비워요.
COUNTIFS(cohort, $A6, churn, ""): 코호트에서 아직 활성인 고객.
COUNTIFS(cohort, $A6, churn, ">"&DATE(…)): 이탈했지만 이 달 이후라서 그때는 결제 중이던 고객.

예시 데이터로 보면: C6 결과 11

어떻게 쓰이나: 고객 유지율 블록이 이 수를 코호트 크기로 나눠요.

바꾸는 방법: 11개월 너머로 늘리려면 N열을 오른쪽으로 복사하고 복사된 수식의 오프셋을 12, 13, …으로 바꾸세요.

배우기: 엑셀 COUNTIF, COUNTIFS 함수 사용법 →

고객 유지율 %Cohorts!C22 · C22부터 N33까지 채워짐

=IF(OR(C6="", $B22=0), "", C6/$B22)

그 개월의 활성 고객을 코호트 크기로 나눠요. 수가 비어 있거나 코호트가 비면 비워 둬요.

부분별 설명

C6="": 그 달 데이터 아직 없음: 비움.
$B22=0: 빈 코호트: 0으로 나누지 않고 비움.
C6/$B22: 활성 ÷ 크기. $B가 오른쪽으로 복사해도 크기 열을 고정해요.

예시 데이터로 보면: C22 결과 1

어떻게 쓰이나: Start Here가 이 블록의 F열(3개월)과 I열(6개월)을 평균해요.

바꾸는 방법: 바꿀 것 없음.

배우기: 엑셀 상대 참조, 절대 참조, 혼합 참조 이해하기 →

순매출 유지율 %Cohorts!C38 · 49행까지 아래로 복사됨

=IF(DATE(YEAR($A38), MONTH($A38)+0,
    1)>'Start Here'!$B$7, "",
    IF(SUMIFS('MRR Log'!$C$5:$C$1004, 'MRR Log'!$D$5:$D$1004, $A38, 'MRR Log'!$A$5:$A$1004, $A38)=0,
    "", SUMIFS('MRR Log'!$C$5:$C$1004, 'MRR Log'!$D$5:$D$1004, $A38, 'MRR Log'!$A$5:$A$1004, DATE(YEAR($A38), MONTH($A38)+0, 1))/SUMIFS('MRR Log'!$C$5:$C$1004, 'MRR Log'!$D$5:$D$1004, $A38, 'MRR Log'!$A$5:$A$1004, $A38)))

특정 달의 코호트 MRR을 코호트 첫 달의 MRR로 나눠요. 100%를 넘으면 확장이 이탈과 다운그레이드보다 커요.

부분별 설명

SUMIFS('MRR Log'!$C$5:$C$1004, 'MRR Log'!$D$5:$D$1004, $A38, 'MRR Log'!$A$5:$A$1004, DATE(…)): 조회된 코호트가 이 행과 같은 고객의 대상 월 MRR.
SUMIFS(…, $A38): 코호트 자신의 달(0개월)에 대한 같은 합계.
IF(…=0, "", …): 코호트에 0개월 매출이 없으면 비움.

예시 데이터로 보면: C38 결과 1

어떻게 쓰이나: Start Here B14가 이 블록의 6개월을 평균해요.

바꾸는 방법: 바꿀 것 없음.

배우기: 엑셀 SUMIF, SUMIFS 함수 사용법 →

개월별 평균Cohorts!C34 · C34부터 N50까지 채워짐

=IFERROR(AVERAGE(C22:C33), "")

한 개월 열에 대한 코호트 12행의 평균이고, 빈 셀은 무시해요. IFERROR는 열 전체가 빈 경우를 처리해요.

부분별 설명

AVERAGE(C22:C33): AVERAGE는 빈 셀을 자동으로 건너뛰어요.
IFERROR(…, ""): 평균할 것이 없을 때.

예시 데이터로 보면: C34 결과 1

어떻게 쓰이나: 각 코호트와 비교할 보통의 유지율 곡선이에요.

바꾸는 방법: 바꿀 것 없음.

배우기: 엑셀 AVERAGE 함수 사용법 →

현재 활성 고객 수Start Here!B10

=COUNTIFS(Customers!$A$5:$A$154,
          "<>", Customers!$C$5:$C$154,
          "")

이름은 있지만 이탈 월이 없는 고객을 세요.

부분별 설명

Customers!$A$5:$A$154, "<>": 이름이 있음.
Customers!$C$5:$C$154, "": 이탈 월이 비어 있음.

예시 데이터로 보면: B10 결과 69

어떻게 쓰이나: B9의 누적 획득 고객 수와 함께 표시돼요.

바꾸는 방법: 바꿀 것 없음.

배우기: 엑셀 COUNTIF, COUNTIFS 함수 사용법 →

3개월 유지율 최고 코호트Start Here!B15

=IFERROR(INDEX(Cohorts!$A$22:$A$33,
         MATCH(MAX(Cohorts!$F$22:$F$33), Cohorts!$F$22:$F$33, 0)),
         "")

가장 높은 3개월 고객 유지율을 찾아 그 행의 코호트 월을 돌려줘요.

부분별 설명

MAX(Cohorts!$F$22:$F$33): 가장 높은 3개월 유지율.
MATCH(…, Cohorts!$F$22:$F$33, 0): 그 행.
INDEX(Cohorts!$A$22:$A$33, …): 그 행의 코호트 월.

예시 데이터로 보면: B15 결과 2025년 11월 1일

어떻게 쓰이나: 그 코호트의 공통점(채널, 가격, 온보딩)을 물어볼 가치가 있어요.

바꾸는 방법: 6개월을 보려면 세 참조의 F를 모두 I로 바꾸세요.

배우기: 엑셀 INDEX와 MATCH 함수 사용법 →

중복 고객명 없음Checks!B6

=IF(SUMPRODUCT((Customers!$A$5:$A$154<>"")*(COUNTIF(Customers!$A$5:$A$154, Customers!$A$5:$A$154)>1))=0,
    "OK", "Review")

비어 있지 않은 모든 이름에 대해 몇 번 나오는지 세요. 1보다 크면 중복이에요.

부분별 설명

Customers!$A$5:$A$154<>"": 이름이 있는 행만.
COUNTIF(range, range)>1: 각 이름을 열 전체와 비교해 세요. 이름이 반복되면 TRUE.
SUMPRODUCT(…): 중복 행을 더해요.

예시 데이터로 보면: B6 결과 OK

어떻게 쓰이나: 중복은 모든 매트릭스에서 두 번 세어져요.

바꾸는 방법: 바꿀 것 없음.

배우기: 엑셀 COUNTIF, COUNTIFS 함수 사용법 →

팁과 한계

용량: 고객 150명, MRR 로그 1,000행, 코호트 12개 × 12개월. 목록을 늘리려면 수식에서 Ctrl+H로 $154와 $1004를 더 큰 행으로 바꾸고, 매트릭스를 늘리려면 마지막 열을 오른쪽으로 복사해 개월 오프셋을 조정하세요.
고객 유지율은 "몇 명이 남았나", 순매출 유지율은 "매출이 얼마나 남았나"에 답해요. 코호트가 고객을 잃으면서도 매출은 늘 수 있어서 둘 다 보여 줘요.
떠났다가 돌아온 고객은 새 고객 행으로(이름 뒤에 -2를 붙여) 입력해 두 코호트 모두 정직하게 유지하세요.
단일 혼합 이탈률과 LTV는 이 사이트의 SaaS 단위 경제성 템플릿을 보세요.

도움이 되었나요? 잘못된 내용이 있나요? 의견 보내기 · admin@arumsoft.com

SaaS 코호트 유지율과 순매출 유지율(NDR) 매트릭스 엑셀 템플릿

이 템플릿으로 할 수 있는 일

구성

시작하기 전에

단계별 사용법

Start Here에 날짜 둘 설정

Customers 탭 채우기

MRR Log 탭 채우기

Cohorts 탭 읽기

코호트 비교

Checks 탭 확인

수식 지도

모든 수식 설명

팁과 한계

이 템플릿에 쓰인 기능 배우기