<jupyter_start><jupyter_text>Marketing Analytics <NAME>, <NAME>, <NAME>, <NAME>, <NAME><jupyter_code>import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns

import missingno as msno
PATH = "/Users/giacomomiolo/Downloads/Marketing analytics/Exam/"
df = pd.read_excel(PATH + "BABD_DB for group assignment_CRM.xlsx", sheet_name="Business Game")
import re
from functools import wraps

def print_return_type(func):
    @wraps(func)
    def wrapper(*args, **kwargs):
        try:
            result = func(*args, **kwargs)
        finally:
            print(f'Function {func.__name__} returned type {type(result)}.')
            return result
    return wrapper

def clean_str(string):
    '''
    Removes special characters and double spaces from a string, preserving single spaces between words.
    
    Args:
        string (str): the dirty string to clean.
    
    Returns:
        cleaned (str)
    '''
    non_word_digit = re.compile(r"[^a-zA-Z0-9 ]+") # Leaving spaces, removing all non letter/digit (add \- to leave - char as well)
    multiple_spaces = re.compile(r"( {1,})") # Catching all repeated spaces
    start_end_spaces = re.compile(r"(\A\s|\s\Z)") # Removing spaces at start/end
    step_1 = re.sub(non_word_digit, "", string)
    step_2 = re.sub(multiple_spaces, "_", step_1)
    cleaned = re.sub(start_end_spaces, "", step_2)
    
    return cleaned

def describe_col(df, col):
    
    '''Takes as input a dataframe and the name of the column you want to describe.
    Returns the unique values, eventual missing values count,
    the absolute frequencies and the relative frequencies of unique values.'''
    
    # Number, count and percentage of unique values in the column
    print(f'{col} - {df[col].nunique()} unique values.')
    count = df[col].value_counts(sort=True, dropna=True)
    perc = df[col].value_counts(normalize=True, sort=True, ascending=False, dropna=True)*100
    d = pd.concat([perc, count], axis=1, keys=['Percentage %', 'Count'])
    
    # dtype of the column
    print(f"dtype: {df[col].dtype}.")
    
    # Number of missing values, if any
    missing_count = df[col].isnull().sum()
    if missing_count != 0:
        total = len(df[col])
        missing_perc = (missing_count/total)*100
        
        print(f'! Missing values: perc - {missing_perc:0.3f}% count - {missing_count}')
    else:
        print(f'No missing values.\n')
    

    return print(d)



def num2quantile(df, col, num1, num2='<', ID='ID', verbose=False):
    '''
    Returns the corresponding quantile of a numeric value of a column.
    '''
    
    if type(num2) == str:
        if num2 == '<':
            quant = df.loc[df[col] < num1, ID].count() / df.shape[0] * 100
        elif num2 == '>':
            quant = df.loc[df[col] > num1, ID].count() / df.shape[0] * 100
    else:
        quant = df.loc[(df[col]>=num1) & (df[col]<num2), ID].count() / df.shape[0] * 100
    if verbose:
        print(f'{quant:0.2f}%')
    return quant


def ranges(input_df, col, name, tuples, last):
    '''
    Creates a new column with labels of the corresponding range
    
    Args:
        input_df (pandas.core.frame.DataFrame): Dataframe to modify.
        col (str): Column with numeric values to use to create ranges.
        name (str): Name of the column to be created with the labels.
        tuples (list): List of tuples with values and labels. Ex. [(100, 'Low'), (400, 'Medium')]
    '''
    df = input_df.copy()
    counter = 0
    df[name] = last
    df['Order'] = len(tuples)+1
    for i in range(len(tuples)):
        if i == 0:
            #print(f"{tuples[i][0]} - {tuples[i][1]}")
            df.loc[df[col] < tuples[i][0], name] = tuples[i][1]
            df.loc[df[col] < tuples[i][0], 'Order'] = i
        else:
            #print(f"{tuples[i][0]} - {tuples[i][1]}")
            df.loc[(df[col] >= tuples[i-1][0]) & (df[col] < tuples[i][0]), name] = tuples[i][1]
            df.loc[(df[col] >= tuples[i-1][0]) & (df[col] < tuples[i][0]), 'Order'] = i
    return df



