<jupyter_start><jupyter_text>Output is cleared due to the confidentiality of the information.  Customer with Discounted Purchase Analysis  Import Data<jupyter_code>import pandas as pd
import matplotlib.pyplot as plt
import numpy as np 
df = pd.read_excel(r'C:\Users\luc57.DESKTOP-NB5DC80\Downloads\A&E\Sales One Year Data (Edit 1).xlsx',sheet_name='Sales One Year Data (2)')
df.describe()
#number of rows
len(df)
#number of columns 
len(df.columns)
#name of columns
df.columns
#number of NA in each column
df.isna().sum()<jupyter_output><empty_output><jupyter_text>Campaign Exploration<jupyter_code>df[['utm_campaign_content','utm_campaign_medium','utm_campaign_name','utm_campaign_source','utm_campaign_term']]
df['utm_campaign_content'].unique()
df['utm_campaign_medium'].unique()
df['utm_campaign_name'].unique()
df['utm_campaign_source'].unique()
df['utm_campaign_term'].unique()
#total number of unique emails
len(df['customer_email'].unique())<jupyter_output><empty_output><jupyter_text>Filter only customers who have received discounts before<jupyter_code>#total number of transactions which have received discounts
subdf = df.loc[df['discounts']!=0]
len(subdf) 
#total number of unique customers who purchased with discounts
len(subdf['customer_email'].unique())
#number of purchase by each person with this email
subdf.groupby('customer_email').order_id.nunique()
Transactions = pd.DataFrame(subdf.groupby('customer_email').order_id.nunique())
Transactions.to_csv(r'C:\Users\luc57.DESKTOP-NB5DC80\Downloads\A&E\Transactions.csv')
Transactions = pd.read_csv(r'C:\Users\luc57.DESKTOP-NB5DC80\Downloads\A&E\Transactions.csv')
Transactions.columns = ['customer_email','count_of_purchase']
Transactions.sort_values(by='count_of_purchase',ascending=False)
Transactions.plot(kind='hist')
plt.title('Histogram of Customers using Discounts, based on Purchase Count')
print(len(Transactions.loc[Transactions['count_of_purchase']==1]))
print(round(len(Transactions.loc[Transactions['count_of_purchase']==1])/len(Transactions)*100,2),'%')
print(len(Transactions.loc[Transactions['count_of_purchase']==2]))
print(round(len(Transactions.loc[Transactions['count_of_purchase']==2])/len(Transactions)*100,2),'%')
print(len(Transactions.loc[Transactions['count_of_purchase']==3]))
print(round(len(Transactions.loc[Transactions['count_of_purchase']==3])/len(Transactions)*100,2),'%')
print(len(Transactions.loc[Transactions['count_of_purchase']==4]))
print(round(len(Transactions.loc[Transactions['count_of_purchase']==4])/len(Transactions)*100,2),'%')
print(len(Transactions.loc[Transactions['count_of_purchase']==5]))
print(round(len(Transactions.loc[Transactions['count_of_purchase']==5])/len(Transactions)*100,2),'%')<jupyter_output><empty_output><jupyter_text>Effectiveness of Campaign<jupyter_code>subdf.columns
subdf2=subdf[['hour','customer_email','customer_type','utm_campaign_content','utm_campaign_medium','utm_campaign_name','utm_campaign_source','utm_campaign_term',
              'order_id','product_price','variant_title','shipping_city','net_sales','ordered_item_quantity','returns']]
