[MySQL] 매출 데이터 분석 : 구매지표

Data analysis/SQL +

[MySQL] 매출 데이터 분석 : 구매지표

츔 2022. 8. 10. 16:35

" 구매지표 추출하기 "

1) 매출액(일자별, 월별, 연도별)
2) 구매자  수, 구매  건수(일자별, 월별, 연도별)
3) 인당  매출액(AMV, Average Member Value) (연도별)
4) 건당 구매 금액(ATV, Average Transaction Value) (연도별)
5) 당월  매출 누계
6) 월별  매출의  작대비(작년  대비  비율)
7) Z 차트(업적  추이 확인)

데이터 확인하기

DATA SOURCE ▼

MySQL Sample Database

This page provides you with a MySQL sample database that helps you to practice with MySQL effectively and quickly. You can download the sample database and load it into your MySQL Server.

www.mysqltutorial.org

1) 매출액
(일자별, 월별, 연도별)

• 문자열에서 원하는 부분만 가져오기
• SUBSTR(칼럼, 위치, 길이)

-- [1-매출액-일별 매출액 조회를 위한 결합]
SELECT A.ORDERDATE,                  -- 주문날짜
PRICEEACH*QUANTITYORDERED            -- 매출액 = 개당가격 * 주문 개수
FROM CLASSICMODELS.ORDERS A          -- 주문날짜
LEFT
JOIN CLASSICMODELS.ORDERDETAILS B    -- 개당 가격, 주문개수 들어있는 테이블
ON A.ORDERNUMBER = B.ORDERNUMBER;

> 일별 매출액을 조회하려면 orderdate에 따라 그룹화

-- [2-매출액-일별 매출액 조회]
SELECT A.ORDERDATE,
SUM(PRICEEACH*QUANTITYORDERED) AS SALES
FROM CLASSICMODELS.ORDERS A
LEFT
JOIN CLASSICMODELS.ORDERDETAILS B
ON A.ORDERNUMBER = B.ORDERNUMBER
GROUP BY 1
ORDER BY 1;

> 월별, 연도별 매출액을 조회하려면?

: SUBSTR 함수를 사용해서 문자열 날짜의 월까지 추출한다.

SELECT SUBSTR('2022-08-10',1,7);

-- [4-매출액-월별 매출액 조회]
SELECT SUBSTR(A.ORDERDATE,1,7) MM,
SUM(PRICEEACH*QUANTITYORDERED) AS SALES
FROM CLASSICMODELS.ORDERS A
LEFT
JOIN CLASSICMODELS.ORDERDETAILS B
ON A.ORDERNUMBER = B.ORDERNUMBER
GROUP BY 1
ORDER BY 1;

-- [5-매출액-연도별 매출액 조회] 
SELECT SUBSTR(A.ORDERDATE,1,4) YY,
	   SUM(PRICEEACH*QUANTITYORDERED) AS SALES
 FROM CLASSICMODELS.ORDERS A
 LEFT
 JOIN CLASSICMODELS.ORDERDETAILS B
 ON A.ORDERNUMBER = B.ORDERNUMBER
 GROUP BY 1
 ORDER BY 1;

2) 구매자 수, 구매 건수
(일자별, 월별, 연도별)

• 주의할 점 : UNIQUE FIELD COUNT

예)

고객번호	주문번호	판매일
510730	PU0001	2022.07.05
510730	PU0013	2022.07.05

> 구매건수 확인하기

-- [6-구매자수&구매건수] 
SELECT ORDERDATE,
	   CUSTOMERNUMBER,
       ORDERNUMBER
 FROM CLASSICMODELS.ORDERS;

> 주문번호 중복건 확인하기 DISTINCT

-- [7-주문번호 중복건 확인] 
SELECT COUNT(ORDERNUMBER) N_ORDERS,
       COUNT(DISTINCT ORDERNUMBER) N_ORDERS_DISTINCT
FROM CLASSICMODELS.ORDERS;

> 일별 구매자수 & 구매건수

-- [8-구매자수&구매건수-일별]
SELECT ORDERDATE,
	   COUNT(DISTINCT CUSTOMERNUMBER) N_PURCHASER,
	   COUNT(ORDERNUMBER) N_ORDERS
 FROM CLASSICMODELS.ORDERS
 GROUP BY 1
 ORDER BY 1;

