본문으로 건너뛰기

코호트 분석, 재주문 표 만드는 엑셀 공식 5개

코호트 분석으로 재주문이 줄었는지 확인하는 법입니다. 주문 내역에 엑셀 공식 5개를 더해 가입 월별 표를 만들고, 가로 · 세로 · 대각선으로 읽어 고칠 곳을 고르는 순서를 정리했습니다.

코호트 분석, 재주문 표 만드는 엑셀 공식 5개

코호트 분석은 같은 달에 가입한 고객을 한 묶음으로 두고, 이들이 1개월 · 2개월 · 3개월 뒤에 몇 %나 다시 주문하는지 따로 보는 방법입니다. 재주문이 정말 줄었는지는 전체 재구매율 한 줄보다 가입 월별 묶음표에서 정확하게 드러납니다. 표는 주문 내역에 엑셀 공식 5개를 더하면 만들어지고, 가로 · 세로 · 대각선 세 방향으로 읽어 고칠 곳을 고릅니다.

코호트 분석은 전체 재구매율과 무엇이 다른가요?

코호트(cohort)는 같은 시기에 한 가지 조건을 충족한 고객 무리입니다 — GA4에서는 "동질 집단"이라고 부릅니다. 묶음표는 행에 가입 월, 열에 그 뒤 지난 개월 수를 둡니다. M1은 가입 1개월 뒤, M2는 2개월 뒤입니다. 칸에는 그 묶음 가운데 해당 달에 주문한 고객 비율을 적습니다.

전체 재구매율은 신규 고객이 몰린 달에 내려가 보입니다. 예를 들어 프로모션으로 가입자가 평소의 2배가 된 달에는 아직 두 번째 주문 시점이 오지 않은 고객이 분모에 섞입니다. 묶음별로 나누면 "기존 고객이 덜 돌아오는 것"과 "신규 가입자의 재주문 시점이 아직 오지 않은 것"이 갈라집니다.

가입 월 기준 묶음표는 엑셀로 어떻게 만드나요?

필요한 데이터는 주문 내역의 고객ID · 주문일과 회원 가입일입니다. 최근 1년 치 주문을 내려받으면 묶음이 12개 생기고, 가장 오래된 쪽은 11개월 뒤까지 보입니다. A열에 고객ID, B열에 주문일을 두고 아래 순서대로 공식을 넣으세요. 시트 정리는 엑셀 표 만들기 글의 관리표 양식을 그대로 써도 됩니다.

순서만들 열공식 예시하는 일
1C 가입일=MINIFS($B:$B,$A:$A,A2)가입일 자료가 없을 때 고객별 첫 주문일로 대신합니다
2D 가입 월=TEXT(C2,"yyyy-mm")묶음 이름이 됩니다
3E 경과 개월=(YEAR(B2)-YEAR(C2))*12+MONTH(B2)-MONTH(C2)가입한 달은 0, 다음 달은 1로 셉니다
4F 첫 등장=IF(COUNTIFS($A$2:A2,A2,$E$2:E2,E2)=1,1,0)한 고객이 같은 달에 여러 번 주문해도 1명으로 셉니다
5요약표 칸=SUMIFS($F:$F,$D:$D,$H2,$E:$E,I$1)H열에 가입 월, 1행에 경과 개월(0 · 1 · 2 …)을 두고 채웁니다

H열의 가입 월은 D열을 복사해 중복 항목을 지워 만드세요. MINIFS는 Microsoft 지원 문서 기준 Office 2019 또는 Microsoft 365 구독 환경에서 쓸 수 있습니다. 비율은 요약표 각 칸을 그 묶음의 가입자 수로 나눠 구합니다. 가입자 수는 회원 목록에서 월별로 세고, 첫 주문일로 묶었다면 경과 0개월(M0) 칸 값을 그대로 씁니다.

코호트 표는 가로 · 세로 · 대각선으로 어떻게 읽나요?

아래는 예시로 가정한 값입니다. 10월 말까지의 주문으로 만든 표이고, 칸은 그 묶음 가입자 가운데 해당 달에 주문한 고객 비율입니다.

가입 월가입자 수M1M2M3M4
5월800명23%15%12%8%
6월900명22%15%9%11%
7월1,500명14%6%8%—
8월850명16%14%——
9월880명23%———

같은 표라도 읽는 방향마다 알려 주는 것이 다릅니다. 대각선은 5월-M4 · 6월-M3 · 7월-M2 · 8월-M1처럼 같은 달력 월(여기서는 9월)에 걸린 칸을 잇는 선입니다.

