[Yammer] WAU(Weekly Active Users) 하락 원인 분석 | 신규유저, 기존유저 Cohort WAU, Device별 WAU

About Analytics Training | SQL Analytics Training - Mode

Mode라는 플랫폼에서 제공해주는 SQL Analytics training 데이터를 활용하여 분석 프로젝트를 Study Case 하였다.

분석 과정은 총 3개로 아래와 같이 진행된다.

첫번째로, 문제를 발견/정의하고 해당 문제가 발생한 원인에 대해서 가설설정-검증의 과정을 거쳐 확인하는 분석이 있다.

두번째로는 Yammer의 '검색 기능'의 중요도와 개선방향에 대해 분석한다.

마지막으로 진행했던 A/B Test의 결과를 가지고 이 결과가 유의미한지 확인하고 적용여부를 검토한다.

 

이 분석은 MODE의 Training 자료와 함께 데이터리안에서 제공하는 강의를 활용하여 진행하였다.

단순히 SQL 쿼리를 짤 수 있는 것을 넘어서 문제 상황이 발생했을 때 어떤 데이터를 볼 것이며, 그것을 확인하기 위해 어떤 쿼리를 짤 것인지에 대한 사고과정을 경험해 볼 수 있는 분석 프로젝트였다.

Mode에서 제시하는 결론 뿐만 아니라 그 외의 방향도 생각해 볼 수 있어서 다각도로 분석하는데 데이터리안의 추가 강의가 도움이 많이 되었다. 

활성유저 수부터 Yammer 플랫폼의 주 기능 및 A/B Test까지 직접 실습해보고 쿼리도 돌려볼 수 있어서 관심있는 사람은 들어보기를 추천한다.

데이터리안 강의 링크

 

---

[Yammer 분석 프로젝트]
1. WAU(Weekly Active Users) 하락 원인 분석
2. 검색 기능(Search Functionality) 분석
3. A/B Test 유의성 검정

 

---

 

• Microsoft Yammer 소개

Yammer는 동료와 소통하기 위한 소셜 네트워크로 문서, 업데이트, 아이디어를 그룹에 게시하여 공유할 수 있는 플랫폼이다. 페이스북과 유사한 플랫폼이지만 회사 내부 조직원들과 내용을 공유하는데 초첨이 맞춰져 있는 소셜 네트워크 플랫폼이라고 보면 되겠다.

* 현재는 viva engage로 이름이 변경됨

 

 

---

• 데이터셋

 

Table은 총 4개로 users, event, email event, rollup period 정보를 각각 가지고 있다.

email_event는 event 중 email 관련 event를 따로 확인할 수 있는 테이블로 성격이 비슷하다.

마지막 rollup period 테이블은 실제로 사용한다기보다는 다른 테이블들을 파악하는데 활용될 수 있는 테이블이다.

 

 

---

• 문제

 

WAU(Weekly Active Users, 주간 활성 사용자) 수가 7월 28일을 기점으로 하락하는 경향을 확인

그 원인을 찾기위해 분석을 진행하고자 함

*Active User = 로그인한 사용자

 

 

---

• 가설 설정

아래 3가지 수치의 감소 가능성을 염두에 두고 이를 검증하며 원인을 파악해보고자 했다.

1. 신규가입자 감소

: 신규가입을 하면 로그인을 하게 되므로 active_user로 집계될 수 밖에 없음. 따라서 신규가입자 수 감소로 WAU가 감소했을 가능성 존재

 

2. 기존가입자 활성 감소 (Cohort별 WAU 확인)

: 시간이 지남에 따라 기존 사용자들의 활성도가 낮아지는 것은 당연하나 평균적인 감소정도보다 활성 수가 더 빠르게 감소한 cohort 집단이 존재 가능성 有

 

3. Device별 WAU 감소

: Device별 UX/UI 업데이트(변화) 혹은 발견하지 못한 오류의 발생이 활성자 수에 영향을 미쳤을 가능성 존재

 

 

• 가설 검증

1. 신규가입자 감소

SELECT DATE_TRUNC('week',created_at) AS week,
       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 week
 ORDER BY week

 

WAU가 감소하기 시작한 7월 28일 이후에 신규가입자 수도 크게 감소하기는 했지만 곧바로 다시 회복하여 조금씩 다시 증가하는 회복세 확인

→ 원인으로 보기 어려움 (기각)

 

 

 

