[TIL-260410] 멋쟁이사자처럼 그로스마케팅 4기 - 데이터분석 개론 day22 | sql데이터로 파이썬 시각화

▼오늘 배운 사항들
1. SQL 복습
- 4/9 실습 시나리오 해설
2. SQL 데이터를 활용한 파이썬 시각화
- my sql & pandas 연결
- matplotlib
3. API & JSON 데이터 확인

 

Part 1. SQL - join & 서브쿼리 & 윈도우 함수 복습

[캔슬 유저의 과거 업그레이드 유무 파악] - case when 구문, 서브쿼리, join 활용

• 사용자를 알아간다고 생각하면 쉬움..
• 캔슬한 유저 중에 업그레이드 경험이 있는지 알아보기 위함
  - 아무것도 경험없이 캔슬 : 온보딩 경험, 서비스 가치 제공 등 마케팅 플랜 수립
  - 경험했는데 캔슬 : 이탈 원인을 확인해보기

유저가 과거에 업그레이드 했는지 알아보는 구문 : cancel한 사람 뽑고 -> 서브쿼리로 엮어서 새로운 컬럼 churn_type 생성

 

[채널별 이탈 현황 파악] - count, group by , join 활용

• 유튜브 채널이 이탈이 가장 높았는데,
  - 왜 이탈이 많았을까? 과대광고한 걸까 타겟이 잘못된걸까 알아내가는 과정

group by는 집계를 위함이니 count컬럼 새로 select 에 넣어주는거 잊지말고

[이탈까지 기간 파악] - join, datediff 활용

• DATEDIFF 를 활용해 이탈까지 며칠 걸렸는지 확인 가능
  - users 테이블의 signup date와 event 테이블의  event type + event date 를 활용해서 cancel date 도출
  - where로 cancel 유저를 필터링 먼저 설정하고
  - date diff로 event date와 signup date를 빼면, 날짜가 나오겠쬬
• 7일 안에 이탈하면 첫 주 경험 개선 강화가 필요 제일 중요한 시기
• Q. 채널별 이탈기간을 비교하려면 어떻게 해야하지? 유튜브/페이스북 등 평균 이탈기간
• Q. 이 데이터가 유용하려면, 전 단계로 plan별 그룹핑 이후에 cancel until을 구하는게 낫지 않나?
  - 유료 이탈기간과 무료 이탈기간을 동일선상에서 비교하기에는 🤔

굉장히 유용한 쿼리잖아!

---

Part 2. my sql & 파이썬 시각화 

• sql 있는 데이터를 pandas로 꺼내서 
• 직접 api 코드 받아서 어디서나 분석할 수 있으니까, 가볍게 분석하고 싶을 때 가능함
• 코드 설명
  - sqlalchemy : 엔진을 만들어서 my sql 서버에 pymysql로 접속하겠다
  - create_engine :
  - text : 

[ my sql과 파이썬 연결]

1. 코랩 sqlalchemy 설치

2. my sql connection info에서 url ssl 코드 전까지 복사해서 연결 url = "" ← 요 사이에 입력

3. pandas로 테이블 불러오기

VS에서도 설치 및 연동 완료

 

• 시각화할 때 한글폰트 필요하면 해당 코드 복붙하고 런타임 - 다시시작 해주기
• 한글폰트 설치안하면, 구글에 한글폰트가 없어서 네모박스로 차트가 깨질 수 있음

---

[ matplotlib 시각화]

• 시각화 차트 만드는 명령어 ▶ 와 신기해~!@~!
• ax.bar(X축 목록, Y축 숫자 목록)
• 채널별로 total conversions 옆에 세우면 좋지 않을까?

# 도화지(fig)와 차트 영역(ax) 만들기
fig, ax = plt.subplots() 

# 여기서 차트를 그립니다
plt.show()

 

[matplotib 핵심 개념]

• matplotlib : 시각화 만들어주는 기본 엔진
• fig = 도화지 전체
• ax = 도화지 위의 차트 영역 
• plt.show() = 화면에 출력

• 함수차트 종류
  

  ax.bar(x, y)	세로 막대	항목 비교할 때
  ax.barh(y, x)	가로 막대	순위 볼 때
  ax.plot(x, y)	선	시간 흐름 볼 때
  ax.pie(y)	파이	비율 볼 때

• import matplotlib.pyplot as plt : import pandas처럼 시각화해주는 matplotlib을 불러온다라는 의미
• ax다음에 es붙여서 여러가지 함수 불러오는거, 1개 도화지에 2개의 그래프를 만들겠다
• axes = plt.subplots (1,2) : 1행에 2열 그래프 만들겠다(행렬 참고)
• market=o은 꼭지점마다 점 표시하기(x, * 변경 가능)

 

 

[실습1&2]

• df로 데이터 프레임 나온 표를 시각화 하고 싶다
  → fig, ax 활용해서 표 만들기
• df[''] 활용해서 x값, y 값넣기

그래프 색은 못바꾸나..? 특정 채널만

 

소름돋게 바로 색상 바꾸는 실습합니다

막대별로는 아직 모르지만...

 

[실습 3-4. 색상 변경 및 차꾸 실습]

• ax.bar()안에 color = '' 명령어 사용해서 색상 변경 가능, #헥사코드 입력 가능
• y값의 매출이 너무 커서 조정 필요할 때

from matplotlib.ticker import FuncFormatter

#y축 숫자를 1,000 단위 콤마로 표시
ax.yaxis.set_major_formatter(FuncFormatter(lambda x, pos: f'{int(x):,}'))

차트꾸미기

 

[실습 5. 월별 매출 선 그래프 구하기]

 

1. dbeaver에서 sql쿼리문 만들기

