[미니 프로젝트] Yammer case 분석1. WAU 감소원인 파악

0-1. Yammer 회사란?

- 회사 소개 및 서비스 설명

0-1) what is Yammer?

- 비즈니스 소셜네트워크 기능 제공(like facebook)

- 조직간 소통 플랫폼 제공

- 채팅 포럼, 비즈니스를 위한 인트라넷

- 소셜 피드, 투표, 파일저장, 발표 등

- 가능한 기기 : desktop / mobile 둘다 가능

 

>>Yammer 예시화면

사내 비즈니스 소통 및 SNS 플랫폼	사내 인트라넷 및 투표

 

0-2) Yammer의 이점

- 팀내 협동심 강화

- 직관적이고 친숙한 ui

- 클라우드 공유를 통한 일 처리속도 향상

- 쉬운 사용방법

- yammer 이외의 플랫폼과의 높은 연계성

 

참고 영상:

https://www.youtube.com/watch?v=Fz5yi4Cyj5o 

 

 

0-2.  테이블 명세서(Table Description)

- user / events / email 테이블

Table1. tutorial.yammer_users 테이블

:유저 정보 명시

user_id	A unique ID per user.
created_at	The time the user was created (first signed up)
state	The state of the user (active or pending) -> 가입할 때 바로 활동이 가능한 것이 아님 (pending은 보류 / active는 활동가능 상태)
activated_at	The time the user was activated, if they are active
company_id	The ID of the user's company
language	The chosen language of the user

 

테이블 모습)

 

Table2. tutorial.yammer_events 테이블

:로그인- 메시지- 검색 기록

: 가입 유입경로를 통해 사용자가 가입함에 따라 기록되는 이벤트 포함

(events logged as users progress through a signup funnel)

: 접속 장치들 기록

 

user_id	The ID of the user logging the event.
occurred_at	The time the event occurred.
event_type	2종류가 존재 1) "signup_flow"(사용자 가입 및 인증과정)  :which refers to anything occuring during the process of a user's authentication, 2) "engagement" (가입 이후의 활동 기록) : which refers to general product usage after the user has signed up for the first time.
event_name	The specific action the user took. Possible values include: 1) event_type == signup_flow인 이벤트 네임들 - create_user : 유저가 회원가입 - enter_email: 이메일 정보 입력 -  enter_info: 유저 정보 입력 -  complete_signup: 인증/가입절차 완료 2)  event_type == engagement인 이벤트 네임들 - home_page: 홈페이지로 로드됨 - login: 로그인 - like_message: 다른 유저의 메시지에 좋아요 표시 - send_message: 메시지 전송 - view_inbox: 메시지 박스에서 메시지 보기  - search_autocomplete: 자동완성 리스트 중에 선택하여 검색  - search_run: User runs a search query and is taken to the search results page - search_click_result_X: User clicks search result X on the results page, where X is a number from 1 through 10.
location	The country from which the event was logged (collected through IP address).
device	The type of device used to log the event.

 

테이블 예시

 

Table3. tutorial.yammer_events 테이블

: This table contains events specific to the sending of emails

user_id	The ID of the user to whom the event relates.
occurred_at	The time the event occurred.
action	1) sent_weekly_digest : 일주일 활동 내역 및 주요 이메일 전송 2) email_open : 유저가 이메일을 오픈함 3) email_clickthrough : 이메일 안에 있는 링크를 유저가 클릭 4) sent_reengagemnet_email : 미접속 인원들에게 복귀 유도 메일을 보냄

 

테이블 예시)

 

 

 Project1. 

문제정의. Problem - WAU 감소

1-1) Probelm

 

2014년 9월 2일 화요일

생산팀장이최근 유저 engagemnet 감소에 대해 묻는다.

 

Yammer는 engagement를 다음과 같이 정의한다.

- server call이 한번이라도 일어난 것

- event테이블 - event_type에  engagement라고 기록됨

 

 

 

Q1 : 우리가 문제가 칭하는 것이 정말 문제인가?

- 계절성이나 일반적으로 보이는 주기는 아닌가?

A : 시간에 따라 주간활성이용자(WAU)의 수와 증감률을 파악해보자!

 

SQL쿼리 - 주간 활성 이용자 수

 

 

- WAU 증감률은 3-4주에 걸쳐 한번씩 감소하는 주기를 보임

- 그러나 8/4일에 보인 감소율은 이례적으로 큰 폭임(-12.27%)

- 또한, 감소 이후 회복이 된 다른 주차와 달리 회복이 아닌 계속된 감소추세를 겪음

 

소결1. 

: 8월 4일 이후 발견된 WAU감소는 이례적인 문제상황으로 정의됨

