[My SQL] 고객 세분화(Customer Segmentation) 확인 - 인구 통계 정보 산출 쿼리문

고객 정보 데이터를 그룹핑할때 응용할 수 있는 쿼리문을 알아보겠다.
고객 세분화(Customer Segmentation)를 하는 방식에는 여러가지가 있을 수 있다.
 

• 인구 통계 - 성별 / 나이 / 결혼여부
• 방문 기록 - 신규고객 / 충성고객 / 유령고객
• 결재액 -  10만원 이상(Friends) / 50만원 이상(VIP) / 100만원 이상(VVIP)

 
오늘은 가장 기본적인 Demographic 특성을 활용해 Demo그룹별 회원 수 및 회원 비중을 알아보는 My SQL 쿼리문 예제를 준비했다.
 
 

---

 

예시 1)

성별 / 나이대 별 전체 회원 수

SELECT 
    CASE 
      WHEN LENGTH(gender) < 1 THEN 'Others'
      ELSE gender 
    END 
    AS gender
    ,
    CASE 
      WHEN age is NULL THEN '무응답'
      WHEN age <= '10' THEN '10세 이하'
      WHEN age <= '20' THEN '20세 이하'
      WHEN age <= '30' THEN '30세 이하'
      WHEN age <= '40' THEN '40세 이하'
      WHEN age <= '50' THEN '50세 이하'
      WHEN age <= '60' THEN '60세 이하'
      WHEN age <= '70' THEN '70세 이하'
      WHEN age <= '80' THEN '80세 이하'
      WHEN age <= '90' THEN '90세 이하'
    END 
    AS age
    , 
    COUNT(*) 
    AS cnt
FROM C.customer
GROUP BY 1,2
ORDER BY 3 DESC ;

 
 
먼저 SELECT 절의 구문을 하나하나 살펴보자.
 
gender 컬럼의 데이터에는 F / M / Others / 공백 4가지가 있다.
 
깔끔한 분류를 위해 F / M / Others 3가지 데이터로 출력시키기 위해, 공백데이터는 Others 데이터에 포함되도록 CASE WHEN 구문을 활용했다.
 
이때 사용된 공백데이터는 NULL이 아니다!
공백은 NULL과 완전히 다른 개념!!
 
 

⭐️ 공백 ≠ NULL ⭐️

 
 
WHEN age is NULL이 아닌, 1보다 작은 길이의 개념으로 접근하여 WHEN LENGTH(gender) < 1 로 작성한다.
 
age 컬럼 역시 CASE 구문을 활용하여 나이대 별로 그룹핑을 한다. 내가 갖고 있는 데이터에는 NULL 이 있어서 포함시켜주었다.
 
회원 수 파악을 위해 COUNT 함수를 활용해 전체를 카운팅.
 
gender 와 age 컬럼을 순서에 따라 각각 1과 2로 그룹핑하고,
3으로 지정된 cnt 컬럼을 내림차순으로 적용하면,
회원 수가 많은 성별/연령 순으로 데이터가 정렬이 되어 아래와 같은 데이터 결과를 확인 할 수 있다 :)
 
 

 
 
위 데이터를 확인한 결과 연령 관계없이 대체적으로 여성 유저의 수가 많으며, 
남녀 포함 30대 유저들의 수가 월등하게 많은 것을 확인할 수 있다. 
 
위 쿼리문에 약간의 연산을 추가하여 성별/연령 별 회원 수 비중(%) 까지 다음 예시를 통해 알아보자!
 
 
 
 

예시 2)

성별 / 나이대 별 전체 회원 비중(%)

SELECT 
    CASE 
      WHEN LENGTH(gender) < 1 THEN 'Others'
      ELSE gender 
      END AS gender
    ,
    CASE 
      WHEN age is NULL THEN '무응답'
      WHEN age <= '10' THEN '10세 이하'
      WHEN age <= '20' THEN '20세 이하'
      WHEN age <= '30' THEN '30세 이하'
      WHEN age <= '40' THEN '40세 이하'
      WHEN age <= '50' THEN '50세 이하'
      WHEN age <= '60' THEN '60세 이하'
      WHEN age <= '70' THEN '70세 이하'
      WHEN age <= '80' THEN '80세 이하'
      WHEN age <= '90' THEN '90세 이하'
    END AS age    
    , 
    COUNT(*) AS cnt
    ,
    ROUND(COUNT(*) / (SELECT COUNT(*)  
      FROM C.customer) * 100 , 2) AS 'per(%)'
FROM C.customer
GROUP BY 1,2
ORDER BY 3 DESC;

 
전체 연령 중 특정 연령 비중을 구하기 위해
예시 1) 쿼리문에서 아래의 쿼리문만 SELECT 절에 추가해주었다.
 
 
ROUND(COUNT(*) / (SELECT COUNT(*) FROM C.customer) * 100 , 2) AS 'per(%)'

 
 
구분하기 쉽도록 컬러로 구분해보았다.
 
여기에 사용된 ROUND 함수는 소수점 2자리까지 남기고 반올림
COUNT(*)는 특정 연령 그룹의 수
(SELECT COUNT(*) FROM C.customer)는 전체 연령 그룹의 수이다.
AS 'per(%)' 는 이 컬럼의 명을 per(%)로 지정하겠다는 의미
 
 
백분위 연산을 적용하면 아래와 같이 전체 연령대비 비중이 계산이 된 값을 출력할 수 있다. 
 
 

 
 
 
앞으로 포스팅에서도 고객 세분화(Customer Segmentation)에 대해 더 많은 예제들로 찾아뵐게요-😊

 

 

---

⇩ 활성 유저(Active User) 집계 쿼리문 알아보기 🧚‍♀️ ⇩

2023.03.14 - [TOOLS/My SQL] - [My SQL] DAU/WAU/MAU 활성 유저(Active User) 집계 / Stickness 지표

[My SQL] DAU/WAU/MAU 활성 유저(Active User) 집계 / Stickness 지표