[12] 시계열에 따른 사용자의 개별적인 행동 분석하기 - 액션 간의 리드 타임, 카트 추가 후 구매까지 걸린 시간, 등록일로부터 경과한 일수 별 매출액 집계

목차

3. 데이터 가공을 위한 SQL
 3-1. 하나의 값 조작하기 (5강)
 3-2. 여러 개의 값에 대한 조작 (6강)
  3-3. 하나의 테이블에 대한 조작 (7강)
3-4. 여러 개의 테이블 조작하기 (8강)
4. 매출을 파악하기 위한 데이터 추출 
  4-1. 시계열 기반으로 데이터 집계하기 (9강)
4-2. 다면적인 축을 사용해 데이터 집약하기 (10강)
5. 사용자를 파악하기 위한 데이터 추출 
  5-1. 사용자 전체의 특징과 경향 찾기 (11강)
  5-2. 시계열에 따른 사용자 전체의 상태 변화 찾기 (12강)
  5-3. 시계열에 따른 사용자의 개별적인 행동 분석하기 (13강)
6. 웹사이트에서 행동을 파악하는 데이터 추출하기
  6-1. 사이트 전체의 특징/경향 찾기
  6-2. 사이트 내의 사용자 행동 파악하기
  6-3. 입력 양식 최적화하기
7. 데이터 활용의 정밀도를 높이는 분석 기술
  7-1. 데이터를 조합해서 새로운 데이터 만들기
  7-2. 이상값 검출하기
  7-3. 데이터 중복 검출하기
  7-4. 여러 개의 데이터셋 비교하기
8. 데이터를 무기로 삼기 위한 분석 기술
  8-1. 검색 기능 평가하기
  8-2. 데이터 마이닝
  8-3. 추천
  8-4. 점수 계산하기
9. 지식을 행동으로 옮기기
  9-1. 데이터 활용의 현장

---

※ Mysql 기준
 
5장. 사용자를 파악하기 위한 데이터 추출

13강. 시계열에 따른 사용자의 개별적인 행동 분석하기

 

사용자의 여러 액션이 어느정도의 '시간차'를 두고 발생하는지 집계

- 사용자가 최종 성과에 도달할 때까지 어느 정도의 검토 과정이 필요한지 알면, 캠페인 시행 시기 설정등에 도움을 줄 수 있다.

---

13-1. 사용자의 액션 간격 집계하기

날짜함수, LAG함수, 리드 타임

같은 레코드에 있는 두 개의 날짜로 계산할 경우

신청일과 숙박일의 리드타임 계산하기

WITH reservations AS(
SELECT 1 AS reservation_id, '2016-09-01' AS register_date, '2016-10-01' AS visit_date, 3 AS days
UNION ALL
SELECT 2 AS reservation_id, '2016-09-20' AS register_date, '2016-10-01' AS visit_date, 2 AS days
UNION ALL
SELECT 3 AS reservation_id, '2016-09-30' AS register_date, '2016-11-20' AS visit_date, 2 AS days
UNION ALL
SELECT 4 AS reservation_id, '2016-10-01' AS register_date, '2017-01-03' AS visit_date, 2 AS days
UNION ALL
SELECT 5 AS reservation_id, '2016-11-01' AS register_date, '2016-12-28' AS visit_date, 3 AS days)
SELECT * FROM reservations;

SELECT
reservation_id,
register_date,
visit_date,
DATEDIFF(visit_date,register_date) AS lead_time
FROM reservations;

 

mysql에는 TO_DATE 함수가 없고, 일반적인 날짜 형식이면 그냥 집어넣어도 됨, 만약 날짜로 인식 못하면 STR_TO_DATE(visit_date, '%Y-%m-%d')로 바꿀 수 있음

 

여러 테이블에 있는 여러 개의 날짜로 계산할 경우

각 단계에서의 리드 타임과 토탈 리드 타임 계산

