Investigating a Drop in User Engagement

# prerequisites
!pip install pandasql
!pip install plotly

# data tools
import pandas as pd
import pandasql					# for querying pandas dataframes
from pandasql import sqldf

# plotting tools
import plotly.graph_objects as go
import plotly.express as px

import plotly.io as pio				# output Plotly charts to HMTL

emails_path = '../src/yammer_emails.csv'
events_path = '../src/yammer_events.csv'
users_path = '../src/yammer_users.csv'

yammer_emails = pd.read_csv(emails_path, sep=',', header=0)
yammer_emails

yammer_events = pd.read_csv(events_path, sep=',', header=0)
yammer_events

yammer_users = pd.read_csv(users_path, sep=',', header=0)
yammer_users

q = """
SELECT strftime('%Y-%m-%d', occurred_at, 'weekday 0', '-6 days') AS week, COUNT(DISTINCT user_id) AS weekly_active_users
FROM yammer_events
WHERE occurred_at >= '2014-05-01 00:00:00'
AND occurred_at < '2014-09-01 00:00:00'
AND event_type = 'engagement'
GROUP BY 1
ORDER BY 1;
"""

weekly_active_users = pandasql.sqldf(q, globals())

week = weekly_active_users['week']
count_users = weekly_active_users['weekly_active_users']

fig1 = go.Figure()
fig1.add_trace(go.Scatter(x=week, y=count_users, mode='lines', name='num_users'))
fig1.update_layout(title='Weekly Active Users', xaxis_title='Week of', yaxis_title='Number of users')
pio.write_html(fig1, file='figures/fig1.html', auto_open=True)

# engagement rate = num of engagments / num of users
q = """
SELECT strftime('%Y-%m-%d', occurred_at) AS day, COUNT(*) * 1.00 / COUNT(DISTINCT user_id) AS engagement_rate
FROM yammer_events
WHERE occurred_at >= '2014-05-01 00:00:00'
AND occurred_at < '2014-09-01 00:00:00'
AND event_type = 'engagement'
GROUP BY 1
ORDER BY 1
"""

daily_engagement = pandasql.sqldf(q, globals())

daily_engagement.head()

day = daily_engagement['day']
engagement_rate = daily_engagement['engagement_rate']

fig2 = go.Figure()
fig2.add_trace(go.Scatter(x=day, y=engagement_rate, mode='lines', name='engagement_rate'))
fig2.update_layout(title='Daily Engagement', xaxis_title='Day', yaxis_title='Engagement rate')
pio.write_html(fig2, file='figures/fig2.html', auto_open=True)

q = """
SELECT strftime('%w', occurred_at) AS dow,
       CASE strftime('%w', occurred_at)
       WHEN '0' THEN 'Sun'
       WHEN '1' THEN 'Mon'
       WHEN '2' THEN 'Tues'
       WHEN '3' THEN 'Wed'
       WHEN '4' THEN 'Thurs'
       WHEN '5' THEN 'Fri'
       WHEN '6' THEN 'Sat'
       ELSE NULL END AS day_of_week,
       COUNT(*) * 1.00 / COUNT(DISTINCT user_id) AS active_users
FROM yammer_events
WHERE occurred_at >= '2014-05-01 00:00:00'
AND occurred_at < '2014-09-01 00:00:00'
AND event_type = 'engagement'
GROUP BY 1
ORDER BY 1
"""

dow_engagement = pandasql.sqldf(q, globals())

dow_engagement.head()

day = dow_engagement['day_of_week']
engagement_rate = dow_engagement['active_users']

fig3 = go.Figure()
fig3.add_trace(go.Bar(x=day, y=engagement_rate))
fig3.update_layout(title='Engagement by Day of Week', xaxis_title='Day of Week', yaxis_title='Engagement rate')
pio.write_html(fig3, file='figures/fig3.html', auto_open=True)

q = """
SELECT strftime('%H', occurred_at) AS hour,
       COUNT(*) * 1.00 / COUNT(DISTINCT user_id) AS engagement_rate
FROM yammer_events
WHERE occurred_at >= '2014-05-01 00:00:00'
AND occurred_at < '2014-9-01 00:00:00'
AND event_type = 'engagement'
GROUP BY 1
ORDER BY 1
"""

