[SQL] 유저 인게이지먼트 하락 원인 분석(Mode)-이메일

1. 문제

- 2014년 8월 4일 이후 WAU(주간 활성 유저)가 감소한다. 감소 원인을 파악하라

 

2. 이메일 분석

차트에서 email_clickthroughs 부분이 감소하는 것을 볼 수 있습니다. 보낸 이메일을 통해 들어오는 유저 수가 감소함을 볼 수 있으며, 조금 더 email_clickthroughs에 대해 자세하게 살펴볼 필요가 있다고 생각합니다.

 

Mode Code

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

Taegu Code

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

3. 이메일 분석 - deep

yammer에서는 두가지 종류의 email을 보냅니다. reengagemnt, weekly_digest입니다.

reengagemnt에 대한 email_open과 email_clickthrough는 감소되지않았습니다. 그러나,

weekly_digest에 대한 email_open 수는 유지되나, open 후 링크를 통해 들어오는 email_clickthrough 수가 급감함을 볼 수 있습니다. 

이런 경우 유저에게 보낸 메일의 내용을 확인 하여 급감에 대한 원인을 파악해볼 수 있습니다.

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