SQL 데이터 분석 프로젝트 6일차 - 0718

[성동2기 전Z전능 데이터 분석가] 0718

 

 

SQL 분석 프로젝트 6일차 / 끘.

❇️ 오늘 배운 내용 
1. 가설 7번 완료
2. 팀별 발표
3. 가설 8번 완료

 

 

주요 키워드

 

파레토 법칙	XLOOKUP()	&	도넛 차트
롱테일 법칙	SUM()	사용자 지정 정렬	상관계수

 

---

오전 강의 

 

 

| 가설 7번 완료 

 

나 삽질하다가.... ㅋㅋ

 

와 저거 하나씩 피벗테이블 만들어서 그룹화 하려고 끙끙거렸는데 걍 내가 만들어둔 퍼센트 열 둘다 가져와서 그룹으로 묶으면 되는 거였다... 

 

 

이러다가 

 

 

이케 바꿔서 해결함 

아 이게 훨씬 쉽자나;; 

 

원래 표에서 보니까 주문량이 22개 이상에서 1% 미만으로 떨어짐. 

그래서 각 선택항목 그룹화로 각각 묶었음. .. 

 

 

 

• 멘토님 답변   

7번 가설을 시각화하고 결론을 정리해보니 ...

- 1~5개만 팔린 제품이 88%나 되는데 주문량은 43%임. 그래서 파레토 법칙이 적용되고 롱테일은 조금 되는구나, 했는데 

- 상관계수 해보니까 0.9인 거야. 엄청 강해서 '전체 제품수 대비 N개 팔린 제품의 비율'과 '전체 주문량 대비 N개 팔린 제품의 주문량 비율'의 상관관계가 높다는 거지. 그럼 롱테일이 해당한다는겨 안한다는겨?? 

1개 팔린 제품이 많아진 시기 등 다른 요소도 검증해볼 게 많고...

유의가설? 어쩌고를 해보면 될텐데 암튼 몰으겟어서 멘토님께 질문했다. 

 

• 인기템의 정의를 명확히 내려두기 
• 쇼핑몰을 이끌어갈 캐리템이 없다고 볼 수도 있을듯. 
• 파레토랑 롱테일 중 하나를 정하려 하지 말고 같이 고려하는 전략 ㄱㄱ (하긴 43%면 무시하기 애매함!)
• 매출액으로도 구하라

 

결론
인기템 잘 보여주자(파레토) 
하위템도 잘보여주게 검색 알고리즘도 개선하고 그러자 (롱테일 반영)
구체적인 건 여러 가설 들어서 추가 검증 해야 한다. 

 

 

---

오후 강의 

| 가설 8번 완료 

엑셀 - 사용자 정렬, xlookup (다중조건은 & 사용)

SQL - 그룹 2개에 해당하는 걸 집계하고 싶은데, 하나는 열로 만들고 싶을 때, COUNT 쓰면 그룹바이 필수이므로 ... 

CASE로 TRUE에 1씩 줘서 SUM 하기.

 

 

어제 밤에 메모장에 적어둔 쿼리 실행하고 (예상치 못한 오류 등장 ㅠ 왜지 어케 해결하지;;) 

쿼리 더 완성도 있게 만드려고 이전처럼 고민하기보다 

걍 엑셀로 옮겨서 피벗했다. 

 

 

• 엑셀 사용자 정렬 

이거 안됨;; 

 

 

이렇게 하나하나 다 써야 됨...

 

이건 날짜 기준으로 숫자랑 한국어 단어랑 혼합해 쓴 건데 뭔가 방법 없나? 

글고 보통은 원래 순서를 따르지만 특정 한두개만 위로 정렬하고 싶을 때는? 

 

 

 

 

 

 

 

 

 

 

 

 

 

분산형 차트 만들고 싶었는데 왜 잘 안되냐. 

걍 파이썬으로 하는 게 낫겠다. 

아니 진짜 축이랑 이런 거 왜 내맘대로 안되지. 

화오일에 옆에 분에게 여쭤봐야 할듯 ㅠ 

