[SQL 케이스 스터디] 유저 인게이지먼트 하락 원인 찾기 - Drop in Engagement

가상 비즈니스 시나리오

📌 Yammer 회사는 소셜 네트워크 (SNS) 를 주축으로 하는 회사로, 문서 공유, 채팅, 글 포스팅 등을 주고 받는 커뮤니티 기반 회사이다. 해당 회사의 Analyst로서 당면한 비즈니스 문제를 해결하는 것이 우리의 임무 !!

Mission. WAU 하락 원인을 찾아오시오!

문제 상황

문제 : 7월 28일 (1442) → 8월 4일 WAU (1266)로 직전주 대비 12.21% 하락한 상황
*WAU : the number of users who logged at least one engagement event during the week starting on that date. 즉, 1주일 내 한번이라도 플랫폼에 로그인한 유저 수

SELECT DATE_TRUNC('week', e.occurred_at) as weekly
			, COUNT(DISTINCT e.user_id) as wau
FROM yammer_events e
WHERE e.event_type = 'engagement'
	And e.event_name = 'login'
GROUP BY 1
ORDER BY 1

가설 설정

• 데이터를 뽑기 전, 가능한 가설을 쭉 나열하라
• 그 후 체크할 가설의 우선순위를 정하고, 어떻게 가설검증을 할 건지도 고민하라

가설 리스트 : 왜 wau가 감소하였나

[디테일한 분석이 필요한 내부 원인]

1. 유입 자체
   1. ⭐ 고객 세그별 유입
      1. 신규 방문이 줆 or 기존 고객 방문이 줆
      2. 기존이라면, 또 미가입자/기가입미연결자/기연결자 유입
   2. 유입 채널별 유입
   3. (앱푸시, 프로모션을 돌리던 게 있다면) 해당 이벤트의 클릭/전환율 떨어지진 않았는가?
   4. 데모별(연령,성별,국가) 유입
   5. 디바이스별 유입

IF, 기존고객 유입이 줄어들었다면,

1. 이탈1 = 영구 탈퇴
   1. 코호트별 이탈율 ex.특정 가입시기별 이탈,
   2. 유입채널별, 디바이스별, 데모별,고객세그별 이탈
2. 이탈2 = 리텐션 = 재방문을 하는가
   1. 코호트별 재방문율 ex.가입시기별, 특정 프로모션별,

[러프한 외부 원인]

1. 계절성이나 일반적인 주기 ? → 증감률 파악
   1. 요일별/시간대별/휴일/주말 : 해당 주차가 휴일은 아닌가 ?
      1. 휴일/주말은 서비스이용자가 증가함
   2. 시즈널리티 : 전년도 대비 동월, 동일 주차 파악, 전월 대비 동일 주차 비교 등
2. 플랫폼 내 이슈 파악
   1. 특정 서비스에 이슈 ex.신규기능오픈, 트래픽 이상, QA 검토 실수로 잘못된 데이터 로깅이 발생하진 않았는지
   2. 마케팅 프로모션이 중단or시작되진 않았는지
   3. 소비자 voc, 부정적인 여론이 많이 들어오진 않았는지
3. 경쟁사 : 경쟁사가 신규 기능을 오픈하진 않았는지, 그래서 고객을 뺏긴것은 아닌지 

 

---

가설 검증

가설1. 계절성이나 주기가 있는 정상적인 문제 아닐까?

select a.weekly
  , a.wau
  , lag(a.wau) over (order by a.weekly) as prev_wau
  , round(100.0 * (a.wau - lag(a.wau) over (order by a.weekly))  / 
              lag(a.wau) over (order by a.weekly)  ,2) rate
from (
  select date_trunc('week', e.occurred_at) as weekly
    , count(distinct user_id) wau
  from tutorial.yammer_events e 
  where e.event_type = 'engagement'
    and date(e.occurred_at) between '2014-04-28' and '2014-08-31'
  group by 1
) a

• WAU 증감률은 3-4주에 걸쳐 한번씩은 감소하는 주기를 보임
• 그러나 8/4일에 보인 감소율은 이례적으로 큰 폭임(-12.27%)
• 또한, 감소 이후 회복이 된 다른 주차와 달리 회복이 아닌 계속된 감소추세를 겪음

SELECT date_trunc('week', sub.occurred_at) as weekly,
  CASE WHEN sub.weekday = 0 THEN 'Sun'
  WHEN sub.weekday = 1 THEN 'Mon'
  WHEN sub.weekday = 2 THEN 'Tue'
  WHEN sub.weekday = 3 THEN 'Wed'
  WHEN sub.weekday = 4 THEN 'Thu'
  WHEN sub.weekday = 5 THEN 'Fri'
  WHEN sub.weekday = 6 THEN 'Sat'
  END AS days,
  COUNT(1) AS activities
