Investigating a Drop in User Engagement

In this study, we will be using a few common technologies:

• SQL (SQLite) for data manipulation/exploration
• Python (pandas) for data frames
• Plotly/Jupyter notebook for interactive charts/data visualization

Programming

# 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

Task overview

This notebook features the first case study in a series of three that dives into the Yammer dataset. For our analysis we will be considering Yammer’s core metrics (user engagement, retention and growth) and analyzing product-specific usage metrics (e.g. number of times someone views another user’s profile). For clarification, Yammer is a social network for the workplace whose primary goal is to drive better product and business decisions using data. While this specific dataset is fabricated due to privacy and security reasons, it is similar in structure to Yammer’s actual data. With all that said–let’s begin our study…

Collecting our dataset

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

The problem

It’s Tuesday, September 2, 2014. You’ve just been informed by the head of the Yammer Product team that user engagement is down. Here’s the chart of weekly active users that you’ve been provided:

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)

The above chart shows the number of engaged users each week. Yammer defines engagement as having made some type of server call by interacting with the product (showed in the data as events of type engagement). Any point in this chart can be interpreted as “the number of users who logged at least one engagement event during the week starting on that date”.

Analysis

We are responsible for determining what caused the dip at the end of the chart shown above, and, if appropriate, recommending solutions for the problem.

In this study, we will be considering the following metrics:

• Engagement by Factors of Time
• New Users (Activation/Growth Rate)
• Engagement by Event Type
• Engagement by Region
• Engagement by Product/Device Type
• Email Open and Click-Through Rate

1. Engagement by Factors of Time

“Do we see the engagement levels gradually decreasing over time or only a one time sudden decrease? Is the decrease specific to certain days of the week or certain times of the day?”

Daily Engagement Rate

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