<jupyter_start><jupyter_text>Background: Customer Lifetime ValueIn marketing, customer lifetime value (CLV or often CLTV), lifetime customer value (LCV), or life-time value (LTV) is a prediction of the net profit attributed to the entire future relationship with a customer. Customer lifetime value can also be defined as the dollar value of a customer relationship, based on the present value of the projected future cash flows from the customer relationship. Customer lifetime value is an important concept in that it encourages firms to shift their focus from quarterly profits to the long-term health of their customer relationships. Customer lifetime value is an important number because it represents an upper limit on spending to acquire new customers. For this reason it is an important element in calculating payback of advertising spent in marketing mix modeling.<jupyter_code>df = pd.read_csv("HW1 clv_transactions.csv", date_parser=True)
df.head()<jupyter_output><empty_output><jupyter_text>Step 1 - About the DatasetThe dataset available is in CSV format and contains 4,200 transactions records. Each row in the dataset represent a single transaction. There are four columns in the dataset. - The TransactionID is a unique identifier of individual transactions. - The TransactionDate is the date of the transaction. - The CustomerID is the identifier of the customer who made the transaction. - And the Amount is the recorded amount of transaction in US dollars.<jupyter_code>df.isna().sum() # 1. There are no missing values in the dataset
print("Min. Transaction Date: {}".format(df.TransactionDate.apply(lambda x:datetime.strptime(x, '%m/%d/%Y')).min())) # 2. Min Transaction Date
print("Max. Transaction Date: {}".format(df.TransactionDate.apply(lambda x:datetime.strptime(x, '%m/%d/%Y')).max())) # 2. Max Transaction Date
print("Count of unique customers: {}".format(len(set(df.CustomerID)))) # 3. Count of unique customers
print("*Descriptive Stats*:\nMin: {}, Max: {}, Var: {:.2f}, Std: {:.2f}".format(df.Amount.min(),df.Amount.max(), df.Amount.var(), df.Amount.std()))

df["Trx_Year"] = df.TransactionDate.apply(lambda x:datetime.strptime(x, '%m/%d/%Y').year)
plt.figure(figsize=(16,8))
plt.scatter(x='Trx_Year', y='Amount', data=df)
plt.xlabel("Transaction Year")
plt.ylabel("Transaction Amount ($)")
plt.title("Transactions over time")
plt.show()
print("### Overall there  doesn't seem to be any relationship of amounts over time. Will explore this again after removing outliers")
df.groupby("Trx_Year")[["Amount"]].sum().reset_index() # Extracting year  
x = df.groupby("Trx_Year")[["Amount"]].sum()
x.plot(figsize=(16,8))
plt.xlabel("Transaction Year")
plt.ylabel("Total Transaction Amount ($)")
plt.title("Transactions over time")
plt.show()<jupyter_output><empty_output><jupyter_text>Other that 2013, there has been a general increase in transaction amount over the years  Step 2 - Further exploring the data<jupyter_code># 1. Are there any outliers? 
plt.figure(figsize=(16,8))
plt.boxplot(df['Amount'])
plt.grid()
plt.title("Boxplot of Transaction Amount")
plt.show()<jupyter_output><empty_output><jupyter_text>Yes, there are a few outliers in the dataset<jupyter_code>df.Amount.describe()
plt.figure(figsize=(16,8))
plt.hist(x='Amount', data=df, bins =50)
plt.xlabel("Transaction Amount")
plt.ylabel("Frequency")
plt.title("Histogram of Transaction Amount")<jupyter_output><empty_output><jupyter_text>Becuase of the extremely low frequency of the outliers, they are not visible Amounts that are 3 SDs away from the mean can be treated as outlier and negative values can be treated as anomalies. For the purpose of elimination, lets group them together as 'Outlier'<jupyter_code>#Labeling the outliers and error

df['Outlier'] = np.logical_or(np.logical_or( (df['Amount'] <= (df['Amount'].mean() - (df['Amount'].std() * 3))), # 3SDs less
                              (df['Amount'] >= (df['Amount'].mean() + (df['Amount'].std() * 3)))  ), # 3SDs more
                              df['Amount']<0) # negative

#There are 5 outliers in the dataset
print("Transaction Outliers:")
df[df["Outlier"]==True]
# 2. If so how would you treat them?
# We will filter out all the outliers
df = df[df['Outlier'] == False]

# New Boxplot
plt.figure(figsize=(16,8))
plt.title("Boxplot of Amount after getting rid of Outliers ( >3 sd away from mean)")
plt.boxplot(df['Amount'])
plt.grid()
plt.show()
plt.figure(figsize=(16,8))
plt.hist(x='Amount', data=df, bins =50,rwidth=0.7)
plt.xlabel("Transaction Amount")
plt.ylabel("Frequency")
plt.title("Histogram of Transaction Amount after Outlier removal")
plt.grid()
plt.figure(figsize=(18,12))
ax = sns.boxplot(x='Trx_Year', y='Amount', data=df,boxprops={'facecolor':'None'}, showfliers=False,whiskerprops={'linewidth':0})
ax = sns.swarmplot(x='Trx_Year', y='Amount', data=df)
plt.xlabel("Transaction Year")
plt.ylabel("Transaction Amount ($)")
plt.title("Transactions over time")
plt.show()<jupyter_output><empty_output><jupyter_text>The mean transaction amounts appear to have stayed constant over the years but the counts have increased as seen in the above swarm plot  Step 3 - Determining when the customers were first acquired<jupyter_code># Similar to a partition by function in SQL -- gets the minimum Trx_Year for each CustomerID
df["Origin Year"] = df.groupby('CustomerID').Trx_Year.transform(np.min)<jupyter_output><empty_output><jupyter_text>Displaying the origin year for a random customer<jupyter_code>df.head(10)<jupyter_output><empty_output><jupyter_text>Finding the Origin year for a random customer<jupyter_code>df.loc[df["CustomerID"] == np.random.randint(100)]<jupyter_output><empty_output><jupyter_text>Step 4 - Calculating cumulative transaction amounts<jupyter_code>df_cohort = df.groupby(['Origin Year', 'Trx_Year']) \
              .agg(Trx_amount=('Amount', 'sum')) \
              .reset_index(drop=False)
df_cohort["period_since_aquisition"]= (df_cohort["Trx_Year"]-df_cohort["Origin Year"]+1)*12
df_cohort["Cum_Trx_amount"] = df_cohort.groupby('Origin Year').Trx_amount.transform(np.cumsum)
cohort_table = df_cohort.pivot_table(index="Origin Year", columns="period_since_aquisition", values="Cum_Trx_amount").reset_index()
cohort_table["Origin Year"] = cohort_table["Origin Year"].apply(lambda x: str(x)+"-01-01 - " + str(x)+"-12-31", )
cohort_table.rename_axis("", axis=1).fillna("")<jupyter_output><empty_output><jupyter_text>Step 5 - New Customers Cohort Analysis<jupyter_code>a = df.groupby(['Origin Year', 'Trx_Year']) \
              .agg(Unique_customers=('CustomerID', 'nunique')) \
              .reset_index()
a["x"] = a["Unique_customers"] * np.floor(a["Origin Year"] / a["Trx_Year"]).astype("int")
a["x"] = a["x"].astype("int")
a["Unique_customers"] = a.groupby(['Origin Year']).x.transform(max)
del a["x"]
a["period_since_aquisition"]= (a["Trx_Year"]-a["Origin Year"]+1)*12
a = a.pivot_table(index="Origin Year", columns="period_since_aquisition", values="Unique_customers").reset_index()
a["Origin Year"] = a["Origin Year"].apply(lambda x: str(x)+"-01-01 - " + str(x)+"-12-31", )
a = a.rename_axis("", axis=1).fillna("")
a<jupyter_output><empty_output><jupyter_text>Step 6 - Historic CLV<jupyter_code>clv_table = ((cohort_table.set_index("Origin Year"))/(a.set_index("Origin Year"))).fillna("")
clv_table
#Plotting historic CLV
plt.figure(figsize=(16,10))
cohort1, = plt.plot(clv_table.iloc[0], marker="o")
cohort2, = plt.plot(clv_table.iloc[1 ,0:5], marker= "o")
cohort3, = plt.plot(clv_table.iloc[2 ,0:4], marker= "o")
cohort4, = plt.plot(clv_table.iloc[3 ,0:3], marker= "o")
cohort5, = plt.plot(clv_table.iloc[4 ,0:2], marker= "o")
cohort6, = plt.plot(clv_table.iloc[5 ,0:1], marker= "o")

plt.title("Historic CLV ")
plt.xlabel('Age')
plt.ylabel('Historic CLV')

plt.grid()
plt.legend([cohort1, cohort2, cohort3, cohort4, cohort5, cohort6], clv_table.index,
           loc='upper left', prop={'size': 14})

plt.show()<jupyter_output><empty_output><jupyter_text>Step 7 - Interpretting Results<jupyter_code>pd.DataFrame(clv_table.replace("", np.nan).mean(axis = 0, skipna = True).round(2), columns=["HistoricCLV"]).reset_index().rename(columns={"period_since_aquisition":"Age"})<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15225334957369063, 'fraction_numerical': 0.015347137637028014, 'mean_word_length': 6.196319018404908, 'pattern_counts': {'":': 1, '<': 51, '<?xml version=': 0, '>': 51, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '12', 'dir': 'jupyter-structured-clean-dedup', 'id': '12642', 'license': 'MIT', 'n_tokens_mistral': 2862, 'n_tokens_neox': 2679, 'n_words': 707, 'path': 'Customer Lifetime Value.ipynb', 'repo_name': 'sparsh-tekriwal/Customer-Lifetime-Value', 'size': '8210'}