DEV Community: minddd64

Understanding Search Functionality

minddd64 — Fri, 19 Apr 2024 11:59:17 +0000

Questions before we create the function

Why does this function make the product better?
How can we measure the effect of the function quantitatively?
How long does it take to create the function
Does this function make the product too complicated?
How dangerous is the function?
How innovative is the function?
Is it aligned with what users want?

Hypothesis that we need to check

Do users use the search function a lot? Among the numerous functions used by users, how commonly is the search function?
How often do users use the search function in one session? (It might be a problem if they use it too often or too rarely)
How much do results clicks occur after searching?
where is the results that is clicked after a search?
The more users search, the more click they do?
What is the retention rate of the search function users?

Summary of problem

Explore whether the search function is necessary
Which part should be improved and how

Data Analysis

1. Percentage of search function usage

SELECT DATE_TRUNC('week', z.session_start) AS week,
       COUNT(*) AS sessions, 
       COUNT(CASE WHEN z.autocompletes > 0 THEN z.session ELSE NULL END) AS with_autocompletes,
       COUNT(CASE WHEN z.runs > 0 THEN z.session ELSE NULL END) AS with_runs
FROM (
SELECT x.session_start,
       x.session,
       x.user_id, 
       COUNT(CASE WHEN x.event_name = 'search_autocomplete' THEN x.user_id ELSE NULL END) AS autocompletes,
       COUNT(CASE WHEN x.event_name = 'search_run' THEN x.user_id ELSE NULL END) AS runs,
       COUNT(CASE WHEN x.event_name LIKE 'search_click_%' THEN x.user_id ELSE NULL END) AS clicks
FROM (
SELECT e.*,
       session.session,
       session.session_start
FROM tutorial.yammer_events e
LEFT JOIN (
SELECT user_id, session, MIN(occurred_at) AS session_start, MAX(occurred_at) AS session_end
FROM(

SELECT bounds.*,
       CASE WHEN last_event >= INTERVAL '10 MINUTE' THEN id
       WHEN last_event IS NULL THEN id 
       ELSE LAG(id, 1) OVER (PARTITION BY user_id ORDER BY occurred_at) END AS session
FROM (
SELECT user_id, event_type, event_name, occurred_at, 
       occurred_at - LAG(occurred_at, 1) OVER (PARTITION BY user_id ORDER BY occurred_at) AS last_event,
       LEAD(occurred_at, 1) OVER (PARTITION BY user_id ORDER BY occurred_at) - occurred_at AS next_event,
       ROW_NUMBER() OVER() AS id
FROM tutorial.yammer_events e
WHERE e.event_type = 'engagement'
ORDER BY user_id, occurred_at
) bounds
WHERE last_event >= INTERVAL '10 MINUTE' OR
      next_event >= INTERVAL '10 MINUTE' OR
      last_event IS NULL OR 
      next_event IS NULL) final
GROUP BY user_id, session) session
ON e.user_id = session.user_id AND
   e.occurred_at >= session.session_start AND
   e.occurred_at <= session.session_end
WHERE e.event_type = 'engagement') x
GROUP BY x.session_start, x.session, x.user_id) z
GROUP BY week

2. The number of sessions with autocompletes

SELECT autocompletes, COUNT(*) AS sessions
FROM (
SELECT x.session_start,
       x.session,
       x.user_id, 
       COUNT(CASE WHEN x.event_name = 'search_autocomplete' THEN x.user_id ELSE NULL END) AS autocompletes,
       COUNT(CASE WHEN x.event_name = 'search_run' THEN x.user_id ELSE NULL END) AS runs,
       COUNT(CASE WHEN x.event_name LIKE 'search_click_%' THEN x.user_id ELSE NULL END) AS clicks
FROM (
SELECT e.*,
       session.session,
       session.session_start
FROM tutorial.yammer_events e
LEFT JOIN (
SELECT user_id, session, MIN(occurred_at) AS session_start, MAX(occurred_at) AS session_end
FROM(

SELECT bounds.*,
       CASE WHEN last_event >= INTERVAL '10 MINUTE' THEN id
       WHEN last_event IS NULL THEN id 
       ELSE LAG(id, 1) OVER (PARTITION BY user_id ORDER BY occurred_at) END AS session
FROM (
SELECT user_id, event_type, event_name, occurred_at, 
       occurred_at - LAG(occurred_at, 1) OVER (PARTITION BY user_id ORDER BY occurred_at) AS last_event,
       LEAD(occurred_at, 1) OVER (PARTITION BY user_id ORDER BY occurred_at) - occurred_at AS next_event,
       ROW_NUMBER() OVER() AS id
FROM tutorial.yammer_events e
WHERE e.event_type = 'engagement'
ORDER BY user_id, occurred_at
) bounds
WHERE last_event >= INTERVAL '10 MINUTE' OR
      next_event >= INTERVAL '10 MINUTE' OR
      last_event IS NULL OR 
      next_event IS NULL) final
GROUP BY user_id, session) session
ON e.user_id = session.user_id AND
   e.occurred_at >= session.session_start AND
   e.occurred_at <= session.session_end
WHERE e.event_type = 'engagement') x
GROUP BY x.session_start, x.session, x.user_id) z
GROUP BY autocompletes

