데이터 분석을 위한 SQL 레시피 5장 공부

사용자를 파악하기 위한 데이터 추출

유저가 특정 기간 동안 기능을 얼마나 사용하는지 집계

•  COUNT(*) == COUNT(1)

-- 로그 전체의 유니크 사용자 수 구하기
with stats as (
	select count(distinct session) as total_uu
	from action_log 
)

select l.action
	-- 액션 UU(unique users)
	, count(distinct l.session) as action_uu
	-- 액션 수
	, count(1) as action_count
	-- 전체 UU
	, s.total_uu
	-- 사용률
	, 100.0*count(distinct l.session) / s.total_uu as usage_rate
	-- 1인당 액션 수
	, 1.0*count(1) / count(distinct l.session) as count_per_user
from action_log as l
cross join stats as s
group by l.action, s.total_uu;

 

연령별 구분 집계하기

• 사용자의 나이 계산

-- 생일과 특정 날짜(나이 계산하는 날)를 정수로 표현하기
with mst_users_with_int_birth_date as (
	select *
		-- 특정 날짜의 정수 표현
		, 20170101 as int_specific_date
		-- 문자열로 구성된 생년월일을 정수 표현으로 변환
		, cast(replace(substring(birth_date, 1, 10), '-', '') as integer) as int_birth_date
	from mst_users
)
-- 나이 계산하기
, mst_users_with_age as (
	select *
		-- 특정 날짜의 나이
		, floor((int_specific_date - int_birth_date)/10000) as age
	from mst_users_with_int_birth_date
)
select user_id
	, sex
	, birth_date
	, age
from mst_users_with_age;

• 성별과 연령으로 구분하기
  • 3살 이하인 사용자의 경우 연령 구분 코드가 NULL
    • CONCAT 함수는 매개 변수 중 하나만 NULL이어도 전체 결과가 NULL

-- 연령대 구분하기
, mst_users_with_category as (
	select user_id
		, sex
		, age
		-- 구분
		, concat(
			case
				when 20 <= age then sex
				else ''
			end
			, case 
				when age between 4 and 12 then 'C'
				when age between 13 and 19 then 'T'
				when age between 20 and 34 then '1'
				when age between 35 and 49 then '2'
				when age >= 50 then '3'
			end
			) as category
	from mst_users_with_age
)

select * from mst_users_with_category;

 

벤 다이어그램으로 사용자 액션 집계하기

• 액션을 했으면 1, 아니면 0

with user_action_flag as (
	select user_id
		, sign(sum(case when action = 'purchase' then 1 else 0 end)) as has_purchase
		, sign(sum(case when action = 'review' then 1 else 0 end)) as has_review
		, sign(sum(case when action = 'favorite' then 1 else 0 end)) as has_favorite
	from action_log 
	group by user_id
)

select * from user_action_flag;

• 모든 액션 조합에 대한 사요자 수 계산
  • CUBE(컬럼명, 컬러명,...)
    • 그룹핑 컬럼이 가질 수 있는 모든 경우의 수에 대하여 소계(SUBTOTAL)과 총계(GRAND TOTAL)을 생성

, action_venn_diagram as (
	select has_purchase
		, has_review
		, has_favorite
		, count(1) as users
	from user_action_flag
	group by cube(has_purchase, has_review, has_favorite)
)

select *
from action_venn_diagram
order by has_purchase, has_review, has_favorite;

• 벤 다이어그램을 만들기 위해 데이터 가공하는 쿼리

select 
	-- 0, 1 플래그를 문자열로 가공
	case has_purchase
		when 1 then 'purchase'
		when 0 then 'not purchase'
		else 'any'
	end as has_purchase
	, case has_review
		when 1 then 'review'
		when 0 then 'not review'
		else 'any'
	end as has_review
	, case has_favorite
		when 1 then 'favorite'
		when 0 then 'not favorite'
		else 'any'
	end as has_favorite
	, users
	-- 전체 사용자 수를 기반으로 비율 구하기
	, 100.0*users
		/ nullif(
			-- 모든 액션이 null인 사용자 수가 전체 사용자 수를 나타냄
			sum(case when has_purchase is null 
					and has_review is null 
					and has_favorite is null 
					then users else 0 end) over()
		, 0) as ratio
