기초 분석 팀 과제 aka.팀프로젝트 맛보기

목적 : “제공된 주제를 선택하여 SQL을 활용해 EDA를 해봅시다.”

- EDA(Exploratory Data Analysis) 
- 데이터를 한 번 여러 방향으로 뜯어보기
- 전체적으로 -> 개별 속성값 별로 -> 속성 간 관계 분석

이머커스 데이터를 활용
대주제 : 구매횟수별 사용자 이용패턴
세부주제 : 각자 시간대별, 가격별, 브랜드별 등

나는 시간대별 분석

---

- 구매 횟수별 시간대 패턴 있는지

# 시간대 별 구매횟수 (퍼센트 순위 1만 조회)

SELECT 	aa.시간,
		aa.구매횟수,
		aa.고객수,
		aa.퍼센트순위
FROM
(select DATE_FORMAT(event_time,'%H') as 시간,
		case when	purchase_count >=2 then 2                # 구매 횟수 0, 1, 2로 분류 
			 when	purchase_count = 1 then 1
			 else	0 end as 구매횟수,
	  	COUNT(*)	as 고객수,
	 	PERCENT_RANK()	over (partition by DATE_FORMAT(event_time,'%H')
        order by COUNT(*)) as 퍼센트순위
from	
(
select	user_id,
		event_time,
		'purchase'	event_type,
		SUM(CASE WHEN	event_type = 'purchase' THEN 1       # 구매했냐 안 했냐 0, 1 구분 후 더함 
				 ELSE	0 END)	purchase_count
from 
	(select * from `2019_oct` o
	union all
	select * from `2019_dec` d
	union all
	select * from `2019_nov` n
	union all
	select * from `2020_feb` f                                 # 테이블 합체 
	union all
	select * from `2020_jan` j) aaa
where	brand != '' 
		and	user_session != ''
group by	1,2 ) a
group by	1,2
order by	1 ) aa
where	aa.퍼센트순위 = 1                        # 여기만 != 바꾸면 나머지 확인 가능
order by 2,1 ;

→ 0회 구매 고객수 19시에 제일 많이 활동

→ 1, 2회 이상 구매자 11시에 제일 많이 활동

- 시간대별 매출

with                                       # 정신없어서 with로 묶음
months_5 as (select * from `2019_oct` o
union all
select * from `2019_dec` d
union all
select * from `2019_nov` n
union all
select * from `2020_feb` f
union all
select * from `2020_jan` j)
SELECT	DATE_FORMAT(event_time,'%H')time,  # 시간만 조회
		count(*) as purchase_count,
		round(sum(price),4) total_price,       #값이 지저분해서 적당히 자름
		round(avg(price),4) avg_price 
from	months_5
GROUP BY	1;

- 시간대별 고유 유저의 수 

SELECT 
	DATE_FORMAT(event_time,'%H') as 시간,
	count(distinct user_id) as 	고유_유저수 
FROM
	online_commerce oc 
group by
	DATE_FORMAT(event_time,'%H');

→ 12시가 MAX

- 시간대별 이벤트 유형 고객수

SELECT
	DATE_FORMAT(event_time,'%H') as 시간,
	event_type,
	count(*) as 고객수
FROM
	online_commerce oc 
group by
	DATE_FORMAT(event_time,'%H'), event_type
order BY 
	event_type, 시간 ;

→ 구매 MAX : 11시

→ 방문, 장바구니 삭제 MAX : 19시

---

이렇게 시간대별을 크게? 알아보다가 튜터님 두 분께 방향성 상담을 받았는데, 너무 다양하고 깊지 않다는 말을 듣었다. 그래서 다시 방향성에 대해 회의..
했다가 매니저님이 이번 과제는 이렇게까지 깊게 분석하지 말고 배운 내용 복습 차원에서 데이터를 이러저리 뜯어보는 연습하는 과제라라고 해서 조금 맘놓고 분석을 하기로 했다.

---

은수님이 전체적인 일별 매출을 구해주셨는데

→ 이와 같이 이상치가 상승구간에서 4개 정도, 하락구간에서 1개가 나타났다.
나는 하락구간에 집중해서 시간대별로 분석을 진행했다.

- 정확히 어느 지점에서 매출이 떨어지는지 확인

SELECT 
	DATE(event_time) AS day,
	sum(price)  
FROM 
	(select * from `2019_dec` d
	union all
	select * from `2020_jan` j) a
WHERE 
	event_type = 'purchase' 
GROUP BY 1;

→ 2019년 12월 31일에 매출이 급격하게 하락

- 고객수와 매출 시간대 별로 비교 (12월 30일 vs 12월 31일)

# 12월 31일 시간대 별 고객수와 총매출

SELECT
	HOUR(event_time),
	count(distinct user_id),
	sum(price) 
FROM
	online_commerce oc 
WHERE 
	event_time between '2019-12-31 00:00:00' and '2019-12-31 23:59:59'
group by
	HOUR(event_time)
order by 
	HOUR(event_time) ;

→ 12월 30일과 비교했을 때, 12월 31일에 전체적으로 고객수와 총매출이 감소한 것을 볼 수 있음

- 시간대별 이벤트 유형

→ 전체 유형이 전반적으로 12월 31일이 낮게 나옴
→ 이상치 : 오전에 12월 31일 구매횟수가 많이 찍혀있음

- 시간대별, 상품별 구매횟수

# 12월 31일 오전 6-8시 사이 상품별 구매횟수

SELECT
	hour(event_time) 시간,
	product_id,
	count(*) 구매횟수 
FROM
	`2019_Dec` d 
where
	hour(event_time) between 6 and 8
	and date(event_time) = '2019-12-31'
	and	event_type ='purchase'
group by
	hour(event_time), product_id
order by
	구매횟수 desc ;

→ 대부분 1회 구매이고 구매횟수 많은게 2회라 특정 상품에서 쿠폰을 뿌린 것 같지는 않음,,

→ 상품이 아니라 오전 6-8시에 어떠한 이벤트가 있었을 수도?

---

- 전체적인 일별 매출 흐름을 봤을 때 급격하게 매출이 떨어진 구간을 분석함 
- 시기가 코로나와 겹치는 것 같아서 코로나 영향일까 가설을 세우고 분석을 진행
 → 고객 유입수와 구매수, 총매출 모든게 전체적으로 감소해서 매출이 떨어짐 (코로나 영향인지는 다른 자료를 더 찾아봐야 알 것 같음)
 - 이벤트 유형을 분석하다가 재미있는 이상치를 발견함. (전체적으로 감소했지만 12월 31일 오전에 구매횟수가 30일보다 훨씬 많은 것을 발견할 수 있었음)
 → 특정 상품이 많이 구매된 것은 아니였음. 오전 시간에 이벤트가 있었던 것으로 예상

---

 매출이 하락됐을 경우
→ 유저 수가 빠진건지 (언제, 얼마나 빠졌는지)
→ 단가가 낮아진건지
→ 구매량이 적어진건지 등

→ 유저 수가 급락한 시점과 매출 급락 시점이 일치하는지 (시간대별, 날짜별)

이커머스 데이터라고 다 매출이 목표는 아님
→ 이번 이커머스 데이터는 로그 데이터라 사용자 이용패턴이 목표