def n_outl(df, col_name, method='norm', n_std=2, detect=False):
    '''
    df (pandas.core.frame.DataFrame): Dataframe.
    col_name (str): Numeric column to be consider.
    method (str, optional): Accepts 'IQR' for interquartile range, defaults to Normal Distribution.
    n_std (int, optional): Number of standard deviations to consider, defaults to 2.
    '''
    if method.lower()=='norm':
        mean = df[col_name].mean()
        sd = df[col_name].std()
        
        # keep only the ones that are within +x to -x standard deviations in the column
        boolean_mask = (np.abs(df[col_name].values - mean) > n_std*sd)
        

    elif method.lower()=='iqr':
    
        q1 = np.quantile(df[col_name], 0.25)
        q3 = np.quantile(df[col_name], 0.75)
        iqr = q3-q1
        th1 = q1 - 1.5*iqr
        th3 = q3 + 1.5*iqr

        boolean_mask = ( (df[col_name] > th3) | (df[col_name] < th1) ) 
    
    
    if detect:
        df_outliers = df[boolean_mask]
        return df_outliers

    outliers = len(df[boolean_mask])
    return outliers

def get_stats(df, group, inputs, order=False, outlier=True, n_std=2):
    '''
    df (pandas.core.frame.DataFrame): Dataframe containing all the columns of interest.
    group (str): Name of the column for which to group by.
    inputs (str, list): If list (max 2 elements), first element is the part, the second is the total.
    order(bool, optional): If there is a column to consider for ordering.
    outlier (bool, optional): Defaults to True, detects outliers.
    n_std (int, optional): Defaults to 2, number of standard deviations to classify outliers.
    '''

    if type(inputs) == list:
    	# Total
        col_tot = inputs[1]
        str_tot = col_tot.split("_")[-1]
        # Part
        col_stats = inputs[0]
        str_stats = col_stats.split("_")[-1]
    elif type(inputs) == str:
        col_stats = inputs
        str_stats = col_stats.split("_")[-1]

    stats = pd.DataFrame()
    stats['Percentage'] = np.round(df[group].value_counts(normalize = True, sort = True, ascending=False, dropna=True)*100, 2)
    stats['Count'] = df[group].value_counts(sort=True, dropna=True)
    stats.reset_index(inplace=True)
    stats.rename(columns={'index': group}, inplace=True)
    stats = stats.merge(df.groupby(group, as_index=False)[col_stats].min().rename(columns={col_stats: 'Sales_Min'}), on=group)
    stats = stats.merge(df.groupby(group, as_index=False)[col_stats].mean().rename(columns={col_stats: 'Sales_Average'}), on=group)
    stats = stats.merge(df.groupby(group, as_index=False)[col_stats].median().rename(columns={col_stats: 'Sales_Median'}), on=group)
    stats = stats.merge(df.groupby(group, as_index=False)[col_stats].max().rename(columns={col_stats: 'Sales_Max'}), on=group)
    stats = stats.merge(df.groupby(group, as_index=False)[col_stats].sum().rename(columns={col_stats: 'Sales_Total'}), on=group)
    
    stats['Sales_Perc_of_' + str_stats] = stats['Sales_Total'] / np.sum(stats['Sales_Total'])

    if type(inputs) == list:

        stats['Sales_Perc_of_' + str_tot] = stats['Sales_Total'] / np.sum(df[col_tot])
        stats['Sales_Perc_of_' + str_tot] = np.round(stats['Sales_Perc_of_' + str_tot] * 100, 2)

   

    if outlier:
        d = {}
        for i in [*stats[group]]:
            this_mask = df[group] == i
            n = n_outl(df[this_mask], col_stats, n_std=n_std)
            d[i] = n
        
        d_df = pd.DataFrame.from_dict(d, orient='index')
        d_df.reset_index(inplace=True)
        d_df.columns = [group, 'Outlier_Count']
        stats = stats.merge(d_df, on=group)
        stats['Outlier_Perc'] = np.round(stats['Outlier_Count'] / stats['Count'] * 100, 2)

    if order:
        # Ordering
        stats = stats.merge(df.loc[:,[group, 'Order']].drop_duplicates(), on=group).sort_values('Order', ascending=True)
        stats.set_index('Order', inplace=True)
        stats.index.rename('', inplace=True)
    
    # Formatting
    
    stats['Sales_Min'] = stats['Sales_Min'].astype(int)
    stats['Sales_Average'] = stats['Sales_Average'].astype(int)
    stats['Sales_Median'] = stats['Sales_Median'].astype(int)
    stats['Sales_Max'] = stats['Sales_Max'].astype(int)
    stats['Sales_Total'] = stats['Sales_Total'].astype(int)
    stats['Sales_Perc_of_' + str_stats] = np.round(stats['Sales_Perc_of_' + str_stats] * 100, 2)
    
    return stats
