How to Build a Customer Lifetime Value Calculator

Knowing your customer lifetime value changes how you spend on acquisition. This automated customer lifetime value calculator built from crm data proves that spending $200 to acquire a customer worth $3,000 is not expensive.

What You Need Before Starting

• Python 3.8+
• CRM or billing system with purchase history
• pandas installed
• SQLite for storage

Step 1: Pull Purchase History

Get all transactions per customer.

import pandas as pd

def get_purchase_history(db_connection):
    return pd.read_sql("""
        SELECT customer_id, amount, purchase_date, product_type
        FROM transactions WHERE purchase_date >= date('now', '-24 months')
    """, db_connection)

Step 2: Calculate Per-Customer LTV

Sum total spend, average order value, and purchase frequency.

def calculate_ltv(purchases_df):
    metrics = purchases_df.groupby("customer_id").agg(
        total_spend=("amount", "sum"),
        order_count=("amount", "count"),
        avg_order=("amount", "mean"),
        first_purchase=("purchase_date", "min"),
        last_purchase=("purchase_date", "max"),
    ).reset_index()
    metrics["tenure_months"] = (
        pd.to_datetime(metrics["last_purchase"]) - pd.to_datetime(metrics["first_purchase"])
    ).dt.days / 30
    return metrics

Step 3: Project Future Value

Use retention patterns to estimate future revenue.

def project_ltv(metrics, months=36, churn_rate=0.05):
    monthly_revenue = metrics["avg_order"] * (metrics["order_count"] / metrics["tenure_months"].clip(lower=1))
    projected = 0
    for month in range(months):
        projected += monthly_revenue * ((1 - churn_rate) ** month)
    return metrics["total_spend"] + projected

Step 4: Segment by Cohort

Group customers by sign-up month and compare LTV.

def ltv_by_cohort(customer_metrics):
    customer_metrics["cohort"] = pd.to_datetime(
        customer_metrics["first_purchase"]
    ).dt.to_period("M")
    return customer_metrics.groupby("cohort").agg(
        avg_ltv=("total_spend", "mean"),
        count=("customer_id", "count"),
    )

Step 5: Write Back to CRM

Store the LTV score on each contact record.

def update_crm_ltv(crm_client, customer_metrics):
    for _, c in customer_metrics.iterrows():
        crm_client.update_contact(c["customer_id"], {
            "lifetime_value": c["total_spend"],
            "projected_ltv": c["projected_ltv"],
        })

What to Build Next

Use LTV data to set acquisition cost targets. A customer worth $2,500 means spending $500 to acquire them is a 5x return.

Related Reading

• Building an Automated FAQ From Customer Questions - automated faq customer questions
• Setting Up Automated Customer Satisfaction Tracking - automated customer satisfaction tracking
• Setting Up Automated Customer Lifecycle Campaigns - automated customer lifecycle campaigns