I'm trying to compute Customer Lifetime Expectancy based on a table of subscriptions, that have 3 columns : user_id, user_subscription_start, user_subscription_end

I'm trying to follow the following methodology :

• compute survival rate:
• for a given year_month of subscription_start, I compute how many user subscribed this particular month, and how they have been declining. So I end up with 3 columns: user_subscription_start_month, after_n_months, number_of_remaining_users
• I divide by the first value, so I end up with 3 columns: user_subscription_start_month, after_n_months, survival_rate
• I group by user_subscription_start_month so I end up with : after_n_months, survival_rate (so basically after 0 month, I have a survival rate of 1, and it decreases, until it reach more or less 20% at month 10, meaning that after 10 months, only 10% of the initial subscribers remain customers)
• but I'm a bit confused because the service was created 14 months ago, so I don't have survival rates after that

How do I go from a table with user_id, user_subscription_start, user_subscription_end to life expectancy?

• If a significant proportion of the users haven't yet cancelled, you can just estimate lower bounds (expected survival time is at least x). Or you need to use some extra assumptions, for example, that your survival times follow some distribution. Commented Jan 18 at 17:18

You could follow a methodology that involves computing the survival rate and then using it to calculate the lifetime expectancy as follows:

Compute Survival Rate:

• For a given year_month of subscription_start, compute how many users subscribed in that particular month and how they have been declining over time.
• This results in 3 columns: user_subscription_start_month, after_n_months, number_of_remaining_users.
• Divide by the first value to get 3 columns: user_subscription_start_month, after_n_months, survival_rate.
• Group by user_subscription_start_month to get: after_n_months, survival_rate, where the survival rate decreases over time, indicating the proportion of initial subscribers that remain customers

• Since the service was created 14 months ago, you may not have survival rates beyond that point.
• To estimate the lifetime expectancy, you can use the available survival rates to make projections about the future. For example, if the survival rate drops to 20% at month 10, you can use this information to estimate the lifetime expectancy based on the observed trend.

By using the available survival rates and making reasonable assumptions about future behavior, you can estimate the lifetime expectancy of customers based on the subscription data.

Here's an example of how you'd do this in SQL (modify appropriately for your case):

-- Compute Survival Rate
WITH initial_subscribers AS (
SELECT
user_subscription_start_month,
COUNT(user_id) AS initial_count
FROM subscriptions
GROUP BY user_subscription_start_month
),
remaining_subscribers AS (
SELECT
user_subscription_start_month,
DATEDIFF(MONTH, user_subscription_start, user_subscription_end) AS after_n_months,
COUNT(user_id) AS remaining_count
FROM subscriptions
GROUP BY user_subscription_start_month, DATEDIFF(MONTH, user_subscription_start, user_subscription_end)
)
SELECT
r.user_subscription_start_month,
r.after_n_months,
CAST(r.remaining_count AS FLOAT) / i.initial_count AS survival_rate
FROM remaining_subscribers r
JOIN initial_subscribers i ON r.user_subscription_start_month = i.user_subscription_start_month;