new_cols = ['ID',
 'Channel_2017',
 'Sales_2017_Total_Omnichannel',
 'Sales_2017_in_Store',
 'Sales_2017_Online',
 'Sales_2018_Total_Omnichannel',
 'Loyalty_Status_2017_in_Store',
 'Loyalty_Status_2017_Online',
 'Loyalty_Status_2017_Total_Omnichannel',
 'Loyalty_Status_2018_Total_Omnichannel',
 'Country_Store',
 'Country_Customer',
 'Complaint',
 'Complaint_Channel',
 'Complaint_Reason']
df.columns = new_cols
pd.DataFrame([df.isna().sum().values,
              np.round(df.isna().sum().values/len(df)*100,0)], index=['Count','Percentage'], columns=df.columns).T
msno.matrix(df)
plt.show()
print("Number of records:",df.shape[0],"\n"+
      "Number of unique customer ID:",df.ID.unique().shape[0]
     )<jupyter_output>Number of records: 48526 
Number of unique customer ID: 48526<jupyter_text>As many records as customer ID. Under the hypothesis that a customer ID belongs to one person, there're no duplicates in the dataset.  Addding Domestic Foreign Column (valid only for stores)<jupyter_code>describe_col(df, 'Channel_2017')
domestic_mask = df['Country_Store'] == df['Country_Customer']
df['Domestic_Foreign'] = domestic_mask
df['Domestic_Foreign'].replace(to_replace=[True,False], value=['Domestic','Foreign'], inplace=True)
df.loc[df['Channel_2017'] == 'Online', 'Domestic_Foreign'] = np.nan<jupyter_output><empty_output><jupyter_text>Exploratory Data Analysis<jupyter_code>describe_col(df,'Loyalty_Status_2017_Total_Omnichannel')
describe_col(df,'Country_Customer')
describe_col(df, 'Country_Store')<jupyter_output>Country_Store - 4 unique values.
dtype: object.
! Missing values: perc - 16.389% count - 7953
        Percentage %  Count
France     28.543613  11581
Italy      27.464077  11143
US         23.205087   9415
China      20.787223   8434<jupyter_text>Segmentation 2017 Omnichannel<jupyter_code>spending = [80, 200, 600, 2000, 10000]
labels = ['Very Low', 'Low', 'Medium', 'High', 'Very High']
last_label = 'Top Clients'
tuples = [*zip(spending, labels)]
asc_labels = labels+[last_label]
desc_labels = [asc_labels[i] for i in range(len(asc_labels)-1,-1,-1)]

df_omni = ranges(df[df['Sales_2017_Total_Omnichannel'].notna()], 'Sales_2017_Total_Omnichannel', 'Spending_Ranges_2017', tuples, last_label)
stats_omni = get_stats(df_omni, 'Spending_Ranges_2017', 'Sales_2017_Total_Omnichannel', n_std=3)
stats_omni<jupyter_output><empty_output><jupyter_text>Outliers of Top Clients - Omnichannel<jupyter_code>stellar_clients_omni = n_outl(df_omni[df_omni["Spending_Ranges_2017"]=="Top Clients"], 'Sales_2017_Total_Omnichannel', n_std=3, detect=True)
print(f"Number of most important clients: {stellar_clients_omni.shape[0]}")
print(f"In 2018, {stellar_clients_omni[stellar_clients_omni['Loyalty_Status_2018_Total_Omnichannel'] == 'Lost'].shape[0]} of them were lost.")
stellar_clients_omni<jupyter_output>Number of most important clients: 15
In 2018, 10 of them were lost.<jupyter_text>Segmentation 2017 Store<jupyter_code>spending = [80, 200, 600, 2000, 10000]
labels = ['Very Low', 'Low', 'Medium', 'High', 'Very High']
last_label = 'Top Clients'
tuples = [*zip(spending, labels)]

