<jupyter_start><jupyter_text>First, I want to take a look at our data in general and get a sense of what I'm working with.I'll start with the emails table. What's in there, and what kind of actions are taken by email?<jupyter_code>emails.head()
print(f'Email actions were: {emails.action.unique()}')
print(f'user types are: {emails.user_type.unique()}')<jupyter_output>Email actions were: ['sent_weekly_digest' 'email_open' 'email_clickthrough'
 'sent_reengagement_email']
user types are: [1. 3. 2.]<jupyter_text>Now, let's look at what's in events<jupyter_code>events.head()<jupyter_output><empty_output><jupyter_text>Cool, there are a lot of different things that we can stratify on to see where the dip is coming from and if it's universal. Odds are that there's something in particular that's causing the decrease in engagement, and not an overall trend for everyone. But we'll have to test that hypothesis.<jupyter_code>print(f'Event types are:{events.event_type.unique()}')
print(f'Event names are:{events.event_name.unique()}')
print(f'Locations are:{events.location.unique()}')<jupyter_output>Event types are:['engagement' 'signup_flow']
Event names are:['login' 'home_page' 'like_message' 'view_inbox' 'search_run'
 'send_message' 'search_autocomplete' 'search_click_result_10'
 'create_user' 'enter_email' 'enter_info' 'complete_signup'
 'search_click_result_7' 'search_click_result_8' 'search_click_result_1'
 'search_click_result_3' 'search_click_result_2' 'search_click_result_5'
 'search_click_result_6' 'search_click_result_9' 'search_click_result_4']
Locations are:['Japan' 'Netherlands' 'Austria' 'Finland' 'United Kingdom' 'India'
 'United States' 'France' 'Iran' 'Germany' 'Australia' 'Brazil' 'Thailand'
 'Russia' 'Taiwan' 'Canada' 'Spain' 'Israel' 'Colombia' 'Iraq' 'Indonesia'
 'Greece' 'Norway' 'United Arab Emirates' 'Korea' 'Venezuela' 'Belgium'
 'Saudi Arabia' 'Poland' 'Sweden' 'Denmark' 'Mexico' 'Italy' 'Egypt'
 'Nigeria' 'Pakistan' 'Portugal' 'Singapore' 'South Africa' 'Hong Kong'
 'Switzerland' 'Turkey' 'Chile' 'Ireland' 'Argentina' 'Malaysia'
 'Philippines']<jupyter_text>Cool, so we can see that we have two types of events, and we're dealing with a decrease in engagement type events.Finally, let's get a look at what's in the users table before we get merging or further explore things.<jupyter_code>users.head()<jupyter_output><empty_output><jupyter_text>This one looks pretty straightforward- userID, what language they speak, what company they are with, and when they activated (finished registering and started using Yammer?)<jupyter_code>rollup.tail()<jupyter_output><empty_output><jupyter_text>Ok, so now I've taken a look at what all we've got. I also went onto the Mode Analytics website where this chart was generated and pulled the SQL query so that I could know just what I'm looking at here.```SELECT DATE_TRUNC('week', e.occurred_at),       COUNT(DISTINCT e.user_id) AS weekly_active_users  FROM tutorial.yammer_events e WHERE e.event_type = 'engagement'   AND e.event_name = 'login' GROUP BY 1 ORDER BY 1 ```So, I'm no SQL expert, but this looks like it's not engagement, but just 'logins'. If it's just logins, we've got a different problem that we want to investigate. But I'm going to assume I don't know anything about SQL and that the underlying assumptions of the challege are correct- IE this is a drop in **engagement** and not just logins. The data goes into the future, so we will use the 'occurred at' values to select only those that are in our time of interest<jupyter_code>events.dtypes<jupyter_output><empty_output><jupyter_text>Because the 'occurred_at' columns are objects, we won't be able to properly select them. Thus, we will change them into pandas datetime objects.<jupyter_code>events.occurred_at = pd.to_datetime(events.occurred_at)
emails.occurred_at = pd.to_datetime(events.occurred_at)
events_current = events[events['occurred_at']<pd.to_datetime('2014-9-2')]
emails_current = emails[emails['occurred_at']<pd.to_datetime('2014-9-2')]
# test = pd.merge(events_current, users, how='left', on='user_id')<jupyter_output><empty_output><jupyter_text>What could be happening?!? There are two likely culprits in this kind of case: First, something broke. If it's not that, then we might have a more complicated problem. It's also possible that this is just normal. Let's investigate the possibilities in a little more detail.1. It's a seasonal variation and is totally normal2. The website broke3. The internet somewhere broke4. We quit sending emails5. We sent some offensive email6. Nothing is wrong. It's literally just random noise  Hypthesis 1 - It's nothing but a seasonal variation<jupyter_code>## Hypothesis 1 - It's seasonal variation and nothing is wrong- we could look at the data from the
## previous year and see if a similar peak and dip occurred.
events.head()<jupyter_output><empty_output><jupyter_text>Well, unfotunately we don't have the data from last year to tell us if it's just a seasonal variation. We could ask someone else at the company, but let's assume that the boss was here last year and judging by his strong response to the graph, this isn't actually normal. Next hypothesis.  Hypothesis 2: The website was down and people couldn't log in. This one is a little hairy- we have the original data that says it's engagement, so if that's true then we want to know about logins specifically because those could be driving other engagement factors. A broken login means no one can engage in any other way. So we will test to see if log-ins preceded the drop in total engagement.<jupyter_code>## The website broke. This means we would have seen a lot less log-ins than usual
logins = events_current[events_current['event_name']=='login']; logins.head()
by_loc = logins.groupby(by='location')
test = logins.groupby(by='occurred_at').location.value_counts()
ts = pd.Series(range(len(logins)), index=logins.occurred_at, )
# In [9]: idx = pd.date_range('2018-01-01', periods=5, freq='H')
# logins.occurred_at.resample('7D')
new = logins.set_index('occurred_at')

