사용자는 서비스 사용 시작일로부터 시간이 지나면 상태 변화가 일어남
- 충성도 높은 사용자로 성장
- 사용을 중지
- 가입은 되어 있지만 사용하지 않는 상태(휴면)
서비스 운영자 입장에서는?
- 사용자가 계속해서 사용하도록 해야함
-> 사용자가 어느 정도 계속해서 사용하는 지 파악
-> 사용자의 서비스 사용을 시계열로 수치화하고 변화를 시각화
- 휴면 사용자를 어떻게 하면 다시 사용하게 만들지 생각 - >메일 매거진/CM/광고
- 완전 탈퇴 사용자는 대책을 적용하기 어려움
1. 등록 수의 추이와 경향 보기
- 사용자 등록이 필요한 서비스에서 등록 수는 중요한 지표
등록자 감소 경향 -> 서비스 활성화하기 어려워짐
등록자 증가 경향 -> 사용자가 서비스에서 이탈할지 아닐지를 분석해서 서비스 활성화와 연결해야 함
날짜별 등록 수의 추이
register_date 로 group by해서 distinct user_id를 count하면 날짜별 등록 수의 추이를 집계할 수 있음!
월별 등록 수 추이
- 날짜 자료형의 데이터에서 연,월 추출
- LAG 윈도 함수로 전월 등록 수와 비율 집계 : 기준 데이터의 이전행의 값을 반환해 주는 함수
with
mst_users_with_year_month as (
select
*
,substr(register_date,1,7) as year_month
from
mst_users)
select
year_month
,count(distinct user_id) as register_count
,lag(count(distinct user_id)) over(order by year_month) as last_month_count
,round(100.0 * count(distinct user_id) / lag(count(distinct user_id)) over(order by year_month),2) as month_over_month_ratio
from
mst_users_with_year_month
group by
year_month;
등록 디바이스별 추이
- CASE 함수 사용
with
mst_users_with_year_month as (
select
*
,substr(register_date,1,7) as year_month
from
mst_users)
select
year_month
,count(distinct user_id) as register_count
,count(distinct case when register_device = 'pc' then user_id end) as register_pc
,count(distinct case when register_device = 'sp' then user_id end) as register_sp
,count(distinct case when register_device = 'app' then user_id end) as register_app
from
mst_users_with_year_month
group by
year_month
디바이스가 pc인 사용자는 user_Id가 그대로 나오고 아닌 사용자는 null 값이다
-> count(열이름)을 사용해서 null을 제외하고 count가 됨
+ 멀티 디바이스 사용자가 있을 수 있음 -> 이걸 파악해두는 것도 좋음
2. 지속률과 정착률 산출하기
- 등록 시점을 기준으로 일정 기간 동안 사용자가 지속해서 사용하고 있는지를 조사
새로운 지표 정의
지속률 : 등록일 기준으로 지정일 동안 사용자가 서비스를 얼마나 이용했는지 나타내는 지표
- 지속사용자 수 / 등록수
-> 사용자가 매일 사용했으면 하는 서비스
-> 1일 지속 사용자 : 다음날 사용
-> 2일 지속 사용자 : 2일 뒤 사용 or 다음날과 2일뒤 모두 사용
: n일 지속 사용자 라고 하면 n일 뒤에만 사용하고 있으면됨.
정착률 : 등록일 기준으로 이후 지정한 7일 동안 사용자가 서비스를 사용했는지 나타내는 지표
- 기간에 한 번이라도 서비스를 사용했다면 정착자(1)로 다룸
- 정착자 수 / 등록수
-> 사용자에게 어떤 목적이 생겼을 때 사용했으면 하는 서비스
지속률
- 다음날(1일) 지속률이 중요
-> 등록 바로 다음 날부터 서비스를 사용하지 않는다면, 이후 습관적으로 서비스를 사용하도록 만들기 어렵
-> 지정한 날짜 다음에 사용한 사용자에 1, 아니면 0 이라는 플래그를 붙이고 avg 함수를 적용해 평균 계산
주의
- 등록 다음 날 사용자가 진짜 0 명인 경우 vs 로그가 아직 쌓이지 않아서 0명인 경우 -> 구분 해야함
- 로그 집계 기간 중에서 가장 최신 날짜 추출 -> 최신 일자를 넘는 기간의 지속률은 NULL로 출력
코드 12-4
- 등록날짜, 액션날짜, 가장 최근 액션날짜, 액션날짜 다음날
- cast 랑 :: 는 같은 기능. 가독성을 위해 :: 쓰는 게 더 예쁨
코드 12-5
- sign : -1, 0, 1 : sum을 통해 지정한 날 다음날에 액션을 한 번이라도 했으면 1
- next_day_1 <= latest_date 가 굳이 필요한가?
- 등록 날짜와 최근 액션날짜가 같은 경우는 next_day에 대한 로그가 없으니까 null 처리 해야함
코드 12-6
- 100.0 곱하고 avg로 평균내기
코드 12-8
- 길어서 이게 뭔소린가 싶겠지만 그냥 위에서 했던 코드들은 바로 다음날 기준인거고
- repeat_interval 테이블을 만들어서 바로 다음날인지, 2일 뒤 인지, 3일 뒤 인지에따라서 지속률을 나타냄
정착률
- 7일 정착률이 극단적으로 낮다면?
-> 다음날 지속률 ~ 7일 지속률을 확인해서 문제 검토하기
코드 12-10
- 정착률을 알기 위해선 특정 날짜 안에 한 번이라도 사용 경험이 있는지 판단해야함
- begin_date, interval_end_date 필요
- 대상 기간안에 액션을 했다면 1, 아니면 0
만약 지속률 or 정착률이 극단적으로 떨어지는 시점이 있다면?
-> 그 때를 기점으로 다양한 이벤트를 기획할 수 있음.
계속 지속하도록, 계속 정착해있도록
지속률과 정착률 모두 n일 후의 행동을 집계함
-> 오래 걸리는 지표 보다는 단기간에 결과를 보고 대책을 세울 수 있는 지표가 좋다!
ex 60일 지속률은 x
3. 지속과 정착에 영향을 주는 액션 집계하기
지속률과 정착률 만으로는 유의미한 조치를 취할 수 없으니
어떤 행동이 지속과 정착에 어떤 영향을 미쳤는지 알아야함
1일 지속률을 개선하고 싶다면?
-> 즉, 등록 다음날에도 사용하게 만들고 싶다면?
-> 등록 당일 사용자들이 무엇을 했는지 확인
14일 정착률을 개선하고 싶다면?
-> 등록 후 14일 동안 한번이라도 사용한 사람들의 비율
-> 7일 정착률 판정기간동안, 즉 앞선 7일 동안 사용자가 무슨 행동을 했는지 확인
사용자의 1일 지속률이 높은 액션, 비사용자의 1일 지속률이 낮은 액션
-> 이러한 액션을 한다면 다음날에도 앱을 사용한다. 이러한 액션을 안하면 다음날 앱을 사용 안한다
-> 중요한 액션
-> 이벤트를 통해 이러한 액션의 사용을 촉진해야함!
+ 그러한 액션을 하기 까지 접속한 페이지, 세션 등을 검토해보는 것 또한 좋을 듯?
'SQL > [책] 데이터 분석을 위한 SQL 레시피' 카테고리의 다른 글
| [데이터 분석을 위한 SQL 레시피] 19강 - 데이터 중복 검출하기 (0) | 2023.11.20 |
|---|---|
| [데이터 분석을 위한 SQL 레시피] 15강 - 사이트 내의 사용자 행동 파악하기 (2) | 2023.11.06 |
| [데이터 분석을 위한 SQL 레시피] 11강 - 사용자 전체의 특징과 경향 찾기 (0) | 2023.10.02 |
| [데이터 분석을 위한 SQL 레시피] 8강 - 여러 개의 테이블 조작하기 (0) | 2023.09.17 |
| [데이터 분석을 위한 SQL 레시피] 7강 - 하나의 테이블에 대한 조작 (0) | 2023.09.17 |
