[SQL 뉴비 가이드북] 2.SQL 쿼리로 게임 업계 지표 추출하기 1탄.AU편 (DAU, WAU, MAU)
게임업계를 희망하는 데이터 분석가들을 위해, SQL로 게임 업계에서 정말 많이 쓰는 주요 지표들을 추출하는 방법에 대해 작성하려고 한다. 정말 많은 지표가 있는데, 그 중에서도 가장 많이 쓰인다고 생각하는 DAU, WAU, MAU에 다뤄보겠다. 쿼리는 빅쿼리 기준으로 작성하였다.
추후 PU , NPU, ARPPU 등을 다루는 PU 편도 작성할 예정!
0.AU
Active User, 활성 사용자 수
: 기간 동안 게임/앱을 사용한 유저의 유니크 카운트
여기서 Active라는걸 어떻게 정의하냐에 따라 다르지만, 내가 일했던 모든 회사들은 로그인 기준으로 산정했기 때문에
해당 글 또한 모두 로그인 기준으로 작성합니당.
1.DAU
Daily Active User , 일일 활성 사용자 수
: 기준일자에 접속한 유저 수를 뜻하는 말로, 그 날 로그인 유저의 유니크 카운트이다. 얼마나 많은 유저들이 게임에 접속하는지 확인할 수 있는 가장 대표적인 지표이다. (데이터 분석가를 지망하지 않아도 앱/게임 서비스에서는 많이 쓰이니 알아두도록 하자)
한 유저가 하루에 5번 접속해도, DAU는 1로 취급한다. 활성 "사용자 수"니까.
로그인 데이터가 위와 같이 있다고 가정하자, 우리는 일자별 유니크 유저수를 구해야한다.
SELECT login_dt ,COUNT(DISTINCT userid)
FROM table
GROUP BY 1
로그인 날짜를 기준으로 DISTINCT (unique) 카운트를 해주면 끝.
login_dt가 저렇게 날짜로 되어있지 않고 datetime 형식으로 되어으면, DATE(login_dt)를 사용해주면 된다.
2.WAU
Weekly Active User , 주간 활성 사용자 수
: 기준주차에 접속한 유저수, 일주일에 몇명의 유저가 접속했는지를 뜻한다.
당연히 A 유저가 일주일에 13123213번 접속해도, 그 주에선 한명의 유저로 잡히니 WAU는 1일 것이다.
단, WAU를 산출할 때 주의할점은 기준일을 일~토로 잡느냐, 월~일로 잡느냐 두가지가 있을텐데 이건 회사마다 다르니 각각 상황에 맞는 걸로 산출하면 된다! (그리고 RDBMS 마다 월요일의 주의 시작인 경우도 있고 일요일이 시작인 경우도 있으니 꼭 꼭 확인하고 추출해야한다.)
데이터가 위처럼 있다고 하자. 주간을 구분하기 위해 날짜는 참고차 넣었다.
빅쿼리에서는 DATE_TRUNC(컬럼,WEEK(기준요일))을 사용해주면 손쉽게 기준 요일로 주차를 발라낼 수 있다.
1) 일~토 기준
SELECT DATE_TRUNC(login_dt,week(sunday)),COUNT(DISTINCT userid)
FROM table
GROUP BY 1
이렇게 일요일 기준으로 유저가 유니크 카운트 될 것이고,
2) 월~일 기준
SELECT DATE_TRUNC(login_dt,week(monday)),COUNT(DISTINCT userid)
FROM table
GROUP BY 1
이렇게 친다면 월요일 기준으로 유저가 유니크 카운트 될 것이다! 기준이 달라지니 값이 달라지는 만큼 꼭꼭 유의해주도록 하자
3.MAU
Monthly Active User , 월간 활성 사용자 수
: 기준월에 접속한 유저수, 한달에 몇명의 유저가 접속했는지를 뜻한다.
SELECT DATE_TRUNC(login_dt,MONTH),COUNT(DISTINCT userid)
FROM table
GROUP BY 1
이렇게!
이렇게 가장 기본이 되는 지표인 AU들에 대한 지표 추출방법을 쿼리로 알아보았다. 정말정말 쉽지만 ! 아주 기본이고 중요한만큼 정확하게 숙지해두도록 하자.
그리고 이게 빅쿼리가 날짜 변환이 진짜 사용자 친화적으로 되어있는데.. DATE_FORMAT을 쓰거나 주차 요일을 변경하기 위해서 되게 복잡한 쿼리가 필요한 RDBMS들도 종종 있다. 사용하는 쿼리 문법에 맞게 잘 변형해서 쓰면 된다.!!
다음편에는 PU , NPU , ARPU, ARPPU 를 통해 PU에 대해 작성해오겠습니다 ( _ _)