3. The number of sessions with full search

SELECT runs, COUNT(*) AS sessions
FROM (
SELECT x.session_start,
       x.session,
       x.user_id, 
       COUNT(CASE WHEN x.event_name = 'search_autocomplete' THEN x.user_id ELSE NULL END) AS autocompletes,
       COUNT(CASE WHEN x.event_name = 'search_run' THEN x.user_id ELSE NULL END) AS runs,
       COUNT(CASE WHEN x.event_name LIKE 'search_click_%' THEN x.user_id ELSE NULL END) AS clicks
FROM (
SELECT e.*,
       session.session,
       session.session_start
FROM tutorial.yammer_events e
LEFT JOIN (
SELECT user_id, session, MIN(occurred_at) AS session_start, MAX(occurred_at) AS session_end
FROM(

SELECT bounds.*,
       CASE WHEN last_event >= INTERVAL '10 MINUTE' THEN id
       WHEN last_event IS NULL THEN id 
       ELSE LAG(id, 1) OVER (PARTITION BY user_id ORDER BY occurred_at) END AS session
FROM (
SELECT user_id, event_type, event_name, occurred_at, 
       occurred_at - LAG(occurred_at, 1) OVER (PARTITION BY user_id ORDER BY occurred_at) AS last_event,
       LEAD(occurred_at, 1) OVER (PARTITION BY user_id ORDER BY occurred_at) - occurred_at AS next_event,
       ROW_NUMBER() OVER() AS id
FROM tutorial.yammer_events e
WHERE e.event_type = 'engagement'
ORDER BY user_id, occurred_at
) bounds
WHERE last_event >= INTERVAL '10 MINUTE' OR
      next_event >= INTERVAL '10 MINUTE' OR
      last_event IS NULL OR 
      next_event IS NULL) final
GROUP BY user_id, session) session
ON e.user_id = session.user_id AND
   e.occurred_at >= session.session_start AND
   e.occurred_at <= session.session_end
WHERE e.event_type = 'engagement') x
GROUP BY x.session_start, x.session, x.user_id) z
GROUP BY runs

4. Distribution of the clickthrough after full search

SELECT clicks, COUNT(*)
FROM (
SELECT x.session_start,
       x.session,
       x.user_id, 
       COUNT(CASE WHEN x.event_name = 'search_autocomplete' THEN x.user_id ELSE NULL END) AS autocompletes,
       COUNT(CASE WHEN x.event_name = 'search_run' THEN x.user_id ELSE NULL END) AS runs,
       COUNT(CASE WHEN x.event_name LIKE 'search_click_%' THEN x.user_id ELSE NULL END) AS clicks
FROM (
SELECT e.*,
       session.session,
       session.session_start
FROM tutorial.yammer_events e
LEFT JOIN (
SELECT user_id, session, MIN(occurred_at) AS session_start, MAX(occurred_at) AS session_end
FROM(

SELECT bounds.*,
       CASE WHEN last_event >= INTERVAL '10 MINUTE' THEN id
       WHEN last_event IS NULL THEN id 
       ELSE LAG(id, 1) OVER (PARTITION BY user_id ORDER BY occurred_at) END AS session
FROM (
SELECT user_id, event_type, event_name, occurred_at, 
       occurred_at - LAG(occurred_at, 1) OVER (PARTITION BY user_id ORDER BY occurred_at) AS last_event,
       LEAD(occurred_at, 1) OVER (PARTITION BY user_id ORDER BY occurred_at) - occurred_at AS next_event,
       ROW_NUMBER() OVER() AS id
FROM tutorial.yammer_events e
WHERE e.event_type = 'engagement'
ORDER BY user_id, occurred_at
) bounds
WHERE last_event >= INTERVAL '10 MINUTE' OR
      next_event >= INTERVAL '10 MINUTE' OR
      last_event IS NULL OR 
      next_event IS NULL) final
GROUP BY user_id, session) session
ON e.user_id = session.user_id AND
   e.occurred_at >= session.session_start AND
   e.occurred_at <= session.session_end
WHERE e.event_type = 'engagement') x
GROUP BY x.session_start, x.session, x.user_id) z
WHERE runs > 0 
GROUP BY clicks

After full search, zero clickthrough is more than half.

