유저 인게이지먼트(참여, 활동) 하락 원인 분석_SQL_CASE STUDY_'MODE'

안녕하세요!

SQL을 활용하여 유저 인게이지먼트 하락 원인을 분석하는 내용입니다.
사내 소셜 네트워크 서비스를 제공하는 'Yammer'의 사례를
'MODE' 라는 플랫폼에서 실제 데이터와 유사한 fake data로 진행한 내용입니다.

---

문제 상황

유저 인게이지먼트 대시보드에서 WAU 지표가 하락한 구간을 확인.
* 인게이지먼트는 서비스와 사용자의 상호작용으로 정의.

---

문제 정의

- 목표
2014년 8월 4일 이후 WAU가 감소한 원인을 분석하고 해결 방향을 제시한다.

- 데이터
2013-01-01 ~ 2014-08-31 사이에 가입한 User 데이터셋
: 전체 사용자를 알기 위해 Yammer를 사용하기 시작한 최초의 시점부터 포함한 것으로 추측.

2014-05-01 ~ 2014-08-31 사이에 발생한 Event 데이터셋
: 문제가 발생한 8월과 이전 3개월의 인게이지먼트 추세를 확인하기 위해 기간을 설정한 것으로 추측.

2014-05-01 ~ 2014-8-31 사이의 E-mail 데이터셋
: Event와 동일한 기간에 발생한 E-mail 데이터를 확인하여 원인을 파악하기 위한 것으로 추측.
* Weekly E-mail을 고객에게 발송하고, E-mail 상호작용을 통해 고객을 Yammer 로 불러오는 것이 목적.

---

가설 설정

- 본인이 생각한 가설
공휴일의 영향 : 사내 커뮤니티 서비스이기에 주말 사용자는 적을 것이다.
단발성 마케팅 이벤트 : 단발성 이벤트의 참여 급증으로 인한 증감을 보일 것이다.
기능 오류 : 서비스 기능의 오류로 인해 참여가 감소했을 것이다.
데이터 적재 오류 : 데이터 적재 오류로 인해 참여가 감소한 것으로 보일 것이다.
네트워크 연결 문제 : 네트워크 연결 오류로 인해 데이터가 제대로 수집되지 않았을 것이다.

- Yammer의 분석가들이 생각해낸 가설 (원문 번역 후 일부 수정)
휴일: Yammer와 같은 업무 어플리케이션을 사용하는 사람들은 휴일에 더 낮은 비율로 고용될 수 있습니다. 한 나라가 다른 나라보다 참여도가 훨씬 낮다면, 이것이 원인일 가능성이 있습니다.

손상된 기능: 애플리케이션의 무언가가 고장 났을 가능성이 있고, 따라서 사람들이 사용하는 것은 불가능할 것입니다. 응용 프로그램의 각 부분이 메트릭에 다르게 표시되기 때문에 이를 정확히 파악하기가 조금 어렵습니다. 예를 들어, 가입 흐름의 무언가가 깨져 신규 사용자가 Yammer에 가입하는 것을 방해한다면, 성장도 하락할 것입니다. 모바일 앱이 불안정하고 충돌하면 해당 장치 유형에 대해서만 작업이 중단됩니다.

손상된 추적 코드: 이벤트를 기록하는 코드 자체가 고장났을 수 있습니다. 특정 유형의 이벤트가 절대적으로 0으로 떨어지는 것을 보고 손상된 기능을 제외한다면 이러한(지표가 감소할) 가능성이 있습니다.

봇의 트래픽 이상 징후: 대부분의 주요 웹사이트는 봇으로부터 많은 활동을 봅니다. 봇이 사이트와 상호 작용하기 더 어렵게 만드는 제품이나 인프라가 변경되면 참여도가 감소할 수 있습니다. 패턴이나 특정 이벤트를 통해 봇과 같은 동작을 식별해야 하기 때문에 결정하기 어렵습니다.

사이트에 대한 트래픽 종료: 인터넷 서비스 공급자가 사용자의 사이트를 차단할 수 있습니다.

마케팅 이벤트: 예를 들어, 슈퍼볼 광고는 제품의 가입에 있어 엄청난 증가를 일으킬 수 있습니다. 그러나 일회성 마케팅 통해 진입한 사용자는 예를 들어 친구로부터 추천받은 사용자보다 낮은 비율을 유지하는 경우가 많습니다. 차트에는 연속 7일 기간이 사용되기 때문에 1주일간 높은 참여율을 기록한 후 다음 주에 큰 폭의 참여율을 보일 것으로 예상됩니다. 이를 확인하는 가장 좋은 방법은 마케팅 부서의 누군가에게 최근에 큰 일이 있었는지 물어보는 것입니다.

잘못된 데이터: 나쁜 데이터를 기록하는 방법은 많습니다. 예를 들어, 대부분의 대형 웹 앱은 QA 데이터와 프로덕션 데이터를 구분합니다. 어떤 방식으로든 QA 데이터는 프로덕션 데이터베이스로 이동할 수 있습니다. 이는 매우 적은 수의 사용자로부터 기록된 추가 데이터로 나타날 가능성이 높기 때문에 이 경우에는 문제가 되지 않을 수 있습니다.