df_store = ranges(df[df['Sales_2017_in_Store'].notna()], 'Sales_2017_in_Store', 'Spending_Ranges_2017', tuples, last_label)
stats_store = get_stats(df_store, 'Spending_Ranges_2017', 'Sales_2017_in_Store', n_std=3)
stats_store<jupyter_output><empty_output><jupyter_text>Outliers Top Clients<jupyter_code>n_outl(df_store[df_store["Spending_Ranges_2017"]=="Top Clients"], 'Sales_2017_in_Store', n_std=3, detect=True)<jupyter_output><empty_output><jupyter_text>Segmentation 2017 Online<jupyter_code>spending = [80, 200, 600, 2000, 10000]
labels = ['Very Low', 'Low', 'Medium', 'High', 'Very High']
last_label = 'Top Clients'
tuples = [*zip(spending, labels)]

df_online = ranges(df[df['Sales_2017_Online'].notna()], 'Sales_2017_in_Store', 'Spending_Ranges_2017', tuples, last_label)
stats_online = get_stats(df_online, 'Spending_Ranges_2017', 'Sales_2017_Online', n_std=3)
stats_online<jupyter_output><empty_output><jupyter_text>Store Analysis  Spending per Country, in stores - 2017<jupyter_code>country_stats_store = get_stats(df_store, 'Country_Store', 'Sales_2017_in_Store', n_std=6)
country_stats_store
top_clients_store_fr = n_outl(df_store[(df_store["Country_Store"]=="France")], 'Sales_2017_in_Store', n_std=6, detect=True)
print(f"Number of most important clients in stores in France: {top_clients_store_fr.shape[0]} - Spending range: {top_clients_store_fr['Sales_2017_in_Store'].min():0.0f} - {top_clients_store_fr['Sales_2017_in_Store'].max():0.0f}")

top_clients_store_it = n_outl(df_store[df_store["Country_Store"]=="Italy"], 'Sales_2017_in_Store', n_std=6, detect=True)
print(f"Number of most important clients in stores in Italy: {top_clients_store_it.shape[0]} - Spending range: {top_clients_store_it['Sales_2017_in_Store'].min():0.0f} - {top_clients_store_it['Sales_2017_in_Store'].max():0.0f}")

top_clients_store_us = n_outl(df_store[df_store["Country_Store"]=="US"], 'Sales_2017_in_Store', n_std=6, detect=True)
print(f"Number of most important clients in stores in US: {top_clients_store_us.shape[0]} - Spending range: {top_clients_store_us['Sales_2017_in_Store'].min():0.0f} - {top_clients_store_us['Sales_2017_in_Store'].max():0.0f}")

top_clients_store_ch = n_outl(df_store[df_store["Country_Store"]=="China"], 'Sales_2017_in_Store', n_std=6, detect=True)
print(f"Number of most important clients in stores in China: {top_clients_store_ch.shape[0]} - Spending range: {top_clients_store_ch['Sales_2017_in_Store'].min():0.0f} - {top_clients_store_ch['Sales_2017_in_Store'].max():0.0f}")<jupyter_output>Number of most important clients in stores in France: 54 - Spending range: 17155 - 74291
Number of most important clients in stores in Italy: 61 - Spending range: 15278 - 81102
Number of most important clients in stores in US: 58 - Spending range: 12445 - 49272
Number of most important clients in stores in China: 47 - Spending range: 15943 - 70226<jupyter_text>Customer segmentation by spending ranges in Stores - 2017<jupyter_code>tier_country_store = pd.crosstab(df_omni.Country_Store, df_omni.Spending_Ranges_2017, normalize='columns').apply(lambda x: np.round(x*100,2))
tier_country_store[asc_labels]<jupyter_output><empty_output><jupyter_text>Customer segmentation by domestic/foreign in Stores - 2017<jupyter_code>dom_for_country_store = pd.crosstab(df_omni.Country_Store, df_omni.Domestic_Foreign, normalize='index').apply(lambda x: np.round(x*100,2))
dom_for_country_store<jupyter_output><empty_output><jupyter_text>* **We find between 70% and 75% of Foreign customers who buy in China, France and Italy, while customers purchasing in US are pretty balanced (around 50%) between Foreign and Domestic.**  Customer segmentation by customer country in Stores - 2017<jupyter_code>country_customer_country_store = pd.crosstab(df_omni.Country_Store, df_omni.Country_Customer, normalize='index').apply(lambda x: np.round(x*100,2))
country_customer_country_store<jupyter_output><empty_output><jupyter_text>Customer Analysis  **Do MPC customers prefer to buy in their country or abroad?**<jupyter_code>describe_col(df_omni, 'Domestic_Foreign')<jupyter_output>Domestic_Foreign - 2 unique values.
dtype: object.
! Missing values: perc - 16.389% count - 7953
          Percentage %  Count
