CREATION OF THE ANALYTICAL DATAMART

SET UP

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
%matplotlib inline
import seaborn as sns


#Automcompletar rápido
%config IPCompleter.greedy=True

#Formato de display
pd.options.display.float_format = '{:15.2f}'.format
pd.options.display.max_columns = 8

DATA UPLOAD

import sqlalchemy as sa
con = sa.create_engine('sqlite:///ecommerce.db')

from sqlalchemy import inspect
insp = inspect(con)
tablas = insp.get_table_names()
tablas

['2019-Dec', '2019-Nov', '2019-Oct', '2020-Feb', '2020-Jan']

oct = pd.read_sql('2019-Oct', con)
nov = pd.read_sql('2019-Nov', con)
dic = pd.read_sql('2019-Dec', con)
ene = pd.read_sql('2020-Jan', con)
feb = pd.read_sql('2020-Feb', con)

DATA INTEGRATION

df = pd.concat([oct,nov,dic,ene,feb], axis = 0)
df

DATA QUALITY

Types of variables

df.info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 2095076 entries, 0 to 429789
Data columns (total 10 columns):
 #   Column         Dtype  
---  ------         -----  
 0   index          int64  
 1   event_time     object 
 2   event_type     object 
 3   product_id     int64  
 4   category_id    int64  
 5   category_code  object 
 6   brand          object 
 7   price          float64
 8   user_id        int64  
 9   user_session   object 
dtypes: float64(1), int64(4), object(5)
memory usage: 175.8+ MB

We remove the column index.

df.drop(columns = 'index', inplace = True)

Analysis and correction of types.

• pass event_time to datetime

We pass event_time to datetime.

def datetime_rapido(dt,formato):

    def divide_fecha(fecha):
        division = fecha.split()
        date = division[0]
        time = division[1]
        cadena = date + ' ' + time
        return cadena

    resultado = pd.to_datetime(dt.apply(lambda x: divide_fecha(x)), format = formato)

    return resultado

Ejecutamos la función.

formato = '%Y-%m-%d %H:%M:%S'

df.event_time = datetime_rapido(df.event_time,formato)

df.info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 2095076 entries, 0 to 429789
Data columns (total 9 columns):
 #   Column         Dtype         
---  ------         -----         
 0   event_time     datetime64[ns]
 1   event_type     object        
 2   product_id     int64         
 3   category_id    int64         
 4   category_code  object        
 5   brand          object        
 6   price          float64       
 7   user_id        int64         
 8   user_session   object        
dtypes: datetime64[ns](1), float64(1), int64(3), object(4)
memory usage: 159.8+ MB

Variable names

df.columns = ['fecha',
              'evento',
              'producto',
              'categoria',
              'categoria_cod',
              'marca',
              'precio',
              'usuario',
              'sesion']
df

Analysis of nulls

df.isna().sum().sort_values(ascending = False)

categoria_cod    2060411
marca             891646
sesion               506
fecha                  0
evento                 0
producto               0
categoria              0
precio                 0
usuario                0
dtype: int64

Conclusions:

• categoria_cod has almost all records set to null
• brand has almost half of the records to null
• there are 500 nulls in session

Actions:

• eliminate the variables category_cod and brand
• remove session nulls as it is a relevant variable

df = df.drop(columns = ['categoria_cod','marca']).dropna()
df

Analysis of numerical variables

df.describe()

We see negatives in the price. Let's go deeper.

df[df.precio <= 0]

It's about 20000 records, we could delete them.

But first, do they perhaps focus on a specific product?

df[df.precio <= 0].producto.value_counts().head(10)

5896186    79
5903915    50
5873428    37
5851294    29
5851304    29
5837624    28
5712583    27
5851272    27
5907812    26
5899512    26
Name: producto, dtype: int64

It doesn't seem like it's a specific product issue, so we're going to remove all logs.

df = df[df.precio > 0]
df

Analysis of categorical variables

df.evento.nunique()

4

df.evento.value_counts()

view                961558
cart                574547
remove_from_cart    410357
purchase            127564
Name: evento, dtype: int64

df.producto.nunique()

45327

df.categoria.nunique()

508

Index

Let's put the date as the index.

df.set_index('fecha', inplace = True)
df

DATA TRANSFORMATION

We are going to create 3 types of new variables

• extract components
• Calendar variables: Local holidays (Russia)
• Exogenous indicators: Days not necessarily holidays but with commercial interest: Black Friday, Cyber Monday, Three Kings, San Valentin

Date Components

def componentes_fecha(dataframe):
    date = dataframe.index.date
    año = dataframe.index.year
    mes = dataframe.index.month
    dia = dataframe.index.day
    hora = dataframe.index.hour
    minuto = dataframe.index.minute
    segundo = dataframe.index.second


    return(pd.DataFrame({'date':date, 'año':año,'mes':mes, 'dia':dia, 'hora':hora, 'minuto':minuto, 'segundo':segundo}))