2. 기존가입자 활성 감소 (Cohort별 WAU 확인)

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", -- 가입한지 10주 이상
       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, -- 가입일부터 마지막 login한 날
               EXTRACT('day' FROM '2014-09-01'::TIMESTAMP - u.activated_at) AS user_age -- 사용자들의 yammer 사용일수
          FROM tutorial.yammer_users u
          JOIN tutorial.yammer_events e
            ON e.user_id = u.user_id
           AND e.event_type = 'engagement' -- Engagement 이벤트만 집계
           AND e.event_name = 'login' -- 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 week
 ORDER BY week

좌) SQL 쿼리 결과, 우) 시각화

 

표로 봤을 땐 어떤 코호트 그룹에서 감소가 많이 일어났는지 한눈에 살펴보기 어려워 시각화를 해봤다.

0주차부터 9주차까지는 감소하는 기울기가 비슷한 반면 10주 이상 사용자의 경우 7월 28일 기점으로 그 다음부터 감소 기울기가 한번 더 크게 일어났다.

따라서 가입일 10주 이상된 사용자에서 활성 수 감소가 전체 WAU 감소의 원인일 가능성이 높음을 짐작해볼 수 있다.

 

 

 

2-1. 추가 분석 ( 기존가입자 중 가입 10주 이상 사용자 활성 감소)

기존 사용자들의 retention을 위한 마케팅 중 하나로 이메일 발송(digest email)이 있다.

이메일을 통해 꾸준히 유입되던 기존 사용자들이 감소했는지 확인해봄으로써 감소의 원인을 구체화 할 수 있을 것이라 생각해 yammer_emails을 활용해 이메일 관련 추가 분석을 진행해보았다.

1. email_opens : 전체 이메일 오픈 수

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

3. sent_weekly_digest : weekly 이메일 발송 유저수

4. sent_reengagement_email : reengagement 이메일 발송 유저수

이메일 관련 이벤트는 4가지이다.

이 중에서 이메일 확인율, 이메일 내 링크 클릭율이 감소하거나 매주 보내는 이메일 발송율이 감소했는지 확인해보았다.

 

 

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, -- clickthrough rate
       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, -- clickthrough rate
       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 -- clickthrough rate
  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 week
       ) a

좌) sent_weekly_digest, 우) sent_reengagement_email

 

sent_weekly_digest와 sent_reengagement_email 모두 이메일 오픈율에서는 큰 변화가 없으나 이메일을 오픈한 후에 yammer로 연결되는 어떠한 링크 클릭율에서 크게 감소한 것을 확인할 수 있었다.

따라서 해당 링크로 유인하는 이메일 내용 또는 링크 자체의 오류에 대한 가능성을 생각해볼 수 있겠으며 해당 부분에 대한 조치를 취해보면 좋겠다는 결론을 도출해 볼 수 있다.

 

 

 

 

3. Device별 WAU 감소

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, -- 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, -- 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 -- tablet 기기
  FROM tutorial.yammer_events e
 WHERE e.event_type = 'engagement'
   AND e.event_name = 'login'
 GROUP BY week
 ORDER BY week

좌) SQL 쿼리 결과, 우) 전주 대비 증감률

 

기기별 WAU는 4~5주에 한번씩 감소하는 규칙성이 조금 보였지만, 7월 28일 기점으로 크게 감소한 이후로 WAU를 회복하지 못하고 모든 device에서 지속적으로 감소한 상태를 유지하는 것으롭 보아 추가적인 확인이 필요한 부분으로 생각된다.

Computer도 크게 감소하기는 했지만 Phone과 Tablet Device에서 특히 WAU가 크게 감소하고 있기 때문에 해당 기기에서 UX/UI 등의 구성의 변경사항이 있는지 확인해보면 좋을 것 같다.

 

 

 

 

---

• 결론

1. 가입한지 10주 이상된 사용자들에게서 WAU가 크게 감소했으며, retention을 위한 email 발송에서 Yammer로 연결되는 링크의 클릭률 감소가 그 원인임을 확인하였다. 따라서 이메일 내용 또는 링크 자체의 오류에 대한 가능성을 염두에 두고 해당 부분에 대한 조치를 취해볼 수 있겠다.

 

 

2. 기기별 WAU에서 Phone과 Tablet에서 유의미한 감소가 확인 됨. 따라서 WAU가 감소한 시기에 UX/UI 등의 구성의 변경사항이 있는지 확인 및 추가 수정을 해볼 수 있겠다.