<jupyter_start><jupyter_text>Data challenge - Yammer**Background:**- Yammer is a social network for communicating with coworkers- Individuals share documents, updates, and ideas by posting them in groups- Companies must pay license fees if they want access to administrative controls, including integration with user management systems like ActiveDirectory.**Problem:**Drop (about 15%) in weekly engagement between beginning August and September. Figure out why.**Hypotheses**2 possibilities:A) Internal reasons - something that can be fixed- diagnose that drop in engagment comes from existing users or decrease in signups- there was an  overall outage in our platform = "catastrophic failures"- only a specfic part is affected: specific device, specific part of platform, location for both try to narrow down to device or company see what distinguishes usage that does go down from one that does not go down.B) External reasons - things that cannot be fixed/need to be investigated further- check per company usage pattern- people go on Vacation in August - compare southern and northern hemisphere usage patterns<jupyter_code>### Load in some useful packages

%matplotlib inline
import matplotlib.pyplot as plt
import numpy as np
import pandas as pd
from scipy import stats
import qgrid
import seaborn as sns; sns.set() # this is another plotting program

pd.show_versions()<jupyter_output>INSTALLED VERSIONS
------------------
commit: None
python: 3.7.1.final.0
python-bits: 64
OS: Darwin
OS-release: 18.2.0
machine: x86_64
processor: i386
byteorder: little
LC_ALL: None
LANG: en_US.UTF-8
LOCALE: en_US.UTF-8

pandas: 0.23.4
pytest: None
pip: 18.1
setuptools: 40.6.3
Cython: None
numpy: 1.15.4
scipy: 1.1.0
pyarrow: None
xarray: None
IPython: 7.2.0
sphinx: None
patsy: 0.5.1
dateutil: 2.7.5
pytz: 2018.9
blosc: None
bottleneck: None
tables: None
numexpr: None
feather: None
matplotlib: 3.0.2
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml: None
bs4: None
html5lib: None
sqlalchemy: None
pymysql: None
psycopg2: None
jinja2: 2.10
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None<jupyter_text>Part 1: Data reading in and preprocessing<jupyter_code>## read in the data + get an idea how many samples I have

users = pd.read_csv('yammer_users.csv')
events = pd.read_csv('yammer_events.csv')
emails = pd.read_csv('yammer_emails.csv')
rollup_periods = pd.read_csv('dimension_rollup_periods.csv')

print(users.shape)
print(events.shape)
print(emails.shape)
print(rollup_periods.shape)
# analyze the Events table for usage by country
# first look at the table and if datatypes were read in correctly

print(events.dtypes)
print(emails.dtypes)
print(rollup_periods.dtypes)
print(users.dtypes)
# need to convert the occured_at column into a datatime
from datetime import datetime # this calls the datetime package

events['occurred_at'] = pd.to_datetime(events['occurred_at'])

rollup_periods['time_id'] = pd.to_datetime(rollup_periods['time_id'])
rollup_periods['pst_start'] = pd.to_datetime(rollup_periods['pst_start'])
rollup_periods['pst_end'] = pd.to_datetime(rollup_periods['pst_end'])
rollup_periods['utc_start'] = pd.to_datetime(rollup_periods['utc_start'])
rollup_periods['utc_end'] = pd.to_datetime(rollup_periods['utc_end'])


print(events.dtypes)
print(rollup_periods.dtypes)
users.head()
users['created_at'] = pd.to_datetime(users['created_at'])
users['activated_at'] = pd.to_datetime(users['activated_at'])
users.dtypes
emails['occurred_at'] = pd.to_datetime(emails['occurred_at'])
emails.dtypes
events.location.unique()
events.head()
# count total events/country

events['location'].value_counts()
rollup_periods.head()
# don't really know in which unit the time in events is  (utc vs pst) assumed utc since
# it's more standardized and the world doesn't care that much about pst and it's about 
# time that Americans switched to the metric system


events['time_id'] = events['occurred_at'].apply(lambda time: rollup_periods['time_id'][(rollup_periods['utc_start'] <= time) & (rollup_periods['utc_end'] > time)].values[0])
events.head()

