엑셀 마케팅 분석 핵심 스킬 - 고객 리뷰 & 구매 연관성 실전 예제

실습 가이드

기프티쇼 비즈 '특별 혜택' 안내

오빠두엑셀 구독자에게 ① 최고 등급을 즉시 적용해 드리고 ② 이마트 상품권까지 제공합니다! ( ~ 24년 12월 31일까지)

이번 행사는 기간 한정 프로모션으로, 추천인 코드를 통한 별도의 추가 이익은 없음을 알려드립니다.😉

고객 문자 발송 내역 분류하기 : ChatGPT 활용

먼저 ChatGPT를 활용해 고객의 문자 발송 내역을 구분별로 분류하는 방법을 살펴보겠습니다.

1. 데이터 준비 : 예제파일을 실행한 후 [발송 메시지] 시트로 이동합니다. 이번 예제에서는 사용자가 지정한 구분에 따라 각 고객의 메시지를 분류해 보겠습니다.

   

2. ChatGPT를 효율적으로 사용하기 위해, 메시지 필드 좌측에 "ID" 열을 추가합니다. 이 열에는 1부터 증가하는 순번을 입력합니다.

   

3. ChatGPT 요청 : 아래 링크를 클릭해 ChatGPT 페이지로 이동한 후 사용 모델을 3.5로 변경합니다. ChatGPT 3.5 모델은 무료로 사용할 수 있습니다.
   https://chatgpt.com/

   

4. 예제파일에서 [프롬프트시트]로 이동한 후, 분류하려는 '구분'과 '예시'를 적절히 입력하면 위쪽의 프롬프트가 완성됩니다. 완성된 프롬프트를 복사해 ChatGPT 입력창에 붙여넣기합니다.

   

5. 이어서 [발송 메시지] 시트로 돌아와 ID와 메시지 열을 복사한 뒤 프롬프트 아래에 이어서 붙여넣기합니다.

   

6. 입력한 프롬프트를 실행하면 각 메시지의 분류 결과가 ID와 함께 출력됩니다.

   

7. ChatGPT 결과를 엑셀에 옮기기 : ChatGPT가 반환한 표를 복사한 뒤, 엑셀에서 붙여넣기 할 셀을 클릭합니다. 이후 Ctrl + Alt + V 를 동시에 눌러 '선택하여 붙여넣기' 옵션을 실행한 후 '유니코드 텍스트' 형식으로 표를 붙여넣기합니다.

   

   오빠두Tip : Ctrl + V 로 단순 붙여넣기하면 ChatGPT의 서식이 함께 복사되어 가독성이 떨어질 수 있으므로, 텍스트만 붙여넣기하는 것을 권장합니다.
8. 붙여넣기한 표의 순번이 원본과 동일하므로, 분류 결과의 '구분' 값을 원본 데이터로 옮겨 문자 발송 내역 분류표를 완성합니다.

   

9. 차트로 시각화하기 : 완성된 발송 메시지 표를 선택한 후 [삽입] - [피벗테이블] - [기존 워크시트]를 차례대로 선택해 시트 우측에 피벗테이블을 추가합니다.

   

10. 아래 그림과 같이 행: 발신목적 / 열: 구분 / 값: 구분으로 필드를 각각 배치한 후, 값의 표시형식을 '행 합계 비율'로 변경합니다.

    

    오빠두Tip : 실무에 유용한 피벗테이블 설정 방법은 아래 5분 기초 영상 강의에서 자세히 정리했습니다!👇
    
11. 피벗테이블을 선택한 상태에서 [삽입] - [추천 차트]를 열어 누적 가로막대형 차트를 추가하고, 셀 서식과 차트를 적절히 꾸며주면 시각화 차트가 완성됩니다. 차트 시각화의 자세한 과정은 영상 강의 12:03 을 참고하세요!

    

상품 구매 연관성 분석하기 : 파워쿼리 활용

파워쿼리를 사용하면 상품 간 구매 연관성을 손쉽게 분석할 수 있습니다. 이번 강의에서는 '상품권'을 구매한 고객의 '일반 상품' 구매율을 계산해 보겠습니다.

1. 상품유형별 데이터 집계 : 예제파일에서 [판매 데이터] 시트로 이동한 후, 표를 선택하고 [데이터] - [테이블/범위에서]를 클릭해 파워쿼리 편집기를 실행합니다.
   
2. 이번 예제에서는 '상품 유형'을 기준으로 '판매 가격'의 연관성을 분석합니다. [상품 유형] 필드를 선택한 후 [변환] 탭 - [피벗 열]을 클릭하고, 값 열로 [판매가격]을 선택한 뒤 [확인]을 눌러 피벗 데이터로 변환합니다.

   

3. [변환] 탭 - [그룹화] 기능을 실행한 후 '고급' 옵션을 체크합니다. 그룹 기준으로 [주문번호]를 선택하고 '상품권'과 '일반 상품' 필드의 합계를 새 열로 추가한 뒤 [확인]을 클릭합니다.

   

4. 집계된 데이터에 "null"이 남아 있으면 계산 오류가 발생할 수 있으므로 'null' 값을 숫자 0으로 변경합니다. 상품권 합계와 일반상품 합계 필드를 선택한 후, [변환] 탭 - [값 바꾸기]에서 찾을값 : null / 바꿀값 : 0 을 입력하고 [확인] 버튼을 클릭합니다.

   

   오빠두Tip : 파워쿼리는 대소문자를 구분하므로 null은 반드시 소문자로 입력해야 합니다!
5. 주문번호별 '일반 상품'의 구매율을 계산합니다. [열 추가] 탭 - [사용자 지정 열]을 실행한 후 "일반 상품 구매율"이라는 이름으로 "=[일반상품합계]/([상품권합계]+[일반상품합계])" 수식을 작성합니다. 수식 작성을 마치고 [확인] 버튼을 클릭하면 일반 상품 구매율이 계산됩니다.

   

6. 쿼리를 시트에 출력하기 : 데이터 가공을 모두 완료한 후 [파일] 탭 - [닫기 및 다음으로 로드]를 선택하고 [연결만 만들기] 옵션으로 완성된 쿼리를 저장합니다.

   

7. [쿼리 및 연결] 패널에서 쿼리를 우클릭한 뒤 [다음으로 로드]를 선택하고, [표] 형식으로 기존 워크시트의 비어 있는 셀에 출력합니다.

   

8. 항목별 일반상품 구매율 집계 : 원본 데이터 우측에 '일반상품 구매율' 필드를 추가한 후, VLOOKUP 함수로 방금 출력한 쿼리 데이터에서 주문번호별 일반상품 구매율을 채워 넣습니다.

   

9. 마지막으로 다음과 같이 피벗테이블을 추가하고 조건부서식을 적절히 적용하면 시각화 보고서가 완성됩니다. 피벗테이블 보고서 작성과 시각화의 자세한 과정은 영상 강의 29:14 를 참고하세요!