SQL데이터 분석 KPI 지표 수립 실습 (서비스 이해 문제 모음)

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

갑자기 실습 난이도가 확 올라간 것을 느낄 수 있는 회차였다.

 

실습1

2020년 7월의 총 Revenue를 구해주세요

 

Revenue 란 수익을 뜻하고 

7월의 Revenue 이니 7월 수익을 추출해볼 것이다.

수익 데이터를 추출하기 위해 purchase 테이블을 사용한다.

집계함수 sum을 사용하여 총 이익 값을 계산할 것이고

where절에 Date 필터를 입혀 7월의 날짜값을 입력한다.

 

fastcampus 데이터베이스에서 가져왔기 때문에 from 절 입력하는 구매목록 테이블에 붙였다.

다음실습 부터는 use 를 사용하여 간단히 생략하겠다.

 

 

실습2

2020년 7월의 MAU를 구해주세요

 

mau란 한 달 동안 해당 서비스를 이용한 순수한 이용자 수를 나타내는 지표 이다.

이용자 수를 얻기 위해 방문자 테이블을 사용한다.

count 함수를 이용하여 방문자 수를 구하는데 순수 이용자수를 구해야 하기 때문에 distinct 를 사용하여 중복값을 제거한다.나머지는 실습 1과 같이 Date 필터를 입혀 7월의 이용자를 추출한다.

 

 

 

실습3

2020년 7월에 우리 Active 유저의 구매율(Paying Rate)은 어떻게 되나요?

 

구매율은 예를들어 100명의 방문자가 있다면 그중에 몇명의 방문자가 구매를 했는지 지표를 추출하는 것이다.

이를 위해서 7월의 순수 방문자와 그 구매 현황을 사용하여 계산을 한 후 비율을 round 함수를 이용하여 나타낼 것이다.

마치 select 절을 계산기처럼 사용하는 것이다.

use fastcampus 문은 있다 가정하고 생략하겠다.

 

실습2에서 추출한 7월의 mau를 가져와 값을 얻었다.

 

7월의 순수 방문자 구매수를 추출하였다. 

위의 2개의 쿼리문의 결과를 이용하여 paying rate 를 추출할 것이다.

여기에서는 round 함수를 이용하여 추출한다.

mau를 순수 방문자  구매수로 나눈 후 100을 곱하고 소숫점2의 자리까지 나타낸다(백분율로 나타냄).

---

 실습4

2020년 7월에 구매 유저의 월 평균 구매액은 어떻게 되나요?

 

이번 실습은 말그대로 ARPPU 를 구하는 것이다.

우리가 원하는 자료는 고객당 평균 구매액이기 때문에 

customer_id 를 기준으로 평균 값을 구하고 이는 revenue 이다

유저마다 구매한 갯수가 제각각 이기에 유저 기준으로 group by로 묶는다. 

(편의를 위해 select 절 첫번째 컬럼이라는 뜻으로 1을 작성한다)

고객당 월평균을 구한다음 작성한 쿼리를 서브쿼리로 묶어 총 평균을 구한다.(인라인뷰 사용)

다만 인라인뷰를 사용할 시 뒤에 아무문자든 넣어줘야 쿼리가 실행된다.

 

실습5

2020년 7월에 가장 많이 구매한 Top3 고객과 Top10~15 고객을 뽑아주세요

 

위의 실습에서 사용한 revenue를 사용할 것이고

순서를 구하기 위해 order by 절을 사용해 순서 나열 (높은 순서이기에 내림차순의 desc 사용)

top 고객을 구하기 위해 행수 제한을 주는 limit 함수를 사용한다

. 여기서 top10~15는 offset 함수를 사용하여 결과 행의 시작 기준을 정한다

 

오늘 실습은 현업 실무에서 사용할 법한 주요 내용들을 다뤄보았다.