# the process before took a long time so better save the output as a .csv
events.to_csv('backup_events.csv')
events.dtypes
### curious what's in each column

print('event_type')
print(events['event_type'].value_counts())
print('event_name')
print(events['event_name'].value_counts())
print('device')
print(events['device'].value_counts())

#in which range do we have data
print(events['time_id'].min())
print(events['time_id'].max())
# sort the events dataframe, so that it's easier to plot

events = events.sort_values(by='time_id', ascending=True)
events.head()
events_over_time = events[events.event_type == 'engagement']
events_over_time = events_over_time['time_id'].value_counts()
events_over_time.head()<jupyter_output><empty_output><jupyter_text>Part 2 - checking of hypotheses  A) Internal reasons - something that can be fixed - diagnose that drop in engagment comes from existing users or decrease in signups<jupyter_code>pd.pivot_table(events,index=["time_id"], values = 'device', columns=["event_type"],aggfunc=[len]).plot(legend=True)<jupyter_output><empty_output><jupyter_text>- there was an overall outage in our platform = "catastrophic failures"<jupyter_code># this is the total number of engagments per time period - dip makes sense
# this is probably because of weekends -
events_over_time.plot()
users_over_time = events[events.event_type == 'engagement']
users_over_time = users_over_time.groupby('time_id')['user_id'].nunique()
users_over_time
users_over_time.plot()
# this is unique users over time

# same dipping trend - so the fact that the pattern still seems normal but reduced doesn't 
# that there is general problems with the platform<jupyter_output><empty_output><jupyter_text>Answer- daily usagepattern still looks the same as in August - no catastrophic failurespecifically there is a spike during the week and a dip on the weekend.  - only a specfic part is affected: specific device, specific part of platform, location the following tables always plot a specific feature per montly usage. I compared July to August to identify if the dip happens in a specific case or is a general feature.  Event type of engagement<jupyter_code>pd.pivot_table(events,index=["event_name"], values = 'device', columns=[pd.Grouper(key='time_id', freq='M')],aggfunc=[len])<jupyter_output><empty_output><jupyter_text>Emails this is just looking at events within emails<jupyter_code>pd.pivot_table(emails, index=["action"], values='user_id', columns=[pd.Grouper(key='occurred_at', freq='M')],aggfunc=[len])<jupyter_output><empty_output><jupyter_text>this is just looking at events within emails after joining to the engagements from the table<jupyter_code>pd.pivot_table(pd.merge(emails, events, on='user_id', how = 'left', sort=False), values= 'device', index=["action"],columns=[pd.Grouper(key='time_id', freq='M')],aggfunc=[len])<jupyter_output><empty_output><jupyter_text>Device used<jupyter_code>pd.pivot_table(events,index=["device"], values= 'event_name',columns=[pd.Grouper(key='time_id', freq='M')],aggfunc=[len])<jupyter_output><empty_output><jupyter_text>User type<jupyter_code>pd.pivot_table(events,index=["user_type"], values='device', columns=[pd.Grouper(key='time_id', freq='M')],aggfunc=[len])<jupyter_output><empty_output><jupyter_text>Location where engagment was recorded<jupyter_code>pd.pivot_table(events,index=["location"], values= 'device', columns=[pd.Grouper(key='time_id', freq='M')],aggfunc=[len])<jupyter_output><empty_output><jupyter_text>Language<jupyter_code>pd.pivot_table(pd.merge(users, events, on='user_id', how = 'left', sort=False),index=["language"],values='activated_at',columns=[pd.Grouper(key='time_id', freq='M')],aggfunc=[len])<jupyter_output><empty_output><jupyter_text>Conclusion:   - there is no specific platform that is mostly affected (both Mac/Windows Mobile and Desktop are affected)   - no specific pattern by language or location   - there is no specic pattern by user-type (whatever that means)   - the only thing that stood out that email sent and received was unchanded but fewer people clicked links in the emails - also something is going on in the engagement table when linkin that to the emails - would need some more time to figure this out...  B) External reasons - things that cannot be fixed/need to be investigated further - check per company usage pattern - if the pattern is specific for companies maybe they are changing their business practices which is decreasing our user engagement<jupyter_code>pd.pivot_table(pd.merge(users, events, on='user_id', how = 'left', sort=False), values = 'activated_at', index=["company_id"],columns=[pd.Grouper(key='time_id', freq='M')],aggfunc=[len])

