[SQL] 유저 인게이지먼트 하락 원인 분석(Mode)-device(장치)

1. 문제

- 2014년 8월 4일 이후 WAU(주간 활성 유저)가 감소한다. 감소 원인을 파악하라

 

2. 기기타입 별 분석

플랫폼이 새로운 업데이트를 진행할 경우 다양한 os, 기기 별 테스트를 진행합니다. 그 이유는 호환성 이슈가 존재하기 때문입니다. 따라서 기기타입 별 분석을 통해 원인은 분석해보겠습니다.

Mode Code

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

 

 

Taegu Code

총 26개의 기기가 존재하며, device, brand 별 분석을 진행하겠습니다. 마지막줄 삼성 오타가 있네요. yammer쪽 데이터 베이스에 잘못 넣은 것 같습니다.

SELECT DISTINCT(e.device) FROM tutorial.yammer_events e

Engagement by Device

전체적으로 감소하는 추세를 보이기는 하지만, 테블릿과 휴대폰에서 급격한 감소를 보이고 있습니다. 웹과 어플의 경우 구조적으로 다를 수 있기 때문에 어플 관련하여 문제가 발생했을 수 있다고 생각합니다. 따라서 관련자에게 문의를 할 필요가 있습니다.

SELECT DATE_TRUNC('week', e.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'
AND e.occurred_at BETWEEN '2014-06-01' AND '2014-09-01'
GROUP BY 1

Engagement by brand

Apple 유저의 수가 많아 다른 브랜드의 차트가 잘 보이지 않습니다. 이런 경우 정규화 또는 증감 수치를 통해 확인할 수 있습니다. 브랜드에 따른 뚜렷한 감소 추세는 보이지 않습니다.

SELECT DATE_TRUNC('week', e.occurred_at) AS week,
       COUNT(DISTINCT e.user_id) AS weekly_active_users,
       COUNT(DISTINCT CASE WHEN e.device IN ('acer aspire desktop','acer aspire notebook')
          THEN e.user_id ELSE NULL END) AS acer,
       COUNT(DISTINCT CASE WHEN e.device IN ('kindle fire','amazon fire phone')
          THEN e.user_id ELSE NULL END) AS amazon,
       COUNT(DISTINCT CASE WHEN e.device IN ('ipad mini','ipad air','iphone 5','iphone 5s','iphone 4s','macbook pro','macbook air','mac mini') 
          THEN e.user_id ELSE NULL END) AS apple,
       COUNT(DISTINCT CASE WHEN e.device IN ('asus chromebook')
          THEN e.user_id ELSE NULL END) AS asus,
       COUNT(DISTINCT CASE WHEN e.device IN ('dell inspiron desktop','dell inspiron notebook')
          THEN e.user_id ELSE NULL END) AS dell,
       COUNT(DISTINCT CASE WHEN e.device IN ('hp pavilion desktop')
          THEN e.user_id ELSE NULL END) AS hp,
       COUNT(DISTINCT CASE WHEN e.device IN ('ahtc one')
          THEN e.user_id ELSE NULL END) AS htc,
       COUNT(DISTINCT CASE WHEN e.device IN ('lenovo thinkpad')
          THEN e.user_id ELSE NULL END) AS lenovo,
       COUNT(DISTINCT CASE WHEN e.device IN ('windows surface')
          THEN e.user_id ELSE NULL END) AS ms,
       COUNT(DISTINCT CASE WHEN e.device IN ('nexus 10','nexus 7','nexus 5') 
          THEN e.user_id ELSE NULL END) AS nexus,          
       COUNT(DISTINCT CASE WHEN e.device IN ('nokia lumia 635') 
          THEN e.user_id ELSE NULL END) AS nokia,          
       COUNT(DISTINCT CASE WHEN e.device IN ('samsumg galaxy tablet','samsung galaxy note','samsung galaxy s4') 
          THEN e.user_id ELSE NULL END) AS samsung                    
FROM tutorial.yammer_events e
WHERE e.event_type = 'engagement'
AND e.event_name = 'login'
AND e.occurred_at BETWEEN '2014-06-01' AND '2014-09-01'
GROUP BY 1

-- SELECT DISTINCT(e.device) FROM tutorial.yammer_events e