[SQL - Bigquery/빅쿼리] 연령별 사용자 특징 추출하기

빅쿼리를 이용하여 연령별 사용자의 특징을 추출하는 쿼리를 작성해 보도록 하겠다.

 

#0. 샘플 데이터

각 액션에 view, favorite, add_cart, purchase, review 등의 액션 이름을 붙였다.

로그인 한 사용자는 user_id 값이 들어있고, 로그인하지 않은 사용자들은 user_id 값이 비어있다.

 

 

 

#1. 연령별 구분 집계하기

다음 표를 기반으로 사용자의 성별과 연령을 구분할 것이다.

연령별 구분	설명
C	4~12 세 남성과 여성(Child)
T	13~19세 남성과 여성(Teenager)
M1	20~34세 남성(Male)
M2	35~49세 남성(Male)
M3	50세 이상의 남성(Male)
F1	20~34세 여성(Female)
F2	35~49세 여성(Female)
F3	50세 이상의 여성(Female)

다음 표를 기반으로 사용자의 성별과 연령을 구분할 것이다.

 

with
mst_users_with_int_birth_date as (
    select
        *,
        20230101 as int_specific_date, -- ①
        cast(replace(substring(cast(birth_date as string), 1, 10), '-', '') as integer) as int_birth_date -- ②
    from data.mst_users
),
mst_users_with_age as (
    select
        *,
        floor((int_specific_date - int_birth_date)/10000) as age -- ③
    from mst_users_with_int_birth_date
)
,
mst_users_with_category as (
    select
        user_id,
        sex,
        age,
        concat(
            case
            	when 20 <= age then sex
            	else ''
            end
            ,case -- ④
            	when age between 4 and 12 then 'C'
                when age between 13 and 19 then 'T'
            	when age between 20 and 34 then '1'
            	when age between 35 and 49 then '2'
            	when age >= 50 then '3'
            end
        ) as category
    from mst_users_with_age
)
select * from mst_users_with_category
order by user_id;

쿼리 결과는 다음과 같다.

 

① 특정날짜(2023년 1월 1일)의 정수표현

 

② 나이 계산을 하기 위해 날짜를 정수형으로 바꾸어 준다. CAST()

CAST(expression AS INT64)

 

 

③ 특정날짜(2023년 1월 1일) 시점의 나이 계산

 : 특정날짜 - 사용자의 생년월일

 

④ category 칼럼에서는 연령별 구분 계산 결과를 출력해 준다. CASE문, CONCAT()

    case문을 이용하여 연령이 20살 이상이라면, 성별 접두사로 'M' 또는 'F'를 출력한다.

   그리고 나이 구분에 따라 C, T,  1, 2, 3을 출력하고 concat함수로 두 개를 결합한다.

 

 

 

#2. 연령별 구분의 특징 추출하기

with
mst_users_with_int_birth_date as (
    select
        *,
        20230101 as int_specific_date,
        cast(replace(substring(cast(birth_date as string), 1, 10), '-', '') as integer) as int_birth_date
    from data.mst_users
),
mst_users_with_age as (
    select
        *,
        floor((int_specific_date - int_birth_date)/10000) as age
    from mst_users_with_int_birth_date
)
,
mst_users_with_category as (
    select
        user_id,
        sex,
        age,
        concat(
            case
                when 20 <= age then sex
                else ''
            end
            ,case
                when age between 4 and 12 then 'C'
                when age between 13 and 19 then 'T'
                when age between 20 and 34 then '1'
                when age between 35 and 49 then '2'
                when age >= 50 then '3'
            end
        ) as category
    from mst_users_with_age
)
select
    p.category as product_category,
    u.category as mst_users_with_category,
    count(*) as purchase_count
from ivory-program-349520.data.action_log as p
    join mst_users_with_category as u
    on p.user_id = u.user_id
where
 -- 구매 로그만 선택하기
 action = 'purchase'
group by p.category, u.category
order by p.category, u.category;

action_log 테이블에서 action이 purchase인 구매 로그만 가져온 결과이다.

이 같은 쿼리를 이용하여 카테고리 내부에서 연령 분포를 확인 할 수 있다.

 

 

 

#3. 추가분석

위의 결과는 데이터 양이 작아 분석용으로는 적합하지 않아 임의로 데이터를 추가하여 M1 성별 action 카테고리의 purchase 항목만을 가져와 결과를 저장해 보았다.

 

csv파일로 결과를 다운로드하고 차트를 간단하게 그려보았다.

위의 결과를 통해서 액션은 M1 계층에게 인기가 있다는 것을 파악할 수 있다.

 

---

전체 코드는 다음과 같습니다.

with
mst_users_with_int_birth_date as (
    select
        *,
        20230101 as int_specific_date,
        cast(replace(substring(cast(birth_date as string), 1, 10), '-', '') as integer) as int_birth_date
    from data.mst_users
)
,mst_users_with_age as (
    select
        *,
        floor((int_specific_date - int_birth_date)/10000) as age
    from mst_users_with_int_birth_date
)
,mst_users_with_category as (
    select
        user_id,
        sex,
        age,
        concat(
            case
           		when 20 <= age then sex
            	else ''
            end
            ,case
           		when age between 4 and 12 then 'C'
            	when age between 13 and 19 then 'T'
            	when age between 20 and 34 then '1'
            	when age between 35 and 49 then '2'
            	when age >= 50 then '3'
            end
        ) as category
    from mst_users_with_age
)
select
    p.category as product_category,
    u.category as mst_users_with_category,
    count(*) as purchase_count
from ivory-program-349520.data.action_log as p
    join mst_users_with_category as u
    on p.user_id = u.user_id
where
 action = 'purchase'
group by p.category, u.category
order by p.category, u.category;

 

<데이터 분석을 위한 SQL 레시피> 11강 부분을 참고하여 작성하였습니다.