[Big Query] 정착률 계산하기 :: 가입일 이후 사용자는 해당 서비스에 정착했는가?

 Today's Topic

• 정착률이란?
• 정착률을 계산하는 실습
• 생활 속 지속률과 정착률  - 게임의 출석이벤트

- Used Function

• CAST 등의 데이터 변환함수
• DATE등의 날짜함수
• SUM, MAX, AVG 등의 집계함수
• SIGN함수 (플래그변환)
• JOIN개념
• CASE구문

 

안녕하세요.

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

지속률과 비슷한 개념일 수 있지만, 약간 다른 개념입니다.정착률은 사용자가 얼마나 해당 서비스에 정착을 했는가?를 알아보는 지표라고 볼 수 있습니다.그렇다면 지금부터 알아보도록 하겠습니다.

 

---

1.  정착률의 개요

 

이전 시간에는 지속률에 대해 살펴보았습니다.

아래 내용과 이어지는 부분이 있을 수 있으므로, 한 번 읽어오는 것을 권합니다.

https://dtandard.tistory.com/32

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

 

이번 시간에는 정착률에 대해 살펴보고, 지속률과 어떤 차이점이 있는지 살펴본다.

 

정착률이란, 가입일을 기준으로 이후 지정한 7일 동안 사용자가 서비스를 사용했는지 나타내는 지표이다.

즉, 사용자가 서비스를 7일이내 한 번이라도 사용했다면 1, 아니라면 0으로 표현하여,

사용자가 얼마나 꾸준히 접속했는가 혹은 지속적으로 이용했는가를 본다기보다는,

사용자가 서비스를 이용했는지 여부에 초점을 맞추어 해당 서비스에 정착하였는가를 보는 지표라고 할 수 있다.

 

그래서 매일매일 접속하는 뉴스와 같은 사이트보다는 목적성이 뚜렷한 웹사이트를 이용할 경우 유용한 방법이다.

예시로 사용자가 어떠한 정보를 필요로 할 경우 이용할 수 있는 백과사이트(나무위키, 위키피디아 등)나 쇼핑몰 사이트(e커머스 사이트)와 같은 경우가 있다.

 

그렇다면 이번 정착률은 어떻게 계산할 수 있는지 한 번 살펴보도록 하겠다.

 

 

---

2.  정착률을 계산하는 실습

 

그렇다면 이러한 정착률은 sql로 어떻게 계산할 수 있을지 실습을 진행해보도록 하겠다.

쿼리진행순서는 다음과 같다.

 

정착률 계산 쿼리진행순서
1. 정착률 지표를 만들기 위한 마스터 테이블 만들기
2. 정착률 지표 집계 쿼리 실습 (timetable로의 변환, 지표의 대상 시작-종료일 설정, action flag)
3. 지속률과 정착률의 추이 확인

 

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

 

 

1. 정착률 지표를 만들기 위한 마스터 테이블 만들기

일단 정착률을 본격적으로 집계하기 위한 사전작업으로, 마스터테이블을 만들 필요가 있다.

해당 테이블 안에 데이터 값을 넣을 것이기 때문이다.

정착률은 보통 7일 단위로 집계하는 것이 일반적이므로 7일 단위로 테이블을 만들어야 한다.

이는 이전 시간이었던 지속률을 집계할 때  실행했던 과정과 비슷한데, 임시테이블을 with구문을 이용해 만들고, union all구문을 사용해 각 행을 결합하는 방식으로 사용한다.

다음 쿼리를 보도록 하겠다.

 

--1) 정착률 지표를 관리하는 마스터 테이블 만들기
with
repeat_interval as (
  select '07 day' as index_name, 1 as interval_begin_date, 7 as interval_end_date
  union all select '14 day' as index_name, 8 as interval_begin_date, 14 as interval_end_date
  union all select '21 day' as index_name, 15 as interval_begin_date, 21 as interval_end_date
  union all select '28 day' as index_name, 22 as interval_begin_date, 28 as interval_end_date
)
select * from repeat_interval
order by index_name;

 

 

 

위 쿼리결과에서도 알 수 있듯, 7일, 14일, 21일, 28일에 대한 정착률을 집계하기위한 사전작업을 진행하였다.

