엑셀 마케팅 분석 실습 : 고객 리뷰 & 구매 연관성 분석 예제
엑셀 마케팅 분석 목차 바로가기
영상 강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- 엑셀 마케팅 분석 예제 - 문자 발송 & 구매 연관성 분석예제파일
기프티쇼 비즈 '특별 혜택' 안내
오빠두엑셀 구독자는 ① 최고 등급 즉시 적용 및 ② 이마트 상품권을 제공합니다! ( ~ 24년 12월 31일까지)
이번 행사는 기간 한정 프로모션으로, 추천인 코드를 통한 어떠한 추가 이익이 없음을 알려드립니다.😉2️⃣ 혜택 안내 :
👉 가입 후, 6개월간 플래티넘 등급 적용
(등급 적용까지 1~2일 정도의 시간이 걸릴 수 있습니다.)
혜택 1 : 일반 상품 구매 시, 최대 15% 할인 혜택
혜택 2 : 상품권 구매 시, 최대 2% 할인 혜택
혜택 3 : 매월 무료 문자 5,000건 제공👉 이마트 상품권 (5,000원) 제공
(회원 가입 후, 30일 이내 발송됩니다.)
고객 문자 발송 내역 분류하기 : ChatGPT 활용
먼저 ChatGPT를 활용하여 고객의 문자 발송 내역을 각 구분별로 분류하는 방법을 알아보겠습니다.
- 데이터 준비 : 예제파일을 실행한 후, [발송 메시지] 시트로 이동합니다. 이번 예제에서는 사용자가 지정한 구분에 따라 각 고객의 메시지를 분류해보겠습니다.
예제파일을 실행한 후 발송 메시지 시트로 이동합니다. - ChatGPT를 효율적으로 사용하기 위해, 메시지 필드 좌측에 "ID" 열를 추가하겠습니다. 이 열에는 1부터 증가하는 순번을 입력합니다.
메시지 열 좌측에 1씩 증가하는 순번으로 ID열을 추가합니다. - ChatGPT 요청 : 아래 링크를 클릭하여 ChatGPT 페이지로 이동한 후, 사용 모델을 3.5로 변경합니다. ChatGPT 3.5 모델은 무료로 사용할 수 있습니다.
ChatGPT 페이지로 이동한 후, 사용 모델을 3.5로 선택합니다. - 예제파일에서 [프롬프트시트] 로 이동한 후, 분류하고자 하는 '구분'과 '예시'를 적절히 입력하면 위쪽의 프롬프트가 완성됩니다. 프롬프트를 복사하여 ChatGPT 입력창에 붙여넣기합니다.
엑셀 프롬프트 템플릿에서 원하는 구분과 예시를 작성한 후, 완성된 프롬프트를 ChatGPT 입력창에 붙여넣기합니다. - 그 다음으로 [발송 메시지] 시트로 돌아와 ID와 메시지 열을 복사하여 프롬프트 아래에 이어서 붙여넣기합니다.
프롬프트 아래에 ID/메시지 열을 이어서 붙여넣기합니다. - 입력한 프롬프트를 실행하면, 각 메시지의 분류 결과가 ID와 함께 출력됩니다.
프롬프트를 실행하면 각 ID별 메시지 발송 구분이 작성된 표가 출력됩니다. - ChatGPT 결과를 엑셀에 옮기기 : ChatGPT로 반환된 표를 복사한 후, 엑셀에서 붙여넣기 할 셀을 클릭합니다. 이후 Ctrl + Alt + V 를 동시에 눌러서 '선택하여 붙여넣기'를 옵션을 실행한 후 '유니코드 텍스트'로 표를 붙여넣기 합니다.
표를 복사한 후, '선택하여 붙여넣기' - '유니코드 텍스트'로 시트에 붙여넣기합니다. 오빠두Tip : Ctrl + V 로 단순 붙여넣기하면 ChatGPT의 서식이 함께 복사되어 불편할 수 있으므로, 텍스트만 붙여넣기하는 것을 권장합니다. - 붙여넣기한 표의 순번이 원본과 동일하므로, 구분을 원본데이터로 옮겨서 문자 발송 내역 분류 데이터를 완성합니다.
분류를 원본데이터로 옮겨서 표를 완성합니다. - 차트로 시각화하기 : 완성된 발송 메시지 표를 선택한 후, [삽입] - [피벗테이블] - [기존 워크시트] 를 차례대로 선택하여 시트 우측에 피벗테이블을 추가합니다.
원본데이터를 선택한 후, 비어있는 곳에 피벗테이블을 추가합니다. - 아래 그림과 같이 행: 발신목적 / 열: 구분 / 값: 구분으로 필드를 각각 배치한 후, 값의 표시형식을 '행 합계 비율'로 변경합니다.
문자 발송내역을 분석하도록 피벗테이블 필드를 적절히 배치합니다. 오빠두Tip : 실무에 유용한 피벗테이블 설정 방법은 아래 5분 기초 영상 강의에서 자세히 정리했습니다!👇
- 피벗테이블을 선택한 상태에서, [삽입] - [추천 차트]에서 누적 가로막대형 차트를 추가하고 셀 서식과 차트를 적절히 꾸며주면 시각화 차트가 완성됩니다. 차트를 시각화하는 자세한 과정은 영상 강의 12:03 을 참고하세요!
차트를 추가하고 시각화하여 보고서를 완성합니다.
상품 구매 연관성 분석하기 : 파워쿼리 활용
파워쿼리를 사용하면 상품의 구매 연관성을 편리하게 분석할 수 있습니다. 이번 강의에서는 '상품권'을 구매한 고객의 '일반 상품'의 구매율을 계산해보겠습니다.
오빠두Tip : 만약 파워쿼리를 처음 사용하신다면, 오빠두엑셀에서 무료로 진행하는 "파워쿼리 원데이 챌린지"를 참여해보세요!👍 실무에서 꼭 필요한 데이터 가공 및 파일 취합 자동화를 단기간에 배울 수 있습니다.
- 상품유형별 데이터 집계 : 예제파일에서 [판매 데이터] 시트로 이동한 후, 표를 선택하고 [데이터] - [테이블/범위에서]를 클릭하여 파워쿼리 편집기를 실행합니다.
표를 선택한 후, [데이터] - [테이블/범위에서]를 클릭하여 파워쿼리 편집기를 실행합니다. - 이번 예제에서는 '상품 유형'을 기준으로 '판매 가격'의 연관성을 분석하겠습니다. [상품 유형] 필드를 선택한 후, [변환] 탭 - [피벗 열] 을 클릭하고, 값 열로 [판매가격]을 선택한 후 [확인]을 눌러 피벗 데이터로 변환합니다.
상품유형 필드를 기준으로 한 피벗 데이터로 변환합니다. - [변환] 탭 - [그룹화] 기능을 실행한 후, '고급' 옵션을 체크합니다. 그룹할 기준으로 [주문번호]를 선택하고 '상품권'과 '일반 상품' 필드의 합계를 새 열로 추가한 후 [확인]을 클릭합니다.
주문번를 기준으로 상품권과 일반상품 합계를 집계합니다. - 집계된 데이터에 "null" 이 있으면, 계산 오류가 발생할 수 있으므로 'null' 값을 숫자 0으로 변경합니다. 상품권 합계와 일반상품 합계 필드를 선택한 후, [변환] 탭 - [값 바꾸기]에서 찾을값 : null / 바꿀값 : 0 으로 입력한 후 [확인] 버튼을 클릭합니다.
집계 데이터에서 null 데이터를 숫자 0 으로 변경합니다. 오빠두Tip : 파워쿼리는 대소문자를 구분하므로 null은 소문자로 입력하는 것을 주의하세요! - 주문번호별 '일반 상품'의 구매율을 계산합니다. [열 추가] 탭 - [사용자 지정 열] 을 실행한 후, "일반 상품 구매율" 이라는 이름으로 "=[일반상품합계]/([상품권합계]+[일반상품합계])" 로 수식을 작성합니다. 수식을 모두 작성한 후, [확인] 버튼을 클릭하면 일반 상품 구매율이 계산됩니다.
일반 상품 구매율을 계산하는 사용자 지정 열을 추가합니다. - 쿼리를 시트에 출력하기 : 데이터 가공을 모두 완료한 후, [파일] 탭 - [닫기 및 다음으로 로드]를 선택하고 [연결만 만들기] 옵션으로 완성된 쿼리를 저장합니다.
파일 - 닫기 및 로드 - 연결만 만들기로 완성된 쿼리를 저장합니다. - [쿼리 및 연결] 패널에서 쿼리를 우클릭 - [다음으로 로드] 를 선택한 후, [표] 형식으로 기존 워크시트 비어있는 곳에 출력합니다.
완성된 쿼리를 시트 비어있는 곳에 표 형태로 출력합니다. - 항목별 일반상품 구매율 집계 : 원본데이터 우측에 '일반상품 구매율' 필드를 추가한 후, VLOOKUP 함수를 사용해 방금 추가한 쿼리 데이터에서 주문번호의 일반상품 구매율을 채워 넣습니다.
VLOOKUP 함수를 사용해 각 항목의 일반 상품 구매율을 채워 넣습니다. - 마지막으로 다음과 같이 피벗테이블을 추가한 후, 조건부서식을 적절히 추가하면 시각화 보고서가 완성됩니다. 피벗테이블 보고서를 작성하는 방법과 시각화하는 자세한 과정은 영상강의 29:14 를 참고하세요!
피벗테이블을 추가한 후, 셀 서식 및 조건부서식을 적절히 적용하여 보고서를 완성합니다.
- 데이터 준비 : 예제파일을 실행한 후, [발송 메시지] 시트로 이동합니다. 이번 예제에서는 사용자가 지정한 구분에 따라 각 고객의 메시지를 분류해보겠습니다.