from action_venn_diagram
order by has_purchase, has_review, has_favorite;

• 어떠한 대책을 수행했을 때, 해당 대책으로 효과가 발생한 사용자가 얼마나 되는지 벤다이어그램으로 확인하면, 대책을 제대로 세웠는지 확인할 수 있다.

 

Decile 분석을 사용해 사용자를 10단계 그룹으로 나누기

• 주요 고객 세그먼트의 개념에 다음과 같은 것이 있다.
  • 데모그래픽 : 연령대, 성별, 직업, 소득 등 인구통계적으로 나누는 사고방식
  • 지오그래픽 : 국가, 지역, 기후 등 지리적 조건으로 나누는 사고방식
  • 사이코그래픽 : 가치관이나 라이프스타일, 행동양식 등의 특징으로 나누는 사고방식
• Decile : 10분의 1
• 구매액이 많은 순서로 사용자 그룹을 10등분
  • NTILE(컬럼명) OVER()
    • 행 데이터를 그룹별로 나누어 차례대로 행 번호를 부여하는 분석 함수

-- 유저 구매액 구하기
with user_purchase_amount as (
	select user_id
		, sum(amount) as purchase_amount
	from action_log 
	where action = 'purchase'
	group by user_id
)
-- 10%씩 그룹 만들기
, users_with_decile as (
	select user_id
		, purchase_amount
		, ntile(10) over (order by purchase_amount desc) as decile
	from user_purchase_amount
)

select * from users_with_decile;

• 각 그룹 집약을 계산

, decile_with_purchase_amount as (
	select decile
		, sum(purchase_amount) as amount
		, avg(purchase_amount) as avg_amount
		-- 누계 구매 금액
		, sum(sum(purchase_amount)) over (order by decile) as cumulative_amount
		-- 전체 구매 금액
		, sum(sum(purchase_amount)) over () as total_amount
	from users_with_decile
	group by decile
)

select * from decile_with_purchase_amount;

 

https://chaemi720.tistory.com/375

RFM 분석

 

지속률

• 등록일 기준으로 이후 지정일 동안 사용자가 서비스를 얼마나 이용했는지 나타내는 지표
• 용도 : 사용자가 매일 사용했으면 하는 서비스
• '로그 최근 일자'와 '사용자별 등록일의 다음날'을 계산
  • OUTER JOIN
    • 내부 조인은 두 테이블에 모두 데이터가 있어야만 결과가 나오지만, 외부 조인은 한쪽에만 데이터가 있어도 결과가 나옴

with action_log_with_mst_users as (
	select u.user_id
		, u.register_date
		, cast(a.stamp as date) as action_date
		-- 가장 최신 날짜
		, max(cast(a.stamp as date)) over() as latest_date
		-- 등록일 다음날의 날짜 계산
		, cast(u.register_date::date + '1 day'::interval as date) as next_day_1
	from mst_users as u
	left outer join action_log as a
		on u.user_id = a.user_id
)

select *
from action_log_with_mst_users
order by register_date;

• 사용자의 액션 플래그 계산

, user_action_flag as (
	select user_id
		, register_date
		,
		-- 4. 등록일 다음날 액션 했으면 1이상, 아니면 0일테니 구분
		sign(
			-- 3. 해당 유저의 다음날 액션 합
			sum(
				-- 1. 등록일 다음날이 로그 최신 날짜 이후인가?
				case
					when next_day_1 <= latest_date 
						-- 2.등록일 다음날에 액션 했는가?
						then case 
							when next_day_1 = action_date then 1 else 0
						end
				end
			)
		) as next_1_day_action
	from action_log_with_mst_users
	group by user_id, register_date
)

select *
from user_action_flag
order by register_date, user_id;

• 다음날 지속률 계산

select register_date
	, avg(100.0*next_1_day_action) as repeart_rate_1_day
from user_action_flag
group by register_date
order by register_date;

• n일 후 지속률 계산

