<jupyter_start><jupyter_text>EDA of the Advertising Department + Overview and Summarize+ Data pre-processing+ Analysis the Data Of Advertising Department+ Data Visualization: Bar Plots, Line Plots, Displots+ Calculating the metric CTR+ Search for bugs in analytical blogs+ Conclusion  Overview and Summarize According to the given data, let's imagine that we got a job in the advertising department team as a data analyst. The boss comes to us and says that several incomprehensible stories have happened: the charts have grown up for some reasons.This project mainly focuses on following parts of data analysis and visualization:   1. Analyze jumps in advertisements   2. Calculating the CTR metric   3. Search for analytical bugs  Dataset Columns + ad_id: The id of ad+ event: Shows whether the ad was viewed or clicked+ time: The date given in timestamp format+ date: The date when ad was published+ ad_cost_type: Type of advertising costs (CPM - Cost per mile)+ has_video: Boolean expression whether the ad had a video or not+ client_union_id: id of the author of advertising + campaign_union_id: Which company had an ad + platform: the platform where the advertisement was placed+ ad_cost: advertising costs (For example: CPM - 1000 VIEWS, CPC - 1000 CLICKS)+ target_audience_count: shows which audience the ads were targeted to+ user_id: the user who saw the ad  Research Questions    1. Why there was a jump on fifth of April?   2. Find top 10 CTR ads. Does the average and median CTR of ads differ in our data?   3. Search for analytical bugs. Is this problem observed on all platforms?   4. Are there any differences in the CTR of an ad with a video and without?   5. And what is the 95th percentile CTR for all ads for 2019-04-04?  Data pre-processing<jupyter_code>import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
%matplotlib inline 
ads_data = pd.read_csv("ads_data.csv.zip", compression='zip') # instead of manually unzipping the data, just add compression = 'zip'
ads_data.head()
ads_data.shape # check the size of dataset 
ads_data.dtypes # check the values for the data type
ads_data['full_date'] = pd.to_datetime(ads_data.time, unit='s') # since the time column given in timestamp, we add unit='s'
ads_data['date'] = pd.to_datetime(ads_data.date)    # convert to datetime
ads_data.dtypes<jupyter_output><empty_output><jupyter_text>Analysis the Data Of Advertising Department<jupyter_code>ads_data.groupby('date').agg({'ad_id': 'count'}).plot() # get statistics by days to get a general picture of events

# pandas .plot() is almost the same as sns.lineplot()<jupyter_output><empty_output><jupyter_text>It can be clearly seen, that On the fifth of April there was a significant jump in advertising It would be interesting to figure out why this happened!<jupyter_code># Firstly, just count the number of views and clicks of advertisements by dates

# we used pivot table in order to mate it easier to work with clicks\views in the fute
ads_data.groupby(['date', 'event'], as_index=False)\
    .agg({"ad_id":"count"})\
    .rename(columns={"ad_id":"number_of_events"})\
    .pivot(index='date', columns='event', values='number_of_events')\
    .reset_index()

# Indeed, we really see this boom on the fifth of April
??pd.pivot # in case you forgot how the function works in pandas
ads_data.ad_id.nunique() # getting the number of unique advertisement id<jupyter_output><empty_output><jupyter_text>Why there was a boom on the fifth of April?Well, there are many hypotheses here:    1. It could be some special day, such as Black Friday and all advertisers decided to spin more ads      2. Some other major advertiser came in and started running most of the ads<jupyter_code># since we know that the boom was on the fifth of April, let's get the statistics of each ad_id on this particular day

ads_data[ads_data.date == '2019-04-05']\
    .groupby("ad_id", as_index=False)\
    .agg({"time":"count"})\
    .rename(columns={"time":"numbers"})\
    .sort_values("numbers", ascending=False)\
    .head()
suspicious_id = 112583 #  this id has much more events than all the others
# let's check what does this id represent in general

ads_data.query("ad_id == @suspicious_id")\
    .groupby("date", as_index=False)\
    .agg({"time":"count"})

# we can observe that this ad run only two days
ads_data.query("ad_id == @suspicious_id").head(1) 

# thus, we found out what kind of client pulled the lion's share of all advertisements
# and why there was such a boom on the fifth of april<jupyter_output><empty_output><jupyter_text>Calculating the metric CTR Tasks: 1. Find the top 10 ads by CTR for all time.2. Does the average and median CTR of ads differ in our data?---CTR is the ratio of all ad views to clicks<jupyter_code>ads_data_by_ad = ads_data.groupby(['ad_id', 'event'], as_index=False) \
    .agg({'time': 'count'}) \
    .pivot(index='ad_id', columns='event', values='time').reset_index()