> 월별 구매자수 & 구매건수

-- [8-1 구매자수&구매건수-월별]
SELECT SUBSTR(ORDERDATE, 1, 7) MM,
	   COUNT(DISTINCT CUSTOMERNUMBER) N_PURCHASER,
	   COUNT(ORDERNUMBER) N_ORDERS
FROM CLASSICMODELS.ORDERS
GROUP BY 1
ORDER BY 1;

3) 인당 매출액(AMV, Average Member Value) (연도별)

• 고객의 인당 매출액은 고객의 로열티를 측정하는 요인으로 사용될 수 있다.

📌 로열티(충성도)

↑ 인당 건당 매출액을 확인해봐야 하는 이유

예) 1명이 4000만원을 구매하는 것과 3명이 4000만원을 구매하는 것은 의미가 다르다.

> 연도별 구매자수, 매출액

-- [9-인당매출액-연도별 매출액&구매자수] 
SELECT SUBSTR(A.ORDERDATE, 1, 4) YY,
	   COUNT(DISTINCT A.CUSTOMERNUMBER) N_PURCHASER,
       SUM(PRICEEACH * QUANTITYORDERED) AS SALES
 FROM CLASSICMODELS.ORDERS A
 LEFT JOIN CLASSICMODELS.ORDERDETAILS B
 ON A.ORDERNUMBER = B.ORDERNUMBER
 GROUP BY 1
 ORDER BY 1;

-- [10-인당매출액-9번결과에 매출액을 구매자수로 나누기]
SELECT SUBSTR(A.ORDERDATE, 1, 4) YY,
	   COUNT(DISTINCT A.CUSTOMERNUMBER) N_PURCHASER,
       SUM(PRICEEACH * QUANTITYORDERED) AS SALES,
       SUM(PRICEEACH * QUANTITYORDERED) / COUNT(DISTINCT A.CUSTOMERNUMBER) AMV
 FROM CLASSICMODELS.ORDERS A
 LEFT JOIN CLASSICMODELS.ORDERDETAILS B
 ON A.ORDERNUMBER = B.ORDERNUMBER
 GROUP BY 1
 ORDER BY 1;

4) 건당 구매 금액(ATV, Average Transaction Value) (연도별)

• ATV(Average Transaction Value)
 1건의 거래는 평균적으로 얼마의 매출을 발생시키는가?

-- [11-ATV-건당구매금액 (연도별)]
SELECT SUBSTR(A.ORDERDATE,1, 4) YY,
       COUNT(DISTINCT A.ORDERNUMBER) N_ORDERS,
       SUM(PRICEEACH * QUANTITYORDERED) AS SALES,
       SUM(PRICEEACH * QUANTITYORDERED) / COUNT(DISTINCT A.ORDERNUMBER) ATV
 FROM CLASSICMODELS.ORDERS A
 LEFT JOIN CLASSICMODELS.ORDERDETAILS B
 ON A.ORDERNUMBER = B.ORDERNUMBER
 GROUP BY 1
 ORDER BY 1;

> 날짜별 매출 일시테이블로

WITH 
DAILY_PURCHASE AS (
					SELECT A.ORDERDATE,
						   SUBSTR(A.ORDERDATE, 1, 4) YY,
                           SUBSTR(A.ORDERDATE, 6, 2) MM,
                           SUBSTR(A.ORDERDATE, 9, 2) DD,
						   SUM(PRICEEACH * QUANTITYORDERED) AS PURCHASE_AMOUNT,
                           COUNT(A.ORDERNUMBER) AS ORDER_CNT
					 FROM CLASSICMODELS.ORDERS A
					 LEFT JOIN CLASSICMODELS.ORDERDETAILS B
                     ON A.ORDERNUMBER = B.ORDERNUMBER
                     GROUP BY ORDERDATE
                     )
 SELECT *
  FROM DAILY_PURCHASE
  ORDER BY ORDERDATE;

5) 당월 매출 누계 구하기