: 큰 폭의 감소 이후 지속적인 참여율 감소가 발견됨

 

Q2 : Growth 분석 : 신규가입자 감소 => WAU 감소?

- 가입하는 사람들이 줄어든 것은 아닐까?

A : 주별 신규가입자의 수와 증감률을 파악해보자

 

SQL 쿼리1- DAY별 신규가입자

 

그래프

 

=> DAY별로는 주기에 큰 문제가 없어보임

=> 주별로 증감률을 살펴보자

 

 

 SQL

 

테이블

그래프

- 8월 4일 주차에 급격히 신규 성장률이 빠짐

- 허나, 이후 원래 기조 확보 및 회복됨

 

소결2.

- 2014년 8월 4주차에 직전 주 대비 신규가입자, 신규 활성유저가 큰폭으로 감소

- but, 이후에는 신규 가입자와 활성유저 모두 이전수준 회복

 

=> WAU 감소의 근본적 원인이 성장률(신규 유저 유입)이 아닐 가능성이 큼

=> 그렇다면 기존 유저의 참여율을 떨어뜨린 원인을 찾아야 한다.

 

Q3 : 코호트 분석 : 기존 가입자의 참여율 감소?

- 기존 가입자 중 어떤 집단에서 참여율이 감소했을까?

A: Yammer의 가입시점을 기준으로 참여율 리텐션을 분석해보자

 

SQL문

리텐션 테이블

 

그래프

 

=> 홈페이지에서는 10주 이전부터 engagement를 했던 user들의 참여율이 떨어진다고 함

=> 그러나 증감률에 대한 어떠한 기준을 가지고 판단하는 것인지 의문

 

=> 가입시점을 바탕으로 cohort 하고 retention 분석을 하는 것까지는 이해가 가는데

=> 어떤 기준으로 10주 이전 가입자들의 참여율이 큰폭으로 떨어진다고 하는 것인지 의문

 

=> cohort 별로 그래프를 따로 분리해보자

=> 1~9주차 코호트는 감소율이 평행선일만큼 눈에 띄는 부분이 거의 없음

 

 => 8-4일 주차에 급격히 내려앉는 부분이 보임

=> 그럼에도 특정 기준점을 만드는 방법을 알아야할 필요성을 느낌

 

소결3.

- 가입시점을 바탕으로 retention 분석을 통해 engagement 주요 감소집단을 알아보고자 함.

- 10주 이전에 참여자들을 대상으로 눈에 띄는 감소구간들이 보임

 

 

Q4 : 기기에 따른 참여율 감소?

: 모바일이나 desktop 퍼널 중에 결함이 생기진 않았을까?

A : 기기 별로 유저들을 분리하여 참여율을 살펴보자

 

준비 : 데이터 라벨링

기기를 크게 computer, tablet, phone으로 분리하자

 

먼저 tutorial.yammer_events의 device 컬럼에서 각 사용 기기의 고유값을 추출해보자

 

각 데이터를 예제대로 분류하면  다음과 같다.

phone	'iphone 5', 'samsung galaxy s4', 'nexus 5', 'iphone 5s', 'iphone 4s' ,'nokia lumia 635', 'htc one', 'samsung galaxy note', 'amazon fire phone'
tablet	'ipad air', 'nexus 7', 'ipad mini', 'nexus 10', 'kindle fire' , 'windows surface', 'samsumg galaxy tablet'
computer	'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'

=> 이제 각각 device 목록값을 기준으로 SQL문을 짜주면 됨

 

SQL문

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

 

]>>테이블

 

>>그래프

tablet

computer

phone

 => computer의 접속률은 소폭감소했으나 유지추세

=> phone과 tablet 의 접속률이 8/4일주간 이후로 떨어지는 모습

 

>>직전 주 대비 증감률

=> 직전 주 대비 증감률을 보아도 주기를 띄는 컴퓨터 사용 주차 이외에

=> 8월 4일 주간에 phone과 tablet은 각각 -16.5% / -30.8%라는 큰폭의 감소를 보임

 

 

소결4.

- phone과 tablet 내부의 유저들의 참여율 감소

- 모바일 유저들의 유연한 참여경험을 방해하는 요소가 있는지 확인필요

 

Q5 :  이메일을 통한 참여 유도율  감소?

: 참여를 유도하는 email push문자의 효과가 떨어진 건 아닐까?

 

A1 : 주차별  이메일 로그빈도를 분석해보자 

 

>> tutorial.yammer_events의 action 부분

 

