[PostgreSQL] 사용자 로그데이터(Log Data) 시계열분석(2) - 행동(action)과 지속률/정착률과의 관계 분석

로그데이터 매출 분석은 아래 포스팅들을 참고해주세요

1. 사용자 로그데이터(Log Data) 매출분석(1) - 사용자들의 특징 찾기 : ysyblog.tistory.com/146?category=1176025

2. 사용자 로그데이터(Log Data) 매출분석(2) - DECILE분석 : ysyblog.tistory.com/147?category=1176025

3. 사용자 로그데이터(Log Data) 매출분석(3) - RFM 분석 : ysyblog.tistory.com/148?category=1176025

4. 사용자 로그데이터(Log Data) 시계열분석(1) - 등록추이분석/지속률/정착률 : ysyblog.tistory.com/149

 

지속과 정착에 영향을 주는 액션 집계

지난 포스팅에서 살펴본 지속률과 정착률의 추이에 사용자의 액션이 어떻게 영향을 미치는지 분석해보겠습니다. 

customer_info 테이블과 로그데이터를 cross join 해서 모든 사용자의 액션 조합을 만듭니다. 그리고 사용자 등록일과 다음날에 액선이 있으면 1 없으면 0으로 나타냅니다.(do_action). 아래 쿼리는 지속률을 바탕으로 사용자의 액션을 파악하는 것입니다.(정착률도 쿼리는 같습니다.)

-- user_flag까지 지난 포스팅에서 했던 지속률 쿼리
with repeat_interval(index_name, interval_date) as(
	values
	('01 day repeat',1)
	,('04 day repeat',4)
	,('07 day repeat',7)
	,('10 day repeat',10)
	,('15 day repeat',15)
	,('20 day repeat',20)
	,('30 day repeat',30)
)
, log_index_date as(
	select c.customerid
		,c.register_date
		,s.date as action_date
		,max(s.date) over() as latest_date
		,r.index_name
		,cast(c.register_date + interval '1 day' * r.interval_date as date) as index_date
	from customer_info as c
		left outer join
		sales_date as s
		on c.customerid= s.customerid
		cross join
		repeat_interval as r
)
, user_flag as(
	select customerid
		,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_day_action
	from log_index_date
	group by customerid,register_date,index_name,index_date
)
-- 모든 사용자의 조합을 도출
, actions as(
			  select 'view' as action
	union all select 'basket' as action
	union all select 'buy' as action
)
, customer_actions as (
	select c.customerid
		,c.register_date
		,a.action
	from customer_info as c
		cross join
		actions as a
)  
, register_action_flag as(
	select distinct c.customerid
		,c.register_date
		,c.action
		,case when a.action is not null then 1 else 0
			end as do_action
		,index_name
		,index_day_action
from customer_actions as c
	left join sales_date as a
	on c.customerid = a.customerid
	and c.register_date = a.date
	and c.action = a.action
	left join user_flag as f
		on c.customerid = f.customerid
where f.index_day_action is not null
)
select *
from register_action_flag	
order by customerid, index_name, action;

index_day_action을 내림차순으로 정렬한 것은 대부분이 0이라 1을 보여주기 위함입니다.

위의 표를 해석하자면 12427 사용자는 등록일에 'basket','buy','view'에 대한 액션을 했으며 4일뒤에도 'basket','buy','view'을 했다는 것입니다.

이제는 액선에 따른 지속률을 집계하겠습니다.(정착률도 동일하게 하면 됩니다.)

with repeat_interval(index_name, interval_date) as(
	values
	('01 day repeat',1)
	,('04 day repeat',4)
	,('07 day repeat',7)
	,('10 day repeat',10)
	,('15 day repeat',15)
	,('20 day repeat',20)
	,('30 day repeat',30)
)
, log_index_date as(
	select c.customerid
		,c.register_date
		,s.date as action_date
		,max(s.date) over() as latest_date
		,r.index_name
		,cast(c.register_date + interval '1 day' * r.interval_date as date) as index_date
	from customer_info as c
		left outer join
		sales_date as s
		on c.customerid= s.customerid
		cross join
		repeat_interval as r
)
, user_flag as(
	select customerid
		,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_day_action
	from log_index_date
	group by customerid,register_date,index_name,index_date
)
-- 모든 사용자의 조합을 도출
, actions as(
			  select 'view' as action
	union all select 'basket' as action
	union all select 'buy' as action
)
, customer_actions as (
	select c.customerid
		,c.register_date
		,a.action
	from customer_info as c
		cross join
		actions as a
)  
, register_action_flag as(
	select distinct c.customerid
		,c.register_date
		,c.action
		,case when a.action is not null then 1 else 0
			end as do_action
		,index_name
		,index_day_action
from customer_actions as c
	left join sales_date as a
	on c.customerid = a.customerid
	and c.register_date = a.date
	and c.action = a.action
	left join user_flag as f
		on c.customerid = f.customerid
where f.index_day_action is not null
)
select action
	,count(1) users
	,avg(100.0 * do_action) as usage_rate
	,index_name
	,avg(case do_action when 1 then 100.0 * index_day_action end) as idx_rate
	,avg(case do_action when 0 then 100.0 * index_day_action end) as no_action_idx_rate
