Как посчитать Upsell Rate в SQL

Зачем Upsell Rate

Upsell Rate — % customers upgraded на higher tier / paid more. Cheaper чем acquire new. Healthy SaaS — 10-20% upsell yearly. Drives Net Revenue Retention (NRR).

Формула

Upsell Rate = upgraded_customers / total_customers × 100%

«Upgraded» = moved to higher MRR tier (e.g., Basic → Pro).

Базовый расчёт

WITH customer_history AS (
    SELECT
        user_id,
        plan,
        plan_started_at,
        LEAD(plan) OVER (PARTITION BY user_id ORDER BY plan_started_at) AS next_plan,
        LEAD(plan_started_at) OVER (PARTITION BY user_id ORDER BY plan_started_at) AS next_plan_at
    FROM subscriptions
),
upgrades AS (
    SELECT DISTINCT user_id
    FROM customer_history
    WHERE plan = 'basic' AND next_plan = 'pro'
      OR plan = 'pro' AND next_plan = 'enterprise'
      OR plan = 'basic' AND next_plan = 'enterprise'
)
SELECT
    COUNT(DISTINCT s.user_id) AS total_customers,
    COUNT(DISTINCT u.user_id) AS upgraded,
    COUNT(DISTINCT u.user_id)::NUMERIC * 100 / COUNT(DISTINCT s.user_id) AS upsell_rate_pct
FROM subscriptions s
LEFT JOIN upgrades u USING (user_id)
WHERE s.plan_started_at >= CURRENT_DATE - INTERVAL '12 months';

Expansion Revenue

Upsell × added MRR:

WITH plan_prices AS (
    SELECT * FROM (VALUES
        ('basic', 29),
        ('pro', 79),
        ('enterprise', 299)
    ) AS p(plan, monthly_price)
),
customer_history AS (
    SELECT
        s.user_id,
        s.plan,
        s.plan_started_at,
        p.monthly_price,
        LAG(p.monthly_price) OVER (PARTITION BY s.user_id ORDER BY s.plan_started_at) AS prev_price
    FROM subscriptions s
    JOIN plan_prices p ON p.plan = s.plan
),
expansion AS (
    SELECT
        user_id,
        plan_started_at,
        monthly_price - prev_price AS mrr_added
    FROM customer_history
    WHERE prev_price IS NOT NULL
      AND monthly_price > prev_price
)
SELECT
    DATE_TRUNC('month', plan_started_at) AS month,
    COUNT(*) AS upsells,
    SUM(mrr_added) AS expansion_mrr
FROM expansion
WHERE plan_started_at >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY 1
ORDER BY 1;

По сегментам

SELECT
    u.country,
    COUNT(DISTINCT s.user_id) AS customers,
    COUNT(DISTINCT CASE WHEN was_upgraded THEN s.user_id END) AS upgraded,
    COUNT(DISTINCT CASE WHEN was_upgraded THEN s.user_id END)::NUMERIC * 100
    / NULLIF(COUNT(DISTINCT s.user_id), 0) AS upsell_pct
FROM subscriptions s
JOIN users u USING (user_id)
LEFT JOIN (
    SELECT user_id, TRUE AS was_upgraded
    FROM plan_changes
    WHERE change_type = 'upgrade'
) up USING (user_id)
WHERE s.created_at >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY u.country
ORDER BY upsell_pct DESC;

Time-to-Upsell

WITH upgrades AS (
    SELECT
        user_id,
        MIN(initial_plan_started_at) AS signed_up_at,
        MIN(upgrade_at) AS first_upgrade_at
    FROM subscription_events
    GROUP BY user_id
)
SELECT
    AVG(EXTRACT(EPOCH FROM (first_upgrade_at - signed_up_at)) / 86400) AS avg_days_to_upgrade,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM (first_upgrade_at - signed_up_at)) / 86400) AS median_days
FROM upgrades
WHERE first_upgrade_at IS NOT NULL;

Частые ошибки

Ошибка 1. Upsell vs Cross-sell. Upsell = bigger plan. Cross-sell = additional product. Different motions.

Ошибка 2. Downgrade tracking. Down → Up after few months still counts? Per definition.

Ошибка 3. Plan price changes. Stayed на «pro», но pro got priced higher. Counts? Usually no (плата за same value).

Ошибка 4. Trial → paid не upsell. Trial conversion ≠ upsell. Separate metric.

Ошибка 5. Seats vs tier. Added seats на same tier — это upsell (expansion). Track seats.

Связанные темы

• Как посчитать cross-sell rate в SQL
• Как посчитать NRR в SQL
• Как посчитать MRR в SQL
• Expansion revenue в SaaS

FAQ

Какой Upsell Rate ok?

SaaS: 10-20% annually — good. Hyper-growth: 25%+.

Upsell vs Expansion?

Expansion = umbrella (upsell + cross-sell + seat additions). Upsell — specific (tier up).

Trigger upsell как?

Usage approaches plan limit → soft notification + upsell offer.

Auto-upgrade ok?

Customers hate. Better: clear warning + opt-in.

Negative upsell — что это?

Customer downgrading. Tracked separately (contraction MRR).