Investigating a Drop in User Engagement(프로젝트)

https://mode.com/sql-tutorial/a-drop-in-user-engagement/

Investigating a Drop in User Engagement | SQL Analytics Training - Mode

 

https://mode.com/sql-tutorial/a-drop-in-user-engagement-answers/

Investigating a Drop in User Engagement: Answers | SQL Analytics Training - Mode

 

 

1. 문제상황

당신은 microsoft의 yammer 서비스의 데이터 분석가이다. (yammer는 기업용 SNS이다) 

2014년 9월 2일 화요일 오전에 프로덕트 팀장이 당신을 방문해 아래 차트를 보여준다.

yammer서비스의 WAU차트이다.

8월 쯤에 WAU가 감소하는 원인을 파악, 문제에 대한 해결책을 제시해보자

 

 

2. 테이블 셋 설명

Table1 : Users

사용자당 하나의 행과 해당 사용자 계정에 대한 설명이 포함되어 있습니다.

user_id	사용자별 고유 ID
created_at	사용자가 최초 생성된 시각
state	사용자 상태(회원가입 완료 or 회원가입 승인 대기)
activated_at	사용자가 회원가입 승인 되 시각
company_id	사용자 회사의 ID
language	사용자 선택 언어

 

Table2 : Events

이벤트당 1개의 행이 포함됩니다. 이벤트는 사용자가 Yammer에서 수행한 액션입니다. 로그인, 메시징, 검색 등의 이벤트가 있습니다.

user_id	이벤트를 로깅하는 사용자 ID
occurred_at	이벤트가 발생한 시각
event_type	회원가입 절차에서 기록되는 signup_flow와 회원가입 후 일반적인 제품 사용을 나타내는 engagement 두가지가 있습니다.
event_name	사용자가 수행한 특정 액션입니다.사용 가능한 값은 다음과 같습니다. create_user: 사용자가 등록 프로세스 중에 Yammer 데이터베이스에 추가됩니다.enter_email:사용자는 이메일 주소를 입력하여 등록 프로세스를 시작합니다.enter_info: 사용자는 등록 프로세스 중에 이름과 개인정보를 입력합니다.complete_signup:사용자는 등록/인증 프로세스 전체를 완료한다.home_page:사용자가 홈 페이지를 로드합니다.like_message:사용자가 다른 사용자의 메시지를 좋아합니다.로그인:사용자가 Yammer에 로그인하다search_incomplete:사용자가 자동 완성 목록에서 검색 결과를 선택합니다.search_run: 사용자가 검색 쿼리를 실행하여 검색 결과 페이지로 이동합니다.search_click_result_X: 사용자가 결과 페이지에서 검색 결과 X 를 클릭합니다.X 는 1 ~10 의 숫자입니다. send_message:사용자가 메시지를 투고하다view_inbox: 사용자가 받은 편지함의 메시지를 봅니다.
location	이벤트가 로그에 기록된 국가(IP 주소를 통해 수집).
device	이벤트 로그에 사용되는 디바이스 유형.

 

Table3 : Email Events

이메일 관련 이벤트가 있습니다.

user_id	이벤트와 관련된 사용자의 ID
occurred_at	이벤트가 발생한 시각
action	발생한 이벤트의 이름. "sent_weekly_digest"는 사용자에게 전날 일어난 일을 보여주는 요약 이메일이 전달되었음을 의미합니다. "email_open"은 사용자가 이메일을 열었음을 의미합니다. "email_clickthrough"는 사용자가 이메일 링크를 클릭했음을 의미합니다

 

 

3. 가설제시

• 휴일 : Yammer는 업무용 SNS입니다. 휴일에 더 적게 사용할 수 있습니다.
• 파손된 기능 : 앱이나 웹은 어떤 기능이 고장나면 고객들이 불편을 느껴 이용이 감소할 수 있습니다.
• 고장난 추적 코드 : 이벤트를 기록하는 코드가 고장날 수도 있습니다.
• 트래픽을 수집하는 봇의 고장 : "허락"이라는 버튼이 "승인"이라고 문구만 바뀌어도 사람은 알 수 있지만 봇은 알지 못합니다. 봇에 문제가 생길 수도 있습니다.
• 사이트에 대한 트래픽 종료 : 법률적이나 운영상의 문제로 인터넷에서 사이트로의 접속이 차단될 수 있습니다.
• 마케팅 이벤트 : 마케팅 행사로 인해 사용자들의 유입이 일시적으로 증가하고 이후에 마케팅으로 인해 일시적으로 늘어난 유입이 빠질 수도 있습니다.
• 불량 데이터 : 불량데이터가 잇을 수도 있습니다.
• 크롤러 변경 검색 : 검색엔진이 변경돼 사이트가 뜨는 순서가 바뀌어서 유입이 감소할 수도 있습니다.

 

 

