고객 이탈리스크 민감도 분석 연습

이번 영상에서는 고객 리스크, 고객 이탈 리스크 민감도 분석 연습을 통해서 참조라든가 서식 등 이런 구글 스프레드시트의 기능을 사용하는 방법을 다시 한번 복습해보도록 하겠습니다. 우리가 비즈니스 분석을 할 때 굉장히 많이 하는 것 중에 하나가 고객의 이탈 리스크를 계산해서 민감도 분석을 하는 것입니다. 일단 고객의 이탈 리스크라고 하는 개념이 있는데요 이탈 리스크라고 하는 것은 쉽게 생각해서 우리가 어떤 고객이 있을 때 우리가 데리고 있는 이 고객이 있는데 우리의 고객이 만약에 어떤 이유로건 우리와 거래를 끊게 된다면 우리가 이 고객을 잃어버린다면 우리가 입게 될 손실이 얼마인가 라고 하는 것이 바로 고객의 이탈 리스크입니다 그래서 우리는 이 고객을 어느 정도의 노력을 들여서 관리해야 하는가 라는 것을 결정할 때 이 고객을 잃어버린다면 우리는 얼마나 큰 손해를 입는가 라고 하는 이탈 리스크를 계산해 보는 거죠 근데 고객의 이탈 리스크를 계산하는 방법은 굉장히 간단합니다 어떠한 집단이 있을 때 자 우리가 앞서서 고객의 매출액 정보를 가지고 이 고객들이 얼마나 자주 얼마나 많이 우리랑 거래를 했느냐 라고 하는 것을 토대로 모든 고객을 한 8,900명 정도 되는 고객이 있었죠 이 고객을 A등급 B등급 C등급으로 3개의 등급으로 나눴습니다 그리고 지금 자료에 제가 미리 우리가 앞서서 연습한 함수이기 때문에 COUNTIF 함수를 이용해서 전체 고객 명단에서 A등급인 고객은 몇 명이나 되는가 라고 하는 것을 계산을 미리 해놨고요 계산에 따르면 A등급은 266명 B등급은 825명 C등급은 7301명이다 라고 하는 이러한 수치를 계산을 했습니다 또 이 사람들이 그러면 A등급에 속하는 266명의 고객들은 그렇다면 평균적으로 얼마 정도의 매출을 냈는가 그들의 평균적인 매출액은 얼마인가 라고 하는 것도 우리가 계산할 수 있습니다 A등급에 속하는 사람들의 평균 매출액은 얼마인가? 역시 우리가 앞서서 연습했던 AVERAGEIF 함수를 이용해서 미리 계산을 넣어 두었습니다. 고객의 매출액에서 A등급에 속한 고객의 매출액을 평균을 내라 라고 하는 것이죠. 그래서 평균된 결과 A등급의 고객은 아무래도 역시 거래 횟수도 많고 평균적인 거래 금액도 높네요 72만원 정도 B등급은 71만원 정도 C등급은 68만원 정도를 거래한 것을 우리가 확인을 했습니다 이렇게 한 명의 고객이 우리와 거래하는 금액 고객당 매출액이라고 하는 것을 우리가 다른 말로 객단가라고 부릅니다 고객당 단가인 거죠 한 명의 고객으로부터 발생하는 인당 매출액 이것을 ARPU, 객단가라고 부릅니다 고객당 매출액인 거죠 고객의 이탈 리스크라고 하는 것은 우리가 이 고객으로부터 벌어들일 수 있는 돈을 잃어버리는 금액이 얼마인가 라고 하는 것이잖아요 그래서 만약에 A등급에 속하는 266명의 고객이 모두 우리를 떠나간다 라고 하면 우리가 입게 되는 손실 이탈 리스크라고 하는 것은 고객수 266 곱하기 객단가 726,509원 그게 바로 우리가 A등급의 고객을 모두 잃어버렸을 때 우리가 입게 될 손실의 규모 이탈 리스크가 됩니다 자 그러면 각각의 등급별로 이탈 리스크가 얼마인가 라고 하는 것 계산 한번 해볼까요 A등급 고객의 이탈 리스크는 고객수 C6번 셀 곱하기 객단가 726,509원이라고 하는 금액을 계산할 수 있습니다. 굉장히 큰 금액이 계산이 되네요. 마찬가지로 다른 등급들도 이렇게 채우기 핸들로 채워주면 이렇게 금액을 얻을 수가 있습니다. 그런데 이것은 각각의 집단에 속한 고객을 모두 잃어버렸을 때 이 정도 손실을 입는다 라는 거잖아요 하지만 현실적으로 어느 날 갑자기 A등급 고객이 모두 다 우리를 떠나진 않을 겁니다 그들이 모두 언제나 우리 고객으로 남아있지는 않을 겁니다. 그들을 100% 고객으로 유지하는 것이 우리의 목표이긴 하지만 여러 가지 현실적인 여건상 일부는 떠날 거고 일부는 우리에게 계속 고객으로 남아있게 되겠죠. 우리가 비즈니스적으로 좀 더 고객을 관리하려고 노력을 하면 더 많은 사람들이 남아있게 될 겁니다 뒤집어 생각하면 우리가 이 정도의 손실은 불가피하게 비용을 생각할 때 고객을 유지 관리하기 위해서 들어가는 비용을 생각할 때 이 정도 손실은 조금 어쩔 수 없이 감수해야 할 것 같다 라고 하는 적정선을 판단하는 것이 필요하다 라는 거죠 고객을 유지 관리하기 위해서 들어가는 비용과 고객을 잃어버렸을 때 얻게 되는 이탈 리스크라고 하는 것을 같이 생각을 해줘야 됩니다 그래서 이탈 리스크의 계산을 하는 것은 각각의 고객 집단이 어느 정도 이탈했을 때 우리가 어느 정도 손실을 입게 되는가 라고 하는 것을 민감도 분석을 통해서 우리가 통상적으로 파악을 합니다 즉 고객이 정말 얼마나 이탈할지는 아무도 모릅니다 그래서 만약에 전체 고객의 5% 정도가 떠난다면 그때 우리가 입게 될 손실은 얼마인 거고 10%일 땐 어느 정도 되고 15% 20% 25% 각각의 상황에 대해서 우리가 그때의 손실 규모가 얼마인가 라고 하는 것을 한번 가늠해 보는 거죠 가능한 모든 상황을 대입해서 가능한 모든 값을 현황표를 만들어서 파악하는 방법을 우리가 민감도 분석이라고 부릅니다 아주 간단한 거니까 민감도 분석표를 한번 만들어 보도록 하겠습니다 이탈 리스크 100%일 때 이 정도 규모, 이 6번 셀에 있는 규모의 손실을 입게 된다 스프레드시트 실전 테크닉 구글 스프레드시트는 굉장히 영리한 추천 기능을 가지고 있습니다 여러분께서 보시고 추천 기능 너무 좋다 라고 생각하시면 클릭해서 입력을 하셔도 좋습니다 바로 입력하셔도 좋고요 만약에 나는 이렇게 추천되는 게 싫다 라고 하실 수가 있잖아요 그럴 경우에는 오른쪽에 있는 이 셀에서 추천 수식을 숨기기 라고 하는 것을 선택하실 수도 있습니다 숨기기 하시면 직접 입력하실 수도 있는 거죠 나는 이것 곱하기 F5번 셀에 있는 이 값을 쓰겠다 라고 수식을 직접 입력하실 수도 있습니다 지금 보시는 수식 추천 기능이라고 하는 것은 위쪽에 있는 도구 탭을 보시면 도구 탭에 자동완성이라고 하는 게 있고요 자동완성에서 가운데에 있는 수식 추천 사용이라는 게 켜져 있을 경우에 나타나게 됩니다 만약에 이 수식 추천이라고 하는 것이 난 오히려 불편한 것 같다 라고 생각하시면 이 체크를 클릭하셔서 체크를 꺼주시면 수식 추천이 나타나지 않습니다 실제로 안 나타나는지 한번 볼까요 제가 다시 한번 는 하고 입력해도 수식 추천이 나타나지 않는 걸 보실 수가 있습니다 스프레드시트의 수식 추천 사용 여부는 도구 자동완성에서 여러분께서 결정하실 수 있습니다. 이렇게 이탈 리스크를 계산했습니다. A집단의 266명의 고객 중에 만약에 5%의 고객이 우리를 떠난다 라고 하면 그때 우리에게 예상되는 손실은 약 966만원 정도다 라고 하는 값을 얻을 수가 있는 거죠. 자 그럼 같은 방법으로 나머지 칸도 모두 채우면 되겠네요 같이 채워 보실까요? 그러면 이것을 채우기 핸들로 쭉 늘리고 또 쭉 늘려 보니까 우리가 예상한 것과는 굉장히 다른 결과를 얻게 됐습니다 지금 보시면 966만원 정도 5%가 떠났을 때 966만원의 손실이 예상되는데 30%면 훨씬 많은 사람이 떠나는 거잖아요 하지만 이때 예상되는 리스크는 달랑 2,174원이다 라고 나와 있습니다 왜 그럴까 수식을 이렇게 클릭해 보시면 우리가 생각하는 것은 이때에 참조해야 하는 것이 이탈 리스크 이탈 리스크라고 하는 것과 그리고 여기 30%를 참조해야 할 것 같은데 보니까 굉장히 엉뚱하게도 전혀 엉뚱하게 바로 옆 칸을 참조하고 있는 것을 보실 수가 있습니다 바로 옆 칸을 이렇게 참조해서 실제 계산이 이루어져 있죠 보시면 이렇게 참조를 하고 있는 겁니다 이건 우리가 원하던 바가 아니었습니다 자 그러니까 이렇게 채우기 핸들로 이렇게 입력하면 안 될 것 같습니다 이럴 경우에 우리가 이것을 조정해 주기 위해서 참조를 사용하게 되는데요 참조를 사용할 때 지금 보시면 이 셀에 들어가는 수식은 이 6번 셀 곱하기 F5번 셀을 사용합니다 이 6번 셀 곱하기 F5번 셀을 사용하는데 지금 우리는 이탈률의 퍼센트가 옆으로 예를 들어서 5번 행에 있는데 5번 행에 있는 값이 이렇게 옆으로 이동하면서 계속 참조되기를 원하고 있습니다 그러니까 이탈률을 의미하는 F5번 셀이라고 하는 것은 5번 행은 유지되면서 F, G, H 이렇게 바뀌어 나가야 되는 거죠 그러니까 여기에는 5번 행 앞에 여기 앞에 이렇게 고정이 필요할 것 같습니다 마찬가지로 이 6번 셀에는 이탈 리스크를 계산해 주는 이 6번, 이 7번, 이 8번 이렇게 아래로 참조하면서 나가야 될 거라고 우리는 생각하고 있습니다 그러면 이 열은 유지되고 6, 7, 8 바뀌어 나가야겠죠? 그러니까 이 경우에는 이 앞에 고정이 필요할 거라고 생각할 수 있습니다 이것이 바로 혼합 참조인 거죠 우리는 앞에 영상에서 참조라고 하는 것이 기본적으로 특정 칸을 잡아주는 절대 참조, 혼합 참조, 상대 참조라고 하는 것이 있고 또한 구글 스프레드시트에는 좀 독특한 열린 참조라고 하는 기능도 있다 라고 하는 것도 살펴보았습니다 자 그러면 참조를 바꿔줄까요? 수식에서 E6번 셀이라고 하는 것을 F4키를 눌러서 이 앞에 고정을 이렇게 시켜 주겠습니다 F4키를 누를 때마다 계속 $ 표시는 바뀌게 되죠 F5번도 이렇게 F4키를 눌러서 적절한 위치를 잡아 주도록 하겠습니다 지금 보시는 F6번 셀의 값은 전혀 바뀐 것이 없지만 이제 이것을 채우기 핸들을 가지고 이렇게 끌어가 보고 아 네 금액이 멀쩡한 것 같죠 이렇게 끌어가 보시면 우리가 원했던 그대로 정상적인 값이 들어온 것을 보실 수가 있습니다 자 그러면 이 값을 가지고 우리가 이제 판단을 해야 돼요 아 그럼 이탈 리스크는 어느 정도로 하는 게 좋을까 우리 이 금액이 어느 정도 손실인 걸까 가늠을 해야 됩니다 그런데 우리가 사람이다 보니까 이런 데이터 값을 볼 때 이렇게 큰 값을 다루면 사실상 이 값들 사이에서의 차이를 알아보는 게 그렇게 쉽지는 않습니다. 이 경우에 우리가 데이터를 조금 더 알아보기 쉽게 하기 위해서 서식을 변경해 주면 굉장히 편리하죠. 서식을 좀 변경해 보겠습니다. 이 값의 표현 서식을 좀 바꿔볼까요? 이 서식을 바꿔줄 칸을 잡아서 이쪽에 보시면 서식을 보겠습니다 숫자 서식을 보겠습니다 라고 하는 것이 있습니다 자 우리가 앞에서 다 살펴 보았죠 숫자 표시 방법을 바꾸겠다 쭉 아래로 바꿔서 내 마음대로 바꾸겠다 맞춤 숫자 형식을 선택해 주고 맞춤 숫자 형식에서 예를 들어서 가장 기본이 되는 모양은 이겁니다 아래쪽에 샘플이 표시되고 있죠 아래쪽에 샘플이 표시되고 있습니다 그런데 샘플 표시를 따라가려고 해도 지금 현재 너무 단위가 커요 그래서 이걸 보고 차이를 알아보기가 어렵습니다 이럴 경우에 우리가 좀 너무 작은 단위는 좀 지워줘도 괜찮지 않겠습니까 대략적으로 보니까 억원 정도로 표시해 주면 별 문제가 없을 것 같긴 해요 몇억원 이 정도로 표현하면 될 것 같습니다 그럼 일단 억원에 가장 가까운 단위를 만들기 위해서 쉼표를 2개 붙여서 100만원 단위로 한번 끊어 보겠습니다 갑자기 샘플 0이 사라져 버렸네요 이거 어떻게 되는 걸까 혼란스럽습니다 지금 뒤에 쉼표를 2개 붙이면 우리가 100만 단위 이하 아래에 있는 0 6개는 삭제하겠다 라고 하는 표시를 한 겁니다 자 그러면 바로 앞에 있는 0 이라고 하는 것은 100만 단위를 이야기합니다 그럼 또 바로 앞에 있는 것은 천만 단위를 이야기 하겠죠 천만 단위를 얘기합니다 좋습니다 그러면 이제 여기에 우리가 점을 찍어 주면 이제 그리고 앞쪽에 있는 쉼표를 지워주면 우리는 몇 억원 몇 점 소수점 아래 얼마 라는 방식으로 억원을 표시할 수 있게 됩니다 어 값이 억원으로 서식이 표현되는 거니까 큰 단위 표에서 억원이다 라고 한번 적어 줄까요 이 데이터 값을 이렇게 몇억원이다 라고 적어 줘 보겠습니다 조금 알아보기 쉽게 하기 위해서 샵을 0으로 바꿔 보겠습니다 지금 보시는 바와 같이 적용하시면 굉장히 복잡하게 표현되었던 그러한 데이터들이 이렇게 간단하게 표현을 해줄 수가 있습니다 지금 보시는 것처럼 A등급 고객의 5% 정도를 우리가 잃어버린다 라고 가정하면 5%의 고객이 우리를 떠난다 라고 가정하면 우리가 입을 손실은 약 0.1억원 천만원 정도를 우리가 잃어버리게 된다 라는 거죠 그리고 A등급 고객의 30% 정도가 우리를 떠나면 그때 우리가 입을 손실의 규모는 어느 정도로 예상이 됩니까 약 5천만원 정도로 예상할 수 있다 라는 것입니다 그런데 아까는 숫자가 너무 복잡해서 잘 몰랐는데 지금 보니까 우리는 A등급 고객이 너무 중요하다고 생각했는데 C등급을 보니까 C등급 고객의 5%가 떠나면 약 2.5억원의 손실을 입게 되고 C등급 고객의 30%가 떠나면 그때 입을 손실은 15억원 정도로 예상이 됩니다 그러면 사실상 우리 사업 규모를 딱 생각할 때 우리가 단골이 소중해 객단가가 높은 사람이 소중해 라는 이러한 기준을 가지고 우리는 단골 고객인 A등급 B등급 고객들은 굉장히 우대하고 이 사람들은 굉장히 중요하게 생각하고 어쩌다 한 번씩 거래를 하는 한 번 정도 거래한 한 번 두 번 거래한 이런 C등급 고객에 대해서는 그 사람들은 우리 단골이 아니니까 신경 안 써도 돼 라고 판단을 하기에는 굉장히 큰 문제가 있는 거죠 아직 우리 비즈니스 이 데이터 한 13,000건 정도의 데이터를 토대로 볼 때 아직 우리 비즈니스는 이렇게 어쩌다 한번 사는 일명 뜨내기 손님들 한번 사시는 이러한 분들까지도 다 소중히 여기지 않고 단골한테만 집중했다가는 너무 큰 손실을 입을 사업이다 라고 하는 것을 우리가 이러한 민감도 분석의 수치를 토대로 해서 판단할 수가 있습니다 여러분께서 실질적으로 직무에서 하시는 업무도 이러한 형태의 민감도 분석 많이 사용하실 텐데요 이러한 표를 만들 때 우리는 적절한 참조를 사용해야 되고 외부의 데이터, 다른 시트에 있다거나 하는 이러한 데이터를 적절히 참조하고 이것을 다시 합산하기 위해서 다양한 조건부 계산을 동원하고 또 이와 같이 참조하는 셀의 위치를 참조를 잡아줄 때 적절한 혼합 참조를 사용해 준다거나 또한 데이터가 너무 단위가 크다면 서식을 조절해 준다거나 하는 이러한 다양한 방법들을 통해서 우리가 가지고 있는 데이터를 가지고 이와 같이 어떠한 잘 요약된 표를 만들 수가 있고 그것으로부터 뭔가 정말 경영적으로, 비즈니스적으로 의사결정할 수 있는 지금 이 회사 같은 경우는 우리는 한 번 오는 이 고객들을 아직은 그들을 단골로 만드는 것에 집중해야 되는 거지 소수의 단골 고객에게 집중할 상황은 아니구나 라고 하는 판단을 내릴 수 있는 그러한 인사이트를 도출할 수 있습니다 스프레드시트를 이용하시면 이러한 인사이트를 나만 보는 것이 아니라 스프레드시트가 협업을 전제로 하기 때문에 누구나 여러분 이렇게 만들어 두시면 이것을 보고 본인의 의견을 남기고 또 접근할 수 있고 다양한 방법으로 공유할 수 있는 이점이 있습니다 이러한 것들에 대해서 이러한 다양한 내용에 대해서 우리가 계속 이어서 다음 영상에서 또 다뤄보도록 하겠습니다