Investigating a drop in user engagement
Published: 2024-04-17 13:11:44

## 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
```

![](https://velog.velcdn.com/images/minddd64/post/01db20e3-02c3-41b9-b528-634cff832317/image.png)


## 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
```

![](https://velog.velcdn.com/images/minddd64/post/8d252e41-2060-4e47-9dc4-8f5887593b3e/image.png)

> ```
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
```

![](https://velog.velcdn.com/images/minddd64/post/29e42b8e-bea6-4288-8ae4-b5db0c6ddaaa/image.png)

- 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
```

![](https://velog.velcdn.com/images/minddd64/post/65b06037-57e2-472d-904e-3e47577db3e4/image.png)


- 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
```


![](https://velog.velcdn.com/images/minddd64/post/fe6ebfa2-e7cb-42df-b2fe-368b65e857ea/image.png)

![](https://velog.velcdn.com/images/minddd64/post/f0bbf851-f8a3-447b-890d-1b852054a8f5/image.png)

- 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
```

![](https://velog.velcdn.com/images/minddd64/post/b08f4e0f-670b-4c9c-8095-3166a91159ac/image.png)
- 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
```

![](https://velog.velcdn.com/images/minddd64/post/ba0bd335-add4-4433-9fe6-c6793dc42851/image.png)

- 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.