WITH requests AS(
SELECT 'U001' AS user_id, '1' AS product_id, '2016-09-01' AS request_date
UNION ALL
SELECT 'U001' AS user_id, '2' AS product_id, '2016-09-01' AS request_date
UNION ALL
SELECT 'U002' AS user_id, '3' AS product_id, '2016-09-01' AS request_date
UNION ALL
SELECT 'U003' AS user_id, '4' AS product_id, '2016-09-01' AS request_date
UNION ALL
SELECT 'U004' AS user_id, '5' AS product_id, '2016-09-01' AS request_date)
,
estimates AS(
SELECT 'U001' AS user_id, '2' AS product_id, '2016-09-21' AS estimate_date
UNION ALL
SELECT 'U002' AS user_id, '3' AS product_id, '2016-10-15' AS estimate_date
UNION ALL
SELECT 'U003' AS user_id, '4' AS product_id, '2016-10-15' AS estimate_date
UNION ALL
SELECT 'U004' AS user_id, '5' AS product_id, '2016-12-01' AS estimate_date),
orders AS(
SELECT 'U001' AS user_id, '2' AS product_id, '2016-10-01' AS order_date
UNION ALL
SELECT 'U004' AS user_id, '5' AS product_id, '2016-12-05' AS order_date)

table requests

table estimates

table orders

SELECT
r.user_id,
r.product_id,
DATEDIFF(e.estimate_date, r.request_date) AS estimate_lead_time,
DATEDIFF(o.order_date, r.request_date) AS order_lead_time,
DATEDIFF(O.order_date, r.request_date) AS total_lead_time
FROM requests AS r
LEFT OUTER JOIN
estimates AS e
ON r.user_id = e.user_id
AND r.product_id = e.product_id
LEFT OUTER JOIN
orders AS o
ON r.user_id = o.user_id
AND r.product_id = o.product_id;

 

같은 테이블의 다른 레코드에 있는 날짜로 계산할 경우

이전 구매일로부터 일수 계산하기

• LAG 구문에서 LAG함수는 기본적으로 현재 행의 바로 이전 행을 보기 때문에 ROWS BETWEEN 부분은 없어도 무관
• 1 PRECEDING AND 1 PRECEDING 형태로 쓴 이유는, LAG 함수 특성 상 다음 행을 참조하는 윈도우 프레임 FOLLOWING과는 논리적으로 맞지 않기 때문에 쓰면 안되는데, 1 PRECEDING만 쓰면 실행이 안됨

WITH purchase_log AS(
SELECT 'U001' AS user_id, '1' AS product_id, '2016-09-01' AS purchase_date
UNION ALL
SELECT 'U001' AS user_id, '2' AS product_id, '2016-09-20' AS purchase_date
UNION ALL
SELECT 'U002' AS user_id, '3' AS product_id, '2016-09-30' AS purchase_date
UNION ALL
SELECT 'U001' AS user_id, '4' AS product_id, '2016-10-01' AS purchase_date
UNION ALL
SELECT 'U002' AS user_id, '5' AS product_id, '2016-11-01' AS purchase_date)
SELECT
user_id,
purchase_date,
DATEDIFF(purchase_date, 
LAG(purchase_date) OVER(
PARTITION BY user_id ORDER BY purchase_date 
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)) AS lead_time
FROM purchase_log;

 

→ 리드타임 집계 후에는 사용자의 데모그래픽 정보를 사용해 비교하면 좋다. 수도권보다 지방이 리드 타임이 짧다거나, 연령별로 구분 되는 등 다양한 경향이 나타날 것

---

13-2. 카트 추가 후에 구매했는지 파악하기

CASE 식, SUM/AVG 카트 탈락률

 

카트 추가 후 경과 시간에 따른 구매 수 추이를 확인해보기

table action_log

 

상품들이 카트에 추가된 시각과 구매된 시각 산출하기

• mysql에서는 products 1,2,3을 펼치는 unnest, lateral view, explode 등 다 안됨
• 이 방법은 정해진 수의 상품ID에만 사용 가능
• RECURSIVE NUMBER로 숫자 시퀀스를 생성하고,
• row_action_log의 products를 SUBSTRING_INDEX()으로 분할해서 새로운 행을 생성
• SUBSTRING_INDEX는 SUBSTRING과 다르게 구분자를 기준으로 문자열을 분할

