Overview

This query calculates the Average Customer Lifetime Value (LTV) across multiple time horizons (60, 90, 180, and 365 days), based on each customer's first order during a defined acquisition cohort.

SQL Query

WITH src_tbl AS
(
SELECT
  customer_id,
  groupArray( (event_date, order_revenue) ) AS orders,
  any(if(is_new_customer AND event_date BETWEEN '2024-01-01' AND '2024-03-31', event_date, null)) AS first_order_date
FROM
  orders_table o
WHERE
  event_date BETWEEN '2024-01-01' AND '2025-03-31'
GROUP BY 
  customer_id
HAVING 
  first_order_date IS NOT NULL
)
SELECT
  avg(arraySum(arrayMap((d, r) -> if(dateDiff('day', first_order_date, d) <= 60, r, 0), orders))) AS avg_ltv_60,
  avg(arraySum(arrayMap((d, r) -> if(dateDiff('day', first_order_date, d) <= 90, r, 0), orders))) AS avg_ltv_90,
  avg(arraySum(arrayMap((d, r) -> if(dateDiff('day', first_order_date, d) <= 180, r, 0), orders))) AS avg_ltv_180,
  avg(arraySum(arrayMap((d, r) -> if(dateDiff('day', first_order_date, d) <= 365, r, 0), orders))) AS avg_ltv_365
FROM 
  src_tbl

Tables and Metrics Used

Table

• orders_table (Orders Table)

Dimensions

• event_date
• customer_id
• is_new_customer

Measures

• order_revenue

Customization Options

This query uses a fixed cohort acquisition window (2024-01-01 to 2024-03-31) and evaluates LTV over four rolling periods from each customer's first order date. You can customize it in the following ways:

Change cohort window

• Adjust the event_date range in the any() condition to define a different acquisition cohort.

Extend LTV windows

• Add or change arrayMap conditions to include new durations (e.g. 730 for 2-year LTV).

Filter by platform or channel

• Add AND platform = 'Shopify' or similar filters inside the WHERE clause if needed.