# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

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

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

# ## Part 1: Data reading in and preprocessing

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

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

pd.pivot_table(events,index=["time_id"], values = 'device', columns=["event_type"],aggfunc=[len]).plot(legend=True)

# ### - there was an overall outage in our platform = "catastrophic failures"

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

# ### Answer- daily usagepattern still looks the same as in August - no catastrophic failure
# specifically 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

pd.pivot_table(events,index=["event_name"], values = 'device', columns=[pd.Grouper(key='time_id', freq='M')],aggfunc=[len])

# ### Emails

# this is just looking at events within emails

pd.pivot_table(emails, index=["action"], values='user_id', columns=[pd.Grouper(key='occurred_at', freq='M')],aggfunc=[len])

# this is just looking at events within emails after joining to the engagements from the table

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


# ### Device used

pd.pivot_table(events,index=["device"], values= 'event_name',columns=[pd.Grouper(key='time_id', freq='M')],aggfunc=[len])

# ### User type

pd.pivot_table(events,index=["user_type"], values='device', columns=[pd.Grouper(key='time_id', freq='M')],aggfunc=[len])

# ### Location where engagment was recorded

pd.pivot_table(events,index=["location"], values= 'device', columns=[pd.Grouper(key='time_id', freq='M')],aggfunc=[len])

# ### Language

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

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

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

# Interesting pattern: there are definitly companies that more of a dip in engagment than others
#
# compare company 3 to company 2 (about simliar size), also look at company 1 since it is the biggest one
#
# company 2 = similar size but has dip in engagement
# company 3 = has no dip in engagment

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

# ### Plotting activated users over time for company 1, 2 and 3

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

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

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


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

company1.head()

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

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

# #### there still is a dip in engagment in the "southern hemisphere" countries so elevated vacation in August that doesn't explain decreased engagemnt

# # Overall Conclusion:
#
# - no catastrophic failure of platform
# - no device specific, language specific, country specific outage
# - mostly website affected and not email
# - decrease is company specific - so companies need to be investigated further to see what the reason behind that is

StarcoderdataJupyterScriptsDedupFiltered