STARTERS 4기 🚉/TIL 👶🏻
[STARTERS 4기 TIL] SQL 심화 #6 - 고객 분석, 윈도우 함수, Decil, RFM (230417)
춘시기좋아
2023. 4. 17. 17:45
📖 오늘 내가 배운 것
1. 고객 분석
2. 기본 테이블 생성 및 분석 시작
3. 과제
1. 고객 분석 간단
B2C 기업 고객 분석한다고 하면?
성별, 연령, 사는 지역 등의 속성이 있을 수 있다!
우리 데이터에서는 소속 지역/도시 정도..?
국가별
1.1 국가 별 고객 수, 누적합계
select *
, sum(고객수) over (order by 고객수 desc, country asc)
from (
select country
, count(*) as 고객수
from customers c
group by 1
order by 2 desc
) tmp주의할 점은 고객수 동일 순위에 대한 문제를 해결하기 위해 order by에 고객수-country 정렬 함께 넣어줘야 한다는 거!!!
1.2 국가 별 고객 수, 구성비, 누적비
--누적 구성비
select *
, sum(구성비) over(order by 구성비 desc, country desc) 누적비
from (
-- 구성비
select *
, 고객수 * 100 / sum(고객수) over () as 구성비
from (
-- 고객수 구하기
select country
, count(*) as 고객수
from customers c
group by 1
order by 2 desc
) a
) b
1번과 마찬가지로 동일 순위 문제 해결하기 위해 country로 정렬 해줘야 한다!!
그리고 서브 쿼리 여러번 작성할 때 가독성을 챙기기 위해
1. 테이블 네이밍
2. 주석처리
3. 들여쓰기
신경쓰면 좋다!!!
1.3 구매 이력이 없는 고객!
orders와 customers는 0..1 관계! 따라서 구매 이력이 없는 고객이 있을 수 있다~ 찾아보도록 하자!
customers에는 있는데 orders에 없는 고객을 구하면 된당
1. LEFT JOIN
-- 1번 방법 is null
select c.customer_id
from customers c
left join orders o on c.customer_id = o.customer_id
where o.order_id is null
-- 2번 방법 having cnt
select c.customer_id
from customers c
left join orders o on c.customer_id = o.customer_id
group by 1
having count(o.order_id) = 0
2. 차집합
select distinct customer_id
from customers
except
select distinct customer_id
from orders oexcept : 윗 테이블에서 아랫 테이블 빼기
2. 기본 테이블 만들기
고객 분석 하기 위해 기본 테이블을 만들어보자!!!
customers-orders-details
매출까지 함께 분석하기 위해 위해 orders
products, categories
구매한 제품-카테고리까지 보기 위해 products, categories까지 JOIN 하자~
이때, 고려해야 할 것은 주문이 없는 고객을 포함해야 하나?
포함할 거면 LEFT JOIN 근데 우리는 주문이 있는 고객들만 데리고 분석하기 위해 INNER JOIN으로!
with cte_customers as (
select -- customers
c.customer_id
, c.company_name
, c.contact_title
, c.country
, c.city
-- order
, o.order_id
, o.order_date
, to_char(order_date, 'YYYY') as year
, to_char(order_date, 'MM') as month
, to_char(order_date, 'dd') as day
, to_char(order_date, 'q') as quarter
, date_part('dow' ,order_date) as dow
-- order_details
, od.product_id
, od.unit_price
, od.quantity
, od.discount
, round( od.unit_price * od.quantity * (1-od.discount) ) as amount
-- categories
, c2.category_id
, c2.category_name
--, p.product_id as 제품ID order_details와 중복!
, p.product_name
from customers c
INNER join orders o ON o.customer_id = c.customer_id
INNER join order_details od ON od.order_id = o.order_id
inner join products p on p.product_id = od.product_id
INNER join categories c2 on p.category_id = c2.category_id
)
select count(*)
from cte_customers* 총 행수는 2,155개
* LEFT JOIN 시 총 행수 2,157
2.1 국가별 고객수, 매출액, 주문건수 + 상관관계
with cte_customers as (
select -- customers
c.customer_id
, c.company_name
, c.contact_title
, c.country
, c.city
-- order
, o.order_id
, o.order_date
, to_char(order_date, 'YYYY') as year
, to_char(order_date, 'MM') as month
, to_char(order_date, 'dd') as day
, to_char(order_date, 'q') as quarter
, date_part('dow' ,order_date) as dow
-- order_details
, od.product_id
, od.unit_price
, od.quantity
, od.discount
, round( od.unit_price * od.quantity * (1-od.discount) ) as amount
-- categories
, c2.category_id
, c2.category_name
--, p.product_id as 제품ID order_details와 중복!
, p.product_name
from customers c
LEFT join orders o ON o.customer_id = c.customer_id
left join order_details od ON od.order_id = o.order_id
LEFT join products p on p.product_id = od.product_id
LEFT join categories c2 on p.category_id = c2.category_id
)
, cte_country_customercnt_amount_ordercnt as (
-- 국가별 고객수, 매출액, 주문건수
select country as 국가
, count(distinct customer_id) as 고객수
, sum(amount) as 매출액
, count(distinct order_id) as 주문건수
from cte_customers
group by country
order by 3 desc
)
select corr(고객수, 매출액) as "고객수_매출액"
, corr(매출액, 주문건수) as "매출액_주문건수"
, corr(고객수, 주문건수) as "고객수_주문건수"
from cte_country_customercnt_amount_ordercnt
2.2 국가별 매출액 시각화하기
국가의 매출액이 높고 낮음을 시각화하기
어떤 대륙의 매출이 가장 높을까?
이렇게만 봐서는 알 수 없다.
2.3 지역 컬럼 추가하기
대륙적으로 매출 분포를 확인하기 위해 직접 국가 컬럼을 추가한다!
이때, 대소문자 고려하지 않기 위해 lower() 사용
with cte_customers as (
select -- customers
c.customer_id
, c.company_name
, c.contact_title
, c.country
, c.city
-- order
, o.order_id
, o.order_date
, to_char(order_date, 'YYYY') as year
, to_char(order_date, 'MM') as month
, to_char(order_date, 'dd') as day
, to_char(order_date, 'q') as quarter
, date_part('dow' ,order_date) as dow
-- order_details
, od.product_id
, od.unit_price
, od.quantity
, od.discount
, round( od.unit_price * od.quantity * (1-od.discount) ) as amount
-- categories
, c2.category_id
, c2.category_name
--, p.product_id as 제품ID order_details와 중복!
, p.product_name
from customers c
LEFT join orders o ON o.customer_id = c.customer_id
left join order_details od ON od.order_id = o.order_id
LEFT join products p on p.product_id = od.product_id
LEFT join categories c2 on p.category_id = c2.category_id
)
, cte_country_customercnt_amount_ordercnt as (
-- 국가별 고객수, 매출액, 주문건수
select country as 국가
, count(distinct customer_id) as 고객수
, sum(amount) as 매출액
, count(distinct order_id) as 주문건수
from cte_customers
group by country
order by 3 desc
)
-- 상관계수 뽑기
, cte_country_customercnt_amount_ordercnt_corr as (
select corr(고객수, 매출액) as "고객수_매출액"
, corr(매출액, 주문건수) as "매출액_주문건수"
, corr(고객수, 주문건수) as "고객수_주문건수"
from cte_country_customercnt_amount_ordercnt
)
-- 지역 추가하기
, cte_country_group as (
select *
, case
when lower(국가) in ('usa','canada','mexico') then 'NorthAmerica'
when lower(국가) in ('brazil','venezuela','argentina') then 'SouthAmerica'
else 'Europe'
end as 지역
from cte_country_customercnt_amount_ordercnt
2.4 지역별 매출액, 주문건수, 고객수, 구성비 구하기
with cte_customers as (
select -- customers
c.customer_id
, c.company_name
, c.contact_title
, c.country
, c.city
-- order
, o.order_id
, o.order_date
, to_char(order_date, 'YYYY') as year
, to_char(order_date, 'MM') as month
, to_char(order_date, 'dd') as day
, to_char(order_date, 'q') as quarter
, date_part('dow' ,order_date) as dow
-- order_details
, od.product_id
, od.unit_price
, od.quantity
, od.discount
, round( od.unit_price * od.quantity * (1-od.discount) ) as amount
-- categories
, c2.category_id
, c2.category_name
--, p.product_id as 제품ID order_details와 중복!
, p.product_name
from customers c
LEFT join orders o ON o.customer_id = c.customer_id
left join order_details od ON od.order_id = o.order_id
LEFT join products p on p.product_id = od.product_id
LEFT join categories c2 on p.category_id = c2.category_id
)
, cte_country_customercnt_amount_ordercnt as (
-- 국가별 고객수, 매출액, 주문건수
select country as 국가
, count(distinct customer_id) as 고객수
, sum(amount) as 매출액
, count(distinct order_id) as 주문건수
from cte_customers
group by country
order by 3 desc
)
-- 상관계수 뽑기
, cte_country_customercnt_amount_ordercnt_corr as (
select corr(고객수, 매출액) as "고객수_매출액"
, corr(매출액, 주문건수) as "매출액_주문건수"
, corr(고객수, 주문건수) as "고객수_주문건수"
from cte_country_customercnt_amount_ordercnt
)
-- 지역 추가하기
, cte_country_group as (
select *
, case
when lower(국가) in ('usa','canada','mexico') then 'NorthAmerica'
when lower(국가) in ('brazil','venezuela','argentina') then 'SouthAmerica'
else 'Europe'
end as 지역
from cte_country_customercnt_amount_ordercnt
)
-- 지역별 고객수, 매출액, 주문건수 집계
, cte_country_group_customercnt_amount_ocnt as (
select 지역
, sum(고객수) as 고객수
, sum(매출액) as 매출액
, sum(주문건수) as 주문건수
from cte_country_group
group by 1
order by 3 desc
)
select 지역
, 고객수
, 고객수 / sum(고객수) over() * 100 as 고객수구성비
, 매출액
, 매출액 / sum(매출액) over() * 100 as 매출액구성비
, 주문건수
, 주문건수 / sum(주문건수) over() * 100 as 주문건수구성비
from cte_country_group_customercnt_amount_ocnt
2.4 지역별 판매된 제품 순위
with cte_customers as (
select -- customers
c.customer_id
, c.company_name
, c.contact_title
, c.country
, c.city
-- order
, o.order_id
, o.order_date
, to_char(order_date, 'YYYY') as year
, to_char(order_date, 'MM') as month
, to_char(order_date, 'dd') as day
, to_char(order_date, 'q') as quarter
, date_part('dow' ,order_date) as dow
-- order_details
, od.product_id
, od.unit_price
, od.quantity
, od.discount
, round( od.unit_price * od.quantity * (1-od.discount) ) as amount
-- categories
, c2.category_id
, c2.category_name
--, p.product_id as 제품ID order_details와 중복!
, p.product_name
from customers c
INNER join orders o ON o.customer_id = c.customer_id
INNER join order_details od ON od.order_id = o.order_id
inner join products p on p.product_id = od.product_id
INNER join categories c2 on p.category_id = c2.category_id
)
, cte_country_customercnt_amount_ordercnt as (
-- 국가별 고객수, 매출액, 주문건수
select country as 국가
, count(distinct customer_id) as 고객수
, sum(amount) as 매출액
, count(distinct order_id) as 주문건수
from cte_customers
group by country
order by 3 desc
)
-- 상관계수 뽑기
, cte_country_customercnt_amount_ordercnt_corr as (
select corr(고객수, 매출액) as "고객수_매출액"
, corr(매출액, 주문건수) as "매출액_주문건수"
, corr(고객수, 주문건수) as "고객수_주문건수"
from cte_country_customercnt_amount_ordercnt
)
-- 지역 추가하기
, cte_country_group as (
select *
, case
when lower(국가) in ('usa','canada','mexico') then 'NorthAmerica'
when lower(국가) in ('brazil','venezuela','argentina') then 'SouthAmerica'
else 'Europe'
end as 지역
from cte_country_customercnt_amount_ordercnt
)
-- 지역별 고객수, 매출액, 주문건수 집계
, cte_country_group_customercnt_amount_ocnt as (
select 지역
, sum(고객수) as 고객수
, sum(매출액) as 매출액
, sum(주문건수) as 주문건수
from cte_country_group
group by 1
order by 3 desc
)
-- 지역별 고객수, 매출액, 주문건수 구성비
, cte_country_group_customercnt_amount_ocnt_per as (
select 지역
, 고객수
, 고객수 / sum(고객수) over() * 100 as 고객수구성비
, 매출액
, 매출액 / sum(매출액) over() * 100 as 매출액구성비
, 주문건수
, 주문건수 / sum(주문건수) over() * 100 as 주문건수구성비
from cte_country_group_customercnt_amount_ocnt
)
-- 지역별 매출 비교
-- 순위-지역([카테고리명]제품명(제품번호))-지역-지역
, cte_country_category_quantity as (
select 지역
, category_name
, product_name
, product_id
, sum(quantity) as total_quantity
from cte_customers cc
left join cte_country_group ccg on cc.country = ccg.국가
group by 1,2,3,4
)
-- 지역-카테고리 quantitiy에 따른 RANK
, cte_country_category_quantity_rank AS (
select *
, row_number () over(partition by 지역 order by total_quantity desc) as rank
from cte_country_category_quantity
)
-- 지역별 판매된 제품 순위
, cte_country_category_quantity_rank_dsec as (
select rank
, MAX(case when 지역 = 'NorthAmerica' then '['||category_name||']'||' '||product_name||' '||'('||product_id||')' end) as "NorthAmerica"
, MAX(case when 지역 = 'SouthAmerica' then '['||category_name||']'||' '||product_name||' '||'('||product_id||')' end) as "SouthAmerica"
, MAX(case when 지역 = 'Europe' then '['||category_name||']'||' '||product_name||' '||'('||product_id||')' end) as "Europe"
from cte_country_category_quantity_rank
group by 1
order by 1
-- 강사님은 CASE WHEN으로 먼저 묶고 가져오심!!!!
-- 추후에 따라해보기
)
--고객별 매출액, 주문건수, 건당평균주문액
, cte_customer_quantity_amount as (
select customer_id
, count(distinct order_id) as order_cnt
, sum(amount) as amount
from cte_customers
group by 1
order by 3 desc
)
-- 고객별 매출액, 주문건수, 건당평균주문액 순위
, cte_customer_ocnt_amount_amountpero_rank as (
select customer_id
, order_cnt as 주문건수
, row_number() over(order by order_cnt desc) as 주문건수순위
, amount as 매출액
, row_number() over(order by amount desc) as 매출액순위
, round(amount / order_cnt) as 건단평균주문액
, row_number() over(order by amount / order_cnt desc) as 건당평균주문액순위
from cte_customer_quantity_amount
order by 5 asc
)
-- 고객당 매출액 및 ntile-등급화
, cte_amount_decil as (
select customer_id, amount
, ntile(10) over (order by amount desc) as decil
from cte_customer_quantity_amount
)
-- decil 별 매출액
, cte_decil_group_amount as (
select decil
, sum(amount) as decil_sum_amount
from cte_amount_decil
group by 1
order by 2 desc
)
-- decil별 매출액 토탈 비율
, cte_decil_amount_per as (
select *
, sum(decil_sum_amount) over() as total
, decil_sum_amount / sum(decil_sum_amount) over() * 100 as decil_sum_amount_rate
from cte_decil_group_amount
)
-- decil별 매출액 토탈 비율 누적비
, cte_decil_amount_per_cumsum as (
select *
, sum(decil_sum_amount_rate) over (order by decil) as cumsum
from cte_decil_amount_per
)
---------- RFM -----------
-- recency 구하기 위한 max(order_date) - order_date 테이블
, cte_customer_maxo as (
select *
, MAX(order_date) over() as maxo
from cte_customers
)
-- rfm
, cte_rfm as (
select customer_id
, max(maxo) - max(order_date) as Recency
, count(distinct order_id) as Frequency
, sum(amount) as Monetary
from cte_customer_maxo
group by 1
order by 2 asc, 3 desc, 4 desc
)
select *
from cte_country_category_quantity_rank_dsec
2.5 고객별 매출액, 주문건수, 건당평균주문액
with cte_customers as (
select -- customers
c.customer_id
, c.company_name
, c.contact_title
, c.country
, c.city
-- order
, o.order_id
, o.order_date
, to_char(order_date, 'YYYY') as year
, to_char(order_date, 'MM') as month
, to_char(order_date, 'dd') as day
, to_char(order_date, 'q') as quarter
, date_part('dow' ,order_date) as dow
-- order_details
, od.product_id
, od.unit_price
, od.quantity
, od.discount
, round( od.unit_price * od.quantity * (1-od.discount) ) as amount
-- categories
, c2.category_id
, c2.category_name
--, p.product_id as 제품ID order_details와 중복!
, p.product_name
from customers c
INNER join orders o ON o.customer_id = c.customer_id
INNER join order_details od ON od.order_id = o.order_id
inner join products p on p.product_id = od.product_id
INNER join categories c2 on p.category_id = c2.category_id
)
, cte_country_customercnt_amount_ordercnt as (
-- 국가별 고객수, 매출액, 주문건수
select country as 국가
, count(distinct customer_id) as 고객수
, sum(amount) as 매출액
, count(distinct order_id) as 주문건수
from cte_customers
group by country
order by 3 desc
)
-- 상관계수 뽑기
, cte_country_customercnt_amount_ordercnt_corr as (
select corr(고객수, 매출액) as "고객수_매출액"
, corr(매출액, 주문건수) as "매출액_주문건수"
, corr(고객수, 주문건수) as "고객수_주문건수"
from cte_country_customercnt_amount_ordercnt
)
-- 지역 추가하기
, cte_country_group as (
select *
, case
when lower(국가) in ('usa','canada','mexico') then 'NorthAmerica'
when lower(국가) in ('brazil','venezuela','argentina') then 'SouthAmerica'
else 'Europe'
end as 지역
from cte_country_customercnt_amount_ordercnt
)
-- 지역별 고객수, 매출액, 주문건수 집계
, cte_country_group_customercnt_amount_ocnt as (
select 지역
, sum(고객수) as 고객수
, sum(매출액) as 매출액
, sum(주문건수) as 주문건수
from cte_country_group
group by 1
order by 3 desc
)
-- 지역별 고객수, 매출액, 주문건수 구성비
, cte_country_group_customercnt_amount_ocnt_per as (
select 지역
, 고객수
, 고객수 / sum(고객수) over() * 100 as 고객수구성비
, 매출액
, 매출액 / sum(매출액) over() * 100 as 매출액구성비
, 주문건수
, 주문건수 / sum(주문건수) over() * 100 as 주문건수구성비
from cte_country_group_customercnt_amount_ocnt
)
-- 지역별 매출 비교
-- 순위-지역([카테고리명]제품명(제품번호))-지역-지역
, cte_country_category_quantity as (
select 지역
, category_name
, product_name
, product_id
, sum(quantity) as total_quantity
from cte_customers cc
left join cte_country_group ccg on cc.country = ccg.국가
group by 1,2,3,4
)
-- 지역-카테고리 quantitiy에 따른 RANK
, cte_country_category_quantity_rank AS (
select *
, row_number () over(partition by 지역 order by total_quantity desc) as rank
from cte_country_category_quantity
)
-- 지역별
, cte_country_category_quantity_rank_dsec as (
select rank
, MAX(case when 지역 = 'NorthAmerica' then '['||category_name||']'||' '||product_name||' '||'('||product_id||')' end) as "NorthAmerica"
, MAX(case when 지역 = 'SouthAmerica' then '['||category_name||']'||' '||product_name||' '||'('||product_id||')' end) as "SouthAmerica"
, MAX(case when 지역 = 'Europe' then '['||category_name||']'||' '||product_name||' '||'('||product_id||')' end) as "Europe"
from cte_country_category_quantity_rank
group by 1
order by 1
-- 강사님은 CASE WHEN으로 먼저 묶고 가져오심!!!!
-- 추후에 따라해보기
)
--고객별 매출액, 주문건수, 건당평균주문액
, cte_customer_quantity_amount as (
select customer_id
, count(distinct order_id) as order_cnt
, sum(amount) as amount
from cte_customers
group by 1
order by 3 desc
)
select customer_id
, order_cnt as 주문건수
, row_number() over(order by order_cnt desc) as 주문건수순위
, amount as 매출액
, row_number() over(order by amount desc) as 매출액순위
, round(amount / order_cnt as 건단평균주문액
, row_number() over(order by amount / order_cnt desc) as 건당평균주문액순위
from cte_customer_quantity_amount
order by 5 asc
2.6 단계 별로 나눠서 살펴보기 Decil
📌 Decil?
Decil : 보통 고객 분석 할때 고객 등급을 나누고 그룹화해서 관리한다고 한다! 이때 사용할 수 있는 그룹화 분석 기법 중 고객을 10단계로 나눠서 분석하는 기법
📌 구하는 순서
1. 고객의 총 매출액 기준 정렬
2. 상위부터 10%씩 나누어 10개의 그룹 할당! 10등분 (ntile? 사용하면 된다고 하심)
3. decil 별 매출합계 (1등급은 ~, 2등급은 ~)
4. decil 별 구성비 (전체 매출 중 구성비)
5. decil 별 구성비 누계 (구성비의 누계!)
ntile : 특정한 컬럼의 값을 기준으로 N개의 등급으로 분류하여 주는 함수 (사용법)
select 컬럼X, 컬럼Y, ...,
ntile(숫자N) over (order by 컬럼K) as 별칭K'
from 테이블A;
📌 ntile 활용해서 decil 분석
with cte_customers as (
select -- customers
c.customer_id
, c.company_name
, c.contact_title
, c.country
, c.city
-- order
, o.order_id
, o.order_date
, to_char(order_date, 'YYYY') as year
, to_char(order_date, 'MM') as month
, to_char(order_date, 'dd') as day
, to_char(order_date, 'q') as quarter
, date_part('dow' ,order_date) as dow
-- order_details
, od.product_id
, od.unit_price
, od.quantity
, od.discount
, round( od.unit_price * od.quantity * (1-od.discount) ) as amount
-- categories
, c2.category_id
, c2.category_name
--, p.product_id as 제품ID order_details와 중복!
, p.product_name
from customers c
INNER join orders o ON o.customer_id = c.customer_id
INNER join order_details od ON od.order_id = o.order_id
inner join products p on p.product_id = od.product_id
INNER join categories c2 on p.category_id = c2.category_id
)
, cte_country_customercnt_amount_ordercnt as (
-- 국가별 고객수, 매출액, 주문건수
select country as 국가
, count(distinct customer_id) as 고객수
, sum(amount) as 매출액
, count(distinct order_id) as 주문건수
from cte_customers
group by country
order by 3 desc
)
-- 상관계수 뽑기
, cte_country_customercnt_amount_ordercnt_corr as (
select corr(고객수, 매출액) as "고객수_매출액"
, corr(매출액, 주문건수) as "매출액_주문건수"
, corr(고객수, 주문건수) as "고객수_주문건수"
from cte_country_customercnt_amount_ordercnt
)
-- 지역 추가하기
, cte_country_group as (
select *
, case
when lower(국가) in ('usa','canada','mexico') then 'NorthAmerica'
when lower(국가) in ('brazil','venezuela','argentina') then 'SouthAmerica'
else 'Europe'
end as 지역
from cte_country_customercnt_amount_ordercnt
)
-- 지역별 고객수, 매출액, 주문건수 집계
, cte_country_group_customercnt_amount_ocnt as (
select 지역
, sum(고객수) as 고객수
, sum(매출액) as 매출액
, sum(주문건수) as 주문건수
from cte_country_group
group by 1
order by 3 desc
)
-- 지역별 고객수, 매출액, 주문건수 구성비
, cte_country_group_customercnt_amount_ocnt_per as (
select 지역
, 고객수
, 고객수 / sum(고객수) over() * 100 as 고객수구성비
, 매출액
, 매출액 / sum(매출액) over() * 100 as 매출액구성비
, 주문건수
, 주문건수 / sum(주문건수) over() * 100 as 주문건수구성비
from cte_country_group_customercnt_amount_ocnt
)
-- 지역별 매출 비교
-- 순위-지역([카테고리명]제품명(제품번호))-지역-지역
, cte_country_category_quantity as (
select 지역
, category_name
, product_name
, product_id
, sum(quantity) as total_quantity
from cte_customers cc
left join cte_country_group ccg on cc.country = ccg.국가
group by 1,2,3,4
)
-- 지역-카테고리 quantitiy에 따른 RANK
, cte_country_category_quantity_rank AS (
select *
, row_number () over(partition by 지역 order by total_quantity desc) as rank
from cte_country_category_quantity
)
-- 지역별
, cte_country_category_quantity_rank_dsec as (
select rank
, MAX(case when 지역 = 'NorthAmerica' then '['||category_name||']'||' '||product_name||' '||'('||product_id||')' end) as "NorthAmerica"
, MAX(case when 지역 = 'SouthAmerica' then '['||category_name||']'||' '||product_name||' '||'('||product_id||')' end) as "SouthAmerica"
, MAX(case when 지역 = 'Europe' then '['||category_name||']'||' '||product_name||' '||'('||product_id||')' end) as "Europe"
from cte_country_category_quantity_rank
group by 1
order by 1
-- 강사님은 CASE WHEN으로 먼저 묶고 가져오심!!!!
-- 추후에 따라해보기
)
--고객별 매출액, 주문건수, 건당평균주문액
, cte_customer_quantity_amount as (
select customer_id
, count(distinct order_id) as order_cnt
, sum(amount) as amount
from cte_customers
group by 1
order by 3 desc
)
-- 고객별 매출액, 주문건수, 건당평균주문액 순위
, cte_customer_ocnt_amount_amountpero_rank as (
select customer_id
, order_cnt as 주문건수
, row_number() over(order by order_cnt desc) as 주문건수순위
, amount as 매출액
, row_number() over(order by amount desc) as 매출액순위
, round(amount / order_cnt) as 건단평균주문액
, row_number() over(order by amount / order_cnt desc) as 건당평균주문액순위
from cte_customer_quantity_amount
order by 5 asc
)
-- 고객당 매출액 및 ntile-등급화
, cte_amount_decil as (
select customer_id, amount
, ntile(10) over (order by amount desc) as decil
from cte_customer_quantity_amount
)
-- decil 별 매출액
, cte_decil_group_amount as (
select decil
, sum(amount) as decil_sum_amount
from cte_amount_decil
group by 1
order by 2 desc
)
-- decil 최종
, cte_decil_amount_per as (
select *
, sum(decil_sum_amount) over() as total
, decil_sum_amount / sum(decil_sum_amount) over() * 100 as decil_sum_amount_rate
from cte_decil_group_amount
)
select *
, sum(decil_sum_amount_rate) over (order by decil) as cumsum
from cte_decil_amount_per
📌 Decil 단점
1. 한 번의 구매로 비싼 물건을 구매한 사용자, 정기적으로 저렴한 물건을 여러 번 구매한 사용자가 같은 그룹으로 판정되는 문제
2. 검색기간이 너무 장기간이면 과거에는 우수고개이었어도 현재는 다른 휴면고객이 포함될 수 있음
3. 또 너무 단기간이면 정기 구매하는 안정된 고객보다 일시적으로 많이 구매한 사용자가 우수고객으로 포함될 수 있음
2.7 RFM 분석
📌 RFM 분석?
Decil 분석의 단점을 보완한 분석 기법이 RFM 분석!
1. 구매 가능성이 높은 고객을 식별하기 위한 데이터 분석 방법
2. 마케팅에서 사용자 타겟팅을 위한 방법
📌 지표 및 우선순위
1. Recency : 얼마나 최근에 구매?
2. Frequency : 얼마나 빈번하게 구매?
3. Monetary : 얼마나 많은 금액을 지불?
📌 구하는 순서
기준일 : orders 테이블의 order_date 최대값으로 하기
R : 기준일 대비 며칠이 지났는가 (MAX(order_date) - order_date)
F : 총구매횟수
M : 총구매액
📌 구하는 순서 1 단계
with cte_customers as (
select -- customers
c.customer_id
, c.company_name
, c.contact_title
, c.country
, c.city
-- order
, o.order_id
, o.order_date
, to_char(order_date, 'YYYY') as year
, to_char(order_date, 'MM') as month
, to_char(order_date, 'dd') as day
, to_char(order_date, 'q') as quarter
, date_part('dow' ,order_date) as dow
-- order_details
, od.product_id
, od.unit_price
, od.quantity
, od.discount
, round( od.unit_price * od.quantity * (1-od.discount) ) as amount
-- categories
, c2.category_id
, c2.category_name
--, p.product_id as 제품ID order_details와 중복!
, p.product_name
from customers c
INNER join orders o ON o.customer_id = c.customer_id
INNER join order_details od ON od.order_id = o.order_id
inner join products p on p.product_id = od.product_id
INNER join categories c2 on p.category_id = c2.category_id
)
, cte_country_customercnt_amount_ordercnt as (
-- 국가별 고객수, 매출액, 주문건수
select country as 국가
, count(distinct customer_id) as 고객수
, sum(amount) as 매출액
, count(distinct order_id) as 주문건수
from cte_customers
group by country
order by 3 desc
)
-- 상관계수 뽑기
, cte_country_customercnt_amount_ordercnt_corr as (
select corr(고객수, 매출액) as "고객수_매출액"
, corr(매출액, 주문건수) as "매출액_주문건수"
, corr(고객수, 주문건수) as "고객수_주문건수"
from cte_country_customercnt_amount_ordercnt
)
-- 지역 추가하기
, cte_country_group as (
select *
, case
when lower(국가) in ('usa','canada','mexico') then 'NorthAmerica'
when lower(국가) in ('brazil','venezuela','argentina') then 'SouthAmerica'
else 'Europe'
end as 지역
from cte_country_customercnt_amount_ordercnt
)
-- 지역별 고객수, 매출액, 주문건수 집계
, cte_country_group_customercnt_amount_ocnt as (
select 지역
, sum(고객수) as 고객수
, sum(매출액) as 매출액
, sum(주문건수) as 주문건수
from cte_country_group
group by 1
order by 3 desc
)
-- 지역별 고객수, 매출액, 주문건수 구성비
, cte_country_group_customercnt_amount_ocnt_per as (
select 지역
, 고객수
, 고객수 / sum(고객수) over() * 100 as 고객수구성비
, 매출액
, 매출액 / sum(매출액) over() * 100 as 매출액구성비
, 주문건수
, 주문건수 / sum(주문건수) over() * 100 as 주문건수구성비
from cte_country_group_customercnt_amount_ocnt
)
-- 지역별 매출 비교
-- 순위-지역([카테고리명]제품명(제품번호))-지역-지역
, cte_country_category_quantity as (
select 지역
, category_name
, product_name
, product_id
, sum(quantity) as total_quantity
from cte_customers cc
left join cte_country_group ccg on cc.country = ccg.국가
group by 1,2,3,4
)
-- 지역-카테고리 quantitiy에 따른 RANK
, cte_country_category_quantity_rank AS (
select *
, row_number () over(partition by 지역 order by total_quantity desc) as rank
from cte_country_category_quantity
)
-- 지역별 판매된 제품 순위
, cte_country_category_quantity_rank_dsec as (
select rank
, MAX(case when 지역 = 'NorthAmerica' then '['||category_name||']'||' '||product_name||' '||'('||product_id||')' end) as "NorthAmerica"
, MAX(case when 지역 = 'SouthAmerica' then '['||category_name||']'||' '||product_name||' '||'('||product_id||')' end) as "SouthAmerica"
, MAX(case when 지역 = 'Europe' then '['||category_name||']'||' '||product_name||' '||'('||product_id||')' end) as "Europe"
from cte_country_category_quantity_rank
group by 1
order by 1
-- 강사님은 CASE WHEN으로 먼저 묶고 가져오심!!!!
-- 추후에 따라해보기
)
--고객별 매출액, 주문건수, 건당평균주문액
, cte_customer_quantity_amount as (
select customer_id
, count(distinct order_id) as order_cnt
, sum(amount) as amount
from cte_customers
group by 1
order by 3 desc
)
-- 고객별 매출액, 주문건수, 건당평균주문액 순위
, cte_customer_ocnt_amount_amountpero_rank as (
select customer_id
, order_cnt as 주문건수
, row_number() over(order by order_cnt desc) as 주문건수순위
, amount as 매출액
, row_number() over(order by amount desc) as 매출액순위
, round(amount / order_cnt) as 건단평균주문액
, row_number() over(order by amount / order_cnt desc) as 건당평균주문액순위
from cte_customer_quantity_amount
order by 5 asc
)
-- 고객당 매출액 및 ntile-등급화
, cte_amount_decil as (
select customer_id, amount
, ntile(10) over (order by amount desc) as decil
from cte_customer_quantity_amount
)
-- decil 별 매출액
, cte_decil_group_amount as (
select decil
, sum(amount) as decil_sum_amount
from cte_amount_decil
group by 1
order by 2 desc
)
-- decil별 매출액 토탈 비율
, cte_decil_amount_per as (
select *
, sum(decil_sum_amount) over() as total
, decil_sum_amount / sum(decil_sum_amount) over() * 100 as decil_sum_amount_rate
from cte_decil_group_amount
)
-- decil별 매출액 토탈 비율 누적비
, cte_decil_amount_per_cumsum as (
select *
, sum(decil_sum_amount_rate) over (order by decil) as cumsum
from cte_decil_amount_per
)
---------- RFM -----------
-- recency 구하기 위한 max(order_date) - order_date 테이블
, cte_customer_maxo as (
select *
, MAX(order_date) over() as maxo
from cte_customers
)
-- rfm
, cte_rfm as (
select customer_id
, max(maxo) - max(order_date) as Recency
, count(distinct order_id) as Frequency
, sum(amount) as Monetary
from cte_customer_maxo
group by 1
order by 2 asc, 3 desc, 4 desc
)
select *
from cte_rfm
3. 과제
3.1 LEFT JOIN 이용한 기본 테이블 사용 분석 복습
1. 시계열
- 월별 매출 증감! 아레 테이블처럼~
- 작대비
- 1997년 분기별 판매수량 TOP 10 - 순위변화
2. 직원
- 직원 별 재주문율
3.2 PIVOT, 고객별 ABC 분석, Z 차트, RFM 분석
1. PIVOT
- 카테고리/제품 - 매출
- 매출 상위 3개 제품 (제품별 매출액)
- 매출순위로 그룹핑 후 카테고리명 뽑기!
2. ABC
3. Z차트
4. RFM 분석
💪🏻 좋았던 점, 앞으로 개선해야 할 점 (추가로 배워야 할 점)
📌 오늘도 윈도우 함수를 활용해서 주요 지표를 확보하는 분석 방안을 학습했다!
새롭게 배운 기법은 ntile 사용한 decil 분석! + RFM 분석 맛보기
점점 익숙해지고 있는 게 느껴지지만 방심은 금물이고 꾸준한 연습이 필요할 거 같다!
그리고 태블로로 시각화하며 깨달았는데, 확실히 SQL에서 정재했을 때 태블로에서 더 편하게 시각화할 수 있는 경우가 존재하기
다양한 쿼리를 시각화해보는 경험으로 cheatsheet? 하나 만들면 좋겠다
#유데미, #유데미코리아, #유데미부트캠프, #취업부트캠프, #부트캠프후기, #스타터스부트캠프, #데이터시각화 #데이터분석 #태블로