WITH RECURSIVE Number AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM Number WHERE n < 3 -- 최대 3개의 상품 ID를 가정
),
row_action_log AS (
  SELECT
    al.dt,
    al.session,
    al.user_id,
    al.action,
    SUBSTRING_INDEX(SUBSTRING_INDEX(al.products, ',', n.n), ',', -1) AS product_id,
    al.stamp
  FROM action_log al
  JOIN Number n ON CHAR_LENGTH(al.products) - CHAR_LENGTH(REPLACE(al.products, ',', '')) >= n.n - 1
  WHERE al.products IS NOT NULL AND al.products != ''
)
SELECT * FROM row_action_log;

table row_action_log

 

stamp의 시간 단위 추출해서 action이 add_cart일 때와 purchase일 때의 차이 구하기

• mysql은 UNIX_TIMESTAMP()로 초 단위 시간 추출

-- row_action_log까지 동일
,
action_time_stats AS(
SELECT
user_id,
product_id,
MIN(CASE WHEN action = 'add_cart' THEN dt END) AS dt,
MIN(CASE WHEN action = 'add_cart' THEN stamp END) AS add_cart_time,
MIN(CASE WHEN action = 'purchase' THEN stamp END) AS purchase_time,
ROUND(MIN(CASE WHEN action = 'purchase' THEN unix_timestamp(stamp) END)
-MIN(CASE WHEN action = 'add_cart' THEN unix_timestamp(stamp) END),0) AS lead_time
FROM row_action_log
GROUP BY user_id, product_id)
SELECT
user_id,
product_id,
add_cart_time,
purchase_time,
lead_time
FROM action_time_stats;

 

마지막으로 카트 추가 후 N시간 이내에 구매된 상품 수와 구매율 집계하기

,
purchase_lead_time_flag AS(
SELECT
user_id,
product_id,
dt,
CASE WHEN lead_time <= 1 * 60 * 60 THEN 1 ELSE 0 END AS purchase_1_hour,
CASE WHEN lead_time <= 6 * 60 * 60 THEN 1 ELSE 0 END AS purchase_6_hours,
CASE WHEN lead_time <= 24 * 60 * 60 THEN 1 ELSE 0 END AS purchase_24_hours,
CASE WHEN lead_time <= 48 * 60 * 60 THEN 1 ELSE 0 END AS purchase_48_hours,
CASE WHEN lead_time IS NULL OR NOT (lead_time <= 48 * 60 * 60) THEN 1 ELSE 0 END AS not_purchase
FROM action_time_stats)
SELECT * FROM purchase_lead_time_flag;

 

일자 별로 집계하기

SELECT
dt,
COUNT(*) AS add_cart,
SUM(purchase_1_hour) AS purchase_1_hour,
AVG(purchase_1_hour) AS purchase_1_hour_rate,
SUM(purchase_6_hours) AS purchase_6_hour,
AVG(purchase_6_hours) AS purchase_6_hours_rate,
SUM(purchase_24_hours) AS purchase_24_hours,
AVG(purchase_24_hours) AS purchase_24_hours_rate,
SUM(purchase_48_hours) AS purchase_48_hours,
AVG(purchase_48_hours) AS purchase_48_hours_rate,
SUM(not_purchase) AS not_purchase,
AVG(not_purchase) AS not_purchase_rate
FROM purchase_lead_time_flag
GROUP BY dt;

---

13-3. 등록으로부터의 매출을 날짜별로 집계하기

CROSS JOIN, CASE 식, AVG 함수, ARPU, ARPPU, LTV

 

사용자 등록 수를 월 별로 집계하고, N일 경과 시점의 1인당 매출 금액 집계하기

- 지속률/정착율을 산출하는 쿼리와 유사하나, 0과 1의 액션 플래그가 아닌 '매출액'을 집계한다는 점이 차이점

 

사용자의 등록일로부터 경과한 일수별 매출 계산하기

1. index_intervals

구매 총액을 계산할 각 인덱스(30,45,60일)의 이름과 시작/끝 정의

WITH index_intervals AS(
SELECT '30 day sales amount' AS indexname, 0 AS interval_begin_date, 30 AS interval_end_date
UNION ALL
SELECT '45 day sales amount', 0, 45
UNION ALL
SELECT '60 day sales amount', 0, 60)
SELECT * FROM index_intervals;