1) sent_weekly_digest : 일주일 활동 내역 및 주요 이메일 전송
2) email_open : 유저가 이메일을 오픈함
3) email_clickthrough : 이메일 안에 있는 링크를 유저가 클릭
4) sent_reengagemnet_email : 미접속 인원들에게 복귀 유도 메일을 보냄

 

SQL문 - 주차별 이메일 로그 분석

select date_trunc('week', occurred_at) as d
      ,count(case when action = 'sent_weekly_digest' then 1 end ) as "sent_weekly_digest"
      ,count(case when action = 'email_open' then 1  end ) as "email_open"
      ,count(case when action = 'email_clickthrough' then 1  end ) as "email_click_through"
      ,count(case when action = 'sent_reengagement_email' then 1 end ) as "sent_reengagement_digest"
from tutorial.yammer_emails
where occurred_at between '2014-04-01 00:00:00' and '2014-08-25:23:59:59'
group by 1

 

>>테이블

 >>그래프

 

email_clickthorugh => 8월 4일 이후로 큰 하락폭

 

email_open => 지속적 증가

 

sent_reengagemnet_email=> 8월 4일주차 이후로 더 많이 보냄

 

sent_weekly_digest => 지속적 증가추세

 

 

소결 5 

: 참여 유도 이메일 발송량은 점차 많아졌지만 링크 클릭 연계가 잘 안되고 있음

 

 

 

A2. 보낸 이메일 중에 "5분 이내" 오픈 및 클릭으로 이어진 메일의 비율을 알아보자!

 

>> 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,
       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

 

 >>오픈 비율

 

 

week : 주차

weekly_open_rate : weekly digest email을 5분 이내 연 비율

weekly_ctr : weekly digest email의 링크를 5분 클릭 비율

weekly_open_rate : weekly digest email을 5분 이내 연 비율

weekly_ctr : weekly digest email의 링크를 5분 클릭 비율

 

 

>>소결6.

weekly digest email의 click-through 비율이 많이 낮음

 

2.  결론 도출 및 배운점

소결 모음

 

1. 8월 4일 주차의 WAU 감소는 이례적인 문제상황으로 정의됨 

2. 신규가입자는 주기에서 큰 변동이 없으므로 WAU 감소의 근본적 원인이 아님

3. 10 주 이전 가입자들의 retention이 눈에 띄게 감소함

4.  phone/ tablet 유저들의 참여율 감소 => 모바일 트래픽 방해요소가 있는지 확인필요 

5. 참여 유도 이메일 발송량에 비해 링크 클릭 연계가 잘 안되고 있음

6. weekly digest email의 click-through 비율이 많이 낮음 => 이메일 참여 유도 상의 문제 추정

 

결론

 

1. 모바일 트래픽 퍼널 분석 필요

컴퓨터 device 사용자보다 mobile 사용자들의 참여율이 상대적으로 많이 떨어진 모습을 보였다.

모바일 상의 유저 참여 경험에 방해가 될만한 요소들을 확인하기 위해 퍼널분석이 필요해보인다.

 

2. weekly digest email - 클릭연계율 상향방안 고려

reengagement email의 ctr이 60%를 상회하는데 반해 weekly digest email의 클릭전환율은 2-30%에 그쳤다.

내용상의 수정 혹은 8/4일 이후의 weekly digest email의 내용 확인이 필요하다.

 

3. retention 상향 방안 고려

가입기간 별 cohort분석 결과 오래 가입한 참여자일수록 그 감소율이 더욱 가속화되었다.

유입 방안이 아닌 기존 참여자들을 락인할 수 있는 방안마련이 필요해보인다.

 

 

배운점

- 프로젝트의 깊이는 스스로 정하는 것이다. 

: 그냥 풀면 되는 SQL문제들과는 달리 더 깊게 생각할수록 파고들 수 있는 곳은 항상 존재한다.

 

- 명확한 기준마련을 위한 경험치의 필요

retention 분석 과정에서 명확한 기준을 설정하지 못하는 모습을 보고 상당량의 경험을 통해 타 부서에서 설득될만한 근거들의 마련이 더욱 필요하다는 생각이 들었다.

 

- 주기 파악은 어떻게 하는 것일까?

과연 일정 주기를 수치 눈때중으로 정의할 수 있을까? 일정 주기가 존재한다는 것의 의미를 너무 쉽게 이야기하는 것은 아닌지 생각했다.

 

- sql문

: extract 함수

https://dondons.tistory.com/44

[SQL] EXTRACT 날짜 함수

 

: date_trunc 함수

https://docs.aws.amazon.com/ko_kr/redshift/latest/dg/r_DATE_TRUNC.html

DATE_TRUNC 함수 - Amazon Redshift