[SQL 실전 무료강의] 프로젝트 1. 주간 활성 유저 감소

인프런에 올라와있는 데이터리안 무료 강의, [백문이불여일타] 데이터 분석을 위한 SQL 실전편 (무료 미니 코스) 수강을 시작했다.

3개의 실무 프로젝트를 sql로 어떻게 해결할 수 있는지 실제 예시와 함께 진행해볼 수 있는데, 강의만으로는 5시간이 채 안되는 분량이어서 이번 주 내로 완강을 목표로 잡았다.

근데 오늘 첫 강의를 들어보니 혼자 열심히 문제 상황을 읽고, 분석하고, 시도해본 뒤 강의를 들으며 내가 생각한 것과 어떻게 달랐는지 매칭하는 식으로 들어야 하는데, 이거 생각보다 오래 걸리겠구나 싶다.

1. 문제 상황 분석하기

출처 : https://mode.com/sql-tutorial/a-drop-in-user-engagement#getting-oriented

상황 및 해결 순서

자. 오늘은 9월 2일이다. 이번주에 출근했는데 지난 달부터 한달 째 주간 활성 유저가 감소하고 있다는 데이터를 확인했다.

당신이 담당자라면 이 사태(!)를 어떻게 해결할 것인가?

데이터를 만지기 전에,

1) 리텐션 하락의 가능한 원인 목록을 작성하고, 체크할 우선순위를 결정한다.

2) 각 가설을 어떻게 테스트할지와, 그 때 활용할 기준을 함께 기록한다.

이제 데이터를 확인해보고,
3) 회사에 제안할 해결책을 생각해보자.

추가 질문

처음 세웠던 가설에 대한 답변이 또 다른 질문으로 이어지지 않았나요?
만약 그렇다면, 그 질문들은 무엇이며 이제 어떻게 테스트할 건가요?
만약 주어진 데이터만으로는 답할 수 없는 질문이라면 어떻게 할 건가요?
(실제로 내가 이 회사에 근무하고 있다면?)
그러면 인게이지먼트가 떨어진 가장 가능성 있는 원인은 뭔가요?
그럼 이제 회사는 이 상황에 어떻게 대응해야 할까요?

원인 목록

휴일이 많은 달일 가능성
어떤 기능 구현에 문제가 있을 가능성
(일부 디바이스 접속이 안된다던지 등)
트래킹 코드에 문제가 있을 가능성
봇에 의해 발생하던 활동들이 프로덕트에 변화가 생겨 제대로 작동하지 않을 가능성
외부 서비스로부터의 유입 저하 (트래픽 문제)
직전에 마케팅 이벤트로 인해 평소보다 사용자가 많이 들어왔을 경우
사내 테스트용 데이터가 관여했을 경우
검색엔진의 크롤링 기준 변경으로 인한 노출 하락

우선순위 기준

경험 : 경험상 그동안 가장 자주 발생했던 문제 등 먼저 검토
소통 : 간단한 소통으로 생각보다 쉽고 빠르게 원인을 파악할 수 있는 경우 먼저 검토
속도 : 쉽고 빠르게 검토할 수 있는 가설을 먼저 검토. 깨끗한 데이터, 이전에 해놨던 쿼리 등
종속성 : 하나를 확인했을 때 빠르게 다음 걸 확인할 수 있는 연결성이 있다면 먼저 검토

* Engagement : 일반적으로 DAU, WAU, MAU 등 활성 사용자.

조직의 합의에 따라 그 '활성'의 기준을 아주 넓게 잡을 수도 있고 (접속, 클릭 등), 좁게 잡을 수도 있다. (로그인, 특정기능, 결제 등)

데이터 테이블 설명 중 인상깊었던 부분

- 가입 퍼널, 활성 조건들에 대한 데이터 구분

2. 원인 추론해보기

Q1. 신규 가입자가 줄어들어서, WAU(Weekly Active User)에 영향을 미쳤나?

신규 가입자에서 발생한 문제인지, 기존 가입자에게서 발생한 문제인지 확인하기 위해 가장 먼저 구분을 나눈 것.
- 2014년 8월 4일 주차에, 직전 주 대비 신규 가입자, 신규 활성 유저 각각 14.71%, 19.23% 감소
- 이후 신규 가입자, 신규 활성 유저 수 모두 이전 수준 회복 & 소폭 증가

