[👩‍💻TIL 50일차 ] 유데미 스타터스 취업 부트캠프 4기

 

📍 고객분석

• 고객이 누구인지 파악하기 
• 고객들의 어떤 특성이 있는가?
• 특성별로 데이터가 어떻게 분포되어있는가?

B2C: 성별, 연령, 사는 지역

 

 

✔️ 국가별 고객 수, 고객 수 누적 합계

• 고객 수로 내림차순 정렬
• 고객 수가 동일한 경우 국가명으로 오름차순 정렬
• cte구문 사용하지 않고 서브쿼리를 사용하여 작성

-- 누적고객수
select *
	, sum(고객수) over(order by 고객수 desc, 국가명 asc) 누적고객수
from(
	-- 고객수
	select country 국가명
		, count(distinct customer_id) 고객수
	from customers c 
	group by 1
	order by 2 desc
) a;

a

 

✔️ 국가별 고객 수, 구성비, 누적 구성비

• 고객 수로 내림차순 정렬
• 고객 수가 동일한 경우 국가명으로 오름차순 정렬
• cte구문 사용하지 않고 서브쿼리를 사용하여 작성

-- 누적구성비
select 국가명, 고객수, 구성비
	, sum(구성비) over(order by 구성비 desc, 국가명) as 누적구성비
from(
	-- 구성비
	select *
		, sum(고객수) over() 총고객수
		, round(고객수 / sum(고객수) over() * 100, 1) as 구성비
	from (
		-- 고객수
		select country 국가명
			, count(distinct customer_id) as 고객수
		from customers c 
		group by 1
		order by 2 desc
	)a
)b;

a

b

➡️ ABC 분석 가능

 

 

: orders 테이블과 customers 테이블이 0~1관계로 되어있음

• fk가 일반속성: 비식별 관계 (점선)
• 한명의 고객에게는 여러 주문이 존재한다. (할수있다?)
  
  
• 주문이 없는 고객이 있을 수 있다.
• 고객이 없는 주문이 존재할 수 있다.
  
  
• 하나의 주문은 고객이 없거나 한명이다.
• 한명의 고객은 주문이 없거나 여러개다.

 

✔️ 구매이력이 없는 고객

-- left join
select c.customer_id , c.company_name , o.order_id 
from customers c left join orders o 
on c.customer_id = o.customer_id
where o.order_id is null;

 

-- 차집합(except)
(select c.customer_id , c.company_name 
from customers c
order by 1,2)
except
(select c.customer_id , c.company_name 
from customers c , orders o 
where c.customer_id = o.customer_id
order by 1,2);

➡️ customers의 customer_id, company_name

➡️ customers와 orders가 inner join되었을 때의 customer_id, company_name

     한명의 고객은 여러번 주문을 했기 때문에 중복값이 여러개 나타난다.

 

✔️ 주문이 없는 고객을 구하기 위해 차집합(except)

 

 

※ 위에서 구한 left join처럼 order_id도 함께 보고 싶어서 시도해본 결과 다음과 같은 오류가 발생했다.

SQL Error [42601]: 오류: 각각의 EXCEPT query 는 같은 수의 columns 를 가져야 한다.

 

---

고객 구매지표 분석 

📍 기본 테이블 만들기

 

사용할 테이블: customers, orders, order_details, products, categories

 

주문이 없는 고객을 포함시킬 것인가에 따라 count(*)결과 달라짐

➡️ 포함한다면 다 left join, 나중에 null값제외

 

/*******************************************
* 고객분석 (매출, 구매지표, 등급 등)
********************************************/
with 
cte_customers as(
    select c.customer_id , c.company_name , c.contact_title , c.country , c.city 
        , o.order_id , o.order_date 
        , to_char(o.order_date, 'YYYY') as year
        , to_char(o.order_date, 'MM') as month
        , to_char(o.order_date, 'DD') as day
        , to_char(o.order_date, 'Q') as quarter
        , date_part('dow', o.order_date) as dow
        , od.product_id , od.unit_price , od.quantity , od.discount
        , od.unit_price * od.quantity * (1-od.discount) as amount
        , p.product_name 
        , c2.category_id ,c2.category_name 
    from customers c , orders o , order_details od , products p , categories c2 
    where c.customer_id = o.customer_id 
    and o.order_id = od.order_id 
    and od.product_id = p.product_id 
    and p.category_id = c2.category_id 
)