왜 이런 것도 내맘대로 안되는가........................ 

 

참고: 도넛차트2개 깔아두고 슬라이스로 움직이는 차트 만들기

 

 

 

• XLOOKUP 다중조건 

& 쓰면 됨. 

행으로도 구하고 열로도 구하고 싶으면 답은 다중조건이고 &다! 

 

무엇을 찾으려 하는가, 어디서 찾을 건가, 어디서 가져올 건가 

순서이니까 내 경우는 

= X룩업(답글단거 분류&리뷰 점수,답글 열&리뷰 점수 열, 횟수 카운트 열)

 

이러케도 가능 

 

 

 

 

SQL로 열에 하나씩 집계 다는 법 (조건2개)

 

Case as ㅇㅇ, case 스코어=1 then 카운트 스코어 end as "1점", 2,3,4,5
그룹바이 ㅇㅇ
정렬 ㅇㅇ

 

어젯밤에 이러고 생각해놨던 거 

 

select case
when datediff(o.review_answer_timestamp, o.review_creation_date) <= 1 then "즉시(0~1일)"
when datediff(o.review_answer_timestamp, o.review_creation_date) <= 3 then "2~3일"
when datediff(o.review_answer_timestamp, o.review_creation_date) <= 7 then "일주일 안"
when datediff(o.review_answer_timestamp, o.review_creation_date) <= 14 then "2주 이내"
when datediff(o.review_answer_timestamp, o.review_creation_date) <= 31 then "한달 안"
when datediff(o.review_answer_timestamp, o.review_creation_date) <= 92 then "3개월 이내"
when datediff(o.review_answer_timestamp, o.review_creation_date) <= 180 then "반년 이내"
else "세월아네월아.."
end as day_category,  
case when review_score =1 then count(*) end as "1점", 
case when review_score =2 then count(*) end as "2점", 
case when review_score =3 then count(*) end as "3점", 
case when review_score =4 then count(*) end as "4점", 
case when review_score =5 then count(*) end as "5점", count(*) 
from order_reviews o
GROUP BY 
    day_category, review_score;

이케 했더니 

 

이런 오류가 났다. 

 

이걸 원했는데... 

 

 

 

• 수업 아지트에 질문하려고 했던 것 

 

#질문 #SQL

 

이 코드 어떻게 고쳐야 할까요??

CASE 문 뒤에 ELSE를 생략하니 자동으로 IS NULL 처리되어서 그런 거 같은데 아예 생략하고 싶거든요...

 

 

코드는 이것이고

 

 

결과가 이렇게 나옵니다.

 

 

제가 뽑고 싶은 결과는 이거였습니다.

 

 

사실 이케 뽑으면 간단하지만 ...

 

 

굳이 저렇게 열에 1~5를 두고 싶었어요.

SQL로는 잘 안되어가지고 걍 엑셀로 데이터 데려가서 피벗이랑 X룩업 했습니다.

 

 

더 좋은 방법 없었을까요???

 

 

• 이전에 지피티가 가르쳐준 쿼리문 

select 
    SUM(CASE WHEN review_comment_title is not null and review_comment_message is not null THEN 1 ELSE 0 END) AS Both_not_null,
    SUM(CASE WHEN review_comment_title is null and review_comment_message is null THEN 1 ELSE 0 END) AS Both_null,
    SUM(CASE WHEN review_comment_title is not null and review_comment_message is null THEN 1 ELSE 0 END) AS title_null,
    SUM(CASE WHEN review_comment_title is null and review_comment_message is not null THEN 1 ELSE 0 END) AS message_null,
    count(*)
from order_reviews o ;

이건 된단 말이지? else 0 생략해도 되고. 

 

select 
    CASE WHEN review_comment_title is not null and review_comment_message is not null THEN count(*) else 0 END AS Both_not_null, 
    CASE WHEN review_comment_title is null and review_comment_message is null THEN count(*) else 0 END AS Both_null
from order_reviews o 
group by review_comment_title, review_comment_message ;

참고로 이것도 이상하게 뜸. 

 

