Overview

Lifetime Value (LTV) represents the average order revenue per customer, for orders placed within the selected timeframe.

📘

LTV = Order Revenue / Unique Customers

The calculation is based on data from the Orders table.

Detailed Breakdown

The formula above is derived from the following components:

Order Revenue = SUM(order_revenue) --> Orders table
Unique Customers = uniq(o.customer_id) --> Orders table

Insights and Actions

Lifetime Value (LTV) shows the average revenue generated per customer, guiding decisions on acquisition spend and retention strategy:

• Assess Acquisition Profitability: Compare LTV to your cost per acquisition; if LTV exceeds acquisition cost, campaigns are generating sustainable value.
• Prioritize High-Value Segments: Segment customers by LTV to focus retention efforts (e.g., loyalty programs or VIP offers) on your most profitable cohorts.
• Boost Repeat Purchases: Use LTV trends to test cross-sell, upsell, and subscription offers aimed at extending the customer relationship and increasing revenue per buyer.

Related Metrics

• Blended CPA: Provides the average cost to acquire a customer, serving as a benchmark to ensure LTV outweighs acquisition spend.
• Returning Customers Percent: Indicates the share of repeat buyers, offering context for how repeat purchasing drives LTV growth.
• Average Order Value (AOV): Shows revenue per transaction; increasing AOV directly raises LTV when purchase frequency remains constant.

Example Use

Prompt

Show me the average LTV for customers over the past year

Response

Query

SELECT
  COALESCE(
    SUM(ot.order_revenue) / NULLIF(uniq (ot.customer_id), 0),
    0
  ) AS ltv
FROM
  orders_table AS ot
WHERE
  ot.event_date BETWEEN CURRENT_DATE()  - 365 AND CURRENT_DATE()  - 1;