인프런 커뮤니티 질문&답변

유저 상태

new user

# User Status 정의
-- "우리 서비스를 이용하는 유저들의 주차별 상태가 어떻게 되나?"

/*
1. New User: 해당 주차에 처음 방문한 유저
2. Current User: 지난주에도 방문했고 이번 주에도 방문한 유저  
3. Resurrected User: 지난주에는 없었는데 이번주에 다시 방문한 유저 (첫 방문은 아님)
4. Dormant User: 지난주까지는 있었는데 이번주에 방문하지 않은 유저
*/

# New User: 해당 주차에 처음 방문한 유저

-- '유저의 첫 방문 주'와 '유저별 이벤트 발생주'가 같은지 확인
-- 같으면, 1
-- 아니면 0

## '유저의 첫 방문 주'

with user_first_visit as (
select
  user_pseudo_id,
  date_trunc(min(event_date), week(monday)) as first_visit_date
from `advanced.app_logs`
where
 event_date between '2022-08-01' and '2022-12-31'
group by
  1
)


## '유저의 모든 방문 주'
, user_event_week as (
select
  distinct
    user_pseudo_id,
    date_trunc(event_date, week(monday)) as visit_date
from `advanced.app_logs`
where
 event_date between '2022-08-01' and '2022-12-31'
)


## 첫 방문 주와 모든 방문 주 중 같은 데이터가 있다면 '방문(=1)'
, new_user_table as (
select
  ufv.user_pseudo_id,
  ufv.first_visit_date,
  uew.visit_date,
  if(ufv.first_visit_date = uew.visit_date, 1, 0) as new_user
from user_first_visit as ufv
inner join user_event_week as uew
on ufv.user_pseudo_id = uew.user_pseudo_id
group by
  1,2,3
)

select
  first_visit_date as week,
  sum(new_user) as new_user_count
from new_user_table
group by
  1
order by
  1

current user

# User Status 정의
-- "우리 서비스를 이용하는 유저들의 주차별 상태가 어떻게 되나?"

/*
1. New User: 해당 주차에 처음 방문한 유저
2. Current User: 지난주에도 방문했고 이번 주에도 방문한 유저  
3. Resurrected User: 지난주에는 없었는데 이번주에 다시 방문한 유저 (첫 방문은 아님)
4. Dormant User: 지난주까지는 있었는데 이번주에 방문하지 않은 유저
*/

-- Current User: 지난주에도 방문했고 이번 주에도 방문한 유저


-- 유저별 모든 방문 주 구하기

-- 모든 방문주 - 전주 방문 데이터 = 1 -->  current user

## 유저별 모든 방문 주 구하기

with user_visits as (
select
  user_pseudo_id,
  date_trunc((event_date), week(monday)) as visit_date
from `advanced.app_logs`
where
 event_date between '2022-08-01' and '2022-12-31'
group by
  1,2
)

## 유저의 전주 방문 데이터

, prev_user_table  as (
select
  user_pseudo_id,
  visit_date,
  lag(visit_date) over(partition by user_pseudo_id order by visit_date) as previous_visit_user
from user_visits
)


## 모든 방문주 - 전주 방문 데이터 = 1 -->  current user

select
  visit_date as week,
  count(distinct user_pseudo_id) as current_user_cnt
from(
select
  distinct
    user_pseudo_id,
    visit_date,
    date_diff(visit_date, previous_visit_user, week) = 1 as current_user
from prev_user_table
) as base
where
  current_user is not null
group by
  1
order by
  1

resurrected user

# User Status 정의
-- "우리 서비스를 이용하는 유저들의 주차별 상태가 어떻게 되나?"

/*
1. New User: 해당 주차에 처음 방문한 유저
2. Current User: 지난주에도 방문했고 이번 주에도 방문한 유저  
3. Resurrected User: 지난주에는 없었는데 이번주에 다시 방문한 유저 (첫 방문은 아님)
4. Dormant User: 지난주까지는 있었는데 이번주에 방문하지 않은 유저
*/


# Resurrected User: 지난주에는 없었는데 이번주에 다시 방문한 유저 (첫 방문은 아님)


## 유저별 모든 방문 주 구하기
with user_visits as (
select
  user_pseudo_id,
  date_trunc((event_date), week(monday)) as visit_date
from `advanced.app_logs`
where
 event_date between '2022-08-01' and '2022-12-31'
group by
  1,2
)


## 유저의 전주 방문 데이터
, prev_user_table  as (
select
  user_pseudo_id,
  visit_date,
  lag(visit_date) over(partition by user_pseudo_id order by visit_date) as previous_visit_user
from user_visits
)


## (첫 방문이 아님) + (모든 방문 주 데이터 - 이전 방문주 > 1) =  Resurrected User
-- 첫 방문이 아닌 유저 : 'previous_visit_user is not null'
    -- 첫 방문이었으면 previous_visit_user가 null이었을테니깐.
select
  visit_date as week,
  count(user_status) as Resurrected_User
from (
select
  user_pseudo_id,
  visit_date,
  previous_visit_user,
  case
    when previous_visit_user is not null and  date_diff(visit_date, previous_visit_user, week) > 1 then 'Resurrected_User'
    else null
    end as user_status
from prev_user_table
) as Resurrected_User
where
  user_status is not null
group by
  1
order by
  1

dormant user

# User Status 정의
-- "우리 서비스를 이용하는 유저들의 주차별 상태가 어떻게 되나?"

/*
1. New User: 해당 주차에 처음 방문한 유저
2. Current User: 지난주에도 방문했고 이번 주에도 방문한 유저  
3. Resurrected User: 지난주에는 없었는데 이번주에 다시 방문한 유저 (첫 방문은 아님)
4. Dormant User: 지난주까지는 있었는데 이번주에 방문하지 않은 유저
*/


# Dormant User: 지난주까지는 있었는데 이번주에 방문하지 않은 유저


## 모든 방문 데이터

with base as (
select
  user_pseudo_id,
  date_trunc((event_date), week(monday)) as visit_date
from `advanced.app_logs`
where
 event_date between '2022-08-01' and '2022-12-31'
group by
  1,2
)

## 유저의 다음 방문주 데이터=
, next_visit_date_table as (
select
  *,
  lead(visit_date) over(partition by user_pseudo_id order by visit_date) as next_visit_date
from base
)


## Dormant User
-- Dormant User?
  -- 다음주 방문 데이터가 null 이거나,
  -- next_visit_date > 1 week가 아닌 유저

, Dormant_User_table as (
select
  *,
  case
    when next_visit_date is null
    or date_diff(next_visit_date, visit_date, week) > 1 then 'Dormant_User'
    else null
    end as Dormant_User_data
from next_visit_date_table
)


select
  visit_date as week,
  count(Dormant_User_data) as Dormant_User_data_cnt
from Dormant_User_table
group by
  1
order by
  1