time_day = pandasql.sqldf(q, globals())

time_day.head()

hour = time_day['hour']
engagement_rate = time_day['engagement_rate']

fig4 = go.Figure()
fig4.add_trace(go.Bar(x=hour, y=engagement_rate))
fig4.update_layout(title='Engagement by Time of Day', xaxis_title='Hour', yaxis_title='Engagement rate')
pio.write_html(fig4, file='figures/fig4.html', auto_open=True)

q = """
SELECT
  strftime('%Y-%m-%d', created_at) AS day,
  COUNT(*) AS all_users,
  COUNT(CASE WHEN activated_at IS NOT NULL THEN user_id ELSE NULL END) AS activated_users
FROM yammer_users
WHERE created_at >= '2014-05-01 00:00:00'
AND created_at < '2014-09-01 00:00:00'
GROUP BY 1
ORDER BY 1
"""

growth = pandasql.sqldf(q, globals())

day = growth['day']
all_users = growth['all_users']
activated_users = growth['activated_users']

growth.head()

fig5 = go.Figure()
fig5.add_trace(go.Scatter(x=day, y=all_users, mode='lines', name='all_users'))
fig5.add_trace(go.Scatter(x=day, y=activated_users, mode='lines', name='activated_users'))
fig5.update_layout(title='Daily signups', xaxis_title='Date', yaxis_title='Number of users')
pio.write_html(fig5, file='figures/fig5.html', auto_open=True)

q = """
SELECT DISTINCT event_name
FROM yammer_events
"""

events = pandasql.sqldf(q, globals())

events.head(50)

q = """
SELECT
  strftime('%Y-%m-%d', occurred_at, 'weekday 0', '-6 days') AS week_of,
  COUNT(DISTINCT CASE WHEN event_name IN ('create_user', 'enter_email', 'enter_info', 'complete_signup') THEN user_id ELSE NULL END) AS signup,
  COUNT(DISTINCT CASE WHEN event_name IN ('login', 'home_page') THEN user_id ELSE NULL END) AS login,
  COUNT(DISTINCT CASE WHEN event_name IN ('view_inbox', 'like_message', 'send_message') THEN user_id ELSE NULL END) AS inbox,
  COUNT(DISTINCT CASE WHEN event_name IN ('search_run', 'search_autocomplete', 'search_click_result_1', 'search_click_result_2', 'search_click_result_3', 'search_click_result_4', 'search_click_result_5', 'search_click_result_6', 'search_click_result_7', 'search_click_result_8', 'search_click_result_9', 'search_click_result_10') THEN user_id ELSE NULL END) AS search_and_click
FROM yammer_events
WHERE occurred_at >= '2014-05-01 00:00:00'
AND occurred_at < '2014-09-01 00:00:00'
GROUP BY 1
ORDER BY 1
"""

event_engagement = pandasql.sqldf(q, globals())

week_of = event_engagement['week_of']
signup = event_engagement['signup']
login = event_engagement['login']
inbox = event_engagement['inbox']
search_and_click = event_engagement['search_and_click']

fig6 = go.Figure()
fig6.add_trace(go.Scatter(x=week_of, y=signup, mode='lines', name='signup'))
fig6.add_trace(go.Scatter(x=week_of, y=login, mode='lines', name='login'))
fig6.add_trace(go.Scatter(x=week_of, y=inbox, mode='lines', name='inbox'))
fig6.add_trace(go.Scatter(x=week_of, y=search_and_click, mode='lines', name='search and click'))
fig6.update_layout(title='Engagement by Event Type', xaxis_title='Week of', yaxis_title='Number of users')
pio.write_html(fig6, file='figures/fig6.html', auto_open=True)

