매출 ABC 분석

이번 영상에서는 매출액 ABC 분석을 통해서 고객을 등급화하는 방법을 함께 살펴보도록 하겠습니다. 여러분께서 가지고 계시는 실습 자료 중에 04번 매출액 ABC 분석 파일을 이용해서 함께 하실 수 있습니다. 우리는 앞서서 홍동균 매니저가 매출을 안정적으로 내고 뭐 이러기 위해서 고객 유효 확률이 고객이 현재 유효할 확률이 얼마인가 라고 하는 것을 계산하고 이걸 토대로 실질적으로 유효하게 살아있는 활성화된 고객들을 집중적으로 관리한다 그래서 확률이 낮은 고객들에게는 굳이 너무 힘을 쓰지 않는다 라고 하는 것을 살펴봤습니다 그런데 유효 확률이라고 하는 기준을 꽤 높여도 이 대상이 되는 고객이 몇 천명 수준이죠 그럼 과연 한 명의 판매 사원이 과연 그럼 몇천명이 되는 한 2천명이라도 굉장히 많은 숫자거든요 그 많은 고객들을 다 잘 관리할 수 있는가 라고 하면 사실은 이게 현실적으로 좀 무리가 있습니다 그래서 본부장님이 물어봤습니다 그래도 몇천명인데 그걸 어떻게 관리합니까 라고 하자 홍동균 사원이 이야기했습니다 물론 그 사람들이 모두 나의 관리 대상인 건 맞지만 다만 고객을 등급화해서 관리를 하는 것이 중요합니다 라고 이야기를 했습니다 물론 고객을 등급화하는 데에 어떠한 지표를 중요하게 볼 것인가 매출액을 중요하게 볼 것인가 이익을 중요하게 볼 것인가 어떤 지표를 중요하게 볼 것인가 라고 하는 것은 상황마다 조금은 다를 수 있습니다 우리는 편의상 홍동균 매니저의 기준 그대로 매출액을 기준으로 해서 고객을 ABC라고 하는 3개의 등급으로 나누려고 합니다 그래서 전체 매출, 마소전자마트의 전체 데이터 고객들 중에서 전체 매출의 한 40%를 차지하는 고객을 A등급이라고 하고 한 40에서 80%까지 차지하는 이 고객들을 B등급 나머지 매출 20%를 차지하는 다수의 고객들은 C등급으로 등급을 매기라고 합니다 우리가 이러한 방법을 통상적으로 ABC 분석이라고 부르고요 다른 말로 비슷하게 소수 20%의 소수가 전체 매출의 80%를 차지한다 라는 뜻에서 파레토 분석이라고도 부릅니다 사실상 같다 라고 볼 수 있습니다 파일을 이용해서 ABC 분석을 빠르게 실행해보도록 하겠습니다. 주어진 실습파일을 열어보시면 2개의 워크시트가 있습니다. 마소전자마트의 전체 데이터를 담고 있는 데이터 워크시트가 있구요. 그리고 01번 매출의 ABC 분류 라고 하는 워크시트가 있습니다 이 워크시트를 우리가 주로 사용하게 될 텐데요 이 시트도 크게 몇 개의 부분으로 나눠져 있습니다 제일 왼쪽에는 우리가 실제 ABC 차트, 파레토 차트를 만들기 위해서 피벗 테이블을 만들 영역이 있습니다 이 피벗 테이블은 데이터를 로데이터로 해서 만들어지는 피벗 테이블입니다 그리고 이 ABC 등급을 우리가 나누는데 예를 들어 전체 매출의 40%를 차지하는 사람은 도대체 몇 명이나 되고 여기서 누적해서 80%가 되는 40에서 80%까지 이 40%를 차지하는 사람은 도대체 몇 명이나 되는지 나머지 C등급에 해당하는 사람은 몇 명이나 되는가 라고 하는 것을 정리하는 등급별 고객 수를 계산하는 표가 있습니다 우리가 요약해야 되는 고객은 총 12,897명이네요 총 12,897명의 고객들을 ABC 세 개 등급으로 매출액을 기준으로 나누고자 합니다 이러한 ABC 등급에 대해서 우리가 피벗 테이블을 만들면 연결되어 있는 피벗 차트에 ABC 차트가 그려질 겁니다 연결되어 있습니다 우리가 이런 ABC 데이터를 토대로 해서 그렇다면 특별히 우수한 고객들은 어떤 기준으로 어느 정도 금액대를 기준으로 우리가 VIP 고객을 선정할 수 있는가 라고 하는 VIP 고객의 선정 기준을 낼 수 있는 부분도 여기에 같이 있습니다 자 그러면 순서대로 한번 만들어 보겠습니다 일단 우리가 ABC 분석의 가장 기본이 되는 피벗 테이블 요약을 좀 해보도록 할 거에요 매출액을 기준으로 해서 각각의 고객들이 그럼 우리가 필요로 하는 첫 번째 데이터 필드는 고객의 아이디겠습니다 우리는 각각의 고객들이 매출액에 대해서 매출액이라고 하는 데이터가 필요하죠 매출액에 대해서 전체 매출의 몇 퍼센트를 차지하고 있는가 라고 하는 것을 알고 싶습니다 열 합계 비율이 되겠네요 이것을 토대로 해서 누적해서 ABC 등급을 만들려고 합니다 그러면 필요한 피벗 테이블을 만들겠습니다 고객 아이디와 매출액 2개만 있으면 되죠 피벗 테이블 필드 목록이 안 나타나실 경우에는 피벗 테이블 분석의 오른쪽 끝에 있는 필드 목록을 눌러주시면 이렇게 피벗 테이블 필드 목록이 나타나게 됩니다 그럼 실제로 가져가 볼까요 고객의 아이디, 거래 고객 아이디를 행으로 가져가면 되겠습니다 그러면 전체 12,897명의 고객의 아이디가 나타나는 걸 볼 수 있습니다 그럼 이 고객들의 개별적인 매출액 정보를 좀 볼까요? 매출액은 값으로 2개 가져가 보겠습니다 두 개 가져가시면 지금 보시면 매출액의 합계가 나타나고 있습니다 근데 우리가 필요로 하는 것은 개별적인 고객이 전체 매출에서 차지하는 비중이죠 그렇기 때문에 우리가 이 첫 번째에 있는 합계 매출액이라고 하는 이 열을 마우스 오른 클릭하셔서 전체에서 차지하는 비중 즉 이 열에서 이 값이 차지하는 비중 중간쯤에는 값 표시 형식에서 열 합계 비율을 선택해 주도록 하겠습니다 값 표시 형식의 위에서 세 번째에는 열 합계 비율을 선택하시면 이러한 개별적인 고객이 각자의 매출액이라고 하는 것이 전체에서 전체 열에서 차지하는 비중이 얼마인가 라고 하는 구체적인 값을 볼 수 있습니다 0이나 0.00 또는 0.01 정도로 표시되긴 하는데요 이건 소수점에 축약해서 표현해서 그런 거고 예를 들어서 지금 0.01로 보이고 있는 데이터도 수식 입력줄 좀 보시면 0.005 하면서 굉장히 긴 어떤 작은 비중을 차지한다라는 것을 볼 수 있습니다 고객의 수가 많다 보니 아무래도 이 차지하는 비중을 나타내는 것도 좀 값이 굉장히 퍼센트가 작죠 그러면 우리가 이것을 우리 입장에서는 당연히 전체 매출에서 차지하는 비중이 높은 고객 많은 비중을 차지하는 고객이 더 중요한 고객입니다 그러므로 우리는 이 열을 전체 내림차순 정렬로 차지하는 비중이 높으면 높을수록 1등이 되는 것이기 때문에 내림차순 순위로 정렬해 주도록 하겠습니다 마우스 오른 클릭하시고 다섯 번째 정렬에서 숫자의 내림차순 정렬을 선택하겠습니다 데이터가 좀 많고 계산량이 있다 보니까 조금 계산이 살짝 오래 걸리죠 응답 없음 여러분도 뜨실 수 있을 겁니다 계산한 결과를 보면 KIA-00111G 이 고객이 가장 많은 비중을 차지하고 있네요 이 사람이 0.05% 정도의 매출을 차지하고 있습니다 개별 고객들이 각자 전체 매출에서 몇 퍼센트를 차지하고 있는가 라고 하는 정보로 우리가 이렇게 정렬을 했습니다 그렇다면 우리가 이것을 가지고 이제 한 명 한 명이 차지하는 비중은 알았으니까 전체 매출의 한 40%까지 차지하는 고객은 몇 명이나 되는가라고 하는 매출의 누적 비율까지 좀 알아야겠죠 그래서 우리가 두 번째에 있는 합계 매출액 2라고 하는 열을 마찬가지로 마우스 오른 클릭하시고 값 표시 형식에서 거의 아래쪽에 있는 누계 비율로 요약을 해 주도록 하겠습니다 누계 비율로 요약을 하시면 기준은 거래 고객 아이디 확인하시면 이렇게 누계 비율이 계산되는 것을 보실 수 있습니다. 예를 들어서 두 번째 고객인 XMV00400U라고 하는 이 고객은 바로 위에 있는 1등 고객이 차지하는 비중이 0.05% 정도가 됩니다. 그리고 이 두 번째 고객도 0.05% 정도가 되죠. 그래서 이 둘을 합치면 0.11% 물론 더하면 0.1이 왜 아니지라고 생각하실지 모르지만 이것은 이 소수점 아래 부분에 대해서 더한 결과이므로 이 부분은 감안해서 봐주시길 바라구요 지금 보시는 것처럼 위쪽에 있는 이 매출액 비중 그 누적된 비율에다가 다음 이번 그 레코드의 비율을 더해서 누계 비율이 계속 계산되서 내려가는 것을 여러분께서 종전의 파레토 차트라던가 이런 것들을 할 때는 데이터가 몇 개 없다 이 레코드가 몇 개 없을 경우에는 전체의 80% 매출을 차지하는 그런 고객은 몇 명이 될까요 라고 하면 마우스를 쭉 내려서 80% 있는 부분까지 스크롤을 내려서 눈으로 확인해도 사실은 괜찮습니다 통상적으로 행번호 보시고 위쪽에 비어있는 개수를 따져갖고 한 몇 번째 고객까지가 되는구나 라고 이렇게 고객의 수를 계산하시기도 하는데요 지금 오른쪽 표에 이렇게 입력해 둔 것 같이 이러한 표에 입력해 둔 것 같이 여러분께서 MATCH 함수를 사용하시면 굉장히 간단한 방법으로 이러한 누계 비율 즉 MATCH 함수 이용해서 현재 D열에 있는 누계비율이 우리가 얻고자 하는 이 누계비율 값보다 아래까지 가는 이러한 고객이 몇 명인가 라고 하는 수치를 엑셀 함수로 굉장히 쉽게 빠르게 계산해 낼 수 있다 라는 것입니다 스크롤을 굳이 내려보지 않아도 이런 것들은 쉽게 계산해 낼 수 있죠 매출 비중이 40% A등급이 되는 고객이 차지하는 매출 비중을 40% 그리고 B등급 고객이 차지하는 매출 비중을 40%로 보고자 합니다 C등급 고객이 차지하는 비중이 20%였으면 좋겠다 라고 생각을 합니다 그래서 이러한 비중을 가지고 이것을 가지고 각각 차지하는 고객이 몇 명인가 라고 했더니 지금 굉장히 의외로 전체 40%의 매출을 차지하는 고객 A등급은 약 2,293명의 고객이 전체 매출의 40%를 차지하고 B등급에 해당하는 고객은 4,539명인데 이 사람들이 전체 매출에서 차지하는 비중은 한 40%가 됩니다 그런데 반면 개별적인 매출의 비중이 낮은 C등급 같은 경우에는 누적해서 C등급이 전체 매출에서 차지하는 비중은 20%밖에 안 되는데 무려 6,065명이나 되죠. 판매사원이 관리할 수 있는 고객의 수라고 하는 것은 사실상 한정되어 있기 때문에 우리가 이럴 경우에 당연히 적은 인원이 더 많은 매출을 차지하는 이런 사람들에 조금 더 집중해서 그러한 고객을 관리하는 것이 당연하다라고 말씀드릴 수 있습니다. 그러면 우리가 이러한 ABC 분류에 대해서 이 ABC 분류의 정도를 그림으로 요약해서 차트로 보도록 하겠습니다. 마우스 오른 클릭하시면은 이 피벗 테이블이 만들어지면서 우리가 기대한 것과는 다른 차트가 나타나 보실 수 있는데요. 차트 종류를 좀 변경해 보겠습니다. 차트를 선택하시고 위쪽에 있는 이 디자인, 디자인 탭을 클릭을 하시면 오른쪽 끝부분에 차트 종류 변경 메뉴가 있는 걸 확인하실 수 있습니다 차트 종류 변경을 한번 클릭해 보겠습니다 현재 우리 차트는 가장 기본이 되는 차트가 딱 만들어질 때 기본적으로 만들어지게 되는 묶은 세로 막대형으로 선정이 되어 있는데요 우리는 이러한 ABC 분석의 결과 ABC 차트 같은 경우에는 우리가 통상적으로 제일 끝에 있는 혼합 차트 여러분이 사용하시는 엑셀 버전에 따라서는 여기에 이름이 혼합 차트 되어 있는 것도 있고 콤보 차트, 콤보 이러한 표기로 되어 있을 수도 있습니다 혼합 차트 아이콘 위주로 보시면 좀 더 좋을 것 같습니다 이런 혼합 차트를 선택해 볼 수 있습니다 혼합 차트에서 두 번째에 있는 이중축을 보겠습니다 이렇게 보시면 이제 우리가 원하는 ABC 차트가 만들어지는 걸 보실 수 있죠 확인해 보겠습니다 차트 종류가 이렇게 변경이 됐습니다 대략적으로 볼 때 조금 이렇게 대략적으로 볼 때 지금 보시면 이 차트는 크게 2개의 축으로 되어 있습니다 왼쪽에 있는 이 축은 개별적인 이 한 명 한 명의 고객 X축에 있는 게 고객들의 거래 고객 아이디고요 한 명 한 명의 고객들이 차지하는 이 열 합계 비율 매출의 열 합계 비율 개별 고객이 전체에서 차지하는 이 매출 비율이 얼마인가 라고 하는 것을 보여주고 있습니다 그리고 오른쪽에는 누계비율을 보여주고 있습니다 그래서 그것을 모두 더하면 누적은 얼마인가라고 하는 것이죠 우리는 전체에서 누계비율이 40%를 차지하는 누계비율 40%를 차지하는 이러한 사람들을 구했습니다 그 사람들은 모두 2,293명이죠 2,293명의 고객들 여기에는 2,293개의 굉장히 많은 막대그래프가 현재 들어있는 겁니다. B등급에 속하는 사람들은 우리가 숫자로 봤을 때 4,539명이 많다라고 생각했는데 눈으로 봐도 확연하게 드러나죠. 2,293명과 4,539명은 그 수치가 굉장히 다르다는 겁니다. 어찌 됐든 우리는 40%부터 누적 80%까지 이 부분을 차지하는 사람들을 B등급으로 결정하려고 합니다 C등급 같은 경우에는 차지하는 누적 비율이 굉장히 이 사람들이 매출에서 차지하는 비중이 정말 작은데도 불구하고 인원이 정말 많죠 무려 6065명이나 되는 이런 인원이 있습니다 우리가 현실적으로 모든 고객을 관리할 수 없다라고 한다면 당연히 C등급의 고객은 좀 다소 적은 노력으로 유지하겠다라는 수준으로 가게 되고 B등급 고객보다는 A등급의 고객에게 조금 더 집중하게 됩니다 물론 이때 우리가 누계 비율을 어느 정도를 ABC 등급의 기준선으로 할 것이냐 40, 40 이렇게 40, 40, 20을 해야 되느냐 라고 하는 절대적인 기준선 같은 건 없습니다 여러분께서 하시는 비즈니스 데이터 분석의 도메인 어떤 비즈니스 데이터에 대해서 어떠한 목적으로 지금 등급을 나누고 있느냐 라고 하는 것에 집중하셔서 적절한 퍼센트를 정해주시면 되겠습니다 그런데 B등급이나 C등급 같은 경우는 지금 보시면 B등급에서 가장 매출에 차지하는 비중이 높은 사람이나 C등급에서 가장 매출에 차지하는 비중이 높은 사람이나 제일 낮은 사람이나 이 격차가 사실 그렇게 크지 않습니다. 매출에서 차지하는 비중의 격차가 크지 않죠. 마찬가지로 B등급도 제일 높은 사람이나 제일 낮은 사람이나 이 격차가 그리 크지 않습니다. 하지만 A등급은 다르죠. A등급을 보시면 A등급에서 가장 매출에 차지하는 비중이 높은 사람은 여기 거의 0.5%를 넘어가는 이 수준까지 올라갑니다. 그런데 같은 A등급이라도 A등급 안에서 제일 낮은 사람이 차지하는 비중은 이렇게 차이가 나는 거죠. 이런 걸 보면 우리가 A등급을 중점적으로 관리하는데 그 중에서도 정말 특출나게 A등급은 워낙에 회원들 간의 격차가 크기 때문에 정말 특출나게 매출이 높은 사람들은 정말 스페셜한 등급 VIP, VVIP, 골드, 플래티넘 이런 어떤 특별한 등급을 줘서 매출 비중이 0.03%를 넘어가는 이 사람들은 VIP로 하겠다 이렇게 결정할 수도 있습니다 하지만 등급을 만든다는 것은 통상적으로 우리가 매출을 높이기 위해서 하는 거니까 현재 우리가 알고 있는 사람들 이미 알고 있는 확보된 샘플 데이터를 가지고 계산할 것이 아니라 어떠한 기준선을 긋고 예를 들어 고객님의 매출액이 얼마를 넘어가면 VVIP가 되고요 그러면 굉장히 특별한 특전을 드립니다 라고 하는 이러한 것도 가능하죠 실제로 우리가 현실에서도 백화점이라던가 이런 곳에서 쇼핑을 하고 하다보면 어떤 기준선을 제시하고 이 금액을 넘어서면 이러한 혜택을 주고 백화점 라운지를 이용할 수 있고 주차를 쓸 수 있고 등등 굉장히 다양한 혜택을 주는 것을 볼 수 있습니다 이런 기준을 정하는 게 필요하죠 우리는 이런 어떤 특별한 기준 뭔가 특별함의 기준을 정하는데 통상적으로 지금까지 굉장히 두루 사용한 방법을 여기서도 사용할 수 있다는 것을 알고 있습니다 어떠한 데이터가 있을 때 이 데이터들의 확률적 분포라고 하는 것은 가장 나타나기 쉬운 것은 평균이고 평균으로부터 표준편차 하나, 두 개, 세 개 떨어짐에 따라서 각각 68% 95% 그리고 99%의 확률적 분포를 보인다 라고 하는 것을 우리는 알고 있죠 그러면 이것을 가지고 우리가 굉장히 이런 고객 이 정도 금액을 이 정도 금액을 결제할 가능성이라고 하는 것은 정말 0.5%도 안 된다 라고 하는 어떤 기준 금액 또 이 정도 금액을 결제할 가능성이라고 하는 건 2.5%도 안 된다라고 하는 그러한 기준 금액을 정해보도록 하겠습니다 이것을 정하는 것은 굉장히 단순하죠 평균으로부터 표준편차 하나만큼 크다라고 하면 전체 데이터의 크다 작다에서 68% 바깥쪽에는 여전히 약 32%의 데이터가 존재하게 되죠 표준편차 2배만큼 크거나 같다 라고 하면 전체 데이터의 95% 이 경우에는 바깥쪽에 존재하는 것이 양쪽에 2.5%씩 총 5%가 존재하게 되죠 그러니까 우리가 상위 2.5% 기준선이라고 하는 건 평균에다가 2 곱하기 표준편차를 하면 되겠구나 라고 이해할 수 있습니다 표준편차 3개만큼 크거나 작은 이 범위 안에 데이터가 존재할 가능성은 99% 그러므로 이 바깥쪽에 있는 이것보다 더 높은 금액을 결제할 확률이라고 하는 것은 0.5% 이하가 되죠 그러므로 우리는 이 상위 0.5%의 VIP를 결정하는 기준선이라고 하는 것은 평균으로부터 표준편차의 3배를 곱한 이 값을 계산하면 된다라는 것을 알 수 있습니다 결국 이러한 기준을 계산하려면 평균과 표준편차 정보가 필요하겠죠 평균과 표준편차를 구해보도록 하겠습니다 전체 고객의 평균 매출을 우리가 구하고 싶습니다 평균 매출을 구하고 싶은데요 현재 우리가 가지고 있는 이 피벗 테이블을 보면 이 피벗 테이블은 현재 매출액의 정보를 매출 정보를 지금 뭐죠? 이것을 열합계 비율 그리고 누계 비율로 가지고 있고 사실상 순수한 매출값으로는 가지고 있지 않습니다 그러니까 우리가 계산의 편의를 위해서 여기에 실제 매출액 값도 추가해 주도록 하겠습니다 피벗 테이블의 값에 매출액을 하나 더 더해서 실제 우리가 구체적인 매출액 값을 볼 수 있는 이 열을 추가하도록 하겠습니다 자 그럼 이제 우리가 이 매출액의 이 열을 가지고 평균을 내고 표준편차를 계산하면 되겠죠 그러면 평균과 표준편차를 계산해 보겠습니다 전체 고객의 평균적인 매출 이 고객의 매출 평균은 얼마인가 라고 하면 평균 함수를 사용하시면 되겠죠 AVERAGE 평균 함수를 사용해서 여기서부터 이 4번 셀부터 Ctrl Shift 아래로 화살표 하셔서 전체 영역을 잡은 다음에 괄호 닫고 엔터 치시면 이 4번 셀부터 이 12900번 셀까지 이렇게 전체 고객들에 대한 평균 매출 금액을 정할 수 있습니다 각각의 고객들은 개인별로 결제한 금액은 다 다르지만 평균을 내면 평균적으로는 약 365만원 정도를 결제했다고 우리가 알 수 있죠 표준편차도 구해보도록 하겠습니다 표준편차는 STDEV.P 라고 하는 함수를 이용해서 사용하실 수 있습니다 STDEV.P 라고 하는 거 바로 나타나실 수 있죠 STDEV.P 마찬가지로 셀부터 아래로 하셔서 셀까지 범위를 잡아서 괄호를 잡고 엔터를 치시면 이렇게 표준편차를 구할 수 있습니다. 지금 우리는 마소 전자마트에 고객 12,897명에 대해서 이 고객들의 매출액 평균은 365만원이고 이들 사이에 이 매출액의 표준편차라고 하는 것은 약 274만원이다 라고 하는 것을 계산했습니다 자 이제 그럼 상위 0.5% 기준선이 얼마인지 확인해 볼까요 이것은 평균 즉 I14번 셀에 있는 평균 더하기 표준편차의 3배만큼 더 큰 이것이 바로 기준선이죠 3 곱하기 표준편차 I15번 셀에 있는 값 하시면 상위 0.5% 이 정도 이 금액을 초과해서 결제할 가능성 그 가능성이라고 하는 건 정말 0.5%가 될까 말까 해요 굉장히 특이하게 정말 높은 거죠 그래서 우리에겐 매우 매우 특별한 정말 VIP VVIP 고객입니다 라고 하는 선은 지금 보시는 것처럼 약 1187만원 1187만원을 결제하면 굉장히 높은 수준에 들어간다 라고 우리가 판단할 수 있습니다 전체 고객들 중에서 1187만원 이상을 구매한 고객은 우리의 VIP 고객이 되는 것이고 앞으로 다른 고객들도 이 정도 금액을 넘어서면 VIP 등급에 넣어 주면 되는 거죠 그러면 상위 2.5% 선도 계산해 보겠습니다 이 사람들도 굉장히 의미 있는 뛰어난 고객들이죠. 우리에게 굉장히 소중한 고객들입니다. 평균 더하기 2 곱하기 표준편차라고 하면 이 정도 금액을 결제할 가능성이라고 하는 것은 2.5%도 안 된다 라고 하는 선은 약 913만원이다 라는 것을 우리가 판단할 수 있습니다. 이와 같이 우리가 어떠한 고객의 정보가 있다 라고 할 때 이 고객의 정보 등급을 나누는데 사람을 등급을 나누든 또는 제품을 나누든 여러가지 케이스에 대해서 등급을 하겠다 데이터를 가지고 등급을 나누겠다 라고 할 때 가장 기초적으로 사용하는 방법은 지금 보시는 것처럼 ABC 등급 분류 그리고 지금 보셨다시피 어떤 확률 분포를 기준으로 한 어떤 기준선의 측정 이것이 바로 우리가 어떤 등급을 분류할 때 가장 많이 비즈니스 애널리틱스에서 보편적으로 사용되는 방법이다 라고 말씀드릴 수 있겠습니다