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

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

엑셀 매출 원인분석 목차 바로가기

영상강의

예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

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

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

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

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

LOOKUP 함수 사용시 주의사항

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

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

고객별 최근방문일을 계산하기 위해 이전 강의에서 알아본 VLOOKUP 마지막값 찾기 공식을 사용합니다. 공식에 대한 자세한 내용은 관련포스트를 참고하세요.

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

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

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

IF 함수와 AND 함수를 사용하여 아래 조건을 만족할 경우, 3개월 이내 재방문 여부로 표시합니다.

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

공식의 동작원리는 영상강의에서 자세히 설명해드렸습니다. [고객방문현황] 시트의 표 우측으로 ‘3개월 내 재방문여부’ 열을 추가한 뒤, 아래의 수식을 첫 셀에 입력합니다.

고객 방문현황 데이터가 완성되었습니다. 이전 과정을 통해, '고객ID'에서 고객별 '이름/성별/나이'를 불러오고, 각 고객의 마지막 방문일을 찾아 고객의 3개월 내 재방문여부를 계산 완료하였는데요.

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

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

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

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

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

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

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

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

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

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