[Big Query] 지속률 계산하기 :: 가입일 이후 사용자는 일정 기간동안 얼마나 많은 서비스를 이용했을까?

 Today's Topic

• 지속률이란?
• 지속률을 집계하기 (실습)

- Used Function

• CASE구문
• UNION ALL 구문
• DATE() / DATE_ADD() 등 날짜 관련함수
• SUM, AVG등 집계함수
• SIGN함수
• JOIN(테이블결합)

 

안녕하세요.

오늘은 지속률에 대해 살펴보는 시간을 가져보도록 하겠습니다.

Business Analytics를 수행할 때 사용될 수 있는 기법으로,

고객들이 가입 이후 우리 서비스를 어느 정도로 이탈하였는가, 이용하는가를 분석해볼 수 있습니다.

 

---

1.  지속률의 개요

 

어느 신규 사업을 론칭하거나, 다른 회사 등에서 고객의 행동은 매우 중요하다.

특히 고객이 가입한 후 어떻게 행동하는지가 제일 관심이 갈 수 있을 것이다.

예를 들어, 고객이 가입하고 3일 정도 연속으로 하다가 그 이후 며칠, 몇주, 심지어는 몇 달동안 접속을 아예 하지 않는다면, 활성화된 사용자가 아닐 수 있으며

그래도 일주일에 한두번, 혹은 세번 주기로 접속을 꾸준히 하는 사용자라면 활성화된 사용자라고 볼 수 있다. 

그래서 이 들이 얼마나 되는지 분석하기 위한 기법으로 '지속률'과 '정착률'이 있다.

이번 시간에는 지속률의 개념, 그리고 다음 시간에는 거기에 따라오는 정착률까지 확인해 볼 예정이다.

 

지금부터는 지속률의 개념에 대해 살펴보도록 한다.

 

"지속률"
등록일 기준으로 이후 지정일동안 사용자가 서비스를 얼마나 이용했는지 나타내는 지표

 

지속률은 사용자가 가입 이후 얼마나 서비스를 이용했는가를 보는 것이 중점이다.

따라서 연속적으로 이용하지 않더라도, 지속자에 포함될 수 있고,

연속적으로, 혹은 더 자주, 정기적으로 해당 서비스를 이용하는 경우도 집계에 포함시킬 수도 있다.

즉, 등록일 다음날 지속률, 2일 뒤 지속률, 3일 뒤 지속률, 좀 더 넓히면 7일 뒤 지속률을 모두 계산할 수 있다는 의미이다.

 

예로 2월 10일에 가입한 사용자 a가 있다고 치자.

a는 2월 11일에 우리 서비스를 이용했다면 1일 지속자, 2월 12일에도 우리 서비스를 이용했다면 2일 지속자가 된다.

번면 2월 11일에 서비스를 이용하지 않아도, 2월 12일에 이용했다면 2일 지속자에 포함된다.

이렇게 사용자수/가입자수를 나눈다면 그 날의 다음날 지속률, 2일 지속률 등이 나오게 될 것이다.

 

지속률은 사용자가 매일 이용했으면 하는 서비스에 적합하다.

사용자가 더 자주 이용한다면 그 스케일은 더욱 커지는 지표이기 때문이다.

그래서 뉴스와 같이 매일 사용자가 정보를 얻을 수 있는 사이트

그리고 소셜 네트워크 서비스와 같이 매일 일상 혹은 정보를 공유할 수 있는 사이트에 이 지속률 개념을 사용하는 것이 적합하다.

 

---

2.  지속률을 집계하기 (실습)

 

집계 순서는 다음과 같다.

1. 최근 일자와 사용자별 등록일의 다음날을 계산한다.
2. 사용자의 액션 플래그를 계산한다.
3. 다음날 지속률을 계산한다.
4. 지속률을 세로 기반으로 집계하여 보기 편하게 만든다. (선택)

 

위 순서에서 선택이라고 적힌 부분은 해도 되고 안 해도 된다.