4. 가설확인

1. 회원가입 지표를 확인해봅니다. 회원가입 기능이 고장나 고객들이 회원가입에 어려움을 느끼면 신규유저의 유입이 감소하고 WAU가 감소할 수 있습니다. yammer_users테이블을 이용합니다.

SELECT DATE_TRUNC('day',created_at) AS day,
       COUNT(*) AS all_users,
       COUNT(CASE WHEN activated_at IS NOT NULL THEN user_id ELSE NULL END) AS activated_users
  FROM tutorial.yammer_users
 WHERE created_at BETWEEN '2014-06-01' AND '2014-09-01'
 GROUP BY day
 ORDER BY day

쿼리 결과

쿼리결과 시각화

 

2014/06/01부터 2014/09/01까지 회원가입한을 신청한 유저와 회원가입 신청이 승인된 유저를 시각화했습니다. 특이사항이 없어서 회원가입 기능에는 문제가 없다고 판단합니다. 

 

 

 

2. 문제는 기존유저에 있을 수도 있습니다. 회원가입을 한 시기에 따라 유저를 분류하는 코호트 분석을 실시합니다.

 WITH z AS (
             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 BETWEEN '2014-05-01' AND '2014-08-31'
             WHERE u.activated_at IS NOT NULL
            )
            
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+ week",
       COUNT(DISTINCT CASE WHEN z.user_age < 70 AND z.user_age >= 63 THEN z.user_id ELSE NULL END) AS "9 week",
       COUNT(DISTINCT CASE WHEN z.user_age < 63 AND z.user_age >= 56 THEN z.user_id ELSE NULL END) AS "8 week",
       COUNT(DISTINCT CASE WHEN z.user_age < 56 AND z.user_age >= 49 THEN z.user_id ELSE NULL END) AS "7 week",
       COUNT(DISTINCT CASE WHEN z.user_age < 49 AND z.user_age >= 42 THEN z.user_id ELSE NULL END) AS "6 week",
       COUNT(DISTINCT CASE WHEN z.user_age < 42 AND z.user_age >= 35 THEN z.user_id ELSE NULL END) AS "5 week",
       COUNT(DISTINCT CASE WHEN z.user_age < 35 AND z.user_age >= 28 THEN z.user_id ELSE NULL END) AS "4 week",
       COUNT(DISTINCT CASE WHEN z.user_age < 28 AND z.user_age >= 21 THEN z.user_id ELSE NULL END) AS "3 week",
       COUNT(DISTINCT CASE WHEN z.user_age < 21 AND z.user_age >= 14 THEN z.user_id ELSE NULL END) AS "2 week",
       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 z
       GROUP BY week
       ORDER BY week

• Less than a week : 1주 이내 가입한 유저들 집합
• n weeks : n주 전에 가입한 유저들 집합
• 10+ weeks : 10주 이전에 가입한 유저들 집합

10주 이전에 가입한 유저들의 WAU가 8월을 기점으로 기울기가 가파르게 감소하고 있습니다. 이로써 기존유저들의 유입이 감소한 것을 파악할 수 있습니다.

 

 

 

3. WAU의 감소가 신규유저가 아닌 기존유저에 있다는 것을 파악했습니다. 이러한 통찰은 트래픽 감소가 일시적인 마케팅 이벤트나 검색엔진 이슈에 관한 것이 아니라고 말해줍니다. 이제 기기별로 WAU를 파악해봅니다.

 SELECT DATE_TRUNC('week', occurred_at) AS week,
        COUNT(DISTINCT user_id) AS weekly_action_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 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 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 user_id ELSE NULL END) AS tablet
 FROM tutorial.yammer_events 
 WHERE event_type = 'engagement' AND event_name = 'login'
 GROUP BY week
 ORDER BY week

