[파워쿼리]활용-Tip 판매주문의 품목 그룹핑 분석 데이터 구성(유사 장바구니 분석)

이번 포스팅은 파워쿼리를 이용하여 장바구니 분석 효과를 어느 정도 낼 수 있도록 데이터를 구성하는 방법을 소개합니다. 파워쿼리를 사용하는 것이니 당연히 한번 잘 익혀두면 계속 사용이 가능하겠죠~ 

 

▶문제 

오늘의 문제는 판매주문 내역으로  "어떤 고객이 어느 채널을 통해서 어떤 조합의 제품을 주문했는가?"   이것이 궁금할 때 어떻게 데이터를 구성하면 좋을까? 

입니다. 

그냥 판매주문 내역을 보면 알 수 있지 않는가?? 맞죠. 여기서 중요한 것은 "어떤 제품 조합으로" 이것입니다. 

판매주문내역은 보통 이런 식이죠. 여기서 고객 A는 라켓과 가방을 같이 주문할 때도 있고, 라켓과 셔틀콕을 같이 주문한 적도 있습니다. 고객과 채널별로 어떤 조합의 제품을 구매하고 있는지 알아보려 합니다.  

▶설계

고객과 채널 별 주문 현황이니 당연히 고객과 채널 단위로 주문 금액을 합산 해야 합니다. 일단 그냥 다 더해봅니다. 

 간단하게 피벗을 돌려서 고객(Customer),채널(Channel),제품분류(Goods category) 별로 주문수량을 합산해 보았습니다. 이걸로 알 수 있는 것은 각 필드 항목별로 합산이죠. 예를 들어 고객 'B'가 오프라인 구매 시 어떤 제품을 같이 구매했는지 알 수 없습니다. 오프라인으로 구매한 품목만을 확인할 수 있죠. 

자 이걸 확인하기 위해서 제품분류이 조합된 데이터가 필요합니다. 아래처럼요.  

Goods Category를 주문 번호(Sales order) 단위로 합쳐 놓았죠. 손으로 직접 타이핑했습니다. ^^;; 

이걸 파워쿼리로 해주면 됩니다. 작업의 순서는 

 1) 주문 번호와 제품 분류 단위로 그룹화
 2) 다시 주문번호 단위로 그룹화 ( 이때 제품분류를 텍스트 합치기, Text.Combine을 사용)
 3) 주문 번호 별 고객과 채널을 Table로 구성
 4) 2번과 3번을 조인하여 고객,채널,주문번호,제품분류 조합 별 매출액 Table 산출 

어렵지 않죠.. 단계를 더 줄일 수도 있지만 파워쿼리를 항상 재사용을 목적으로 작성하는 것이니 만큼 수정이 용이하도록 단계를 보기 좋게 구성하는 것이 포인트입니다. 

관련된 기능은 아래 지난 포스팅을 링크 합니다. 참고하세요. 

2020.07.21 - [엑셀/파워쿼리기능소개] - [파워쿼리기능소개] 6-1. 쿼리 관리 : 병합과 추가(Join , Union)

[파워쿼리기능소개] 6-1. 쿼리 관리 : 병합과 추가(Join , Union)

2020.11.20 - [엑셀/파워쿼리 공부] - [파워쿼리] 그룹화 기능을 활용하여 문자셀을 병합하여 표를 정리하자

[파워쿼리] 그룹화 기능을 활용하여 문자셀을 병합 하여 표를 정리하자

2020.07.23 - [엑셀/파워쿼리기능소개] - [파워쿼리기능소개] 6-2. 쿼리 관리 : 복제와 참조

[파워쿼리기능소개] 6-2. 쿼리 관리 : 복제와 참조

2020.07.07 - [엑셀/파워쿼리기능소개] - [필수 Tip]변환 작업 시 팁 - 적용된 단계 관리

[필수 Tip]변환 작업시 팁 - 적용된 단계 관리

 

▶작업수행 

작업순서대로 수행한 내역을 간단히 정리합니다. 

 1) 주문 번호와 제품 분류 단위로 그룹화

최초 원본입니다. ①쿼리 리스트를 보면 저는 쿼리 참조 기능을 사용해서 원본을 유지한 상태에서 2개의 참조 쿼리를 만들고 그것을 다시 조인해서 최종 결과물을 얻는 방식을 사용했습니다. 

이렇게 작업하면 나중에 이 파일을 열어볼때 어떤 목적으로 구성했는지 보다 명확하게 알아보는데 도움이 됩니다. 일종의 디버깅이 편한 구조라고 할 수 있겠네요. 

원본을 참조하여 생성한 쿼리 fSales_data(cat)에서 그룹화를 우선 한번 (주문번호와 제품분류 별 금액) 

 2) 다시 주문번호 단위로 그룹화 ( 이때 제품분류를 텍스트 합치기, Text.Combine을 사용)

그리고 다시 주문번호와 제품분류 조합 단위로 그룹화를 진행

 3) 주문 번호 별 고객과 채널을 Table로 구성

 4) 2번과 3번을 조인하여 고객,채널,주문번호,제품분류 조합 별 매출액 Table 산출 

Sales order 필드를 조인 조건으로 병합한 뒤 확장하여 데이터 완성!! 

¶결과물

쿼리를 피벗 테이블로 바로 로드하였습니다. 

이제 고객 'B' 가 어떤 제품분류 조합으로 오프라인에서 구매를 하고 있는지 알 수 있게 되었네요 요즘 제가 배드민턴을 배우기 시작해서 배드민턴 용품 샵을 가정하고 데이터를 만든 것이라 별 인사이트는 없지만 실제 데이터로 작업하다 보면 재미있는 결과가 될 것 같네요. 

지금은 판매 데이터를 가지고 했지만 제조 수율 데이터를 기준으로 어떤 조합일 경우 가장 좋은 수율과 수익율을 갖는지 분석하는 것도 가능할 것 같습니다. 언제나 데이터가 문제죠.. 아이디어는 있지만 데이터가 없다는.. 

혹시라도 이 비슷한 주제로 공유 할 수 있는 데이터가 있다면 받아서 같이 진행해 보는 것도 좋겠네요. ^^;;;; 

이상입니다. 작업 파일을 첨부합니다. 받아서 직접 응용해 보시면 좋겠네요. ~ 

파워쿼리_장바구니 분석_주문품목조합 그룹핑.xlsx

0.06MB