如何在 Odoo 中计算客户终身价值 (CLV)

By the end of this recipe, you will compute Customer Lifetime Value (CLV) from your Odoo data — both historical (what each customer has actually spent) and predictive (what they'll likely spend going forward) — at customer level, segment level, and acquisition-channel level. Skill required: SQL fluency + intermediate Power BI. Time required: 3 hours setup, 30 minutes per refresh. ECOSIRE has built this for SaaS, e-commerce, and B2B service clients, and the recipe below is the playbook we ship.

The reason DIY CLV calculations mislead: people use simple "average revenue per customer × average years of relationship" which assumes customers churn linearly. In reality, retention curves are exponential — most churn happens early. The recipe below uses survival analysis to produce realistic predictive CLV that correlates with actual long-term outcomes.

What you will need

• Odoo version: 17, 18, or 19 with Sales + CRM data of at least 24 months.
• Read-only Postgres for SQL.
• Power BI Desktop for visualization.
• Time: 3 hours.

Step-by-step

1. Compute historical CLV per customer

CREATE OR REPLACE VIEW bi.historical_clv AS
SELECT
    rp.id AS partner_id,
    rp.name AS customer_name,
    MIN(so.date_order)::date AS first_order_date,
    MAX(so.date_order)::date AS last_order_date,
    COUNT(DISTINCT so.id) AS total_orders,
    SUM(so.amount_untaxed) AS total_spent,
    AVG(so.amount_untaxed) AS avg_order_value,
    EXTRACT(EPOCH FROM (MAX(so.date_order) - MIN(so.date_order))) / 86400 AS customer_age_days,
    CASE
        WHEN COUNT(DISTINCT so.id) > 1
        THEN EXTRACT(EPOCH FROM (MAX(so.date_order) - MIN(so.date_order))) / 86400 / (COUNT(DISTINCT so.id) - 1)
        ELSE NULL
    END AS avg_days_between_orders
FROM res_partner rp
JOIN sale_order so ON rp.id = so.partner_id
WHERE so.state IN ('sale', 'done')
GROUP BY rp.id, rp.name;

Verification: top customers ranked by total_spent match your CRM team's intuition.

2. Add gross margin to compute "true" CLV

Gross-margin CLV is more meaningful than revenue CLV because revenue isn't profit:

CREATE OR REPLACE VIEW bi.gm_clv AS
SELECT
    h.partner_id,
    h.customer_name,
    h.total_spent,
    h.total_orders,
    -- Pull COGS from sale.order.line × product.standard_price (approximation)
    (SELECT SUM(sol.product_uom_qty * pp.standard_price)
     FROM sale_order so2 JOIN sale_order_line sol ON so2.id = sol.order_id
     JOIN product_product pp ON sol.product_id = pp.id
     WHERE so2.partner_id = h.partner_id AND so2.state IN ('sale','done')) AS total_cogs,
    h.total_spent - (SELECT SUM(sol.product_uom_qty * pp.standard_price)
     FROM sale_order so2 JOIN sale_order_line sol ON so2.id = sol.order_id
     JOIN product_product pp ON sol.product_id = pp.id
     WHERE so2.partner_id = h.partner_id AND so2.state IN ('sale','done')) AS total_gross_margin
FROM bi.historical_clv h;

For more accuracy, use stock.valuation.layer instead of standard_price (see the COGS recipe).

Verification: total_gross_margin is always less than total_spent.

3. Compute predictive CLV using BG/NBD model

The BG/NBD (Beta-Geometric/Negative-Binomial Distribution) model predicts future purchases. The simplified formula:

Expected Future Purchases (1 year) = lambda × (1 - p)^(t/avg_days_between)

Where:

• lambda = average purchase rate (1 / avg_days_between)
• p = probability of churn after a purchase (estimated from cohort data)
• t = forward time horizon (365 days)

In SQL (simplified):

WITH churn_rate AS (
    SELECT 0.10 AS p_churn  -- typical 10% churn per purchase event for B2B; calibrate to your data
)
SELECT
    h.partner_id,
    h.customer_name,
    h.total_spent AS historical_clv,
    h.avg_order_value,
    -- Estimate future orders over next 365 days
    CASE
        WHEN h.avg_days_between_orders IS NOT NULL
        THEN GREATEST(365.0 / h.avg_days_between_orders, 0)
        ELSE 0
    END * (1 - cr.p_churn) AS estimated_future_orders_1yr,
    h.avg_order_value *
        CASE
            WHEN h.avg_days_between_orders IS NOT NULL
            THEN GREATEST(365.0 / h.avg_days_between_orders, 0)
            ELSE 0
        END * (1 - cr.p_churn) AS estimated_future_revenue_1yr,
    h.total_spent +
        h.avg_order_value *
        CASE
            WHEN h.avg_days_between_orders IS NOT NULL
            THEN GREATEST(365.0 / h.avg_days_between_orders, 0)
            ELSE 0
        END * (1 - cr.p_churn) AS predicted_clv
FROM bi.historical_clv h
CROSS JOIN churn_rate cr
WHERE h.total_orders >= 2;  -- Need at least 2 orders for prediction

For sophisticated CLV (true BG/NBD with parameter estimation), use Python's lifetimes library and pull results back into Odoo via a custom field.

Verification: predicted_clv is reasonable — typically 1.5x to 3x historical CLV for active customers.

4. Segment CLV by acquisition channel

SELECT
    so.medium_id,
    um.name AS medium,
    COUNT(DISTINCT h.partner_id) AS customers,
    AVG(h.total_spent) AS avg_clv,
    SUM(h.total_spent) AS total_clv
FROM bi.historical_clv h
JOIN sale_order so ON so.partner_id = h.partner_id AND so.date_order = (
    SELECT MIN(date_order) FROM sale_order WHERE partner_id = h.partner_id
)
JOIN utm_medium um ON so.medium_id = um.id
GROUP BY so.medium_id, um.name
ORDER BY avg_clv DESC;

Verification: organic channels typically have higher CLV than paid; loyalty programs drive even higher.

5. Pair with CAC for ROI

If you know Customer Acquisition Cost (CAC) per channel from your marketing systems:

SELECT
    medium,
    customers,
    avg_clv,
    50 AS avg_cac,  -- pull from your marketing platform
    avg_clv / 50 AS clv_cac_ratio,
    avg_clv - 50 AS contribution_per_customer
FROM (-- previous query --) ...
ORDER BY clv_cac_ratio DESC;

A healthy ratio is 3:1 (CLV is 3x CAC). Below 1:1 you're losing money on acquisition.

Verification: best channels show ratio above 5:1.

6. Build the CLV dashboard

In Power BI:

• Card: average CLV (overall)
• Bar chart: top 10 customers by CLV
• Scatter plot: CLV vs first-order date, sized by total orders
• Matrix: CLV by segment × channel
• Trend line: monthly cohort CLV (rolling)

7. Surface in Odoo

Add a stored compute field on res.partner called clv_estimate:

class ResPartner(models.Model):
    _inherit = 'res.partner'

    clv_estimate = fields.Float(compute='_compute_clv', store=True)

    @api.depends('sale_order_ids.amount_untaxed', 'sale_order_ids.state')
    def _compute_clv(self):
        for p in self:
            self.env.cr.execute("SELECT predicted_clv FROM bi.predictive_clv WHERE partner_id = %s", (p.id,))
            row = self.env.cr.fetchone()
            p.clv_estimate = row[0] if row else 0

Display in CRM lead form so reps see CLV potential when qualifying.

Verification: opening a partner record shows the CLV estimate.

8. Trigger upsell campaigns based on CLV cohorts

VIP cohort (CLV > $10,000): white-glove account management. Mid (CLV $1,000-$10,000): quarterly check-ins. Low (CLV < $1,000): automated nurture only.

Verification: customers with CLV > $10k get a dedicated CSM assignment.

Common mistakes

• Using gross revenue as CLV. Always use gross margin minimum; ideally net contribution after support cost.
• Ignoring discount rate (NPV). Future cash is worth less. For long-horizon CLV (5+ years), apply ~10% discount rate.
• Treating refunded orders as revenue. Subtract refunded amount.
• Computing CLV on a single dimension. CLV varies by segment, channel, geography. Build a multidimensional model.
• Forgetting churn assumption. Without churn, predicted CLV is unrealistically high.

Going further

Cohort-based CLV decay curves: pair with the cohort retention recipe to build cohort-specific CLV. Each cohort gets its own retention curve and its own predicted CLV. Compare across cohorts to detect product-fit improvements (newer cohorts have higher CLV) or fit deterioration (newer cohorts have lower CLV).

Predictive churn scoring: use ML (gradient boosting, neural nets) on customer behavior features to predict churn probability per customer, then compute CLV with personalized churn rate. Features include recency, frequency, monetary value, support tickets, NPS, product usage patterns. ECOSIRE has built this for clients with 80%+ AUC accuracy.

Cross-sell potential: CLV is forward revenue from the customer; cross-sell potential adds expected new product attach. Build a co-purchase model showing which products are most likely to be added to a given customer's basket. Drives recommendation systems.

LTV/CAC by cohort: each cohort has different CAC (rates evolve as channels mature) and different LTV. Track per-cohort to know which marketing investments paid off. The CMO uses this to justify channel mix shifts.

Per-segment CLV multipliers: segment customers (B2B/B2C, industry, deal size) and compute CLV per segment. Targeting acquisition spend at high-CLV segments improves overall economics dramatically.

Customer Health Score integration: pair CLV with operational signals (logins, support volume, NPS) to identify high-CLV-at-risk customers needing immediate attention. Often the single most valuable application of CLV.

Cumulative CLV for portfolio analysis: sum CLV across all active customers as your "Total CLV at risk". Tracks the company's customer asset value over time.

CLV-based pricing: high-CLV customers may justify premium pricing or premium service tiers. Reveals when "VIP" treatment actually pays off.

Refund-adjusted CLV: subtract refunded revenue. Customers who buy and refund repeatedly have negative CLV after support cost. Surface them for proactive intervention.

Discounted CLV (NPV): apply a discount rate (typically 10-15% for SaaS) to future revenue. Gives a more realistic present value estimate. Critical for strategic decisions like LTV-justified retention bonuses.

For full CLV analytics setup including ML-based churn prediction and CAC attribution, ECOSIRE Power BI services build the entire stack. Pair this with how to integrate Mailchimp with Odoo Marketing.

Frequently Asked Questions

Should I use simple historical CLV or predictive?

Both — historical for understanding what's been earned, predictive for forward decisions (e.g., how much to spend on retention).

What discount rate should I apply?

For SaaS, 8-15% (the company's WACC or industry capital cost). For consumer e-commerce, 10-20%. Lower is more aggressive (assumes future cash is more valuable).

How do I handle B2B vs B2C differently?

B2B: company-level CLV. B2C: individual-level CLV. The SQL is the same; just group by commercial_partner_id (the parent company) for B2B.

When should I refresh predictive CLV?

Quarterly is enough for most ops. SaaS companies running CS playbooks refresh monthly.

For complex CLV modeling including ML-driven churn, customer success playbook integration, and revenue forecasting, ECOSIRE Power BI services ship turnkey systems. Or read how to build a customer cohort retention report.