-- 검증
select count(*)
from cte_customers;

 

from customers c 
left join orders o
on c.customer_id  = o.customer_id
left join order_details od
on o.order_id = od.order_id
left join products p
on od.product_id = p.product_id
left join categories c2
on p.category_id = c2.category_id

➡️ left join을 하면 주문 없는 고객이 포함되어 행개수가 2개 늘어난다.

 

📍 국가별 매출액, 고객수, 주문건수 상관계수

-- 국가별 고객수, 매출액, 주문건수 집계
, cte_country_customercnt_amount_ordercnt as(
	select country     
	     , count(distinct customer_id) 고객수
	     , round(sum(amount)) as 매출액
	     , count(distinct order_id) as 주문건수 
	from cte_customers 
	group by 1
	order by 3 desc
)

 

-- 상관계수
, cte_corr_customer_amount_ordercnt as(
	select round(corr(고객수, 매출액)::numeric, 2) as "고객수_매출액"
	     , round(corr(매출액, 주문건수)::numeric, 2) as "매출액_주문건수"
	     , round(corr(고객수, 주문건수)::numeric, 2) as "고객수_주문건수"
	from cte_country_customercnt_amount_ordercnt
)

 

상관계수를 그냥 round 처리하려고 하면 다음과 같은 오류가 발생한다.

SQL Error [42883]: 오류: round(double precision, integer) 이름의 함수가 없음
Hint: 지정된 이름 및 인자 자료형과 일치하는 함수가 없습니다. 명시적 형변환자를 추가해야 할 수도 있습니다.

따라서 numeric으로 형변환 후 round를 해주었더니 알맞게 처리되었다. 

 

 

✔️ 국가별 매출액 지도 시각화

➡️ 고객이 북미, 남미, 유럽에 분포해있음을 알 수 있다. 

 

 

📍 지역별 매출 비교 

-- 지역컬럼 추가('NorthAmerica','SouthAmerica','Europe')
-- NorthAmerica : USA, CANADA, MAXICO
-- SouthAmerica : BRAZILE, VENEZUELA, ARGETINA
-- Europe : 나머지
, cte_country_group as(
	select * 
	     , case 
	     	when lower(country) in ('usa','canada','mexico') then 'NorthAmerica'
	     	when lower(country) in ('brazil','venezuela','argentina') then 'SouthAmerica'
	     	else 'Europe'
	     end as 지역     
	from cte_country_customercnt_amount_ordercnt
)

➡️ country에 대소문자가 섞여있기 때문에 lower로 조건을 걸어줌 

 

 

-- 지역별 집계
, cte_country_group_agg as(
	select 지역
		, sum(고객수) as 고객수
		, sum(매출액) as 매출액
		, sum(주문건수) as 주문건수
	from cte_country_group
	group by 1
	order by 2 desc
)

 

-- 지역별 고객수, 매출액, 주문건수 구성비
, cte_country_group_ratio as(
	select 지역
	     , 고객수, round(고객수/sum(고객수) over(),2) 고객수구성비
	     , 매출액, round(매출액/sum(매출액) over()*100) /100 매출액구성비
	     , 주문건수, round(주문건수/sum(주문건수) over()*100,2) 주문건수구성비
	from cte_country_group_agg
)

 

➡️ 매출액이 계속 round오류가 발생했다. 위와 같은 오류였는데 numeric으로도 해결불가였다. chatGPT에게 물어보니

이렇게 기깔난 해결법을 제시해준다. 덕분에 오류 해결!

 

 

 

지역별 판매된 제품 순위 (판매수량)

[카테고리명], 제품명, (제품번호 product_id)

