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

Solving the case 2

Since growth is normal, it's possible that the dip in engagement is coming from existing users as opposed to new ones. One of the most effective ways to look at this is to cohort users based on when they signed up for the product. This chart shows a decrease in engagement among users who signed up more than 10 weeks prior.

 

유저 코호트 차트라고 함. 특정 시기에 가입한 유저별로 Active User가 감소한다면 retention 정도를 측정할 수 있음.

첫 가입 이후 > 주별로 얼마나 Engagement 를 보이는지 파악할 것.

 

쿼리문이 어렵고, 강의에 설명이 없어서 직접 작성하지 못함.

단, 10주 전에 가입한 사람들의 Engagement 폭이 낮아지는 시점이 있어, 해당 시점 가입자가 고려할 요소라고 볼 수 있음.

 

 

Solving the case 3

디바이스별로 사용자 활동에 차이가 보인다면, 뭔가 문제가 있을 수 있다.

각 디바이스별로 추이를 확대해서 살펴보면 확실히 phone과 tablet이 WAU 감소 시점에 함께 감소함을 볼 수 있다.

그런데 강의에서 그래프만 봤을 때는 가장 아래에 있는 tablet은 전체 사용자 수 자체가 많지 않아서 크게 카운트하지 않는다고 했다.

해당 그래프의 쿼리는 아래와 같다.

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
LIMIT 100

 

쿼리 뜯어보기

1. Event table에서, event_type이 'engagement'이고, event_name이 'login'한 것만 봄 👉 로그인한 활동만 보겠다는 것.

 FROM tutorial.yammer_events e
 WHERE e.event_type = 'engagement'
   AND e.event_name = 'login'

2. 이벤트 발생 시점을 주간으로 보고 

SELECT DATE_TRUNC('week', occurred_at) AS week

3. 디바이스 별로 사용한 user_id 를 중복값없이 합산

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

 

그래프 분석

엑셀에 쿼리로 뽑은 데이터에 증감률을 확인해봤더니, 특정 시기 이후 폰과 태블릿 사용자 수의 급격한 감소가 나타났다.

디바이스와 WAU의 감소가 유의미한 관계에 있다고 볼 수 있다.!