💡 실제로 현업에서도 GA만 들어가봐도 바로 알 수 있는 정보이기 때문에, 가장 파악이 쉬운 정보 중의 하나. 우선 순위가 높을 수밖에 없다.

Q2. WAU를 유저 코호트 별로 분해해보면, 어떤 시기에 가입한 유저들이 문제인지 알 수 있지 않을까?

가입으로부터 시간이 지날수록 Active User 가 감소하는 것은 Retention 차트의 전형적인 패턴
그러나 10+ weeks 유저들 WAU가 2014년 8월 4일 주차에 차트상 예외적으로 급감하는 모습을 보이기 때문에 WAU에 영향을 미친다고 볼 수 있음.

💡 전형적인 리텐션 차트다. 자주 쓰는 유저 코호트 분석.

Q3. WAU를 디바이스별로 분해해보면, 어떤 디바이스로 접속한 유저들이 문제인지 알 수 있지 않을까?

2014년 8월 4일로 시작하는 주차의 phone, tablet WAU가 전 주 대비 각각 16.5%, 30.8% 감소.
computer WAU의 경우 2014년 8월 4일 주차의 WAU가 직전 주 대비 4% 감소하였으나, 이는 seasonality 성격으로 보임(5월 26일, 6월 30일 주 데이터 참고).
하지만 computer 역시 트래픽을 회복하지 못하고 지속적 감소를 보이고 있으므로 확인 필요.

💡 데이터에 '전체 수치' 혹은 '높은 트래픽' 항목이 끼어 있는 경우, 'tablet'과 같이 트래픽이 낮은 항목은 납작하게 뭉개져서 눈에 잘 보이지 않기 쉬움. 주의!

💡 역시 GA로 디바이스별 사용자수도 간단하게 확인할 수 있다.

Q4. 푸쉬 등 유저의 engagement 는 어떻게 발생하나? engagement 의 대표적인 발생 경로인 이메일 데이터를 살펴보자.

이메일 오픈률은 크게 낮지 않음.
이메일 내의 링크 클릭(email_clickthrough) 수가 8월 4일 전주 대비 31.75% 감소

3. SQL 로 어떻게 위와 같은 차트를 뽑았을지 실습해보기

1) 문제 상황 분석

검색하고자 하는 대상 정의 : event_type 의 두 가지 유형 'engagement', 'signup_flow' 중 전자만 활성유저로 검색
가장 먼저 해야 하는 일 : 원하는 기간으로 한정하는 일
GROUP BY, ORDER BY : 1이라고 쓰면 첫 번째 열을 기준으로 한다는 뜻이지만, 추후 복잡한 데이터로 실제 쿼리를 짜게 되면 헷갈릴 수 있으므로 항상 ALIAS 지정 후 명시해주는 게 좋음.
테이블명 명시 : 이 문제에서는 조인을 쓰지는 않았지만 현업 분석가 분들은 늘 쓸테니 ㅎㅎ 습관처럼 명시해주는 듯! 배우자.

SELECT  DATE_TRUNC('week', e.occurred_at) AS WEEK,
        COUNT(DISTINCT e.user_id) AS WEEKLY_ACTIVE_USER
  FROM  tutorial.yammer_events e
 WHERE  e.occurred_at BETWEEN '2014-04-28 00:00:00' AND '2014-08-25 23:59:59'
        AND e.event_type = 'engagement'
 GROUP
    BY  WEEK
 ORDER
    BY  WEEK
  ;

2) 신규 가입 회원 분석

쿼리를 다 뽑은 뒤에는 실제 데이터와 맞게 잘 나왔는지 한 번씩 확인하는 과정이 꼭 필요.

SELECT  DATE_TRUNC('DAY', u.created_at) AS signup_date
      , COUNT(u.user_id) AS signup_users
      , COUNT(CASE WHEN state = 'active' THEN u.user_id 
                   ELSE NULL END) AS active_users
  FROM  tutorial.yammer_users u
 WHERE  u.created_at BETWEEN '2014-06-01 00:00:00' AND '2014-08-31 23:59:59'
 GROUP
    BY  signup_date
  ;

3) 디바이스별 분석