• 월별로 목표를 설정해야 될 때에는 해당 월에 어느 정도의 매출이 누적되었는지 동시에 확인할 수 있어야 한다.

# WITH문으로 별도 추출한 컬럼들을 임시 테이블로 따로 빼서 가독성을 높일 수 있다.
# WITH 테이블명 AS (추출 데이터 쿼리)

WITH
DAILY_PURCHASE AS (
                SELECT A.ORDERDATE,
                        SUBSTR(A.ORDERDATE,1,4) AS YY,
                        SUBSTR(A.ORDERDATE,6,2) AS MM,
                        SUBSTR(A.ORDERDATE,9,2) AS DD,
                        SUM(B.QUANTITYORDERED) AS PURCHASE_AMOUNT,
                        COUNT(A.ORDERNUMBER) AS ORDER_CNT
                FROM CLASSICMODELS.ORDERS A
                
                LEFT
                JOIN CLASSICMODELS.ORDERDETAILS B
                ON A.ORDERNUMBER = B.ORDERNUMBER
                
                GROUP
                BY ORDERDATE
				)
SELECT ORDERDATE,
       CONCAT(YY, '-', MM, '-', DD) AS YY_MM_DD,
       PURCHASE_AMOUNT,
       SUM(PURCHASE_AMOUNT) OVER(PARTITION BY YY, MM
                                 ORDER BY ORDERDATE
                                 ROWS UNBOUNDED PRECEDING) AS AGG_AMOUNT
FROM DAILY_PURCHASE
ORDER BY ORDERDATE
;

cf. 반복적으로 나오는 부분은 따로 테이블로 추출해두거나
 CREATE TABLE 테이블명 AS

일시 테이블로 만들어 주면 가독성이 높아진다 -> WITH문

WITH 임시테이블명 AS ( 테이블 생성쿼리)

• Window 함수

MySQL Window Functions: An Essential Guide to Window Functions in MySQL

This tutorial introduces you to MySQL windows functions and provides you with some practical and motivational examples of the windows functions.

www.mysqltutorial.org

https://learnsql.com/blog/mysql-window-functions-examples/

Six Examples Using MySQL Window Functions

What are window functions? How do you use them in MySQL? We discuss their syntax and some ways to implement RANK, DENSE_RANK, ROW_NUMBER, LEAD, and LAG.

learnsql.com

window_function_name(expression) OVER ( 
   [partition_defintion]
   [order_definition]
   [frame_definition]
)

ROWS UNBOUNDED PRECEDING

> 처음부터 내가 지정한 파티션 끝까지 순차적으로 누계 시켜줄 수 있다.

6) 월별 매출의 작대비 : 작년 대비 비율

• 월별 매출 추이를 추출해서 작년의 해당 월의 매출과 비교
• 계절 효과 문제를 해결할 수 있다.

예) 자동차는 안팔리는 월이 있다.

① 월별 매출을 쉽게 파악하기 위한 임시 테이블 생성하기

> 연, 월, 일, 매출, 판매량을 ORDERS 테이블과 ORDERDETAILS 테이블을 조인해서 출력하는데

SELECT A.ORDERDATE,
		SUBSTR(A.ORDERDATE, 1, 4) AS YY,
		SUBSTR(A.ORDERDATE, 6, 2) AS MM,
		SUBSTR(A.ORDERDATE, 9, 2) AS DD,
		SUM(PRICEEACH*QUANTITYORDERED) AS PURCHASE_AMOUNT,
        COUNT(A.ORDERNUMBER) AS ORDER_CNT
 FROM CLASSICMODELS.ORDERS A
 LEFT JOIN CLASSICMODELS.ORDERDETAILS B
 ON A.ORDERNUMBER = B.ORDERNUMBER
 GROUP BY 1;

> 이것을 WITH문으로 임시테이블로 만든다.

② CASE문 사용해서 연도별 총 판매량(PURCHASE_AMOUNT) 구하기

> '04년 매출 / '03년 매출 * 100 으로 작년 대비 매출 % 구해서 RATE_2003_2004 컬럼으로 생성하기

