[Mode Project 1] A Drop in Engagement (4)

Solving the case 4

다음은 프로덕트 사용을 촉진하는 이메일에 대한 데이터가 있다.

홍보이메일을 받고 5분 내에 이메일을 열어보거나, 내부의 링크를 클릭한 사람 수가 점점 줄어든다면 WAU 감소의 원인으로 간주할 수 있다.

이메일과 관련하여 Mode는 두 가지 그래프를 제시한다. 

이메일을 받기만 한 사람 / 받고 열어본사람(5분 이내) / 받고, 5분 이내 링크까지 클릭한 사람을 살펴보자.

여기서 이메일도 두 가지 종류가 있는데, 한 가지 종류의 이메일에 대해서만 스터디 하기로 했다.

 

연습용으로 작성한 쿼리는 아래와 같다.

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_digest_email
      , COUNT(case when e1.action = 'sent_weekly_digest' THEN e2.user_id ELSE NULL END) as weekly_digest_email_open
      , COUNT(case when e1.action = 'sent_weekly_digest' THEN e3.user_id ELSE NULL END) as weekly_digest_email_click
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 e2.user_id = e1.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 e3.user_id = e1.user_id
          and e3.action = 'email_clickthrough'
WHERE e1.action IN ('sent_weekly_digest', 'sent_reengagement_email')
GROUP BY 1
ORDER BY 1

쿼리 뜯어보기

1. weekly_digest 라는 이메일이 보내진 사람 중, 5분 이내 열어본 사람을 파악하기 위해 email table을 LEFT JOIN 

LEFT JOIN tutorial.yammer_emails e2 
          on e2.occurred_at between e1.occurred_at and e1.occurred_at + interval '5 minute'
          and e2.user_id = e1.user_id
          and e2.action = 'email_open'

👉 이메일을 열어보지 않은 사람도 있을 수 있으므로, 받은 사람 기준으로 left join 사용

👉 같은 테이블을 조인하되, action 은 '이메일 오픈' 으로 제한

👉 동일한 요소인 user_id로 엮음

👉 action(이메일 오픈)이 일어난 시간인 e2.occurred_at 이 이메일을 받은 시간(e1.occurred_at)과 5분 이내여야 함.

     + interval '5 minute' 

 

2. 동일한 쿼리로, 이메일을 받은 후 > 5분 이내 링크를 클릭한 사람을 파악하기 위해 한 번 더 join

LEFT JOIN tutorial.yammer_emails e3
          on e3.occurred_at between e1.occurred_at and e1.occurred_at + interval '5 minute'
          and e3.user_id = e1.user_id
          and e3.action = 'email_clickthrough'

3. 주 별로 이메일이 발송된 시점을 묶고 > 이메일을 받고 5분 이내 오픈한 사람 / 링크를 클릭한 사람을 각각 지정

👉 e1 테이블에서는 이메일을 '받은' 유저의 수를 취합할 수 있고

👉 조인한 e2 테이블에서는 이메일을 받고, '열어본' 유저 수를 취합할 수 있고

👉 조인한 e3 테이블에서는 이메일을 받고, '링크를 클릭한' 유저 수를 취합할 수 있음!

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_digest_email
      , COUNT(case when e1.action = 'sent_weekly_digest' THEN e2.user_id ELSE NULL END) as weekly_digest_email_open
      , COUNT(case when e1.action = 'sent_weekly_digest' THEN e3.user_id ELSE NULL END) as weekly_digest_email_click

 

---

강의와 함께 작성하니 이해가 되었지만, 다시 또 해석하며 쓰려니 헷갈린다..!

확실히 여러 데이터를 이렇게 저렇게 가공하면서 쿼리 쓰는 데에 익숙해질 필요가 있음을 느낀다.

문제도 더 많이 풀어보고, 유사 프로젝트도 해보고 싶다😊