차트에서 email_clickthroughs 부분이 감소하는 것을 볼 수 있습니다. 보낸 이메일을 통해 들어오는 유저 수가 감소함을 볼 수 있으며, 조금 더 email_clickthroughs에 대해 자세하게 살펴볼 필요가 있다고 생각합니다.
Mode Code
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
GROUP BY 1
ORDER BY 1
Taegu Code
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
GROUP BY week
ORDER BY week
3. 이메일 분석 - deep
yammer에서는 두가지 종류의 email을 보냅니다. reengagemnt, weekly_digest입니다.
reengagemnt에 대한 email_open과 email_clickthrough는 감소되지않았습니다. 그러나,
weekly_digest에 대한 email_open 수는 유지되나, open 후 링크를 통해 들어오는 email_clickthrough 수가 급감함을 볼 수 있습니다.
이런 경우 유저에게 보낸 메일의 내용을 확인 하여 급감에 대한 원인을 파악해볼 수 있습니다.
SELECT week,
weekly_opens/CASE WHEN weekly_emails = 0 THEN 1 ELSE weekly_emails END::FLOAT AS weekly_open_rate,
weekly_ctr/CASE WHEN weekly_opens = 0 THEN 1 ELSE weekly_opens END::FLOAT AS weekly_ctr,
retain_opens/CASE WHEN retain_emails = 0 THEN 1 ELSE retain_emails END::FLOAT AS retain_open_rate,
retain_ctr/CASE WHEN retain_opens = 0 THEN 1 ELSE retain_opens END::FLOAT AS retain_ctr
FROM (
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_emails,
COUNT(CASE WHEN e1.action = 'sent_weekly_digest' THEN e2.user_id ELSE NULL END) AS weekly_opens,
COUNT(CASE WHEN e1.action = 'sent_weekly_digest' THEN e3.user_id ELSE NULL END) AS weekly_ctr,
COUNT(CASE WHEN e1.action = 'sent_reengagement_email' THEN e1.user_id ELSE NULL END) AS retain_emails,
COUNT(CASE WHEN e1.action = 'sent_reengagement_email' THEN e2.user_id ELSE NULL END) AS retain_opens,
COUNT(CASE WHEN e1.action = 'sent_reengagement_email' THEN e3.user_id ELSE NULL END) AS retain_ctr
FROM tutorial.yammer_emails e1
LEFT JOIN tutorial.yammer_emails e2
ON e2.occurred_at >= e1.occurred_at
AND e2.occurred_at < 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 >= e2.occurred_at
AND e3.occurred_at < e2.occurred_at + INTERVAL '5 MINUTE'
AND e3.user_id = e2.user_id
AND e3.action = 'email_clickthrough'
WHERE e1.occurred_at >= '2014-06-01'
AND e1.occurred_at < '2014-09-01'
AND e1.action IN ('sent_weekly_digest','sent_reengagement_email')
GROUP BY 1
) a
ORDER BY 1
플랫폼이 새로운 업데이트를 진행할 경우 다양한 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
신규 유저의 감소가 아님을 확인했기 때문의 기존 유저의 감소라고 생각할 수 있습니다. 기존 유저 감소를 파악할 때 가장 효율적인 분석방법은 제품 가입 시점을 기반으로 한 코호트 분석입니다.
코호트 분석은 사용자를 그룹으로 분류하여 그룹의 행동과 유지율을분석할 때 활용하는 기법입니다. 아래 차트를 보게 되면 가입기간이 10주 이상인 유저의 engagement가 감소함을 볼 수 있습니다.
5 weeks와 4 weeks를 필터링 했습니다. 가입 시점을 기준으로 가장 높으며, 점차 하락하는 모슴을 볼 수 있습니다. 이 부분은 대부분의 플랫폼에서 볼 수 있는 추이입니다.
Mode Code
SELECT DATE_TRUNC('week',z.occurred_at) AS "week",
AVG(z.age_at_event) AS "Average age during 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-05-01'
AND e.occurred_at < '2014-09-01'
WHERE u.activated_at IS NOT NULL
) z
GROUP BY 1
ORDER BY 1
LIMIT 100
Mode output
Taegu Code
Mode의 코드는 위의 output과 같이 테이블 형태의 결과물을 얻기 위해 user_age 별 count를 작성하였고, 저는 week별 user_age를 7로 나눈 week2별 group by를 통해 코드를 작성하였습니다. 위의 output과 동일한 결과를 원할 경우 엑셀 피벗테이블을 통해 만들 수 있습니다.
SELECT DATE_TRUNC('week',z.occurred_at) AS "week",
z.week2,
COUNT(DISTINCT(z.user_id))
FROM
(SELECT u.user_id,
e.occurred_at,
DATE_TRUNC('week', u.activated_at) as week,
EXTRACT('day' FROM '2014-09-01'::TIMESTAMP - u.activated_at) AS user_age,
CASE WHEN
(CAST(EXTRACT('day' FROM '2014-09-01'::TIMESTAMP - u.activated_at) AS INTEGER)/ 7)>10
THEN 10
ELSE (CAST(EXTRACT('day' FROM '2014-09-01'::TIMESTAMP - u.activated_at) AS INTEGER) / 7)
END as week2
FROM tutorial.yammer_users u
JOIN tutorial.yammer_events e
ON u.user_id = e.user_id
AND e.event_type ='engagement'
AND e.event_name = 'login'
AND e.occurred_at BETWEEN '2014-05-01' AND '2014-09-01'
WHERE u.activated_at IS NOT NULL) z
GROUP BY 1, 2
가장 먼저 체크해야할 것은 성장입니다.. 왜냐하면 성장은 측정하기도 쉬우며, 대부분의 회사는 이미 추적하고 있습니다. 따라서, 성장의 지표가 되는 신규 유저의 수의 변동이 있는지 확인해 보겠습니다..
신규 유저의 수는 지속해서 증가추세 임을 확인할 수 있습니다.. 따라서, 신규유저의 감소는 아니라고 생각합니다.
Mode Code
SELECT DATE_TRUNC('day',created_at) AS day,
COUNT(*) AS all_users,
COUNT(CASE WHEN activated_at IS NOT NULL THEN u.user_id ELSE NULL END) AS activated_users
FROM tutorial.yammer_users u
WHERE created_at >= '2014-06-01'
AND created_at < '2014-09-01'
GROUP BY 1
ORDER BY 1
Taegu Code
SELECT DATE_TRUNC('day', u.created_at) AS "day",
COUNT(*) AS created_users,
COUNT(CASE WHEN u.activated_at IS NOT NULL THEN u.user_id ELSE NULL END) AS "activated_users"
FROM tutorial.yammer_users u
WHERE u.created_at BETWEEN '2014-06-01' AND '2014-09-01'
GROUP BY "day"
Mode 플랫폼에서는 microsoft의 yammer 제품 데이터를 제공, 분석할 수 있는 환경을 제공합니다.
실제 데이터는 아니지만 실제 데이터와 유사하게 만든 fake 데이터로 SQL을 통해 다양한 가설을 세우고 확인할 수 있습니다.
첫 번째 프로젝트로 yammer의 유저 인게이지먼트 하락 원인 분석을 진행하겠습니다.
1. 문제
- 2014년 8월 4일 이후 WAU(주간 활성 유저)가 감소한다. 감소 원인을 파악하라
2. 데이터
Table 1: Users
This table includes one row per user, with descriptive information about that user's account.
Table 2: Events
This table includes one row per event, where an event is an action that a user has taken on Yammer. These events include login events, messaging events, search events, events logged as users progress through a signup funnel, events around received emails.
Table 3: Email Events
This table contains events specific to the sending of emails. It is similar in structure to the events table above.
yammer에서는 위의 3가지의 테이블을 제공합니다.
우선 yammer에서 제공하는 정답에 대해 공부하고 저만의 가설을 만들어 분석하는 방향으로 진행하고자 합니다.