FROM(      
      SELECT occurred_at,
            EXTRACT('dow' FROM occurred_at ) AS Weekday
            FROM tutorial.yammer_events
            WHERE occurred_at between '2014-04-28' and '2014-08-31'
    ) sub
GROUP BY 1,2
ORDER BY 1,2

• 8/4주차의 요일별 유저 유입 비율에도 차이가 없음 = 특정요일에 떨어지지 않았음

결론 : 현재 발견된 WAU감소는 이례적인 문제상황 O, 아직 회복 X

---

 

가설2. 특정 유입 채널에서 유입이 빠졌을 것이다

• 유입채널을 볼 수 있는 데이터 X

---

가설3. 특정 디바이스에서 유입이 빠졌을 것이다.

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 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 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 END) AS tablet
FROM tutorial.yammer_events e
WHERE e.event_type = 'engagement'
 #AND e.event_name = 'login'
GROUP BY 1
ORDER BY 1

• 여기서 phone_user에서의 하락을 엿볼 수 있음 !!

결론 : ios/android 모바일 app 서버에서 문제가 발생한 것은 아닌지 체크 !

---

 

가설3. 특정 데모 (연령/성별/국가)에서 유입이 빠졌을 것이다.

• 연령, 성별 칼럼 X

SELECT
  DATE_TRUNC('day',occurred_at) AS date,
  COUNT(CASE WHEN location = 'United States' THEN 1 END) AS location_US,
  COUNT(CASE WHEN location = 'Japan' THEN 1 END) AS location_Japan,
  COUNT(CASE WHEN location = 'Germany' THEN 1 END) AS location_Germany,
  COUNT(CASE WHEN location = 'France' THEN 1 END) AS location_France,
  COUNT(CASE WHEN location = 'United Kingdom' THEN 1 END) AS location_UK
FROM tutorial.yammer_events
GROUP BY 1

• 상위 5개의 국가를 확인했을 때, 모든 국가에서 DROP이 일어남

결론 : Engagement is universal !!

---

 

가설4. 특정 고객 세그의 유입이 줄어들었을 것이다 - 신규 유저 가입 감소

SELECT DATE_TRUNC('day', created_at) AS days,
  COUNT(*) AS all_users,
  COUNT(CASE WHEN state = 'active' THEN 1 END) AS activated_users
FROM tutorial.yammer_users
WHERE created_at > '2014-06-01'
GROUP BY 1
ORDER BY 1

• 8월4일주차에 신규 유입이 급격히 낮아지긴 했으나, 이후 주차에서 회복된 것을 확인

결론 : 근본적인 원인이 신규 유저 가입의 하락만은 아닐 것.

 

---

가설5. 특정 고객 세그의 유입이 줄어들었을 것이다 - 기가입자 방문 감소

SELECT DATE_TRUNC('week', e.occurred_at) as weekly
  , count(distinct e.user_id) exist_wau
FROM tutorial.yammer_events e
INNER JOIN tutorial.yammer_users u
 ON e.user_id = u.user_id and date(e.occurred_at) > date(u.created_at)
WHERE e.event_type = 'engagement'
AND occurred_at between '2014-04-28' and '2014-08-31'
GROUP BY 1
ORDER BY 1

결론 : 기가입자의 방문이 줄어든 것이 맞다 !

• 어떤 기가입자의 방문이 줄어든 것일까? → 가입주기별 코호트를 살펴보자

With engage_tb as (
select user_id
	, min(occurred_at) as occurred_at -- 최소시점 기준 or 방문시점 기준?
from tutorial.yammer_events e
where e.event_type = 'engagement' 
	and e.occurred_At  between '2014-04-28' and '2014-08-31' 
group by 1
)

, create_tb as (
select *
from tutorial.yammer_users u
where date(u.created_at) between '2014-04-28' and '2014-08-31'
)

, final_tb as (
select e.user_id,
		DATE_TRUNC('week', u.created_at) AS signup_week,
    DATE_TRUNC('week', e.occurred_at) AS event_week,
    (DATE_TRUNC('week', e.occurred_at) - DATE_TRUNC('week', u.created_at) )/7 as week_diff,
		YEARWEEK(e.occurred_At) - YEARWEEK(u.created_At) week_diff2,
		FLOOR(DATEDIFF('day', DATE_TRUNC('week', u.created_at), DATE_TRUNC('week', e.occurred_at)) / 7) AS week_diff3
from engage_tb e 
left join create_tb c on e.user_id = u.user_id and e.occur_at >= u.created_at
)