resamp = new.resample('W').nunique()
resamp = new.resample('W')['event_name'].value_counts()
# plt.plot(x=resamp.index[0], y=resamp)
resamp.plot()<jupyter_output><empty_output><jupyter_text>The above graph shows the number of logins, a specific type of engagement, by week. It looks pretty similar, but let's take a look at the numbers<jupyter_code>resamp<jupyter_output><empty_output><jupyter_text>OK so this is kind of weird. We see the drop in engagement from the 27th to the 3rd, mostly. Then we see a decrease in logins the following week.So it looks like it's very possible that something broke, people couldn't use the product, and then the didn't log in the next week because they thought it would still be down.We can see that it wasn't a break in the ability to log in, which means that it was probably something with the functionality of the site.<jupyter_code># new['counts'] = logins.event_name.value_counts()
# let's look for each different type of event and see if any of them dropped to zero.
ev_tests = [events.event_name.unique()]
z=0
for name in ev_tests[0]:    
    stats = events_current[events_current['event_name']==name]
    new = stats.set_index('occurred_at')
    resamp = new.resample('W')['event_name'].value_counts()
    resamp.plot()
#I didn't put a key on here because this is a quick and dirty look, and then I dive into the numbers later
# This cell shows what categories are displayed on the above chart in the various colored lines.
print (ev_tests[0])<jupyter_output>['login' 'home_page' 'like_message' 'view_inbox' 'search_run'
 'send_message' 'search_autocomplete' 'search_click_result_10'
 'create_user' 'enter_email' 'enter_info' 'complete_signup'
 'search_click_result_7' 'search_click_result_8' 'search_click_result_1'
 'search_click_result_3' 'search_click_result_2' 'search_click_result_5'
 'search_click_result_6' 'search_click_result_9' 'search_click_result_4']<jupyter_text>According to the above graph, it looks like this drop in engagement wasn't caused by some certain part of the website breaking. We've looked into each separate engagement metric and none of them went to zero or gave any indication that they aren't working. We can look at the underlying numbers below, but they aren't promising.<jupyter_code>ev_tests = [events.event_name.unique()]
z=0
for name in ev_tests[0]:    
    stats = events_current[events_current['event_name']==name]
    new = stats.set_index('occurred_at')
    resamp = new.resample('W')['event_name'].value_counts()
    print(resamp)<jupyter_output>occurred_at  event_name
2014-05-04   login          967
2014-05-11   login         1999
2014-05-18   login         2021
2014-05-25   login         2088
2014-06-01   login         1995
2014-06-08   login         2173
2014-06-15   login         2184
2014-06-22   login         2264
2014-06-29   login         2266
2014-07-06   login         2249
2014-07-13   login         2409
2014-07-20   login         2491
2014-07-27   login         2446
2014-08-03   login         2568
2014-08-10   login         2252
2014-08-17   login         2109
2014-08-24   login         2072
2014-08-31   login         2057
Name: event_name, dtype: int64
occurred_at  event_name
2014-05-04   home_page     2546
2014-05-11   home_page     5113
2014-05-18   home_page     5032
2014-05-25   home_page     5217
2014-06-01   home_page     4978
2014-06-08   home_page     5469
2014-06-15   home_page     5371
2014-06-22   home_page     5585
2014-06-29   home_page     5548
2014-07-06   home_page     5503
2014-07-13   home_page   [...]<jupyter_text>Hypothesis 3: The internet broke somewhere<jupyter_code># events_current.set_index('occurred_at', inplace=True)
locs=events_current.groupby(by='location')
resamp = locs.resample('W')#['location'].value_counts()
locs.head()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09433005375798763, 'fraction_numerical': 0.04199208844710417, 'mean_word_length': 4.993920972644377, 'pattern_counts': {'":': 0, '<': 51, '<?xml version=': 0, '>': 49, 'https://': 0, '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': '51822', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 3426, 'n_tokens_neox': 3082, 'n_words': 1104, 'path': 'others/Data_Challenge2/Stephen_Filios_data_challenge_2.ipynb', 'repo_name': 'davestanley-cogrhythms/insight-datachallenge1', 'size': '9859'}