[빅쿼리-SQL]고객 마스터 데이터를 카테고리화(성별,연령대)하여 집계하기
서비스를 제공하는 측에서 사용자와 관련된 정보로 알고 싶은 것을 정리해보면 다음과 같은 두 가지로 분류할 수 있습니다.
- 사용자의 속성(나이,성별,주소지)
- 사용자의 행동(구매한 상품, 사용한 기능, 사용하는 빈도)
이 중 사용자의 속성을 정리한 데이터를 '고객 마스터 데이터'라고 합니다.
'고객 마스터 데이터'를 통하여 분석가는 '어떤 속성의 사용자가 우리 서비스를 사용 중인가?'를 알아볼 수 있습니다.
이번 글에서는 예제 '고객 마스터 데이터'를 사용하여 성별, 연령별로 구분하고 이를 '카테고리화' 하는 방법을 알아보겠습니다.
샘플 데이터
<데이터 분석을 위한 SQL 레시피 도서>의 예제 데이터 셋을 사용하였습니다.
- 테이블 명 : mst_users
- user_ iD : 고객 ID
- Sex : 고객의 성별
- birth_date : 고객의 생년월일
- register_date : 등록 날짜 (고려 X)
- register_device : 등록한 장치 (고려 X)
전체 쿼리 먼저 보기
- 고객의 생일을 이용하여 나이 구하기
- 고객의 성별과 연령을 사용하여 카테고리 화 하기
전체 쿼리 뜯어보기
전체 쿼리를 색깔별로 구분한 박스들의 SQL 문을 하나씩 해석해보며 전체 쿼리를 이해해 보겠습니다.
with 절을 사용하여 임시 테이블을 만들어 쿼리를 작성합니다.
- 고객의 생일을 이용하여 나이 구하기
1. 빨간색 박스
특정 날짜의 고객 나이를 구할 것이므로, 특정 날짜를 고객 마스터 데이터에 추가해야 합니다.
필자는 오늘 날짜를 추가하여 오늘에 해당하는 고객의 나이를 알아보겠습니다.
또한, 두 열의 '차'를 구하기 위하여 생일과, 특정 날짜 열을 수치형으로 변환해야 합니다. ex) 19700723
생성된 int_specific_date 열과 int_birth_date 열에 주목합니다.
cast, replace, substr 함수를 사용하여 (1977-06-17 ----> 19990617) 형식으로 바꾸어 줍니다.
각 함수의 사용법은 이전 글에서 다루었으니 이번 글에서는 생략하겠습니다.
2. 파란색 박스
생성한 두 열의 차를 구하고, 이를 10000으로 나누어 주면 특정 날짜에 해당하는 '나이'를 구할 수 있습니다.
floor 함수를 사용하여 결과를 내림차순 한다.
ex ) (20220621 - 19770617) / 10000 = 45.0004
floor(45.0004) = 45(세)
- 고객의 성별과 연령을 사용하여 카테고리 화 하기
앞선 쿼리에서 구한 나이를 사용하여 성별+연령대로 고객을 카테고리 화 할 수 있습니다.
해당 쿼리의 빨간색 박스, 초록색 박스는 위와 해석 및 방법이 동일하니 생략하겠습니다.
1. 초록색 박스
concat 함수를 사용한 열에서 case 함수를 사용하여 카테고리 화를 하였습니다.
카테고리는 concat 함수를 사용하여 (성별, 연령대)로 나타남을 알 수 있습니다.
단, when 20 <= age then sex else "" end 문을 사용하여 20 살 이하는 성별을 구분하지 않았습니다.
이번 글에서는 고객 마스터 데이터의 정보(성별, 생일)를 사용하여 고객을 카테고리화 하는 법을 알아보았습니다.
카테고리를 사용하여 연령, 나이별 매출, 선호도, 액션의 횟수 등 특정 속성의 고객의 사용 지표를 구할 수 있으므로
반드시 알아두어야 하는 집계 방법입니다.