[PostgreSQL] 1. 사용자의 액션 수 집계

사용자 데이터

서비스를 제공하는 측에서 사용자와 관련된 정보로 알고 싶은 것을 정리해보면 다음과 같다.

• 사용자의 속성(나이, 성별, 주소지 등)
• 사용자의 행동(구매한 상품, 사용한 기능, 사용하는 빈도)

👉 어떤 속성의 사용자가 사용중인가?와 어떻게 사용하는가?를 파악하지 않고 서비스 개선을 검토할 수는 없다!

 

사용자 마스터 테이블

일반적으로 이커머스 사이트는 가입한 뒤, 로그인을 하고 사용한다.

따라서 사용자 정보를 저장하는 '사용자 마스터 테이블'이 존재한다. 예시는 다음과 같다.

사용자 데이터

 

액션 로그 테이블

• 일반적으로 서비스와 관련된 업무 데이터가 저장된 데이터베이스에 관심 상품 등록, 카트 추가, 구매, 댓글 등 각각의 테이블이 있을 것이다.
• 그래도 다음과 같이 액션 로그 테이블을 따로 만들어 내부에 별도 내용을 적으면 JOIN이나 UNION 없이 데이터를 다룰 수 있다.

액션 로그 데이터

 

---

사용자의 액션 수 집계

• 사용자가 서비스 내부에서 제공되는 기능을 얼마나 이용하는지 집계하는 작업은 사용자 행동 패턴 파악할 때와 어떤 액션의 효과를 확인할 때 매우 중요하다.
• 사용률(usage_rate)과 1명 당 액션 수(count_per_user)를 각각 구해보자.

1. 전체 사용자 수를 먼저 구한다.
2. 전체 사용자수를 액션 로그 데이터와 CROSS JOIN 한다.
3. 액션 별로 유니크한 사용자 수와 액션 수를 카운트 한다.
4. 액션 uu와 액션 수를 활용하여 사용률과 1인당 액션 수를 계산한다.

WITH stats AS (
	-- 로그 전체에서 유니크한 사용자 수(unique users) 
	SELECT count(DISTINCT session) AS total_uu
	FROM action_log 
)
SELECT l.ACTION
	, count(DISTINCT l."session") AS action_uu -- 액션 별 유니크 사용자 수(uu) 
	, count(1) AS action_count -- 액션의 수 
	, s.total_uu --전체 uu 
	, 100.0 * count(DISTINCT l.session) / s.total_uu AS usage_rate -- 사용률:액션uu / 전체uu 
	, 1.0 * count(1) / count(DISTINCT l.session) AS count_per_user -- 1인당 액션 수 : 액션수 / 액션 uu
FROM action_log AS l
CROSS JOIN stats AS s -- 로그 전체의 유니크 사용자 수를 모든 레코드에 결합 
GROUP BY l.ACTION, s.total_uu
;

 

 

---

로그인 사용자와 비로그인 사용자를 구분하여 집계

• 로그인, 비로그인, 회원, 비회원을 판별할 때는 로그 데이터에 session 정보가 이써야 한다.
• 여기서는 사용자 ID값이 빈 레코드를 비로그인 사용자로 가정하고, session을 사용해 그 수를 집계한다

▶︎ user_id가 NULL이면 '' 을 반환, 값이 있으면 그대로 user_id를 반환

▶︎ user_id가 반환되면, 즉, 빈값이 아니면 login을 반환하고 빈 값이면 guest 반환

WITH 
ACTION_log_with_status AS (
	SELECT SESSION
		, user_id
		, ACTION
		-- user_id가 null이면 로그인으로 판정
		, CASE WHEN COALESCE(user_id, '') <> '' THEN 'login' ELSE 'guset' END AS login_status
	FROM action_log
)
SELECT *
FROM ACTION_log_with_status
;

 

• 이제 login_status를 기반으로 액션수와 UU를 집계해본다.
• 추가로 로그인/비로그인을 따지지 않고 전체(all)도 함께 집계해 살펴본다. 👉 ROLLUP 구문 사용

WITH 
ACTION_log_with_status AS (
	SELECT SESSION
		, user_id
		, ACTION
		-- user_id가 null이면 로그인으로 판정
		, CASE WHEN COALESCE(user_id, '') != '' THEN 'login' ELSE 'guset' END AS login_status
	FROM 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)
;

샘플 데이터는 참고만..!

 

---

회원과 비회원을 구분하여 집계

• 로그인을 하지 않았더라도, 이전에 한 번이라도 로그인했다면 회원으로 계산하고 싶을 수도 있다.
• 이를 위해 로그 데이터를 가공한다. 다음과 같이 회원 상태를 추가해보자.

1. 로그를 타임스탬프 순서로 나열하고, 세션 내에서 한번이라도 로그인한 사용자인지 확인한다.
2. 해당 session에서 한 번이라도 로그인했다면 → 즉, user_id의 max 값이 빈값이 아니라면 Member를 반환하고 그렇지 않은 경우(로그인 하지 않음) none을 반환하게 한다.

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 action_log 
)
SELECT *
FROM action_log_with_status
;

---

🎯 포인트

• 로그인하지 않은 상태일 경우 user_id 컬럼의 값이 비어있다고 판단해서 coalesce 함수를 사용해 빈 문자열로 변환했다.
• 그런데 이런 경우 count(distinct user_id)를 하면 로그인하지 않은 사용자도 1로 추가가 된다.
• 따라서 count(distinct)로 사용자 수를 정확히 추출하고자 한다면, 사용자 ID를 NULL로 지정하는 것이 좋다.
• COALESCE 함수와 NULLIF 함수 사용법과 변환법을 잘 파악해두자.