q = """
-- Selects Top 5 countries of all-time
WITH region AS (SELECT location, COUNT(user_id) AS num_users FROM yammer_events WHERE occurred_at >= '2014-05-01 00:00:00' AND occurred_at < '2014-09-01 00:00:00' AND event_type = 'engagement' GROUP BY 1 ORDER BY 2 DESC),
top AS (SELECT region.location, region.num_users, ROW_NUMBER() OVER (ORDER BY num_users DESC) AS rank FROM region)
SELECT strftime('%Y-%m-%d', e.occurred_at, 'weekday 0', '-6 days') AS week_of,
       e.location,
       COUNT(DISTINCT e.user_id) AS num_users
FROM yammer_events e
INNER JOIN top
ON e.location = top.location
WHERE occurred_at >= '2014-05-01 00:00:00' 
AND occurred_at < '2014-09-01 00:00:00' 
AND event_type = 'engagement'
AND top.rank <=5
GROUP BY 1, 2
ORDER BY 1, 3 DESC
"""

top_regions = pandasql.sqldf(q, globals())

top_regions.head(10)

fig7 = px.line(top_regions, x="week_of", y="num_users", color="location", title='Weekly Engagement by Region')
#iplot(fig7)
pio.write_html(fig7, file='figures/fig7.html', auto_open=True)

q = """
SELECT DISTINCT device
FROM yammer_events
"""

devices = pandasql.sqldf(q, globals())

devices.head(50)

q = """
SELECT 
  strftime('%Y-%m-%d', occurred_at, 'weekday 0', '-6 days') AS week_of,
  COUNT(DISTINCT user_id) AS weekly_active_users,
  COUNT(DISTINCT CASE WHEN device IN ('dell inspiron notebook', 'macbook air', 'macbook pro', 'lenovo thinkpad', 'acer aspire notebook', 'asus chromebook', 'acer aspire desktop', 'mac mini', 'hp pavillion desktop', 'dell inspiron desktop') THEN user_id ELSE NULL END) AS desktop,
  COUNT(DISTINCT CASE WHEN device IN ('iphone 5', 'iphone 4s', 'iphone 5s', 'nexus 5', 'samsung galaxy s4', 'htc one', 'nokia lumia 635', 'samsung galaxy note', 'amazon fire phone') THEN user_id ELSE NULL END) AS phone,
  COUNT(DISTINCT CASE WHEN device IN ('windows surface', 'kindle fire', 'ipad mini', 'nexus 7', 'samsung galaxy tablet', 'ipad air', 'nexus 10') THEN user_id ELSE NULL END) AS tablet
FROM yammer_events
WHERE occurred_at >= '2014-05-01 00:00:00'
AND occurred_at < '2014-09-01 00:00:00'
AND event_type = 'engagement'
GROUP BY 1
ORDER BY 1
"""

device_engagement = pandasql.sqldf(q, globals())

week_of = device_engagement['week_of']
weekly_active_users = device_engagement['weekly_active_users']
desktop = device_engagement['desktop']
phone = device_engagement['phone']
tablet = device_engagement['tablet']

fig8 = go.Figure()
fig8.add_trace(go.Scatter(x=week_of, y=weekly_active_users, mode='lines', name='weekly active users'))
fig8.add_trace(go.Scatter(x=week_of, y=desktop, mode='lines', name='desktop'))
fig8.add_trace(go.Scatter(x=week_of, y=phone, mode='lines', name='phone'))
fig8.add_trace(go.Scatter(x=week_of, y=tablet, mode='lines', name='tablet'))
fig8.update_layout(title='Engagement by Device Type', xaxis_title='Week of', yaxis_title='Number of users')
pio.write_html(fig8, file='figures/fig8.html', auto_open=True)

q = """
SELECT
  strftime('%Y-%m-%d', occurred_at, 'weekday 0', '-6 days') AS week_of,
  COUNT(DISTINCT CASE WHEN device IN ('iphone 4s', 'iphone 5', 'iphone 5s') THEN user_id ELSE NULL END) AS "iOS",
  COUNT(DISTINCT CASE WHEN device IN ('nexus 5', 'samsung galaxy s4', 'htc one', 'nokia lumia 635', 'samsung galaxy note', 'amazon fire phone') THEN user_id ELSE NULL END) AS "Other"
FROM yammer_events
WHERE occurred_at >= '2014-05-01 00:00:00' 
AND occurred_at < '2014-09-01 00:00:00'
AND event_type = 'engagement'
GROUP BY 1
ORDER BY 1
"""

os_engagement = pandasql.sqldf(q, globals())