그루밥이가 문제라는 생각이 드는디. 

 

 

 

• 쇼킹~!! 스스로 해결 ~!

select case
when datediff(o.review_answer_timestamp, o.review_creation_date) <= 1 then "즉시(0~1일)"
when datediff(o.review_answer_timestamp, o.review_creation_date) <= 3 then "2~3일"
when datediff(o.review_answer_timestamp, o.review_creation_date) <= 7 then "일주일 안"
when datediff(o.review_answer_timestamp, o.review_creation_date) <= 14 then "2주 이내"
when datediff(o.review_answer_timestamp, o.review_creation_date) <= 31 then "한달 안"
when datediff(o.review_answer_timestamp, o.review_creation_date) <= 92 then "3개월 이내"
when datediff(o.review_answer_timestamp, o.review_creation_date) <= 180 then "반년 이내"
else "세월아네월아.."
end as day_category,  
SUM(CASE when review_score =1 then 1 ELSE 0 end) as "1점", 
SUM(CASE when review_score =2 then 1 ELSE 0 end) as "2점", 
SUM(CASE when review_score =3 then 1 ELSE 0 end) as "3점", 
SUM(CASE when review_score =4 then 1 ELSE 0 end) as "4점", 
SUM(CASE when review_score =5 then 1 ELSE 0 end) as "5점", count(*) 
from order_reviews o
GROUP BY 
    day_category;

 

 

트루면 1 펄쓰면 0을 주고 썸하라 시키면 됨... 

COUNT(*)를 쓰면 그룹화하라고 시켜서 

그룹바이에 리뷰 스코어도 넣었더니 절케 뜨는 거임. 

COUNT 넌 대체 뭐냐 곤란한 놈일세. 

 

CASE문에 두르는 SUM은 왜 되고 CASE 문에 THEN으로 등장하는 COUNT는 왜 안되는 거냐? 

걍 COUNT 자체가 그룹바이 필수인 거 같은데 왜지? 카운트만 그래 아님 몇몇 함수가 그래? 

 

 

case when review_score =3 then count(*) end as "3점",

내가 원래 했던 거 이거는 카운트를 쓴 시점에서 그룹바이를 뺄 수가 없음. 

 

 

 

8번 가설 결론

 

8번 가설 시각화해본 결과: 

내가 추측한대로 어떤 경향성이 나오긴 하는데 그게 너무 작아서 별 의미가 없다. 

 

이 경우 상관계수 어떻게 구해야 할지 모르겠다. 

 

결론적으로는... 리뷰 점수 3~5점일 때 내용이 좀더 비어있을 확률 높고 (특히 4점일 때) 
3, 4점일 때 리뷰 답글이 살짝은 더 늦게 달린다. 

1, 2점일 땐 리뷰 내용이 좀더 차있을 확률 높고 
1점일 땐 즉시 답글 달릴 확률이 제일 높지만 
1일을 벗어나면 답글 달리는 속도가 조금은 느려진다. 

 

HMMM... 의미있나 이거 

 

물론 5~4점 내용을 채울 방안(옵션 등)도 제안할 수 있긴 한데 

유의미할 정도로 각각의 수치가 높은 건 아니다. 

셀러가 다양한데도 어쩜 이런 경향성이 있지? 

애초에 왜 여기 쇼핑몰은 리뷰 답글이 꼭 달리는 거지? 

 

사실 셀러별 리뷰 다는 속도와 고객 반응(주문량이나...뭐...) 차이를 알고 싶었어. 

 

 

 

 

| 팀 발표 듣기, 회고 

• 다른 팀 발표 들으면서 메모한 것 

 

 

1. 리뷰 달린 템이든 안달린 템이든 3개월 뒤에 둘다 0.9의 상관관계를 가진다(둘다 매출 올랐다는 가설)에 대한 나의 질문 

• 인기템은 왜 인기가 없는데도 매출이 오른건가요? 

리뷰별, 제품별로 했다기보다는 그냥... 

리뷰달린 템/리뷰 안달린 템 2개로 구분함. 

