1. 매출 = 주문 수 * 건당 주문 금액
매출은 비즈니스 지표 중에서 가장 큰 지표라고 생각할 수 있다. 따라서 매출은 '판매 금액 총합'으로 구할 수 있다. 매출에는 '주문 수'와 '건당 주문 금액'을 구해야한다.
'주문 수'는 '주문 건수 세기'라고 생각하면 되며 쉽게 말해 고유한 주문 건수이다. 한번의 주문 건수에 대해서는 한번만 세야한다. '건당 주문 금액'은 '판매 금액/주문 건수'로 구할 수 있다.
1.1 SQL 쿼리 실습
1) SQL 쿼리 만들기 클릭
Google BigQuery에서는 Python 코드를 입력할 때 따옴표(')가 아닌 백틱(`)를 쓴다는 점 유의해야 한다.
SELECT FROM `olist.olist_order_items`
SELECT
SUM(price) AS ord_amt, -- 총 매출
COUNT(DISTINCT order_id) As ord_cnt, -- 주문 건수
SUM(price) / COUNT(DISTINCT order_id) As amount_per_order -- 주문 1건당 평균 가격
FROM `olist.olist_order_items`;
2. 주문 수 = 고객 수 * 주문 빈도
'주문 수'는 '고객 수'와 '주문 빈도'로 나눌 수 있다. '주문 수'는 '주문 건수 세기'로 구할 수 있고, '고객 수'는 '유니크한 고객 수 세기'라고 생각하면 된다. 즉, 한명의 고객이 2번 주문을 했더라도 세는 단위는 고객 한명이여야 한다라는 의미이다.
2.2 SQL 쿼리 실습
한글 별칭을 사용하고 싶다면 백틱(`)을 사용하여하며 백틱을 쓰지 않는 경우, 에러가 발생한다.
COUNT(DISTINCT ord.order_id) AS `주문건수`
SELECT
COUNT(DISTINCT ord.order_id) AS `주문건수`, -- 주문건수 세기
COUNT(DISTINCT cust.customer_unique_id) AS `주문고객수`, -- 고객 고유 ID 세기
COUNT(DISTINCT ord.order_id) / COUNT(DISTINCT cust.customer_unique_id) AS `주문빈도` -- 주문 빈도 수
FROM `olist.olist_orders` As ord
LEFT JOIN `olist.olist_customers` As cust
ON ord.customer_id = cust.customer_id
;
쿼리 저장하는 방법:
1) 상단의
저장버튼 클릭
2)쿼리 저장(기존)선택
3) 이름 입력
4)저장버튼 클릭
왼쪽 패널에(기존) 쿼리에 쿼리가 정상적으로 저장된 것을 확인할 수 있다.
3. 건당 주문 금액 = 주문 건당 판매 품목 수 + 물품 평균 가격
'건당 주문 금액'을 '주문 건당 판매 품목 수'와 '물품 평균 가격'으로 나눠보려고 한다. '평균 얼마 정도의 제품을 구매할까?'라는 질문은 고객들이 '한 건에 몇 개 샀지?'와 한 건 주문한해서 '개당 얼마하지?'로 나눠보는 것이다.
'건당 주문 금액'은 '매출/주문 건수'로 구할 수 있고, '주문 건당 판매 품목 수'는 한 개의 주문건이 평균적으로 갖고 있는 판매된 상품 수이므로 '총 판매 상품 수/주문 건수'로 나누어주면 된다. 그리고 '물품 평균 가격'은 '총 매출/총 판매 상품 수'로 나누면 상품 한 개당 평균 상품 수를 구할 수 있다.
3.2 SQL 쿼리 실습
/*는 여러 줄의 주석을 달 때 사용할 수 있다./* 총 매출, 주문건수 주문 1건당 평균 가격 */
SELECT
SUM(price) AS `총 매출`,
COUNT(DISTINCT order_id) AS `총 주문수`,
COUNT(order_item_id) AS `총 판매상품수`,
SUM(price) / COUNT(DISTINCT order_id) AS `주문당평균가격`,
COUNT(order_item_id) / COUNT(DISTINCT order_id) AS `평균판매상품수`,
SUM(price) / COUNT(order_item_id) AS `제품개당평균가격`
FROM `olist.olist_order_items` AS ord
;
4. 종합
WITH tb AS (
SELECT
item.order_id,
SUM(item.price) AS ord_amt,
COUNT(item.order_item_id) AS prd_cnt
FROM `olist.olist_order_items` AS item
GROUP BY item.order_id
)
, base AS (
SELECT
ord.order_id,
ord.customer_id,
cust.customer_unique_id,
tb.ord_amt,
tb.prd_cnt
FROM `olist.olist_orders` AS ord
LEFT JOIN `olist.olist_customers` AS cust
ON ord.customer_id = cust.customer_id
INNER JOIN tb
ON ord.order_id = tb.order_id
)
SELECT
SUM(ord_amt) AS `총매출`,
COUNT(DISTINCT order_id) AS `총주문수`,
SUM(prd_cnt) AS `총판매상품수`,
SUM(ord_amt) / COUNT(DISTINCT order_id) AS `주문당평균가격`,
SUM(prd_cnt) / COUNT(DISTINCT order_id) AS `평균판매상품수`,
SUM(ord_amt) / SUM(prd_cnt) AS `제품개당평균가격`,
COUNT(DISTINCT customer_unique_id) AS `주문고객수`,
COUNT(DISTINCT order_id) / COUNT(DISTINCT customer_unique_id) AS `주문빈도`
FROM base
;