## to get an idea how many users are there by company
users.groupby(['company_id']).count()<jupyter_output><empty_output><jupyter_text>Interesting pattern: there are definitly companies that more of a dip in engagment than otherscompare company 3 to company 2 (about simliar size), also look at company 1 since it is the biggest onecompany 2 = similar size but has dip in engagementcompany 3 = has no dip in engagment<jupyter_code># make new df with 2 companies

company1 = users.loc[users['company_id'] == 1.0]
company2 = users.loc[users['company_id'] == 2.0]
company3 = users.loc[users['company_id'] == 3.0]

company1.size<jupyter_output><empty_output><jupyter_text>Plotting activated users over time for company 1, 2 and 3<jupyter_code>company1.set_index('activated_at').resample('M')["state"].count().plot()
company2.set_index('activated_at').resample('M')["state"].count().plot()
company3.set_index('activated_at').resample('M')["state"].count().plot()<jupyter_output><empty_output><jupyter_text>There seems to be a dip in activation also in August of 2013 for company 1, so maybe the dip in engagment is specific for some companies. Company2 and 3 had not that many users.  Looked also at daily use engagment patterns for company 1, 2 and 3 to see if there is a different pattern in cycling<jupyter_code>pd.pivot_table(pd.merge(company1, events, on='user_id', how = 'left', sort=False),values='activated_at', index=[pd.Grouper(key='time_id', freq='D')], columns=["event_type"],aggfunc=[len]).plot()

pd.pivot_table(pd.merge(company2, events, on='user_id', how = 'left', sort=False),values='activated_at', index=[pd.Grouper(key='time_id', freq='D')], columns=["event_type"],aggfunc=[len]).plot()
pd.pivot_table(pd.merge(company3, events, on='user_id', how = 'left', sort=False),values='activated_at', index=[pd.Grouper(key='time_id', freq='D')], columns=["event_type"],aggfunc=[len]).plot()<jupyter_output><empty_output><jupyter_text>cycling patterns for the companies didn't appear that much different. there might be some trend that company 1 and 2 might have been working up to some deadline in July (weekend usages increased) so maybe they are taking a break in August?<jupyter_code>company1.head()<jupyter_output><empty_output><jupyter_text>- people go on Vacation in August - compare southern and northern hemisphere usage patterns -looked at the countries with a decent user base and then aggregated countries that don't have holidays in August. https://en.wikipedia.org/wiki/Summer_vacation<jupyter_code>events['location'].value_counts()
users_northern_HM = events[(events.location != 'India')|(events.location != 'Australia') | (events.location != 'Brazil') | (events.location != 'South Africa') | (events.location != 'Chile' )]
users_northern_HM.groupby('time_id')['user_id'].nunique().plot()
users_southern_HM = events[(events.location == 'India')|(events.location == 'Australia') | (events.location == 'Brazil') | (events.location == 'South Africa') | (events.location == 'Chile' )]
users_southern_HM.groupby('time_id')['user_id'].nunique().plot()

### look at engagement per month for southern hemisphere

users_southern_HM[users_southern_HM.event_type == 'engagement'].resample('M').count()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12134100575431574, 'fraction_numerical': 0.008839963305812692, 'mean_word_length': 6.936465916611516, 'pattern_counts': {'":': 0, '<': 69, '<?xml version=': 0, '>': 69, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 1}, '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': '17', 'dir': 'jupyter-structured-clean-dedup', 'id': '525052', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 3901, 'n_tokens_neox': 3752, 'n_words': 1123, 'path': 'others/Data_Challenge2/Eva_Fast_Datachallenge2.ipynb', 'repo_name': 'davestanley-cogrhythms/insight-datachallenge1', 'size': '11991'}