다만, 리포트를 조금 더 보기 좋게 만들기 위해서라면 하는 것을 권장한다.

 

★ 쿼리 작성에 앞서, 데이터의 부족으로 쿼리의 결과가 약간 부족하게 보일 수 있다는 점 미리 양해 부탁드립니다.

 

1. 최근 일자와 사용자별 등록일의 다음날을 계산한다.

지속률을 집계하기 위해서는 '가입 다음날, 얼마나 사람들이 많이 이용했을까'에 대한 정보, 즉 1일 지속률부터 집계해볼 필요가 있다. 

하지만 이를 바로 구하기가 어려운데, 그 이유는 다음날의 로그데이터가 모두 집계완료된 상태여야만 하기 때문이다.

만일, 등록일 다음날 사용한 이용자수가 0명일 경우, 진짜 이용자가 없어서 0명인지, 아니면 아직 로그가 쌓이지 않아 0명인지 알 수가 없기 때문으로, 이 경우 약간의 사전작업을 진행해야 한다.

 

사전작업은 로그 집계 기간 중 가장 최신 날짜를 추출하고, 이 날짜를 넘는 기간의 지속률은 null값으로 대치하는 방식을 사용해 본다.

 

참고로 유저와 관련된 정보가 있는 테이블은 user테이블, 사용자 액션과 관련된 정보가 있는 테이블은 action테이블이다.

----------1. 지속률 계산하기-----------------
--1) 로그별 최근 일자와 사용자별 등록일의 다음날을 계산하기

with action_log_users as (
  select 
   u.user_id, 
   u.register_date, 
   date(timestamp(a.stamp)) as action_date,
   max(date(timestamp(a.stamp))) over() as latest_date,
   date_add(cast(u.register_date as date), interval 1 day) as next_day_1
  from 
   inlaid-lane-373607.user as u
   left outer join
   inlaid-lane-373607.action as a
   on u.user_id = a.user_id
)
select * from action_log_users
order by register_date;

1) with ~ as ()구문

위 구문을 사용하여 action_log_users라는 임시테이블을 만든다.

 

2)  with구문 from절

user 테이블과 action테이블을 user_id칼럼을 기준으로 left outer join을 하고, user테이블의 별칭으로 u, action테이블의 별칭으로 a를 부여한다.

 

3) with구문 select절

user테이블에서 user_id, register_date칼럼을 추출한다.

 

4) with구문 select절

또한, 기존의 문자열 날짜 데이터를 날짜형 날짜 데이터로 변환하기 위해 date()함수를 사용해 timestamp자료형으로 변환한다. 이를 action_date라는 별칭으로 부르기로 지정한다.

 

5)  with구문 select절

날짜계산을 하기위해 register_date칼럼을 date타입으로 변환하고(원래는 문자열이었기때문) date_add함수의 interval 1 day를 인수로 지정하여 register_date로부터 1일 후의 날짜를 반환한다.

 

 

2. 사용자의 액션플래그를 계산한다.

액션플래그란, 액션을 취했는지 안했는지를 0과 1로 구분하는 것이다.

다음날에 대한 날짜를 구했다면 이제는 그 날에 사용자가 특정 액션을 했는지 안했는지 살펴보아야 한다.

이를 확인하기 위해 0과 1로 표시하며 특정 액션을 했다면 1, 하지 않았다면 0, 그 다음 날이 로그의 최근 날짜의 이후라면, 그 플래그를null로 반환한다.

 

위의 null지정이 이해가 안될 수 있는데, 다음 예를 통해 설명해 본다.

만일 오늘 날짜가 2023년 2월 20일이라면, 로그 데이터 상에서의 가장 최신 날짜는 아마도 2023년 2월 20일일 것이다. 하지만 완전한 로그데이터로 저장된 날은 2월 19일일 것이다.