df = pd.concat([df.reset_index(),componentes_fecha(df)], axis = 1).set_index('fecha')
df

Calendar variables: holidays

To incorporate holidays we can use the holidays.

It's not perfect, but it gives us a lot of flexibility because it has parties from various countries and even at the community level.

And we can see the list of countries and the basic use in:

https://github.com/dr-prodigy/python-holidays

import holidays

We define the object festive_ru since this ecommerce is Russian.

festivo_ru = holidays.RU(years=2020)
festivo_ru

{datetime.date(2020, 1, 1): 'Новый год', datetime.date(2020, 1, 2): 'Новый год', datetime.date(2020, 1, 3): 'Новый год', datetime.date(2020, 1, 4): 'Новый год', datetime.date(2020, 1, 5): 'Новый год', datetime.date(2020, 1, 6): 'Новый год', datetime.date(2020, 1, 7): 'Православное Рождество', datetime.date(2020, 1, 8): 'Новый год', datetime.date(2020, 2, 23): 'День защитника отечества', datetime.date(2020, 3, 8): 'День женщин', datetime.date(2020, 5, 1): 'Праздник Весны и Труда', datetime.date(2020, 5, 9): 'День Победы', datetime.date(2020, 6, 12): 'День России', datetime.date(2020, 11, 4): 'День народного единства', datetime.date(2020, 12, 31): 'Новый год'}

We are going to add a variable that tells each record if it was a holiday or not.

df['festivo'] = df.date.apply(lambda x: 1 if (x in festivo_ru) else 0)
df

We check the holidays.

df[df.festivo == 1].date.value_counts().sort_index()

2019-11-04    16430
2019-12-31     2848
2020-01-01     7644
2020-01-02    10776
2020-01-03    10617
2020-01-04    13084
2020-01-05    14554
2020-01-06    10621
2020-01-07    12922
2020-01-08    14004
2020-02-23     9817
Name: date, dtype: int64

Exogenous indicators

We are going to add indicators for Black Friday and Valentine's Day.

df['black_friday'] = 0
df.loc['2019-11-29','black_friday'] = 1

df['san_valentin'] = 0
df.loc['2020-02-14','san_valentin'] = 1

Comprobamos

df['black_friday'].value_counts()

0    2051695
1      22331
Name: black_friday, dtype: int64

df['san_valentin'].value_counts()

0    2061781
1      12245
Name: san_valentin, dtype: int64

FINAL ANALYTICAL BOARD

We review what we have.

df.head()

Let's put the columns in a more natural order.

variables = df.columns.to_list()
variables

['evento',
 'producto',
 'categoria',
 'precio',
 'usuario',
 'sesion',
 'date',
 'año',
 'mes',
 'dia',
 'hora',
 'minuto',
 'segundo',
 'festivo',
 'black_friday',
 'san_valentin']

orden = ['usuario',
         'sesion',
         'categoria',
         'evento',
         'producto',
         'precio']

orden

['usuario', 'sesion', 'categoria', 'evento', 'producto', 'precio']

resto = [nombre for nombre in variables if nombre not in orden]

resto

['date',
 'año',
 'mes',
 'dia',
 'hora',
 'minuto',
 'segundo',
 'festivo',
 'black_friday',
 'san_valentin']

df = df[orden + resto]
df

We save as pickle so as not to lose the metadata.

df.to_pickle('tablon_analitico.pickle')

ANALYSIS AND INSIGHTS

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
%matplotlib inline
import seaborn as sns

import fiser_tools as fs
fs.misc.dark_theme()


#Automcompletar rápido
%config IPCompleter.greedy=True

#Formato de display
pd.options.display.float_format = '{:15.2f}'.format
pd.options.display.max_columns = 8

df = pd.read_pickle('tablon_analitico.pickle')
df

Understanding events

How is the Customer Journey working?

eventos = df.evento.value_counts()
eventos

view                961558
cart                574547
remove_from_cart    410357
purchase            127564
Name: evento, dtype: int64

kpi_visualizaciones_p = 100
kpi_carrito_p = eventos.loc['cart'] / eventos.loc['view'] * 100
kpi_abandono_p = eventos.loc['remove_from_cart'] / eventos.loc['cart'] * 100
kpi_compra_p = eventos.loc['purchase'] / eventos.loc['cart'] * 100

kpis = pd.DataFrame({'kpi':['visitas','carrito','compra'],
                     'valor':[kpi_visualizaciones_p,kpi_carrito_p,kpi_compra_p]})

kpis

from plotly import graph_objects as go

