<jupyter_start><jupyter_text>Yammer: Investigating a Drop in User Engagement  Question: What is behind the recent drop in weekly user engagement?  Engagement: Making some type of server call by interacting with the product. User Engagement: Measured as the number of users who logged at least one engagement event during the week starting on that date.  Note: Engagement figure is based on login events specifically, though this always precedes any other engagement activities  Hypotheses:- Does it differ by country?    - Time range corresponds to common European holiday<jupyter_code># Import packages
import os
import pandas as pd
from datetime import datetime
import seaborn as sns
# Read in data
os.chdir('/Users/ckm/insight/Data Challenges/Week2')
df_emails = pd.read_csv('yammer_emails.csv')
df_events = pd.read_csv('yammer_events.csv')
df_users = pd.read_csv('yammer_users.csv')
df_rollup = pd.read_csv('dimension_rollup_periods.csv')
# Functions to convert string timestamps to datetime, date, and time formats

def make_datetime(string):
    return datetime.strptime(string, '%Y-%m-%d %H:%M:%S')
def make_date(string):
    return datetime.strptime(string[:10], '%Y-%m-%d')
def make_time(string):
    return datetime.strptime(string[11:], '%H:%M:%S')

df_emails['datetime'] = df_emails['occurred_at'].apply(make_datetime)
df_emails['date'] = df_emails['occurred_at'].apply(make_date)
df_emails['time'] = df_emails['occurred_at'].apply(make_time)

df_users['datetime_c'] = df_users['created_at'].apply(make_datetime)
df_users['date_c'] = df_users['created_at'].apply(make_date)
df_users['time_c'] = df_users['created_at'].apply(make_time)

df_events['datetime'] = df_events['occurred_at'].apply(make_datetime)
df_events['date'] = df_events['occurred_at'].apply(make_date)
df_events['time'] = df_events['occurred_at'].apply(make_time)
# Merge dataframes into one, add some other info.

df_emails = df_emails.rename({'action': 'event_type'}, axis='columns')
df_emails = df_emails.set_index('user_id', drop=False)
df_users = df_users.set_index('user_id', drop=False)
df_events = df_events.set_index('user_id', drop=False)
df_full = pd.concat([df_emails, df_events], sort=False)

def add_comp(row):
    return df_users['company_id'].loc[row]

df_full['company_id'] = df_full['user_id'].apply(add_comp)<jupyter_output><empty_output><jupyter_text>Yammer emails Exploratory Data Analysis  Email clickthroughs are down, though they are still being opened:<jupyter_code>ax = df_emails.hist(column='date', by='event_type', figsize=(20,20), bins=16,
                    range=('2014-05-01', '2014-09-01'), rwidth=0.9,
                    xlabelsize=14, ylabelsize=16)<jupyter_output><empty_output><jupyter_text>Yammer users Exploratory Data Analysis  Number of active users in companies<jupyter_code>ax=df_users[df_users['state']=='active'].hist(column='company_id', by='state',
                                              bins=120, figsize=(20,10), xrot=45,
                                              rwidth=0.9,xlabelsize=24, ylabelsize=24)<jupyter_output><empty_output><jupyter_text>Not many more unique users than companies:<jupyter_code>companies = df_users['company_id'].unique()
print('Unique companies: ' + str(len(companies)))
print('Total users: ' + str(len(df_users)))<jupyter_output>Unique companies: 13198
Total users: 19066<jupyter_text>Signups are actually increasing<jupyter_code>ax=df_events[df_events['event_name']=='complete_signup'].hist(column='date',
                                                              by='event_name',
                                                              rwidth=0.9)<jupyter_output><empty_output><jupyter_text>Decreasing trend consistent across event types:<jupyter_code>ax=df_events[df_events['event_type']=='engagement'].hist(column='date', by='event_name',
                                                         figsize=(20,50), layout=(6,3),
                                                         bins=16, rwidth=0.9)<jupyter_output><empty_output><jupyter_text>Decrease most pronounced in user type 1, though present across all:<jupyter_code>ax=df_events[df_events['event_name']=='login'].hist(column='date', by='user_type',
                                                    figsize=(20,20), bins=16,
                                                    rwidth=0.9)<jupyter_output><empty_output><jupyter_text>Decrease in logins appears more pronounced in many European countries:<jupyter_code>ax=df_events[df_events['event_name']=='login'].hist(column='date', by='location',
                                                        figsize=(30,60), layout=(12,4),
                                                        rwidth=0.9, bins=16)<jupyter_output><empty_output><jupyter_text>Not due to complete failure on a device:<jupyter_code>ax=df_events[df_events['event_name']=='login'].hist(column='date', by='device',
                                                    figsize=(30,40), layout=(7,4),
                                                    rwidth=0.9, bins=16)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12578864353312302, 'fraction_numerical': 0.01971608832807571, 'mean_word_length': 3.566156615661566, 'pattern_counts': {'":': 0, '<': 36, '<?xml version=': 0, '>': 36, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 24, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '24484', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 1608, 'n_tokens_neox': 1525, 'n_words': 299, 'path': 'others/Data_Challenge2/Caitlin_Monaghan_Yammer.ipynb', 'repo_name': 'davestanley-cogrhythms/insight-datachallenge1', 'size': '5072'}