매출 원인분석의 핵심지표 | 고객 방문율 방문주기 차트 만들기

매출 원인분석의 핵심 지표인 '고객 방문율'과 '방문주기' 분석을 위한 데이터 관리 및 차트 제작방법을 단계별로 살펴봅니다.

홈페이지 » 매출 원인분석의 핵심지표 | 고객 방문율 방문주기 차트 만들기

엑셀 매출분석의 시작 | 고객 방문율 방문주기 구하기 :: 기초 2-8강

예제/완성파일E-Book 교재
첨부파일에 이상이 생겼을 경우, 1:1 문의하기로 연락주시면 신속히 해결해드리겠습니다. 

관련강의 살펴보기

엑셀 피벗테이블 강의
엑셀 데이터 정규화 기초 1탄 :: 피벗테이블 효율 높이기
따라서 이번 강의에서는 '데이터 1차 정규화' 작업과, 엑셀에서 제공되는 가장 강력한 기능중 하나인 '피벗테이블'의 기초 사용법을 알아봅니다. 엑셀 정규화가 많이 ...
정규화 엑셀 lookup 함수
엑셀 데이터 정규화 응용 (2차/3차 정규화) :: 표기능 활용
이번 강의에서는 데이터 2차/3차 정규화에 대해 알아봅니다. 1차 정규화 강의를 못보고 오신 분은 이전 강의를 참고해주세요. 2차 정규화 이상의 작업은 ...

관련 기초내용 포스트 | by 오빠두엑셀

엑셀 LOOKUP 함수 예제
엑셀 LOOKUP 함수 사용법 및 실전예제 총정리 :: 참조 함수
실습파일 선택한 범위에서 값을 찾은 뒤, 다른 범위에서 같은 위치에 있는 값을 반환합니다. LOOKUP 함수는 해당 범위에서 값을 검색한 뒤, 같은 ...
IFERROR 함수 사용법 PNG
엑셀 IFERROR 함수 사용법 및 실전예제 총정리 :: 논리 함수
실습파일 입력한 수식에서 오류가 발생할 경우 사용자가 지정한 값을 반환합니다. IFERROR 함수는 입력한 수식에서 오류가 발생할 경우 오류대신 사용자가 지정한 ...

1. 데이터 범위를 표로 변경하기

쉬운 자료편집을 위해 입력된 데이터 범위를 표로 변경합니다. 각 [고객방문현황] 시트와 [고객정보] 시트에 입력된 데이터 범위를 표로 변경합니다.

데이터 범위를 선택한 뒤, 키보드 Ctrl + T 를 눌러 범위를 표로 변환합니다. 그 후 상단의 [표 도구] – [디자인] 탭의 [표 이름] 값을 각각 ‘tbl방문현황’, ‘tbl고객정보’로 변경합니다.

  • [고객방문현황] 시트 : tbl방문현황
  • [고객정보] 시트 : tbl고객정보
범위를 표로 변환
범위를 선택한 후, 단축키 Ctrl + T 를 눌러 범위를 표로 변경합니다.

2. 고객ID에서 고객정보 불러오기

[고객방문현황]시트의 자료에는 ‘고객ID’와 ‘방문일’만 나열되어 있습니다. 따라서 [고객정보]시트의 각 고객정보, 이름/성별/나이를 LOOKUP 함수를 사용하여 불러옵니다.

LOOKUP 함수는 VLOOKUP 함수와 같은 기능을 하며 표기능과 같이 사용할 경우 매우 편리하게 값을 참조할 수 있습니다. 다만 아래 2가지의 주의사항이 있으니 사용시 주의가 필요하며, 자세한 내용은 함수관련 포스트를 확인해주세요.

  1. 참조하는 범위는 반드시 ‘오름차순’으로 정렬되어야 합니다.
  2. 참조하는 범위에 찾는 값이 없을 경우, LOOKUP 함수는 찾는 값보다 작은 최대값을 반환합니다. (오류값을 반환하지 않습니다.) 따라서 오류처리가 안되므로 사용시 주의가 필요합니다.

방문현황 시트에서 [이름], [성별], [나이] 열을 추가한 뒤 아래 함수를 각 열의 첫번째 셀에 입력합니다.

  1. 이름 : =LOOKUP([@고객ID],tbl고객정보[고객ID],tbl고객정보[이름])
  2. 성별 : =LOOKUP([@고객ID],tbl고객정보[고객ID],tbl고객정보[성별])
  3. 나이 : =LOOKUP([@고객ID],tbl고객정보[고객ID],tbl고객정보[나이])
LOOKUP 함수 입력
각 열의 첫번째 셀에 LOOKUP 함수를 입력하면 표의 나머지 값은 자동으로 채워집니다.

3. 고객별 마지막 방문일 구하기

각 고객별로 ‘첫방문’인지 또는 첫방문이 아닐 경우 언제 마지막으로 방문하였는지 ‘최근방문일’을 계산합니다.

고객별 최근방문일을 계산하기 위해 이전 강의에서 다루었던 마지막 값을 찾는 방법 그리고 혼합참조를 이용한 동적확장범위를 이용합니다. 각 공식에 대한 자세한 내용은 관련포스트를 참고하세요.

[고객방문현황] 시트에서 ‘최근방문일’ 열을 추가한 뒤, 아래 공식을 첫번째 셀에 입력합니다.

=IFERROR(LOOKUP(2,1/($B$1:B1=B2),$A$1:A1),"첫방문")
고객 마지막 방문일 계산
고객의 최근방문일을 계산하기 위하여 수식을 입력합니다.

함수를 입력하면 각 고객별로 처음 방문하였는지, 또는 언제 마지막으로 방문하였는지 최근방문일이 계산됩니다. 이후 최근방문일을 ‘날짜’형식으로 표시하기 위해, ‘최근방문일’ 범위를 전체 선택한 뒤, 단축키 Ctrl + 1 키를 눌러 사용자 지정서식을 아래와 같이 변경합니다.

YYYY-MM-DD
사용자 지정서식 날짜 형태
사용자 지정서식을 날짜형태(예: 2019-05-24)로 변경합니다.

4. 고객의 3개월내 재방문 여부 구하기

각 고객별 최근방문일을 계산하였습니다. 따라서 이제는 각 고객별로 [현재 방문한 날짜] – [최근방문일]을 계산하면 고객의 방문주기를 계산할 수 있습니다.

IF 함수AND 함수를 응용하여

  • 고객이 첫 방문이 아니고
  • [현재 방문한 날짜] – [마지막 방문한 날짜] 가 90일 이하일 경우

위 조건을 만족할 시 “Y”를 표시하고, 그렇지 않을 경우 “N”을 표시하여 3개월 이내 재방문 여부를 구분합니다.

각 함수에 대한 자세한 내용은 관련 포스트를 참고해주세요. 마찬가지로 [고객방문현황] 시트에서 ‘3개월 내 재방문여부’ 열을 추가한 뒤, 아래의 수식을 첫 셀에 입력합니다.

=IF(AND([@최근방문일]<>"첫방문",IFERROR([@방문일]-[@최근방문일],0)<90),"Y","N")
3개월 내 재방문여부 계산
고객의 3개월 내 재방문여부를 계산합니다. 첫셀에 수식을 입력하면 표의 나머지범위는 자동으로 채워집니다.

5. 피벗테이블 추가 및 레이아웃 설정

고객 방문현황 데이터를 완성하였습니다. 고객ID와 방문일에서부터, 고객별 이름/성별/나이를 불러온 뒤, 각 고객의 마지막 방문일을 찾아서 출력하고 고객의 3개월 내 재방문여부를 계산하였습니다.

완성된 데이터 표를 선택한 뒤, 키보드 단축키 Ctrl – N – V 를 눌러 피벗테이블을 삽입합니다. 피벗테이블이 생성되었으면 아래와 같이 각 필드에 값을 끌어서 설정합니다.

  • 행 : [방문일], [3개월 내 재방문여부]
  • 열 : [나이]
  • 값 : [3개월 내 재방문여부](개수)
피벗테이블 필드 설정
피벗테이블의 필드를 위와 같이 설정합니다

피벗테이블의 행과 열에 각 필드를 추가하였으면, 좀 더 보기 쉽게 정리하기 위해 각 행과 열의 값을 ‘그룹화’ 합니다.  각 [방문일] 필드와 [날짜] 필드를 우클릭하여 [그룹]을 클릭한 뒤, 아래와 같이 각 필드를 ‘그룹화’ 합니다.

피벗테이블 필드 그룹화
방문일 및 날짜 필드를 위와 같이 ‘그룹화’ 합니다.

필드 그룹화를 완료하면 아래와 같이 피벗테이블이 완성됩니다.

피벗테이블 완성
피벗테이블의 각 필드를 그룹화하면 위와 같이 피벗테이블이 깔끔하게 완성됩니다.

이후 우측의 [피벗테이블 필드] 중 [값] 필드에서, ‘3개월 내 재방문여부’ 필드를 클릭한 뒤, ‘값 필드설정’ 으로 들어갑니다. 이후 [값 표시형식]에서 ‘상위 행 합계비율’을 선택한 뒤 확인을 눌러 적용합니다.

피벗테이블 상위행 합계 비율 선택
[값 필드 설정]의 값 표시형식에서 ‘상위 행 합계비율’을 선택합니다.
‘상위 행 합계비율’을 적용하면 아래와 같이 각 분기별로 각 나이대별 고객의 3개월 내 재방문여부가 % 비율로 구분되어 출력되는 것을 볼 수 있습니다.

피벗테이블 상위행 합계 비율 완성
상위행 합계비율을 선택하면 각 분기/나이대별 3개월 내 고객 재방문 비율이 계산됩니다.

6. 분기별 고객 방문율 차트 만들기

피벗테이블이 완성되었습니다. 여기서 한가지 문제점이 있는데요. 피벗테이블에서 ‘3개월 내 재방문여부’를 필터링 할 경우 출력되는 값이 모두 100%로 변경되는 문제가 생깁니다. 따라서 피벗테이블에서 값만 추출하여 “Y”값을 필터링 한 뒤 차트를 만듭니다.