fig = go.Figure(go.Funnel(
    y = kpis.kpi,
    x = kpis.valor.round(2),
    marker = {'color': ['red','blue','green']},
    opacity = 0.3
    ))

fig.update_layout(
    title = 'Funnel Conversión Inicial')

fig.show()

Conclusions:

• Starting rates are 60% cart on views and 22% purchase on cart
• Therefore, there are 40% of visits that need to be worked on to get more carts, and 78% of carts that need to be worked on to get more purchases.

How many products are viewed, added to cart, abandoned and purchased on average in each session?

Unlike the macro analysis of the funnel, this analysis is per session, which makes it more operational.

Knowing the main kpis per session allows us to establish the baseline to measure the results of CRO actions.

First we create a dataframe with the granularity at the session and event level that we need.

sesion_prod = df.groupby(['sesion','evento']).producto.count()
sesion_prod

sesion                                evento
0000597b-de39-4a77-9fe5-02c8792ca14e  view      3
0000645a-8160-4a3d-91bf-154bff0a22e3  view      2
000090e1-da13-42b1-a31b-91a9ee5e6a88  view      1
0000b3cb-5422-4bf2-b8fe-5c1831d0dc1b  view      1
0000de26-bd58-42c9-9173-4763c76b398e  view      1
                                               ..
ffff6695-b64d-4a67-aa14-34b3b7f63c3f  view      2
ffff7d69-b706-4c64-9d6d-da57a04bc32b  view      1
ffff8044-2a22-4846-8a72-999e870abbe9  view      1
ffff91d4-7879-4a4b-8b26-c67915a27dc8  view      1
ffffbe0a-d2c2-47c7-afab-680bfdfda50d  view      1
Name: producto, Length: 581763, dtype: int64

We pass the events to columns.

sesion_prod = sesion_prod.unstack().fillna(0)
sesion_prod

To check we calculate the totals and it should give us the same as globally.

sesion_prod.sum()

evento
cart                     574547.00
purchase                 127564.00
remove_from_cart         410357.00
view                     961558.00
dtype: float64

We rearrange the columns.

sesion_prod = sesion_prod[['view','cart','remove_from_cart','purchase']]
sesion_prod

We calculated the mean of each event per session.

media_eventos_sesion = sesion_prod.mean()
media_eventos_sesion

evento
view                          2.16
cart                          1.29
remove_from_cart              0.92
purchase                      0.29
dtype: float64

Conclusion:

In each session, on average:

• 2.2 products are seen
• 1.3 products are added to the cart
• 0.9 products are removed from the cart
• 0.3 products are purchased

As we said, these are the numbers that we must increase with the CRO actions.

Are there differences between hourly events?

We create the dataframe at event and time granularity.

eventos_hora = df.groupby(['evento','hora']).producto.count()
eventos_hora

evento  hora
cart    0        6475
        1        5555
        2        6433
        3        8544
        4       11242
                ...  
view    19      63730
        20      57311
        21      38905
        22      23043
        23      13307
Name: producto, Length: 96, dtype: int64

We pass the events to columns.

eventos_hora = eventos_hora.unstack(level = 0)
eventos_hora

Let's visualize how the events are distributed per hour.

eventos_hora.plot()
plt.xticks(ticks = eventos_hora.index);

There is a global pattern as expected.

But to better see the differences we can create a new variable that is the ratio of purchases per visit in each hour.

eventos_hora['compras_visitas'] = eventos_hora.purchase / eventos_hora.view * 100
eventos_hora

We rearrange the variables

eventos_hora = eventos_hora[['view','cart','remove_from_cart','purchase','compras_visitas']]
eventos_hora

We visualize to see if there are hours in which proportionally more is purchased.

plt.figure(figsize = (12,6))
sns.lineplot(data = eventos_hora, x = eventos_hora.index, y = 'compras_visitas')
plt.xticks(eventos_hora.index);

Conclusions:

• The hours in which people buy the most are 1, 8, 11 to 13 and 18
• The hours in which people do not buy are 24:00, from 3 to 7, from 14 to 17 and from 19 to 23

We are now going to analyze not proportionally, but absolutely, whether or not there are more frequent hours for each type of event.

plt.figure(figsize = (12,12))
sns.heatmap(data = eventos_hora);

The problem is that since each event has a different scale, this graph does not allow us to differentiate the patterns well.

To solve it we can use the typing of variables that we learned in the statistics module.

def tipificar(variable):
    media = variable.mean()
    dt = variable.std()
    return(variable.apply(lambda x: (x - media) / dt))

eventos_hora_tip = eventos_hora.apply(tipificar)
eventos_hora_tip

plt.figure(figsize = (12,12))
sns.heatmap(data = eventos_hora_tip);

Let's also pull out the line graphs to see it more clearly.

