How We Calculate Member Lifetime Value — The Technical Breakdown

Technical companion to Member Lifetime Value in The Numbers That Matter series.

---

Member Lifetime Value is the total revenue a member generates across their entire relationship with your club. It combines three metrics covered elsewhere in this series into one number.

The formula

LTV = Average Revenue Per Visit × Average Visit Frequency (monthly) × Average Membership Duration (months)

Or equivalently:

LTV = Average Monthly Revenue Per Member × Average Membership Duration (months)

Both arrive at the same number. The first version is more useful for diagnosing which component to improve.

Calculating historical LTV (churned members)

For members who've already left, LTV is exact — sum everything they ever spent:

SELECT
  m.id,
  m.name,
  COUNT(v.id) AS total_visits,
  SUM(v.total_revenue) AS lifetime_revenue,
  MIN(v.checked_in_at) AS first_visit,
  MAX(v.checked_in_at) AS last_visit,
  EXTRACT(DAY FROM MAX(v.checked_in_at) - MIN(v.checked_in_at)) AS tenure_days
FROM members m
JOIN visits v ON v.member_id = m.id AND v.status = 'completed'
WHERE m.membership_streak_start IS NULL  -- churned members
GROUP BY m.id, m.name;

The average across all churned members gives you your historical LTV:

SELECT
  ROUND(AVG(lifetime_revenue), 2) AS avg_ltv,
  ROUND(AVG(total_visits), 1) AS avg_visits,
  ROUND(AVG(tenure_days), 0) AS avg_tenure_days
FROM (above query);

Projecting LTV for active members

Active members are still accumulating value. You can project their LTV using the cohort average:

WITH member_stats AS (
  SELECT
    m.id,
    m.membership_streak_start,
    EXTRACT(DAY FROM NOW() - m.membership_streak_start) AS current_tenure_days,
    COUNT(v.id) AS total_visits,
    SUM(v.total_revenue) AS revenue_to_date,
    SUM(v.total_revenue) /
      NULLIF(EXTRACT(MONTH FROM AGE(NOW(), m.membership_streak_start)), 0)
      AS monthly_revenue
  FROM members m
  JOIN visits v ON v.member_id = m.id AND v.status = 'completed'
  WHERE m.membership_streak_start IS NOT NULL
  GROUP BY m.id, m.membership_streak_start
)
SELECT
  id,
  revenue_to_date,
  monthly_revenue,
  current_tenure_days,
  -- Project using average total tenure from churned members
  ROUND(
    monthly_revenue * (SELECT AVG(tenure_days) / 30.0 FROM churned_stats)
  , 2) AS projected_ltv
FROM member_stats;

The projection multiplies the member's current monthly revenue rate by the average tenure of churned members. It's a rough estimate that improves as you collect more cohort data.

LTV by segment

This is where LTV gets actionable. Segment by any dimension:

By membership tier:

SELECT
  mt.name AS tier,
  COUNT(DISTINCT m.id) AS members,
  ROUND(AVG(ms.lifetime_revenue), 2) AS avg_ltv,
  ROUND(AVG(ms.total_visits), 1) AS avg_visits,
  ROUND(AVG(ms.tenure_days), 0) AS avg_tenure_days
FROM member_stats ms
JOIN members m ON ms.id = m.id
JOIN membership_tiers mt ON m.tier_id = mt.id
GROUP BY mt.name
ORDER BY avg_ltv DESC;

By signup month (cohort analysis):

SELECT
  DATE_TRUNC('month', m.created_at) AS cohort_month,
  COUNT(*) AS members,
  ROUND(AVG(ms.lifetime_revenue), 2) AS avg_ltv,
  ROUND(AVG(ms.tenure_days), 0) AS avg_tenure_days,
  ROUND(
    COUNT(*) FILTER (WHERE m.membership_streak_start IS NULL)::decimal /
    COUNT(*) * 100, 1
  ) AS churn_rate_pct
FROM member_stats ms
JOIN members m ON ms.id = m.id
GROUP BY cohort_month
ORDER BY cohort_month;

Cohort analysis shows whether your newer members are trending toward higher or lower LTV than your older ones — a signal of whether your product, pricing, or acquisition channels are improving.

The three levers

LTV decomposes into three levers, each of which can be independently improved:

SELECT
  -- Lever 1: Revenue per visit
  ROUND(AVG(v.total_revenue), 2) AS avg_rpv,
  -- Lever 2: Visit frequency (monthly)
  ROUND(
    COUNT(*)::decimal /
    NULLIF(EXTRACT(MONTH FROM AGE(MAX(v.checked_in_at), MIN(v.checked_in_at))), 0)
  , 1) AS avg_monthly_frequency,
  -- Lever 3: Tenure (months)
  ROUND(
    EXTRACT(DAY FROM MAX(v.checked_in_at) - MIN(v.checked_in_at)) / 30.0
  , 1) AS avg_tenure_months
FROM visits v
WHERE v.status = 'completed';

If LTV is $1,800 and it breaks down as $45 RPV × 4 visits/month × 10 months:

• Increasing RPV from $45 to $50 → LTV becomes $2,000 (+$200)
• Increasing frequency from 4 to 5 → LTV becomes $2,250 (+$450)
• Increasing tenure from 10 to 12 months → LTV becomes $2,160 (+$360)

Frequency and tenure typically offer the biggest ROI because they compound — a member who visits more often also tends to stay longer.

Acquisition cost threshold

The standard rule: you can profitably spend up to 1/3 of LTV to acquire a member.

SELECT
  ROUND(AVG(lifetime_revenue) / 3, 2) AS max_acquisition_cost
FROM churned_member_stats;

If your LTV is $1,800, your ceiling for acquisition spend is $600 per member. Any channel bringing in members for less than that is profitable.

LTV-informed win-back prioritization

When doing outreach to lapsed members, prioritize by their historical value:

SELECT
  m.id,
  m.name,
  sh.total_days AS previous_streak,
  ms.lifetime_revenue AS revenue_to_date,
  ms.monthly_revenue AS monthly_rate,
  ROUND(
    ms.monthly_revenue *
    GREATEST(
      (SELECT AVG(tenure_days) / 30.0 FROM churned_stats) -
      (ms.tenure_days / 30.0)
    , 0)
  , 2) AS unrealized_ltv
FROM members m
JOIN member_stats ms ON ms.id = m.id
JOIN streak_history sh ON sh.member_id = m.id
WHERE m.membership_streak_start IS NULL  -- churned
  AND sh.streak_end >= NOW() - INTERVAL '60 days'  -- recently lapsed
ORDER BY unrealized_ltv DESC;

unrealized_ltv estimates how much more the member would have generated if they'd stayed to the average tenure. A member with $800 in unrealized LTV is worth a personal phone call. A member with $50 gets an automated email.

---

Back to the main article: Member Lifetime Value — The Number That Tells You What a Member Is Really Worth