[Excel마스터] 온라인 쇼핑몰 데이터 분석 (문자열의 상관분석)

Hyejin Beck·2023년 12월 22일

Excel & Sheet

목록 보기

7/22

=Text(값,'코드') , =Text(날짜,'aaa')
'날짜'에 맞는 요일(월,화,수,목...) 출력
'2023-01-01' 이라면 '일' 출력 되게 합니다.
=Search('찾을값',참조셀) , =Search('수박',A1)
'수박'이 A1(수박배수박)에 있으면 배의 텍스트 순서인 5가 출력됩니다.
Isnumber(A1)
A1의 값이 숫자면 True, 아니면 False 반환됩니다.

날짜 추출 컬럼 : data컬럼 -> 날짜형태로 변경된 컬럼 생성
=left(data셀,10)
data셀 데이터에서 1번~10번째 값만 추출하여 2023-12-22 만 추출됨
요일 추출 컬럼 : 거기에서 요일만 컬럼 생성
=text(날짜셀,"aaa")
traficSource (접속 경로)컬럼을 비슷한 데이터끼리 병합하는 컬럼을 만들어보겠습니다.
mail.google.com
google.com
google.co.kr
google.co.uk
등을 그냥 google로 통합되게 해줄겁니다.
일단, 찾는값이 몇 번째 자리에 있는지 추출하는 함수를 만듭니다.
=search("찾는값", 셀 )
있다면 숫자위치값, 없다면 False가 나옵니다.
이제 isnumber 함수로 감쌉니다.
=isnumber(search("찾는값", 셀))
숫자값이라면 (찾는값이 있다면) True / 숫자값이 없다면 (찾는값이 없다면) False
그리곤 만약 True(찾는값이 있다면) google로 출력되게, False(찾는값이 없다면 그 값 그대로 출력)
=if(isnumber(search("찾는값", 셀)),"google",셀)

피벗테이블을 이용해 만들어줍니다.

행 (위의 traffic source 접속경로 추가 컬럼) , 값 (fullVisitorld의 개수)
총합계 해제
fullVisitorld의 개수 데이터 값 복사붙혀넣기 해서 히스토그램 시각화 ---> 시각화 결과 경로에 대해서는 데이터 인사이트가 별 의미가 없는 것 같습니다.
피벗차트를 이용해서도 추천차트를 통해 시각화 해봅니다.
아무래도 (direct)와 youtube등의 경로를 통해 많이 들어옵니다.

총합계 해제
행 (날짜 = 월) , 값 (갯수 : fullVisitorld) 에서 데이터 표시 형식을 다시 '열 합계 비율'
피벗테이블 추천차트
차트종류변경 (꺾은선 그래프)
-> 단, 두 그래프의 차이가 너무 커서, 상대적으로 적은 '장바구니 담은'1 데이터 수치가 작아 잘 보이지 않습니다.
차트종류변경 (꺾은선 그래프)
그리고 데이터 계열지정의 보조축 체크해서 각각의 수치를 다르게 주어, 각 그래프별 추이를 더 자세하게 볼 수 있습니다.
-> 장바구니 담은(1) 의 데이터 추이가 시간이 지날수록 우상향 하고 있는 추이를 볼 수 있습니다.

행 (total time on site) , 값(product pages viewed)의 합계 , 열 (add to cart)
만들어진 테이블 값 복사 붙혀넣기
분사형 차트만들기 전, 열 레이블의 0과 1을 이름바꿔줍니다. (장바구니x 장바구니o)
행 레이블과 열레이블(장바구니x 부분만) 으로 분사형 차트
-> 결과를 보니 상품페이지뷰 시간과 장바구니담지 않은 것의 상관관계는 (양이든 음이든) 없는 것 같습니다.
행 레이블과 열레이블(장바구니o 부분만) 으로도 분사형 차트를 만들어줍니다.
-> 결과를 보니 상품페이지뷰 시간과 장바구니담은 것은 약간의 양의 상관관계를 가지고 있는 것 같습니다.

문자열 컬럼인 deviceCategory 을 수치열로 변형하겠습니다.

다행히 문자열값이 Mobile , PC 두 개 밖에 없습니다.
두 개의 새로운 columns 삽입 합니다.
기준이되는 deviceCategory셀이 'mobile'이라면 1, 아니면 0 이 되는 서식을 만들어 줍니다.
=if($기준="mobile", 1, 0)
기준이되는 deviceCategory셀이 'PC'라면, 1, 아니면 0 이 되는 서식을 만들어 줍니다.
=if($기준="PC", 1, 0)