<jupyter_start><jupyter_text>Customer Segmentation and Sales Forecasting  Methodology  1. Getting the data<jupyter_code>import pandas as pd
import numpy as np
import matplotlib.pyplot as plt 
import seaborn as sns
import datetime as dt
from sklearn.model_selection import train_test_split, cross_val_score
from sklearn.preprocessing import StandardScaler
from sklearn.ensemble import RandomForestRegressor
from sklearn.tree import DecisionTreeRegressor
from sklearn.linear_model import LinearRegression
from sklearn.metrics import mean_squared_error
import warnings
warnings.filterwarnings("ignore")
def clean_columns(df):
    df.columns = df.columns.str.lower().str.replace(' ', '_') # lower case column names
    df.rename(columns={'año':'anho'}, inplace=True) # replace odd characters
    df['date'] = pd.to_datetime(df['anho'].astype(str).str.cat(df[['mes', 'dia']].astype(str), sep='-')) # create a datetime column for plotting
    return df
# read in the data
data = pd.read_excel('Ejercicio.xls')
clean_columns(data)
data.head()<jupyter_output><empty_output><jupyter_text>2. Exploratory Data Analysis<jupyter_code># checking for missing values
data.isnull().sum()
# descriptive statistics for each variable
data.describe()
# gross margin 2004-2012
data.plot(kind='line', x='date', y='margen_bruto', alpha=0.5, figsize=(10,5), title='Margen Bruto 2004-2012')
plt.ylabel('margen_bruto')
plt.show()
# sales 2004-2012
data.plot(kind='line', x='date', y='ventas', alpha=0.5, figsize=(10,5), title='Ventas 2004-2012')
plt.ylabel('ventas')
plt.show()
# top 10 costumers by gross margin
data.groupby('id_cliente')['margen_bruto'].sum().sort_values(ascending=False)[:10].plot(kind='barh')
plt.xlabel('ventas')
plt.show()
# top 10 costumers by sales
data.groupby('id_cliente')['ventas'].sum().sort_values(ascending=False)[:10].plot(kind='barh')
plt.xlabel('ventas')
plt.show()
# checking for correlation
corr_matrix = data.corr()
corr_matrix["ventas"].sort_values(ascending=False)
# checking for correlation
from pandas.plotting import scatter_matrix
attributes = ['lista_precio', 'ventas', 'margen_bruto']
scatter_matrix(data[attributes], figsize=(10, 8))
plt.show()
print('Number of unique transactions: ', data['transaccion_id'].nunique())
print('Number of unique customers: ', data['id_cliente'].nunique())
print('Number of unique products: ', data['producto_id'].nunique())
print('Number of unique serial number: ', data['num_serie'].nunique())
print('Number of unique vendors: ', data['vendedor_id'].nunique())<jupyter_output>Number of unique transactions:  473
Number of unique customers:  157
Number of unique products:  5
Number of unique serial number:  653
Number of unique vendors:  37<jupyter_text>3. RFM Model **RFM** (Recency, Frequency, Monetary) Model is a behavior-based approach grouping customers into segments, using past purchase behavior to divide customers into groups. RFM helps divide customers into various categories or clusters to identify customers who are more likely to respond to promotions and also for future personalization services.- Recency (R): Who have purchased recently? Number of days since last purchase.- Frequency (F): Who has purchased frequently? It means the total number of purchases.- Monetary Value (M): Who have high purchase amount? It means the total money customer spent.<jupyter_code># first and last purchase
data['date'].min(),data['date'].max()
# date reference to calculate recency
now = dt.datetime(2012,1,1)
print(now)
# calculating recency, frequency and monetary
rfm = data.groupby('id_cliente').agg({'date': lambda date:(now - date.max()).days,
                                     'transaccion_id': lambda num: len(num),
                                    'margen_bruto': lambda num: num.sum()})

rfm.columns = ['recency', 'frequency', 'monetary']
rfm.head()<jupyter_output><empty_output><jupyter_text>The simplest way to create customers segments from RFM Model is to use **Quartiles**. We assign a score from 1 to 4 to Recency, Frequency and Monetary. Four is the best/highest value, and one is the lowest/worst value. A final RFM score is calculated simply by combining individual RFM score numbers.<jupyter_code># calculating quartiles
rfm['r_quartile'] = pd.qcut(rfm['recency'], 4, ['4', '3', '2', '1'])
rfm['f_quartile'] = pd.qcut(rfm['frequency'], 4, ['1', '2', '3', '4'])
rfm['m_quartile'] = pd.qcut(rfm['monetary'], 4, ['1', '2', '3', '4'])
rfm.head()
# creating rfm score
rfm['rfm_score'] = rfm['r_quartile'].astype(str)+ rfm['f_quartile'].astype(str) + rfm['m_quartile'].astype(str)
rfm.head()<jupyter_output><empty_output><jupyter_text>Best Recency score = 4: most recently purchase. Best Frequency score = 4: most quantity purchase. Best Monetary score = 4: spent the most<jupyter_code># best customers in terms of rfm score
rfm[rfm['rfm_score'] == '444'].sort_values('monetary', ascending=False).head()
print("Best Customers: ",len(rfm[rfm['rfm_score']=='444']))
print('Loyal Customers: ',len(rfm[rfm['f_quartile']=='4']))
print("Big Spenders: ",len(rfm[rfm['m_quartile']=='4']))
print('Almost Lost: ', len(rfm[rfm['rfm_score']=='244']))
print('Lost Best Customers: ',len(rfm[(rfm['rfm_score']=='144') | (rfm['rfm_score']=='143') | (rfm['rfm_score']=='134') | (rfm['rfm_score']=='133')]))
print('Lost Cheap Customers: ',len(rfm[rfm['rfm_score']=='111']))<jupyter_output>Best Customers:  23
Loyal Customers:  34
Big Spenders:  39
Almost Lost:  2
Lost Best Customers:  5
Lost Cheap Customers:  20<jupyter_text>4. Predictive Model  4.1 Pre-Processing Data<jupyter_code># we'll use data from 2007 to 2011
df = data[data['anho'] >= 2007]
df.plot(kind='line', x='date', y='ventas', alpha=0.5, figsize=(10,5))
plt.show()
features = ['anho', 'mes', 'dia', 'vendedor_id','producto_id','id_cliente', 'margen_bruto']