with repeat_interval (index_name, interval_date) as (
	values ('01 day repeat', 1)
		, ('02 day repeat', 2)
		, ('03 day repeat', 3)
		, ('04 day repeat', 4)
		, ('05 day repeat', 5)
		, ('06 day repeat', 6)
		, ('07 day repeat', 7)
)

-- n일 후 날짜 계산
, action_log_with_index_date as (
	select u.user_id
		, u.register_date
		-- 액션 날, 로그 전체 최신 날
		, cast(a.stamp as date) as action_date
		, max(cast(a.stamp as date)) over() as latest_date
		-- 등록일로부터 n일 후의 날짜
		, r.index_name
		, cast(cast(u.register_date as date) + interval '1 day' * r.interval_date as date) as index_date
	from mst_users as u
	left outer join action_log as a
		on u.user_id = a.user_id 
	cross join repeat_interval as r
)

-- n일 후 활동 유무 확인
, user_action_flag as (
	select user_id
		, register_date
		, index_name
		
		-- 지속률
		, sign(
			sum(
				case when index_date <= latest_date
						then case when index_date = action_date then 1 else 0
						end
				end	
			)
		) as index_date_action
	from action_log_with_index_date
	group by user_id, register_date, index_name, index_date
)

select register_date
	, index_name
	, avg(100.0*index_date_action) as repeat_rate
from user_action_flag
group by register_date, index_name
order by register_date, index_name;

 

정착률

• 등록일 기준으로 이후 지정한 7일 동안 사용자가 서비스를 사용했는지 나타내는 지표
• 용도 : 사용자에게 어떤 목적이 생겼을 때 사용햇으면 하는 서비스

with repeat_interval(index_name, interval_begin_date, interval_end_date) as (
	values 
		('07 day retention', 1, 7)
		, ('14 day retention', 8, 14)
		, ('21 day retention', 15, 21)
		, ('28 day retention', 22, 28)
)
-- 대상 시작일과 종료일 계산
, action_log_with_index_date as (
	select u.user_id
		, u.register_date
		-- 액션 날짜, 최신 날짜
		, cast(a.stamp as date) as action_date
		, max(cast(a.stamp as date)) over() as latest_date
		, r.index_name
		-- 지표의 대상 기간 시작일과 종료일 계산
		, cast(u.register_date::date + '1 day'::interval*r.interval_begin_date as date) as index_begin_date
		, cast(u.register_date::date + '1 day'::interval*r.interval_end_date as date) as index_end_date
	from mst_users as u
	left outer join action_log as a
		on u.user_id = a.user_id
	cross join repeat_interval as r
)

-- 기간 동안 활동 유무 확인
, user_action_flag as (
	select user_id
		, register_date
		, index_name
		-- 기간 내 활동했는지 확인
		, sign(
			sum(
				case when index_end_date <= latest_date then
					case when action_date between index_begin_date and index_end_date
						then 1 else 0
					end 
				end
			)	
		) as index_date_action
	from action_log_with_index_date
	group by user_id, register_date, index_name, index_begin_date, index_end_date
)

select register_date
	, index_name
	, avg(100.0*index_date_action) as index_rate
from user_action_flag
group by register_date, index_name
order by register_date, index_name;

 

MAU(Monthly Active users)

• 신규 사용자
• 리피트 사용자 : 이전 달에도 사용했던 사용자
• 컴백 사용자 : 이번 달의 신규 등록자가 아니고, 이전 달에도 사용하지 않았던, 한동안 사용하지 않았다가 돌아온 사용자
• 신규, 리피트, 컴백 수 각각 집계

-- 월별 사용자 액션 집약
with monthly_user_action as (
	
	select distinct u.user_id
		-- 등록월, 액션월
		, substring(u.register_date, 1, 7) as register_month
		, substring(l.stamp, 1, 7) as action_month
		-- 액션월 한달 전
		, substring(cast(l.stamp::date - interval '1 month' as text), 1, 7) as action_month_priv
	from mst_users as u
	join action_log as l
		on u.user_id = l.user_id
)