index_intervals

2. mst_users_with_base_date

mst_users 테이블에서 user_id와 register_date를 불러오고, 기준 날짜로 지정

,
mst_users_with_base_date AS(
SELECT user_id,
register_date AS base_date -- 등록일을 기준일로 지정
FROM mst_users)
SELECT * FROM mst_users_with_base_date;

mst_users_with_base_date

action_log

3. purchsae_log_with_index_date

• mst_users_with_base_date와 action_log를 결합하고, 각각의 index_intervals와 크로스조인하여 가능한 모든 조합 생성
• action_log의 최신 날짜를 찾기 위해 lateset_date 사용, 여기에서 MAX() 윈도함수로 모든 행에 대한 최대 날짜 계산

-- mst_users_with_base_date까지 동일
,
purchase_log_with_index_date AS(
SELECT 
u.user_id,
u.base_date,
CAST(p.stamp AS date) AS action_date,
MAX(CAST(p.stamp AS date)) OVER() AS latest_date,
SUBSTRING(u.base_date, 1, 7) AS month,
i.index_name,
DATE_ADD(CAST(u.base_date AS date), INTERVAL i.interval_begin_date DAY) AS index_begin_date,
DATE_ADD(CAST(u.base_date AS date), INTERVAL i.interval_end_date DAY) AS index_end_date,
p.amount
FROM mst_users_with_base_date AS u
LEFT OUTER JOIN
action_log AS p
ON u.user_id = p.user_id
AND p.action = 'purchase'
CROSS JOIN index_intervals AS i)
SELECT * FROM purchase_log_with_index_date;

purchase_log_with_index_date

 

4. user_purchase_amount

• 사용자 별, 월 별, 인덱스 간격 별로 구매 금액의 합계를 계산
• 구매가 인덱스 기간 내에 발생한 경우에만 집계

-- purchase_log_with_index_date까지 동일
,
user_purchase_amount AS(
SELECT
user_id,
month,
index_name,
SUM(CASE WHEN index_end_date <= latest_date THEN 
CASE WHEN action_date BETWEEN index_begin_date AND index_end_date THEN amount ELSE 0 END END) 
AS index_date_amount -- end date가 최신 날짜 이전인지 확인하고, 기간 내 구매한 경우에 amount가 출력되도록 지정
FROM purchase_log_with_index_date
GROUP BY user_id, month, index_name, index_begin_date, index_end_date)
SELECT * from user_purchase_amount

5. 최종 SELECT문

• 월 별, 인덱스 이름 별로 다음을 계산
  1. users 해당 월에 등록한 사용자 수
  2. purchase_uu 대상 기간동안 구매한 고유 사용자 수
  3. total_amount 대상 기간동안의 총 구매 금액
  4. avg_amount 대상 기간동안의 평균 구매 금액

-- user_purchase_amount까지 동일
SELECT
  month,
  COUNT(user_id) AS users,
  index_name,
  COUNT(CASE WHEN index_date_amount > 0 THEN user_id END) AS purchase_uu,
  SUM(index_date_amount) AS total_amount,
  AVG(index_date_amount) AS avg_amount
FROM user_purchase_amount
GROUP BY month, index_name
ORDER BY month, index_name;

여기에서 추가로 

1. 분모에 '서비스의 사용자 수'를 넣으면, '1인당 평균 매출 금액(ARPU, Average Revenue Per User)'이 된다.
2. 분모에 '과금 사용자 수'를 넣으면 '과금 사용자 1인당 평균 매출 금액(ARPPU, Average Revenue Per Paid User)이 된다.
3. 과금 사용자의 경우, 프리미엄 모델에서 무과금 사용자와 과금 사용자를 구별할 필요가 있는 경우에 사용한다.

LTV(고객 생애 가치)

:고객 생애에 걸쳐 어느 정도로 이익에 기여하는가

- 고객 생애가치(CPA, Cost Per Acquisition)를 설정/관리할 때 중요한 지표

 

LTV (고객생애가치) = <연간 거래액> * <수익률> * <지속 연수(체류기간)>

---