연말 잘 보내고 계시나요?

0. 데이터 준비
고객 주문 데이터를 준비합니다.
주문 날짜와 고객 식별 정보가 필수로 포함되어 있어야 합니다.
저는 고객 ID를 고객 식별 정보로 사용하겠습니다. ID가 없다면 전화번호나 이메일 주소 등 고객을 식별할 수 있는 고유한 정보로 활용하면 됩니다.

이탈률은 위와 같이 계산합니다.

X개월(또는 X일) 값이 몇인지에 따라 이탈률이 달라질 수 있기 때문에 이를 매개 변수 시트에 따로 지정해 둡니다.
저는 180일(약 6개월)로 해보겠습니다.

고객 식별 정보와 구매 날짜를 통해서 각 고객별로 다음 구매 날짜를 계산합니다.

3. 이탈 여부
아래와 같은 조건으로 이탈/재구매 여부를 계산합니다.

4. 매개 변수 정하기(날짜의 최댓값)
주문 데이터 중 가장 최신 날짜가 언제인지를 매개 변수에 작성해 둡니다.
이는 이탈률 그래프를 그릴 때 최신 날짜 이후에는 값을 표현하지 않기 위함입니다.

5. 이탈 기준 날짜
이탈 기준 날짜는 날짜 + 이탈 기준일(180일)입니다.
주문 데이터의 날짜 최댓값보다 크면 이 값은 표시하지 않습니다.

6. X개월(또는 X일) 전 구매한 고객수
피봇 테이블을 생성하고, 
이탈 기준 날짜의 구매 고객수를 계산하면, 그 값이 곧 X개월(또는 X일) 전 구매한 고객수가 됩니다.

피봇 날짜는 년-월 그룹을 만듭니다.

7. 이탈한 구매 고객수
6.에서 만든 피봇 테이블을 복사하고, 이탈 여부="이탈" 값만 필터링하면 이탈한 구매 고객수가 계산됩니다.

각 날짜에 맞게 입력되도록 6.에서 만든 X개월(또는 X일) 전 구매한 고객수 옆에 작성합니다.

8. 이탈률
이탈률 = 이탈한 구매 고객수 / X개월(또는 X일) 전 구매한 고객수
위의 식으로 이탈률을 계산합니다.

9. 이탈률 차트
차트를 생성하면 월별로 이탈률이 어떻게 변화하는지 확인할 수 있습니다.

10. 이탈 기준 변경
이탈 기준을 180일에서 30일로 짧게 변경해 보면 이탈률이 더 커지는 것을 확인할 수 있습니다.

이렇게 이탈률이 어떻게 변화하는지 추적할 수 있습니다.

여기에 추가로 X기간 객단가를 구하면 아래와 같은 공식으로 고객 생애 가치를 계산할 수 있습니다.

이를 기반으로 마케팅 전략을 수립해 보시기 바랍니다.

위에서 이탈률 구하는 과정의 계산식이 모두 포함된 샘플 파일을 공유해 드리겠습니다.
참고하셔서 님의 비즈니스에도 적용해 보시기 바랍니다.