[Analysis] Mode 1. Investigating a Drop in User Engagement

분석 사례: Yammer
Yammer는 동료와 소통하기 위한 소셜 네트워크입니다. 개인은 문서, 업데이트 및 아이디어를 그룹에 게시하여 공유합니다. Yammer는 무기한 무료로 사용할 수 있지만 ActiveDirectory와 같은 사용자 관리 시스템과의 통합을 포함하여 관리 제어에 액세스하려면 회사에서 라이선스 비용을 지불해야 합니다.

 

 

Mode 1. Investigating a Drop in User Engagement

 

The Problem

제품팀장이 사용자참여 대시보드의 최신활동에 대한 의견을 묻는다. 

이에 대한 답변을 만들어라

 

View Mode Analysis 

view mode anlysis

차느는 매주 참여하는 사용자 수를 보여줍니다.

이 차트의 모든 지점은 "해당 날짜를 시작으로 한 주 동안 최소한 하나의 참여 이벤트를 기록한 사용자 수"로 해석할 수 있습니다.

위에 표시된 차트의 끝에서 하락의 원인을 파악하고 해당되는 경우 문제에 대한 솔루션을 권장

 

SELECT DATE_TRUNC('week', e.occurred_at),
       COUNT(DISTINCT e.user_id) AS weekly_active_users
  FROM tutorial.yammer_events e
 WHERE e.event_type = 'engagement'
   AND e.event_name = 'login'
 GROUP BY 1
 ORDER BY 1

-> 즉 , 이벤트 중에서도 기존사용자의 login 에 대한 WAU 

 

Getting oriented

데이터를 만지기 전에 위의 차트에 표시된 유지율 하락의 가능한 원인 목록을 작성하십시오. 

목록을 만들고 확인할 순서를 결정하십시오. 각 가설을 테스트하는 방법을 기록해 두십시오. 

주문하는 데 사용하는 기준에 대해 신중하게 생각하고 기준도 적어 두십시오.

또한 위의 차트가 무엇을 보여주고 무엇을 보여주지 않는지 이해했는지 확인하십시오.

 

 

Digging in

가능한 문제의 목록을 정렬했으면 조사할 차례입니다.

이 문제의 경우 4개의 테이블을 사용해야 합니다. 테이블 이름과 열 정의가 아래에 나열되어 있습니다. 테이블 이름을 클릭하면 해당 테이블에 대한 정보를 볼 수 있습니다.

 

Table 2: Events

This table name in Mode is tutorial.yammer_events

user_id
occured_at
event_type	"signup_flow"는 사용자 인증 과정에서 발생하는 모든 것을 의미하고, "engagement"는 사용자가 처음 가입한 후 일반적인 제품 사용을 의미합니다.
event_name	create_user: 등록 프로세스 중에 사용자가 Yammer의 데이터베이스에 추가됩니다. enter_email: 사용자가 이메일 주소를 입력하여 등록 프로세스를 시작합니다. enter_info: 사용자가 등록 프로세스 중에 자신의 이름과 개인 정보를 입력합니다. complete_signup: 사용자가 전체 등록/인증 프로세스를 완료합니다. home_page : 사용자가 홈 페이지를 로드합니다. like_message: 사용자가 다른 사용자의 메시지를 좋아합니다. 로그인: 사용자가 Yammer에 로그인합니다. search_autocomplete: 사용자가 자동 ​​완성 목록에서 검색 결과를 선택합니다. search_run: 사용자가 검색 쿼리를 실행하고 검색 결과 페이지로 이동합니다. search_click_result_X: 사용자가 검색을 클릭합니다. 결과 페이지의 결과 X, 여기서 X는 1에서 10 사이의 숫자입니다. send_message: 사용자가 메시지를 게시합니다. view_inbox: 사용자가 받은 편지함에서 메시지를 봅니다.
location
device

Table 3: Email Events