7일에 해당하는 내용은 1일(시작일)부터 7일(종료일)까지, 14일에 해당하는 내용은 8일(시작일)부터 15일(종료일) 이렇게 설정하였는데,

이는 시작일과 종료일을 지정함으로써, 해당 기간동안 사용자가 서비스를 이용했다면 1씩 카운팅되도록 하고

하지 않았다면 0으로 카운팅이 되지 않도록 하는 이후 작업의 밑바탕이 된다고 할 수 있다.

 

그렇다면 이제는 본격적으로 정착률 지표를 만들어보는 쿼리를 작성해보도록 하겠다.

 

---

2. 정착률 지표를 집계하는 쿼리 실습

정착률 지표를 집계하여야 하는데, 7일간격으로 정착률을 집계한다.

각 날짜별로 가입자들은 첫 7일동안 한 번이라도 접속했다면 1,

아니라면 0으로 카운팅되므로 사용자들이 해당 서비스를 이용했는지의 여부를 판별할 수 있다.

14일 정착률의 경우 가입일 이후 8일부터 14일 기간 동안 한 번이라도 접속했다면 1, 아니라면 0으로 카운팅하여 계산하게 되며, 21일(15일~21일)과 28일(22일~28일)의 경우도 마찬가지이다.

 

참고로, 일반적으로 정착률은 주차가 지날수록 낮아지는 경향이 있다.

즉 7일 정착률은 높다가 14일, 21일로 갈수록 점점 우하향하는 경향을 보인다는 것이다.

처음에는 접속을 많이 하다가 점점 그 빈도가 줄어들더니(물론 빈도가 줄어들어도 접속만 하면 정착률은 그대로 1이기는 하다), 특정 시점 이후부터는 접속을 하지 않아 정착률이 0으로 집계되는 케이스도 더러 있기 때문이다.

 

그렇다면 해당 정착률은 어떻게 구할 수 있는지 쿼리와 결과로 확인해 보도록 하겠다.

 

--2) 정착률 계산하기
with
-- 정착률 지표를 관리하는 마스터 테이블 만들기
repeat_interval as (
  select '07 day' as index_name, 1 as interval_begin_date, 7 as interval_end_date
  union all select '14 day' as index_name, 8 as interval_begin_date, 14 as interval_end_date
  union all select '21 day' as index_name, 15 as interval_begin_date, 21 as interval_end_date
  union all select '28 day' as index_name, 22 as interval_begin_date, 28 as interval_end_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,
   r.index_name,
   -- 지표의 대상 기간 시작일과 종료일 계산하기
   date_add(cast(u.register_date as date), interval r.interval_begin_date day) as index_begin_date,
   date_add(cast(u.register_date as date), interval r.interval_end_date day) as index_end_date
  from
   inlaid-lane-373607.sqlr202301.lec12_1 as u
   left outer join
   inlaid-lane-373607.sqlr202301.lec12_2 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함수1 -> 대상 기간의 종료일이 로그의 최신 날짜 이전인지 확인
   -- 2. case함수2 -> 지표의 대상 기간에 액션을 했다면 1, 하지 않았다면 0으로 표시
   -- 3. sum함수-> 사용자별로 대상 기간에 한 액션의 합계 구함
   -- 4. sign함수 -> 대상의 기간에 액션을 했는지 플래그로 나타냄, 
   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_idx_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;

 

 

현재 데이터가 부족하여 제대로된 결과가 나오지 않았지만,

실제 데이터셋, 혹은 케이스가 많은 데이터셋으로 해당 쿼리를 실행한다면 의미 있는 쿼리가 될 것이라고 생각한다.

 

위 결과창에서 10월 1일 가입자의 1주차 정착률은 약 66.6%를 보이고 있으나, 14일 정착률은 0.0인 모습이다. 즉, 1일부터 7일까지는 3명중 2명은 서비스를 이용했지만, 8일부터 15일동안 해당 서비스를 이용한 고객은 없다는 이야기이다.

또한, 가입일이 10월 1인 회원의 21일 정착률이 NULL로 보이는 것은, 해당 데이터의 로그데이터상의 날짜가 가입일자 21일 후의 날짜 보다 이전이기 때문이다.

지속률 설명당시 지표의 결과의 날짜가 로그데이터상의 날짜 이후라면 NULL로 표시되게끔 만들어준다고 설명한 바 있는데 이 원리가 여기에서도 적용된 것이다.

 

 

---

3.  지속률과 정착률의 추이 확인하기

이전까지는 정착률, 그리고 지속률을 따로 집계하였지만,

이번에는 지속률과 정착률의 추이를 함께 확인해보도록 하겠다.

해당 내용은 앞선 지속률 포스팅을 참고하고 오면 조금 더 이해가 쉬울 것으로 예상한다.

 

1일부터 7일까지는 지속률의 개념을 사용하여 얼마나 사용자가 가입 이후 해당 서비스를 어느 정도 이용했는지 확인해 볼 것이며,7일부터 28일까지는 앞서 진행했던 정착률의 개념을 사용하여 사용자가 가입 이후 해당 서비스를 이용했는지의 여부를 확인해 볼 것이다.

 

다음 코드와 결과를 확인해 보도록 하자.

 

--3) n일 지속률과 n일 정착률의 추이

