SQL_Intermediate 05. 프로덕트 분석 심화

05. 프로덕트 분석 심화

---

Q19. 2020년 7월에 우리 신규 유저가 하루 안에 결재로 넘어가는 비율이 어떻게 되나요? 
그 비율이 어떤지 알고 싶고, 결제까지 보통 몇 분 정도가 소요되는지 알고 싶어요.

---

Paying Conversion within 1 day 이해

최초 구매일 찾기

LEFT JOIN 사용 이유

TIMEDIFF 사용

 

TIME_TO_SEC()

   - 시간을 초로 변환해주는 함수

 

SELECT A.customer_id
	,  A.created_at
	,  B.customer_id AS paying_user
	 ,  B.purchased_at
	, TIME_TO_SEC(TIMEDIFF(B.purchased_at,A.created_at)) / 3600 AS diff_hour
FROM tbl_customer AS A
LEFT JOIN( SELECT customer_id
			, MIN(purchased_at) as purchased_at
			FROM tbl_purchase 
			GROUP BY 1) B
ON A.customer_id = B.customer_id
AND B.purchased_at < A.created_at + interval 1 day
WHERE created_at >= '2020-07-01'
AND created_at < '2020-08-01' ;

 

 

 

 

WITH rt_tbl AS (
                SELECT A.customer_id
                    ,  A.created_at
                    ,  B.customer_id AS paying_user
                     ,  B.purchased_at
                    , TIME_TO_SEC(TIMEDIFF(B.purchased_at,A.created_at)) / 3600 AS diff_hour
                FROM tbl_customer AS A
                LEFT JOIN( SELECT customer_id
                            , MIN(purchased_at) as purchased_at
                            FROM tbl_purchase 
                            GROUP BY 1) B
                ON A.customer_id = B.customer_id
                AND B.purchased_at < A.created_at + interval 1 day
                WHERE created_at >= '2020-07-01'
                AND created_at < '2020-08-01' 
    )

SELECT  ROUND(COUNT(paying_user) / COUNT(customer_id) * 100 , 2) AS Paying_Conversion 
FROM rt_tbl
UNION ALL
SELECT  AVG(diff_hour) AS pay_mean
FROM rt_tbl ;

 

 

신규 유저가 하루 안에 결재로 넘어가는 비율 : 26.4 %

결제까지 보통 몇 분 정도가 소요: 14분

 

 

Q20. 우리 서비스는 유저의 재 방문율이 높은 서비스 인가요? 

이를 파악하기 위해 7월 기준 Day1 Retention이 어떤지 구해주시고, 추세를 보기 위해 Daily로 추출해 주세요.

---

Retention : 시간이 지날 수록 얼마나 많은 유저가 제품이나 서비스로 다시 돌아오는지
N-day Retention : n = 1, 2, 3, 4, 5, ..... 30 .... 

 

SELECT DATE_FORMAT(A.visited_at  - interval 9 hour, '%Y-%m-%d') AS d_date
     , COUNT(DISTINCT A.customer_id) AS Active_user
     , COUNT(DISTINCT B.customer_id) AS retained_user
     , COUNT(DISTINCT B.customer_id) / COUNT(DISTINCT A.customer_id) AS retention_rate
FROM tbl_visit AS A
LEFT JOIN tbl_visit AS B
ON A.customer_id = B.customer_id
AND DATE_FORMAT(A.visited_at - interval 9 hour, '%Y-%m-%d') = DATE_FORMAT(B.visited_at - interval 9 hour - interval 1 day, '%Y-%m-%d') 
WHERE A.visited_at >= '2020-07-01'
AND A.visited_at < '2020-08-01'
GROUP BY 1;

 

 

Q21. 우리 서비스는 신규 유저가 많나요? 기존 유저가 많나요? 

유저들이 가입한 기간별로 그룹지어 고객 분포가 어떤지 알려주세요. DAU 기준으로 부탁합니다.

---

DAU : Daily Active User, 유저의 나이는 서비스나이, 1년차 사용, 2년차사용 이런 것 처럼

User Age에 따른 DAU 분석

마지막 접속일 찾기

DATEDIFF 사용