ads_data_by_ad
ads_data_by_ad = ads_data_by_ad.assign(ctr = ads_data_by_ad.click / ads_data_by_ad.view,
                                      ctr_per = 100 * (ads_data_by_ad.click / ads_data_by_ad.view))
# assign function is useful because you can create several columns at once -> instead of the usual  ' df['some_column']= ' way
top_10_ctr = ads_data_by_ad.sort_values("ctr", ascending=False).head(10) # getting top 10 ads by ctr
top_10_ctr
plt.figure(figsize=(12, 6))
chart = sns.barplot(data = top_10_ctr, x="ad_id", y="ctr_per")
chart.set_xticklabels(chart.get_xticklabels(), rotation=45, horizontalalignment='right')
plt.show() 

# visualizing top 10 ads by ctr<jupyter_output><empty_output><jupyter_text>Does the average and median CTR of ads differ in our data?<jupyter_code>ads_data_by_ad.ctr.mean() # average CTR is about 2%
ads_data_by_ad.ctr.median() # median CTR is about 1%<jupyter_output><empty_output><jupyter_text>It can be concluded, that they differ twice. But why?-<jupyter_code>sns.displot(ads_data_by_ad.ctr, kde=False) # displot() shows the distribution<jupyter_output><empty_output><jupyter_text>The CTR distribution looks rather strange. There are many ads with a low CTR, meanwhile there are few ads with a high CTR. It can be expained that such data is hard to characterize with one number. For example: it is bad to take the average value of people's salaries, since the calculation includes both the richest people and people with low earnings. That's why we pick the median value of CTR.  Search for bugs in analytical blogs It looks like there is a bug in our logs, ads come with clicks, but without views!How many such ads are there, are there any patterns? Is this problem observed on all platforms?<jupyter_code>ads_data_by_ad
ads_data_by_ad = ads_data_by_ad.fillna(0) # No clicks is 0 clicks
ads_data_by_ad.query("click == 0").shape # 40 ads come with zero clicks
ads_ids_bug = ads_data_by_ad.query("click==0").ad_id 
ads_ids_bug
ads_data[ads_data.ad_id.isin(ads_ids_bug)] # We select from the initial data ids with bug
ads_with_bugs = ads_data[ads_data.ad_id.isin(ads_ids_bug)]\
    .groupby("platform", as_index=False)\
    .agg({"ad_id":"count"})\
    .rename(columns={"ad_id":"bug_number"})\
    .sort_values("bug_number")
sns.barplot(data=ads_with_bugs, x="bug_number", y="platform", orient="h", color="salmon")<jupyter_output><empty_output><jupyter_text>It would seem that the bug appears more often on android devices than on other devices. However, this is a hasty conclusion. Because initially the number of events on android is more. Therefore, the bug appears on android not because it happens there more often, but because there are more events with android in general.<jupyter_code>ads_data_by_platform = ads_data\
    .groupby("platform", as_index=False)\
    .agg({"ad_id":"count"})\
    .rename(columns={"ad_id":"number_of_events"})\
    .sort_values("number_of_events")
ads_data_by_platform
sns.barplot(data=ads_data_by_platform, x="platform", y="number_of_events")<jupyter_output><empty_output><jupyter_text>Are there any differences in the CTR of an ad with a video and without?And what is the 95th percentile CTR for all ads for 2019-04-04?<jupyter_code>df_2019_04_04 =  ads_data[ads_data.date == '2019-04-04'].groupby(['ad_id', 'event'], as_index=False) \
    .agg({'time': 'count'}) \
    .pivot(index='ad_id', columns='event', values='time').reset_index() \
    .fillna(0)
df_2019_04_04['ctr'] = df_2019_04_04.click / df_2019_04_04.view
df_2019_04_04
print(f"the 95th percentile CTR for all ads for 2019-04-04 is {df_2019_04_04[df_2019_04_04.click > 0].ctr.quantile(q=0.95)}")<jupyter_output>the 95th percentile CTR for all ads for 2019-04-04 is 0.10211538461538454
{'fraction_non_alphanumeric': 0.09372838367535162, 'fraction_numerical': 0.0223656905695181, 'mean_word_length': 5.057960893854749, 'pattern_counts': {'":': 9, '<': 40, '<?xml version=': 0, '>': 42, 'https://': 0, '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': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '272409', 'license': 'Unlicense', 'n_tokens_mistral': 2878, 'n_tokens_neox': 2670, 'n_words': 1014, 'path': 'Exploratory Data Analysis.ipynb', 'repo_name': 'yondaime43/Exploratory-Data-Analysis', 'size': '8674'}