# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# Output is cleared due to confidentiality of the information.

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

from jupyterthemes import jtplot
jtplot.style()

# +
import pandas as pd
import numpy as np
from googleapiclient.discovery import build
from google_auth_oauthlib.flow import InstalledAppFlow,Flow
from google.auth.transport.requests import Request
import os
import pickle

os.chdir(r'C:\Users\luc57.DESKTOP-NB5DC80\AE\ipynb')
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']

# here enter the id of your google sheet
SAMPLE_SPREADSHEET_ID_input = ''
SAMPLE_RANGE_NAME = 'A1:ZZ30000'

def main():
    global values_input, service
    creds = None
    if os.path.exists('token.pickle'):
        with open('token.pickle', 'rb') as token:
            creds = pickle.load(token)
    if not creds or not creds.valid:
        if creds and creds.expired and creds.refresh_token:
            creds.refresh(Request())
        else:
            flow = InstalledAppFlow.from_client_secrets_file(
                'credentials.json', SCOPES) # here enter the name of your downloaded JSON file
            creds = flow.run_local_server(port=0)
        with open('token.pickle', 'wb') as token:
            pickle.dump(creds, token)

    service = build('sheets', 'v4', credentials=creds)

    # Call the Sheets API
    sheet = service.spreadsheets()
    result_input = sheet.values().get(spreadsheetId=SAMPLE_SPREADSHEET_ID_input,
                                range=SAMPLE_RANGE_NAME).execute()
    values_input = result_input.get('values', [])

    if not values_input and not values_expansion:
        print('No data found.')

main()

concatdf=pd.DataFrame(values_input[1:], columns=values_input[0])
#file must be a google sheets, not a normal xlsx uploaded to gdrive
# -

concatdf = pd.read_excel(r'C:\Users\luc57.DESKTOP-NB5DC80\AE\ipynb\concatdf.xlsx',index_col=0)

concatdf.dtypes

# # Data Wrangling

concatdf.day.nunique()

#convert column day to datetime
concatdf['day']=pd.to_datetime(concatdf['day'])
concatdf['day'].nunique()

concatdf.month

# +
from datetime import datetime
#correct the month variable
concatdf['month'] = concatdf['day'].dt.month
concatdf['month'] = concatdf['month'].astype(str)

def month_name(month_number):
    datetime_object = datetime.strptime(month_number, "%m")
    return datetime_object.strftime("%B")

concatdf['month']=concatdf['month'].apply(month_name)
concatdf['month']
# -

cols = ['orders','product_price','gross_sales','discounts','net_sales',
        'taxes','total_sales','average_order_value',
        'units_per_transaction','net_quantity',
        'ordered_item_quantity']
concatdf[cols] = concatdf[cols].apply(pd.to_numeric, errors='coerce', axis=1)

concatdf.drop(columns=[''],inplace=True)

concatdf.dtypes

concatdf.product_variant

# # Determine Last Transactions

s = concatdf.groupby("customer_name")["day"].transform("max")
grouped_data=concatdf[concatdf["day"].eq(s)].groupby("customer_name").agg(lambda d: ", ".join(d.unique()))
grouped_data

grouped_data[['product_variant','discount_code','channel']]

# +
#get total net sales
net_sales=concatdf.groupby('customer_name')['net_sales'].sum().reset_index()

#get earliest transaction date
earliest_trans=concatdf.groupby('customer_name')['day'].min().reset_index()
#get last transaction date
last_trans=concatdf.groupby('customer_name')['day'].max().reset_index()

#rename column name
earliest_trans.columns=['customer_name','earliest_transaction_date']
last_trans.columns=['customer_name','last_transaction_date'] 

#convert the column to a datetime variable
last_trans['last_transaction_date']=pd.to_datetime(last_trans['last_transaction_date'])
earliest_trans['earliest_transaction_date']=pd.to_datetime(earliest_trans['earliest_transaction_date'])
# -

earliest_trans.sort_values(by='earliest_transaction_date')

import datetime as dt
today=pd.to_datetime(dt.date.today())
#create new column to determine last purchase date
last_trans['last_purchase_in_days']=today-last_trans['last_transaction_date']
last_trans['last_purchase_in_days']=last_trans['last_purchase_in_days'].apply(lambda x: x.days)

