#!pip install pandas
#!pip install numpy
#!pip install plotly
#!pip install scikit-learn
#!pip install optuna
import pandas as pd
import numpy as np
import plotly.express as px
import plotly.graph_objects as go
from plotly.subplots import make_subplots
import os
pd.set_option('display.float_format', lambda x: '%.3f' % x)
data_CF = pd.read_csv(path_archivos + 'mmm.csv')
data_CF.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 200 entries, 0 to 199
Data columns (total 9 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 Date 200 non-null object
1 OpenTV 200 non-null float64
2 PayTV 200 non-null float64
3 Radio 200 non-null float64
4 Print 200 non-null float64
5 Facebook 200 non-null float64
6 Google 200 non-null float64
7 Email 200 non-null float64
8 Sales 200 non-null float64
dtypes: float64(8), object(1)
memory usage: 14.2+ KB
data_CF['Date'] = pd.to_datetime(data_CF['Date'])
data_desc = data_CF.describe().T
data_desc['cv'] = (data_desc['std'] / data_desc['mean']).round(3)
data_desc.sort_values(by='cv', ascending=False, inplace=True)
data_desc
countfloat64
meanfloat64
PayTV
200
3440.496211
OpenTV
200
4154.276494
200
1398.720108
Radio
200
3232.233894
200
3418.953571
200
1346.299509
200
1942.386141
Sales
200
106681.415
medios = ['Print', 'Email', 'Radio', 'Facebook', 'Google', 'PayTV', 'OpenTV']
fig = make_subplots(rows=7, cols=1, subplot_titles=medios)
for i, medio in enumerate(medios):
fig.add_trace(go.Scatter(x=data_CF['Date'], y=data_CF[medio], name=medio), row=i+1, col=1)
fig.update_layout(width=1200, height=800)
fig.show()
Histogramas de inversiones
def plot_histogram(medio):
plot = px.histogram( data_CF[data_CF[medio] != 0],
x=medio,
marginal="box",
title="Histograma de " + medio)
return plot
plot_histogram('PayTV')
plot_histogram('OpenTV')
plot_histogram('Facebook')
plot_histogram('Radio')
plot_histogram('Google')
plot_histogram('Print')
plot_histogram('Email')
px.pie(pd.melt( data_CF,
id_vars=['Date'],
value_vars=['Print', 'Email', 'Radio', 'Facebook', 'Google', 'PayTV', 'OpenTV'],
var_name= "Medio",
value_name= "Inversion"),
values='Inversion',
names='Medio',
title='Share of Investment by Media')
data_CF['year_month'] = data_CF['Date'].dt.strftime('%Y-%m')
data_CF['InversionTotal'] = data_CF[['Print', 'Email', 'Radio', 'Facebook', 'Google', 'PayTV', 'OpenTV']].sum(axis=1)
data_CF['month'] = data_CF['Date'].dt.month
data_CF['year'] = data_CF['Date'].dt.year
data_CF_com_year = data_CF.groupby(['month'], as_index = False )['Print', 'Email', 'Radio', 'Facebook', 'Google', 'PayTV', 'OpenTV','InversionTotal'].sum()
medios = ['Print', 'Email', 'Radio', 'Facebook', 'Google', 'PayTV', 'OpenTV']
px.bar(data_CF_com_year, x= 'month', y = medios)
/shared-libs/python3.7/py-core/lib/python3.7/site-packages/ipykernel_launcher.py:1: FutureWarning:
Indexing with multiple keys (implicitly converted to a tuple of keys) will be deprecated, use a list instead.
Correlacion de Inversion y Ventas
fig = make_subplots(specs=[[{"secondary_y": True}]])
data_tem = data_CF.groupby(['year_month'], as_index = False )['InversionTotal', 'Sales'].sum()
fig.add_trace(go.Bar(x=data_tem['year_month'],
y=data_tem['Sales'],
marker_color='LightSkyBlue',
name='Sales'), secondary_y=False)
fig.add_trace(go.Scatter(x=data_tem['year_month'],
y=data_tem['InversionTotal'],
marker_color='MediumPurple',
name='InversionTotal'), secondary_y=True)
fig.update_layout( title='Inversion en medios vs Ventas',
xaxis_title='Date',
yaxis_title='Inversion Total',
barmode='group',
# plot_bgcolor='white'
)
fig.show()
/shared-libs/python3.7/py-core/lib/python3.7/site-packages/ipykernel_launcher.py:3: FutureWarning:
Indexing with multiple keys (implicitly converted to a tuple of keys) will be deprecated, use a list instead.
data_CF['year'] = data_CF['year'].astype(str)
data_CF['InversionTotal'] = data_CF[['Print', 'Email', 'Radio', 'Facebook', 'Google', 'PayTV', 'OpenTV']].sum(axis=1)
import plotly.figure_factory as ff
df = data_CF[['Sales','Print', 'Email', 'Radio', 'Facebook', 'Google', 'PayTV', 'OpenTV']].corr().round(2)
fig = ff.create_annotated_heatmap( df.values.tolist(),
x=df.columns.values.tolist(),
y=df.index.values.tolist(),
colorscale='Viridis')
fig.update_layout(title_text='Correlacion')
fig['data'][0]['showscale'] = True
fig.show()
data_CF = data_CF.set_index('Date')
data_CF
OpenTVfloat64
0.0 - 18164.39087
PayTVfloat64
0.0 - 16677.10789
2018-01-07T00:00:00.000000
13637.75157
0
2018-01-14T00:00:00.000000
0
0
2018-01-21T00:00:00.000000
0
0
2018-01-28T00:00:00.000000
0
0
2018-02-04T00:00:00.000000
0
0
2018-02-11T00:00:00.000000
12914.28609
0
2018-02-18T00:00:00.000000
0
10299.58135
2018-02-25T00:00:00.000000
14560.64836
0
2018-03-04T00:00:00.000000
0
11595.63946
2018-03-11T00:00:00.000000
0
0
data_model = data_CF[['OpenTV', 'PayTV', 'Radio', 'Print', 'Facebook', 'Google', 'Email', 'Sales']]
data_model.head(3)
from sklearn.model_selection import train_test_split
train, test = train_test_split(data_model, test_size=0.2, random_state=10)
distributions= np.array([len(train), len(test)])
print(distributions)
[160 40]
from scipy.signal import convolve2d
from sklearn.base import BaseEstimator, TransformerMixin
from sklearn.utils.validation import check_is_fitted, check_array
class ExponentialSaturation (BaseEstimator, TransformerMixin):
def __init__ (self, a = 1.):
self.a = a
def fit(self, X, y = None):
X = check_array(X)
self._check_n_features(X, reset = True)
return self
def transform (self, X):
check_is_fitted (self)
X = check_array (X)
self._check_n_features (X, reset = False)
# Adbudg
#data_CF2.OpenTV/(data_CF2.OpenTV + (data_CF2.OpenTV.mean() * rho))
# rho = self.a
#return (X / (X + (X[X != 0].mean() * self.a)))
return (X / (X + (X.mean() * self.a)))
# exponencial
#return 1 - np.exp (-self.a * X)
from sklearn.base import BaseEstimator, TransformerMixin
class ExponentialCarryover(BaseEstimator, TransformerMixin):
def __init__(self, strength = 0.5, length = 1):
self.strength = strength
self.length = length
def fit(self, X, y = None):
X = check_array(X)
self._check_n_features(X, reset=True)
self.sliding_window_ = (
self.strength ** np.arange(self.length + 1)
).reshape(-1, 1)
return self
def transform(self, X: np.ndarray):
check_is_fitted(self)
X = check_array(X)
self._check_n_features(X, reset=False)
convolution = convolve2d(X, self.sliding_window_)
if self.length > 0:
convolution = convolution[: -self.length]
return convolution
from sklearn.pipeline import Pipeline
from sklearn.compose import ColumnTransformer
from sklearn.linear_model import LinearRegression
'OpenTV', 'PayTV', 'Radio', 'Print', 'Facebook', 'Google', 'Email'
adstock = ColumnTransformer([
('OpenTV_pipe', Pipeline([
('carryover', ExponentialCarryover()),
('saturation', ExponentialSaturation())
]), ['OpenTV']),
('PayTV_pipe', Pipeline([
('carryover', ExponentialCarryover()),
('saturation', ExponentialSaturation())
]), ['PayTV']),
('Radio_pipe', Pipeline([
('carryover', ExponentialCarryover()),
('saturation', ExponentialSaturation())
]), ['Radio']),
('Print_pipe', Pipeline([
('carryover', ExponentialCarryover()),
('saturation', ExponentialSaturation())
]), ['Print']),
('Facebook_pipe', Pipeline([
('carryover', ExponentialCarryover()),
('saturation', ExponentialSaturation())
]), ['Facebook']),
('Google_pipe', Pipeline([
('carryover', ExponentialCarryover()),
('saturation', ExponentialSaturation())
]), ['Google']),
('Email_pipe', Pipeline([
('carryover', ExponentialCarryover()),
('saturation', ExponentialSaturation())
]), ['Email']),
])
model = Pipeline([
('adstock', adstock),
('regression', LinearRegression())
])
from sklearn import set_config
set_config(display="diagram")
model
X_train = train.drop(columns='Sales')
Y_train = train['Sales']
model.fit(X_train,Y_train)
from sklearn.model_selection import cross_val_score, TimeSeriesSplit
x = data_model.drop(columns='Sales')
y = data_model['Sales']
print(cross_val_score(model, x, y, cv=TimeSeriesSplit()).mean())
0.7186041006756188
print(x.columns)
print('Coeficientes :', model[1].coef_)
print('Intersección :', model[1].intercept_)
Index(['OpenTV', 'PayTV', 'Radio', 'Print', 'Facebook', 'Google', 'Email'], dtype='object')
Coeficientes : [26646.62002489 17665.98921781 -3177.49937394 10057.34725363
1634.03830885 14738.56221635 73116.87324804]
Intersección : 48951.323145828894
# Optuna nos ayuda a encontrar los mejores parametros
from optuna.integration import OptunaSearchCV
from optuna.distributions import UniformDistribution, IntUniformDistribution
tuned_model = OptunaSearchCV(
estimator=model,
param_distributions={
'adstock__OpenTV_pipe__carryover__strength':UniformDistribution(0, 1), #Probara con valores de 0, 1
'adstock__OpenTV_pipe__carryover__length':IntUniformDistribution(0, 6), #Probara con valores de 0, 6
'adstock__OpenTV_pipe__saturation__a': UniformDistribution(0.1,1), #Probara con valores de 0, 1
'adstock__PayTV_pipe__carryover__strength':UniformDistribution(0, 1),
'adstock__PayTV_pipe__carryover__length':IntUniformDistribution(0, 6),
'adstock__PayTV_pipe__saturation__a': UniformDistribution(0.1,1),
'adstock__Radio_pipe__carryover__strength':UniformDistribution(0, 1),
'adstock__Radio_pipe__carryover__length':IntUniformDistribution(0, 6),
'adstock__Radio_pipe__saturation__a':UniformDistribution(0.1, 1),
'adstock__Print_pipe__carryover__strength':UniformDistribution(0, 1),
'adstock__Print_pipe__carryover__length':IntUniformDistribution(0, 6),
'adstock__Print_pipe__saturation__a':UniformDistribution(0.1, 1),
'adstock__Facebook_pipe__carryover__strength':UniformDistribution(0, 1),
'adstock__Facebook_pipe__carryover__length':IntUniformDistribution(0, 3),
'adstock__Facebook_pipe__saturation__a':UniformDistribution(0.1, 1),
'adstock__Google_pipe__carryover__strength':UniformDistribution(0, 1),
'adstock__Google_pipe__carryover__length':IntUniformDistribution(0, 3),
'adstock__Google_pipe__saturation__a':UniformDistribution(0.1, 1),
'adstock__Email_pipe__carryover__strength':UniformDistribution(0, 1),
'adstock__Email_pipe__carryover__length':IntUniformDistribution(0, 4),
'adstock__Email_pipe__saturation__a':UniformDistribution(0.1, 1),
},
n_trials=1000,
cv=TimeSeriesSplit(),
random_state=0
)
/shared-libs/python3.7/py-core/lib/python3.7/site-packages/ipykernel_launcher.py:36: ExperimentalWarning:
OptunaSearchCV is experimental (supported from v0.17.0). The interface can change in the future.
cross_val_score(tuned_model, x, y, cv = TimeSeriesSplit ()).mean()
tuned_model.fit(x,y)
tuned_model.best_params_
pd.set_option('display.max_rows', 30)
best_params = pd.DataFrame({'params': (tuned_model.best_params_).keys(),
'values' : (tuned_model.best_params_).values()})
best_params[['Modelo','Medio','Feature','Parametro']] = best_params.params.str.split('__', expand = True)
best_params
paramsobject
adstock__OpenTV_pipe__carryover__strength4.8%
adstock__OpenTV_pipe__carryover__length4.8%
19 others90.5%
valuesfloat64
0.0 - 2.0
0
adstock__OpenTV_pipe__carryover__strength
0.5200927346
1
adstock__OpenTV_pipe__carryover__length
0
2
adstock__OpenTV_pipe__saturation__a
0.2374347453
3
adstock__PayTV_pipe__carryover__strength
0.09265662458
4
adstock__PayTV_pipe__carryover__length
2
5
adstock__PayTV_pipe__saturation__a
0.1177277509
6
adstock__Radio_pipe__carryover__strength
0.2673447106
7
adstock__Radio_pipe__carryover__length
0
8
adstock__Radio_pipe__saturation__a
0.2529686216
9
adstock__Print_pipe__carryover__strength
0.2169068182
best_params.to_csv('best_params.csv', index = False)
print(tuned_model.best_estimator_.named_steps ['regression']. coef_)
[25383.5496797 16583.0537165 764.11824607 9821.47095091
3428.08290661 12588.22515772 56637.42887177]
coefbestparam = pd.DataFrame({'Medio' : list(data_model.columns)[:7],
'Valores' : tuned_model.best_estimator_.named_steps ['regression']. coef_})
base = {'Medio':'base', 'Valores' : tuned_model.best_estimator_.named_steps ['regression'].intercept_}
coefbestparam = coefbestparam.append(base, ignore_index=True)
coefbestparam.to_csv('coefbestparam.csv', index = False)
print(tuned_model.best_estimator_.named_steps ['regression'].intercept_)
54948.20598775267
data_model[[medio + '_sat' for medio in ['OpenTV', 'PayTV', 'Radio', 'Print', 'Facebook', 'Google', 'Email']]] = tuned_model.best_estimator_.named_steps['adstock'].transform(x)
data_model.to_csv('DataSaturada.csv')
df2 = data_model[['Sales','OpenTV_sat', 'PayTV_sat', 'Radio_sat', 'Print_sat', 'Facebook_sat', 'Google_sat', 'Email_sat']].corr().round(2)
fig = ff.create_annotated_heatmap( df2.values.tolist(),
x=df2.columns.values.tolist(),
y=df2.index.values.tolist(),
colorscale='Viridis')
fig.update_layout(title_text='Correlacion')
fig['data'][0]['showscale'] = True
fig.show()
fig = go.Figure()
for i, medio in enumerate(medios):
medio_sat = medio+'_sat'
data_adstock = data_model[data_model[medio_sat] != 0]
fig.add_trace(go.Scatter(x=data_adstock[medio],
y=data_adstock[medio_sat],
mode='markers',
name=medio))
fig.show()
fig = make_subplots(rows=7, cols=1, subplot_titles=medios)
for i, medio in enumerate(medios):
medio_sat = medio+'_sat'
data_adstock = data_model[data_model[medio_sat] != 0]
fig.add_trace(go.Scatter(x=data_adstock[medio], y=data_adstock[medio_sat], mode='markers', name=medio), row=i+1, col=1)
fig.update_layout(width=800, height=3000)
fig.show()
data_model.to_csv('DataSaturada.csv')
adstock_data = pd.DataFrame(
tuned_model.best_estimator_.named_steps['adstock'].transform(x),
columns=x.columns,
index=x.index
)
adstock_data.to_csv('adstock_data.csv')
weights = pd.Series(
tuned_model.best_estimator_.named_steps['regression'].coef_,
index=x.columns
)
weights
weights.to_csv('weights.csv')
adstock_data = pd.DataFrame(
tuned_model.best_estimator_.named_steps['adstock'].transform(x),
columns=x.columns,
index=x.index
)
weights = pd.Series(
tuned_model.best_estimator_.named_steps['regression'].coef_,
index=x.columns
)
base = tuned_model.best_estimator_.named_steps['regression'].intercept_
unadj_contributions = adstock_data.mul(weights).assign(Base=base)
adj_contributions = (unadj_contributions
.div(unadj_contributions.sum(axis=1), axis=0)
.mul(y, axis=0)
)
adj_contributions.to_csv('adj_contributions.csv')
adj_contributions = adj_contributions.reset_index()
inversion_melt = pd.melt(adj_contributions,
id_vars = 'Date',
value_vars = ['OpenTV', 'PayTV', 'Radio', 'Print', 'Facebook', 'Google', 'Email'],
var_name='Medios',
value_name='Inversion')
inversion_melt.head()
Datedatetime64[ns]
Mediosobject
0
2018-01-07T00:00:00.000000
OpenTV
1
2018-01-14T00:00:00.000000
OpenTV
2
2018-01-21T00:00:00.000000
OpenTV
3
2018-01-28T00:00:00.000000
OpenTV
4
2018-02-04T00:00:00.000000
OpenTV
atribucion_ventas_medio = pd.read_csv('./adj_contributions.csv')
atribucion_ventas_medio['Date'] = pd.to_datetime(atribucion_ventas_medio['Date'])
atribucion_ventas_medio = atribucion_ventas_medio.set_index('Date')
atribucion_ventas_medio = atribucion_ventas_medio.rename(columns=lambda x: x+'_revenue')
atribucion_ventas_medio = atribucion_ventas_medio.drop(columns=['Base_revenue'])
investment_medio = pd.read_csv('./Data/mmm.csv')
investment_medio['Date'] = pd.to_datetime(investment_medio['Date'])
investment_medio = investment_medio.set_index('Date')
investment_medio = investment_medio.rename(columns=lambda x: x+'_investment')
investment_medio = investment_medio.drop(columns=['Sales_investment'])
atribucion_investment_medio = pd.merge(investment_medio, atribucion_ventas_medio, how='left', left_index=True, right_index=True)
atribucion_investment_medio
OpenTV_investmentfloat64
0.0 - 18164.39087
PayTV_investmentfloat64
0.0 - 16677.10789
2018-01-07T00:00:00.000000
13637.75157
0
2018-01-14T00:00:00.000000
0
0
2018-01-21T00:00:00.000000
0
0
2018-01-28T00:00:00.000000
0
0
2018-02-04T00:00:00.000000
0
0
2018-02-11T00:00:00.000000
12914.28609
0
2018-02-18T00:00:00.000000
0
10299.58135
2018-02-25T00:00:00.000000
14560.64836
0
2018-03-04T00:00:00.000000
0
11595.63946
2018-03-11T00:00:00.000000
0
0
ROI Analisis
# calcular el ROI para cada medio
atribucion_investment_medio = pd.merge(investment_medio, atribucion_ventas_medio, how='inner', left_index=True, right_index=True)
amedios3 = ['OpenTV', 'Print', 'Email', 'Radio', 'Facebook', 'Google', 'PayTV']
for medio in amedios3:
atribucion_investment_medio[medio+'_ROI'] = (atribucion_investment_medio[medio+'_revenue'] - atribucion_investment_medio[medio+'_investment'])/atribucion_investment_medio[medio+'_investment']
amedios4 = ['OpenTV', 'Print', 'Facebook', 'Google', 'PayTV', 'Email', 'Radio']
medio_select = amedios4[1]
atribucion_investment_medio = atribucion_investment_medio[atribucion_investment_medio[medio_select+'_investment'] != 0]
roi_plot = make_subplots(specs=[[{"secondary_y": True}]])
# revenue vs investment
roi_plot.add_trace(go.Scatter( x=atribucion_investment_medio[medio_select+'_investment'],
y=atribucion_investment_medio[medio_select+'_revenue'],
mode='markers',
name='Investment vs Revenue'),
secondary_y=False)
# revenue vs ROI
roi_plot.add_trace(go.Scatter( x=atribucion_investment_medio[medio_select+'_investment'],
y=atribucion_investment_medio[medio_select+'_ROI'],
mode='markers',
name='Investment vs ROI'),
secondary_y=True)
roi_plot.update_layout( #title_text=medio_select+' ROI',
yaxis_title='Revenue',
yaxis2_title='ROI',
xaxis_title='Investment')
roi_plot.show()