SQL데이터 분석 KPI 지표 수립 실습 (프로덕트 분석 심화)

이번 실습은 각 기업에서 실제로 통계자료로써 각종 KPI 지표들을 mysql을 통해  추출할 것이다.

 

실습1 

2020년 7월에 우리 신규유저가 하루 안에 결제로 넘어가는 비율을 구하라

그리고 결제까지 보통 몇분이 소요되는지 구하라

우선 신규 유저 가입일과 최초 구매일을 구해야 한다.

신규 유저 가입일은 created_at 컬럼을 사용하고 

최초 구매일은 purchased_at 컬럼에 min 함수를 사용하여 처음 구매한 날을 추출한다.

두개의 컬럼은 다른테이블에 존재하기에 left join을 통해 테이블을 병합하고

on절에 구매일을 가입일+1일 보다 작게 함으로써 하루안에 결제로 넘어가는 비율을 알 수 있다.

where절에 7월 조건을 넣고 

select 절에 timediff함수를 사용하여 결제까지의 시간을 구한다.

보시다 시피 null 값이 나온 유저들은 하루안에 결제를 안했거나 결제를 아예안한 사람들이다.

 

이후 with함수로 테이블을 만들고

해당 테이블로 신규유저의 하루안에 결제로 넘어가는 비율과 평균 몇분이 소요되는지 구한다.

 

 

실습2

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

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

 

Retention 이란 한번 방문한 유저들이 서비스를 이탈하지 않고 계속 서비스를 이용하는 것이다. 즉 재방문하는 것

이번 실습은 self join 을 이용하는 것이다. 그동안 사용했던 join과 같은 조인을 사용하지만 같은 테이블을 조인한다.

조인후 on 절에서 당일과 다음날을 묶어준다

유의해야할 점이 같은날에 2번온사람들이 있기 때문에 유저들은 distinct를 통해 중복을 제거한다.

Retention을 구함으로써 기업은 새로운 제품을 만들었을때  제품의 완성도를 확인해볼 수 있다.

 

 

실습3

우리 서비스는 신규유저가 많은가 기존유저가 많은가

유저들이 가입한 기간별로 그룹지어 고객분포가 어떤지 알려주세요.

DAU 기준으로 분석해라

이번 실습을 통하여 진성유저의 정착도나 프로젝트의 수명주기를 확인할 수 있다.

먼저 tbl_visit 테이블에서 일자별로 고객의 마지막 방문일자와 가입일자를 빼서 서비스기간을 나타낼 수 있다.

마지막 방문일자는 max 함수를 사용하여 나타내고 

datediff함수를 사용하여 두 날짜를 계산한다.

생성일자는 tbl_customer을 사용해야하기에 조인하여 그룹핑한다.

 

 

해당 쿼리를 with로 테이블화 시키고 다음 작업을 진행한다.

DAU로 분석하기 위해 case문을 이용하여 날짜별로 구분한다.

이후 조인을 통해 기간의 날짜별 총 인원 수를 추출한다.

총 인원수와 서비스 이용 시간별 인원을 나눔으로써 분포율을 추출할 수 있다.