#get the product variants purchased on the last day and discount code used
product_variants=pd.DataFrame(grouped_data.product_variant.tolist(),columns=['product_variants'])
discount_codes=pd.DataFrame(grouped_data.discount_code.tolist(),columns=['discount_codes'])
channels=pd.DataFrame(grouped_data.channel.tolist(),columns=['channels'])

order_count=concatdf.groupby(['customer_name'])['order_name'].agg(pd.Series.nunique).to_frame()
last_trans = pd.merge(last_trans, order_count,on='customer_name',how='left')
last_trans.rename(columns={'order_name':'order_count'},inplace=True)

last_trans['product_variants']=product_variants
last_trans['discount_codes']=discount_codes
last_trans['channels']=channels

last_trans

last_trans.product_variants.value_counts()

# # Determine churners

last_trans['Churn']=None

# +
#Churn
last_trans['Churn']=np.where((last_trans['product_variants'].str.contains('50ml')) &
                             (last_trans['last_purchase_in_days']>150), 'Churned',
                              last_trans['Churn']
                            )

last_trans['Churn']=np.where((last_trans['product_variants'].str.contains('30ml')) &
                             (last_trans['last_purchase_in_days']>90), 'Churned',
                              last_trans['Churn']
                            )

#Survived
last_trans['Churn']=np.where((last_trans['product_variants'].str.contains('50ml')) &
                             (last_trans['last_purchase_in_days']<=150) &
                             (last_trans['order_count'] >1), 'Survived',
                              last_trans['Churn']
                            )
                             
last_trans['Churn']=np.where((last_trans['product_variants'].str.contains('30ml')) &
                             (last_trans['last_purchase_in_days']<=90) &
                             (last_trans['order_count'] >1), 'Survived',
                              last_trans['Churn']
                            )

# Too Early to Identify
last_trans['Churn']=np.where((last_trans['product_variants'].str.contains('30ml')) &
                             (last_trans['last_purchase_in_days']<=90) &
                             (last_trans['order_count'] ==1), 'Too Early to Identify',
                              last_trans['Churn']
                            )

last_trans['Churn']=np.where((last_trans['product_variants'].str.contains('50ml')) &
                             (last_trans['last_purchase_in_days']<=150) &
                             (last_trans['order_count'] ==1), 'Too Early to Identify',
                              last_trans['Churn']
                            )
# -

last_trans['Churn'].value_counts()

last_trans

date_trans = pd.merge(last_trans, earliest_trans, on='customer_name',how='left')
date_trans

# # Create DF to Include All Disc. Codes and Product Variants

concatdf.columns

product_variant_and_discount_code=concatdf[['customer_name','day','product_variant','discount_code','channel']]

product_variant_and_discount_code

product_variant_and_discount_code['cc'] = (product_variant_and_discount_code.groupby('customer_name').\
                                           cumcount() + 1).astype(str)
product_variant_and_discount_code = product_variant_and_discount_code.pivot( \
    index=['customer_name'], columns='cc', values=['day','product_variant','discount_code','channel'])
product_variant_and_discount_code.columns = ['_'.join(col) for col in product_variant_and_discount_code.columns]

product_variant_and_discount_code

product_variant_and_discount_code.to_excel('customer_data_one_line.xlsx')

# # Create customer_df

shipping_city=concatdf.groupby(['customer_name'])['shipping_city'].agg(pd.Series.mode).to_frame()
shipping_country=concatdf.groupby(['customer_name'])['shipping_country'].agg(pd.Series.mode).to_frame()
shipping_postal_code=concatdf.groupby(['customer_name'])['shipping_postal_code'].agg(
    pd.Series.mode).to_frame()
channel_unique=concatdf.groupby(['customer_name'])['channel'].agg(
    pd.Series.nunique).to_frame()
average_order_value=concatdf.groupby(['customer_name'])['average_order_value'].agg(
    pd.Series.mean).to_frame()
product_variant_unique=concatdf.groupby(['customer_name'])['product_variant'].agg(
    pd.Series.nunique).to_frame()
