요청 1. 주별 주문 수와 고객 수
주(state)별 침투율을 확인하고, 침투율이 특히 낮은 지역을 중심으로 침투율을 개선해보려 합니다. 이를 위해 주별 주문 수와 고객 수를 뽑아주세요
/* 1. 주(state)별 주문 수, 고객 수 추출 */
-- 고객 정보 기준
-- 주문 상태는 필터링 x
SELECT
customer_state,
COUNT(DISTINCT ord.order_id) AS ord_cnt,
COUNT(DISTINCT cust.customer_unique_id) AS cust_cnt
FROM `olist.olist_orders` ord
LEFT JOIN `olist.olist_customers` cust
ON ord.customer_id = cust.customer_id
GROUP BY customer_state
ORDER BY cust_cnt DESC
;
요청 2. 상파울루(SP), 리우데자네이루(RJ)만
주(state)별 주문 수를 뽑아야 하는데, 고객 수 top2인 상파울루(SP), 리우데자네이루(RJ)주만 뽑아주세요
(서브쿼리를 이용한 join 연습)
/* 2. 주(state)별 주문수를 고객수 top2인 상파울루(SP), 리우데자네이루(RJ) 주만 추출 */
SELECT
cust.customer_state,
COUNT(DISTINCT ord.order_id) AS ord_cnt
FROM `olist.olist_orders` ord
LEFT JOIN (SELECT customer_id, customer_state, customer_unique_id
FROM `olist.olist_customers`
WHERE customer_state IN ('SP', 'RJ')) AS cust
ON ord.customer_id = cust.customer_id
WHERE cust.customer_state IS NOT NULL
GROUP BY customer_state
;
- 임시 테이블로 만들고 사용해도 동일한 결과
- 서브쿼리 안에 where절로 인해 SP와 RJ에 해당하지 않는 경우는 NULL값 표시
→ 2가지 주에 해당하는 값만 가져오기 위해 NULL값은 제외하고 불러오는 where절 작성
요청 3. 도시(city)별 주문 수와 고객 수, 매출
지역별 광고 캠페인을 진행해보려고 합니다. 고객 수를 보고 고객이 많은 지역부터 전략을 짜려고 하는데, 이를 위해 2017년의 도시(city)별 주문 수와 고객 수, 매출을 뽑아주세요
/* 3. 2017년 도시별(city)별 주문수와 고객수, 매출 추출 */
-- 고객수 기준 순위
-- 전체 순위, 주(state) 내에서의 순위 둘 다 제공
-- 배송완료(delivered)된 건만 집계
WITH tb AS (
SELECT
order_id,
SUM(price) AS ord_amt
FROM `olist.olist_order_items` AS item
GROUP BY order_id
)
, base AS (
SELECT
cust.customer_state,
cust.customer_city,
COUNT(DISTINCT ord.order_id) AS ord_cnt,
COUNT(DISTINCT cust.customer_unique_id) AS cust_cnt,
SUM(tb.ord_amt) AS ord_amt
FROM `olist.olist_orders` ord
INNER JOIN tb
ON ord.order_id = tb.order_id
LEFT JOIN `olist.olist_customers` cust
ON ord.customer_id = cust.customer_id
WHERE 1=1
AND EXTRACT(YEAR FROM order_approved_at) = 2017
AND order_status = 'delivered'
GROUP BY customer_state, customer_city -- 1,2로 작성 가능 (테이블에서 첫번째, 두번째 컬럼 의미)
)
SELECT
*,
RANK() OVER (ORDER BY cust_cnt DESC) AS cust_rank, -- 도시별 전체 순위
RANK() OVER (PARTITION BY customer_state ORDER BY cust_cnt DESC) AS state_cust_rank -- 주(state)별 순위
FROM base
ORDER BY cust_cnt DESC
요청 4. 월별 평균 소비 금액
우리 고객들은 우리 서비스에 매월 평균적으로 얼마를 쓰고 있나요?
- 객단가 = 주문빈도 * 건당 주문 금액
- ARPU : Average Revenue Per User (매출 / 사용자 수)
- ARPPU : Average Revenue Per Paid User (매출 / 구매자 수)
-- SAFE_DIVIDE 활용
SELECT
DATE_TRUNC(DATE(ord.order_approved_at), MONTH) AS ord_month,
SUM(item.price) AS ord_amt,
COUNT(DISTINCT cust.customer_unique_id) AS cust_cnt,
SAFE_DIVIDE(SUM(item.price), COUNT(DISTINCT cust.customer_unique_id)) AS avg_amt
FROM `olist.olist_orders` AS ord
INNER JOIN `olist.olist_order_items` AS item
ON ord.order_id = item.order_id
LEFT JOIN `olist.olist_customers` AS cust
ON ord.customer_id = cust.customer_id
GROUP BY ord_month
ORDER BY ord_month
- 고객들이 사용하는 금액이 감소하고 있다면
- 구매빈도가 감소하는 것인지 한번에 사용하는 금액이 감소하는 것인지 확인
- 그에 따라 어떻게 대처할지 계획하기도 함
요청 5. 도시(city)별 주문 수와 고객 수, 매출
고객들의 월별 구입금액에 기반해서 그룹을 나누고, 각 그룹별 비중이 변화하는지/유지되는지를 확인하려 합니다.
월 - 고객 unique ID - 해당 월의 구매금액 - 그룹 순서로 보여주세요.
(300BRL 이상은 A, 150BRL 이상 300BRL 미만은 B, 그 외 C)WHERE 1=1 AND order_status IN ('delivered','shipped') AND order_approved_at IS NOT NULL -- 조건 유지
SELECT
DATE_TRUNC(DATE(ord.order_approved_at), MONTH) AS ord_month,
cust.customer_unique_id,
SUM(item.price) AS ord_amt,
CASE WHEN SUM(item.price) > 300 THEN 'A'
WHEN SUM(item.price) >= 150 AND SUM(item.price) < 300 THEN 'B'
ELSE 'C'
END AS level
FROM `olist.olist_orders` AS ord
INNER JOIN `olist.olist_order_items` AS item
ON ord.order_id = item.order_id
LEFT JOIN `olist.olist_customers` AS cust
ON ord.customer_id = cust.customer_id
WHERE 1=1
AND order_approved_at IS NOT NULL
GROUP BY 1,2
ORDER BY ord_month ASC, ord_amt DESC
;
- 평균값으로만 보면 그 안에서 고객들이 어떻게 변화하는지 확인하기 어려울 때가 있는데, 그룹을 나누어 비중의 변화를 보게 되면 고객들의 성격이 어떻게 변하는지도 함께 확인 가능
→ 구매금액이 낮은 고객들이 증가하는지 높은 고객들이 증가하는지 확인 가능
