Calculating Revenue Per Customer

The Investor Question

During a funding round, an investor asked: *"What's the average lifetime value of your customers?"* The marketing team was spending $50 to acquire a customer. If the LTV was $40, we were dying. If it was $400, we were thriving.

The Quest: The LTV Calculation

Customer Lifetime Value (CLTV/LTV) in its simplest form is:

**Total Revenue / Total Unique Customers**

More advanced versions factor in time horizons and churn, but this basic version is a powerful start.

The Implementation: The Value Assessment

-- Simple Lifetime Value across all customers

SELECT

SUM(order_amount) / COUNT(DISTINCT customer_id) AS avg_ltv

FROM orders;

LTV by Segment

What if we want to know which customer **source** has the highest value? (e.g., customers from Google Ads vs. Instagram).

SELECT

acquisition_source,

SUM(o.order_amount) / COUNT(DISTINCT o.customer_id) AS avg_ltv

FROM orders o

JOIN customers c ON o.customer_id = c.id

GROUP BY c.acquisition_source

ORDER BY avg_ltv DESC;

The "Oops" Moment

I accidentally calculated LTV including free-trial users who never paid. The number was incredibly low and spooked the investors.

**Pro Tip**: Make sure your `WHERE` clause filters for only paying customers or customers who have reached a certain milestone. Your definitions matter.

The Victory

We discovered that customers from our YouTube ads had an LTV of $350, while customers from Instagram had an LTV of $80. Marketing immediately shifted budget, and customer quality went up. SQL changed our company's strategy in real-time.

Your Task for Today

Calculate the "Average Value" of your customers using `SUM(amount) / COUNT(DISTINCT customer_id)`.

*Day 26: SQL for the Product Manager.*