-- 지역별 판매된 제품 순위
, cte_rank as (
select row_number () over(partition by 지역 order by sum(quantity) desc) as 순위 
	, 지역
	, '[' || category_name || '] ' ||  product_name || ' (' || product_id || ')' 제품정보
	, sum(quantity) 개수
from cte_country_group
where quantity is not null
group by 2,3
)
select 순위
	, coalesce (max(case when 지역 = 'NorthAmerica' then 제품정보 end), '') "North America"
	, coalesce (max(case when 지역 = 'SouthAmerica' then 제품정보 end), '') "South America"
	, coalesce (max(case when 지역 = 'Europe' then 제품정보 end), '') "Europe"
from cte_rank
group by 1
order by 1;

 

 

고객별 매출액, 주문건수, 건당평균 주문

-- 고객별 매출액, 주문건수, 건당평균 주문
, cte_customer as(
select customer_id
	, count(distinct order_id) 주문건수
	, round(sum(amount)) 매출액
	, round(round(sum(amount))/count(distinct order_id))건당평균주문액
from cte_customers
where order_id is not null
group by 1
order by 3 desc
)
, cte_customer_rank as(
select customer_id
	, 주문건수
	, row_number () over(order by 주문건수 desc) as 주문건수순위 
	, 매출액
	, row_number () over(order by 매출액 desc) as 매출액순위 
	, 건당평균주문액
	, row_number () over(order by 건당평균주문액 desc) as 건당평균주문액순위 
from cte_customer
)
select *
from cte_customer_rank
order by 7;

 

시각화

---

Decil 분석

데이터를 10단계로 분할하여 중요도를 파악하는 방법

1. 고객의 총 매출액 기준으로 정렬
2. 상위부터 10%씩 나누어 10개의 그룹 할당 (ntile) = decil
3. decil별 매출액 (decil_sum_amount)
4. decil별 구성비 (decil_sum_amount_rate)
5. decil별 구성비 누계 (cumsum)

 

➡️ decil별 전략을 세울 수 있다.

 

🔎 decil 분석 문제점

• 한 번의 구매로 비싼 물건을 구매한 사용자와 정기적으로 저렴한 물건을 여러 번 구매한 사용자가 같은 그룹으로 판정되는 문제
• 검색기간이 너무 장기간이면 과거에는 우수고객이었어도 현재는 다른 서비스를 사용하는 휴면고객이 포함될 수 있음
• 검색기간이 너무 단기간이면 정기적으로 구매하는 안정고객보다 해당 기간 동안 일시적으로 많이 구매한 사용자가 우수고객으로 포함될 수 있음 

---

RFM 분석

• 구매 가능성이 높은 고객을 식별하기 위한 데이터 분석 방법
• 마케팅에서 사용자 타겟팅을 위한 방법
  • Recency: 얼마나 최근에 구매했는가?
  • Frequency: 얼마나 빈번하게 구매했는가?
  • Monetary: 얼마나 많은 금액을 지불했는가?

 ➡️ 비중은 위 순서대로

 

고려사항: 집계 기간을 어떻게 설정할 것인가? (전체기간)

기준일: orders 테이블의 order_date 최대값 (1998-05-06)

• recency: 기준일 대비 며칠이 지났는가?
• frequency: 총구매횟수
• monetary: 총구매

-- rfm 분석
, cte_rfm as(
select customer_id, order_date, amount, order_id
	, max(order_date) over() 기준일
	, max(order_date) over() - order_date 구매일diff
from cte_customers
)
select customer_id 
	, min(구매일diff) "Recency"
	, count(distinct order_id) "Frequency"
	, round(sum(amount))"Monetary"
from cte_rfm
group by 1;

---

과제

left join 한 기본 테이블을 사용하여 분석

고객별 abc 분석

---

* 유데미 큐레이션 바로가기 : https://bit.ly/3HRWeVL

* STARTERS 취업 부트캠프 공식 블로그 : https://blog.naver.com/udemy-wjtb

📌 본 후기는 유데미-웅진씽크빅 취업 부트캠프 4기 데이터분석/시각화 학습 일지 리뷰로 작성되었습니다.