SELECT DATE_TRUNC('week', occurred_at) AS week,
       COUNT(DISTINCT e.user_id) AS weekly_active_users,
       COUNT(DISTINCT CASE WHEN e.device IN ('macbook pro','lenovo thinkpad','macbook air','dell inspiron notebook','asus chromebook','dell inspiron desktop','acer aspire notebook','hp pavilion desktop','acer aspire desktop','mac mini')
       		THEN e.user_id ELSE NULL END) AS computer,
       COUNT(DISTINCT CASE WHEN e.device IN ('iphone 5','samsung galaxy s4','nexus 5','iphone 5s','iphone 4s','nokia lumia 635','htc one','samsung galaxy note','amazon fire phone') 
       		THEN e.user_id ELSE NULL END) AS phone,
       COUNT(DISTINCT CASE WHEN e.device IN ('ipad air','nexus 7','ipad mini','nexus 10','kindle fire','windows surface','samsumg galaxy tablet')
       		THEN e.user_id ELSE NULL END) AS tablet
  FROM tutorial.yammer_events e
 WHERE e.event_type = 'engagement'
   AND e.event_name = 'login'
 GROUP BY 1
 ORDER BY 1

4) 이메일 데이터 분석 - 오픈율과 클릭률

꼭 SQL로 모든 분석을 하려고 머리 싸맬 필요 없다. 더 빠르고 쉽게 할 수 있는 방법이 있다면 적절히 활용하는 것도 능력!

SELECT  DATE_TRUNC('WEEK', occurred_at) AS WEEK,
        action,
        COUNT(user_id) AS CNT_USER
  FROM  tutorial.yammer_emails
 GROUP
    BY  WEEK, ACTION
 ORDER
    BY  WEEK, ACTION
  ;

↓

혹은, 예시 정답처럼 SQL로 한번에 다 짜려면 아래와 같이 짜면 된다.

COUNT를 하나씩 넣어주고, ACTION 조건에 해당하는 걸 USER_ID로 뽑아주기 위해 CASE 구문 활용.

SELECT DATE_TRUNC('week', occurred_at) AS week,
       COUNT(CASE WHEN e.action = 'sent_weekly_digest' THEN e.user_id ELSE NULL END) AS weekly_emails,
       COUNT(CASE WHEN e.action = 'sent_reengagement_email' THEN e.user_id ELSE NULL END) AS reengagement_emails,
       COUNT(CASE WHEN e.action = 'email_open' THEN e.user_id ELSE NULL END) AS email_opens,
       COUNT(CASE WHEN e.action = 'email_clickthrough' THEN e.user_id ELSE NULL END) AS email_clickthroughs
  FROM tutorial.yammer_emails e
 GROUP BY 1
 ORDER BY 1

5) 이메일 데이터 분석 - 5분 내의 오픈율과, 오픈한 사람 중 클릭률 비율

실무를 제대로 엿볼 수 있었던 다음 문제.

믿기는가... 이걸... 내가 손으로 하나하나 짰다! (감격)

SELECT  DATE_TRUNC('WEEK',E1.occurred_at) AS WEEK
      , COUNT(CASE WHEN E1.action = 'sent_weekly_digest' THEN E1.user_id ELSE NULL END) AS SENT_WK_DG
      , COUNT(CASE WHEN E1.action = 'sent_weekly_digest' THEN E2.user_id ELSE NULL END) AS SENT_WK_DG_OPEN
      , COUNT(CASE WHEN E1.action = 'sent_weekly_digest' THEN E3.user_id ELSE NULL END) AS SENT_WK_DG_CLICKTHROUGH
  FROM  tutorial.yammer_emails AS E1
        LEFT JOIN tutorial.yammer_emails AS E2
        ON E2.user_id = E1.user_id
        AND E2.ACTION = 'email_open'
        AND E2.occurred_at BETWEEN E1.occurred_at AND E1.occurred_at + INTERVAL '5 MINUTE'
        LEFT JOIN tutorial.yammer_emails AS E3
        ON E3.user_id = E1.user_id 
        AND E3.ACTION = 'email_clickthrough'
        AND E3.occurred_at BETWEEN E1.occurred_at AND E1.occurred_at + INTERVAL '5 MINUTE'
        WHERE  E1.occurred_at BETWEEN '2014-06-01 00:00:00' AND '2014-08-31 23:59:59'
        AND E1.ACTION IN ('sent_reengagement_email', 'sent_weekly_digest')
 GROUP
    BY  WEEK
  ;

그 다음 비율을 구하고 싶다면... 힘들게 SQL로 똥싸지 말고 또다시 구글 스프레드시트로 휙 넘어가자.

