Calculate Stripe Customer LTV by Subscription Plan from CSV

How to export plan-level subscription data from Stripe

1. In the Stripe Dashboard, navigate to Billing > Subscriptions.
2. Click Export, include both active and canceled subscriptions so you can measure lifespan and churn by plan, and select All columns.
3. Download subscriptions.csv (and optionally invoices.csv for exact cumulative cash collected). Drop it above for a first chart, or import it into the xmetric app to build the full LTV view.

Key Stripe CSV columns used

• Customer ID (or customer): groups multiple invoices or subscription periods to a single buyer.
• Plan (or Price ID / Product): the pricing tier identifier on the subscription item.
• Interval and Interval Count: separates monthly (month) from annual (year) commitments.
• Amount and Quantity: plan unit price and seat count.
• Created (UTC) (or Created date (UTC)) and Canceled At (UTC) / Ended At (UTC) (created, canceled_at, ended_at): start and termination timestamps used to compute customer tenure in months.

Realized LTV vs. Formula LTV: Tableau formulas and SQL

Analysts often confuse Formula LTV (ARPU / Churn Rate, which can explode to infinity when a new annual plan has zero churn yet) with Realized Cumulative LTV (actual cash collected per customer over N months). Build both side by side.

1. Realized Cumulative LTV per customer (FIXED LOD)

Using invoices.csv, sum total paid revenue per customer and average it by initial pricing plan:

// [Customer Realized Spend]
{ FIXED [Customer ID] : SUM(IF [Status] = "paid" THEN [Amount Paid] ELSE 0 END) }

In the xmetric app, place [Plan] on Rows and AVG([Customer Realized Spend]) plus MEDIAN([Customer Realized Spend]) on Columns to see both mean and median realized LTV. You can test the LOD first in the formula playground.

2. Observed Tenure in Months (DATEDIFF)

Using subscriptions.csv, measure how many months each subscription lasted up to cancellation or today:

// [Subscription Lifespan Months]
MAX(1, DATEDIFF(
  'month',
  [Created (UTC)],
  IFNULL([Ended At (UTC)], TODAY())
))

3. SQL equivalent for Plan-Level ARPU, Churn, and LTV

The equivalent SQL for the plan-level view:

WITH sub_lifespan AS (
  SELECT
    "Customer ID" AS customer_id,
    "Plan" AS plan_name,
    "Interval" AS billing_interval,
    "Status" AS status,
    CASE
      WHEN "Interval" = 'year' THEN ("Amount" * COALESCE("Quantity", 1)) / 12.0
      ELSE ("Amount" * COALESCE("Quantity", 1))
    END AS monthly_arpu,
    GREATEST(
      1,
      DATE_DIFF(
        'month',
        CAST("Created (UTC)" AS TIMESTAMP),
        COALESCE(CAST(NULLIF("Ended At (UTC)", '') AS TIMESTAMP), CURRENT_TIMESTAMP)
      )
    ) AS active_months
  FROM stripe_subscriptions
  WHERE "Status" IN ('active', 'past_due', 'canceled')
)
SELECT
  plan_name,
  billing_interval,
  COUNT(DISTINCT customer_id) AS total_customers,
  ROUND(AVG(monthly_arpu), 2) AS avg_monthly_arpu,
  ROUND(AVG(active_months), 1) AS avg_tenure_months,
  ROUND(AVG(monthly_arpu * active_months), 2) AS avg_realized_ltv,
  ROUND(
    100.0 * COUNT(DISTINCT CASE WHEN status = 'canceled' THEN customer_id END)
    / NULLIF(SUM(active_months), 0),
    2
  ) AS implied_monthly_churn_pct
FROM sub_lifespan
GROUP BY 1, 2
ORDER BY avg_realized_ltv DESC;

Pitfalls when comparing LTV across Stripe plans

• Right-censoring on new plans: If you launched a "Pro Team" tier 60 days ago, its Realized LTV will look tiny (at most 2 months of billing) while its Formula LTV may look infinite if nobody has canceled yet. Filter to cohorts at least 6 months old ([Created (UTC)] <= DATEADD('month', -6, TODAY())) when comparing tiers.
• Plan upgrades mid-stream: Updating a subscription in Stripe replaces the price on the subscription item, so subscriptions.csv reflects only the subscription's current Plan. If a customer starts on Starter and upgrades to Pro before churning, subscriptions.csv attributes their entire tenure to Pro. Join invoices.csv line items if you need first-touch plan attribution.
• Refunds and credit notes: amount_paid on an invoice records the amount paid on that invoice; credit notes are tracked separately (post_payment_credit_notes_amount), and refunds issued against a charge appear as Amount Refunded on the payments export. Subtract credit notes or Amount Refunded if you need net post-refund LTV.

Agent-native LTV modeling

In xmetric, every shelf placement and formula is a command. An MCP server for Claude and ChatGPT is in development, so your AI assistant will be able to segment Stripe LTV by plan, billing interval, or discount code. Live Postgres and BigQuery connectors are also coming.