<jupyter_start><jupyter_text>Datachallenge 2 Investigating a Drop in User Engagement We have seen a recent drop in user engagement on the Yammer platform, shown in the graph [below](Data-visualization), which shows weekly unique logins. We want to investigate what happened, using the historical data about user engagement with the platform. Some first theories as to what could have happened.1. The drop starts in August, which is a big vacation month in parts of the world (in the Mediterranean for example, entire companies may be closed for a couple weeks or the entire month), so maybe vacation season causes the observed drop?    - This could be examined by looking into any geographic patterns in user engagement.2. The drop could be caused by one (or few) big clients dropping out. Looking into engagement evolution for different companies could shed a light on that.3. Could there be technical issues on the side of Yammer that make it hard for users to connect? This won't be clear from the data we currently have (and should be considered unlikely, since in the course of a month we should expect feedback from users alerting us to such a widespread issue), but would be worth a quick check.<jupyter_code>import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns

%matplotlib inline<jupyter_output><empty_output><jupyter_text>We read in the three data files, and take a first look at the data.<jupyter_code>users = pd.read_csv('yammer_users.csv', low_memory=False)
events = pd.read_csv('yammer_events.csv', low_memory=False)
emails = pd.read_csv('yammer_emails.csv', low_memory=False)
users.head(2)
events.head(2)
emails.head(2)<jupyter_output><empty_output><jupyter_text>Let's look into active vs inactive users.<jupyter_code>users[users.state=='pending'].info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 9685 entries, 1 to 19065
Data columns (total 6 columns):
user_id         9685 non-null float64
created_at      9685 non-null object
company_id      9685 non-null float64
language        9685 non-null object
activated_at    0 non-null object
state           9685 non-null object
dtypes: float64(2), object(4)
memory usage: 529.6+ KB<jupyter_text>Roughly half of the users are 'pending', meaning they haven't been activated yet. Short analysis in SQL confirms that pending users have no engagement (other than actions during sign-up process; event table has pending users only when event_type='signup_flow', and email table has no pending users). I'm assuming the active users are then who we need to focus on for our analysis. In order to create plots, we merge tables and create datetime columns.<jupyter_code>events_users = events.merge(users,how='left',on='user_id')
events_users.loc[:,'occurred_at'] = pd.to_datetime(events_users.occurred_at)
events_users.loc[:,'created_at'] = pd.to_datetime(events_users.created_at)
events_users.loc[:,'activated_at'] = pd.to_datetime(events_users.activated_at)
events_users.head()
events_users.set_index?<jupyter_output><empty_output><jupyter_text>The below table will have the occurred_at column as its index, this makes some plotting operations and labeling easier.<jupyter_code>ev_us_dt = events_users.set_index('occurred_at')
ev_us_dt.head()<jupyter_output><empty_output><jupyter_text>Data visualizationFor a start, this is the plot we are starting out from in the description: We filter by user engagement, resample weekly and plot the unique counts of users. In addition to users, we also plot unique company counts, and we find the same general trend.<jupyter_code>_= ev_us_dt[(ev_us_dt.event_type=='engagement')].loc[:,['user_id','company_id']].resample('W').nunique().plot()<jupyter_output><empty_output><jupyter_text>Play around with this<jupyter_code>ev_us_dt[(ev_us_dt.event_type=='engagement')].loc[:,['user_id','company_id']].resample('W').nunique().head()
ev_us_dt[ev_us_dt.event_type=='engagement'].head()
foo = ev_us_dt[(ev_us_dt.event_type=='engagement')].loc[:,['user_id','company_id']]
foo.head()
foo2 = foo.resample('W')
foo.head()
foo2.nunique()<jupyter_output><empty_output><jupyter_text>To see whether there is some regional trend (that might support our vacation hypothesis), we plot login numbers for some countries with highest user numbers, grouped by continent to simplify interpretation.<jupyter_code>americas = ['Canada', 'Mexico', 'Brazil']#'United States', 
europe = ['Germany', 'France', 'United Kingdom', 'Italy']
asiaoceania = ['Japan','India','China','Russia','Australia']
# this from https://matplotlib.org/gallery/text_labels_and_annotations/date.html
# to format axis labels
import matplotlib.dates as mdates 

months = mdates.MonthLocator()  # every month
monthsFmt = mdates.DateFormatter('%m-%Y')

fig,ax1 = plt.subplots(1,1)

for ctry in americas+['United States']:    
    plotseries = ev_us_dt[(ev_us_dt.event_name=='login') & (ev_us_dt.location==ctry)].user_id.resample('W').nunique()
    _ = ax1.plot(plotseries, label=ctry)
