Data Science Challenge: Ads Analysis¶
In [141]:
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
#sns.set_style("ticks")
sns.set_style("white")
#plt.style.use('fivethirtyeight')
plt.style.use('ggplot')
import warnings
warnings.filterwarnings("ignore")
%matplotlib inline
%config InlineBackend.figure_format = 'retina'
plt.rcParams["figure.figsize"] = (10,6)
plt.rcParams['axes.labelsize'] = 18
plt.rcParams['axes.titlesize'] = 22
pd.set_option('display.max_columns', 50)
pd.set_option('display.max_rows', 50)
Load Data¶
In [142]:
df = pd.read_csv('data/ad_table.csv')
df.head()
Out[142]:
In [143]:
df.info()
In [144]:
df.describe()
Out[144]:
Observations:
- Some problematic values, such as a negative revneue.
Data Cleaning¶
In [145]:
# convert date to datetime type
df['date'] = pd.to_datetime(df['date'])
In [146]:
df[df['total_revenue']<0]
Out[146]:
In [147]:
# There are 4 events with negative revenue
# We decide to remove those values
df = df[df['total_revenue']>=0]
In [148]:
# check if the funnel makes sense
len(df.query('shown<clicked | clicked<converted'))
Out[148]:
In [149]:
# check the shown trend across time by different group of ads
import matplotlib.dates as mdates
sns.set_style("white")
g = sns.lineplot(x='date',y='shown',hue='ad',data = df,legend='')
g.xaxis.set_major_locator(mdates.MonthLocator())
g.xaxis.set_major_formatter(mdates.DateFormatter('%b %d'))
plt.title('Ad impressions');
Observation:
- There are some sudden zeros definitely look weird.
In [150]:
# let's remove the zero values from shown data
df = df[df['shown']>0]
g = sns.lineplot(x='date',y='shown',hue='ad',data = df,legend='')
g.xaxis.set_major_locator(mdates.MonthLocator())
g.xaxis.set_major_formatter(mdates.DateFormatter('%b %d'))
plt.title('Ad impressions');
In [151]:
# check the click trend
g = sns.lineplot(x='date',y='clicked',hue='ad',data = df,legend='')
g.xaxis.set_major_locator(mdates.MonthLocator())
g.xaxis.set_major_formatter(mdates.DateFormatter('%b %d'))
plt.title('Ad clicked');
Observation:
- There are still a few weird drops to zero, despite having removed when impressions was zero.
- We decide to remove them, too.
In [152]:
df = df[df['clicked']>0]
g = sns.lineplot(x='date',y='clicked',hue='ad',data = df,legend='')
g.xaxis.set_major_locator(mdates.MonthLocator())
g.xaxis.set_major_formatter(mdates.DateFormatter('%b %d'))
plt.title('Ad clicked');
In [153]:
# check the conversion trend
g = sns.lineplot(x='date',y='converted',hue='ad',data = df,legend='')
g.xaxis.set_major_locator(mdates.MonthLocator())
g.xaxis.set_major_formatter(mdates.DateFormatter('%b %d'))
plt.title('Ad clicked');
In [154]:
# check out the final data
df.describe()
Out[154]:
Q: If you had to identify the 5 best ad groups, which ones would be? Which metric did you choose to identify the best ones? Why? Explain the pros of your metric as well as the possible cons. From a business perspective, choosing that metric implies that you are focusing on what?¶
There are obviously tons of ad related metric. And they are all plausible and useful in different situations, depending on the main business focus of a given company. A few examples are:
Click-through-rate (CTR), # of clicks/ # of impressions, i.e. of all people seeing the ad, how many click on it?
- Pros: it is great to identify demand from users. Also, since it only takes into account what the user does after seeing the ad, it can be easily estimated and allows to test and improve ad characteristics to maximize the number of users coming to the site
- Cons: It has no information about what the user does once they come to the site as well as costs. A very high CTR is not that useful is no one of those users converts. It can also easily be tricked via click-bait ads.
- This metric is purely growth-based when the goal is to bring users to the site
Conversion rate (CR), # of conversions/ # of impressions, i.e. of all people seeing the ad, how many people end up becoming customers?
- Pros: Fixes most of CTR issues. After all, the goal of an ad is to get customers, and this is what this metric represents.
- Cons: It has no information about costs. Also, conversion on the site highly depends on the product. An ad could be great in capturing demand, but if the product is bad, no one converts. Finally, how to link conversion to an ad click is pretty messy, conversion might happen after a long time, might happen after multiple visits through different channels, etc.
- This metric combines both growth and product.
Profits: revenue from conversions - ad costs
- Pros: Very concrete and business driven. Am I making money via ads?
- Cons: Same as CR, hard to define revenue from an ad, not only there are the issues described above, but here you would need to predict user lifetime value to see if an ad is profitable. Also, it only considers users after they click on the ad. It has no information about the top of the funnel, i.e. how many users see the ad and choose to not click.
- This metric focuses on the business/product side, but it is hardly useful from a growth standpoint. It is typically used by very large companies whose main goal is being profitable.
My choice of metrics in this project:
- In this project, let’s look into profits and CTR. We will pick the top 5 ads based on CTR as long as profits are above a certain threshold. In plain English, that means we want the ads with the highest growth potential after removing the ads that would make us go broken.
In [155]:
# check CTR & profits by ad groups
data_rank = df.groupby('ad').apply(
lambda x: pd.Series({
'CTR': (x['clicked']/x['shown']).mean(),
'profits': (x['total_revenue']-x['clicked']*x['avg_cost_per_click']).mean(),
'count': x['shown'].mean()
})
).reset_index()
data_rank.head()
Out[155]:
In [156]:
data_rank['profits'].describe()
Out[156]:
In [157]:
# check the distribution of profits
sns.distplot(data_rank['profits'], kde = True, bins = 20);
In [158]:
# barplot: count of ads in each group
sns.barplot(x = 'ad', y='count', data = data_rank)
plt.xticks(rotation=45, ha='right');
In [159]:
# plot CTR vs.profits
plt.figure(figsize=(10,8))
plt.scatter(data_rank['profits'], data_rank['CTR'])
plt.axvline(x=-5000)
plt.xlabel('profits')
plt.ylabel('CTR')
plt.show()
- We can define as threshold for profitability -5K.
- We will remove all ads below that, and then find the ads with the highest CTR among the remaining ones.
- Since we are considering short term revenue from the ad, we are expecting that number to be negative. An ad should be positive when taking into account a user life time value (as usual, lifetime -> 1 yr).
In [160]:
# Get the 5 best ad groups with highest CTR and above profitability of -5k
data_rank[data_rank['profits']>-5000].sort_values(by='CTR',ascending = False).head()
Out[160]:
Q: For each group, predict how many ads will be shown on Dec, 15¶
- Use the time series package prophet to do the prediction.
In [161]:
# check the shown for ad group1
data_ad1 = df[df['ad']=='ad_group_1'][["date", "shown"]]
#plot the time series trend
plt.figure(figsize=(10,6))
g=sns.lineplot(x="date", y="shown", data=data_ad1)
g.xaxis.set_major_locator(mdates.MonthLocator())
g.xaxis.set_major_formatter(mdates.DateFormatter('%b %d'))
g.set_title("Ad group 1")
plt.show()
In [162]:
#change column names and index
data_ad1['ds'] = data_ad1['date']
data_ad1.rename({'shown': 'y'}, axis=1, inplace=True)
data_ad1.set_index('date', inplace=True)
data_ad1.head()
Out[162]:
In [163]:
# current max day
data_ad1['ds'].max()
Out[163]:
In [164]:
#Let's build time series using prophet.
import fbprophet
#build the model
ts = fbprophet.Prophet()
ts.fit(data_ad1)
Out[164]:
In [165]:
#make predictions until Dec 15. This is the number of days for which we need a prediction to get to Dec 15
days_predictions = (pd.to_datetime("2015-12-15")-data_ad1['ds'].max()).days
future_data = ts.make_future_dataframe(periods = days_predictions)
predictions = ts.predict(future_data)
#let's plot the prediction
ts.plot(predictions)
plt.show()
In [166]:
#plot the time series component
ts.plot_components(predictions)
plt.show()
Observations:
- There is some weekly pattern.
- Most importantly, the time serie overall is pretty flat in October and starts going up in Nov.
- Predictions for Dec 15 tend to be up given that the model assumes the time series keeps following its most recent trend. Obviously, this would be much more useful with yearly data, so we would be able to capture yearly seasonality.
In [167]:
# fit the model to all groups and make predictions
#data set to store results
predictions_all_groups = pd.DataFrame({'ad_group':df['ad'].unique(),
'date': pd.to_datetime("2015-12-15")})
prediction_dec_15 = []
predictions_all_groups.head()
Out[167]:
In [168]:
# loop through all groups
for level in predictions_all_groups.ad_group:
#subset data, only keep 1 ad group and columns needed
tmp = df.loc[df['ad']==level][["date", "shown"]]
#change column names and index
tmp['ds'] = tmp['date']
tmp.rename({'shown': 'y'}, axis=1, inplace=True)
tmp.set_index('date', inplace=True)
#build the model
ts_tmp = fbprophet.Prophet()
ts_tmp.fit(tmp)
#make predictions until Dec 15. This is the number of days for which we need a prediction to get to Dec 15
days_predictions_tmp = (pd.to_datetime("2015-12-15")-tmp['ds'].max()).days
future_data_tmp = ts_tmp.make_future_dataframe(periods = days_predictions_tmp)
predictions_tmp = ts_tmp.predict(future_data_tmp)
#store results for the last day, i.e. Dec 15 prediction
prediction_dec_15.append(round(predictions_tmp['yhat'].iat[-1]))
In [169]:
# prediction results
predictions_all_groups['predictions'] = prediction_dec_15
predictions_all_groups
Out[169]:
Q: Cluster ads into 3 groups: the ones whose avg_cost_per_click is going up, the ones whose avg_cost_per_click is flat and the ones whose avg_cost_per_click is going down¶
In [170]:
# plot the overall avg_cost_per_click
g=sns.lineplot(x="date", y="avg_cost_per_click",hue="ad", data=df, legend="")
g.xaxis.set_major_locator(mdates.MonthLocator())
g.xaxis.set_major_formatter(mdates.DateFormatter('%b %d'))
plt.title("Avg Cost per Click") ;
It is not easy to detect trend from the plot above, though the overall trends seem to be flat.
Strategy:
- build a linear regression for each ad group.
- Cost_per_click will be the y value and the x axis is days as a number (1,2,3,etc).
- We will then check the coefficient of the regression【use Bonferroni rule for significance】:
- Positive and significant coefficient -> going up
- Negative and significant coefficient -> going down
- Non-significant coefficient -> flat
In [171]:
import statsmodels.api as sm
# store results
coefficient = []
p_value = []
# Loop through all ad groups
for level in df['ad'].unique():
#subset dataset and keep variables we care about
data_reg = df.loc[df['ad']==level][["date", "avg_cost_per_click"]]
#replace dates with numbers. 0 is the minimum date, 1 is minimum date + 1 day and so on
data_reg['date'] = (data_reg['date'] - data_reg['date'].min()).dt.days
#add intercept
data_reg['intercept'] = 1
#build the regression
lr = sm.OLS(data_reg['avg_cost_per_click'],data_reg.drop('avg_cost_per_click', axis=1)).fit()
#save coefficient and p-value
coefficient.append(lr.params[0])
p_value.append(lr.pvalues[0])
In [172]:
# put all together in a dataframe
lr_output = pd.DataFrame({
'ad_group': df['ad'].unique(),
'coefficients': coefficient,
'p_value': p_value})
# classify based on the rule described above.
# Note：we use Bonferroni rule for significance
lr_output['cluster'] = np.where((lr_output['coefficients']>0) & (lr_output['p_value']<0.05/40), 'going_up',
np.where((lr_output['coefficients']<0) & (lr_output['p_value']<0.05/40), 'going_down',
'flat'))
lr_output
Out[172]:
Insights:
- Looks like everything is flat.
- W/o using the Bonferroni correction (i.e. had we chosen a less aggressive p-value threshold), we would have likely found some significant ads.
- Also, via a linear regression, we are looking at the overall trend. That is, old data points weigh just as much as the most recent ones. Had we focused only on the most recent data, we obviously might have found different results.
In [ ]: