SQL - 매출 데이터 분석

이번 글에서는 데이터를 이용한 분석 실습 방법에 대해 알아보자.

이전 글과 이어지는 내용이니, 데이터 준비를 위해 아래 글을 참조하기 바란다.

SQL - MySQL 데이터 준비

 

0. 사용 Schema 지정

USE classicmodels;

• classicmodels 스키마를 사용

 

 

 

 

 

1. ERD(Entity Relationship Diagram) 파악

• 위 메뉴에서 Database > Reverse Engineer (단축키: Crtl + R)

 

• 현재 연결된 DB설정 후 Next 클릭

 

• 암호 입력 후 DBMS에 연결이 끝나면 Next 클릭

 

• classicmodels를 선택하고 Next 클릭

 

• 암호 입력 후 Retrieve Objects 진행, 완료 시 Next 클릭

 

• objects 설정 체크 후 Execute 클릭

 

• Reverse Engineering 진행, 완료되면 Next 클릭

 

• Finish를 눌러 완료

 

• 표시되는 ERD를 잘 정리하면 다음 그림과 같다.

데이터 테이블간의 관계와 포함되는 컬럼들을 확인할 수 있다.

 

 

 

 

 

2. 연도별 인당 매출액

SELECT 
	SUBSTR(orderdate, 1, 4) YY
    , COUNT(DISTINCT A.customernumber) AS 구매자수
    , SUM(priceeach * quantityordered) AS 매출액
    , SUM(priceeach * quantityordered) / COUNT(DISTINCT A.customernumber) AS AMV
FROM orders A
LEFT
JOIN orderdetails B
ON A.ordernumber = B.ordernumber
GROUP BY 1
ORDER BY 1;

• 주문일자는 orders 테이블에 존재
• 판매액과 판매수량은 orderdetails에 존재
• 두 개의 테이블을 JOIN하여 매출액을 구할 수 있음
• 주문일자(’YYYY-MM-DD’)에서 SUBSTR()를 이용하여 연도만 추출하고,
• SUM()으로 연도별로 매출액을 합산하고,
• COUNT()로 연도별 구매자수를 중복 되지 않게DISTINCT를 적용해 카운트한다.
• (연매출÷구매자수)로 연도별 인당 매출액을 구할 수 있다.
• 마지막으로 GROUP BY를 사용해 주문 연도별로 그룹화한 다음 정렬을 해주면 된다
• (aggregated 쿼리를 할 때 그룹화를 하지 않으면 Error Code: 1140 발생!)

 

 

 

 

 

3. 건당 구매 금액; ATV(Average Transaction Value)

주문 데이터베이스에서 연도별 구매 건수, 총 매출액 및 평균 거래 금액를 계산하는 작업을 수행해보자. 이는 VIP 고객을 선정하는데 좋은 지표가 된다.

SELECT
	SUBSTR(A.orderdate, 1, 4) YY
    , COUNT(DISTINCT A.ordernumber) AS 구매건수
    , SUM(priceeach * quantityordered) AS 매출액
    , SUM(priceeach * quantityordered) / COUNT(DISTINCT A.ordernumber) AS ATV
FROM orders A
LEFT
JOIN orderdetails B
ON A.ordernumber = B.ordernumber
GROUP BY 1
ORDER BY 1;

• 주문 날짜("orderdate")에서 연도 정보를 추출하여 "YY" 컬럼으로 표시
• "구매건수" 열: 고유 주문 번호("ordernumber")를 세어 해당 연도의 구매 건수를 계산
• "주문 내역("orderdetails")의 제품 가격("priceeach")과 주문 수량("quantityordered")을 곱한 합계로 해당 연도의 총 매출액을 계산하고 매출액 컬럼으로 표시
• 총 매출액을 해당 연도의 구매 건수로 나눈 결과로, 평균 거래 금액(Average Transaction Value)를 계산하고 ATV 컬럼으로 표시
• "orders" 테이블과 "orderdetails" 테이블을 "ordernumber" 열을 기준으로 왼쪽 조인하여 데이터를 조합
• 연도별로 그룹화하고, "YY" 열을 기준으로 오름차순으로 정렬

 

 

 

 

 

