app.py

from datetime import datetime

import pandas as pd
import streamlit as st


def months_between_dates(start_date, end_date):
    return (end_date.year - start_date.year) * 12 + (end_date.month - start_date.month)

def calculate_lifespan(row):
    if pd.notna(row["Churned"]):
        return (row["Churned"] - row["Date"]).days
    else:
        return (datetime.now() - row["Date"]).days
    
def date_filtered_df(df, start_date, end_date):
    return df[(df['Date'] >= start_date) & (df['Date'] <= end_date)]

def average_customer_lifespan_calculation(
        df,
        start_date,
        end_date,
    ) -> float:
    df.sort_values(by=['Customer', 'Date'], inplace=True)
    mask = (df['Date'] >= start_date) & (df['Date'] <= end_date)
    df = df.loc[mask]
    df["Lifespan"] = df.apply(calculate_lifespan, axis=1)
    df = df.dropna(subset=["Value"])
    # Calculate average customer lifespan
    return round(df["Lifespan"].mean(), 0)


def icon_select(value):
    if value >= 7:
        return '🚀'
    elif value >= 5:
        return '🔥'
    elif value > 3.5:
        return '💤'
    else:
        return '💀'

@st.cache_data(ttl="5m")
def get_data(file_link):
    
    if 'dl=0' in file_link:
        file_link = file_link.replace('dl=0', 'dl=1')
    all_data_df = pd.read_excel(file_link)
    return all_data_df


st.title('Customer LTV Calculator')

file_link = st.text_input(
    'Link to data file',
)

if not file_link:
    st.stop()

all_data_df = get_data(file_link)

col1, col2, col3 = st.columns(3)
with col1:
    start_date = st.date_input(
        'Start Date:',
        value=pd.to_datetime('2022-09-01'),
        max_value=pd.to_datetime(datetime.now().date()),
        format='DD-MM-YYYY',
    )
with col2:
    end_date = st.date_input(
        'End Date:',
        value=pd.to_datetime(datetime.now().date()),
        max_value=pd.to_datetime(datetime.now().date()),
        format='DD-MM-YYYY',
    )
with col3:
    start_datetime = pd.to_datetime(start_date)
    end_datetime = pd.to_datetime(end_date)
    number_of_months = months_between_dates(start_datetime, end_datetime)
    st.write(str(number_of_months), 'months')

calculated_acl = average_customer_lifespan_calculation(
    all_data_df,
    start_datetime,
    end_datetime,
)

if start_date < end_date:
    # Filter the dataframe based on the selected date range
    mask = (all_data_df['Date'] >= start_datetime) & (all_data_df['Date'] <= end_datetime)
    all_data_df = all_data_df.loc[mask]
else:
    st.error('Error: End date must be after the start date.')

all_data_date_filtered = date_filtered_df(all_data_df, start_datetime, end_datetime)
average_order_size = all_data_date_filtered['Value'].mean()
formatted_num = "£{:,.2f}".format(average_order_size)
st.write('Average order size (AOS):', str(formatted_num))

purchase_frequency = all_data_date_filtered.groupby('Customer')['Date'].nunique()
average_purchase_frequency_rate = purchase_frequency.mean()/number_of_months
st.write('Average purchase frequency rate (APFR) per customer per month:', str(round(average_purchase_frequency_rate, 2)))
customer_value = average_order_size * average_purchase_frequency_rate
customer_value_formatted = "£{:,.2f}".format(customer_value)
st.write('Customer Value (AOS x APFR):', customer_value_formatted)

average_customer_lifespan = 12
average_customer_lifespan = st.slider(
    f'Average Customer Lifespan (months) - calculated value {calculated_acl} days',
    min_value=1,
    max_value=50,
    step=1,
    value=12,
)
customer_lifetime_vale = average_customer_lifespan * customer_value
customer_lifetime_vale_formatted = "£{:,.2f}".format(customer_lifetime_vale)
st.write('Customer Lifetime Value (CLV):', customer_lifetime_vale_formatted)

acquisition_cost = 50
acquisition_cost = st.slider('Cost of acquisition', min_value=0, max_value=1000, step=10, value=50)
clv_cac_ratio = customer_lifetime_vale/acquisition_cost

all_data_df['year_month'] = all_data_df['Date'].dt.to_period('M')
all_data_df = all_data_df.sort_values(by='Date')

st.write(
    'CLV to CAC ratio:',
    "{:,.2f}".format(clv_cac_ratio),
    ': 1',
    icon_select(clv_cac_ratio),
)