This table name in Mode is tutorial.yammer_emails

user_id
occured_at
action	발생한 이벤트의 이름입니다. "sent_weekly_digest"는 사용자가 전날 관련 대화를 보여주는 다이제스트 이메일을 수신했음을 의미합니다. "email_open"은 사용자가 이메일을 열었음을 의미합니다. "email_clickthrough"는 사용자가 이메일의 링크를 클릭했음을 의미합니다.

 

 

Making a recommendation

참여 감소의 원인을 파악하기 위해 가설 목록을 통해 작업을 시작하십시오. 탐색할 때 작업을 저장해야 합니다. 차트 바닥글에 있는 링크를 클릭하고 "쿼리" 탭으로 이동하여 찾을 수 있는 위 쿼리를 생성하는 코드로 시작하는 것이 도움이 될 수 있습니다.

다음 질문에 답하십시오.

귀하의 원래 가설에 대한 답변이 추가 질문으로 이어집니까?
그렇다면 그것들은 무엇이며 어떻게 테스트할 것입니까?
데이터만으로는 답할 수 없는 질문이라면 어떻게 답하시겠습니까(가정적으로 이 회사에서 일했다고 가정)?
참여 감소의 가장 가능성 있는 원인은 무엇이라고 생각합니까?
있다면 기업은 이에 대해 어떻게 대처해야 할까요?

 

---------------------------------------------------------------------------------------------------------------------

 

Preparation and prioritizing

가설을 세우고 평가하는 것이 종종 이 문제의 가장 중요한 부분입니다. 이 작업을 잘 수행하면 데이터를 탐색하는 데 드는 많은 시간을 절약할 수 있습니다. 이런 종류의 문제에 대한 완전한 가능성 목록을 제공하는 것은 불가능하지만 다음은 브레인스토밍 세션에서 생각해낸 몇 가지 사항입니다.

- Holiday : 휴일 및 연휴로 인한 트래픽 감소

- Broken feature : 일부 기능의 장애 . device 별로 나누어 분석해 보자 

 

- Broken tracking code : 로그를 남기는 코드의 장애.

 

- Traffic anomalies from bots : 

- Traffic shutdown to your site : 나의 사이트가 차단당함 

 

- Marketing event : 이전의 마케팅 이벤트의 종료로 인한 낙수효과 

 

- Bad data : 사내 계정의 트래픽이 포함되어 전체 트래픽 양이 증가

 

- Search crawler changes: 검색 엔진의 변경 

 

 

That's a lot of possibilities ! 우선순위를 정해서 원인을 탐색해야한다. 

우선순위를 결정하는 방법

 

- 경험 : 해당 도메인에 대한 경험 

- 의사소통 : 물어봐라 

- 속도 : 빨리 처리할 수 있는 것 부터 - 의존성 : 관련이 있는 순으로 

 

 

 

Solving the case

1. 신규 가입자 수 확인 

- 이유 : 측정하기 쉽고, 대부분의 회사에서 신규가입자 수를 확인하는 지표가 있다. 

- 분석 결과 : 신규 사용자 추이는 일정 주기로 동일한 그래프 양상을 띈다.

                    8월 이후 사용자의 수가감소 하는데 영향이 있다고 판단하기 어렵다. 

# 아래 두 쿼리문의 결과는 동일하다 

# 내가 작성한 쿼리문
SELECT DATE_TRUNC('day', created_at) as day,
	count(created_at)as all_users,
	count(activated_at) as activated_users
FROM tutorial.yammer_users
WHERE DATE_TRUNC('day', created_at) >= '2014-06-01'
GROUP BY 1
ORDER BY 1