읽는 방향비교하는 것예시 표에서 보이는 것먼저 확인할 곳
가로(한 행)한 묶음이 시간이 지나며 남는 비율5월 · 6월 묶음이 M3 무렵부터 11~12% 안팎에서 멈춤(9월 칸 제외)멈추기 전 M1~M2 구간의 재주문 안내
세로(한 열)같은 경과 시점의 묶음끼리7월 묶음 M1이 14%로 앞뒤 묶음보다 낮음그 달 가입 경로 · 첫 주문 상품
대각선같은 달력 월에 걸린 칸들9월 칸이 묶음과 상관없이 함께 내려감그 달 품절 · 가격 변경 · 배송 · 정기 메시지 기록

재주문이 줄었다면 개선 지점은 어떤 순서로 찾나요?

코호트 분석에서 고칠 곳은 원인을 확인하기 쉬운 쪽부터 좁힙니다. 아래 4단계를 차례로 밟으세요.

  1. 대각선부터 보세요. 여러 묶음이 같은 달력 월에 함께 내려갔다면 원인은 고객보다 그 시기 운영에 있습니다. 품절 · 가격 변경 · 배송 지연 · 정기 메시지 중단 같은 그 달 기록을 표 옆에 적어 맞춰 보세요.
  2. 세로로 M1을 비교하세요. 최근 묶음일수록 M1이 낮다면 첫 주문 뒤 한 달 안의 경험이 약해진 것입니다. 해당 묶음을 가입 경로나 첫 주문 상품으로 나눠 표를 한 번 더 만들면 어느 쪽에서 빠지는지 보입니다.
  3. 가로로 멈추는 지점을 찾으세요. 비율이 멈추기 전 구간이 이탈이 몰리는 곳입니다. 첫 주문부터 재주문까지의 접점을 고객 여정 지도로 펼치면 안내를 어느 시점에 둘지 정할 수 있습니다.
  4. 한 번에 하나만 바꾸세요. 바꾼 달 이후 가입자의 M1을 직전 3개 묶음 평균과 비교합니다. M1 값은 가입 다음 달이 끝나야 채워지므로 판단은 바꾼 뒤 2개월이 지나서 하세요.

주문 데이터가 여러 판매 채널과 오프라인 매장에 흩어져 있으면 고객ID를 하나로 맞추는 일이 표 만들기보다 오래 걸립니다. 이 정리를 포함해 코호트 분석을 데이터 분석 컨설팅으로 맡긴다면 데이터 출처 수 · 고객ID 정리 범위 · 매달 갱신 여부를 먼저 정해 두세요 — 견적 범위가 그만큼 분명해집니다.

자주 묻는 질문

코호트 분석은 월별로 하나요, 주별로 하나요?

재주문 주기에 맞춰 고르세요. 주기가 한 달 이상인 상품은 월별, 매주 다시 쓰는 서비스는 주별로 묶어야 이탈 시점이 보입니다. GA4 동질 집단 탐색 분석은 일일 · 주간(일요일~토요일) · 월간(1일~말일) 단위를 고를 수 있습니다.

GA4 동질 집단 탐색 분석으로 코호트 분석을 할 수 있나요?

탐색 메뉴의 템플릿 갤러리에서 동질 집단 탐색 분석을 고르고 포함 기준과 재방문 기준을 정하면 표가 만들어집니다. Google 도움말에 따르면 동질 집단은 기기 데이터만 기준으로 하고 User-ID는 고려하지 않습니다. 회원 단위 재주문을 보려면 주문 내역으로 만든 엑셀 표를 함께 쓰세요.

코호트 분석과 RFM 분석은 무엇이 다른가요?

코호트 분석은 가입 시점이 같은 묶음이 시간이 지나며 어떻게 변하는지 봅니다. RFM 분석은 지금 시점에서 고객 한 명씩 최근 주문일 · 주문 횟수 · 주문 금액으로 등급을 나눕니다. 재주문 감소 여부는 코호트로 판단하고, 메시지를 먼저 보낼 대상은 RFM으로 고르세요.

코호트 분석을 외부에 맡기면 비용은 무엇으로 정해지나요?

데이터 출처 수(자체몰 · 오픈마켓 · 오프라인), 고객ID를 하나로 맞추는 정리 작업의 양, 결과물이 한 번 만드는 표인지 매달 갱신하는 보고인지에 따라 작업량이 정해집니다. 맡기기 전에 최근 1년 치 주문 내역 · 회원 목록과 알고 싶은 질문 한 줄을 준비하면 견적 범위를 좁힐 수 있습니다.

오늘 할 일

판매 채널 관리자 화면에서 최근 1년 치 주문 내역을 내려받아 가장 오래된 가입 월 묶음 하나의 M1 재주문율을 구하세요. 가입일 · 가입 월 · 경과 개월 열만 더한 뒤 필터로 그 묶음의 경과 개월 1인 행을 고르고, 중복을 지운 고객 수를 가입자 수로 나누면 이 한 칸이 나옵니다.

FacebookX

이 글과 맞는 서비스

이어서 읽기

블로그 전체 글 목록 →

카카오톡 상담식스원 고객센터 카카오톡 채널 (새 창)