 |
데이터 분석을 위한 SQL 레시피 -  가사키 나가토.다미야 나오토 지음, 윤인성 옮김/한빛미디어 |
5장 사용자를 파악하기 위한 데이터 추출
11강 사용자 전체의 특징과 경향 찾기
1. 사용자의 액션 수 집계하기: COUNT(DISTINCT)
2. 연령별 구분 집계하기: CASE, COUNT
3. 연령별 구분의 특징 추출하기
4. 사용자의 방문 빈도 집계하기: SUM() OVER()
5. 벤 다이어그램으로 사용자 액션 집계하기
6. Decile 분석을 사용해 사용자를 10단계 그룹으로 나누기
Decile 분석 과정
- 사용자를 구매 금액이 많은 순으로 정렬
- 정렬된 사용자 상위부터 10%씩 Decile 1부터 Decile 10까지 그룹 할당
- 각 그룹의 구매 금액 합계 집계
- 전체 구매 금액에 대해 각 Decile의 구매 금액 비율(구성비)를 계산
- 상위에서 누적으로 어느 정도의 비율을 차지하는지 구성비 누계 집계
Decile 분석 과정
- 사용자를 구매 금액이 많은 순으로 정렬
- 정렬된 사용자 상위부터 10%씩 Decile 1부터 Decile 10까지 그룹 할당
- 각 그룹의 구매 금액 합계 집계
- 전체 구매 금액에 대해 각 Decile의 구매 금액 비율(구성비)를 계산
- 상위에서 누적으로 어느 정도의 비율을 차지하는지 구성비 누계 집계
7. RFM 분석으로 사용자를 3가지 관점의 그룹으로 나누기
12강 시계열에 따른 사용자 전체의 상태 변화 찾기
1. 등록 수의 추이와 경향 보기: COUNT(), LAG()
2. 지속률과 정착률 산출하기
- 지속률: 사용자가 매일 사용하는 서비스(뉴스, 게임, SNS)
- 정착률: 사용자가 어떤 목적이 생겼을 때 사용하는 서비스(E-C, 리뷰, ...)
지속률: '지정한 날짜에 등록한 사용자 중에서 다음날에도 서비스를 사용한 사람의 비율'
- 단순하게 사용자 수를 센 뒤 나누어 비율을 집계해도 괜찮지만,
지정한 날짜 다음에 사용한 사용자에 1, 사용하지 않은 사용자에 0이라는 플래그를 붙이고, 여기에 AVG 함수를 적용해 평균을 구하는 방법이 훨씬 간단
- 다음날의 지속률을 집계하려면 다음날의 로그 데이터가 모두 쌓여있어야 함: 기간 중 최신 날짜를 추출하고, 이후 기간은 NULL 처리
- '집계 기준일'(first_date), ''다음날'의 날짜'(next_day), '로그의 최근 날짜'(latest_date)를 집계
→ 사용자별 MIN(session_date), DATE_ADD(first_date, interval N day), MAX(session_date)
→ copy_t 테이블로 N일자 집계 가능
- next_day가 latest_date보다 작을 때, next_day에 세션이 있다면 1, 없다면 0
→ CASE WHEN next_day = visit_date THEN 1 ELSE 0 END
- 사용자별로 평균*100 해서 백분율로 나타냄
→ 그런데 이렇게 할 경우! 첫 날은 무조건 사용자들이 '처음 오는 날'이 되므로, 지속률이 높게 나타날 수밖에 없음. 넉넉하게 기간을 잡고 해야 오류가 안 생김~~
3. 지속과 정착에 영향을 주는 액션 집계하기
4. 액션 수에 따른 정착률 집계하기
5. 사용 일수에 따른 정착률 집계하기
6. 사용자의 잔존율 집계하기
7. 방문 빈도를 기반으로 사용자 속성을 정의하고 집계하기
8. 방문 종류를 기반으로 성장 지수 집계하기
9. 지표 개선 방법 익히기
13강 시계열에 따른 사용자의 개별적인 행동 분석하기
1. 사용자의 액션 간격 집계하기
2. 카트 추가 후에 구매했는지 파악하기
3. 등록으로부터의 매출을 날짜별로 집계하기
6장 웹사이트에서의 행동을 파악하는 데이터 추출
14강 사이트 전체의 특징/경향 찾기
1. 날짜별 방문자 수 / 방문 횟수 / 페이지 뷰 집계하기
2. 페이지별 쿠키 / 방문 횟수 / 페이지 뷰 집계하기
3. 유입원별로 방문 횟수 또는 CVR 집계하기
4. 접근 요일, 시간대 파악하기
15강 사이트 내의 사용자 행동 파악하기
1. 입구 페이지와 출구 페이지 계산하기
2. 이탈률과 직귀율 계산하기
3. 성과로 이어지는 페이지 파악하기
4. 페이지 가치 산출하기
5. 검색 조건들의 사용자 행동 가시화하기
6. 폴아웃 리포트를 사용해 사용자 회유를 가시화하기
7. 사이트 내부에서 사용자 흐름 파악하기
8. 페이지 완독률 집계하기
9. 사용자 행동 전체를 시각화하기
16강 입력 양식 최적화하기
1. 오류율 집계하기
2. 입력-확인-완료 까지의 이동률 집계하기
3. 입력 양식 직귀율 집계하기
4. 오류가 발생하는 항목과 내용 집계하기
7장 데이터 활용의 정밀도를 높이는 분석 기술
17강 데이터를 조합해서 새로운 데이터 만들기
1. IP 주소를 기반으로 국가와 지역 보완하기
2. 주말과 공휴일 판단하기
3. 하루 집계 범위 변경하기
18강 이상값 검출하기
1. 데이터 분산 계산하기
2. 크롤러 제외하기
3. 데이터 타당성 확인하기
4. 특정 IP 주소에서의 접근 제외하기
19강 데이터 중복 검출하기
1. 마스터 데이터의 중복 검출하기
2. 로그 중복 검출하기
20강 여러 개의 데이터셋 비교하기
1. 데이터의 차이 추출하기
2. 두 순위의 유사도 계산하기
8장 데이터를 무기로 삼기 위한 분석 기술
21강 검색 기능 평가하기
1. NoMatch 비율과 키워드 집계하기
2. 재검색 비율과 키워드 집계하기
3. 재검색 키워드를 분류해서 집계하기
4. 검색 이탈 비율과 키워드 집계하기
5. 검색 키워드 관련 지표의 집계 효율화하기
6. 검색 결과의 포괄성을 지표화하기
7. 검색 결과의 타당성을 지표화하기
8. 검색 결과 순위와 관련된 지표 계산하기
22강 데이터 마이닝
1. 어소시에이션 분석
23강 추천
1. 추천 시스템의 넓은 의미
2. 특정 아이템에 흥미가 있는 사람이 찾아보는 아이템 검색
3. 당신을 위한 추천 상품
4. 추천 시스템을 개선할 때의 포인트
5. 출력할 때 포인트
6. 추천과 관련한 지표
24강 점수 계산하기
1. 여러 값을 균형있게 조합해서 점수 계산하기
2. 값의 범위가 다른 지표를 정규화해서 비교 가능한 상태로 만들기
3. 각 데이터의 편차값 계산하기
4. 거대한 숫자 지표를 직감적으로 이해하기 쉽게 가공하기
5. 독자적인 점수 계산 방법을 정의해서 순위 작성하기