eventos_hora_tip.plot(subplots = True, sharex = False, figsize = (12,12),xticks = eventos_hora_tip.index);

Conclusions:

• INSIGHT #1: All metrics are maximized in the time slots between 9 a.m. and 1 p.m. and between 6 p.m. and 8 p.m.
• This information is very relevant, for example, for paid ads, both for traffic generation and retargeting
• In addition, there seems to be some subtype of user who buys at 1 in the morning, who, although not very frequent, does buy a lot

What is the average monthly billing?

df.loc[df.evento == 'purchase'].groupby('mes').precio.sum().mean()

124309.92

¿Cúal es la tendencia en los últimos meses?

tendencia = df.groupby('evento').resample('W').evento.count().unstack(level = 0)
tendencia

tendencia = tendencia[['view','cart','remove_from_cart','purchase']]
tendencia

tendencia.plot(subplots = True, figsize = (12,6), sharex = True, xticks = tendencia.index, x_compat=True, rot = 90);

The trend is flat across all metrics, confirming the need for CRO stocks.

There is a significant peak in the week of the 24th, obviously due to Black Friday, we are going to do the same analysis but daily and only for November and December to see the effect.

tendencia_diaria = df.loc['2019-11':'2019-12'].groupby('evento').resample('D').evento.count().unstack(level = 0)
tendencia_diaria

tendencia_diaria = tendencia_diaria[['view','cart','remove_from_cart','purchase']]
tendencia_diaria

tendencia_diaria.plot(subplots = True, figsize = (16,10), sharex = True, xticks = tendencia_diaria.index, x_compat=True, rot = 90);

Conclusions:

• Indeed the peak coincides with black friday (day 29)
• But there is still a bigger peak a few days before, on the 22nd, possibly due to the start of Black Friday week
• Surprisingly, the days of Christmas themselves have a decreasing trend, which means that consumers have clearly advanced their purchases

We are going to do the same analysis for January and February.

tendencia_diaria = df.loc['2020-01':'2020-02'].groupby('evento').resample('D').evento.count().unstack(level = 0)
tendencia_diaria = tendencia_diaria[['view','cart','remove_from_cart','purchase']]
tendencia_diaria.plot(subplots = True, figsize = (16,10), sharex = True, xticks = tendencia_diaria.index, x_compat=True, rot = 90);

Conclusions:

• During the week of Kings there is no sales peak either
• Nor the days before Valentine's Day
• But there is a very pronounced peak on January 27, surely some local event

INSIGHT #2 The big takeaway is that all the Christmas shopping pie is delivered on Black Friday week

Moments of truth?

Could we manage to identify moments at the day-hour level in which the greatest number of purchases take place?

It would be very useful to focus a large part of the investment of campaigns just at those moments.

compras_dia_hora = df.loc[df.evento == 'purchase'].groupby(['date','hora']).evento.count().unstack(level = 0).fillna(0)
compras_dia_hora

plt.figure(figsize = (20,14))
sns.heatmap(compras_dia_hora);

Understanding customers

To analyze at the customer level, it is best to create a dataframe of only buyers with customer granularity and the variables that interest us.

We must be careful with the aggregation function that we use in each one.

clientes = df.loc[df.evento == 'purchase'].groupby(['usuario']).agg({'producto':'count',
                                                          'sesion':'nunique', 
                                                          'precio': 'mean',
                                                          'date': 'max'})

clientes

we rename

clientes.columns = ['productos_tot_num','compras_tot_num','precio_medio_prod','ult_compra']
clientes

We are going to calculate additional variables.

clientes['gasto_tot'] = clientes.productos_tot_num * clientes.precio_medio_prod
clientes['productos_por_compra'] = clientes.productos_tot_num / clientes.compras_tot_num
clientes

How are customers distributed in terms of spending?

sns.histplot(data = clientes, x = 'gasto_tot', bins = 50)
plt.xlim([0,300]);

The vast majority of clients have spent less than €50 in the period.

How are customers distributed in terms of the number of purchases?

sns.countplot(data = clientes, x = 'compras_tot_num');

INSIGHT #3 The vast majority of customers only make one purchase.

There is a long way to go to improve this ratio through:

• email marketing with newsletters and personalized offers

How many products does an average customer buy with each purchase?

clientes.productos_por_compra.describe()

count          11040.00
mean               7.79
std                9.49
min                1.00
25%                3.00
50%                5.00
75%               10.00
max              219.00
Name: productos_por_compra, dtype: float64

INSIGHT #4 The medium purchase includes 5 products.

But 25% of customers buy more than 10 products in the same purchase.

There is a long way to go to improve this ratio through:

• recommendation systems at the time of purchase

Which clients have generated the most income for us?

clientes.nlargest(n = 10, columns = 'gasto_tot')

To calculate we calculate the average total spend per customer.

clientes.gasto_tot.describe()

count          11040.00
mean              56.30
std               81.73
min                0.13
25%               16.22
50%               32.74
75%               60.30
max             1559.21
Name: gasto_tot, dtype: float64

INSIGHT #5 There are customers with average spending dozens of times higher than the average.

These customers must be retained through loyalty programs.

What is customer survival?

Since we only have 5 months of history, we are going to create cohort analyzes 3 months into the future, which gives us 3 cohorts.

We prepare a dataframe only with buyers and with the user and month variables.

c = df.loc[df.evento == 'purchase', ['usuario','mes']]
c

We pass the months to columns.

c = pd.crosstab(c.usuario,c.mes).reset_index()
c

We rename and delete the user that we no longer need it.

c.columns = ['usuario','c4','c5','c1','c2','c3']
c.drop(columns = 'usuario', inplace = True)
c

The first cohort will be the one from month 2, since we want to select "new" clients (unless they were not there the previous month)

c2 = c.loc[(c.c1 == 0) & (c.c2 > 0)]
c2

We go to a binary dataframe since we only care if that customer has purchased or not in each month.

def binarizar(variable):
    variable = variable.transform(lambda x: 1 if (x > 0) else 0)
    return(variable)

c2_b = c2.apply(binarizar)
c2_b

We calculate the percentage of customers in this cohort who have continued to purchase in subsequent months.

c2_f = c2_b.sum() / c2_b.shape[0]
c2_f = c2_f.sort_index()
c2_f

c1              0.00
c2              1.00
c3              0.10
c4              0.10
c5              0.08
dtype: float64

We replicated the entire process for cohort 3

c3 = c.loc[(c.c2 == 0) & (c.c3 > 0)]
c3_b = c3.apply(binarizar)
c3_f = c3_b.sum() / c3_b.shape[0]
c3_f = c3_f.sort_index()
c3_f['c1'] = 0
c3_f

c1              0.00
c2              0.00
c3              1.00
c4              0.10
c5              0.08
dtype: float64

We replicated the entire process for cohort 4

c4 = c.loc[(c.c3 == 0) & (c.c4 > 0)]
c4_b = c4.apply(binarizar)
c4_f = c4_b.sum() / c4_b.shape[0]
c4_f = c4_f.sort_index()
c4_f['c1'] = 0
c4_f['c2'] = 0
c4_f

c1              0.00
c2              0.00
c3              0.00
c4              1.00
c5              0.12
dtype: float64

We create the cohort dataframe.

cohortes = pd.DataFrame({'c2':c2_f,'c3':c3_f,'c4':c4_f})
cohortes

cohortes = cohortes.drop(index = 'c1').T
cohortes

plt.figure(figsize = (12,6))
sns.heatmap(cohortes,annot = True, fmt = '.0%', cmap='Greys');

INSIGHT #6: 90% of new customers do not buy again in the following months

What is the LTV of the clients?

Taking into account 90% of the fact that new customers do not buy again in the following months, we can calculate the LTV with the historical one that we have without fear of being very wrong.

To do this, we are going to take the customers of cohort 2 and calculate the total of their purchases.

maestro_ltv = df.loc[(df.evento == 'purchase') & (df.mes != 10) & (df.mes == 11),'usuario'].to_list()
maestro_ltv

[549319657,
 549319657,
 549319657,
 549319657,
 ...
 ...
 510126861,
 510126861,
 510126861,
 440684807,
 ...]

clientes_ltv = clientes.loc[clientes.index.isin(maestro_ltv)]
clientes_ltv

clientes_ltv.gasto_tot.describe()

count           3105.00
mean              79.62
std              113.62
min                0.13
25%               20.29
50%               41.49
75%               90.00
max             1453.37
Name: gasto_tot, dtype: float64

Given the variability of the mean, it would be safer to take the median.

INSIGHT #7: The average LTV is €42.

Applying our margin on that figure and the % that we want to dedicate to acquisition, we get the maximum amount to invest in CPA.

Applying CRO actions will allow you to increase the LTV and therefore also the CPA, being a very important strategic advantage.

On which customers to run the next campaigns (RFM)?

We are going to learn a technique called RFM (Recency - Frequency - Monetary).

This technique is very powerful for retail contexts and therefore also in ecommerce.

It allows responding to needs such as:

• What is the ratio of customers who place a single order and repeat customers
• Which are the VIP clients (who potentially need loyalty programs and personalized attention)
• What is the number of new customers (to encourage them to return to place an order)
• How many and which are the customers who have not made purchases for a long time
• How many and which are the clients in which it is not worth investing more time and resources
• Etc

Despite its power, it is very simple to build, therefore it is almost mandatory in this type of analysis.