4. 재구매율/재사용률 (Retention Rate)

국가별로 연도별 재구매율을 계산해보자.

이 지표는 지속적인 매출을 파악하는데 유용하다.

SELECT 
	C.country
    , SUBSTR(A.orderdate, 1, 4) YY
    , COUNT(DISTINCT A.customernumber) BU_1
    , COUNT(DISTINCT B.customernumber) BU_2
    , COUNT(DISTINCT B.customernumber) / COUNT(DISTINCT A.customernumber) AS 재구매율
FROM orders A
LEFT
JOIN orders B
ON A.customernumber = B.customernumber 
	AND SUBSTR(A.orderdate, 1, 4) = SUBSTR(B.orderdate, 1, 4) -1
LEFT
JOIN customers C
ON A.customernumber = C.customernumber
GROUP BY 1, 2
;

• 국가 정보를 "C.country" 컬럼으로 표시
• 연도 정보를 "YY" 컬럼으로 표시
• 해당 연도에 주문한 고객의 수를 "BU_1" 컬럼으로 표시
• 전년도에 주문한 고객의 수를 "BU_2" 컬럼으로 표시. 이는 고객 번호가 같은 현재 연도의 주문과 연결됨.
• 재구매율은 전년도에 구매한 고객 수를 현재 연도에 주문한 고객 수로 나눈 것으로 정의하고 "재구매율" 컬럼으로 표시
• "orders" 테이블과 "customers" 테이블을 조인하여 고객 정보를 가져온다.
• "A.customernumber"와 "B.customernumber"를 조인하여 현재 연도와 전년도 주문을 연결한다.
• 결과를 국가와 연도별로 그룹화하여 표시

 

 

 

 

 

5. 이탈률 (Churn Rate)

이탈률은 기존 사용자나 고객들이 이탈한 비율을 나타낸다.

보통 온라인 쇼핑몰을 일정 기간 동안 이용하지 않으면 휴면 계정으로 전환되는 것은 다들 알고있을 것이다. 이는 개인정보 보호 목적으로 적용되는 것이긴 하나 마케팅 분야에서는 기존 고객이 이탈했다는 정보로 활용될 수 있다.

해당 고객이 구매한 목록과 시점, 리뷰등을 분석하여 의미 있는 정보를 얻을 수도 있어 이탈률은 분석에 많이 활용되는 지표이다.

SELECT
    CASE WHEN DIFF >= 90 THEN 'CHURN' ELSE 'NON-CHURN' END CHURN_TYPE
    , COUNT(DISTINCT customernumber) N_CUS
FROM (
	SELECT
		customernumber
		, MX_ORDER
		, '2005-06-01'
		, DATEDIFF('2005-06-01' , MX_ORDER) DIFF
	FROM (
	SELECT
		customernumber
		, MAX(orderdate) MX_ORDER -- 테이블에서 마지막 구매일 (GROUP BY 없을 시 전 고객 기준)
	FROM orders
	GROUP BY 1 -- (GROUP BY 1 사용시 각 고객 기준)
	) BASE
) BASE2
GROUP BY 1
;

• CASE WHEN을 사용해 90일 이상 주문내역이 없을 시 이탈인 "CHURN"으로, 아닐시 미이탈인 "NON-CHURN"으로 분류하고 CHURN_TYPE 컬럼으로 표시.
• 고객 번호("customernumber")를 고유하게 세어 해당 카테고리의 고객 수를 계산하고 "COUNT(DISTINCT customernumber)" 컬럼으로 표시
• 중첩 서브쿼리(BASE)를 통해 최근 구매일자("MX_ORDER")를 구한다.
  • "orders" 테이블에서 각 고객의 최근 주문일을 찾아, "MAX(orderdate) MX_ORDER" 컬럼으로 표시하고 정보를 고객번호별로 그룹화함
• 서브쿼리(BASE)를 통해 최근 주문일("MX_ORDER")과 '2005-06-01' 사이의 날짜 차이를 DATEDIFF()로 계산하여 "DIFF" 컬럼으로 표시

Churn Rate = 69 / (69 + 29) * 100 % = 약 70.4 % 이다.