quantity_count=concatdf.groupby(['customer_name'])['product_id'].agg(
    pd.Series.count).to_frame()
disc_code_unique=concatdf.groupby(['customer_name'])['discount_code'].agg(
    pd.Series.nunique).to_frame()
net_sales= concatdf.groupby('customer_name')['net_sales'].sum()

customer_df = pd.concat([shipping_city,shipping_country,shipping_postal_code,
                         product_variant_unique,channel_unique,
                         order_count,quantity_count,disc_code_unique,
                        average_order_value,net_sales],axis=1)
customer_df

customer_df.rename(columns={'order_name': 'order_quantity',
                           'product_id':'purchase_quantity',
                            'channel':'number_of_channels',
                            'product_variant':'number_of_product_variants',
                           'discount_code':'number_of_discount_codes'},inplace=True)

# # Merge date_trans with customer_df

customer_df = pd.merge(customer_df, date_trans, on='customer_name',how='left')

customer_df

customer_df.drop(columns=['discount_codes','channels','product_variants','order_quantity'],inplace=True)
#these columns are dropped because they only contain information from the last day of transactions

# # Merge with order_name, disc_code, channel, product_variant

#concatenate the following variables to later store them in customer_df
filter_column = concatdf.groupby(by='customer_name', sort=False).agg( ','.join)
filter_column = filter_column[['order_name','discount_code','channel','product_variant']]

customer_df = pd.merge(customer_df, filter_column, on='customer_name',how='left')
customer_df

for column in customer_df.columns:
     print("\n" + column)
     print(customer_df[column].value_counts())

customer_df.Churn.value_counts()

print('Churn Rate: ',1983/(1983+513+245))

customer_df.loc[customer_df['customer_name']=='<NAME>']

customer_df.product_variant

customer_df.columns

customer_df.iloc[5]

# As order name, discount code, channel and product variant may contain duplicate values, we join them.

# ## Join duplicate values in the string

customer_df['channel_2']=customer_df['channel'].apply(lambda x: ', '.join(dict.fromkeys(y for y in x.split(',')).keys()))

customer_df[['channel','channel_2']][:20]

# The result looks good, so we can apply the same concept to the other variables mentioned above.

customer_df['order_name']=customer_df['order_name'].apply(lambda x: ', '.join(dict.fromkeys(y for y in x.split(',')).keys()))
customer_df['discount_code']=customer_df['discount_code'].apply(lambda x: ', '.join(dict.fromkeys(y for y in x.split(',')).keys()))
customer_df['product_variant']=customer_df['product_variant'].apply(lambda x: ', '.join(dict.fromkeys(y for y in x.split(',')).keys()))
customer_df['channel']=customer_df['channel_2']
customer_df.drop(columns=['channel_2'],inplace=True)
customer_df[['customer_name','order_name','discount_code','product_variant','channel']]

# # Define customer group according to by the numbers segmentation

customer_df[['customer_name','last_purchase_in_days','order_count']]

# best: >=4 orders, <=30 days ago
# recent: 1 order, <=30 days ago
# loyal: >=4 orders, 1-6 months ago
#
# promising: 2-3 orders, 30 to 180 days 
#
# defecting: 1 order, 1-6 months ago 30 to 180 days
# at risk : 180 to 360 ago 
# dormant: >360 days ago
#

# # Combine with By the Numbers segments

at_risk = pd.read_csv(r'C:\Users\luc57.DESKTOP-NB5DC80\AE\ipynb\By_the_numbers\at_risk_customers.csv')
best = pd.read_csv(r'C:\Users\luc57.DESKTOP-NB5DC80\AE\ipynb\By_the_numbers\best_customers.csv')
defecting = pd.read_csv(r'C:\Users\luc57.DESKTOP-NB5DC80\AE\ipynb\By_the_numbers\defecting_customers.csv')
dormant = pd.read_csv(r'C:\Users\luc57.DESKTOP-NB5DC80\AE\ipynb\By_the_numbers\dormant_customers.csv')
loyal = pd.read_csv(r'C:\Users\luc57.DESKTOP-NB5DC80\AE\ipynb\By_the_numbers\loyal_customers.csv')
promising = pd.read_csv(r'C:\Users\luc57.DESKTOP-NB5DC80\AE\ipynb\By_the_numbers\promising_customers.csv')
recent = pd.read_csv(r'C:\Users\luc57.DESKTOP-NB5DC80\AE\ipynb\By_the_numbers\recent_customers.csv')