-- 월별 사용자 분류 테이블
, monthly_user_with_type as (
	select action_month
		, user_id
		, case
			-- 신규 사용자?
			when register_month = action_month then 'new_user'
			-- 리피트 사용자?
			when action_month_priv = lag(action_month) over(partition by user_id order by action_month) then 'repeat user'
			-- 나머지 컴백 사용자
			else 'come_back_user'
		end as c
		, action_month_priv
	from monthly_user_action
)

select action_month
	-- 특정 달의 MAU
	, count(user_id) as mau
	, count(case when c = 'new_user' then 1 end) as new_user_mau
	, count(case when c = 'repeat user' then 1 end) as repeat_user_mau
	, count(case when c = 'come_back_user' then 1 end) as comback_user_mau
from monthly_user_with_type
group by action_month
order by action_month;

 

성장지수

• 성장지수
  • 사용자의 서비스 사용과 관련한 상태 변화를 수치화해서 서비스가 성장하는지 알려주는 지표
    • 1이상이라면 서비스가 성장한다는 뜻
    • 0미만이라면 서비스가 퇴보중이라는 뜻
  • Signup + Reactivation - Deactivation - Exit
    • 계속 사용하는 사용자와 계속 사용하지 않는 사용자는 성장지수에 영향을 주지 않음
• 성장지수 집계

with unique_action_log as (
	select distinct user_id
		, substring(stamp, 1, 10) as action_date
	from action_log
)

-- 집계하고 싶은 기간을 캘린더 테이블로 만들어두기
, mst_calendar as (
			  select '2016-10-01' as dt
	union all select '2016-10-02' as dt
	union all select '2016-10-03' as dt
	-- 생략
	union all select '2016-11-04' as dt
)

-- 사용자 마스터에 캘린더 테이블의 날짜를 target_date로 추가하기
, target_date_with_user as (
	select c.dt as target_date
		, u.user_id
		, u.register_date
		, u.withdraw_date
	from mst_users as u
	cross join mst_calendar as c
)

-- 사용자 상태 체크
, user_status_log as (
	select u.target_date
		, u.user_id
		, u.register_date
		, u.withdraw_date
		, a.action_date
		-- 신규유저?
		, case when u.register_date = a.action_date then 1 else 0 end as is_new
		-- 탈퇴유저?
		, case when u.withdraw_date = a.action_date then 1 else 0 end as is_exit
		-- 사용 유저
		, case when u.target_date = a.action_date then 1 else 0 end as is_access
		-- 이전 사용 유저
		, lag(case when u.target_date = a.action_date then 1 else 0 end) 
			over(partition by u.user_id order by u.target_date) as was_access
	from target_date_with_user as u
	left join unique_action_log as a
		on u.user_id = a.user_id
			and u.target_date = a.action_date
	where
		-- 집계 기간을 등록일 이후로만 필터링
		u.register_date <= u.target_date
		-- 탈퇴 날짜가 포함되어 있으면, 집계 기간을 탈퇴 날짜 이전만으로 필터링
		and (u.withdraw_date is null or u.target_date <= u.withdraw_date)
)

-- 성장 지수 계산
, user_growth_index as (
	select *
		, case
			-- 신규 등록, 탈퇴
			when is_new + is_exit = 1 then
				case
					when is_new = 1 then 'signup'
					when is_exit = 1 then 'exit'
				end
			-- reactivation, deactivarion
			when is_new + is_exit = 0 then
				case
					when was_access = 0 and is_access = 1 then 'reactivation'
					when was_access = 1 and is_access = 0 then 'deactivation'
				end
		end as growth_index
	from user_status_log
		
)

select target_date
	, sum(case growth_index when 'signup' then 1 else 0 end) as signup
	, sum(case growth_index when 'reactivation' then 1 else 0 end) as reactivation
	, sum(case growth_index when 'deactivation' then -1 else 0 end) as deactivation
	, sum(case growth_index when 'exit' then -1 else 0 end) as exit
	-- 성장 지수 계산
	, sum(
		case growth_index
			when 'signup' then 1
			when 'reactivation' then 1
			when 'deactivation' then -1
			when 'exit' then -1
			else 0
		end
	) as growth_index