만약 2월 19일의 로그데이터를 기반으로 플래그를 계산한다고 하자. 그리고 2023년 2월 19일에 가입한 회원이 있다고 하면, 해당 회원의 가입일 다음날(2월 20일)에 대한 액션 플래그는 존재하지 않으므로 null로 지정하는 것이다.

 

with action_log_users as (
  [1단계 코드를 참조]
)
, user_action_flag as (
  select
   user_id,
   register_date,
   -- 1. 첫 번째 바깥쪽 case함수 => 등록일 다음날이 로그의 최신 날짜 이전인지 확인한다.
   -- 2. 두 번째 안쪽 case함수 => 등록일 다음날에 날짜에 액션을 했다면 1, 안 했다면 0으로 표시한다.
   -- 3. sum함수를 사용 => 사용자별로 등록일 다음날에 한 액션의 합계를 구한다.
   -- 4. sign함수를 사용 => 등록일 다음날에 액션을 했는지, 안했는지 플래그로 나타낸다.
   sign(sum(case when next_day_1 <= latest_date then case when next_day_1 = action_date then 1 else 0 end end)) as next_1_day_action
  from
   action_log_users
  group by
   user_id, register_date
)
select * from user_action_flag
order by register_date, user_id;

1) with ~ as구문

위 구문을 사용해 user_action_flag라는 임시테이블을 만든다.

 

2) user_action_flag 임시테이블 group by절

유저id, 가입일자별로 묶는다.

 

3) user_action_flag 임시테이블  select절

action_log_users테이블에서 유저의id, 가입일자, 다음날 액션을 했는지 여부를 표시하는 플래그를 추출한다.

 

현재 위 테이블에서는 u002회원만이 가입일 기준 다음날 활동하였다.

 

 

3. 최종적으로 집계율을 계산한다.

집계율을 계산하는 방법은 플래그에 100을 곱하고 avg로 평균을 구해 비율퍼센테이지로 나타낸다.

이렇게 첫번째 일자에 대한 지속률을 구해보았으며, 2일의 지속률을 구할 경우에도 아래의 작업을 반복 실행하면 된다.

물론, date_add()에 나오는 인수값과 같이 변경될 수 있는 부분들은 변경해 주어야 한다.

with action_log_users as (
  select 
   u.user_id, 
   u.register_date, 
   -- 액션 날짜와 로그 전체의 최신 날짜를 날짜 자료형으로 변환하기...빅쿼리의 경우 타임스탬프 자료형으로 변환한 후 날짜 자료형으로 변환함
   date(timestamp(a.stamp)) as action_date,
   max(date(timestamp(a.stamp))) over() as latest_date,
   -- 등록일 다음날의 날짜 계산
   date_add(cast(u.register_date as date), interval 1 day) as next_day_1
  from 
   inlaid-lane-373607.user as u
   left outer join
   inlaid-lane-373607.action as a
   on u.user_id = a.user_id
)
, user_action_flag as (
  select
   user_id,
   register_date,
   -- 1. 첫 번째 바깥쪽 case함수 => 등록일 다음날이 로그의 최신 날짜 이전인지 확인한다.
   -- 2. 두 번째 안쪽 case함수 => 등록일 다음날에 날짜에 액션을 했다면 1, 안 했다면 0으로 표시한다.
   -- 3. sum함수를 사용 => 사용자별로 등록일 다음날에 한 액션의 합계를 구한다.
   -- 4. sign함수를 사용 => 등록일 다음날에 액션을 했는지, 안했는지 플래그로 나타낸다.
   sign(sum(case when next_day_1 <= latest_date then case when next_day_1 = action_date then 1 else 0 end end)) as next_1_day_action
  from
   action_log_users
  group by
   user_id, register_date
)
select
 register_date,
 avg(100.0*next_1_day_action) as repeat_rate_1_day
from
 user_action_flag
group by
 register_date
order by
 register_date;

 

즉, 10월 1일에 가입한 3명(u001,u002,u003) 중 1명(u002)만 가입 다음 날 활동하였으므로 33.3%의 비율이 나왔다.

 

