인프런 - 데이터 분석을 위한 SQL 실전편 - 섹션1-유저 인게이지먼트 하락 원인분석

본 내용은 인프런 강의 <데이터 분석을 위한 SQL 실전편>를 수강하며 중요한 점을 정리한 글입니다.

weekly active user 추출

SELECT DATE_TRUNC('week', e.occurred_at) AS week
     , COUNT(DISTINCT user_id) AS weekly_active_user
FROM tutorial.yammer_events e 
WHERE e.occurred_at BETWEEN '2014-04-28 00:00:00' AND '2014-08-25 23:59:59'
AND e.event_type = 'engagement'
GROUP BY week
ORDER BY week

코드해석:

DATE_TRUNC('week', e.occurred_at) AS week: 'occurred_at' 열을 기준으로 주 단위로 날짜를 잘라냅니다. 이것은 주간 데이터를 집계하기 위한 기준이 됩니다.

---

all users, active users 추출

SELECT DATE_TRUNC('day', created_at) AS signup_date
     , COUNT(DISTINCT user_id) AS signup_users
     , COUNT(CASE WHEN activated_at IS NOT NULL THEN user_id ELSE NULL END) AS activated_users
FROM tutorial.yammer_users
WHERE created_at BETWEEN '2014-06-01 00:00:00' AND '2014-08-31 23:59:59'
GROUP BY signup_date

---

Email Actions(특정 주차에 이메일 이벤트가 몇개 들어왔는지 count), Open and CT Rates(특정 이메일을 보낸 이후, 5분 이내에 메일을 클릭했는지 그리고 그 안의 링크를 클릭했는지)

Email Actions(특정 주차에 이메일 이벤트가 몇개 들어왔는지 count)

SELECT DATE_TRUNC('week', occurred_at) AS week
     , action
     , COUNT(DISTINCT user_id) AS cnt_user
FROM tutorial.yammer_emails
WHERE occurred_at BETWEEN '2014-04-28 00:00:00' AND '2018-08-25 23:59:59'
GROUP BY week, action

Open and CT Rates(특정 이메일을 보낸 이후, 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_clickthrough
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.occurred_at BETWEEN '2014-06-01 00:00:00' AND '2014-08-31 23:59:59'
AND e1.action IN ('sent_weekly_digest', 'sent_reengagement_email')
GROUP BY week