week_of = os_engagement['week_of']
iOS = os_engagement['iOS']
Other = os_engagement['Other']

fig9 = go.Figure()
fig9.add_trace(go.Scatter(x=week_of, y=iOS, mode='lines', name='iOS'))
fig9.add_trace(go.Scatter(x=week_of, y=Other, mode='lines', name='Other'))
fig9.update_layout(title='Engagement by Phone OS', xaxis_title='Week of', yaxis_title='Number of users')
pio.write_html(fig9, file='figures/fig9.html', auto_open=True)

q = """
SELECT 
  strftime('%Y-%m-%d', ue.occurred_at, 'weekday 0', '-6 days') AS week_of,
  COUNT(DISTINCT CASE WHEN ue.user_age < 7 THEN ue.user_id ELSE NULL END) AS '<1 week old',
  COUNT(DISTINCT CASE WHEN ue.user_age >= 7 AND ue.user_age < 14 THEN ue.user_id ELSE NULL END) AS '1 week old',
  COUNT(DISTINCT CASE WHEN ue.user_age >= 14 AND ue.user_age < 21 THEN ue.user_id ELSE NULL END) AS '2 weeks old',
  COUNT(DISTINCT CASE WHEN ue.user_age >= 21 AND ue.user_age < 28 THEN ue.user_id ELSE NULL END) AS '3 weeks old',
  COUNT(DISTINCT CASE WHEN ue.user_age >= 28 AND ue.user_age < 35 THEN ue.user_id ELSE NULL END) AS '4 weeks old',
  COUNT(DISTINCT CASE WHEN ue.user_age >= 35 AND ue.user_age < 42 THEN ue.user_id ELSE NULL END) AS '5 weeks old',
  COUNT(DISTINCT CASE WHEN ue.user_age >= 42 AND ue.user_age < 49 THEN ue.user_id ELSE NULL END) AS '6 weeks old',
  COUNT(DISTINCT CASE WHEN ue.user_age >= 49 AND ue.user_age < 56 THEN ue.user_id ELSE NULL END) AS '7 weeks old',
  COUNT(DISTINCT CASE WHEN ue.user_age >= 56 AND ue.user_age < 63 THEN ue.user_id ELSE NULL END) AS '8 weeks old',
  COUNT(DISTINCT CASE WHEN ue.user_age >= 63 AND ue.user_age < 70 THEN ue.user_id ELSE NULL END) AS '9 weeks old',
  COUNT(DISTINCT CASE WHEN ue.user_age >= 70 THEN ue.user_id ELSE NULL END) AS '10+ weeks old'
FROM (SELECT e.occurred_at, u.user_id, CAST((julianday('2014-09-01') - julianday(u.activated_at)) AS INT) AS user_age
      FROM yammer_users u
      JOIN yammer_events e
      ON e.user_id = u.user_id
      WHERE e.occurred_at >= '2014-05-01 00:00:00'
      AND e.occurred_at < '2014-09-01 00:00:00'
      AND e.event_type = 'engagement'
      AND u.activated_at IS NOT NULL) ue
GROUP BY 1
ORDER BY 1
"""

age_cohort = pandasql.sqldf(q, globals())

age_cohort.head(50)

week_of = age_cohort['week_of']
y0 = age_cohort['<1 week old']
y1 = age_cohort['1 week old']
y2 = age_cohort['2 weeks old']
y3 = age_cohort['3 weeks old']
y4 = age_cohort['4 weeks old']
y5 = age_cohort['5 weeks old']
y6 = age_cohort['6 weeks old']
y7 = age_cohort['7 weeks old']
y8 = age_cohort['8 weeks old']
y9 = age_cohort['9 weeks old']
y10 = age_cohort['10+ weeks old']