The first thing is to identify the variables with which to create each of the dimensions:

• Recency: last_purchase
• Frequency: purchases_tot_num
• Monetary: total_expense

And discretize each of them.

We are going to leave the recency for last because it will require a previous transformation.

We start with Frequency

clientes['F'] = clientes.compras_tot_num.transform(lambda x: pd.cut(x,5, labels = False)) + 1
clientes

we check

clientes.groupby('F').compras_tot_num.mean()

F
1              1.31
2              7.06
3             12.00
4             16.50
5             23.50
Name: compras_tot_num, dtype: float64

Now Monetary

clientes['M'] = clientes.gasto_tot.transform(lambda x: pd.cut(x,5, labels = False)) + 1
clientes.groupby('M').gasto_tot.mean()

M
1             48.36
2            410.98
3            765.18
4           1043.96
5           1468.34
Name: gasto_tot, dtype: float64

For the recency we have to transform the date into a number, for example the distance in days from each date to the most recent date available.

mas_reciente = clientes.ult_compra.max()

clientes['ult_compra_dias'] = clientes.ult_compra.transform(lambda x: mas_reciente - x)

clientes

A timedelta has been created for us, we have to pass it to number of days.

clientes['ult_compra_dias'] = clientes.ult_compra_dias.dt.days
clientes

We can now create the R, but keep in mind that in this case the best are the lowest values.

clientes['R'] = clientes.ult_compra_dias.transform(lambda x: pd.cut(x,5, labels = False)) + 1
clientes.groupby('R').ult_compra_dias.mean()

R
1             14.62
2             43.04
3             75.94
4            103.85
5            135.91
Name: ult_compra_dias, dtype: float64

To standardize its interpretation with the rest of the dimensions, we are going to turn it around.

clientes['R'] = 6 - clientes.R
clientes.groupby('R').ult_compra_dias.mean()

R
1            135.91
2            103.85
3             75.94
4             43.04
5             14.62
Name: ult_compra_dias, dtype: float64

We integrate into an rfm dataframe.

clientes

We create additional variables.

clientes['valor'] = clientes.R + clientes.F + clientes.M
clientes['RFM'] = clientes.apply(lambda x: str(x.R) + str(x.F) + str(x.M), axis = 1)
clientes

On this dataframe we can already do an infinite number of analyses.

For example, combining it with the minicube technique we can obtain all kinds of insights.

#Paso 1: Seleccionar qué variables serán la métricas y cuales las dimensiones
metricas = ['productos_tot_num','compras_tot_num','gasto_tot']
dimensiones = ['R','F','M','RFM','valor']

minicubo = clientes[dimensiones + metricas]
minicubo

#Paso 2: pasar a transaccional las dimensiones
minicubo = minicubo.melt(id_vars = metricas)
minicubo

#Paso 3: Agregar las métricas por "variable" y "valor" con las funciones deseadas
minicubo = minicubo.groupby(['variable','value'], as_index = False)[metricas].mean()
minicubo

To analyze each dimension we select it.

minicubo[minicubo.variable == 'F']

And we analyze it graphically.

minicubo[minicubo.variable == 'F'].set_index('value').plot.bar(subplots = True, sharex = False, figsize = (12,12))
plt.tight_layout();

c:\Users\sgarciam\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\core\indexes\base.py:6982: FutureWarning:

In a future version, the Index constructor will not infer numeric dtypes when passed object-dtype sequences (matching Series behavior)

minicubo[minicubo.variable == 'R']

minicubo[minicubo.variable == 'R'].set_index('value').plot.bar(subplots = True, sharex = False, figsize = (12,12))
plt.tight_layout();

c:\Users\sgarciam\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\core\indexes\base.py:6982: FutureWarning:

In a future version, the Index constructor will not infer numeric dtypes when passed object-dtype sequences (matching Series behavior)

minicubo[minicubo.variable == 'M']

minicubo[minicubo.variable == 'M'].set_index('value').plot.bar(subplots = True, sharex = False, figsize = (12,12))
plt.tight_layout();

c:\Users\sgarciam\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\core\indexes\base.py:6982: FutureWarning:

In a future version, the Index constructor will not infer numeric dtypes when passed object-dtype sequences (matching Series behavior)

minicubo[minicubo.variable == 'RFM']

minicubo[minicubo.variable == 'RFM'].set_index('value').plot.bar(subplots = True, sharex = False, figsize = (12,12))
plt.tight_layout();

minicubo[minicubo.variable == 'valor']

minicubo[minicubo.variable == 'valor'].set_index('value').plot.bar(subplots = True, sharex = False, figsize = (12,12))
plt.tight_layout();

c:\Users\sgarciam\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\core\indexes\base.py:6982: FutureWarning:

In a future version, the Index constructor will not infer numeric dtypes when passed object-dtype sequences (matching Series behavior)

The analysis could be improved because in F and M the outliers cause most of the data to be concentrated in category 1.

What should be done is eliminate those atypical ones and redo the exercise.

I leave it to you as a practice task.

But with this analysis we are able to identify the customers who are most likely to respond best to new campaigns, as well as gain a lot of valuable insights for the business.

Understanding the products

We are going to create a dataframe at the product level to be able to analyze this dimension.

We first calculate the counts of each event in each product.

prod = df.groupby(['producto','evento']).size()
prod

producto  evento          
3752      view                 10
3762      cart                127
          purchase             28
          remove_from_cart     59
          view                258
                             ... 
5932538   view                  1
5932540   cart                  1
          view                  2
5932578   view                  1
5932585   view                  2
Length: 137068, dtype: int64

prod  = prod.unstack(level = 1).fillna(0)
prod

We are going to incorporate the price, for this we first create a price master by product.

maestro_precios = df.groupby('producto', as_index = False).precio.mean()
maestro_precios

prod = pd.merge(left = prod, right = maestro_precios, how = 'left', on = 'producto')
prod

We rearrange the names.

prod

prod = prod[['producto','view','cart','remove_from_cart','purchase','precio']]
prod

What are the most sold products?

prod.sort_values('purchase',ascending = False)[0:20]

Possibly we would be able to increase sales and the average ticket simply by highlighting these products in the store.

Are there products that are not being sold and could be removed from the catalogue?

prod[prod.purchase == 0]

INSIGHT #8: Almost half of the products have not had any sales in the 5 months of history.

A whole new analysis could be started on these products:

• They are not visible?
• Are they seen but not bought?
• Is it because they are replaced by other own products?
• Is it because they are much cheaper in the competition?
• Etc

They could be removed from the catalog, or at least from the store, newsletter, etc., so that they do not take up space from the products that are sold.

What is the relationship between price and sales volume?

Since this analysis includes sales we will eliminate the products that have not had any.

sns.scatterplot(data = prod[prod.purchase > 0], x = 'precio', y = 'purchase', hue = 'precio');

Yes, there is a clear decreasing relationship.

Let's zoom in for example below €50 to understand it better.

sns.scatterplot(data = prod[(prod.purchase > 0) & (prod.precio < 50)], x = 'precio', y = 'purchase', hue = 'precio');

Are there products that customers regret and remove more from the cart?

prod.insert(loc = 4,
            column = 'remove_from_cart_porc',
            value = prod.remove_from_cart / prod.cart *100 )
prod

prod.loc[prod.cart > 30].sort_values('remove_from_cart_porc', ascending = False)[0:30]

It would be necessary to see why these products are removed more times than they are added:

• If the reason makes sense: review what happens with these products (other alternative products, etc.)
• If it does not have it, delete these records and analyze only those with remove_from_cart_perc less than or equal to 100

What are the most viewed products?

prod.view.sort_values(ascending = False)[0:20].plot.bar();

Possibly we would be able to increase sales and the average ticket simply by highlighting these products in the store.

Provided that in addition to being seen they are also sold.

Are there products wanted but not purchased?

For example, products that many customers look at but then do not buy.

If we found them, we would have to check what happens to them.

sns.scatterplot(data = prod, x = 'view', y = 'purchase');

We're going to remove the outlier and zoom into the many views few purchases window.

sns.scatterplot(data = prod.loc[prod.view < 4000], x = 'view', y = 'purchase', hue = 'precio')
plt.xlim(1000,3000)
plt.ylim(0,150)

(0.0, 150.0)

There is an opportunity with these products, because for some reason they generate the interest of the clients, but in the end they do not buy them.

It would be necessary to do an analysis on them.

Building a recommendation system

One of the assets that can most increase the sales of an ecommerce is a recommendation system.

We could already apply a basic one with the analysis of the most viewed and the best sold previously carried out.

But the real power comes when we create a recommender that personalizes for each purchase.

Types of recommender systems:

• Collaborative filtering:
  • Item based
  • User based
• From content

In our case we are going to develop one with collaborative filtering based on items.

The steps to follow are:

1. Create the dataframe with the kpi of interest
2. Reduce dimension (optional)
3. Select a distance metric
4. Compute the item-item matrix
5. Create the prioritization logic

Create the dataframe with the kpi of interest

In this case we will use what is called an implicit kpi, which will be the number of times that the products have been purchased by the same user.

Explicit Kpis would be, for example, stars or scores from 1 to 10.

Since this is an algorithm that takes time to calculate, we are going to reduce the problem and calculate it only for the 100 best-selling products.

First we calculate a master with the top 100 best-selling products.

