Lifetime Value (LTV)

Overview

Lifetime Value (LTV) represents the total revenue you can expect from a single customer account throughout the business relationship.

πŸ“˜

LTV = Order Revenue / Unique Customers

The calculation is based on data from the Orders table.

Insights and Actions

Lifetime Value (LTV) is crucial for understanding the long-term value of your customer base and guiding strategic investment in customer acquisition and retention. Efficiently leveraging LTV data can guide strategic business decisions:

  • Optimize Customer Acquisition Costs (CAC): Compare LTV to CAC to ensure you're investing a sustainable amount to acquire new customers, aiming for a high LTV to CAC ratio.
  • Tailor Retention Strategies: Focus on high-LTV customer segments with personalized marketing, loyalty programs, and customer service enhancements to boost retention.
  • Inform Product and Service Development: Use insights from LTV analysis to invest in products or services that are likely to increase the lifetime value of customers.
  • Adjust Pricing Strategies: Analyze the impact of pricing changes on LTV and adjust your strategies to maximize long-term revenue from your customer base.

Example Use

Prompt

Give me my LTV for customers over the past year

Response

Query

WITH
  new_customers_cte AS (
    SELECT DISTINCT
      customer_id
    FROM
      orders_table
    WHERE
      platform IN ('stripe', 'shopify', 'amazon')
      AND event_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR) AND CURRENT_DATE()
  )
SELECT
  COALESCE(
    SUM(order_revenue) / NULLIF(COUNT(DISTINCT NC.customer_id), 0),
    0
  ) AS ltv
FROM
  orders_table AS O
  JOIN new_customers_cte AS NC ON NC.customer_id = O.customer_id
WHERE
  o.event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR);