SQL 주간 활성 사용자(WAU) 감소 문제 연습

Yammer's A Drop in Engagement

** 강의 영상 출처

https://youtu.be/bLLo9UmjrbQ\

 

1. Yammer

• 비즈니스 내에서의 소셜 네트워크
• 조직 내에서의 사적인 소통 수단

2. Problem 문제 파악하기

• key metric dashboards에서 주간 활성 유저의 수(WAU)가 줄어들었다는 사실을 발견
• 정의된 engagement는 버튼을 클릭했다거나, 로그인했다거나, 홈을 들어가는 등 적어도 한 번이라도 interact했을 경우를 의미
• 발생 원인이 될만한 것들을 적어서 가설을 세우고 테스트를 해보아야 함, '순서'도 중요

3. Digging in

• 테이블 확인
  • 1. 유저 테이블 YAMMER_USERS
    • user_id / created_at (가입 시점) / state (active OR pending) / actiavated_at / company_id (사용 유저의 기업아이디) / language
  • 2. 이벤트 테이블 YAMMER_EVENT
    • user_id / occurred_at / event_type (이벤트 카테고리) / event_name (회원가입, 로그인, 메세지 전송 등..) / location / device
  • 3. 이메일 테이블 YAMMER_EMAILS
    • user_id / occurred_at / action (email_open, email_clickthrough etc ..)

 

4. Preparation and Prioritizing

• '~한 문제가 있지 않을까?' 에서 그치는 것이 아니라, '~할 것이다.' 라고 가설을 세우고 분석을 시작해야 한다.
• 원인 유형
  • Holiday
    • 사내 메신저이기 때문에, 주말이나 공휴일에는 engagement가 떨어졌을 것
  • Broken feature 
    • 기능이 고장났을 경우, (로그인하는 부분, 인증번호 받는 부분) engagement가 떨어졌을 것
  • Broken tracking code
    • 로그를 남기는 기능이 고장났을 경우, 활동이 있더라도 기록을 남기지 못했을 것
  • Traffic anomalied from bots 
    • 사이트에서의 활동을 bots들이 확인하는 경우, 서비스에 변경 사항이 생겼다면 (예를 들어 play 버튼이 run 버튼으로 바뀐 경우, bots들은 알 수 없음) engagement가 줄어들 수밖에 없었을 것
  • Traffic shutdown to your site
    • internet service providers가 yammers 사이트를 차단했을 수 있음
  • Marketing event
    • 마케팅 이벤트가 있었다면, 그 때에 폭발적으로 증가했다가 다시 줄어들어 그런 것은 아닐까
  • Bad data
    • QA data (Qualiti Assurance data, 사내에서 테스트해보기 위한 데이터, 실제 유저가 아님) 를 분리해놓지 않았을 수 있음. 사내 계정과 외부 고객 계정을 분리해놓지 않은 경우
  • Search crawler changes
    • 검색 엔진의 ranking logic이 변경되었더라면, 상단에 떴다가 하단에 위치하게되어 engagement가 떨어졌을 수 있다.

=> 너무 많은 경우가 있기 때문에, 조건에 따라 우선순위를 세우자.

• Experience : 관련 경험이 있는지 여부
• Communication : 마케팅 이벤트가 있었는지 확인하기 위하여 마케팅 부서에 물어보는 등의 소통이 필요
• Speed : 빠르게 체크할 수 있는 것부터 하기, 이전에 분석활용에 쓰기위해  정제해놓은 데이터가 있을 수 있음
• Dependency : 가능성이 있는 후보 원인 중 관련성이 있는 것들을 묶어서 분석해보기

=> 분석 부서에서는 제품이나 서비스, 마케팅, 운영에 대한 정보를 모두 갖고 있지 않기 때문에 항상 다른 부서와 소통을 잘하는 것이 중요하다.

 

5. Solving the case

가설1. 신규 유저가 줄어들어서 engagement가 줄어든 것이다.

• 신규 유저 수 체크는 매우 쉽기 때문에 위의 speed 기준이 부합하여 우선적으로 점검
• 같은 패턴을 반복하고 있기 때문에 별다른 특이점은 없었음

가설2. engagement가 줄어든 원인이 신규 유저가 아니라, 기존 유저의 활동 감소일 것이다.

• 확인해보기 위해서, 유저를 가입 기간에 따라서 그룹화하여 살펴보자 ( cohort users )

리텐션 차트

• 리텐션 차트는 가입 시점 이후 로그인 한 횟수 등 얼마나 engagement가 유지되는지 보여주는 차트인데, 로그인 시점에 가장 높았다가 완만하게 내려가는 모양이 일반적이다. 여기서는 완만하게 내려가다가 Jul 28을 변곡점으로 하여 급격하게 내려가는 형태라는 특이점을 발견
• 마케팅 트래픽이나 검색 엔진에서 낮아진 랭킹 등 신규 유저들이 영향을 받을만한 것들이 아닌, 가입한지 10주가 지난 유저들에게서 이런 engagement drop이 생긴 것 같다는 결론을 내릴 수 있다.