4. 지속률을 세로 기반으로 집계하기

하지만 위와 같은 방법으로 볼 경우, 꽤나 복잡한 과정을 거치게 되며, 날짜가 직접적으로 나오게 되기는 하지만 며칠후와 같은 정돈된 형식으로는 확인하기 어렵다.

그래서 이를 세로 기반으로 집계하여, 테이블을 조금 더 정돈되게 만들어 주는 작업을 진행해보도록 하겠다.

여기서는 가입일 이후 1일부터 7일까지 7일동안의 집계율을 계산할 것이다.

--5) 지속률을 세로 기반으로 집계하기
with
-- 지속률 지표를 관리하는 마스터 테이블 작성
repeat_interval as (
select '01 day ' as index_name, 1 as interval_date
 union all select '02 day' as index_name, 2 as interval_date
 union all select '03 day' as index_name, 3 as interval_date
 union all select '04 day' as index_name, 4 as interval_date
 union all select '05 day' as index_name, 5 as interval_date
 union all select '06 day' as index_name, 6 as interval_date
 union all select '07 day' as index_name, 7 as interval_date)
, action_log_idx_date as (
  select
   u.user_id,
   u.register_date,
   -- 액션 날짜와 로그 전체의 최신 날짜를 날짜 자료형으로 변환하기...빅쿼리의 경우 타임스탬프 자료형으로 변환한 후 날짜 자료형으로 변환함
   date(timestamp(a.stamp)) as action_date,
   max(date(timestamp(a.stamp))) over() as latest_date,
   -- 등록일로부터 n일 후의 날짜 계산하기
   r.index_name,
   date_add(cast(u.register_date as date), interval r.interval_date day) as index_date
  from
   inlaid-lane-373607.sqlr202301.user as u
   left outer join
   inlaid-lane-373607.sqlr202301.action 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,
   -- 1. 첫 번째 바깥쪽 case함수 => 등록일로부터 n일 후가 로그의의 최신 날짜 이전인지 확인한다다.
   -- 2. 두 번째 안쪽 case함수 => 등록일로부터 n일 후의 날짜에 액션을 했다면 1, 안 했다면 0으로 표시한다.
   -- 3. sum함수를 사용 => 사용자별로 등록일로부터 n일 후에 한 액션의 합계를 구한다.
   -- 4. sign함수를 사용 => 등록일로부터 n일 후에 액션을 했는지, 안했는지 플래그로 나타낸다.
   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_idx_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;

 

앞서 진행했던 방식과 다른 점은 바로 직접 테이블을 생성했다는 점이다.

먼저 union  all을 사용하여 index_name과 interval_date칼럼 안에 들어가는 값들을 행끼리 모두 결합시킨다.

이렇게 되면 다음과 같은 테이블이 만들어 진다.

 

 

다음으로는 위 쿼리의 결과이다. 앞서 했던 방식과 비슷하지만, 테이블이 방금 만들었던 인덱스 기반의 테이블이므로

1~3번에서 진행했던 테이블에서 약간의 수정은 있었다.

다만, 앞서 말씀드린 바와 같이, 데이터의 부족으로 결과가 잘 나오지 않는다는 점은 양해해 주시기 바란다. 

 

 

10월 1일 가입자들의 지속률은 첫째날 33.3%, 그리고 넷째날 33.3%를 기록하였다.

이는 10월 1일 가입자 3명 중 1명만 첫째날, 넷째날에 활동을 한 것이다.

 

---

이렇게 오늘은 지속률에 대해 알아보았으며,

다음에는 정착률에 대해 알아보도록 하겠습니다.

이 둘의 개념은 비슷하지만 약간씩 다른 부분이 있기 때문에 이어서 설명드리도록 하겠습니다.

 

감사합니다.

 

★ 본 내용은 데이터 분석을 위한 sql레시피 12강을 참고하여 쓴 글입니다.