SQL 분석 실습_Investigating a Drop in User Engagement

[무료] [백문이불여일타] 데이터 분석을 위한 SQL 실전편 (무료 미니 코스) - 인프런 | 강의 (inflearn.com)

[무료] [백문이불여일타] 데이터 분석을 위한 SQL 실전편 (무료 미니 코스) - 인프런 | 강의

위의 강의를 듣고 정리한 내용입니다.

---

Investigating a Drop in User Engagement | SQL Analytics Training - Mode

Investigating a Drop in User Engagement | SQL Analytics Training - Mode

 

위 사이트의 데이터를 사용하며 , 해당 사이트에서만 SQL 사용이 가능합니다. (가입 필요)

 

☑️ 문제 상황

2014년 4월 28일부터 2014년 9월 이전까지의 Yammer 사용 고객 engagement를 주 단위로 살펴보았다.

고객 engagement(고객 참여율)은 login을 한 경우를 기준으로 살펴보기로 했다.

사용 가능한 테이블 중 Events라는 테이블을 사용하여 event_type의 컬럼이 'engagement'이며 , event_name의 컬럼은 'login'인 경우만 추출하였다.

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' and '2014-08-31'
      and e.event_type='engagement' and e.event_name='login'
group by week
order by week;

그러면 위와 같은 결과가 나타나며

차트로 그리면 이러한 형태임을 알 수 있다.

차트로 보았을 때에는 주간 사용자가 크게 변화가 없는 것이 아닌가 하는 생각이 들었다.

 

하지만 WAU 증감 비율을 나타내었더니 2014년 8월 4일 이후로 계속 감소하고 있는 것으로 나타났다.

이에 대한 원인을 파악하고자 하는 것이 목표이다.

 

☑️ 가설 설정

1. 해당 주에 공휴일이 많기 때문이다.

(Yammer는 회사 내의 소통을 위한 프로그램이기에 공휴일이 있으면 회사에 출근하지 않는 경우가 생겨 사용률이 감소할 수 있음)

​

2. 특정 디바이스에 문제가 생겼다.

​

3. 마케팅으로 인해 유입된 사람들 중에서 시간이 지날수록 사용하는 고객이 감소하고 있다.

​

4. 마케팅 효과가 감소 등의 이유로 새로 유입하는 고객이 감소했다.

 

☑️ 분석

1. 신규 가입자 추이 파악

신규 가입자는 가입 후 반드시 로그인을 하게 되므로 신규 가입 유저가 감소하면 WAU 감소에도 영향을 줄 수 있다고 한다.

SELECT date_trunc('day',created_at) as signup_date
    ,count(user_id) as signup_users
    ,count(case when activated_at is not null then user_id else null end) as activted_users
from tutorial.yammer_users
where created_at between '2014-06-01' and '2014-09-01'
group by signup_date

8월 4일 이후 감소했는지를 알아보기 위함이기 때문에 모든 것을 파악할 필요는 없다고 생각하였고, 4월 28일부터의 추이가 아닌 6월부터의 추이를 살펴보았다.

users 테이블에서 일자별로 신규 가입한 사람과 신규 활성 고객을 추출했다.

 

일정하게 굴곡이 나타나는 이유는 앞에서 말했듯이 yammer가 회사를 위한 프로그램이기에 주말에는 사용하지 않아 생긴 것으로 예상된다.

​

일자별로 보았을 때 8월 4일 정도에 직전 주보다 약간 감소한 것으로 보이는데 정확하게 판단을 내리기 어려워

주별로 나타내 보았다.

 

SELECT date_trunc('week',created_at) as signup_date
    ,count(user_id) as signup_users
    ,count(case when activated_at is not null then user_id else null end) as activted_users
from tutorial.yammer_users
where created_at between '2014-06-01' and '2014-09-01'
group by signup_date

주별로 나타내니 확실히 8월 4일이 있는 주에 신규 가입자와 신규 활성 유저가 감소한 것으로 보인다.

하지만 8월 4일이 있는 주에만 감소가 확실히 나타나고 이후에는 다시 증가했다.

 

2. 유저 코호트 별 WAU 파악

코호트 별로 WAU를 분석해보면 어떤 주에 가입한 유저가 영향을 많이 준 것인지 파악할 수 있을 것이라 생각되어 분석해보았다.

