<jupyter_start><jupyter_text>Data Challenge 2: Investigating a Drop in User Engagement  Yammer:Yammer is a social network for communicating with coworkers. Individuals share documents, updates, and ideas by posting them in groups, it’s like Slack. Yammer is free to use indefinitely, but companies must pay license fees if they want access to administrative controls, including integration with user management systems like ActiveDirectory.  ProblemYou show up to work Tuesday morning, September 2, 2014. The head of the Product team walks over to your desk and asks you what you think about the latest activity on the user engagement dashboards (yes this really happens). You fire them up, and something immediately jumps out:  Interactive Engagement Dashboard:https://modeanalytics.com/modeanalytics/reports/cbb8c291ee96/runs/7925c979521e/viz/cfcdb6b78885The 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 (shown 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.”  Question:The head of product says “Can you look into this and get me a summary by this afternoon?”  As she runs to a meeting. Hints:- Make sure you really understand what the above chart shows, and what it means!- Remember don’t start with the data, start with the hypothesis.  Maybe have an ordered set you can check.- How do you order your hypothesis in descending importance?- Remember, time is critical here, so think about how you’re spending your time! Digging in:For this problem, you have four sources of data. The tables names and column definitions are listed below—click a table name to view information about that table. Note: this data is fake and was generated for the purpose of this case study. It is similar in structure to Yammer’s actual data, but for privacy and security reasons it is not real.  Solution:We start with different hypothesis and delve into some of them.  App is down or some features are brokenOne of the main reasons for reduced engagement is often the fact that the app itself is down or some of its features are either broken or redesigned in a way that is not user-friendly. Sign-Up feature broken?One of the features that if broken can reduce the engagement activity is user sign-up process. To check this we take a look at the time history of creating accounts and activating accounts by the users. Importing the needed libraries and packages:<jupyter_code>import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
%matplotlib inline
import seaborn as sns
import datetime
import matplotlib.dates as mdates
from matplotlib.dates import AutoDateFormatter, AutoDateLocator, WeekdayLocator
from matplotlib.dates import MO, TU, WE, TH, FR, SA, SU<jupyter_output><empty_output><jupyter_text>Reading different csv files/tables:<jupyter_code>users=pd.read_csv("yammer_users.csv")
emails=pd.read_csv("yammer_emails.csv")
events=pd.read_csv("yammer_events.csv")
periods=pd.read_csv("dimension_rollup_periods.csv")<jupyter_output><empty_output><jupyter_text>- We create new columns and convert string date-time entries into timestamps- also every sample (row), based on its timestamp is mapped to a time_id (by the function assign_time_id) which is the last day of the week that the timestamp belongs to.<jupyter_code>users["created_at2"]=users["created_at"].map(lambda x: datetime.datetime.strptime(x, '%Y-%m-%d %H:%M:%S'))
users["created_time_id"]=users["created_at2"].map(lambda x: assign_time_id(x))
users.head()
users["activated"]=users["activated_at"].notnull().astype("int")
users["created_day"]=users["created_at"].map(lambda x: datetime.datetime.strptime(x.split()[0], '%Y-%m-%d'))
users.head()<jupyter_output><empty_output><jupyter_text>To test our hypothesis that sign-up feature is causing the issue, we plot the time history of number of user sign-ups (with and without account activation). We plot it for the last few months as well as the whole time range. The latter shows that in general the number of user sign-ups have been growing indicating that there is no issues with the sign-up feature of the app. To capture and understand the short time-scale decrease-increase pattern of the number of sign-ups, we take a look at a shorter time period (last few months); from this figure it becomes apparent that over the weekend the number of sign-ups decrease which intuitively makes sense.<jupyter_code># plotting the time history of the number of sign-ups in the last few months

sunday = WeekdayLocator(SU)        # major ticks on the Sundays

created_users_list=users.groupby(["created_day"])["activated"].count()
activated_users_list=users.groupby(["created_day"])["activated"].sum()

fig, ax = plt.subplots(figsize=(14,8))
sns.lineplot(x=created_users_list.index[500:608], y=created_users_list.values[500:608], ax=ax, label="signed-up users")
sns.lineplot(x=activated_users_list.index[500:608], y=activated_users_list.values[500:608], ax=ax, label="activated users")