--n일 지속률을 집계하기기
with
-- 지속률 지표를 관리하는 마스터 테이블을 정착률 형식으로 만들기
repeat_interval as (
  select '01 day' as index_name, 1 as interval_begin_date, 1 as interval_end_date
  union all select '02 day' as index_name, 2 as interval_begin_date, 2 as interval_end_date
  union all select '03 day' as index_name, 3 as interval_begin_date, 3 as interval_end_date
  union all select '04 day' as index_name, 4 as interval_begin_date, 4 as interval_end_date
  union all select '05 day' as index_name, 5 as interval_begin_date, 5 as interval_end_date
  union all select '06 day' as index_name, 6 as interval_begin_date, 6 as interval_end_date
  union all select '07 day' as index_name, 7 as interval_begin_date, 7 as interval_end_date
  union all select '07_01 day' as index_name, 1 as interval_begin_date, 7 as interval_end_date
  union all select '14 day' as index_name, 8 as interval_begin_date, 14 as interval_end_date
  union all select '21 day' as index_name, 15 as interval_begin_date, 21 as interval_end_date
  union all select '28 day' as index_name, 22 as interval_begin_date, 28 as interval_end_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,
   r.index_name,
   -- 지표의 대상 기간 시작일과 종료일 계산하기
   date_add(cast(u.register_date as date), interval r.interval_begin_date day) as index_begin_date,
   date_add(cast(u.register_date as date), interval r.interval_end_date day) as index_end_date
  from
   inlaid-lane-373607.sqlr202301.lec12_1 as u
   left outer join
   inlaid-lane-373607.sqlr202301.lec12_2 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함수1 -> 대상 기간의 종료일이 로그의 최신 날짜 이전인지 확인
   -- 2. case함수2 -> 지표의 대상 기간에 액션을 했다면 1, 하지 않았다면 0으로 표시
   -- 3. sum함수-> 사용자별로 대상 기간에 한 액션의 합계 구함
   -- 4. sign함수 -> 대상의 기간에 액션을 했는지 플래그로 나타냄, 
   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_idx_date
   group by user_id, register_date, index_name, index_begin_date, index_end_date
)
select 
 index_name,
 avg(100.0 * index_date_action) as repeat_rate
from
 user_action_flag
group by
 index_name
order by
 index_name;

 

위 결과를 보면 알 수 있듯, 1일부터 7일까지의 지속률, 그리고 7일부터 28일까지는 7일간격으로의 정착률을 확인할 수 있게 된다.

가입 다음날에는, 전체 중 8.3%의 유저만이 서비스를 이용했고, 가입 이후 일주일동안 한 번이라도 서비스를 이용한 사람은 전체의 25%정도로 볼 수 있다.

 

지속률과 정착률은 가입일 이후 N일 이후의 행동을 집계하는것이므로, 만일 N일이 경과하지 않은 시점이라면 해당 조사가 불가하다.