WITH DAILY_PURCHASE AS (
						SELECT A.ORDERDATE,
								SUBSTR(A.ORDERDATE, 1, 4) AS YY,
								SUBSTR(A.ORDERDATE, 6, 2) AS MM,
								SUBSTR(A.ORDERDATE, 9, 2) AS DD,
								SUM(PRICEEACH*QUANTITYORDERED) AS PURCHASE_AMOUNT,
								COUNT(A.ORDERNUMBER) AS ORDER_CNT
						 FROM CLASSICMODELS.ORDERS A
						 LEFT JOIN CLASSICMODELS.ORDERDETAILS B
						 ON A.ORDERNUMBER = B.ORDERNUMBER
						 GROUP BY 1)
 SELECT MM,
		SUM(CASE YY WHEN '2003' THEN PURCHASE_AMOUNT END) AS AMOUNT_2003,
		SUM(CASE YY WHEN '2004' THEN PURCHASE_AMOUNT END) AS AMOUNT_2004,
		100.0 *
		SUM(CASE YY WHEN '2004' THEN PURCHASE_AMOUNT END) /
		SUM(CASE YY WHEN '2003' THEN PURCHASE_AMOUNT END) AS RATE_2003_2004
 FROM DAILY_PURCHASE
 GROUP BY MM
 ORDER BY MM
 ;

> 해석

'03년 대비 '04년 월별로 매출 상승률을 볼 수 있다.

작년 대비 매출 상승률 :

작년 대비 매출 상승률 뿐만 아니라 볼 수 있는 것.

· '03년도 매출 최고/최저 월 : 11월 / 1월· '04년도 매출 최고/최저 월 : 11월 / 4월

7) Z 차트(업적 추이 확인)

• 월차매출, 매출누계, 이동년계 3개 지표로 구성된다.

용어 정의

월차매출

매출 합계를 월별로 집계

매출누계

해당 월의 매출에 이전월까지 매출 누계를 합한 값

이동년계

해당 월의 매출에 과거 11개월의 매출을 합한 값

📌 캐시카우(Cash Cow)

현금을 뜻하는 '캐시'와 젖소를 일컫는 '카우'의 합성어로 표면적으로는 현금 창출원을 말한다.

이 용어는 기업에 적용됐을 때 뜻이 구체화되는데 제품 성장성이 낮아지면서 수익성(점유율)이 높은 산업

z차트 구하기

1) WITH절로 '월차매출, 매출누계, 이동년계' 임시테이블 만들기

· 월차매출

> 일별 매출 구한후, 연·월별로 구해서 매출합으로 구해준다.

-- DAILY_PURCHASE (일별 구매수량)
WITH DAILY_PURCHASE AS (
                        SELECT A.ORDERDATE,
                                SUBSTR(A.ORDERDATE, 1, 4) AS YY,
                                SUBSTR(A.ORDERDATE, 6, 2) AS MM,
                                SUBSTR(A.ORDERDATE, 9, 2) AS DD,
                                SUM(PRICEEACH * QUANTITYORDERED) AS PURCHASE_AMOUNT,
                                COUNT(A.ORDERNUMBER) AS ORDER_CNT
                         FROM CLASSICMODELS.ORDERS A
                         LEFT JOIN CLASSICMODELS.ORDERDETAILS B
                         ON A.ORDERNUMBER = B.ORDERNUMBER
                         GROUP BY ORDERDATE
                         )
 
-- 월차 매출 (일별 매출을 월별로 그룹핑해서 추출)
SELECT YY, MM, SUM(PURCHASE_AMOUNT)
 FROM DAILY_PURCHASE
 GROUP BY YY, MM;

· 매출누계, 이동년계

-- DAILY_PURCHASE (일별 구매수량)
WITH DAILY_PURCHASE AS (
						SELECT A.ORDERDATE,
								SUBSTR(A.ORDERDATE, 1, 4) AS YY,
								SUBSTR(A.ORDERDATE, 6, 2) AS MM,
								SUBSTR(A.ORDERDATE, 9, 2) AS DD,
								SUM(PRICEEACH * QUANTITYORDERED) AS PURCHASE_AMOUNT,
								COUNT(A.ORDERNUMBER) AS ORDER_CNT
						 FROM CLASSICMODELS.ORDERS A
						 LEFT JOIN CLASSICMODELS.ORDERDETAILS B
						 ON A.ORDERNUMBER = B.ORDERNUMBER
						 GROUP BY ORDERDATE
                         ),
 