at_risk.head()

#create the variable customer_name
at_risk['customer_name']=at_risk['first_name']+str(' ')+at_risk['last_name']
best['customer_name']=best['first_name']+str(' ')+best['last_name']
defecting['customer_name']=defecting['first_name']+str(' ')+defecting['last_name']
dormant['customer_name']=dormant['first_name']+str(' ')+dormant['last_name']
loyal['customer_name']=loyal['first_name']+str(' ')+loyal['last_name']
promising['customer_name']=promising['first_name']+str(' ')+promising['last_name']
recent['customer_name']=recent['first_name']+str(' ')+recent['last_name']

#convert the string to upper cases
at_risk['customer_name']=at_risk['customer_name'].str.upper()
best['customer_name']=best['customer_name'].str.upper()
defecting['customer_name']=defecting['customer_name'].str.upper()
dormant['customer_name']=dormant['customer_name'].str.upper()
loyal['customer_name']=loyal['customer_name'].str.upper()
promising['customer_name']=promising['customer_name'].str.upper()
recent['customer_name']=recent['customer_name'].str.upper()

# We only need the customer_group and verified_email, then merge on customer_name.

at_risk = at_risk[['customer_name','customer_group','verified_email']]
best = best[['customer_name','customer_group','verified_email']]
defecting = defecting[['customer_name','customer_group','verified_email']]
dormant = dormant[['customer_name','customer_group','verified_email']]
loyal = loyal[['customer_name','customer_group','verified_email']]
promising = promising[['customer_name','customer_group','verified_email']]
recent = recent[['customer_name','customer_group','verified_email']]

at_risk['customer_name']=at_risk['customer_name'].astype(str)
best['customer_name'] = best['customer_name'].astype(str)
defecting['customer_name'] = defecting['customer_name'].astype(str)
dormant['customer_name'] = dormant['customer_name'].astype(str)
loyal['customer_name'] = loyal['customer_name'].astype(str)
promising['customer_name'] = promising['customer_name'].astype(str)
recent['customer_name'] = recent['customer_name'].astype(str)
recent.dtypes

# ## Concatenate the different customer groups

customer_groups = pd.concat([at_risk, best, defecting, dormant, loyal, promising, recent])
print(len(customer_groups))

customer_groups

customer_groups = customer_groups.groupby(by='customer_name', sort=False).agg( ','.join)
#customer_groups.columns = customer_groups.columns.droplevel(0)
customer_groups.reset_index()

# ## Merge customer_df and customer_groups

customer_df = pd.merge(customer_df, customer_groups, on='customer_name', how='left')
print(len(customer_df.columns))
print(len(customer_df))

customer_df.customer_group.value_counts()

# +
customer_df['customer_group']=np.where(customer_df['customer_group']=='at_risk,at_risk',
                                      'at_risk',customer_df['customer_group'])

customer_df['customer_group']=np.where(customer_df['customer_group']=='defecting,defecting',
                                      'defecting',customer_df['customer_group'])

customer_df['customer_group']=np.where(customer_df['customer_group']=='dormant,dormant',
                                      'dormant',customer_df['customer_group'])

customer_df['customer_group']=np.where(customer_df['customer_group']=='promising,promising',
                                      'promising',customer_df['customer_group'])
# -

customer_df.customer_group.value_counts()

# ## Fill in the na values

customer_df.isna().sum()

# ### Fillna for customer_group

cust_group_na=customer_df.loc[customer_df['customer_group'].isna()]
print(len(cust_group_na))

cust_group_na[['customer_name','last_purchase_in_days','order_count']]

# +
cust_group_na['customer_group']=np.where(cust_group_na['last_purchase_in_days']>365, 'dormant',
                                        cust_group_na['customer_group'])

cust_group_na['customer_group']=np.where((cust_group_na['last_purchase_in_days']<=30) &
                                         (cust_group_na['order_count']>=4), 'best',
                                        cust_group_na['customer_group'])

cust_group_na['customer_group']=np.where((cust_group_na['last_purchase_in_days']<=30) &
                                         (cust_group_na['order_count']==1), 'recent',
                                        cust_group_na['customer_group'])

cust_group_na['customer_group']=np.where((cust_group_na['last_purchase_in_days']<=180) &
                                         (cust_group_na['order_count']>=4), 'loyal',
                                        cust_group_na['customer_group'])

cust_group_na['customer_group']=np.where((cust_group_na['last_purchase_in_days']<=180) &
                                         (cust_group_na['order_count']==1), 'defecting',
                                        cust_group_na['customer_group'])

cust_group_na['customer_group']=np.where((cust_group_na['last_purchase_in_days']<=180) &
                                         (cust_group_na['order_count']<=3), 'promising',
                                        cust_group_na['customer_group'])

cust_group_na['customer_group']=np.where(cust_group_na['last_purchase_in_days']>=180, 'at_risk',
                                        cust_group_na['customer_group'])



cust_group_na['customer_group'].value_counts()
# -

customer_df= customer_df.loc[~customer_df['customer_group'].isnull()]
print(len(customer_df))

# Concatenate the dataframe which initially have null customer groups with the initial sliced dataframe.

customer_df = pd.concat([customer_df,cust_group_na],axis=0)

print('Number of customers: ',len(customer_df))
print('')
print(customer_df.customer_group.value_counts())
print('')
print(customer_df.isna().sum())

# ### Fillna for Churn

customer_df.loc[customer_df['Churn'].isna()][['customer_name','last_purchase_in_days','order_count',
                                             'product_variant']]

na_churn = customer_df.loc[customer_df['Churn'].isna()]
print(len(na_churn))

# +
#Churn
na_churn['Churn']=np.where((na_churn['product_variant'].str.contains('50ml')) &
                             (na_churn['last_purchase_in_days']>150), 'Churned',
                              na_churn['Churn']
                            )

na_churn['Churn']=np.where((na_churn['product_variant'].str.contains('30ml')) &
                             (na_churn['last_purchase_in_days']>90), 'Churned',
                              na_churn['Churn']
                            )

#Survived
na_churn['Churn']=np.where((na_churn['product_variant'].str.contains('50ml')) &
                             (na_churn['last_purchase_in_days']<=150) &
                             (na_churn['order_count'] >1), 'Survived',
                              na_churn['Churn']
                            )
                             
na_churn['Churn']=np.where((na_churn['product_variant'].str.contains('30ml')) &
                             (na_churn['last_purchase_in_days']<=90) &
                             (na_churn['order_count'] >1), 'Survived',
                              na_churn['Churn']
                            )

# Too Early to Identify
na_churn['Churn']=np.where((na_churn['product_variant'].str.contains('30ml')) &
                             (na_churn['last_purchase_in_days']<=90) &
                             (na_churn['order_count'] ==1), 'Too Early to Identify',
                              na_churn['Churn']
                            )

na_churn['Churn']=np.where((na_churn['product_variant'].str.contains('50ml')) &
                             (na_churn['last_purchase_in_days']<=150) &
                             (na_churn['order_count'] ==1), 'Too Early to Identify',
                              na_churn['Churn']
                            )

na_churn.Churn.value_counts()
# -

na_churn.loc[na_churn['Churn'].isna()][['customer_name','last_purchase_in_days',
                                        'product_variant','order_count']]

# +
na_churn['Churn']= np.where((na_churn['product_variant']!='Sample') & 
                            (na_churn['last_purchase_in_days']>120),'Churn',
                            na_churn['Churn'])
                            
na_churn['Churn']= np.where((na_churn['product_variant']!='Sample') & 
                            (na_churn['last_purchase_in_days']<=120) &
                            (na_churn['order_count']==1),
                            'Too Early to Identify',
                            na_churn['Churn'])

na_churn['Churn']= np.where((na_churn['product_variant']!='Sample') & 
                            (na_churn['last_purchase_in_days']<=120) &
                            (na_churn['order_count']>1),
                            'Survived',
                            na_churn['Churn'])

na_churn['Churn']= np.where(na_churn['product_variant']=='Sample',
                            'Sample-Taker',
                            na_churn['Churn'])

na_churn['Churn'].value_counts()
# -

# Concatenate the revised churn and the original customer_df.

customer_df = customer_df.loc[~customer_df['Churn'].isnull()]
print(len(customer_df))

customer_df = pd.concat([customer_df, na_churn],axis=0)
print(len(customer_df))
print('')
print(customer_df.Churn.value_counts())

# # No more na values

customer_df.isnull().sum()

# ## Fix the city variable again

customer_df['shipping_city']=customer_df['shipping_city'].astype(str)

customer_df.dtypes

customer_df.shipping_city.value_counts()[:50]

# +
#get a list of unique cities
shipping_city = customer_df.shipping_city.tolist()

def unique_city(x):
  return list(dict.fromkeys(x))

unique_city_list =unique_city(shipping_city)

print(unique_city_list)
# -

top_20_shipping_cities = customer_df.shipping_city.value_counts()[:20].index.tolist()
top_20_shipping_cities

customer_df['shipping_city_rev']=customer_df['shipping_city']
customer_df.loc[~customer_df['shipping_city_rev'].isin(top_20_shipping_cities), 'shipping_city_rev']='Others'
#if the shipping city is not in the top 50 shipping cities, the value is others.

customer_df['shipping_city']=customer_df['shipping_city_rev']
customer_df.drop(columns=['shipping_city_rev'],inplace=True)

customer_df.shipping_city.value_counts()

# # Fix the customers which listed two countries.

customer_df.shipping_country = customer_df.shipping_country.astype(str)

customer_df.loc[customer_df['shipping_country'].str.contains('Netherlands')]\
[['customer_name','last_transaction_date','shipping_country','order_name']]

customer_df.shipping_country.value_counts()

# ## Value Distribution

for column in customer_df.columns:
     print("\n" + column)
     print(customer_df[column].value_counts())

# # Reorder the columns

customer_df.columns

customer_df=customer_df.reindex(columns=['customer_name','Churn','customer_group',
                                         'shipping_country', 'shipping_city',
                               'shipping_postal_code','order_name',
                                'channel','discount_code','product_variant',
                                'average_order_value','net_sales',
                                'order_count','purchase_quantity',
                                'number_of_channels',
                                'number_of_discount_codes','number_of_product_variants',
                               'earliest_transaction_date','last_transaction_date',
                                'last_purchase_in_days'])

customer_df.to_excel('customer_df.xlsx')

customer_df

customer_df.loc[customer_df['customer_group']=='defecting']\
[['customer_name','customer_group','Churn','last_purchase_in_days','order_count',
  'product_variant']]

customer_df.Churn.value_counts()


# +
def segmentor(row):
    if (row["last_purchase_in_days"] <= 30) & (row["order_count"] >= 4):
        return "best"
    elif (row["last_purchase_in_days"] <= 180) & (row["order_count"] >= 4):
        return "loyal"
    elif (row["last_purchase_in_days"] <= 30) & (row["order_count"] == 1):
        return "recent"
    elif (row["last_purchase_in_days"] <= 180) & (row["order_count"] == 1):
        return "defecting"
    elif (row["last_purchase_in_days"] <= 180) & (row["order_count"] <= 3):
        return "promising"
    elif row["last_purchase_in_days"] <= 360:
        return "at_risk"
    elif row["last_purchase_in_days"] >= 360:
        return "dormant"

customer_df["segment"] = customer_df.apply(lambda x: segmentor(x), axis=1)
# -

customer_df.loc[~(customer_df['segment'] == customer_df['customer_group'])]\
[['customer_name','last_purchase_in_days','order_count','segment','customer_group']][250:300]

customer_df.drop(columns=['customer_group'],inplace=True)

customer_df.columns

customer_df.to_excel('customer_df.xlsx')

StarcoderdataJupyterScriptsDedupFiltered