Sql order 데이터 분석

SQL ORDER 데이터 분석

24년 06~08월 월별 구매 건수

DATE_FORMAT(날짜 , 형식) : 날짜를 지정한 형식으로 출력

  
SELECT DATE_FORMAT(order_date, '%Y-%m') date, COUNT(order_id) count
FROM orders
WHERE order_date BETWEEN '2024-06-01' AND '2024-08-31'
GROUP BY DATE_FORMAT(order_date, '%Y-%m') 
;

| date | count | | :—: | :—: | | 2024-06 | 10462 | | 2024-07 | 9163 | | 2024-08 | 12531 |

8월에 구매건수가 상승했다.

*year(order_date)=2024 AND MONTH(order_date)>=06 AND MONTH(order_date)<=08

24년 06~08월 데이터 중 가장 많은 금액을 지불한 고객 TOP 3

*소수점 없이 출력

상위 고객의 결제 금액에 전체 매출에 영향을 미칠 수 있다.

  
SELECT member_id, FORMAT(MAX(order_price), 0)
FROM orders
WHERE year(order_date)=2024 AND MONTH(order_date)>=06 AND MONTH(order_date)<=08 AND member_id !=''
GROUP BY member_id
LIMIT 3
;

member_id	FORMAT(MAX(order_price), 0)
123	1,690,420
456	1,083,520
789	892,350

가장 비싼 주문 1건을 조회했다.

가장 많이 지불한 고객을 보기 위해선 SUM(order_price)을 사용해야할듯 하다.

24년 06~08월 데이터 중 가장 많이 구매한 고객 TOP 3

충성 고객 식별

  
SELECT member_id, COUNT(order_id)
FROM orders
WHERE order_date BETWEEN '2024-06-01' AND '2024-08-31 23:59:59' AND member_id !=''
GROUP BY member_id
ORDER BY COUNT(order_id) DESC
LIMIT 3
;

member_id	COUNT(order_id)
59595	42
98765	25
82821	17

고액 결제 고객과 구매 횟수 상위 고객이 일치하지 않을 수 있다.

구매 횟수 상위 고객을 중심으로 재구매 유도를 할 수 있다.

*between A and B 는 A 이상 B 이하

하지만 날짜 데이터를 다루는 경우 시분초 데이터까지 생각해야한다.

시분초 없이 날짜만 이용한다면 00시 00분 00초로 자동으로 인식한다.

between A and B : 0.063 초
year, month 쓰는 경우 : 0.078 초
- year(order_date)=2024 AND MONTH(order_date)>=06 AND MONTH(order_date)<=08

BETWEEN을 활용하는게 시간이 더 적게 소요되어 이후에 BETWEEN으로 작성했다.

24년 06~08월 월별 구매건수와 구매액

  
SELECT DATE_FORMAT(order_date, '%y - %m') date, COUNT(order_id) count, 
FORMAT(SUM(order_price), 0) total_price
FROM orders
WHERE order_date BETWEEN '2024-06-01' and '2024-08-31:23:59:59'
GROUP BY DATE_FORMAT(order_date, '%y - %m')
;

date	count	total_price
24 - 06	10462	786,543,210
24 - 07	9163	876,543,210
24 - 08	12531	987,654,320

6월은 구매건수는 7월보다 많지만 구매액은 적었는데 해당 시기에 할인 이벤트가 있었을 수 있다.
→ 상품군별 분석
7월은 구매건수는 감소했지만 평균 구매액이 상승했는데 소수의 고액 구매자가 영향을 준 것으로 보여진다.
→ 고액 결제 고객 분석
8월의 구매건수와 구매액이 모두 증가했다.

24년 6월과 25년 6월 데이터 구매건수 및 구매액 비교

동일 시기의 전년 대비 성과 비교

  
SELECT DATE_FORMAT(order_date, '%y - %m') date, COUNT(order_id) count, 
FORMAT(SUM(order_price), 0) total_price 
FROM orders
WHERE (year(order_date)=2024 AND MONTH(order_date)=06) OR (year(order_date)=2025 AND MONTH(order_date)=06)
GROUP BY DATE_FORMAT(order_date, '%y - %m')
;

date	count	total_price
24 - 06	10462	786,543,210
25 - 06	8521	762,482,320

24년 6월에 비해 25년의 6월 주문량과 구매액이 적은 것을 알 수 있다.

25년 6월은 전년 동월 대비 주문량 19% 감소, 매출 3% 감소했다.

프로모션, 경제 상황 등 파악 필요

주문 사이트 별 총 구매 가격

각 판매 채널의 매출 규모 파악

  
SELECT order_site_id, SUM(order_price) price
FROM orders 
JOIN orderitems
USING (order_id)
GROUP BY order_site_id 
ORDER BY price DESC
;

order_site_id	price
homepage	123,456,789,250
mobile	100,000,000,690
google	85,156,753,710
naver	82,456,750,390
coupang	75,412,689,580
kakao	71,498,273,190
daum	59,268,152,170

홈페이지 구매액이 가장 높다.

채널별 고객 특성 분석을 통해 마케팅 효율 최적화를 진행할 수 있을 것 같다.

REFERENCE

[mysql] DATE_FORMAT - 날짜 형식 설정

Sql order 데이터 분석