-- 월차 매출 (일별 매출을 월별로 그룹핑해서 추출)
	MONTHLY_AMOUNT AS (  
						SELECT YY, 
							   MM,
							   SUM(PURCHASE_AMOUNT) AS AMOUNT
						 FROM DAILY_PURCHASE
						 GROUP BY YY, MM
                         )
 
-- 매출 누계, 이동 년계
SELECT YY, MM, AMOUNT,
		SUM(CASE WHEN YY = '2004' THEN AMOUNT END) OVER (ORDER BY YY, MM
														ROWS UNBOUNDED PRECEDING) AS AGG_AMOUNT,
        SUM(AMOUNT) OVER (ORDER BY YY, MM 
                          ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS YY_AVG_AMOUNT
 FROM MONTHLY_AMOUNT
 ORDER BY YY,MM;

> 이동년계에서 WINDOW 함수 사용

MONTHLY_AMOUNT에서 구한 월별 매출을 누계하기 위해

SUM(AMOUNT)를 월별로 정렬한 후 기준행(월) 11개월 전부터의 합을 구한다 < 이동년계

SUM(AMOUNT) OVER (ORDER BY YY, MM

ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS YY_AVG_AMOUNT

2) 임시 테이블 (DAILY_PURCHASE, MONTHLY_AMOUNT, CALE_INDEX) 에서
2004년도의 월별 매출(AMOUNT), 매출누계( AGG_AMOUNT), 이동년계(YY_AVG_AMOUNT) 추출

WITH
	DAILY_PURCHASE AS (
                    SELECT A.ORDERDATE,
                            SUBSTR(A.ORDERDATE,1,4) AS YY,
                            SUBSTR(A.ORDERDATE,6,2) AS MM,
                            SUBSTR(A.ORDERDATE,9,2) AS DD,
                            SUM(PRICEEACH*QUANTITYORDERED) AS PURCHASE_AMOUNT,
                            COUNT(A.ORDERNUMBER) AS ORDER_CNT
                    FROM CLASSICMODELS.ORDERS A
                    LEFT JOIN CLASSICMODELS.ORDERDETAILS B
                    ON A.ORDERNUMBER = B.ORDERNUMBER
                    GROUP BY ORDERDATE
					),		
	MONTHLY_AMOUNT AS (
                    SELECT YY, MM,
                            SUM(PURCHASE_AMOUNT) AS AMOUNT
                    FROM DAILY_PURCHASE
                    GROUP BY YY, MM
						), -- 월별 매출 집계
	CALC_INDEX AS (
                SELECT YY, MM, AMOUNT,
                        SUM(CASE WHEN YY='2004' THEN AMOUNT END)
                            OVER(ORDER BY YY, MM ROWS UNBOUNDED PRECEDING) AS AGG_AMOUNT,
                        SUM(AMOUNT) OVER(ORDER BY YY, MM ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS YY_AVG_AMOUNT
                FROM MONTHLY_AMOUNT
                ORDER BY YY, MM
					)
                    
SELECT CONCAT(YY, '-', MM) AS YY_MM,
		AMOUNT,
		AGG_AMOUNT,
		YY_AVG_AMOUNT
FROM CALC_INDEX
WHERE YY='2004'
ORDER BY YY_MM
;

> 해당 출력 결과를 CSV로 EXPORT 해서 차트로 나타내면,

'Data analysis > SQL +' 카테고리의 다른 글

[MySQL] 매출 데이터 분석 : 구매지표 - 매출액 (0)	2022.08.14
[MySQL] 매출 데이터 분석 : 이탈률 (0)	2022.08.10
[데이터 전처리4] 날짜 다루기2 : Pyspark, Pandas (0)	2022.07.31
[데이터 전처리4] 날짜 다루기1 : MySQL, Postgre (0)	2022.07.31
[데이터 전처리3] 문자열 분해하기 : 계층 추출하기 (0)	2022.07.29

현재글[MySQL] 매출 데이터 분석 : 구매지표

츔로그