크롤러 변경 내용 검색: 많은 트래픽을 수신하는 웹 사이트의 경우 검색 엔진이 인덱싱하는 방식의 변화가 트래픽에 큰 변동을 일으킬 수 있습니다. - 상위권 노출에서 제외되면 트래픽 유입이 하락될 수 있음.

* 잘못된 데이터라는 가설에서의 QA데이터는 Quality Assurance의 약자로 테스트를 위한 사내 트래픽이라고 합니다. 분석 진행을 위해 이와 같은 데이터는 실제 데이터와 명확하게 구분을 해야합니다.

문제의 원인에는 다양한 가능성이 있기 때문에 가장 효율적인 순서로 진행하는 것이 중요합니다.
4 가지 요소를 참고하여 효율적으로 진행하면 좋을 것입니다.
- 경험으로 얻은 업무에 대한 감각
- 커뮤니케이션을 통한 문제와 원인 파악
- 처리하기 쉬운(데이터가 깨끗한 것, 이전에 해봤던 것 등) 것부터 빠르게 진행
- 각 가설의 연관성을 고려하여 적절한 순서로 진행

---

분석

모든 가설에 대한 답을 나열할 수는 없지만, 분석 케이스의 시작부터 해결까지의 사고 과정을 볼 수 있습니다.

1. 가장 확인하기 쉬운 것 중 하나인 성장 지표를 봅니다.
2014-06-01부터 2014-08-31일까지의 3개월 간 단위 가입자와 활성 사용자를 확인해보면,
주중에는 사용자가 높게 나타나고 주말에는 낮게 나타나는 경향을 보입니다.
2014-08-04일 이후의 수치가 낮아졌지만 그 다음 주차부터는 이전 수준으로 회복한 것으로 보입니다.

SELECT DATE_TRUNC('day', created_at) signup_date
     , COUNT(DISTINCT user_id) all_users
     , COUNT(DISTINCT CASE WHEN activated_at IS NOT NULL THEN user_id ELSE NULL END) activated_users
FROM tutorial.yammer_users
WHERE created_at BETWEEN '20140601' AND '20140831'
GROUP BY signup_date
ORDER BY signup_date

---

2. 신규 가입자에서 큰 이상을 확인하지 못했기 때문에 기존 사용자로부터 하락한 것인지 확인해봅니다.
가입 기간을 기준의 코호트를 통해 살펴봅니다.
고객 별로 가입일로부터 현재일까지 얼마의 시간이 지났는지 구분합니다.
여기서는 주 단위로 구분하여 1~9주, 그리고 10주 이상의 그룹을 만들었습니다.
10주 이상의 그룹에서 눈에 띄게 감소하고 있음을 볼 수 있습니다.

SELECT DATE_TRUNC('week', occurred_at) week
     , COUNT(DISTINCT CASE WHEN user_age >= 70 THEN user_id END) week_10ormore
     , COUNT(DISTINCT CASE WHEN user_age BETWEEN 63 AND 69 THEN user_id END) week9
     , COUNT(DISTINCT CASE WHEN user_age BETWEEN 56 AND 62 THEN user_id END) week8
     , COUNT(DISTINCT CASE WHEN user_age BETWEEN 49 AND 55 THEN user_id END) week7
     , COUNT(DISTINCT CASE WHEN user_age BETWEEN 42 AND 48 THEN user_id END) week6
     , COUNT(DISTINCT CASE WHEN user_age BETWEEN 35 AND 41 THEN user_id END) week5
     , COUNT(DISTINCT CASE WHEN user_age BETWEEN 28 AND 34 THEN user_id END) week4
     , COUNT(DISTINCT CASE WHEN user_age BETWEEN 21 AND 27 THEN user_id END) week3
     , COUNT(DISTINCT CASE WHEN user_age BETWEEN 14 AND 20 THEN user_id END) week2
     , COUNT(DISTINCT CASE WHEN user_age BETWEEN 7 AND 13 THEN user_id END) week1
     , COUNT(DISTINCT CASE WHEN user_age < 7 THEN user_id END) under_week1
FROM 
  (SELECT u.user_id
        , e.occurred_at
        , EXTRACT('day' FROM '20140901' - u.activated_at) user_age
  FROM tutorial.yammer_users u
  INNER JOIN tutorial.yammer_events e
  ON u.user_id = e.user_id
  AND e.event_type = 'engagement'
  AND e.event_name = 'login'
  AND e.occurred_at BETWEEN '20140501 00:00:00' AND '20140831 23:59:59'
  WHERE u.activated_at IS NOT NULL) age
GROUP BY week
ORDER BY week

가입 후 10주 이상된 사용자들의 수가 감소하고 있는 모습입니다.

가입 후 한 주 씩 지날 수록 사용자가 감소하는 것은 모든 그룹에서 보이는 일반적인 현상입니다.
* 유지율 관리가 중요함을 알 수 있습니다.

---