#제공된 쿼리문
SELECT DATE_TRUNC('day',created_at) AS day,
       COUNT(*) AS all_users,
       COUNT(CASE WHEN activate_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

 

2. Cohort User  :기존 유저의  재 방문율 (기존 유저의 가입 시기로 분류) => 전형적인 리텐션 차트 (플랫할 수록 좋다 ) 

- 이유 : 신규 가입자의 수가 문제가 없다고 판단했기 때문에 기존 유저의  재 방문률에 문제가 있는 지 확인해 본다. 

- 분석결과 : 가입한지 10주차 이상 된 유저 군의 접속량이 줄었다. !

                   => 마케팅효과나 마케팅 트래킹과 관련된 신규유저에는 변화가 없고 기존 유저들의 접속량이 줄었다. ! 

                        우선순위를 결정하는 요소 중 우선순위 요소에 따라  서비스 자체의 변경이 있는지 확인할 수 있는 지표를 확인하자

SELECT DATE_TRUNC('week', 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 weeks",
       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_events e 
      JOIN tutorial.yammer_users u 
      ON e.user_id = u.user_id 
      WHERE e.event_type = 'engagement'
      and e.event_name = 'login'
      and e.occurred_at >= '2014-05-01'
      and e.occurred_at < '2014-09-01'
      and u.activated_at IS NOT NULL ) z
GROUP BY 1
ORDER BY 1

3. Device 별 WAU

- 이유 : 기존 유저의 사용량이 감소한 것을 확인했으니 세분화 하여 , 디바이스 별로 WAU 를 구해보자

- 분석 결과 : 전체적인 사용량이 줄었는데, 그 비중이 컴퓨터는 아주 적고 그 다음이 모바일, tablet은 사용량이 현저히 많이 줄어들었다. 

SELECT DATE_TRUNC('week',e.occurred_at) AS week,
  COUNT(DISTINCT e.user_id) AS weekly_active_users,
  COUNT(DISTINCT CASE WHEN 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 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 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
      event_type = 'engagement'
      AND event_name = 'login'
    GROUP BY
      1
    ORDER BY
      1

4. 이메일 

- 배경 : 기존 사용자들이 제품에 다시 참여하게 만드는 원인데 대해서도 생각해 볼 수 있다. 요약 메일을 전송하는 것도 사용자를 제품으로 다시 안내하는 방법이다. 이메일과 관련된 이벤트가 참여율 하락의 원인인지 분석해 보자

- 분석결과 : email_clickthroughs  수치, 즉 이메일 내의 링크를 클릭하는 이벤트의 수치가 줄어드는 것을 확인할 수 있다. 

 

 

SELECT DATE_TRUNC('week', occurred_at) AS week,
  COUNT(DISTINCT CASE WHEN action = 'sent_weekly_digest' THEN user_id ELSE NULL END) as sent_weekly_digest,
  COUNT(DISTINCT CASE WHEN action = 'sent_reengagement_email' then user_id ELSE NULL END) AS reengagement_email,
  COUNT(DISTINCT CASE WHEN action = 'email_open' then user_id ELSE NULL END) AS email_open,
  COUNT(DISTINCT CASE WHEN action = 'email_clickthrough' then user_id ELSE NULL END) AS email_clickthrough
FROM tutorial.yammer_emails
GROUP BY 1
ORDER by 1

 

           

결론 :

조사 결과, 문제는 모바일 사용 및 다이제스트 이메일과 관련이 있는 것으로 나타났습니다. 여기서 의도한 조치는 명확해야 합니다. 문제가 이러한 영역에 국한되어 있으며 문제가 있거나 제대로 구현되지 않았는지 확인하는 것이 가치가 있다고 제품 책임자(처음에 귀하에게 접근한 사람)에게 알립니다. 데이터에서 문제가 정확히 무엇인지 또는 어떻게 해결해야 하는지 명확하지 않지만 위의 작업은 다른 팀이 어디를 봐야 하는지 파악하는 데 많은 시간을 절약할 수 있습니다.

 

 

 

참고 : https://www.inflearn.com/course/데이터-분석-sql-실전/lecture/72138?tab=note&volume=1.00&speed=1.5