Foreign      68.035393  27604
Domestic     31.964607  12969<jupyter_text>* **MPC customers perfer to buy abroad. There are 68% (27,604) Foreign customers and 32% (12,969) of Domestic customers.**  Which country generates the most sales?<jupyter_code>country_customer_for_spending = get_stats(df_omni, 'Country_Customer', 'Sales_2017_Total_Omnichannel', order=False, outlier=True, n_std=3)
country_customer_for_spending = country_customer_for_spending.merge(pd.crosstab(df_omni.Country_Customer, df_omni.Spending_Ranges_2017)['Top Clients'].reset_index(), on='Country_Customer')
country_customer_for_spending.sort_values('Sales_Perc_of_Omnichannel', ascending=False)<jupyter_output><empty_output><jupyter_text>* **Customers from China generate the most sales, 24.5% (10.6 million), followed by customers from Russia, who generate 22% (9.6 million) of total sales for the year 2017.**  The Mix Domestic and Foreign customers for each spending tier<jupyter_code>dom_for_spending = pd.crosstab(df_omni.Domestic_Foreign, df_omni.Spending_Ranges_2017, normalize='index').apply(lambda x: np.round(x*100,2))
dom_for_spending[asc_labels]<jupyter_output><empty_output><jupyter_text>* **Here we segment Domestic and Foreign customers by Spending Range. We can notice that there are no great differences between  Domestic and Foreign customers percentages in any spending tier.**  What is the distribution of customers based on their spending?<jupyter_code>describe_col(df_omni, 'Spending_Ranges_2017')<jupyter_output>Spending_Ranges_2017 - 6 unique values.
dtype: object.
No missing values.

             Percentage %  Count
Medium          35.731361  17339
Low             21.256646  10315
High            20.887772  10136
Very Low        12.786960   6205
Very High        8.189424   3974
Top Clients      1.147838    557<jupyter_text>Loyalty and Spending, 2017<jupyter_code>get_stats(df_omni, 'Loyalty_Status_2017_Total_Omnichannel', 'Sales_2017_Total_Omnichannel', order=False, outlier=True, n_std=3)<jupyter_output><empty_output><jupyter_text>* **New and Loyal customers purchasing at Omnichannel level are equally distributed*** **Loyal customers purchasing at Omnichannel level account for 62% of Total Sales in 2017.**<jupyter_code>get_stats(df_store, 'Loyalty_Status_2017_in_Store', 'Sales_2017_in_Store', order=False, outlier=True, n_std=3)
get_stats(df_online, 'Loyalty_Status_2017_Online', 'Sales_2017_Online', order=False, outlier=True, n_std=3)<jupyter_output><empty_output><jupyter_text>* **New customers purchasing only Online represent 68% of the customer base and of Online Sales in 2017.**  Loyalty per Country, 2017<jupyter_code>country_customer_loyalty = pd.crosstab(df_omni.Country_Customer, df_omni.Loyalty_Status_2017_Total_Omnichannel, normalize='index').apply(lambda x: np.round(x*100,2))
country_customer_loyalty
sns.set_style("whitegrid")
sns.set_palette('Set1')
fig, ax = plt.subplots(figsize=(8, 4), dpi=100)
sns.barplot(data=country_customer_loyalty.stack().reset_index(), x='Country_Customer', y=0, hue='Loyalty_Status_2017_Total_Omnichannel')

plt.title("Loyalty Status by Country of Customer", fontweight='bold')
ax.set_xlabel("Country of Customer", fontweight='bold')
ax.set_ylabel("Percentage of Total Customers", fontweight='bold')

# Showing 50% threshold
ax.axhline(y=50, color='black', linestyle='--', lw=1)