3. 가입 후 10주 이상된 기존 유저에 문제가 있다는 것을 통해 일회성 마케팅이나 다른 요인들에 의한
트래픽 영향과 관련 없을 것이라는 생각이 듭니다. 이번에는 이벤트가 발생한 디바이스 별로 확인해봅니다.

SELECT DATE_TRUNC('week', occurred_at) week
     , COUNT(DISTINCT user_id) 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 user_id END) 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 END) 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 END) tablet
FROM tutorial.yammer_events
WHERE event_type = 'engagement' AND event_name = 'login'
AND occurred_at BETWEEN '20140501 00:00:00' AND '20140831 23:59:59'
GROUP BY week
ORDER BY week

8월 4일부터 일주일 간 phone 과 tablet 에서 감소 폭이 상당히 큰 것으로 보이며, computer 도 점차 감소하는 것으로 보입니다.

---

4. 다음으로 이메일 행동에 따른 데이터를 확인해봅니다. 8월 4일부터 일주일 간 이메일에 있는 내용(URL)을
클릭하는 사용자가 눈에 띄게 감소한 것으로 보입니다.

SELECT DATE_TRUNC('week', occurred_at) week
     , COUNT(DISTINCT CASE WHEN action LIKE '%weekly%' THEN user_id END) weekly_emails
     , COUNT(DISTINCT CASE WHEN action LIKE '%reengagement%' THEN user_id END) reengagement_emails
     , COUNT(DISTINCT CASE WHEN action LIKE '%open%' THEN user_id END) email_opens
     , COUNT(DISTINCT CASE WHEN action LIKE '%click%' THEN user_id END) email_clickthroughs
FROM tutorial.yammer_emails
GROUP BY week
ORDER BY week

이메일을 열어보는(email_opens) 사용자는 점진적으로 증가하는 모습으로 보이나,
그 이후의 행동(email_clickthroughs)으로 이어지지 않은 것으로 보입니다.

---

5. 마지막으로 weekly_email 과 reengagement_email 을 수신한 고객들의 행동을 분석해봅니다.
메일을 수신하고 일정 시간(여기서는 5분) 내에 고객들이 다음 행동으로 얼마나 이어지는지 살펴봅니다.

SELECT DATE_TRUNC('week', e1.occurred_at) week
     , COUNT(DISTINCT CASE WHEN e1.action LIKE '%weekly%' THEN e1.user_id END) weekly_email
     , COUNT(DISTINCT CASE WHEN e1.action LIKE '%weekly%' THEN e2.user_id END) weekly_open
     , COUNT(DISTINCT CASE WHEN e1.action LIKE '%weekly%' THEN e3.user_id END) weekly_ctr
     , COUNT(DISTINCT CASE WHEN e1.action LIKE '%reengagement%' THEN e1.user_id END) reengagement_email
     , COUNT(DISTINCT CASE WHEN e1.action LIKE '%reengagement%' THEN e2.user_id END) reengagement_open
     , COUNT(DISTINCT CASE WHEN e1.action LIKE '%reengagement%' THEN e3.user_id END) reengagement_ctr
FROM tutorial.yammer_emails e1
LEFT JOIN tutorial.yammer_emails e2
ON e1.user_id = e2.user_id 
AND e2.occurred_at BETWEEN e1.occurred_at AND e1.occurred_at + INTERVAL '5 MINUTE' 
AND e2.action = 'email_open'
LEFT JOIN tutorial.yammer_emails e3
ON e2.user_id = e3.user_id 
AND e3.occurred_at BETWEEN e2.occurred_at AND e2.occurred_at + INTERVAL '5 MINUTE' 
AND e3.action = 'email_clickthrough'
WHERE e1.occurred_at BETWEEN '20140601 00:00:00' AND '20140831 23:59:59'
GROUP BY week
ORDER BY week

weekly_email 을 수신한 후 5분 이내에 열어 보는(open) 사용자의 비율은 30% 전후로 일정한 듯 보이며,
오픈 후 5분 내에 내용을 클릭하는(clickthrough) 사용자의 비율은 급격하게 감소한 것으로 보입니다.

reengagement_email 의 경우에는 들쭉날쭉해 보이지만 weekly_email에 비해 오픈과 클릭의 비율이 높습니다.
하지만 weekly_email과 비슷하게 오픈 후 5분 내에 내용을 클릭하는(clickthrough) 사용자의 비율이 감소한 것으로 보입니다.

---

결론

'디바이스(phone, tablet), 이메일에서 어떤 문제가 있었는지 확인해보면 좋을 것 같다.'

1. 신규 가입자는 8월 4일 이후에 감소하였다가 이전 수준으로 회복한 것으로 보인다.
2. 가입 시기 별 코호트 확인 결과 가입 후 10주 이상 지난 사용자의 WAU가 감소한 것으로 보인다.
3. 디바이스 별로 감소 폭이 가장 큰 것은 phone과 tablet으로 보인다.
4. 이메일 행동을 분석한 결과 이메일 오픈 후 클릭하는 사용자의 비율이 감소한 것으로 보인다.

데이터를 통해 정확한 원인과 해결 방안을 도출할 수는 없지만 문제 해결을 위한 방향에 기여할 수 있는 분석이었다.



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