- [from] 어떤 테이블에서 revenue 가져올지 확인 → daily revenue
- [group by] date_format 활용해서 일자별로 나온 매출일자를 월별로 묶고 select 에 추가

- [select] 월별 총 매출합계도 필요하니 sum()도 select 에 추가

- [order by] 데이터 깔끔하게 파이썬 가져가기 위해서 정렬해두기

 

2. 만든 sql쿼리문을 코랩에서 pd.read_sql()에 넣기

- sql 날짜는 %Y

- 파이썬 날짜는 %%Y로 2개로 변경해야 오류 안뜨고 실행됨

- , engine 명령어로 my sql과 연결짓는 것도 잊지말기

 

 

앞에 직접 데이터를 넣은 월별 매출 추이 그래프와 달리 sql에서 데이터 가져와서 만드는 그래프!

 

 

[VS에서 실습]

• ax.plot() ← 사이에 들어갈 x값, y값을 아까 지정한 df_monthly_revenue에서추출해서 넣는것

[실습 6. 파이차트 만들기]

• 월별 매출 선 그래프와 동일하게 sql 쿼리문 작성해서 ax.pie로 생성
• ax.pie는 비율, label, 퍼센티지 순으로 입력

채널 = ['Instagram', 'Google', 'Facebook', 'YouTube', '기타']
비율 = [30, 28, 20, 15, 7]

fig, ax = plt.subplots(figsize=(7, 7))
ax.pie(비율, labels=채널, autopct='%1.1f%%')
ax.set_title("채널별 유저 비율")
plt.show()

채널별 유저 비율을 알기 위한 파이차트

 

[실습7. 가로 막대 바 그래프 만들기]

• 변수명 헷갈리지말기 df = 랑 df_cpa = 

채널별 cpa를 알기 위한 가로막대 바 그래프

 

[실습8. 신규 구독자 vs 이탈자 비교]

• 신규 구독자 그래프는 선 / 이탈 구독자 그래프는 바로 그래프 2개 나올 수 있도록 만들기
• axes는 차트만들 때 axes[0].plot / axes[1].plot 으로 순번 까먹지 말기
• Q. 루커스튜디오에서 처럼 두 그래프를 겹쳐서 보는건 어떻게 만들지?

axes[1].tick_params(axis='x', rotation=45) #글자가 겹치니까 각도를 조정하는 명령어

신규 구독도 이탈도 전부 5월이 피크네 들어온 만큼 나간건가 나간만큼 들어온건가

 

[실습9. 이벤트 타입별 발생 횟수]

• 사용자가 앱에서 어떤 행동을 가장 많이 하는지 파악할 수 있음
• 데이터 전처리 때 사용하는 슬라이싱 기능 활용해서 막대별로 색 지정
  - 컬러 데이터를 브랜드 톤앤매너 맞추는 경우가 있음

color = colors[:len(df_event)] #여러가지 색을 할 건데 순서대로 dfevent의 수만큼 색지정

※ 슬라이싱

- 필요한 df의 컬럼 수의 바 막대 색상 정하는 것

- [0:2] ▶뒤에있는 숫자만큼 뽑아내는 것.첫번째, 두번째 추출

- [1:3] ▶뒤에있는 숫자만큼 뽑아내는 것. 3번째까지 뽑을건데 0은 제외하고니까 2개나옴

음 실습1에서 하고싶던걸 바로 배웠다 함수이용!

결론 : 실제 DB 데이터로 차트를 그리는 패턴은 항상 동일 

① read_sql로 데이터 가져오기
② 차트 그리기

---

Part 3. API & JSON

[API]

• 마케터로서 API 알고 있으면, 경쟁사 유튜브 댓글 수집 / 키워드 트렌드 수집 등 쉽게 작업할 수 있음
• API 데이터를 가져올 때 JSON으로 포장해서 가져옴

[JSON]

{                     #중괄호 쓰는 JSON
  "author": "김민준", #파이썬은 작은따옴표인데, JSON은 쌍따옴표 사용
  "text": "진짜 좋다!!!",
  "like_count": 42
}

• response.json() : json데이터를 텍스트로 변환해서 파이썬이 읽게하는 코드
  - 작은 따옴표로 바뀐 결과값을 보아 딕셔너리인 걸 알 수 있음
• 이전에 data['title'] pandas 데이터 프레임에서 데이터 뽑아올 때와 똑같음(=딕셔너리에서 값 꺼내는)
  - 대괄호 안에 키값넣어서 딕셔너리에서 값빼는것과 동일하게 데이터 뽑을 수 있음
• data list에 첫번째 값만 보고 싶으면 data_list[0]

json데이터를 텍스트로 바꾸고 데이터 타입도 확인하기

data list에 첫번째 데이터만 뽑기

# 셀 28: 여러 개의 데이터도 한번에 가져오기
response = requests.get("https://jsonplaceholder.typicode.com/posts?_limit=5") #이 주소에 있는 api 값 가져올래 그걸 response에 담아줘
response.text #쌍따옴표 보니 json인 것 같으니 딕셔너리로 바꿔야겟어

data_list = response.json() #그걸 data_list에 담아줘
data_list

• data_list[0]['title'] : 여러개의 데이터가 있는 리스트에서 첫번째 데이터의 타이틀만 뽑고싶을때

대괄호 두번

 

매일이 금요일이면 오늘처럼 이해가 잘될까?^^

아니면 매일 다음날이 주말이면 이해가 잘 되는 걸까^^?

오늘 집중도 잘되고 이해도 잘되고 어제보다 나은 나

역시 배움에 있어서 필요성의 유무를 인지하는 건 중요하고,

sql은 정리하고 다듬기 위한 코드를 배우고, 파이썬은 print 만들어내는게.... 나는 sql보다 파이썬이 훨 재밌고

json..은..

코랩 귀요미