SELECT runs, AVG(clicks) ::FLOAT AS average_clicks
FROM (
SELECT x.session_start,
       x.session,
       x.user_id, 
       COUNT(CASE WHEN x.event_name = 'search_autocomplete' THEN x.user_id ELSE NULL END) AS autocompletes,
       COUNT(CASE WHEN x.event_name = 'search_run' THEN x.user_id ELSE NULL END) AS runs,
       COUNT(CASE WHEN x.event_name LIKE 'search_click_%' THEN x.user_id ELSE NULL END) AS clicks
FROM (
SELECT e.*,
       session.session,
       session.session_start
FROM tutorial.yammer_events e
LEFT JOIN (
SELECT user_id, session, MIN(occurred_at) AS session_start, MAX(occurred_at) AS session_end
FROM(

SELECT bounds.*,
       CASE WHEN last_event >= INTERVAL '10 MINUTE' THEN id
       WHEN last_event IS NULL THEN id 
       ELSE LAG(id, 1) OVER (PARTITION BY user_id ORDER BY occurred_at) END AS session
FROM (
SELECT user_id, event_type, event_name, occurred_at, 
       occurred_at - LAG(occurred_at, 1) OVER (PARTITION BY user_id ORDER BY occurred_at) AS last_event,
       LEAD(occurred_at, 1) OVER (PARTITION BY user_id ORDER BY occurred_at) - occurred_at AS next_event,
       ROW_NUMBER() OVER() AS id
FROM tutorial.yammer_events e
WHERE e.event_type = 'engagement'
ORDER BY user_id, occurred_at
) bounds
WHERE last_event >= INTERVAL '10 MINUTE' OR
      next_event >= INTERVAL '10 MINUTE' OR
      last_event IS NULL OR 
      next_event IS NULL) final
GROUP BY user_id, session) session
ON e.user_id = session.user_id AND
   e.occurred_at >= session.session_start AND
   e.occurred_at <= session.session_end
WHERE e.event_type = 'engagement') x
GROUP BY x.session_start, x.session, x.user_id) z
WHERE runs > 0 
GROUP BY runs

The more users search, the more they click.
Above 13 runs, the amount of sessions decreased -> not accurate

5. The order of clicked contents

SELECT event_name, COUNT(user_id)
FROM tutorial.yammer_events
WHERE event_name LIKE 'search_click_result_%'
GROUP BY event_name
ORDER BY event_name

The order of contents that users click is distributed.
In chart 4, users who click only one result is 2.45%. It implies that it's not the matter of ranking, but the matter of results itself.

6. Retention users

SELECT searches, COUNT(*) AS users
FROM (
SELECT user_id, COUNT(*) AS searches
FROM (
SELECT x.session_start,
       x.session,
       x.user_id, 
       COUNT(CASE WHEN x.event_name = 'search_autocomplete' THEN x.user_id ELSE NULL END) AS autocompletes,
       COUNT(CASE WHEN x.event_name = 'search_run' THEN x.user_id ELSE NULL END) AS runs,
       COUNT(CASE WHEN x.event_name LIKE 'search_click_%' THEN x.user_id ELSE NULL END) AS clicks
FROM (
SELECT e.*,
       session.session,
       session.session_start
FROM tutorial.yammer_events e
LEFT JOIN (
SELECT user_id, session, MIN(occurred_at) AS session_start, MAX(occurred_at) AS session_end
FROM(

SELECT bounds.*,
       CASE WHEN last_event >= INTERVAL '10 MINUTE' THEN id
       WHEN last_event IS NULL THEN id 
       ELSE LAG(id, 1) OVER (PARTITION BY user_id ORDER BY occurred_at) END AS session
FROM (
SELECT user_id, event_type, event_name, occurred_at, 
       occurred_at - LAG(occurred_at, 1) OVER (PARTITION BY user_id ORDER BY occurred_at) AS last_event,
       LEAD(occurred_at, 1) OVER (PARTITION BY user_id ORDER BY occurred_at) - occurred_at AS next_event,
       ROW_NUMBER() OVER() AS id
FROM tutorial.yammer_events e
WHERE e.event_type = 'engagement'
ORDER BY user_id, occurred_at
) bounds
WHERE last_event >= INTERVAL '10 MINUTE' OR
      next_event >= INTERVAL '10 MINUTE' OR
      last_event IS NULL OR 
      next_event IS NULL) final
GROUP BY user_id, session) session
ON e.user_id = session.user_id AND
   e.occurred_at >= session.session_start AND
   e.occurred_at <= session.session_end
WHERE e.event_type = 'engagement') x
GROUP BY x.session_start, x.session, x.user_id) z
WHERE z.runs > 0
GROUP BY user_id) z 
GROUP BY searches
ORDER BY searches

Within a month after the first full search, 1,177 users used full search once more, and 428 users used it twice.
Within a month after the first autocomplete, 1,239 users used autocomplete once more, and 1,003 users used it twice.

Summary of analysis

Both autocomplete (222%) and full search(8%) are used in many sessions, so it's worthy to put more resources.
88% of users search more than twice in full search.
More than half of users don't click any result.
After full search, people used to click several results not only one or two.
The order of contents which users click is distributed.
It implies that users can't get the results they want easily.

Review

If there are not enough data, it can distort the result (especially in ratio)

Investigating a drop in user engagement

minddd64 — Wed, 17 Apr 2024 13:11:44 +0000

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.