예를 들어 오늘이 2월 22일인데 7일, 14일, 21일 정착률을 구하고자 한다면 최소한 가입일자는 2월 22일 기준으로 21일 전인 2월 1일 이전이 되어야 한다는 뜻이다.(오늘의 로그데이터는 전량 쌓이지 않았다고 한다면, 2월 22일로부터 22일 전인 1월 31일 가입일 기준으로 하여야 함)

만일 그 이후 가입일자에 대한 정착률을 계산하려면 조금 더 기다려야 한다.

이는 이전시간에 설명한 지속률에도 같은 개념이 적용된다.

 

이렇게 정착률을 집계하는 쿼리까지 진행해 보았다.

쿼리가 많이 길어져서 머리가 조금 아플 수 있다.

지금까지 열심히 달려왔으니, 이제부터는 머리를 조금 식히는 주제를 마지막으로 진행해 보고자 한다.

앞서 보았던 지속률과 정착률이 실제로는 어떻게 활용되고 있는지 게임의 예시를 통해 알아보도록 하겠다.

 

 

---

3.  게임의 출석이벤트로 살펴보는 지속률과 정착률

 

지속률과 정착률은 E커머스 뿐만 아니라 게임에서도 많이 쓰이고 있다.

온라인게임, 혹은 콘솔게임을 처음 시작했을 때 '신규환영이벤트'로 누적접속일수에 따라 보상을 주는 이벤트를 종종 볼 수 있다.

이는 신규가입자들이 꾸준한 접속을 하게 만들어 게임을 계속 플레이할 수 있게 만드는 동기를 부여함으로써

단기적으로는 게임에 자주 접속하게끔 하여 지속률을 올리고, 장기적으로는 게임에 적응을 하고 메인 유저로서 플레이하는 정착률로 이동할 수 있게끔 만들어준다.

 

그렇다면 사례를 통해 어떻게 실제로 이벤트를 진행하고 있는지 살펴보겠다.

 

출처: 검은사막모바일 //지속률을 올리기 위한 이벤트이다.

위의 경우에는 7일간의 신규유저 이벤트로, 지속률을 올리기 위한 이벤트이다.

지속률을 올리기 위해서는 많은 유저가 매일 꾸준히 접속하여 해당 서비스를 이용하는 것이 중요한데

이것으로 인해 3일차와 4일차에는 황금 등급의 보상이 주어짐으로써 유저에게 꾸준한 접속의 중요성을 상기시킨다.

또한 마지막 7일차에는 더욱 업그레이드된 보상을 지급하여 접속이 습관화될 수 있게끔 만들어준 케이스이다.

 

또한, 지속률은 물론 정착률을 올리기 위한 케이스도 존재한다.

이 경우 출석 이벤트의 기간은 매우 장기적일 수 있는데 다음과 같은 사례가 있다.

출처: STOVE 에픽세븐

해당 게임은 신규 유저 출석이벤트로 무려 28일 동안 진행하고 있다.

위 사례에서 알 수 있듯 보통 1달 남짓 진행하는 출석이벤트라면 3일, 7일, 14일, 21일 그리고 특히 마지막날의 보상은 매우 화려한데,

처음에는 주기적인 접속을, 그리고 나중에는 게임에 재미를 붙이고 해당 게임에 완벽하게 정착할 수 있도록 만들어 결과적으로 유저의 수를 늘리려는 게임사의 마케팅 전략이라고 볼 수 있겠다.

즉, 지속률과 정착률을 모두 높이는 방향으로 타깃을 잡았다고 해석할 수 있을 것이다.

 

이렇게 게임산업에서도 지속률과 정착률의 개념을 활용하여 이벤트에 쓰는 경우가 있다는 것을 확인할 수 있었다.

 

---

오늘은 이렇게 정착률에 대해 살펴보고, 해당 지표를 통한 실제 예시를 적용시켜보기도 하였습니다.

지속률과 정착률이라는 지표는 생각보다 밀접하게 생활 속에서 이용되고 있다는 점을 알 수 있었던 것 같네요.

 

그럼 여기서 해당 포스팅은 마치도록 하겠습니다.

 

감사합니다.

 

* 본 내용은 데이터 분석을 위한 SQL레시피 12장을 참고하여 작성된 글입니다.