SELECT DATE_TRUNC('week',z.occurred_at) AS "week",
       COUNT(DISTINCT CASE WHEN z.user_age >70 THEN z.user_id ELSE NULL END) AS "10 weeks",
       COUNT(DISTINCT CASE WHEN z.user_age < 70 AND z.user_age >= 63 THEN z.user_id ELSE NULL END) AS "9 weeks",
       COUNT(DISTINCT CASE WHEN z.user_age < 63 AND z.user_age >= 56 THEN z.user_id ELSE NULL END) AS "8 weeks",
       COUNT(DISTINCT CASE WHEN z.user_age < 56 AND z.user_age >= 49 THEN z.user_id ELSE NULL END) AS "7 weeks",
       COUNT(DISTINCT CASE WHEN z.user_age < 49 AND z.user_age >= 42 THEN z.user_id ELSE NULL END) AS "6 weeks",
       COUNT(DISTINCT CASE WHEN z.user_age < 42 AND z.user_age >= 35 THEN z.user_id ELSE NULL END) AS "5 weeks",
       COUNT(DISTINCT CASE WHEN z.user_age < 35 AND z.user_age >= 28 THEN z.user_id ELSE NULL END) AS "4 weeks",
       COUNT(DISTINCT CASE WHEN z.user_age < 28 AND z.user_age >= 21 THEN z.user_id ELSE NULL END) AS "3 weeks",
       COUNT(DISTINCT CASE WHEN z.user_age < 21 AND z.user_age >= 14 THEN z.user_id ELSE NULL END) AS "2 weeks",
       COUNT(DISTINCT CASE WHEN z.user_age < 14 AND z.user_age >= 7 THEN z.user_id ELSE NULL END) AS "1 week",
       COUNT(DISTINCT CASE WHEN z.user_age < 7 THEN z.user_id ELSE NULL END) AS "Less than a week"
  FROM (
        SELECT e.occurred_at,
               u.user_id,
               DATE_TRUNC('week',u.activated_at) AS activation_week,
               EXTRACT('day' FROM e.occurred_at - u.activated_at) AS age_at_event,
               EXTRACT('day' FROM '2014-09-01'::TIMESTAMP - u.activated_at) AS user_age
          FROM tutorial.yammer_users u
          JOIN tutorial.yammer_events e
            ON e.user_id = u.user_id
           AND e.event_type = 'engagement'
           AND e.event_name = 'login'
           AND e.occurred_at >= '2014-06-10'
           AND e.occurred_at < '2014-09-01'
         WHERE u.activated_at IS NOT NULL
       ) z

 GROUP BY 1
 ORDER BY 1

결과는 위 사진과 같이 나타났으며 이를 복사해서 구글시트에서 비율로 나타내 보았다.

 

가입한 날로부터 시간이 지날수록 WAU가 감소하는 것은 흔하게 있는 일이다.

​

하지만 이를 감안하더라도 7월 28일에서 8월 4일로 넘어가는 시점에 WAU가 많이 감소한 유저는 가입한 지 5주가 된 유저, 6주가 된 유저, 10주 이상이 된 유저들을 뽑을 수 있다.

​

 

3. 디바이스별 WAU 파악

특정 디바이스에 문제가 생긴 경우를 파악할 수 있기에 디바이스별 WAU를 확인해 보았다.

​

event 테이블에는 device라는 컬럼이 존재한다. 이를 통해 어떤 디바이스로 접속했는지 알 수 있을 것이며, iphone 5, samsung, macbook 등 디바이스가 상세하게 분류되어 있다.

이를 휴대폰, 노트북, 태블릿 3가지로 묶어 각 분류별 WAU를 파악해보았다.

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

다른 디바이스에 비해 태블릿의 WAU가 가장 큰 감소를 보이는 것 같다.

 

증감 비율을 나타내 본 결과 8월 4일이 있는 주에 태블릿의 감소폭이 매우 크다.

다른 주들의 감소와 비교해봐도 감소량이 큰 편이다.

휴대폰의 감소량도 태블릿보다는 적지만 2번째로 큰 감소임을 볼 수 있다.

​

컴퓨터 사용자도 WAU가 감소했긴 하지만 5월 26일과 6월 30일에도 감소가 나타났기에 이는 계절성으로 인해

나타나는 결과일 수도 있다. (4~5주에 한 번씩 월말쯤에 나타나는 감소)

하지만 이후에는 계속해서 감소가 있는 것으로 보아 더 자세히 볼 필요는 있는 것 같다.

​

 

4. 이메일 관련 로그 추이

yammer의 대표적인 engagement 발생 경로는 이메일이다.

이에 이메일 관련 로그에 특별한 문제점이 없는지 확인해보았다.

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
where occurred_at between '2014-04-28' and '2014-08-25'
GROUP BY 1
ORDER BY 1

weekly_emails : 이메일 발송 유저 수

email_clickthroughs : 이메일 내 클릭 요소 클릭한 유저 수

email_opens : 이메일 오픈 유저 수

reengagement_emails : reengagement 이메일 발송 유저 수

​

