LTV, 로그 데이터

LTV(Lifetime Value)

고객 생애 가치로 유저가 서비스를 사용하는 기간동안 창출하는 매출
EX) 신규고객 획득비용(CAC) 기준 설정
효율적인 마케팅 채널을 파악해 예산 분배

LTV = ARPU * 리텐션
ARPU = 유저당 주문금액(매출/유저수)
그룹을 코호트로 쪼개서 LTV 비교 분석

실습 코드

WITH 
ord as(
	SELECT a.mem_no,
			b.age_range,
			b.first_ord_dt,
			a.ord_dt,
			a.order_amount
	FROM order_master_ltv a
	LEFT JOIN first_ord_table_ltv b ON a.mem_no = b.mem_no
)
,cohort as(
	SELECT age_range,
			CASE WHEN first_ord_dt = ord_dt THEN 'M-0'
				WHEN first_ord_dt < ord_dt AND date(ord_dt) <= date_add(first_ord_dt, INTERVAL 1 month) THEN 'M-1'
				WHEN date(ord_dt) > date_add(first_ord_dt, INTERVAL 1 month) AND date(ord_dt) <= date_add(first_ord_dt, INTERVAL 2 month) THEN 'M-2'
				WHEN date(ord_dt) > date_add(first_ord_dt, INTERVAL 2 month) AND date(ord_dt) <= date_add(first_ord_dt, INTERVAL 3 month) THEN 'M-3'
				WHEN date(ord_dt) > date_add(first_ord_dt, INTERVAL 3 month) AND date(ord_dt) <= date_add(first_ord_dt, INTERVAL 4 month) THEN 'M-4'
				WHEN date(ord_dt) > date_add(first_ord_dt, INTERVAL 4 month) AND date(ord_dt) <= date_add(first_ord_dt, INTERVAL 5 month) THEN 'M-5'
				ELSE 'over-5m' END month_nm,
			count(DISTINCT mem_no) sample_cnt,
			round(avg(order_amount)) ARPU
	FROM ord
	GROUP BY 1, 2
	ORDER BY 1, 2
)
SELECT age_range,
		month_nm,
		sample_cnt,
		ARPU,
		sample_cnt * 1.00 / max(sample_cnt) over(PARTITION BY age_range) retention
FROM cohort
ORDER BY 1, 2

연령대	10대	20대	30대	40대	50대
인당 LTV	55,882원	83,332원	111,645원	94,328원	96,027원

결론

30대가 첫 주문 이후 5개월까지 가장 높은 LTV를 나타냄

로그데이터 분석

로그데이터: 고객이 서비스에 유입되어 남기는 행동 데이터
유입, 페이지 이동, 노출, 클릭 등 데이터가 이벤트 단위로 적재

분석 주제: 어떤 배너를 통해 유입된 고객의 주문 전환율이 더 높을까

실습 코드

WITH 
log AS
	(SELECT DISTINCT mem_no,
			session_id,
			log_dt,
			cast(log_stamp AS datetime) log_stamp,
			min(referrer) OVER(PARTITION BY mem_no ORDER BY log_stamp) bnr_type
	FROM log_table
),
ord AS
	(SELECT DISTINCT log.mem_no,
			log_dt,
			log_stamp,
			bnr_type,
			cast(ord_stamp AS datetime) ord_stamp,
			count(DISTINCT ord_no) ord_cnt
	FROM log 
	LEFT JOIN order_master_log oml 
	ON log.mem_no = oml.mem_no 
	AND log_dt = date(ord_stamp)
	AND log_stamp < CAST(ord_stamp AS datetime)
-- 	AND log.session_id = oml.session_id 
	GROUP BY 1, 2, 3, 4, 5
),
ord2 AS 
	(SELECT DISTINCT mem_no,
			bnr_type,
			CASE WHEN ord_cnt>0 THEN 1 ELSE 0 END is_order
	FROM ord
)

SELECT bnr_type, sum(is_order)
FROM ord2
GROUP BY 1
ORDER BY 1;

결론

배너 1에 비해 배너 2로 유입되어 주문한 사람이 4배 차이로 차이가 있었지만
작은 데이터 이기 때문에 의미는 거의 없을 것 같다.