ax.xaxis.set_major_locator(sunday)
plt.gca().xaxis.set_major_formatter(mdates.DateFormatter('%m/%d/%Y'))
plt.gcf().autofmt_xdate()
plt.xlabel("Date")
plt.ylabel("number of users")
ax.legend()


# plotting the time history of the number of sign-ups in the whole time range

fig, ax = plt.subplots(figsize=(14,8))
sns.lineplot(x=created_users_list.index[0:608], y=created_users_list.values[0:608], ax=ax, label="signed-up users")
sns.lineplot(x=activated_users_list.index[0:608], y=activated_users_list.values[0:608], ax=ax, label="activated users")

plt.gca().xaxis.set_major_formatter(mdates.DateFormatter('%m/%d/%Y'))
plt.gca().xaxis.set_major_locator(AutoDateLocator())
plt.gcf().autofmt_xdate()
plt.xlabel("Date")
plt.ylabel("number of users")
ax.legend()<jupyter_output><empty_output><jupyter_text>a device-specific feature/component is broken?Next we check to see if any device-specific feature could be a potential reason. There are 3 different devices types: computers, tablets, and phones. We break down engagement metrics based on device type.  We check which columns have null values. The column of "activated_at" has null values and it corresponds to pending accounts<jupyter_code>users.isnull().any()
emails["action"].unique()
emails["occurred_at2"]=emails["occurred_at"].map(lambda x: datetime.datetime.strptime(x, '%Y-%m-%d %H:%M:%S'))
emails["time_id"]=emails["occurred_at2"].map(lambda x: assign_time_id(x))
emails.head()
events.head()
events["occured_at2"]=events["occurred_at"].map(lambda x: datetime.datetime.strptime(x, '%Y-%m-%d %H:%M:%S'))
events["device"].unique()
phone_list=['iphone 5', 'iphone 4s', 'iphone 5s', 'samsung galaxy s4', 'htc one',
       'nokia lumia 635', 'amazon fire phone', 'samsung galaxy note']
tablet_list=['kindle fire', 'ipad mini', 'nexus 7', 'nexus 5', 'samsumg galaxy tablet', 'ipad air', 'nexus 10']
computer_list=['dell inspiron notebook', 'windows surface', 'macbook air', 'macbook pro', 'lenovo thinkpad',
               'acer aspire notebook', 'asus chromebook', 'acer aspire desktop', 'mac mini',
               'hp pavilion desktop', 'dell inspiron desktop']

len(phone_list)+len(tablet_list)+len(computer_list)==events["device"].nunique()

events["computer"]=events["device"].map(lambda x: 1 if x in computer_list else 0)
events["tablet"]=events["device"].map(lambda x: 1 if x in tablet_list else 0)
events["phone"]=events["device"].map(lambda x: 1 if x in phone_list else 0)
events.head()<jupyter_output><empty_output><jupyter_text>We have two types of engagement and we consider only engagements other than "sign-up" flow.<jupyter_code>events["event_type"].unique()<jupyter_output><empty_output><jupyter_text>Below we show the the number of engagement activity per device type. As seen in the figure there is a significant drop for computers and even more for phones. So there could be a problem in the mobile apps; maybe a broken feature or a recent change in the mobile app user-interface.<jupyter_code>computer_events_list=events[events["event_type"]=='engagement'].groupby(["time_id"])["computer"].sum()
tablet_events_list=events[events["event_type"]=='engagement'].groupby(["time_id"])["tablet"].sum()
phone_events_list=events[events["event_type"]=='engagement'].groupby(["time_id"])["phone"].sum()

N = computer_events_list.shape[0]

fig, ax = plt.subplots(figsize=(14,8))
sns.lineplot(x=np.arange(N), y=computer_events_list.values, ax=ax, label="computer")
sns.lineplot(x=np.arange(N), y=tablet_events_list.values, ax=ax, label="tablet")
sns.lineplot(x=np.arange(N), y=phone_events_list.values, ax=ax, label="phone")

plt.xlabel("Date")
plt.ylabel("number of engagement activity")
plt.xticks(np.arange(N), active_users["time_id"])
plt.xticks(rotation='vertical')
ax.legend()