SELECT 
  signup_week,
  COUNT(DISTINCT CASE WHEN week_diff = '0 days' THEN e.user_id END) AS w0,
  COUNT(DISTINCT CASE WHEN week_diff = '1 days' THEN e.user_id END) AS w1,
  COUNT(DISTINCT CASE WHEN week_diff = '2 days' THEN e.user_id END) AS w2,
  COUNT(DISTINCT CASE WHEN week_diff = '3 days' THEN e.user_id END) AS w3,
  COUNT(DISTINCT CASE WHEN week_diff = '4 days' THEN e.user_id END) AS w4,
  COUNT(DISTINCT CASE WHEN week_diff = '5 days' THEN e.user_id END) AS w5,
  COUNT(DISTINCT CASE WHEN week_diff = '6 days' THEN e.user_id END) AS w6,
  COUNT(DISTINCT CASE WHEN week_diff = '7 days' THEN e.user_id END) AS w7,
  COUNT(DISTINCT CASE WHEN week_diff = '8 days' THEN e.user_id END) AS w8,
  COUNT(DISTINCT CASE WHEN week_diff = '9 days' THEN e.user_id END) AS w9,
  COUNT(DISTINCT CASE WHEN week_diff = '10 days' THEN e.user_id END) AS w10
FROM final_tb e
GROUP BY signup_week
ORDER BY signup_week;

signup_week w0 w1 w2 w3 w4 w5 w6 w7 w8 w9 w10

2014-04-28 0:00	159	118	66	45	30	32	26	21	20	21	19
2014-05-05 0:00	160	104	67	45	34	22	18	24	11	17	12
2014-05-12 0:00	186	144	77	64	40	26	20	19	23	18	15
2014-05-19 0:00	177	121	79	50	40	28	21	32	23	23	23
2014-05-26 0:00	186	117	78	53	34	24	33	30	18	18	13
2014-06-02 0:00	197	133	83	60	49	42	29	25	26	17	17
2014-06-09 0:00	198	146	85	56	44	41	34	28	22	21	14
2014-06-16 0:00	222	135	89	57	41	31	28	25	15	17	12
2014-06-23 0:00	210	151	100	62	44	30	24	19	15	15	0
2014-06-30 0:00	199	130	82	60	43	34	33	26	14	0	0
2014-07-07 0:00	223	152	95	83	52	39	26	23	0	0	0
2014-07-14 0:00	215	144	91	52	33	19	20	0	0	0	0
2014-07-21 0:00	228	156	82	59	40	31	0	0	0	0	0
2014-07-28 0:00	234	154	94	64	47	0	0	0	0	0	0
2014-08-04 0:00	189	126	69	48	0	0	0	0	0	0	0
2014-08-11 0:00	250	163	82	0	0	0	0	0	0	0	0
2014-08-18 0:00	259	173	0	0	0	0	0	0	0	0	0
2014-08-25 0:00	266	0	0	0	0	0	0	0	0	0	0

 

• 가입후 5주차가 지날 수록 점점 참여율이 떨어지고 있음
• 하지만, 특출나게 뭐 ‘특정 가입시기’에서의 리텐션이 떨어지는 것은 아닌 것 같음

---

가설6. 이메일 앱푸시의 효과가 떨어진 것은 아닐까?

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

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' and '2014-08-25'
group by 1

 

• 참여유도 sent 횟수는 약 2천회 → 4천회까지 증가함
• 이메일 발송 대비 오픈 전환 cvr은 변화 미미함
• 이메일 오픈 대비 클릭 전환 cvr의 평균 40후반~50초반이었던 것이 30초반%로 하락함
• 이를 뒤따라 발송 대비 클릭 전환 또한 10% 초반으로 하락함

결론 : 참여 유도 이메일 발송은 증가하는 반면, 실제 클릭 CVR은 저조함 !

---

최종 가설 채택 + 액션 제안

1. 현재 문제상황은 이례적인 WAU하락 상황이 맞다.

→ WAU 하락 주요 원인에 대한 모니터링용 대시보드 제작

→ 주요 지표를 회의를 통해서 선정한 후 대시보드 만들기

2. 특히, Phone 디바이스에서의 이탈이 나타났다.

→ 특정 디바이스에 대한 UI 개선 실험

→ 앱버전, 로딩시간 분석 → 최적화하기

3. 신규 가입의 감소보다 기가입자의 방문이 줄어들었음, 특히 가입 이후 5주가 흐르면 재방문율이 낮아짐

→ W4~W5 유저 대상 리마인드 이메일/푸시 캠페인 ( “처음 기능 경험 리마인드”, “혜택 제공” 등 유도 메시지)

→ 리텐션을 올릴 수 있는 개인화 넛지

4. 참여 유도 이메일 발송량 대비 링크 클릭 유도 효과가 X

→ 제목 + CTA 개선 실험 (e.g. A/B 제목 3종 테스트)

→ 모바일 최적화 여부 확인 (예: 버튼 위치, 클릭하기 어려운 경우)

→ 클릭하지 않은 유저 대상 3일 뒤 Follow-up 메일 자동화