0
$\begingroup$

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)
  • compute lifetime expectancy
    • 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?

$\endgroup$
1
  • $\begingroup$ 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. $\endgroup$
    – Valentas
    Commented Jan 18 at 17:18

1 Answer 1

0
$\begingroup$

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

Compute Lifetime Expectancy:

  • 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;

-- Compute Lifetime Expectancy
WITH survival_rates AS (
    -- The previous query to compute survival rates
)
SELECT 
    user_subscription_start_month,
    MAX(after_n_months) AS max_months,
    MAX(survival_rate) AS final_survival_rate
FROM survival_rates
GROUP BY user_subscription_start_month;
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.