글래서 리뷰안달린 템들도 판매량 늘엇다고 해둠.

 

• 리뷰 기간이 3개월 단위라는데 데이터 총 기간이 1개월을 넘는다. 언제와 어디를 기준으로 어떻게 잡은건지? 

리뷰 기간은 3개월 단위로 모든 걸 끊어서 카운트, 하나의 데이터셋으로 묶고 그거 전체의 상관계수를 봣다고 함 

즐겨찾기로 들어갈 때가 언노운 

 

 

 

2. 재구매고객과 구매전환율을 어떻게 구하셨나요? 

• 재구매고객 구하는 법 

커스터머유니크아이디(커스터머아이디 하면 주문 테이블에서 중복됨. 그래서 찐 고유값인 유니크 아이디 사용)로다가 count해서 

case 걸고 =1 이면 1번 구매하고 튄 고객, else면 아닌 걸로 치부함 

그런 다음 크리에이티브 테이블함. 안그럼 속도 느려져서. 

 

• 구매전환율 

테이블 중에 WON_DATE라고 언제 샀는지가 있음. 

그거 있는 테이블에 유입경로 ID가 있어서, 마케팅 유입경로 테이블이랑 이어짐. 

조인시켜서 구매날짜가 있는 경로를 살펴봤다.  

 

 

3. 

지형적 차이 - 산악지형 평지지형으로. 산악지형은 유명한 산 있는 곳으로. 주별로 분류

 

 

교육수준 물가 도로 깨끗함 정도 

 

 

 

 

~하고 ~하고 ~하면 점수 높다. Rfm 검증

 

 

데이터 재현성

초반에 데이터 설명 꼭 필요  컬럼 정의서 로우 개수 데이터 기간 등

 

 

 

• ㅍㅍㅍㅍ

 

• 나의 회고... 

 

 

 

 

---

 

여담

 

 

 

 

 

 

---

 

[회고]

 

나는 잘한다는 평가를 듣고 싶었다. 최고로 잘한다 말고 적당히 잘한다고. 

천재 취급은 내 실제 능력과는 다른 평가이기 때문에 부담스러울 것 같고, 내 자신이 오만해질까봐 걱정되니까 원하지 않는다. 

그냥 뭐, 퍼센트로 얘기하면 3~40% 안에 드는 정도? 중상위권 안에 넣었을 때 들어가는 정도? 

말그대로 적당히 잘하는 정도다.

만약 내가 제일 잘하는 경우에도 그걸 몰라줘도 된다. 그냥 뭐, 나를 다른 사람보다 못하다고 여기지 않으면서 얘 정도면(얼마나 잘하는진 모르겠지만 일단 내가 알기로는) 적당히 잘한다, 정도로만 봐줘도 충분하다. 

아 얘 정도면 제 몫은 한다, 믿을만 하다는 평가를 받고 싶은 거다. 

 

 

그리하여 나는 몹시 노력했다. 이 정도면 노력한 축에 들지 않을까 싶다. 

나름대로 퀴즈쇼 상품도 받고(수업 내용으로 

 

1. 자괴감
2. 가설 8개 보상 

 

 

아 쪼매만 더 쓰면 완성하는데 일단 올려둠. 

 

 

 

나름대로 사회성을 위해서 애교를 배웠는데 이게 문제는 

쿨타임이 남들보다 높고 표적이 필요하단 점이다. 

 

저 인디음악 좋아해요~ 

 

그럼 혹시 ㅇㅇ라는 가수 아세요? 

ㅇㅇ라는 가수 아셰요? 

 네알아용 

(팔 흔들면서) 오! 혹시 ㅇㅇ라는 노래 들으세요? 

저 요즘 그 노래에 꽂혔어요!

 

네 그 노래 좋아해요~ 

 

 

---

#청년취업사관학교 #데이터분석가 #데이터분석가부트캠프 #DA교육 #데이터분석교육 #실무프로젝트 #실무경험 #취업포트폴리오 #포트폴리오 #취업연계교육 #코멘토 #모비니티