가설3. phone/tablet 디바이스의 engagement가 더 가파르게 떨어진 것으로 보아, 모바일 앱 관련 문제가 원인일 것이다.

• 접속 디바이스별로 engagement 추이 그래프를 확인해보았을 때, phone 차트에서 특정 시점에 engagement가 떨어지는 것이 보여진다.
• 이런 추이 그래프를 비교할 때 주의해야할 점은, 예를 들어 여기에서는 전체 WAU와 phone은 트래픽 수가 비슷하여 비교할만 하지만, tablet의 경우 트래픽 수가 많지 않아서 단독으로 볼 때에는 engagement drop이 확실히 눈에 보이지만, 전체 그래프를 같이 볼 경우 매우 완만해서 잘 보이지 않으므로 주의해야 한다.

• 시점별로 engagement 수가 테이블에 나와있는데, 이러한 값을 "전 주 대비 몇%증가" 로 엑셀 표등을 활용하여 표현해보면 한 눈에 비교할 수 있다. (아래 표)

%로 확인해보니, 8월에 모바일 디바이스 engagement가 얼마나 심하게 떨어졌는지 확인할 수 있었다

컴퓨터의경우, 매년 반복되는 Seasonality 문제라는 것을 발견 & 하지만, 항상 그 후 회복하는 양상을 보였던 반면, 이번에는 회복 속도가 매우 더딤 => 무시할 수 없는 원인이다.

• 기업에서는 데이터베이스가 BI에 연결되어있지 않은 경우, SQL 쿼리를 작성해서 데이터베이스에 쿼리를 주고, 원하는 데이터를 뽑아와서 (엑셀 등으로) 시각화하는 과정을 거치며 데이터를 확인한다.

해당 가설 검증을 통해 세울 수 있는 또다른 가설

• 우리 프로덕트의 engagement는 어떤 채널을 통해서 발생하는가?
  • ex. digest email : 요약해서 보내주는 이메일을 보내줌으로써 engagement 늘리는 방법
  • 모바일 기기는 주로 이메일로 유입될 것이고, 이메일이라는 채널에서 문제가 있을 것이라는 가설을 또 세울 수 있음
  • 이메일 관련 지표를 확인해보자. 

이메일을 열어보는 건 대부분이 하지만, 이메일 링크를 클릭하는 비율은 비교적 낮은 것을 확인할 수 있었고, 링크에 문제가 있다는 사실을 발견할 수 있었음

여기까지 분석함으로써 다음과 같은 사실을 알아낼 수 있었다.

1. PC유저들보다는 모바일 유저들 위주로 살펴보아야하고,
2. "이메일 속 링크 클릭 (disgest email)"에 집중하면 문제를 해결할 방향을 찾을 수 있을 것이다. 

---

분석에 사용된 SQL 쿼리 정리

 

1. WAU (주간 활성사용자) 구하기

SELECT DATE_TRUNC('week', e.occurred_at) AS week,
       COUNT(DISTINCT e.user_id) AS weekly_active_users
  FROM tutorial.yammer_events e
 WHERE e.occured_at BETWEEN '2014-04-28 00:00:00' AND '2014-08-25 23:59:59'
   AND e.event_name = 'engagement'
 GROUP BY week
 ORDER BY week

 

출력 결과

• 보통 누적된 데이터를 조회하기 때문에 WHERE절에 기간 설정 먼저 해주기
• 여기에서는 event_name이 engagement인 것만 포함을 시켰다. 만약 login까지 포함을 시킨다면, 로그인을 하지 않고 접속한 유저들의 활동이 누락될 것. 로그인하지 않고 들어온 사용자 수를 포함시킬건지에 대한 판단이 필요함
• GROUP BY 1 로 써도 무관하다. 1로 쓸 경우, select절에 있는 첫 번째 컬럼인 'week'로 groupby 한다는 의미로 동일함, 하지만 복잡한 쿼리를 짜게될 경우 헷갈리기 때문에, select절 입력시 alias를 주는 것이 더 나은 방법

---

2. 일간 가입자수(Daily signups) 구하기

SELECT DATE_TRUNC('day',created_at) AS signup_date,
       COUNT(user_id) AS signup_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-08-31' AND '2014-08-31 23:59:59'
 GROUP BY signup_date

출력 결과

---

3. Email actions 이메일 받은 유저의 수 구하기

쿼리 1.

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 week
 ORDER BY week

SQL로 피봇팅까지 완료된 출력 결과

쿼리 2.

SELECT DATE_TRUNC('week', ocurred_at) AS week
	, action
    , COUNT(DISTINCT user_id) AS cnt_user
FROM tutorial.yammer_emails
WHERE occurred_at BETWEEN '2014-04-28 00:00:00' AND '2014-08-25 23:59:59'
GROUP BY week, action

피봇팅 되기 전까지 출력되도록 쿼리를 짠 결과

엑셀에 불러와서 피봇테이블 기능을 활용하여 만든 결과, SQL 쿼리로 모든 과정을 다 할 필요는 없다.

---