221014 AI스쿨 TIL SQL 특강 마지막날

21일차 멋사 AI스쿨 special lecture by 데이터리안

 

인프런 SQL 중급 강의 (JOIN, UNION)

중급 문제 풀이 (solveSQL, 해커링크, 리트코드)

-> SQL 공부방

 

매출 분석

ARPU = 매출 / 전체 유저 수  (Average Revenue Per User)

ARPPU = 매출 / 결제 유저 수  (Average Revenue Per Paying User)

매출 = 결제 유저 수  X ARPPU

매출 = 유저 수 X 결제자 비율  X ARPPU

전체유저수 / 결제 유저의 비율 / 인당 결제액 등 어떤 변수가 전체 매출 변화에 영향을 미치는 지 파악해야한다.

 

AARRR

서비스 성장 분석 방법론

• A(Acquisition, 획득): 광고 등의 방법으로 새로운 사용자를 얻어오는 단계
• A(Activation, 활성화): 회원 가입, 튜토리얼 등 사용자를 활성화 시키는 단계
• R(Retention, 리텐션): 지속적으로 서비스를 사용하게 만드는 단계
• R(Revenue, 매출): 매출을 만드는 단계
• R(Referral, 추천): 다른 사용자에게 제품을 추천하는 단계

리텐션 분석 관련 더 읽어보기: https://www.datarian.io/blog/classic-retention 

EDA (탐색적 데이터분석)

SQL환경에서는 데이터베이스간의 관계, 각각의 DB의 컬럼명, 자료 형태, 프라이머리키, 외래키 등 확인하는 것 필요하다.

날짜 자료형은 표현할떄 시분초단위까지 고려하기 ex) between "2017-01-01 00:00:00" and "2017-12-31 23:59:59"

실제 DB자료는 엄청 크기 때문에 select * from table은 거의 네버 쓸 일 없을 것.

WHERE절에 날짜를 지정해서 가져오거나 LIMIT (N)을 붙여서 쿼리문 쓰기

매출 트렌드

(시각화-> 구글 스프레드시트)

실무에서는 구글스프레드시트의 피벗테이블, 시각화 기능을 병행해서 사용하는 경우가 많다.

 

실전 문제

1. 특정 기간의 일일 매출 데이터 구하기

SELECT DATE(o.order_purchase_timestamp) AS dt
    , ROUND(SUM(p.payment_value), 2) revenue_daily
FROM olist_orders_dataset AS o
    INNER JOIN olist_order_payments_dataset AS p ON o.order_id = p.order_id
WHERE o.order_purchase_timestamp BETWEEN '2017-05-01 00:00:00' AND '2017-11-19 23:59:59'
GROUP BY dt
ORDER BY dt

2. 특정 기간의 일별 결제 유저(PU: paying user)수, 매출액, ARPPU 구하기

SELECT DATE(o.order_purchase_timestamp) AS dt
     , ROUND(SUM(p.payment_value), 2) revenue_daily
     , COUNT(DISTINCT c.customer_unique_id) PU
     , ROUND(SUM(p.payment_value) / COUNT(DISTINCT c.customer_unique_id), 2) ARPPU
FROM olist_orders_dataset AS o
    INNER JOIN olist_order_payments_dataset AS p ON o.order_id = p.order_id
    INNER JOIN olist_customers_dataset AS c ON o.customer_id = c.customer_id
WHERE o.order_purchase_timestamp BETWEEN '2017-05-01 00:00:00' AND '2017-11-19 23:59:59'
GROUP BY dt
ORDER BY dt

 

고객세분화(customer Segmentation) 분석

고객 세분화 분석이란, 전체 유저를 여러 기준으로 그룹으로 나누어, 그룹 특성을 보는 것이다.

기준: 인구통계학적 정보, 지역 정보, 결제 정보, 가입 시기, 유입 경로 등

코호트 분석이란? 고객 세분화를 '시간의 흐름' (ex. 가입 시기)으로 하는 것

참고자료: 데이터리안 - 코호트 분석 Cohort Analysis

 

실전 문제

Q. 특정기간 고객 거주지(state)별 일일 매출액 구하기.

매출 top3 지역의 일일 매출액과 일일 전체 매출액에서 차지하는 비중 구하기

SELECT DATE(o.order_purchase_timestamp) AS dt
     , c.customer_state
     , ROUND(SUM(p.payment_value), 2) revenue_daily
FROM olist_orders_dataset AS o
    INNER JOIN olist_order_payments_dataset AS p ON o.order_id = p.order_id
    INNER JOIN olist_customers_dataset AS c ON o.customer_id = c.customer_id
WHERE o.order_purchase_timestamp BETWEEN '2017-11-01 00:00:00' AND '2017-11-19 23:59:59'
GROUP BY dt, c.customer_state
ORDER BY dt, c.customer_state

 

KPT 회고

Keep (이번에 잘했고, 앞으로도 이렇게 하자)

Problem (문제가 있었거나, 개선이 필요한 부분)

Try (다음에는 이런 걸 시도해보면 어떨까)

참고자료: [인프런 블로그] 개발자의 공유 문화 이모저모 (2) 회고 문화

 

오늘은 새로 알게 된 KPT 회고 형식으로 이번주를 회고해봐야겠다.

 

---

10/10~10/14

벌써 4주차! 이번 주도 수고 많았다. 미니프로젝트2와 미드프로젝트를 앞두고 있어 마음이 매우 바쁘지만 즐기자!!!

 

Keep

- TIL에 쓸 내용을 수업 들으면서 바로바로 정리하니까 수업 집중도도 높아지고, 정리해야 한다는 부담감도 줄어든다.

- 파이썬 문제 풀이 스터디를 통해서 코딩 테스트 연습을 한 것 

Problem

- ADsP 시험을 준비하려고 하다보니 수업 복습과 시험 준비 사이에서 조금 갈팡질팡

- 블로그에 정리하고 싶은 내용이 너무 많은데, 한 번 글을 쓸때 분량이 많아지다보니 미루게 됨

Try

- 블로그 정리하고 싶은 내용은 밀리지 말고 그날 공부가 완성이 덜 되었더라도 최대한 글 쓰기, 분량을 짧게 여러개를 쓰기

- 실습 파일을 다시 처음부터 혼자 해보는 연습을 해봐도 좋을 것 같다. (의약품 처리정보)

- 미니프로젝트1 크롤링 다시 도전해보기