Summary of the problem situation
- WAU (Weekly active users) is decreasing after 2014-08-04
- Not sure about the cause of the weekly decrease in active users
( In this project, Active Users are counted as logged in user.)
SELECT DATE_TRUNC('week', occurred_at) AS week,
COUNT(DISTINCT user_id) AS weekly_active_user
FROM tutorial.yammer_events
WHERE occurred_at BETWEEN '2014-04-28 00:00:00' AND '2014-08-23 23:59:59'
AND event_type = 'engagement' AND event_type = 'login'
GROUP BY week
ORDER BY week
Data Analysis
1. New users
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 00:00:00' AND '2014-08-31 23:59:59'
GROUP BY signup_date
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 00:00:00' AND '2014-08-31 23:59:59'
GROUP BY signup_date
- At 2014-08-04 week, engagement decreased slightly
- After that, signup users and activated users both are recovered.
2. User Cohort
Retention chart analysis by user age cohort
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 week
ORDER BY week
- Typical Retention pattern which decrease in active users over time.
- However, 10+ weeks users WAU can be seen as affecting WAU because it shows an exceptional sharp drop on the chart
3. WAU per 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)
FROM tutorial.yammer_events e
LEFT JOIN tutorial.yammer_users u
ON e.user_id = u.user_id
WHERE u.activated_at IS NOT NULL
GROUP BY week
ORDER BY week
- After 8/4, Weeckly active user is significantly decreased.
- For computer, it seems seanal decrease, but it's not recovered after that. (need to check)
- Table has huge decrease by around 30%
4. Email data analysis
- Email is common source of engagement
Engagement related to Email
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
SELECT DATE_TRUNC('week',occurred_at) AS week,
action,
COUNT(user_id) as cnt_user
FROM tutorial.yammer_emails
GROUP BY week, action
- Email_clickthrough (clicking link in emails) is decreased
Opening Emails rate
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
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 week
) a
ORDER BY week
- This shows the rate of opening Emails in 5 minutes.
- As we saw above, weekly clickthrough is significantly decreased after 8/4
Summary of Analysis
- Both signup-users and activated-users are decreased after 8/4 and recovered slightly (1. New users)
- Even though decreasing active user is common pattern in retention chart, 10+ week users significantly decreased after 8/4 (2. User Cohort)
- According to WAU of phone and tablet are decreased by 16.5%, 30.8%, high possibility in problems of mobile apps. (3. WAU per device)
- Weekly digest clickthrough, and E-mail opening rate in 5 minutes are all decreased comparing to previous week. (4. Email data analysis)
- Link in Email, phrase inducing clickthrough,or email link in digest email should be checked.
Review
- Cohort analysis
- In this project, WAU was divided by user cohort and device to find the reason of decrease.
- If I divide the whole into parts like this, I can observe the cause of the change that was not known when you looked at the whole.
Top comments (0)