[mysql] Investigating a Drop in User Engagement (2)

이전 글 너무 길어지고 이번 파트는 중요하다 보니 나눠서 씀 !!

(3) Email Actions

- 주간 단위로 "weekly_digest" 와 "reengagement_email" 의 전환 퍼널 분석을 수행

• 오픈율 (open rate)
• 클릭율 (ctr)
• reengagement 대한 전환율(open rate, click-through rate)

-- mysql version
select week
    , weekly_opens/cast(case when weekly_emails = 0 then 1 else weekly_emails end as float) as weekly_open_rate
    , weekly_ctr/cast(case when weekly_opens = 0 then 1 else weekly_opens end as float) as weekly_ctr
    , retain_opens/cast(case when retain_emails = 0 then 1 else retain_emails end as float) as retain_open_rate
    , retain_ctr/cast(case when retain_opens = 0 then 1 else retain_opens end as float) as retain_ctr

from (
    select date_sub(date(e1.occurred_at), interval weekday(e1.occurred_at) day) as week -- 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 yammer_emails e1
      left join yammer_emails e2
      on e2.occurred_at between e1.occurred_at and e1.occurred_at + interval 5 minute
      and e1.user_id = e2.user_id
      and e2.action = 'email_open'
      
      left join yammer_emails e3
      on e3.occurred_at between e1.occurred_at and e1.occurred_at + interval 5 minute
      and e1.user_id = e3.user_id
      and e3.action = 'email_clickthrough'
      
    where e1.occurred_at between '2014-06-01 00:00:00' and '2014-08-30 23:59:59'
      and e1.action in ('sent_weekly_digest', 'sent_reengagement_email')
    group by week
  ) cbw
order by week

select date_sub(date(e1.occurred_at), interval weekday(e1.occurred) day) 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 between e1.occurred_at and e1.occurred_at + interval 5 minute
  and e1.user_id = e2.user_id
  and e2.action = 'email_open'
  
  left join tutorial.yammer_emails e3
  on e3.occurred_at between e1.occurred_at and e1.occurred_at + interval 5 minute
  and e1.user_id = e3.user_id
  and e3.action = 'email_clickthrough'
  
where e1.occurred_at between '2014-06-01 00:00:00' and '2014-08-30 23:59:59'
  and e1.action in ('sent_weekly_digest', 'sent_reengagement_email')
group by week  -- select 함수에서 집계함수 빼고 나머지 다 group by로