Plain-language questions
Ask for customer lifetime value (CLV) per month, customer or product in the SQL Explorer with AI. The AI writes the SQL, you see the query and run it yourself; the AI only receives metadata.
KPI · E-commerce
Customer lifetime value (CLV): formula and example.
Customer lifetime value (CLV) is the value an average customer generates over the entire time they buy from you. It is best calculated on gross margin rather than revenue, so you see what a customer really contributes. CLV tells you how much you can reasonably spend to acquire a new customer.
Customer lifetime value (CLV) formula
Average order value × orders per customer per year × average customer lifespan in years × gross margin %
Worked example
At Acme Groothandel, average order value is €150, a customer orders 3.2 times a year and stays for 2.5 years on average. Revenue per customer is €150 × 3.2 × 2.5 = €1,200. With a gross margin of 35%, CLV is €1,200 × 35% = €420.
Fictitious figures for Acme Groothandel, a sample wholesaler.
How to read this KPI
A higher CLV means customers are worth more over the whole relationship, because they buy more often, stay longer or place larger orders. Compare CLV with what it costs to acquire a customer, and split it by acquisition channel or customer segment. Track it by cohort (customers acquired in the same quarter) to see whether new customers are becoming more or less valuable.
Pitfalls with customer lifetime value (CLV)
- Using revenue instead of margin, which overstates customer value and the acquisition budget it justifies.
- Assuming a customer lifespan longer than your own history can support.
- Using one average CLV when business customers and consumers buy very differently.
SQL & DAX
Calculating customer lifetime value (CLV) in SQL and DAX
Two examples on a fictitious data warehouse for Acme Groothandel: a PostgreSQL query and a measure for a Power BI model. Adjust table and column names to your own model.
WITH first_orders AS (
SELECT customer_id, MIN(order_date) AS first_order
FROM orders
WHERE status <> 'cancelled'
GROUP BY customer_id
)
SELECT COUNT(DISTINCT f.customer_id) AS customers,
ROUND(SUM(ol.quantity * (ol.unit_price - ol.unit_cost) - ol.discount_amount)
/ NULLIF(COUNT(DISTINCT f.customer_id), 0), 2) AS clv_margin_3y
FROM first_orders f
JOIN orders o ON o.customer_id = f.customer_id AND o.status <> 'cancelled'
AND o.order_date < f.first_order + interval '3 years'
JOIN order_lines ol ON ol.order_id = o.order_id
WHERE f.first_order < current_date - interval '3 years';CLV (historic margin) =
DIVIDE(
SUMX(OrderLines, OrderLines[quantity] * (OrderLines[unit_price] - OrderLines[unit_cost]) - OrderLines[discount_amount]),
DISTINCTCOUNT(Orders[customer_id])
)In ENABLE
Tracking this KPI in ENABLE
In your Power BI dashboard
Define the KPI as a measure in your Power BI model and show it in the portal, with "Last refreshed" next to it. Access profiles and optional row-level security decide who sees which figures.
Custom dashboards (pilot)
Add the KPI as a tile to a custom dashboard, compared with last year. The Dashboard Builder is a pilot and can be switched on per client.
Monthly targets
With the built-in Targets app you set a target for the KPI per month, so you see actuals and target side by side.
FAQ
Frequently asked questions
How do you calculate customer lifetime value?
A simple method is average order value × orders per year × lifespan in years × gross margin. With €80 per order, 4 orders a year, a 3-year lifespan and a 40% margin, CLV is €384. A historical variant adds up the actual margin per customer over the first years after their first purchase.
What is the difference between CLV and revenue per customer?
Revenue per customer covers a single period, usually a year. CLV covers the whole customer relationship and is ideally based on margin. That makes CLV better suited to justifying acquisition budgets.
How do CLV and CAC relate?
CLV is what a customer brings in; CAC (customer acquisition cost) is what it costs to win them. Dividing one by the other gives the LTV:CAC ratio, which shows whether acquisition pays for itself. Use the same scope for both, for example margin and all acquisition costs.
Further reading
- KPI libraryE-commerce KPIs: formulas and examplesE-commerce KPIs cover visitors, orders, returns and margin per channel. The data comes from your web shop, marketplaces and payment provider, alongside purchasing and stock from your ERP.Read more
- ResourcesHow to build a KPI dashboard: steps and examplesA KPI dashboard is an overview of the key figures you use to steer towards your goals, such as revenue, margin, on-time delivery or absenteeism. A good KPI dashboard does not start with the software, but with the decisions it needs to support. You then choose a limited number of KPIs, define them, sketch the dashboard and only then build it.Read more
- SolutionPower BI dashboard portal: share dashboards securelyShare your Power BI dashboards securely in your own portal, with role-based access.Read more
Use AI on your own data. Securely, and without the headache.
In an online demo we show how ENABLE makes your data securely available and how you build on it: describing an app in the AI Builder, plain-language questions, dashboards and APIs. And how we set it up and manage it for you.