plt.legend()
plt.show()<jupyter_output><empty_output><jupyter_text>* **There are no countries for which there is a noticeable unbalance between Loyal and New customers.*** **Japan is the country with the highest unbalance (41% Loyal vs. 59% New)**<jupyter_code>country_customer_tier = pd.crosstab(df_omni.Country_Customer, df_omni.Spending_Ranges_2017, normalize='index').apply(lambda x: np.round(x*100,2))
country_customer_tier
sns.set_style("whitegrid")
sns.set_palette(reversed(sns.color_palette("inferno")), 6)
fig, ax = plt.subplots(figsize=(8, 4), dpi=100)
sns.barplot(data=country_customer_tier[asc_labels].stack().reset_index(), x='Country_Customer', y=0, hue='Spending_Ranges_2017')

plt.title("Spending Tiers by Country of Customer", fontweight='bold')
ax.set_xlabel("Country of Customer", fontweight='bold')
ax.set_ylabel("Percentage of Spending Tiers", fontweight='bold')

# Showing 50% threshold
#ax.axhline(y=50, color='black', linestyle='--', lw=1)
ax.legend(mode='expand', ncol=6)

plt.show()
loyalty_tiers = pd.crosstab(df_omni.Loyalty_Status_2017_Total_Omnichannel, df_omni.Spending_Ranges_2017, normalize='columns').apply(lambda x: np.round(x*100,2))
loyalty_tiers
sns.set_style("whitegrid")
sns.set_palette('Set1')
fig, ax = plt.subplots(figsize=(8, 4), dpi=100)
sns.barplot(data=loyalty_tiers[asc_labels].stack().reset_index(), x='Spending_Ranges_2017', y=0, hue='Loyalty_Status_2017_Total_Omnichannel')

plt.title("Spending Tiers by Country of Customer", fontweight='bold')
ax.set_xlabel("Country of Customer", fontweight='bold')
ax.set_ylabel("Percentage of Spending Tiers", fontweight='bold')

ax.legend()

plt.show()<jupyter_output><empty_output><jupyter_text>Domestic Foreign vs. Loyalty<jupyter_code>dom_for_loyalty = pd.crosstab(df_omni.Domestic_Foreign, df_omni.Loyalty_Status_2017_Total_Omnichannel, normalize='index').apply(lambda x: np.round(x*100,2))
dom_for_loyalty
sns.set_style("whitegrid")
sns.set_palette('Set1')
fig, ax = plt.subplots(figsize=(8, 4), dpi=100)
sns.barplot(data=dom_for_loyalty.stack().reset_index(), x='Domestic_Foreign', y=0, hue='Loyalty_Status_2017_Total_Omnichannel')

plt.title("Spending Tiers by Country of Customer", fontweight='bold')
ax.set_xlabel("Country of Customer", fontweight='bold')
ax.set_ylabel("Percentage of Spending Tiers", fontweight='bold')