* 위의 표를 차트화한다고 해도 아래와 같이 나오지 않고 위와 동일하게 나와서 왜지? 한참 고민했는데

위의 표의 CLICKTHROUGH 비율 계산 시에 '클릭한 사람 / 오픈한 사람' 으로 해야 하는데 '클릭한 사람 / 이메일 보낸 사람' 으로 해서 비율을 계산하는 것이 의미가 없게 그냥 숫자로 쓴 것과 똑같이 된 것... 😇

아무튼 계산을 잘 해주면 문제없이 아래 차트가 나올 것이다.

* 혹시나 해서 SQL로 비율까지 구현한 코드도 추가한다.

SELECT week,
       weekly_opens/CASE WHEN weekly_emails = 0 THEN 1 ELSE weekly_emails END::FLOAT AS weekly_open_rate,
       weekly_ctr/CASE WHEN weekly_opens = 0 THEN 1 ELSE weekly_opens END::FLOAT AS weekly_ctr
  FROM (
SELECT DATE_TRUNC('week',e1.occurred_at) AS week,
       COUNT(CASE WHEN e1.action = 'sent_weekly_digest' THEN e1.user_id ELSE NULL END) AS weekly_emails,
       COUNT(CASE WHEN e1.action = 'sent_weekly_digest' THEN e2.user_id ELSE NULL END) AS weekly_opens,
       COUNT(CASE WHEN e1.action = 'sent_weekly_digest' THEN e3.user_id ELSE NULL END) AS weekly_ctr
  FROM tutorial.yammer_emails e1
  LEFT JOIN tutorial.yammer_emails e2
    ON e2.occurred_at >= e1.occurred_at
   AND e2.occurred_at < e1.occurred_at + INTERVAL '5 MINUTE'
   AND e2.user_id = e1.user_id
   AND e2.action = 'email_open'
  LEFT JOIN tutorial.yammer_emails e3
    ON e3.occurred_at >= e2.occurred_at
   AND e3.occurred_at < e2.occurred_at + INTERVAL '5 MINUTE'
   AND e3.user_id = e2.user_id
   AND e3.action = 'email_clickthrough'
 WHERE e1.occurred_at >= '2014-06-01'
   AND e1.occurred_at < '2014-09-01'
   AND e1.action = 'sent_weekly_digest'
 GROUP BY 1
       ) a
 ORDER BY 1

회고

Keep 좋았던 점

처음으로 SQL이 현업에서 어떻게 쓰이는지 얕게나마 피부로 느껴볼 수 있었다.
확실히 한 번 해보니 요즘 JD들에서 왜 그렇게나 '논리적인 사고', '데이터 시각화 역량', '빠른 습득 능력' 등을 강조하는지 이제야 이해가 된다... 그동안은 그냥 당연한 얘기 아닌가? 막연하게만 생각했는데, 이제야 그들이 어떤 맥락에서 어떤 인재를 찾는 것인지 어렴풋이 알겠다는 생각. 면접 보기 전에 들어보길 진짜 잘했다.

Problem 어려웠던 점

코딩테스트 풀 때는 오히려 문제와 조건이 명확하게 주어지니 그대로 SQL 코드 짜는 법만 알면 되었는데, 말 그대로 밑도 끝도 없이 '문제 상황'만 눈앞에 펼쳐진 상황이니 아래와 같은 어려움들이 피부로 와닿았다.
- 표면적인 문제 하나를 해결하기 위해, 망망대해와 같은 가능성들 가운데 합리적인 원인을 추측해 좋은 가설을 뽑아내야 하고 (문제가 뭐지? 왜 이런 일이 일어났지?)
- 무엇부터 확인하는게 가장 쉽고 빠를지 우선순위를 머릿속으로 뽑아낼 줄 알아야 하며
- 그걸 검증하기 위해 적절한 데이터를 활용해(어떤 DB를 확인하지?)
- 적절한 방식으로(SQL로 쉽고 빠르게 뽑을 수 있나? 다른 툴을 써야 하나?)
- 보기 좋게 시각화하고 (선 차트? 바 차트?)
- 최종 함유점을 이끌어내 남들에게 공적으로 전달해야 한다.

Try 이후 노력할 점

위의 모든 과정을 혼자서도 뚝딱 만들어낼 수 있도록 이런 식의 실전 프로젝트 강의를 더 들어봐야겠다.
스터디 멤버 분들과 협의해 실제 더미 데이터를 가지고 프로젝트를 진행해보는 경험을 해봐야겠다.
실제 기업에서의 관련 사례를 더 찾아보고 공부해봐야겠다.

