고객 방문율 분석기법

실습 가이드

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

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

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

LOOKUP 함수는 VLOOKUP 함수와 동일한 기능을 수행하며, 표 기능과 함께 사용할 때 매우 편리하게 값을 조회할 수 있습니다. 다만 아래 두 가지 주의사항이 있으므로 사용 시 유의해야 하며, 자세한 내용은 함수 관련 포스트를 참고하세요.

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

다음으로 고객별 최근 방문일을 계산합니다. 고객마다 첫 방문 여부를 확인하고, 첫 방문이 아닐 경우 가장 마지막으로 방문한 날짜를 산출합니다.

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

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

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

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

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

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

이로써 고객 방문현황 데이터가 완성되었습니다. 앞선 과정에서 ‘고객ID’를 기준으로 ‘이름/성별/나이’ 정보를 불러오고, 각 고객의 마지막 방문일을 찾아 3개월 내 재방문 여부까지 계산하였습니다.

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

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

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

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

‘상위 행 합계비율’을 적용하면 아래와 같이 각 분기별로 나이대별 고객의 3개월 내 재방문 여부가 % 비율로 구분되어 출력됩니다.

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

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

설정이 완료되면 피벗테이블에서 열과 행을 포함하여 값이 표시된 ‘일부 영역’만 복사하여 다른 위치에 붙여넣기 합니다. (*전체 피벗테이블을 복사하면 값이 아닌 피벗테이블 자체가 붙여넣어지므로 ‘반드시’ 피벗테이블의 일부 영역만 복사하여 붙여넣기 해야 합니다.)

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

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

마지막으로 기타 차트 옵션을 조정하여 아래와 같이 ‘3개월 내 고객 재방문율’ 차트를 완성합니다. 차트 제작에 대한 자세한 순서는 영상 강의를 참고하세요.