from register_action_flag	
group by index_name, action
order by index_name, action;

 

액션수에 따른 정착률 집계

이번에는 7일동안 실행한 액션수에 따라 14일 정착률이 어떻게 변화하는지 알아보겠습니다.

view와 basket의 단계를 0, 1~5, 6~10, 11이상으로 구분하는 임시 테이블을 만들고 이것과 로그데이터를 Cross Join합니다. 그리고 7일동안의 로그를 Left join하고 7일동안의 액션 수를 집계하겠습니다. 도수분포표를 만드는 과정과 유사합니다!

with repeat_interval(index_name, interval_date_start, interval_date_end) as(
	values
('14 day retention',8,14)

)
, log_index_date as(
	select c.customerid
		,c.register_date
		,s.date as action_date
		,max(s.date) over() as latest_date
		,r.index_name
		-- 지표의 대상기간 시작일과 종료일 계산
		,cast(c.register_date + '1 day'::interval * r.interval_date_start as date) as index_date_start
		,cast(c.register_date + '1 day'::interval * r.interval_date_end as date) as index_date_end

	from customer_info as c
		left outer join
		sales_date as s
		on c.customerid= s.customerid
		cross join
		repeat_interval as r
)
, user_flag as(
	select customerid
		,register_date
		,index_name
		-- 지표 대상기간에 액선을 했는지 나타내고(SIGN) 사용자 별로 대상 기간에 한 액션 합계 구한후(SUM)
		-- 대상 기간의 종료일이 로그의 최신날짜 이전인지 확인하고(첫번째 CASE)
		-- 지표의 대상기간이 액션을 했으면 1, 안했으면 0이라고 지정한다.(두번째 CASE)
		,sign(sum(CASE WHEN index_date_end <= latest_date THEN 
				 	case when action_date between index_date_start and index_date_end then 1 else 0 end
				   END)) as index_day_action
	from log_index_date
	group by customerid,register_date,index_name,index_date_start,index_date_end
)
, action_bucket(action, min_count, max_count) as (
values
	('view',0,0)
	,('view',1,5)
	,('view',6,10)
	,('view',11,999)
	,('basket',0,0)
	,('basket',1,5)
	,('basket',6,10)
	,('basket',11,999)
), user_action_bucket as(
select c.customerid, c.register_date, a.action, a.min_count, a.max_count
from customer_info as c
	cross join
	action_bucket as a
), register_action_flag as (
	select m.customerid
		,m.action
		,m.min_count
		,m.max_count
		,count(a.action) as action_count
		,case when count(a.action) between m.min_count and m.max_count then 1 else 0
			End as achieve
		,index_name
		,index_day_action
	from user_action_bucket as m
	left join
		-- 등록일 당일부터 7일 후까지 로그 결합하기
		sales_date as a
		on m.customerid = a.customerid
		and a.date between m.register_date and m.register_date + interval '7 days'
		and m.action = a.action
	left join
		user_flag as f
		on m.customerid = f.customerid
	where f.index_day_action is not null
	group by m.customerid, m.action, m.min_count, m.max_count, f.index_name, f.index_day_action
)
select * 
from register_action_flag
order by customerid, action, min_count;

 

이제 위 코드를 기반을 14일 정착률을 계산하겠습니다. 위의 마지막 Select문 대신에 아래 코드를 사용하면됩니다.

select action
	,min_count || '~' || max_count as count_range -- 최소개수와 최대개수 문자열을 연결하여 기준만들기
	,sum(case achieve when 1 then 1 else 0 end)
	,index_name
	,avg(case achieve when 1 then 100.0 * index_day_action end) as achieve_index_rate
from register_action_flag
group by index_name, action, min_count, max_count
order by index_name, action, min_count;

이렇게 액션별로 사용자를 집계하면 사용자가 어떤 기능을 더 많이 사용하도록 유도해야하는지 알 수 있습니다.

 

해당 포스팅은 다음의 책을 활용하여 작성하였습니다.

- 가사키 나가토, 다미야 나오토,  『데이터 분석을 위한 SQL 레시피』, 한빛미디어, p233~257