ax.legend()
plt.show()<jupyter_output><empty_output><jupyter_text>Retention Rate (RR) Analysis**It measures the capability to make our clients loyal and to retain them. The higher, the better.Customers who bought during the year of the analysis over customers who bought during the year plus lost customers.**$$\begin{equation} Retention\ Rate =  \frac {Retained\ Customers} {Retained\ Customers + Lost\ Customer}\end{equation}$$<jupyter_code>RR = np.round((df.Loyalty_Status_2018_Total_Omnichannel!='Lost').sum() / df.shape[0] * 100, 2)
print(f"Retention Rate between 2017 and 2018: {RR} %")<jupyter_output>Retention Rate between 2017 and 2018: 24.7 %<jupyter_text>**How to interpret this retention rate?**<jupyter_code>get_stats(df_omni, 'Loyalty_Status_2017_Total_Omnichannel', 'Sales_2017_Total_Omnichannel', order=False, outlier=True, n_std=3)<jupyter_output><empty_output><jupyter_text>* We have no information on new customers of 2018.* We know that in 2017, almost exactly 50% (24,492) of total customers were newly acquired customers and that they accounted for 38% (16.4 million) of total revenue for the year.* The 62% (27.1 million) of the revenue in 2017 was generated by Loyal customers.* In 2018, Loyal customers generated only 4.1 million.* In order to keep the revenue constant from 2017 to 2018, the company would have needed 39.4 millions generated from new customers (assuming all other conditions equal).* Assuming that the average spending for new customers remained the same in 2018, the company would have needed 59 thousands new customers.**We can conclude that the retention rate of 24.7% is indeed a serious issue for the company.**Retaining only 1/4 of the customers surely had a negative impact on the company revenues in 2018.  **Which actions should be taken?**<jupyter_code>mask_returned = df.Sales_2018_Total_Omnichannel.notna()
lost_customers = df_omni[mask_returned].groupby('Spending_Ranges_2017')['ID'].count().to_frame().loc[desc_labels,:].rename(columns={'ID':'Total_2018'}).reset_index()
lost_customers['Total_2017'] = df_omni.groupby('Spending_Ranges_2017')['ID'].count().to_frame().loc[desc_labels,:].values
lost_customers['Absolute_Diff'] = lost_customers['Total_2017'] - lost_customers['Total_2018']
lost_customers['Perc_Diff'] = np.round((lost_customers['Absolute_Diff'] / lost_customers['Total_2017'])*100, 2)
lost_customers.rename(columns={'Spending_Ranges_2017':'Spending_Ranges'}, inplace=True)
lost_customers<jupyter_output><empty_output><jupyter_text>* We can see that the lost customers are distributed evenly amongst the spending tiers.* However Top Clients accounted for 21.7 % of the total revenue.Our recommendation would be to invest heavily in a **Loyalty Program** targeted at Top Clients and an even more targeted action towards the "Stellar" Customers, that can be in the excel sheets attached named "stellar_clients_omni".Out of the company's top 15 customers, 10 were lost in 2018. We recommend to investigate directly the reason with those 10 lost.We recommend to heavily fidelize the top customers for each store, proposing customized shopping experience if they return and trying to establish a deeper connection with them.The ID of those important customers can be found in the excel sheets named respectively:- For the stores in France, top_clients_store_fr.xslx- For the stores in Italy, top_clients_store_it.xslx- For the store in the US, top_clients_store_us.xslx- For the store in China, top_clients_store_ch.xslx<jupyter_code>print(f"Avg sale of 2017: {df.Sales_2017_Total_Omnichannel.mean():0.0f}")
print(f"Avg sale of 2018: {df.Sales_2018_Total_Omnichannel.mean():0.0f}")
print(f"Total sales of 2017: {df.Sales_2017_Total_Omnichannel.sum():0.0f}")
print(f"Total sales of 2018: {df.Sales_2018_Total_Omnichannel.sum():0.0f}")
print(f"New customers of 2017: {df[df.Loyalty_Status_2017_Total_Omnichannel=='New'].shape[0]:0.0f}")
print(f"Revenue difference from 2017 to 2018: {df.Sales_2017_Total_Omnichannel.sum() - df.Sales_2018_Total_Omnichannel.sum():0.0f}")<jupyter_output>Avg sale of 2017: 898
Avg sale of 2018: 346
Total sales of 2017: 43562067
Total sales of 2018: 4145767
New customers of 2017: 24492
Revenue difference from 2017 to 2018: 39416300<jupyter_text>Spending Omnichannel, by Country - 2017<jupyter_code>country_stats_omni = get_stats(edited_df, 'Country_Store', 'Sales_2017_Total_Omnichannel', n_std=6)
country_stats_omni
top_clients_store_fr = n_outl(df_omni[df_omni["Country_Store"]=="France"], 'Sales_2017_Total_Omnichannel', n_std=6, detect=True)
top_clients_store_it = n_outl(df_omni[df_omni["Country_Store"]=="Italy"], 'Sales_2017_Total_Omnichannel', n_std=6, detect=True)
top_clients_store_us = n_outl(df_omni[df_omni["Country_Store"]=="US"], 'Sales_2017_Total_Omnichannel', n_std=6, detect=True)
top_clients_store_ch = n_outl(df_omni[df_omni["Country_Store"]=="China"], 'Sales_2017_Total_Omnichannel', n_std=6, detect=True)<jupyter_output><empty_output><jupyter_text>Segmentation 2018 Omnichannel<jupyter_code>df_omni_18 = ranges(df[df['Sales_2018_Total_Omnichannel'].notna()], 'Sales_2018_Total_Omnichannel', 'Spending_Ranges_2018', tuples, last_label)
stats_omni_18 = get_stats(df_omni_18, 'Spending_Ranges_2018', 'Sales_2018_Total_Omnichannel', order=True, n_std=3)
stats_omni_18<jupyter_output><empty_output><jupyter_text>Spending Evolution<jupyter_code>spending_evolution = np.round(pd.crosstab(df_omni_18.Spending_Ranges_2018, df_omni[mask_returned].Spending_Ranges_2017,normalize=True)\
                        .loc[desc_labels,desc_labels] * 100,2)