mas_vendidos = prod.sort_values('purchase', ascending = False).producto[0:100]
mas_vendidos

16807    5809910
28178    5854897
6644     5700037
314         5304
9900     5751422
          ...   
30395    5862564
9778     5749720
9732     5749149
22751    5835859
22116    5833335
Name: producto, Length: 100, dtype: int64

We create a temporary dataframe filtering by these products.

temp = df.loc[df.producto.isin(mas_vendidos)]
temp

We create the user-item array.

usuario_item = temp.loc[temp.evento == 'purchase'].groupby(['usuario','producto']).size().unstack(level = 1).fillna(0)
usuario_item

Select a distance metric

Most common metrics:

• Euclidean distance
• Correlation
• cosine

In this case we are going to take, for example, the Euclidean distance.

We operationalize it using Scipy's spatial.distance.euclidean function.

from scipy import spatial

Calculate item-item array

We create the recommender that takes as input a user-item array and returns an item-item array with the Euclidean distance as data.

def recomendador(dataframe):

    def distancia(producto):
        return(dataframe.apply(lambda x: spatial.distance.euclidean(x,producto)))

    return(dataframe.apply(lambda x: distancia(x)))

item_item = recomendador(usuario_item)
item_item

Create the prioritization logic

We already have the recommender ready.

What we would have to do is a call to this table every time a user looks at a product or puts it in the cart.

But to make it more effective we could use all the information accumulated from the session or even from the entire user if he is logged in.

That means we need a system to recommend products whether the input is from a single product or multiple ones.

And that at the same time returns several recommendations, to cover all the "gaps" of recommendation that our web could have.

We will apply a very simple algorithm that will do:

1. Create an array with the input products to extract their vectors from the item-item matrix
2. Calculate the sum of distances of all products
3. Remove themselves so as not to self-recommend.
4. Return the 10 with the least distance

#En el caso de varios productos vendrá del servidor web como una cadena separada con punto y coma
def priorizador(productos,devuelve = 10):
    #crear array con productos de entrada
    array = np.int64(productos.split(';'))

    #extraer sus vectores de la matriz total
    matriz = item_item[array]

    #calcular la suma de distancias
    suma_distancias = matriz.agg(sum,axis = 1)

    #eliminar los productos input
    suma_distancias = suma_distancias.loc[~suma_distancias.index.isin(list(array))]

    #Devolver los 10 con menor distancia
    return(suma_distancias.sort_values()[0:devuelve])

We check how it works if we pass it a product

priorizador('4497')

producto
5724230             14.39
4600                14.42
5550302             14.49
4768                14.49
5749149             14.56
5833318             14.63
5824810             14.70
5835859             14.70
5809303             14.73
5833335             14.73
dtype: float64

We check how it works if we pass several products to it

priorizador('4497;4600;4768')

producto
5749149             40.25
5833318             40.47
5833335             40.81
5809303             40.81
5724230             41.00
5824810             41.08
5835859             41.23
5550302             41.47
5816169             41.51
5844894             41.55
dtype: float64

CONCLUSIONS

The current trend is flat across all metrics, confirming the need for CRO stocks.

After the analysis carried out on the transactional data, a CRO plan has been developed with 12 specific initiatives organized into 5 major business levers that with a high probability will increase the baselines, achieving a global increase in ecommerce income.

Baseline

In each session, on average:

• KPIs per session: 2.2 products are viewed
• KPIs per session: 1.3 products are added to the cart
• KPIs per session: 0.9 products are removed from the cart
• KPIs per session: 0.3 products are purchased
• Cross-selling: median of 5 products per purchase
• Recurrence: 10% of customers buy again after the first month
• Conversion: 60% add to cart on views
• Conversion: 22% of purchase on additions to cart
• Conversion: 13% purchase on views
• Average monthly turnover: €125,000

Actions to increase views

1. Review paid campaigns (generation and retargeting) to concentrate investment in slots between 9 a.m. and 1 p.m. and between 6 p.m. and 8 p.m.
2. Concentrate the investment of the Christmas and post-Christmas period in the week of Black Friday
3. Increase the investment until reaching the maximum CPA based on the LTV that we have identified

Conversion increase actions

4. Preconfigure the home page with the products identified in the most viewed and most sold analysis.
5. Work on products with high cart abandonment rates
6. Work on products that are highly viewed but rarely purchased

Increase cross-sell actions

7. The median purchase includes 5 products
8. Increase this ratio by recommending in real time with the new recommender

Actions to increase purchase frequency

9. 90% of customers only make one purchase
10. Create a regular newsletter with the new recommender to increase the frequency of visits
11. Promotional campaigns on the top segments of the RFM segmentation

Customer loyalty actions

12. Create a loyalty program segmented by the new RFM segmentation