subdf2.head(2)
subdf2.returns.value_counts()
# The return column is not needed
del subdf2['returns']
subdf2['utm_campaign_content'].value_counts()<jupyter_output><empty_output><jupyter_text>Campaign: LJ<jupyter_code>dfLJ=subdf2.loc[subdf2['utm_campaign_content']=='LauraJoelle']
len(dfLJ)
dfLJ.loc[dfLJ.customer_type=='returning']
dfLJ=subdf2.loc[subdf2['utm_campaign_content']=='LJ']
dfLJ.groupby('customer_email').order_id.nunique()
dfLJgroupby = pd.DataFrame(dfLJ.groupby('customer_email').order_id.nunique())
print('Number of customers purchasing using LJ\'s coupon:',len(dfLJgroupby))
#number of non-returning customers
print('Number of non-returning customers:',len(dfLJgroupby.loc[dfLJgroupby.order_id!=1]))
dfLJgroupby.sort_values(by='order_id',ascending=False)
dfLJgroupby['order_id'].value_counts()<jupyter_output><empty_output><jupyter_text>Campaign: 'Dynamic_Feed - Winter'<jupyter_code>dfDFW=subdf2.loc[subdf2['utm_campaign_content']=='Dynamic_Feed - Winter']
dfDFW.groupby('customer_email').order_id.nunique()
dfDFWgroupby = pd.DataFrame(dfDFW.groupby('customer_email').order_id.nunique())
print('Number of customers purchasing using Dynamic Feed Winter\'s coupon:',len(dfDFWgroupby))
#number of non-returning customers
print('Number of non-returning customers:',len(dfDFWgroupby.loc[dfDFWgroupby.order_id!=1]))
dfDFW.loc[dfDFW.customer_type=='returning']<jupyter_output><empty_output><jupyter_text>Campaign: 'FP'<jupyter_code>dfFP=subdf2.loc[subdf2['utm_campaign_content']=='fp']
dfFP.groupby('customer_email').order_id.nunique()
dfFPgroupby = pd.DataFrame(dfFP.groupby('customer_email').order_id.nunique())
print('Number of customers purchasing using FP\'s coupon:',len(dfFPgroupby))
#number of non-returning customers
print('Number of non-returning customers:',len(dfFPgroupby.loc[dfFPgroupby.order_id!=1]))
dfFP.loc[dfFP.customer_type=='returning']<jupyter_output><empty_output><jupyter_text>Campaign: Dynamic_Feed - Black Friday<jupyter_code>dfDFBF=subdf2.loc[subdf2['utm_campaign_content']=='Dynamic_Feed - Black Friday']
dfDFBF.groupby('customer_email').order_id.nunique()
dfDFBFgroupby = pd.DataFrame(dfDFBF.groupby('customer_email').order_id.nunique())
print('Number of customers purchasing using Dynamic Feed - Black Friday\'s coupon:',len(dfDFBFgroupby))
#number of non-returning customers
print('Number of non-returning customers:',len(dfDFBFgroupby.loc[dfDFBFgroupby.order_id!=1]))
dfDFBF.loc[dfDFBF.customer_type=='returning']<jupyter_output><empty_output><jupyter_text>Campaign: Dynamic_Feed - Clean<jupyter_code>dfDFC=subdf2.loc[subdf2['utm_campaign_content']=='Dynamic_Feed - Clean']
dfDFC.groupby('customer_email').order_id.nunique()
dfDFCgroupby = pd.DataFrame(dfDFC.groupby('customer_email').order_id.nunique())
print('Number of customers purchasing using Dynamic Feed - Clean\'s coupon:',len(dfDFCgroupby))
#number of non-returning customers
print('Number of non-returning customers:',len(dfDFCgroupby.loc[dfDFCgroupby.order_id!=1]))
dfDFC.loc[dfDFC.customer_type=='returning']<jupyter_output><empty_output><jupyter_text>Campaign: Sales_white_jar<jupyter_code>dfSWJ=subdf2.loc[subdf2['utm_campaign_content']=='Sales_white_jar']
dfSWJ.groupby('customer_email').order_id.nunique()
dfSWJgroupby = pd.DataFrame(dfSWJ.groupby('customer_email').order_id.nunique())
print('Number of customers purchasing using Sales white jar\'s coupon:',len(dfSWJgroupby))
#number of non-returning customers
print('Number of non-returning customers:',len(dfSWJgroupby.loc[dfSWJgroupby.order_id!=1]))
dfSWJ.loc[dfSWJ.customer_type=='returning']<jupyter_output><empty_output><jupyter_text>Customer Emails with Purchase greater than 1<jupyter_code>subdf2.customer_email.value_counts()<jupyter_output><empty_output><jupyter_text>Returning Customers<jupyter_code>#number of returning customers that purchased using discounts before
returning = subdf2.loc[subdf2['customer_type']=='Returning']
len(returning)
#number of returning customers that purchased using discounts before
print(len(df.loc[df['customer_type']=='Returning']))
pd.DataFrame(returning['utm_campaign_content'].dropna())
df['utm_campaign_content'].value_counts()
returning['utm_campaign_content'].value_counts()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1486652977412731, 'fraction_numerical': 0.010677618069815195, 'mean_word_length': 12.405504587155963, 'pattern_counts': {'":': 0, '<': 49, '<?xml version=': 0, '>': 49, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '12', 'dir': 'jupyter-structured-clean-dedup', 'id': '664695', 'license': 'MIT', 'n_tokens_mistral': 2645, 'n_tokens_neox': 2546, 'n_words': 323, 'path': 'Facebook Campaign Analysis.ipynb', 'repo_name': 'andree-ldn/python', 'size': '7305'}