from user_growth_index
group by target_date
order by target_date;

 

카트 추가 후에 구매 파악

• 상품 카트 추가 후 구매까지 시간 구하기 
  • regexp_split_to_table(컬럼명, 구분기호)
    • 구분기호로 구분된 컬럼을 리스트로 전개하기

with row_action_log as (
	select dt
		, user_id
		, action
		-- 쉽표로 구분된 product_id 리스트로 전개하기
		, regexp_split_to_table(products, ',') as product_id
		, stamp
	from action_log
)

-- 사용자와 상품 조합의 카트 추가 시간과 구매 시간 추출
, action_time_status as (
	select user_id
		, product_id
		, min(case action when 'add_cart' then dt end) as dt
		, min(case action when 'add_cart' then stamp end) as add_cart_time
		, min(case action when 'purchase' then stamp end) as purchase_time
		-- 카트 추가 후 구매까지 시간
		, extract(epoch from 
			min(case action when 'purchase' then stamp::timestamp end)
			- min(case action when 'add_cart' then stamp::timestamp end)) as lead_time 			
	from row_action_log
	group by user_id, product_id
)

select * from action_time_status

• 카트 추가 후 n 시간 이내에 구매된 상품 수와 구매율 집계

with row_action_log as (
	select dt
		, user_id
		, action
		-- 쉽표로 구분된 product_id 리스트로 전개하기
		, regexp_split_to_table(products, ',') as product_id
		, stamp
	from action_log
)

-- 사용자와 상품 조합의 카트 추가 시간과 구매 시간 추출
, action_time_status as (
	select user_id
		, product_id
		, min(case action when 'add_cart' then dt end) as dt
		, min(case action when 'add_cart' then stamp end) as add_cart_time
		, min(case action when 'purchase' then stamp end) as purchase_time
		-- 카트 추가 후 구매까지 시간
		, extract(epoch from 
			min(case action when 'purchase' then stamp::timestamp end)
			- min(case action when 'add_cart' then stamp::timestamp end)) as lead_time 			
	from row_action_log
	group by user_id, product_id
)

, purchase_lead_time_flag as (
	select user_id
		, product_id
		, dt
		, case when lead_time <= 1*60*60 then 1 else 0 end as purchase_1_hour
		, case when lead_time <= 6*60*60 then 1 else 0 end as purchase_6_hour
		, case when lead_time <= 24*60*60 then 1 else 0 end as purchase_24_hour
		, case when lead_time <= 48*60*60 then 1 else 0 end as purchase_48_hour
		, case 
			when lead_time is null or not (lead_time <= 48*60*60) then 1
			else 0
		end as not_purchase
	from action_time_status
)

select dt
	, count(*) as add_cart
	, sum(purchase_1_hour) as purchase_1_hour
	, avg(purchase_1_hour) as purchase_1_hour_rate
	, sum(purchase_6_hour) as purchase_6_hour
	, avg(purchase_6_hour) as purchase_6_hour_rate
	, sum(purchase_24_hour) as purchase_24_hour
	, avg(purchase_24_hour) as purchase_24_hour_rate
	, sum(purchase_48_hour) as purchase_48_hour
	, avg(purchase_48_hour) as purchase_48_hour_rate
	, sum(not_purchase) as not_purchase
	, avg(not_purchase) as not_purchase_rate
from purchase_lead_time_flag
group by dt;

 

평균 매출 금액

• ARPU (Average Revenue Per User) : 1인당 평균 매출 금액
• ARPPU (Average Revenue Per Paid User) : 과금 사용자 1인당 평균 매출 금액

 

LTV

• Life Time Value
• 고객이 생애에 걸쳐 어느 정도로 이익에 기여를 하는지를 산출한 것
  • CPA(Cost Per Acquistion)를 설정/관리할 때 굉장히 중요한 지표
• LVT = 연간 거래액 * 수익률 * 지속연수
  • 인터넷 서비스는 사이클이 굉장히 빠르므로 측정 단위를 몇 달 또는 반년 정도로 설정하는 것이 좋다.