* 출처 : 데이터리안 강의 [백문이불여일타] 데이터 분석을 위한 SQL 실전편 (무료 미니 코스)

'[공부] 데이터 분석 활용' 카테고리의 다른 글

[SQL 강의] 러닝스푼즈 \| SQL과 구글 시트로 배우는 데이터 리터러시 첫 걸음 (5) 보고서 작성 (1)	2024.02.20
[SQL 강의] 러닝스푼즈 \| SQL과 구글 시트로 배우는 데이터 리터러시 첫 걸음 (4) 데이터 분석 방법론 (1)	2024.02.18
[SQL 강의] 러닝스푼즈 \| SQL과 구글 시트로 배우는 데이터 리터러시 첫 걸음 (3) 스프레드시트 활용 (1)	2024.02.04
[SQL 강의] 러닝스푼즈 \| SQL과 구글 시트로 배우는 데이터 리터러시 첫 걸음 (2) SQL 활용 (0)	2024.02.01
[SQL 강의] 러닝스푼즈 \| SQL과 구글 시트로 배우는 데이터 리터러시 첫 걸음 (1) (1)	2024.01.30

데이터와 비즈니스를 공부하는 글쟁이

[SQL 실전 무료강의] 프로젝트 1. 주간 활성 유저 감소

1. 문제 상황 분석하기

상황 및 해결 순서

추가 질문

원인 목록

우선순위 기준

데이터 테이블 설명 중 인상깊었던 부분

2. 원인 추론해보기

Q1. 신규 가입자가 줄어들어서, WAU(Weekly Active User)에 영향을 미쳤나?

Q2. WAU를 유저 코호트 별로 분해해보면, 어떤 시기에 가입한 유저들이 문제인지 알 수 있지 않을까?

Q3. WAU를 디바이스별로 분해해보면, 어떤 디바이스로 접속한 유저들이 문제인지 알 수 있지 않을까?

Q4. 푸쉬 등 유저의 engagement 는 어떻게 발생하나? engagement 의 대표적인 발생 경로인 이메일 데이터를 살펴보자.

3. SQL 로 어떻게 위와 같은 차트를 뽑았을지 실습해보기

1) 문제 상황 분석

2) 신규 가입 회원 분석

3) 디바이스별 분석

4) 이메일 데이터 분석 - 오픈율과 클릭률

↓

5) 이메일 데이터 분석 - 5분 내의 오픈율과, 오픈한 사람 중 클릭률 비율

회고

Keep 좋았던 점

Problem 어려웠던 점

Try 이후 노력할 점

프로젝트와 관련된 추가 자료

'[공부] 데이터 분석 활용' 카테고리의 다른 글

티스토리툴바

[SQL 실전 무료강의] 프로젝트 1. 주간 활성 유저 감소

1. 문제 상황 분석하기

상황 및 해결 순서

추가 질문

원인 목록

우선순위 기준

데이터 테이블 설명 중 인상깊었던 부분

2. 원인 추론해보기

Q1. 신규 가입자가 줄어들어서, WAU(Weekly Active User)에 영향을 미쳤나?

Q2. WAU를 유저 코호트 별로 분해해보면, 어떤 시기에 가입한 유저들이 문제인지 알 수 있지 않을까?

Q3. WAU를 디바이스별로 분해해보면, 어떤 디바이스로 접속한 유저들이 문제인지 알 수 있지 않을까?

Q4. 푸쉬 등 유저의 engagement 는 어떻게 발생하나? engagement 의 대표적인 발생 경로인 이메일 데이터를 살펴보자.

3. SQL 로 어떻게 위와 같은 차트를 뽑았을지 실습해보기

1) 문제 상황 분석

2) 신규 가입 회원 분석

3) 디바이스별 분석

4) 이메일 데이터 분석 - 오픈율과 클릭률

↓

5) 이메일 데이터 분석 - 5분 내의 오픈율과, 오픈한 사람 중 클릭률 비율

회고

Keep 좋았던 점

Problem 어려웠던 점

Try 이후 노력할 점

프로젝트와 관련된 추가 자료

'[공부] 데이터 분석 활용' 카테고리의 다른 글

'[공부] 데이터 분석 활용' Related Articles

티스토리툴바