[Ch2] 분석지표 만들기

큰 지표에서 작은 지표로!
총 거래액과 매출

* olist도 셀러가 있는 형태이기 때문에 매출은 수수료로 보는 것이 맞지만, 자사몰로 가정하고, 판매된 총 금액(총거래액)을 매출로 사용

• 예) 배달 치킨집 매출 올리기 전략
  

분석지표

---

• 매출 → 주문수 * 건당주문금액
• 주문수 → 고객수 * 주문빈도
• 건당 주문 금액 → 판매물품수 * 물품평균가격

	의미
매출 a * b	얼마를 벌었나?
⠀a. 주문수 (c*d)	몇 건을 팔았나?
⠀⠀⠀c. 주문고객수	몇 명이나 샀나?
⠀⠀⠀d. 주문빈도	몇 번이나 샀나?
⠀b. 건당 평균 금액 (e*f)	한 건에 얼마 정도?
⠀⠀⠀e. 평균 단가	하나당 얼마 정도?
⠀⠀⠀f. 평균 구매 수량	몇 개나 샀나?

테이블 사용시 유의사항

• Customer_id는 매 주문건마다 새로 생성됨
  • customer_id가 달라도 같은 고객일 수 있음
  • customer_unique_id가 고객 고유 고객번호
  • 고객정보 테이블에 customer_unique_id가 있음
• order_item_id는 주문건내 동일 상품이 있는 경우 증가함
  • 따라서 order_item_id를 모두 더하면 안됨!
  • order_item_id를 세어야 함! (Count)

• 주문 테이블 내 주문건수와 고객번호 수는 일치
• with tablename as( ) : 임시테이블을 만드는 명령어
• from 절로 가져와서 서브쿼리로 사용가능
• 컬럼명을 한글로 할 경우 (``) 백틱 안에 작성

• 주문건수 = customer_unique_id 수 세기
• 총판매상품수 = 판매품목 수세기
• 매출 = 판매가격 전부 더하기

원하는 지표 합치기

• 주문당 정보를 나타내는 테이블을 생성

/* 첫 번째 : 주문 1건당 주문금액, 판매상품의 수를 구합니다 */

WITH tb AS (
  SELECT
    item.order_id,
    SUM(item.price) AS ord_amt,
    COUNT(DISTINCT item.order_item_id) AS prd_cnt
  FROM `olist.olist_order_items` AS item
  GROUP BY item.order_id
)
/* 두 번째 : 주문 정보 테이블에 1번 결과와, 고객 unique ID를 붙입니다 */

, base AS (
  SELECT 
    ord.order_id,
    ord.order_status,
    ord.customer_id,
    cust.customer_unique_id,
    tb.ord_amt,
    tb.prd_cnt
  FROM `olist.olist_orders` ord
  LEFT JOIN `olist.olist_customers` cust
    ON ord.customer_id = cust.customer_id
  INNER JOIN tb
    ON ord.order_id = tb.order_id
)
/* 세 번째 : 2번 결과를 집계합니다 */

SELECT
  SUM(ord_amt) AS `총매출`,
  COUNT(DISTINCT order_id) `총주문수`,
  SUM(prd_cnt) `총판매상품수`,
  SUM(ord_amt) / COUNT(DISTINCT order_id) `주문당평균가격`,
  SUM(prd_cnt) / COUNT(DISTINCT order_id) `평균판매상품수`,
  SUM(ord_amt) / SUM(prd_cnt) `제품개당평균가격`,
  COUNT(DISTINCT customer_unique_id) `주문고객수`,
  COUNT(DISTINCT order_id) / COUNT(DISTINCT customer_unique_id) `주문빈도`
FROM base
;

쿼리 결과