목표 까지의 고객 여정을 단계별로 나눠 단계별 전환,이탈을 측정하는 분석. (깔때기의 형태로 단계별로 나누어 분석함)
퍼널분석 적용해보기
실습
SELECT page, COUNT(user_id) AS user_count, COUNT(DISTINCT user_id) AS check_user_count
FROM 고객 데이터
GROUP BY page
ORDER BY page
전환율 구하기 = 현재단계 / 전단계 %
시간 흐름에 따라 growth와 retention을 측정하는 방법
고객을 segementation해서 그룹별로 시간에 따라 지표의 변화를 측정함
TIME 코호트
코호트 실습
최초 구매일자 구하기
SELECT customer_id, DATE(MIN(invoice_date)) AS cohort_day
FROM 구매 데이터
GROUP BY customer_id
최초 구매일 이후 구매 일 month로 차이 계산 (cohort_group으로 구매 월 확인) → 차이를 cohort_index로 설정
WITH first_purchase AS (
SELECT customer_id, DATE(MIN(invoice_date)) AS cohort_day
FROM `taling-excercise.data_pre.sales`
GROUP BY customer_id
)
SELECT s.*, f.cohort_day, DATE_DIFF(DATE(invoice_date), cohort_day, MONTH) AS cohort_index, DATE_TRUNC(cohort_day, month) AS cohort_group
FROM `taling-excercise.data_pre.sales` s LEFT JOIN first_purchase f
ON s.customer_id = f.customer_id
cohort_index 별로 고객 수 확인 (중복은 삭제)
WITH first_purchase AS (
SELECT customer_id, DATE(MIN(invoice_date)) AS cohort_day
FROM `taling-excercise.data_pre.sales`
GROUP BY customer_id
)
SELECT cohort_group, cohort_index, COUNT(DISTINCT customer_id) as customer_count
FROM (
SELECT s.*, f.cohort_day, DATE_DIFF(DATE(invoice_date), cohort_day, MONTH) AS cohort_index, DATE_TRUNC(cohort_day, month) AS cohort_group
FROM `taling-excercise.data_pre.sales` s LEFT JOIN first_purchase f
ON s.customer_id = f.customer_id
)
GROUP BY cohort_group, cohort_index
다음 코호트 테이블로 보는건 액셀이 더 편리함!
액셀은 자신있지...
피벗테이블로 열에 cohort_index, 행에 chort_group, 값에 customer_id 넣기
리텐션 % 로 구하기 (index 0, 첫구매 가 100% 기준)
오랜만에 글을남기네,,, 이미 공부는 다 했는데 벨로그에 잘 안올리게 된다...