반응형
11강. 사용자 전체의 특징과 경향 찾기
서비스를 제공하는 측에서 사용자와 관련된 정보로 알고 싶은 것
- 사용자의 속성 (나이, 성별, 주소지)
- 사용자의 행동 (구매한 상품, 사용한 기능, 사용하는 빈도)
일반적으로 서비스와 관련된 업무 데이터가 저장된 데이터베이스에 관심 상품 등록, 카트 추가, 구매, 댓글 등의 각각의 테이블이 있을 것이지만, 액션 로그 테이블을 따로 만들고 내부에 내용을 적으면 별도의 테이블 결합 없이 데이터를 다룰 수 있다.
1. 사용자의 액션 수 집계하기
사용자들이 특정 기간 동안 기능을 얼마나 사용하는지 집계
- 사용률 (usage_rate) = 특정 액션 UU / 전체 액션 UU
- 1명 당 액션 수 (count_per_user) = 사용자가 평균적으로 액션을 몇 번 사용했는지
💡 UU : Unique_Users 중복없이 집계된 사용자 수
-- 5장 p.189 액션 수와 비율을 계산하는 쿼리
with stats AS (
-- 로그 전체의 유니크 사용자 수 구하기 (세션 사용)
select count(distinct session) AS total_uu
from sqlrecipe.action_log
)
select l.action
-- 액션 UU
, count(distinct l.session) AS action_uu
-- 액션의 수
, count(1) AS action_count
-- 전체 UU
, s.total_uu
-- 사용률 : <액션 UU> / <전체 UU>
, 100.0 * count(distinct l.session) / s.total_uu AS usage_rate
-- 1인당 액션 수 : <액션 수> / <액션 UU>
, 1.0 * count(1) / count(distinct l.session) AS count_per_user
from sqlrecipe.action_log AS l
-- 로그 전체의 유니크 사용자 수를 모든 레코드에 결합하기
cross join stats AS s
group by l.action, s.total_uu;
- 로그인 사용자와 비로그인 사용자를 구분해서 집계하기
- 로그인하지 않아도 서비스 일부를 사용할 수 있는 사이트의 경우, 회원과 비회원을 따로 나누어 집계하는 것이 좋다. 이렇게 하면 서비스에 대한 충성도가 높은 사용자와 낮은 사용자가 어떤 경향을 보이는지 발견할 수 있다.
-- 5장 p.191 로그인 상태에 따라 액션 수 등을 따로 집계하는 쿼리 with action_log_with_status AS ( select session , user_id , action -- user_id가 NULL 또는 빈 문자가 아닌 경우 login으로 판정하기 , case when coalesce(user_id, '') <> '' then 'login' else 'guest' end AS login_status from sqlrecipe.action_log ) select coalesce(action, 'all') AS action , coalesce(login_status, 'all') AS login_status , count(distinct session) AS action_uu , count(1) AS action_count from action_log_with_status group by rollup(action, login_status); - 회원과 비회원을 구분해서 집계하기
- 한 번이라도 로그인했다면 회원으로 계산하고 싶은 경우
-- 5장 p.192 회원 상태를 판별하는 쿼리 with action_log_with_status AS ( select session , user_id , action -- 로그를 타임스탬프 순서로 나열하고, 한 번이라도 로그인한 사용자일 경우, -- 이후의 모든 로그 상태를 member로 설정 , case when coalesce(max(user_id) over(partition by session order by stamp rows between unbounded preceding and current row), '') <> '' then 'member' else 'none' end AS member_status , stamp from sqlrecipe.action_log ) select * from action_log_with_status;- 로그인하지 않은 상태일 경우 null을 빈 문자열로 변환하는 함수를 추가하였는데, 이로 인해 count(distinct user_id)의 결과에 1이 추가될 수 있다. 따라서 정확하게 추출하려면 사용자ID를 NULLIF를 사용하여 null로 지정하는 것이 좋다.
2. 연령별 구분 집계하기
시청률 분석에 많이 사용되는 연령별 구분 집계 방법
- 생일을 기반으로 리포트를 만드는 시점에 집계함.
- 사용자의 생일을 계산하는 쿼리
-- 5장 p.196 사용자의 생일을 계산하는 쿼리
with mst_users_with_int_birth_date AS (
select *
-- 특정 날짜의 정수 표현
, 20170101 AS int_specific_date
-- 문자열로 구성된 생년월일을 정수 표현으로 변환하기
, cast(replace(substr(birth_date, 1, 10), '-', '') AS int64) AS int_birth_date
from sqlrecipe.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;
- 연령별 구분의 사람 수를 계산하는 쿼리
-- 5장 p.198 연령별 구분의 사람 수를 계산하는 쿼리
with mst_users_with_int_birth_date AS (
select *
-- 오늘 날짜의 정수 표현
, cast(replace(substr(format_date('%Y%m%d', current_date), 1, 10), '-', '') AS int64) AS int_current_date
-- 문자열로 구성된 생년월일을 정수 표현으로 변환하기
, cast(replace(substr(birth_date, 1, 10), '-', '') AS int64) AS int_birth_date
from sqlrecipe.mst_users
)
, mst_users_with_age AS (
select *
-- 특정 날짜의 나이
, floor((int_current_date - int_birth_date) / 10000) AS age
from mst_users_with_int_birth_date
)
, 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 category
, count(1) AS user_count
from mst_users_with_category
group by category;
3. 연령별 구분의 특징 추출하기
서비스의 사용 형태가 사용자 속성에 따라 다르다는 것을 확인하면 상품 또는 기사를 사용자 속성에 맞게 추천할 수 있다. 그러면 상품을 더 구매하거나 기사를 더 클릭하게 만들 수 있다.
- 카테고리별 X 연령별 구분 그래프
- 연령별 구분 X 카테고리별 그래프
두 가지를 모두 확인해야 특징을 제대로 파악할 수 있다.
-- 5장 p.200 연령별 구분과 카테고리를 집계하는 쿼리
with mst_users_with_int_birth_date AS (
select *
-- 오늘 날짜의 정수 표현
, cast(replace(substr(format_date('%Y%m%d', current_date), 1, 10), '-', '') AS int64) AS int_current_date
-- 문자열로 구성된 생년월일을 정수 표현으로 변환하기
, cast(replace(substr(birth_date, 1, 10), '-', '') AS int64) AS int_birth_date
from sqlrecipe.mst_users
)
, mst_users_with_age AS (
select *
-- 특정 날짜의 나이
, floor((int_current_date - int_birth_date) / 10000) AS age
from mst_users_with_int_birth_date
)
, 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 p.category AS product_category
, u.category AS user_category
, count(*) AS purchase_count
from sqlrecipe.action_log AS p
join mst_users_with_category AS u
on p.user_id = u.user_id
where action = 'purchase'
group by p.category
, u.category
order by p.category
, u.category;
💡 ABC 분석과 구성비누계를 리포트에 추가하면, 리포트의 내용 전달성을 향상시킬 수 있다.
4. 사용자의 방문 빈도 집계하기
사용자가 일주일 혹은 한달동안 서비스를 얼마나 이용하는지
-- 5장 p.203 한 주에 며칠 사용되었는지를 집계하는 쿼리
with action_log_with_dt AS (
select *
, substr(stamp, 1, 10) AS dt
from sqlrecipe.action_log
)
, action_day_count_per_user AS (
select user_id
, count(distinct dt) AS action_day_count
from action_log_with_dt
where dt between '2016-11-01' and '2016-11-07'
group by user_id
)
select action_day_count
, count(distinct user_id) AS user_count
from action_day_count_per_user
group by action_day_count
order by action_day_count;
- 사용자별 day수를 카운트하는 CTE를 생성 후, day수별로 사용자 수를 카운트한다.
-- 5장 p.204 구성비와 구성비누계를 계산하는 쿼리
with action_log_with_dt AS (
select *
, substr(stamp, 1, 10) AS dt
from sqlrecipe.action_log
)
, action_day_count_per_user AS (
select user_id
, count(distinct dt) AS action_day_count
from action_log_with_dt
-- where dt between '2016-11-01' and '2016-11-07'
group by user_id
)
select action_day_count
, count(distinct user_id) AS user_count
-- 구성비
, 100.0 * count(distinct user_id) / sum(count(distinct user_id)) over() AS composition_ratio
-- 구성비누계
, 100.0 * sum(count(distinct user_id)) over(order by action_day_count rows between unbounded preceding and current row)
/ sum(count(distinct user_id)) over() AS cumulative_ratio
from action_day_count_per_user
group by action_day_count
order by action_day_count;
5. 벤 다이어그램으로 사용자 액션 집계하기
여러 기능의 사용 상황을 조사한 뒤 제공하는 기능을 사용자가 받아들이는지, 예상대로 사용하는지 등을 확인할 수 있다.
-- 5장 p.206 사용자들의 액션 플래그를 집계하는 쿼리
with user_action_flag AS (
-- 사용자가 액션을 했으면 1, 안 했으면 0으로 플래그
select user_id
, sign(sum(case when action = 'puchase' 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 sqlrecipe.action_log
group by user_id
)
select *
from user_action_flag
-- 5장 p.211 모든 액션 조합에 대한 사용자 수 계산하기
with user_action_flag AS (
-- 사용자가 액션을 했으면 1, 안 했으면 0으로 플래그
select user_id
, sign(sum(case when action = 'puchase' 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 sqlrecipe.action_log
group by user_id
)
, action_venn_diagram AS (
select mod_has_purchase AS has_purchase
, mod_has_review AS has_review
, mod_has_favorite AS has_favorite
, count(1) AS users
from user_action_flag
-- 각 컬럼에 NULL을 포함하는 레코드를 유사적으로 추가하기
-- Bigquery : CROSS JOIN, unnest 함수 사용하기
cross join unnest(array[has_purchase, NULL]) AS mod_has_purchase
cross join unnest(array[has_review, NULL]) AS mod_has_review
cross join unnest(array[has_favorite, NULL]) AS mod_has_favorite
group by mod_has_purchase
, mod_has_review
, mod_has_favorite
)
select *
from action_venn_diagram
order by has_purchase
, has_review
, has_favorite;
-- 5장 p.212 벤 다이어그램을 만들기 위해 데이터를 가공하는 쿼리
with user_action_flag AS (
-- 사용자가 액션을 했으면 1, 안 했으면 0으로 플래그
select user_id
, sign(sum(case when action = 'puchase' 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 sqlrecipe.action_log
group by user_id
)
, action_venn_diagram AS (
select mod_has_purchase AS has_purchase
, mod_has_review AS has_review
, mod_has_favorite AS has_favorite
, count(1) AS users
from user_action_flag
-- 각 컬럼에 NULL을 포함하는 레코드를 유사적으로 추가하기
-- Bigquery : CROSS JOIN, unnest 함수 사용하기
cross join unnest(array[has_purchase, NULL]) AS mod_has_purchase
cross join unnest(array[has_review, NULL]) AS mod_has_review
cross join unnest(array[has_favorite, NULL]) AS mod_has_favorite
group by mod_has_purchase
, mod_has_review
, mod_has_favorite
)
select
-- 0,1 vmfformfmf answkduffh rkrhdgkrl
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인 사용자 수가 전체 사용자 수를 나타내므로
-- 해당 레코드의 사용자 수를 window 함수로 구하기
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;
- 확장해서 SNS 등의 사이트에도 활용할 수 있다.
- 글을 작성하지 않고 다른 사람의 글만 확인하는 사용자
- 글을 많이 작성하는 사용자
- 글을 거의 작성하지 않지만 댓글은 많이 작성하는 사용자
- 글과 댓글 모두 적극적으로 작성하는 사용자
6. Decile 분석을 사용해 사용자를 10단계 그룹으로 나누기
데이터를 10단계로 분할해서 중요도를 파악하는 ‘Decile 분석’ 방법
- 사용자를 구매 금액이 많은 순으로 정렬
- 정렬된 사용자 상위부터 10%씩 그룹을 할당
- 각 그룹의 구매 금액 합계 집계
- 전체 구매 금액에 대햇 각 그룹별 구매 금액 구성비 계산
- 상위에서 누적으로 어느 정도 비율 차지하는지 구성비누계 집
-- 5장 p.216 구매액이 많은 순서로 사용자 그룹을 10등분
with user_purchase_amount AS (
select user_id
, sum(amount) AS purchase_amount
from sqlrecipe.action_log
where action = 'purchase'
group by user_id
)
, 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;
-- 5장 p.219 구매액이 많은 Decile 순서로 구성비와 구성비누계를 계산하는 쿼리
with user_purchase_amount AS (
select user_id
, sum(amount) AS purchase_amount
from sqlrecipe.action_log
where action = 'purchase'
group by user_id
)
, users_with_decile AS (
select user_id
, purchase_amount
, ntile(10) over (order by purchase_amount desc) AS decile
from user_purchase_amount
)
, 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 decile
, amount
, avg_amount
, 100.0 * amount / total_amount AS total_ratio
, 100.0 * cumulative_amount / total_amount AS cumulative_ratio
from decile_with_purchase_amount;
- 그룹을 세분화하여 사용자 속성을 자세하게 파악할 수 있다.
- Decile 7~10은 정착되지 않은 고객
- 메일 매거진 등으로 리텐션을 높이는 등의 대책을 세울 수 있음
- Decile 7~10은 정착되지 않은 고객
7. RFM 분석으로 사용자를 3가지 관점의 그룹으로 나누기
- Recency : 최근 구매일
- Frequency : 구매 횟수
- Monetary : 구매 금액 합계
-- 5장 p.221 사용자별로 RFM을 집계하는 쿼리
with purchase_log AS (
select user_id
, amount
, substr(stamp, 1, 10) AS dt
from sqlrecipe.action_log
where action = 'purchase'
)
, user_rfm AS (
select user_id
, max(dt) AS recent_date
, date_diff(current_date, date(timestamp(max(dt))), day) AS recency
, count(dt) AS frequency
, sum(amount) AS monetary
from purchase_log
group by user_id
)
select *
from user_rfm;
-- 5장 p.223 사용자들의 RFM 랭크를 계산하는 쿼리
with purchase_log AS (
select user_id
, amount
, substr(stamp, 1, 10) AS dt
from sqlrecipe.action_log
where action = 'purchase'
)
, user_rfm AS (
select user_id
, max(dt) AS recent_date
, date_diff(current_date, date(timestamp(max(dt))), day) AS recency
, count(dt) AS frequency
, sum(amount) AS monetary
from purchase_log
group by user_id
)
, user_rfm_rank AS (
select user_id
, recent_date
, recency
, frequency
, monetary
, case when recency < 14 then 5
when recency < 28 then 4
when recency < 60 then 3
when recency < 90 then 2
else 1
end AS r
, case when 20 <= frequency then 5
when 10 <= frequency then 4
when 5 <= frequency then 3
when 2 <= frequency then 2
when 1 <= frequency then 1
end AS f
, case when 300000 <= monetary then 5
when 100000 <= monetary then 4
when 30000 <= monetary then 3
when 5000 <= monetary then 2
else 1
end AS m
from user_rfm
)
select *
from user_rfm_rank;
-- 5장 p.225 각 그룹의 사람 수를 확인하는 쿼리
with purchase_log AS (
select user_id
, amount
, substr(stamp, 1, 10) AS dt
from sqlrecipe.action_log
where action = 'purchase'
)
, user_rfm AS (
select user_id
, max(dt) AS recent_date
, date_diff(current_date, date(timestamp(max(dt))), day) AS recency
, count(dt) AS frequency
, sum(amount) AS monetary
from purchase_log
group by user_id
)
, user_rfm_rank AS (
select user_id
, recent_date
, recency
, frequency
, monetary
, case when recency < 14 then 5
when recency < 28 then 4
when recency < 60 then 3
when recency < 90 then 2
else 1
end AS r
, case when 20 <= frequency then 5
when 10 <= frequency then 4
when 5 <= frequency then 3
when 2 <= frequency then 2
when 1 <= frequency then 1
end AS f
, case when 300000 <= monetary then 5
when 100000 <= monetary then 4
when 30000 <= monetary then 3
when 5000 <= monetary then 2
else 1
end AS m
from user_rfm
)
, mst_rfm_index AS (
select 1 AS rfm_index
union all select 2 AS rfm_index
union all select 3 AS rfm_index
union all select 4 AS rfm_index
union all select 5 AS rfm_index
)
, rfm_flag AS (
select m.rfm_index
, case when m.rfm_index = r.r then 1 else 0 end AS r_flag
, case when m.rfm_index = r.f then 1 else 0 end AS f_flag
, case when m.rfm_index = r.m then 1 else 0 end AS m_flag
from mst_rfm_index AS m
cross join user_rfm_rank AS r
)
select rfm_index
, sum(r_flag) AS r
, sum(f_flag) AS f
, sum(m_flag) AS m
from rfm_flag
group by rfm_index
order by rfm_index desc;
- 1차원으로 사용자 구분 하기
- 총 125개의 그룹이 발생 → 각 점수의 합으로 13개의 그룹화
-- 5장 p.229 종합 랭크별로 사용자 수를 집계하는 쿼리
with purchase_log AS (
select user_id
, amount
, substr(stamp, 1, 10) AS dt
from sqlrecipe.action_log
where action = 'purchase'
)
, user_rfm AS (
select user_id
, max(dt) AS recent_date
, date_diff(current_date, date(timestamp(max(dt))), day) AS recency
, count(dt) AS frequency
, sum(amount) AS monetary
from purchase_log
group by user_id
)
, user_rfm_rank AS (
select user_id
, recent_date
, recency
, frequency
, monetary
, case when recency < 14 then 5
when recency < 28 then 4
when recency < 60 then 3
when recency < 90 then 2
else 1
end AS r
, case when 20 <= frequency then 5
when 10 <= frequency then 4
when 5 <= frequency then 3
when 2 <= frequency then 2
when 1 <= frequency then 1
end AS f
, case when 300000 <= monetary then 5
when 100000 <= monetary then 4
when 30000 <= monetary then 3
when 5000 <= monetary then 2
else 1
end AS m
from user_rfm
)
select r+f+m AS total_rank
, count(user_id)
from user_rfm_rank
group by total_rank
order by total_rank desc;
- 2차원으로 사용자 인식하기
- R과 F를 사용해 2차원 사용자 층의 사용자 수를 집계하여 마케팅 대책 세우기
-- 5장 p.230 R과 F를 사용해 2차원 사용자 층의 사용자 수를 집계하는 쿼리
with purchase_log AS (
select user_id
, amount
, substr(stamp, 1, 10) AS dt
from sqlrecipe.action_log
where action = 'purchase'
)
, user_rfm AS (
select user_id
, max(dt) AS recent_date
, date_diff(current_date, date(timestamp(max(dt))), day) AS recency
, count(dt) AS frequency
, sum(amount) AS monetary
from purchase_log
group by user_id
)
, user_rfm_rank AS (
select user_id
, recent_date
, recency
, frequency
, monetary
, case when recency < 14 then 5
when recency < 28 then 4
when recency < 60 then 3
when recency < 90 then 2
else 1
end AS r
, case when 20 <= frequency then 5
when 10 <= frequency then 4
when 5 <= frequency then 3
when 2 <= frequency then 2
when 1 <= frequency then 1
end AS f
, case when 300000 <= monetary then 5
when 100000 <= monetary then 4
when 30000 <= monetary then 3
when 5000 <= monetary then 2
else 1
end AS m
from user_rfm
)
select concat('r_', cast(r AS string)) AS r_rank
, count(case when f=5 then 1 end) AS f_5
, count(case when f=4 then 1 end) AS f_4
, count(case when f=3 then 1 end) AS f_3
, count(case when f=2 then 1 end) AS f_2
, count(case when f=1 then 1 end) AS f_1
from user_rfm_rank
group by r
order by r_rank desc;
- 사용자 속성 정의를 통해 서비스 개선 검토, 사용자에 따른 메일 최적화 등 다양한 용도로 활용 가능하다.
반응형
'Data Analysis > SQL Recipe for Data Analysis' 카테고리의 다른 글
| [SQL 레시피] 다면적인 축을 사용해 데이터 집약하기 (1) | 2023.11.14 |
|---|---|
| [SQL 레시피] 시계열 기반으로 데이터 집계하기 (0) | 2023.11.14 |
| [SQL 레시피] 하나의 테이블에 대한 조작 (0) | 2023.11.13 |
| [SQL 레시피] 여러 개의 값에 대한 조작 (0) | 2023.11.13 |
| [SQL 레시피] 하나의 값 조작하기 (0) | 2023.11.13 |