# define X and y
X = df[features].values
y = df['ventas'].values
# scale all numeric features
scaler = StandardScaler()
X = scaler.fit_transform(X.astype(np.float))
# split dataframe into training and test set
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.3, random_state=42)<jupyter_output><empty_output><jupyter_text>4.2. Predictive models For this problem I will try 3 different models:- Linear Regression- Decision Tree- Random ForestLet's evaluate them:<jupyter_code>seed=42

# define models
models = {'Linear Regression': LinearRegression(),
          'Decision Tree':DecisionTreeRegressor(random_state=seed),
          'Random Forest':RandomForestRegressor(n_jobs=-1, n_estimators=10, random_state=seed)}

y_pred = pd.DataFrame(columns=models.keys())
results = []

# train, predict and evaluate each model
for model in models.keys():  
    scores = cross_val_score(models[model], X_train, y_train, scoring="neg_mean_squared_error", cv=10)
    rmse = np.sqrt(-scores)
    results.append({'Rmse': rmse.mean(),
                    'Model': str(model)})
models_results = pd.DataFrame(results)
models_results.sort_values('Rmse', inplace=True)
models_results = models_results.set_index('Model')
models_results<jupyter_output><empty_output><jupyter_text>**Random Forest** is the best model in terms of RMSE  4.3. Tuning the model<jupyter_code>from sklearn.model_selection import GridSearchCV

param_grid = [
    {'n_estimators': [200, 500, 1000], 'max_features': [2, 3, 4, 5, 6, 7]},
]

forest_reg = RandomForestRegressor(n_jobs=-1, random_state=seed)
grid_search = GridSearchCV(forest_reg, param_grid, cv=10,
                           scoring='neg_mean_squared_error', return_train_score=True)
grid_search.fit(X_train, y_train)<jupyter_output><empty_output><jupyter_text>The best hyperparameter combination found:<jupyter_code>grid_search.best_params_
feature_importances = grid_search.best_estimator_.feature_importances_
sorted(zip(feature_importances, features), reverse=True)
final_model = grid_search.best_estimator_

final_predictions = final_model.predict(X_test)

final_mse = mean_squared_error(y_test, final_predictions)
print('final_rmse:', np.sqrt(final_mse))<jupyter_output>final_rmse: 194.12548380470076<jupyter_text>95% confidence interval for the test RMSE:<jupyter_code>from scipy import stats
confidence = 0.95
squared_errors = (final_predictions - y_test) ** 2
mean = squared_errors.mean()
m = len(squared_errors)

np.sqrt(stats.t.interval(confidence, m - 1,
                         loc=np.mean(squared_errors),
                         scale=stats.sem(squared_errors)))<jupyter_output><empty_output><jupyter_text>4.4. Making predictions<jupyter_code>dataTesting = pd.read_excel('test_data.xls')
clean_columns(dataTesting)
dataTesting.head()
# scaling features
test = dataTesting.drop('date', axis=1).values
test = scaler.transform(test.astype(np.float))
dataTesting['ventas'] = final_model.predict(test)
dataTesting.head()
fig = plt.figure(figsize=(10,5))

for frame in [df, dataTesting]:
    plt.plot(frame['date'], frame['ventas'])
plt.show()
ventas = dataTesting.groupby('id_cliente').agg({'ventas':np.sum})
ventas.sort_values('ventas', ascending=False).plot(kind='bar', sort_columns=True, figsize=(10,5))
plt.show()
# top 10 customers during the next two years, representing 53% of total sales
ventas_pct = dataTesting.groupby('id_cliente').agg({'ventas':np.sum}).apply(lambda x:((x / x.sum())*100).round(1))
ventas_pct.sort_values('ventas', ascending=False).head(10)
ventas_pct.sort_values('ventas', ascending=False).head(10).sum()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1439668014471164, 'fraction_numerical': 0.024473292189827623, 'mean_word_length': 6.337236533957846, 'pattern_counts': {'":': 0, '<': 42, '<?xml version=': 0, '>': 43, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '422121', 'license': 'Apache-2.0', 'n_tokens_mistral': 3347, 'n_tokens_neox': 3078, 'n_words': 753, 'path': '05_CustomerSegmentation&SalesForecasting.ipynb', 'repo_name': 'jorgelmadrid/projects', 'size': '9398'}