events["event_name"].unique()<jupyter_output><empty_output><jupyter_text>Issues with re-engagement / weekly emails?Another issue could be that the email contents are such that they do not engage the users. To check this, we look into the email activities. Below we plot the time history of the number of email activities. Based on the figure, the only thing that drops is the email click-throughs; this suggests that there could be a problem with the weekly digest and re-engagement emails.<jupyter_code>emails.head()
emails["action"].unique()
emails_conc=pd.concat([emails, pd.get_dummies(emails["action"])], axis=1) 

email_clickthrough_list = emails_conc.groupby(["time_id"])["email_clickthrough"].sum()
email_open_list = emails_conc.groupby(["time_id"])["email_open"].sum()
sent_reengagement_email_list = emails_conc.groupby(["time_id"])["sent_reengagement_email"].sum()
sent_weekly_digest_list = emails_conc.groupby(["time_id"])["sent_weekly_digest"].sum()

N = email_clickthrough_list.shape[0]

fig, ax = plt.subplots(figsize=(14,8))
sns.lineplot(x=np.arange(N-1), y=email_clickthrough_list.values[0:N-1], ax=ax, label="email clickthrough")
sns.lineplot(x=np.arange(N-1), y=email_open_list.values[0:N-1], ax=ax, label="email open")
sns.lineplot(x=np.arange(N-1), y=sent_reengagement_email_list.values[0:N-1], ax=ax, label="reengagement email")
sns.lineplot(x=np.arange(N-1), y=sent_weekly_digest_list.values[0:N-1], ax=ax, label="weekly digest")


plt.xlabel("Date")
plt.ylabel("number of email activity")
plt.xticks(np.arange(N-1), active_users["time_id"][0:N-1])
plt.xticks(rotation='vertical')
ax.legend()

periods.head()
periods[periods["period_id"]==1007.0]
periods[periods["period_id"]==1007.0].iloc[[0, -1]]

periods7=periods[periods["period_id"]==1007.0].iloc[::7, :]
periods7["time_id2"]=periods7["time_id"].map(lambda x: datetime.datetime.strptime(x, '%Y-%m-%d %H:%M:%S'))
time_id=periods7["time_id2"]
def assign_time_id(timestamp):
    for t in time_id:
        if timestamp<=t:
            return t
        

events["time_id"]=events["occured_at2"].map(lambda x: assign_time_id(x))
# assign_time_id(events["occured_at2"][0])
weekly_active_users=events.groupby(["time_id"])["user_id"].nunique()
type(weekly_active_users)
weekly_active_users
pd.Series.to_frame(weekly_active_users)
# weekly_active_users.reset_index(level=0, inplace=True)
type(weekly_active_users)
active_users=pd.DataFrame({'time_id':weekly_active_users.index, 'n_active_users':weekly_active_users.values})
active_users
plt.figure(figsize=(12,8))

# xtick_locator = AutoDateLocator()
# xtick_formatter = AutoDateFormatter(xtick_locator)

# ax = plt.axes()
# ax.xaxis.set_major_locator(xtick_locator)
# ax.xaxis.set_major_formatter(xtick_formatter)

N = active_users.shape[0]
# x2 = np.arange(N)

# plt.plot(x2, y)

sns.lineplot(x=np.arange(N), y="n_active_users", data=active_users)
plt.xticks(np.arange(N), active_users["time_id"])
plt.xticks(rotation='vertical')
# users_grouped=users.groupby(["created_time_id"])["user_id"].nunique()
# sns.lineplot(x=users_grouped.index, y=users_grouped.values)
# plt.xticks(rotation='vertical')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12269479980063133, 'fraction_numerical': 0.012958963282937365, 'mean_word_length': 6.707426376440461, 'pattern_counts': {'":': 0, '<': 34, '<?xml version=': 0, '>': 33, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '535586', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 3988, 'n_tokens_neox': 3825, 'n_words': 1135, 'path': 'others/Data_Challenge2/Ashkan_HajiHosseinloo_Yammer.ipynb', 'repo_name': 'davestanley-cogrhythms/insight-datachallenge1', 'size': '12038'}