fig10 = go.Figure()
fig10.add_trace(go.Scatter(x=week_of, y=y0, mode='lines', name='<1 week old'))
fig10.add_trace(go.Scatter(x=week_of, y=y1, mode='lines', name='1 week old'))
fig10.add_trace(go.Scatter(x=week_of, y=y2, mode='lines', name='2 weeks old'))
fig10.add_trace(go.Scatter(x=week_of, y=y3, mode='lines', name='3 weeks old'))
fig10.add_trace(go.Scatter(x=week_of, y=y4, mode='lines', name='4 weeks old'))
fig10.add_trace(go.Scatter(x=week_of, y=y5, mode='lines', name='5 weeks old'))
fig10.add_trace(go.Scatter(x=week_of, y=y6, mode='lines', name='6 weeks old'))
fig10.add_trace(go.Scatter(x=week_of, y=y7, mode='lines', name='7 weeks old'))
fig10.add_trace(go.Scatter(x=week_of, y=y8, mode='lines', name='8 weeks old'))
fig10.add_trace(go.Scatter(x=week_of, y=y9, mode='lines', name='9 weeks old'))
fig10.add_trace(go.Scatter(x=week_of, y=y10, mode='lines', name='10+ weeks old'))
fig10.update_layout(title='Engagement by User Age Cohort', xaxis_title='Week of', yaxis_title='Number of Users')
pio.write_html(fig10, file='figures/fig10.html', auto_open=True)

q = """
SELECT DISTINCT action
FROM yammer_emails
"""

emails = pandasql.sqldf(q, globals())

emails.head()

q = """
SELECT 
  strftime('%Y-%m-%d', e.occurred_at, 'weekday 0', '-6 days') AS week_of,
  COUNT(CASE WHEN e.action = 'sent_weekly_digest' THEN e.user_id ELSE NULL END) AS emails_sent,
  COUNT(CASE WHEN e.action = 'sent_weekly_digest' THEN e1.user_id ELSE NULL END) AS emails_opened,
  COUNT(CASE WHEN e.action = 'sent_weekly_digest' THEN e2.user_id ELSE NULL END) AS emails_ct,
  COUNT(CASE WHEN e.action = 'sent_reengagement_email' THEN e.user_id ELSE NULL END) AS reengagements_sent,
  COUNT(CASE WHEN e.action = 'sent_reengagement_email' THEN e1.user_id ELSE NULL END) AS reengagements_opened,
  COUNT(CASE WHEN e.action = 'sent_reengagement_email' THEN e2.user_id ELSE NULL END) AS reengagements_ct
FROM yammer_emails e
LEFT JOIN yammer_emails e1
  ON e.user_id = e1.user_id
    AND e1.action = 'email_open'
    AND e1.occurred_at >= e.occurred_at
    AND e1.occurred_at < datetime(e.occurred_at, '+24 hours')
LEFT JOIN yammer_emails e2
  ON e.user_id = e2.user_id
    AND e2.action = 'email_clickthrough'
    AND e2.occurred_at >= e.occurred_at
    AND e2.occurred_at < datetime(e.occurred_at, '+24 hours')
WHERE e.occurred_at >= '2014-05-01 00:00:00'
AND e.occurred_at < '2014-09-01 00:00:00'
GROUP BY 1
ORDER BY 1
"""

email_counts = pandasql.sqldf(q, globals())

email_counts.head()

week_of = email_counts['week_of']
emails_sent = email_counts['emails_sent']
emails_opened = email_counts['emails_opened']
emails_ct = email_counts['emails_ct']
reengagements_sent = email_counts['reengagements_sent']
reengagements_opened = email_counts['reengagements_opened']
reengagements_ct = email_counts['reengagements_ct']

fig11 = go.Figure()
fig11.add_trace(go.Scatter(x=week_of, y=emails_sent, mode='lines', name='emails sent'))
fig11.add_trace(go.Scatter(x=week_of, y=emails_opened, mode='lines', name='emails opened'))
fig11.add_trace(go.Scatter(x=week_of, y=emails_ct, mode='lines', name='emails clicked-through'))
fig11.add_trace(go.Scatter(x=week_of, y=reengagements_sent, mode='lines', name='reengagements sent'))
fig11.add_trace(go.Scatter(x=week_of, y=reengagements_opened, mode='lines', name='reengagements opened'))
fig11.add_trace(go.Scatter(x=week_of, y=reengagements_ct, mode='lines', name='reengagements clicked-through'))
fig11.update_layout(title='Email Open and Click-Through Rates', xaxis_title='Week of', yaxis_title='Number of emails')
pio.write_html(fig11, file='figures/fig11.html', auto_open=True)