ax1.legend()
ax1.xaxis.set_major_locator(months)
ax1.xaxis.set_major_formatter(monthsFmt)<jupyter_output><empty_output><jupyter_text>The trend is clearly visible for users in the United States. The US user numbers flatten out everything else however, so we'll remove the US from this plot to get a better idea of the other countries.<jupyter_code>fig,[ax1,ax2,ax3] = plt.subplots(1,3,figsize=(20,8))

for ctry in americas:
    plotseries = ev_us_dt[(ev_us_dt.event_name=='login') & (ev_us_dt.location==ctry)].user_id.resample('W').nunique()
    _ = ax1.plot(plotseries, label=ctry)
ax1.legend()
ax1.xaxis.set_major_locator(months)
ax1.xaxis.set_major_formatter(monthsFmt) 

for ctry in europe:
    plotseries = ev_us_dt[(ev_us_dt.event_name=='login') & (ev_us_dt.location==ctry)].user_id.resample('W').nunique()
    _ = ax2.plot(plotseries, label=ctry)
ax2.legend()
ax2.xaxis.set_major_locator(months)
ax2.xaxis.set_major_formatter(monthsFmt)

for ctry in asiaoceania:
    plotseries = ev_us_dt[(ev_us_dt.event_name=='login') & (ev_us_dt.location==ctry)].user_id.resample('W').nunique()
    _ = ax3.plot(plotseries, label=ctry)
ax3.legend()
ax3.xaxis.set_major_locator(months)
_ = ax3.xaxis.set_major_formatter(monthsFmt)<jupyter_output><empty_output><jupyter_text>We find that across continents, some downward trend in engagement can be observed in August. The countries with seemingly the least effect are Mexico and Russia (both show a sharp tip in early August but then engagement appears to rembound in the weeks after). The overall user numbers for these countries are low however, so this may not be a significant observation.The overall trend is however an indicator that we are not dealing with a local phenomenon (like vacation season in some part of the world, or one or few individual companies quitting the service). Below, we look at different types of user engagement (not just login), to see how they are related (we expect to  see a similar trend in other categories, since login is the first step toward other actions).<jupyter_code>event_dummies = pd.get_dummies(ev_us_dt[(ev_us_dt.state=='active') & (ev_us_dt.event_type=='engagement')].event_name)
event_dummies = event_dummies.resample('W').sum()
todrop = [col for col in event_dummies.columns if 'search_click_result' in col]
event_dummies = event_dummies.drop(todrop,axis=1)

event_dummies.plot()
_ = plt.legend(bbox_to_anchor=(1,1))<jupyter_output><empty_output><jupyter_text>As expected, the trend persists across these categories.  Engagement with emailsLet's now look at how user engagement with emails is evolving over time.We first merge the users and emails dataframes, and make some plots. We also introduce a column 'occurred_after', counting the days since we started collecting data that a certain action is taken.<jupyter_code>emails_users = emails.merge(users,how='left',on='user_id')
emails_users.loc[:,'occurred_after'] = pd.to_datetime(emails_users.occurred_at) - pd.to_datetime(emails_users.occurred_at.min())
emails_users.at[:,'occurred_after'] = emails_users.occurred_after.dt.days
emails_users.loc[:,'occurred_at']= pd.to_datetime(emails_users.occurred_at)
emailus_dt = emails_users.set_index('occurred_at')<jupyter_output><empty_output><jupyter_text>We plot different types of actions related to emails over time.<jupyter_code>toplot = pd.get_dummies(emailus_dt[emailus_dt.state=='active'].action)
fig,ax = plt.subplots(1,1)
for val in toplot.columns[:-1]:
    toplot.loc[:,val].resample('W').sum().plot(kind='line')
_ = ax.legend(toplot.columns.values)<jupyter_output><empty_output><jupyter_text>We observe the dip in clickthrough, a little less in opening emails. If we plot counts of actions regarding email, we observe clear weekly seasonality, with a slight downward trend in clickthroughs:<jupyter_code>fig, ax = plt.subplots(1,1,figsize=(12,8))
ax =emails_users.action.groupby(emails_users.occurred_after).value_counts().unstack('action').plot(kind='area',stacked=True, ax=ax)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10340226817878585, 'fraction_numerical': 0.010340226817878585, 'mean_word_length': 6.464730290456432, 'pattern_counts': {'":': 0, '<': 53, '<?xml version=': 0, '>': 53, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '13', 'dir': 'jupyter-structured-clean-dedup', 'id': '304023', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 2935, 'n_tokens_neox': 2805, 'n_words': 861, 'path': 'others/Data_Challenge2/Monika-Pichler-Yammer.ipynb', 'repo_name': 'davestanley-cogrhythms/insight-datachallenge1', 'size': '8994'}