spending_evolution
def Sum(mat, r, c): 
  
    i, j = 0, 0; 
    upper_sum = 0; 
    lower_sum = 0; 
      
    # to calculate sum of upper triangle 
    for i in range(r): 
        for j in range(c): 
            if (i < j): 
                upper_sum += mat[i][j]; 
  
    print("Upper sum is ", upper_sum); 
      
    # to calculate sum of lower 
    for i in range(r): 
        for j in range(c): 
            if (j < i): 
                lower_sum += mat[i][j]; 
  
    print("Lower sum is ", lower_sum);
Sum(spending_evolution.values, spending_evolution.shape[0], spending_evolution.shape[1])<jupyter_output>Upper sum is  26.57
Lower sum is  45.260000000000005<jupyter_text>**The picture represented by the Spending Evolution is quite problematic:*** **Apart from Low and Very Low spending tiers, the percentage of customers moving to a lower spending range is always higher.*** **Particularly, the percentage of customers moving to a lower spending range is 45%, while the ones moving to a higher spending range is 26%**<jupyter_code>loyalty_lost = df_omni[~mask_returned].groupby('Loyalty_Status_2017_Total_Omnichannel')['ID'].count().to_frame().rename(columns={'ID':'Total_Lost_2018'}).reset_index()
loyalty_lost['Lost_Percentage'] = np.round(loyalty_lost['Total_Lost_2018'] / loyalty_lost['Total_Lost_2018'].sum() * 100, 2)
loyalty_lost<jupyter_output><empty_output><jupyter_text>Complaints Analysis The CEO of Max Performance Company noticed that customer complaints are not well managed by the company. As a solution he wants to implement a chatbot in the corporate website.Please provide the CEO data and insights on complaint topic. Based on a data driven point of view, do you support the CEO proposal of implementing a chatbot? Please explain and support your recommendation.<jupyter_code>complaint_mask = df_omni['Complaint'].notna()
complaints = df_omni[complaint_mask]
print(f"There were {complaints.shape[0]} total complaints in 2017, {(complaints.shape[0]/df_omni.shape[0])*100:0.2f} % of the customers complained.\n")
print(f"There were {complaints[complaints.Complaint_Channel=='Complaint in Boutique'].shape[0]} Botique complaints in 2017, {(complaints[complaints.Complaint_Channel=='Complaint in Boutique'].shape[0]/df_omni.shape[0])*100:0.2f} % of the customers complained through this channel.")
print(f"There were {complaints[complaints.Complaint_Channel=='Online Complaint'].shape[0]} Botique complaints in 2017, {(complaints[complaints.Complaint_Channel=='Online Complaint'].shape[0]/df_omni.shape[0])*100:0.2f} % of the customers complained through this channel.")
complaint_tiers = complaints.groupby('Spending_Ranges_2017')['ID'].count().to_frame().rename(columns={'ID':'Count'}).reset_index()
complaint_tiers['Total_2017'] =  df_omni.groupby('Spending_Ranges_2017')['ID'].count().values
complaint_tiers['Percentage'] = np.round((complaint_tiers['Count'] / complaint_tiers['Total_2017'])*100, 2)
complaint_tiers = complaint_tiers.set_index('Spending_Ranges_2017').loc[asc_labels,:].reset_index()
complaint_tiers
describe_col(complaints, 'Complaint_Channel')
describe_col(complaints, 'Complaint_Reason')
describe_col(complaints, 'Loyalty_Status_2018_Total_Omnichannel')
np.round(pd.crosstab(complaints.Loyalty_Status_2018_Total_Omnichannel, complaints.Spending_Ranges_2017, normalize='index')[desc_labels]*100, 2)
pd.crosstab(complaints.Complaint_Reason, complaints.Spending_Ranges_2017)[desc_labels]
pd.crosstab(complaints.Complaint_Channel, complaints.Spending_Ranges_2017)[desc_labels]
pd.crosstab(complaints.Complaint_Channel, complaints.Complaint_Reason)
get_stats(df_omni, 'Complaint_Channel', 'Sales_2017_Total_Omnichannel')
np.round((get_stats(df_omni, 'Complaint_Channel', 'Sales_2017_Total_Omnichannel')['Sales_Total'] / df_omni.Sales_2017_Total_Omnichannel.sum()) * 100,2)<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup