SQL_프로젝트1_동료의 분석 파악

Eunjung-Cho·2021년 7월 20일
0

SQL

목록 보기
15/16

상황설명

  • 'Yammer'라는 서비스를 만드는 회사에 입사
  • 업무: 동료의 분석결과에 대해 리뷰

포함되어야 하는 항목

  1. 문제 상황 파악 (동료가 어떤 문제를 풀고자 했는지)
    -> 제시된 문제상황: Investigating a Drop in User Engagement

  2. 테이블 파악
    -> 동료의 분석결과: Investigating a Drop in User Engagement: Answers

  3. 제시된 분석 방향, 쿼리에 대한 이해 (동료는 이 문제를 어떤 방식으로 풀었는지)

  4. 제시된 해결 방법에 대한 비판 (동료의 논리 전개 방식 또는 쿼리에 문제점은 없었는지, 또는 추가적으로 들여다보면 좋을 것 같은 부분이 있는지 등)

  5. 과제를 수행하면서 얻은 것, 느낀 점

  6. 이외에 추가 아이디어 등 과제와 관련해서 공유하고 싶은 것이면 무엇이든 좋습니다

가설 설정

쿼리 분석

1번째 쿼리: 성장률 확인

가장 쉽게 확인할 수 있는 것 중 하나는 성장률이다.
이는 측정이 쉽고 대부분의 기업이 이미 이를 면밀히 추적하기 때문이다.
쿼리 결과에 대한 결론: 주중에는 높은 성장률이, 주말에는 낮은 성장률이 계속된다.

SELECT DATE_TRUNC('day',created_at) AS day,
       COUNT(*) AS all_users,
       COUNT(CASE WHEN activated_at IS NOT NULL THEN u.user_id ELSE NULL END) AS activated_users
  FROM tutorial.yammer_users u
 WHERE created_at >= '2014-06-01'
   AND created_at < '2014-09-01'
 GROUP BY 1
 ORDER BY 1
  • 결과 매트릭스:

의견:

  • activated_users에 대한 정의: activated_at (활성화 시점: yammer에 로그인 한 시점)에서 null 값이 없음
  • 6,7,8월의 날짜별 활성화 유저를 확인하여 어느 시점에서 활성화 유저가 줄어드는지 분석할려고 해당 쿼리를 작성 한 것 같다.

2번째 쿼리: 기존 유저 코호트

1번째 쿼리에서 성장률 확인 -> 이러한 성장은 정상적, 따라서 신규 사용자가 아닌 기존 사용자의 참여도가 떨어지는 건가 확인하기
사용자가 제품을 등록한 시점을 기준으로 코호트를 사용

  • 쿼리 결과에 대한 결론: 10주 이상 전에 등록한 사용자의 참여 감소가 나와 있다.
SELECT DATE_TRUNC('week',z.occurred_at) AS "week",
       AVG(z.age_at_event) AS "Average age during week",
       COUNT(DISTINCT CASE WHEN z.user_age > 70 THEN z.user_id ELSE NULL END) AS "10+ weeks",
       COUNT(DISTINCT CASE WHEN z.user_age < 70 AND z.user_age >= 63 THEN z.user_id ELSE NULL END) AS "9 weeks",
       COUNT(DISTINCT CASE WHEN z.user_age < 63 AND z.user_age >= 56 THEN z.user_id ELSE NULL END) AS "8 weeks",
       COUNT(DISTINCT CASE WHEN z.user_age < 56 AND z.user_age >= 49 THEN z.user_id ELSE NULL END) AS "7 weeks",
       COUNT(DISTINCT CASE WHEN z.user_age < 49 AND z.user_age >= 42 THEN z.user_id ELSE NULL END) AS "6 weeks",
       COUNT(DISTINCT CASE WHEN z.user_age < 42 AND z.user_age >= 35 THEN z.user_id ELSE NULL END) AS "5 weeks",
       COUNT(DISTINCT CASE WHEN z.user_age < 35 AND z.user_age >= 28 THEN z.user_id ELSE NULL END) AS "4 weeks",
       COUNT(DISTINCT CASE WHEN z.user_age < 28 AND z.user_age >= 21 THEN z.user_id ELSE NULL END) AS "3 weeks",
       COUNT(DISTINCT CASE WHEN z.user_age < 21 AND z.user_age >= 14 THEN z.user_id ELSE NULL END) AS "2 weeks",
       COUNT(DISTINCT CASE WHEN z.user_age < 14 AND z.user_age >= 7 THEN z.user_id ELSE NULL END) AS "1 week",
       COUNT(DISTINCT CASE WHEN z.user_age < 7 THEN z.user_id ELSE NULL END) AS "Less than a week"
  FROM (
        SELECT e.occurred_at,
               u.user_id,
               DATE_TRUNC('week',u.activated_at) AS activation_week,
               EXTRACT('day' FROM e.occurred_at - u.activated_at) AS age_at_event,
               EXTRACT('day' FROM '2014-09-01'::TIMESTAMP - u.activated_at) AS user_age
          FROM tutorial.yammer_users u
          JOIN tutorial.yammer_events e
            ON e.user_id = u.user_id
           AND e.event_type = 'engagement'
           AND e.event_name = 'login'
           AND e.occurred_at >= '2014-05-01'
           AND e.occurred_at < '2014-09-01'
         WHERE u.activated_at IS NOT NULL
       ) z

 GROUP BY 1
 ORDER BY 1
LIMIT 100
  • 결과 매트릭스:

의견:

  • age_at_event : 이벤트가 일어난 시점(occurred_at) - 활성화 시점(로그인한 시점(activated_at))의 날짜
  • user_age : 9월 1일 기준으로 이전 날짜를 타임스탬프 - 활성화 시점의 날짜
  • 이렇게 하면 신규유저와 사용한지 오래된 유저를 가를 수 있다.

3번째 쿼리: 기기 사용 문제 확인

2번째 쿼리에서 기존의 오래 사용한 유저에게 국한된다는 사실(기존유저 사용 감소) -> 마케팅 트래픽으로 인한 일회성 급증 or 검색 엔진에서 순위가 변경되거나 차단되는 것과 같은 사이트로의 새로운 트래픽에 영향을 미치는 문제와 관련이 없다고 판단
따라서, 다양한 기기로의 사용 문제가 특정 제품에 국한되어 있는지 알아보기

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
LIMIT 100
  • 결과 매트릭스:

4번째 쿼리: 이메일 문제 확인

3번째 쿼리 결과 차트에서 전화로 필터링: 전화 가입률이 상당히 가파르게 하락 ->모바일 앱에 장기 사용자 보존과 관련된 문제
->모바일 앱에서 최근에 변경된 사항이 있는지 확인 + 사람들이 제품에 참여하게 되는 원인에 대해생각
위에서 언급한 요약 이메일의 목적은 사용자를 다시 제품에 불러오기 위한 것
이 문제가 장기 사용자 보존과 관련이 있으므로 e-메일이 이와 관련이 있는지 확인하기

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번째 쿼리:이메일 문제 weekly로 보기

clickthroughs로 필터링: clickthroughs가 훨씬 아래에 있다.
위의 4번째 쿼리 결과 차트에서는 e-메일의 클릭 수와 열기 속도를 보다 자세히 보여 주며, 이는 모바일 앱뿐 아니라 요약 e-메일과도 문제가 있다고 확인할 수 있다.

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,
       retain_opens/CASE WHEN retain_emails = 0 THEN 1 ELSE retain_emails END::FLOAT AS retain_open_rate,
       retain_ctr/CASE WHEN retain_opens = 0 THEN 1 ELSE retain_opens END::FLOAT AS retain_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,
       COUNT(CASE WHEN e1.action = 'sent_reengagement_email' THEN e1.user_id ELSE NULL END) AS retain_emails,
       COUNT(CASE WHEN e1.action = 'sent_reengagement_email' THEN e2.user_id ELSE NULL END) AS retain_opens,
       COUNT(CASE WHEN e1.action = 'sent_reengagement_email' THEN e3.user_id ELSE NULL END) AS retain_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 IN ('sent_weekly_digest','sent_reengagement_email')
 GROUP BY 1
       ) a
 ORDER BY 1
  • 결과 매트릭스:

동료의 결론:

모바일 사용 및 digest 이메일과 관련

  • 동료의 solution: 제품 책임자에게 문제가 이러한 영역에 국한되어 있으며, 문제가 손상되거나 제대로 구현되지 않았는지 확인할 가치가 있다고 알리기

동료의 분석에 대한 의견

한계점:

  • 데이터로부터 문제가 정확히 무엇인지 또는 문제의 해결 방법이 명확하지 않다.

    1) 이메일의 콘텐츠가 문제?
    2) 이메일 head가 문제?
    3) 모바일 앱에서 어떤 문제?: 시스템 오류? UI의 불편함? 콘텐츠 사용의 한계?

장점:

1) 문제의 큰 틀은 파악했다.
2) 위의 작업을 통해 다른 팀이 어디를 찾아야 할지 파악하는 데 많은 시간을 절약할 수 있다.

profile
IT컨설팅 데이터 분석가

0개의 댓글