왼쪽은 디바이스별 WAU, 오른쪽은 직전 주 대비 증감률

2014/08/04 이후로 WAU가 큰폭으로 감소하고 이후에 회복하지 못함

 

 

 

4. 위 차트에서 모바일과 태블렛, 기존유저에서 WAU가 감소한 것을 확인할 수 있습니다. 이로써 기존유저들이 사용하는 모바일 앱에 문제가 생긴것을 확인할 수 있습니다. 우리는 앱의 이메일 관련 기능을 분석해보면서 WAU의 감소 원인을 살펴보겠습니다.

 SELECT DATE_TRUNC('week', occurred_at) AS week,
        COUNT(CASE WHEN action = 'sent_weekly_digest' THEN user_id ELSE NULL END) AS weekly_emails,
        COUNT(CASE WHEN action = 'sent_reengagement_email' THEN user_id ELSE NULL END) AS reengagement_emails,
        COUNT(CASE WHEN action = 'email_open' THEN user_id ELSE NULL END) AS email_opens,
        COUNT(CASE WHEN action = 'email_clickthrough' THEN user_id ELSE NULL END) AS email_clickthroughs
 FROM tutorial.yammer_emails 
 WHERE occurred_at BETWEEN '2014-04-28 00:00:00' AND '2014-08-31 23:59:59'
 GROUP BY week
 ORDER BY week

* weekly_emails : weekly 이메일 발송수

* reengagement_emails : reengagement 이메일 발송수

* email_opens : 전체 이메일 오픈 수

* email_clickthroughs : 이메일 내 클릭요소를 클릭한 횟수

 

이메일을 받고 오픈한 횟수는 꾸준히 증가하지만 이메일 속의 링크를 누르는 횟수는 8월 쯤에 감소한 것을 확인할 수 있습니다.

 

 

 

5. 위 차트에서 이메일 속의 링크를 누르는 횟수가 감소하는 것을 알 수 있습니다. 이 부분을 좀 더 파고들어 봅시다.

SELECT week,
       weekly_digest_email_open/CASE WHEN weekly_digest_email = 0 THEN 1 ELSE weekly_digest_email END :: FLOAT AS weekly_open_rate,
       weekly_digest_email_clickthrough /CASE WHEN weekly_digest_email = 0 THEN 1 ELSE weekly_digest_email 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_emails = 0 THEN 1 ELSE retain_emails 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_digest_email,
              COUNT(CASE WHEN e1.action = 'sent_weekly_digest' THEN e2.user_id ELSE NULL END) AS weekly_digest_email_open,
              COUNT(CASE WHEN e1.action = 'sent_weekly_digest' THEN e3.user_id ELSE NULL END) AS weekly_digest_email_clickthrough,
              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 BETWEEN e1.occurred_at AND 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 BETWEEN e1.occurred_at AND e1.occurred_at + INTERVAL '5 MINUTE'
                                              AND e3.user_id = e1.user_id 
                                              AND e3.action = 'email_clickthrough'
       WHERE e1.occurred_at BETWEEN '2014-06-01 00:00:00' AND '2014-08-31 23:59:59'
             AND e1.action IN('sent_weekly_digest', 'sent_reengagement_email') 
       GROUP BY week
      ) sub

* weekly_open_rate : weekly digest 이메일 수신 5분 이내 메일 오픈률

* weekly_ctr : weekly digest 이메일 수신 5분 이내 메일 안의 링크 클릭률

* retain_open_rate : reengagement 이메일 수신 5분 이내 메일 오픈률

* retain_ctr : reengagement 이메일 수신 5분 이내 메일 안의 링크 클릭률

weekly_digest_email속의 링크를 클릭하는 비율이 8월쯤에 급감한 것을 확인할 수 있다.

 

 

 

5. 분석 결과

• 기존유저에서 WAU의 감소되었습니다.
• 스마트폰과 태블렛에서 WAU가 감소가 두드러졌습니다.
• weekly digest email의 링크 클릭률이 큰폭으로 감소한 것을 확인했습니다.

 

6. 행동 제시

스마트폰과 태블렛이서 구동되는 앱의 메일 관련 기능을 확인해봅니다. 기존 유저들을 중점적으로 파악해봅니다.