피벗테이블에서 ‘연’ 과 ‘방문일’ 필드를 우클릭 한 뒤, [필드설정]을 클릭합니다. 이 후 나타난 팝업창에서 [레이아웃 및 인쇄] 탭에서 ‘항목 레이블 반복’ 을 체크하여 각 필드의 레이블 값이 반복되도록 설정합니다.

피벗테이블 항목 레이블 반복
‘연도’ 와 ‘방문일’ 필드의 레이블이 반복되서 출력되도록 필드설정을 변경합니다.

피벗테이블이 완성되었으면 피벗테이블에서 열과 행을 포함하여 값이 표시된 ‘일부분’만 복사하여 다른 곳에 붙여넣기 합니다. (*전체 피벗테이블을 복사하여 붙여넣기 할 경우, 값이 아닌 피벗테이블 전체가 붙여넣기 되므로 ‘반드시’ 피벗테이블의 일부분만 복사하여 붙여넣기 합니다.)

피벗테이블 값 붙여넣기
피벗테이블의 일부분만 선택하여 ‘값’ 형태로 붙여넣기 합니다.

이후 범위를 선택한 상태로 키보드 단축키 Ctrl + Shift + L 키를 눌러 자동필터를 적용하여 ‘3개월 내 재방문여부’ 에서 ‘Y’만 보이도록 필터링합니다. 이후 단축키 Alt – N – R 을 눌러 차트를 삽입합니다.

차트삽입
입력된 범위에서 ‘3개월 내 재방문 여부’를 ‘Y’만 보이도록 필터링 한 뒤 차트를 삽입합니다.

삽입된 차트를 우클릭 한 뒤, [차트종류변경] – [혼합] 으로 이동합니다. 이후 아래와 같이 차트 종류를 변경합니다.

  • 총합계 : [보조축] 꺾은선형 차트
  • 그 외 : [주축] 묶은 세로막대형 차트
차트 종류 변경
위와 같이 차트 종류를 변경합니다.

이후 기타 차트 설정을 변경하여 아래처럼 ‘3개월 내 고객 재 방문율’ 차트를 완성합니다. 차트를 만드는 방법에 대한 자세한 순서는 영상강의를 참고하세요.

고객 방문율 차트 완성
3개월 내 고객 재방문율 차트를 완성하였습니다.

강의가 도움 되셨으면 좋아요와 댓글 부탁드려요! 여러분의 소중한 댓글은 강의제작에 큰 힘이 됩니다.
엑셀 공부는 오빠두엑셀과 함께하세요! #엑셀은오빠두

5 14 votes
게시글평점
현재 페이지 댓글알림 신청
알림 설정
guest
19 Comments
Inline Feedbacks
View all comments
창창한하루
게시글평점 :
     

요식업 종사자입니다. 올해초부터 멤버쉽제도를 도입하면서 고객방문 패턴이 어떻게 변하는지 알아보고 싶었는데 큰 도움이 되었습니다. 좋은 내용 올려주셔서 감사합니다.!!

조용은
게시글평점 :
     

첫방문 주기에서 IFERROR(LOOKUP(2,1/($B$1:B1=[@고객ID]),$A$1:A1)이 어떻게 최근 방문일로 나오는지 알수 있을까요?
1/ <- 이 수식이 무슨 의미인가요?

새하얀아이
게시글평점 :
     

완성 파일은 없나요??

소나로사
게시글평점 :
     

잘 봤습니다.
피벗 테이블과 차트는 많이 사용을 해봐야 손에 익을듯 합니다

김선주
게시글평점 :
     

처음으로 문의 드립니다. 우선 좋은 강의 감사드립니다.
피봇테이블 만드실때 "방문일"을 행으로 끌어 내릴 때,
"분기", "연" 도 같이 생성되는 것을 확인했습니다.
그런데 고객방문현황 테이블에는 방문일 만 있는데요,
어떻게 하신 건지요? 궁금합니다. 알려주시면 감사하겠습니다.

daniel_p
게시글평점 :
     

제가 하고있는 업무에 활용도가 높겠습니다.
감사합니다~

크리슈
게시글평점 :
     

좋은 정보 감사합니다!

노은
게시글평점 :
     

고객관리에 유용할것 같네요

angami919
게시글평점 :
     

개꿀입니다아아아아!!!!!!!

오빠두짱
게시글평점 :
     

완성파일 다운받을 수 있을까요?

닥코드
게시글평점 :
     

항상 좋은 내용 감사합니다.

엑린이
게시글평점 :
     

좋은 강의 감사합니다

날으는공

알찬 강의 감사합니다.

JH Anthony Lee
게시글평점 :
     

나중에 자영업을 하게 되면은 굉장히 도움이 될것 같습니다. 감사합니다!

올챙이
게시글평점 :
     

엑셀 실무 기초 7강... 수강을 했으나, 이해력 부족으로 다시 봐야할 듯...
동적확장범위, 피벗테일블에서 따라 했는데, 이해하려니 잘 안되네요.
하지만, 나중에 생각이 나면서 응용할 수는 있겠죠... 감사합니다.

19
0
여러분의 생각을 댓글로 남겨주세요.x