이메일 발송 유저 수는 시간이 지날수록 꾸준히 증가하고 있다.

하지만 8월 4일이 있는 주에 이메일 클릭 수가 감소한 것이 확실하게 나타났다.

 

증감 비율을 비교해봤을 때에도 클릭 유저수가 다른 주에 비해 크게 감소한 것이 나타났다.

이메일 오픈 수도 감소하긴 했지만 그 전주에 오픈 수가 꽤 높은 편이었다는 것을 유의해야 할 것 같다.

​

이메일 오픈, 클릭에 감소가 있었기에 이와 관련한 로그를 자세히 살펴보았다.

이메일을 5분 이내에 오픈하거나 클릭 요소를 클릭한 사람들을 추출했다.

elect 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_diget_email /*발송 유저 수*/
    ,count(case when e1.action = 'sent_weekly_digest' then e2.user_id else null end) as weekly_diget_email_open /*수신 5분 이내 오픈*/
    ,count(case when e1.action = 'sent_weekly_digest' then e3.user_id else null end) as weekly_diget_email_click /*수신 5분 이내 클릭*/
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' and '2014-08-31'
      and e1.action in('sent_weekly_digest','sent_reengagement_email')
group by week;

이메일의 클릭요소를 클릭한 유저가 8월 4일이 있던 주에 감소한 것이 보인다.

 

비율로 살펴봐도 5분 이내에 클릭요소를 클릭한 사람이 8월 4일이 있던 주에 급감한 것을 볼 수 있다.

이후에도 그 비율이 계속 유지가 되는 것으로 보아 관련해서 더 파악해볼 필요가 있다고 생각한다.

 

☑️ 결론

SQL 추출을 통한 분석을 해보았고 간단하게 요약하면

1. 신규 가입자와 신규 활성 유저가 일시적으로 감소 후 다시 증가

2. 가입한 지 5주가 된 유저, 6주가 된 유저, 10주 이상이 된 유저들의 코호트에서 WAU 감소

3. 디바이스 별로 전체적으로 감소가 있었으며, 태블릿의 감소 폭이 매우 컸음

4. 이메일 클릭 수가 크게 감소했고 자세히 들어가서 분석해본 결과, 5분 이내 클릭 수도 급감

으로 나타낼 수 있을 것 같다.

​

위에서 내린 가설을 분석과 연관 지어 살펴보면

1. 해당 주에 공휴일이 많기 때문이다.

이는 다른 공휴일 관련 데이터를 가져와야 분석이 가능할 것 같다.

또한 공휴일이 아닌 여름철 휴가로 인한 감소가 있을 수도 있기에 2014년 이외의 다른 년도 데이터를 불러와서 8월 초에 감소하는 경향이 꾸준히 존재했는지도 파악해보면 좋을 것 같다.

​

2. 특정 디바이스에 문제가 생겼다.

3번째 분석을 보면 태블릿과 휴대폰에서 큰 감소가 있었다.

만약 특정 디바이스에 문제가 있는 것이 사실이라면 태블릿과 휴대폰, 특히 태블릿 쪽에서 문제가 있었을 것이라는 다음 가설을 세울 수 있을 것 같다.

​

3. 마케팅으로 인해 유입된 사람들 중에서 시간이 지날수록 사용하는 고객이 감소하고 있다.

4. 마케팅 효과 감소 등의 이유로 새로 유입하는 고객이 감소했다.

두 가지를 한꺼번에 살펴보자면

일단 신규 가입자 수가 감소한 것이 공휴일, 휴가의 영향이 아니라면 마케팅 효과 감소가 또 다른 원인이 될 수 있을 것 같다.

그리고 코호트 별 분석을 통해 가입한 지 5,6,10주가 된 사람들의 감소한 것은 마케팅 효과 감소의 원인이 될 수 있지 않을까 생각이 든다. 마케팅으로 인해 가입했다가 잘 사용하지 않게 되는 시점인 셈이다.

또한 engagement의 큰 영향을 주는 이메일에서도 감소가 있었기에 이메일을 통한 마케팅의 효과가 감소했기에 이러한 결과가 나올 수도 있지 않을까 예상해본다.

​

---

강의를 보면서 해본 분석을 이렇게 정리해 보았다.

사실 이렇게 적는 게 맞는지도 모르겠지만.. 특히 결론 부분은 어떻게 적어야 하나요 ㅎ

SQL 문법은 많이 익혔지만 이렇게 직접 실습할 기회는 적기에 이 강의를 좋아했고 열심히 들어야지 생각했던 것 같다.(이런 실습 기회 많았으면)

​

실습 데이터 자체도 그렇게 생소한 분야는 아닌 것 같아 나중에 실제로 사용하게 될 때 도움이 될 것 같다.