How to Build a Customer Cohort Retention Report from Odoo

By the end of this recipe, you will have a customer cohort retention matrix computed from Odoo sales data showing what percentage of customers from each acquisition month are still purchasing N months later. The matrix renders as a heatmap in Power BI, exports to Excel for board packs, and alerts when a cohort drops below a defined threshold. Skill required: SQL fluency + intermediate Power BI or Excel. Time required: 3 hours setup, 30 minutes per report run. ECOSIRE has built this for SaaS clients tracking ARR retention and B2C clients tracking repeat-purchase behavior, and the recipe below is the playbook.

The reason this matters: average customer lifetime is a misleading single number. A cohort matrix shows whether retention is improving over time (newer cohorts retain better than older ones — your product is getting stickier) or declining (newer cohorts churn faster — something broke recently). It's the single most actionable retention chart for product and growth teams.

What you will need

• Odoo version: 17, 18, or 19 with Sales + CRM data of at least 12 months.
• Database access: read-only connection to Postgres (see the Power BI integration recipe).
• Power BI Desktop OR Excel 2019+ with PowerQuery.
• Time: 3 hours initial, 30 minutes per refresh.

Step-by-step

1. Define a "purchase" event

Decide what counts as a purchase. For most ECOSIRE clients: a confirmed sale order (state in ('sale', 'done')) with amount_total > 0. For SaaS, use the date of the first invoice in the subscription. For e-commerce, the date of the website order.

Verification: a SQL count of qualifying events matches the marketing team's intuition.

2. Build the cohort base view

In Postgres, create a view that assigns each customer a cohort month (their first purchase) and lists every purchase month thereafter:

CREATE OR REPLACE VIEW bi.customer_cohorts AS
WITH first_purchase AS (
    SELECT
        partner_id,
        DATE_TRUNC('month', MIN(date_order))::date AS cohort_month
    FROM sale_order
    WHERE state IN ('sale', 'done')
      AND amount_total > 0
    GROUP BY partner_id
),
all_purchases AS (
    SELECT
        partner_id,
        DATE_TRUNC('month', date_order)::date AS purchase_month,
        COUNT(*) AS orders_in_month,
        SUM(amount_total) AS revenue_in_month
    FROM sale_order
    WHERE state IN ('sale', 'done')
      AND amount_total > 0
    GROUP BY partner_id, DATE_TRUNC('month', date_order)::date
)
SELECT
    fp.cohort_month,
    fp.partner_id,
    ap.purchase_month,
    EXTRACT(YEAR FROM AGE(ap.purchase_month, fp.cohort_month)) * 12 +
    EXTRACT(MONTH FROM AGE(ap.purchase_month, fp.cohort_month)) AS months_since_cohort,
    ap.orders_in_month,
    ap.revenue_in_month
FROM first_purchase fp
JOIN all_purchases ap USING (partner_id);

Verification: SELECT COUNT(DISTINCT partner_id) FROM bi.customer_cohorts WHERE cohort_month = '2024-01-01' returns the count of customers acquired in Jan 2024.

3. Pivot into a cohort matrix

The retention matrix has cohort_month as rows, months_since_cohort as columns, and "% of original cohort retained" as cell values:

CREATE OR REPLACE VIEW bi.cohort_retention_matrix AS
WITH cohort_size AS (
    SELECT cohort_month, COUNT(DISTINCT partner_id) AS size
    FROM bi.customer_cohorts
    WHERE months_since_cohort = 0
    GROUP BY cohort_month
),
retention AS (
    SELECT
        c.cohort_month,
        c.months_since_cohort,
        COUNT(DISTINCT c.partner_id) AS retained
    FROM bi.customer_cohorts c
    GROUP BY c.cohort_month, c.months_since_cohort
)
SELECT
    r.cohort_month,
    cs.size AS cohort_size,
    r.months_since_cohort,
    r.retained,
    ROUND(100.0 * r.retained / cs.size, 1) AS retention_pct
FROM retention r
JOIN cohort_size cs USING (cohort_month);

Verification: for cohort_month = '2024-01-01' AND months_since_cohort = 0, retention_pct = 100.0 always.

4. Visualize in Power BI

Connect Power BI to the bi.cohort_retention_matrix view (see the Power BI integration recipe). Create a Matrix visual:

• Rows: cohort_month
• Columns: months_since_cohort
• Values: retention_pct (with conditional formatting: green at 100, yellow at 50, red at 20)

Apply a slicer for cohort_month to filter to recent cohorts only. Verification: the matrix shows the classic upper-triangle pattern of retention decay.

5. Add a retention curve overlay

Create a line chart with average retention curve across all cohorts:

Avg Retention =
AVERAGEX(
    VALUES('cohort_retention_matrix'[cohort_month]),
    'cohort_retention_matrix'[retention_pct]
)

Drop with months_since_cohort on X-axis. Verification: a smooth declining curve from 100% at month 0.

6. Compute revenue-weighted retention (advanced)

Customer-count retention can hide cases where one whale leaves. Revenue-weighted retention shows the dollar reality:

CREATE OR REPLACE VIEW bi.cohort_revenue_retention AS
WITH cohort_revenue AS (
    SELECT
        c.cohort_month,
        SUM(c.revenue_in_month) AS m0_revenue
    FROM bi.customer_cohorts c
    WHERE c.months_since_cohort = 0
    GROUP BY c.cohort_month
),
retained_revenue AS (
    SELECT
        c.cohort_month,
        c.months_since_cohort,
        SUM(c.revenue_in_month) AS retained_revenue
    FROM bi.customer_cohorts c
    GROUP BY c.cohort_month, c.months_since_cohort
)
SELECT
    r.cohort_month,
    r.months_since_cohort,
    r.retained_revenue,
    cr.m0_revenue,
    ROUND(100.0 * r.retained_revenue / cr.m0_revenue, 1) AS revenue_retention_pct
FROM retained_revenue r
JOIN cohort_revenue cr USING (cohort_month);

Verification: revenue retention can exceed 100% (NRR) when existing customers expand.

7. Set up churn alerts

Build a Power BI alert: when the most recent cohort's M3 retention drops 10 points below the 12-month rolling average, fire a Slack notification. Use Power Automate or Power BI Service alerts.

Recent M3 vs Avg =
VAR Recent =
    CALCULATE([Avg Retention],
        'cohort_retention_matrix'[months_since_cohort] = 3,
        'cohort_retention_matrix'[cohort_month] = MAX('cohort_retention_matrix'[cohort_month])
    )
VAR Avg12M =
    CALCULATE([Avg Retention],
        'cohort_retention_matrix'[months_since_cohort] = 3,
        'cohort_retention_matrix'[cohort_month] >= MAX('cohort_retention_matrix'[cohort_month]) - 365
    )
RETURN Recent - Avg12M

Verification: the alert fires on a synthetic test where you set M3 retention abnormally low.

8. Export to Excel

Right-click the Matrix visual > Export Data. The Excel export keeps formatting and is the format CFOs prefer for review. Schedule a Power BI subscription to email the report monthly. Verification: monthly email delivers with the matrix attached.

Common mistakes

• Defining "purchase" inconsistently. Pick a clean definition (confirmed SO, paid invoice) and stick with it.
• Ignoring partial cohort exposure. The most recent cohort hasn't had N months to retain through. Truncate the matrix or shade incomplete cells.
• Mixing B2B and B2C cohorts. A retailer's cohort model differs from a SaaS model. Run separately.
• Treating refunds/cancellations as retention. Subtract refunded revenue or you double-count.
• Reporting on too small a base. Cohorts under 30 customers are statistical noise.

Going further

Segmented cohorts: split by acquisition channel (Google, paid social, organic) to find which channel produces stickiest customers. Add utm_source_id to the base view's GROUP BY. The output reveals that organic-search-acquired customers often retain 2-3x better than paid-social ones, which has direct implications for marketing spend allocation.

Predictive retention modeling: feed the matrix into a survival analysis model (Kaplan-Meier) to forecast 12-month retention from M3 data. Python's lifelines library handles the math; export the cohort matrix to a parquet, run the model, write predictions back to Odoo via a custom field. The 90-day predicted retention becomes a forward-looking KPI.

Cohort-based LTV: combine retention with average revenue per retained customer to compute realized LTV per cohort. Multiply each cell of the retention matrix by the cohort's average revenue per retained customer to get a revenue retention matrix, then sum across columns for cohort LTV.

Customer Health Score: pair retention with usage data (logins, support tickets) to produce a forward-looking churn score. Build a feature vector per customer (tenure, recent login frequency, support ticket count, NPS score) and train a binary classifier predicting churn within next 90 days. ECOSIRE has built this for SaaS clients with 80%+ AUC accuracy.

Cohort heatmap with revenue weighting: instead of customer-count retention, color cells by revenue retention. Reveals cases where 50% of customers churning leaves 90% of revenue (whales staying) — a different operational picture than equal-weight retention.

Acquisition-vintage analysis: pair cohort with acquisition cost per cohort. Compute LTV/CAC by cohort. Marketing budgets that produced $2 LTV per $1 CAC look very different from those producing $5 LTV per $1 CAC.

Time-to-value cohort: instead of grouping by acquisition month, group by "time to first feature adoption". Customers who hit value early retain dramatically better. Drives onboarding optimization.

Reactivation cohort: customers who churn and return. Track separately as their retention curve is usually different from first-time customers.

For full retention analytics setup including SQL warehouse, Power BI dashboards, and predictive modeling, ECOSIRE Power BI services build the entire stack. Pair this with how to calculate customer lifetime value in Odoo.

Frequently Asked Questions

Should I use customer-count or revenue retention?

Both. Customer count is the traditional cohort matrix. Revenue retention (NRR / GRR) captures expansion and is the SaaS gold standard. Run both side-by-side. Differences between the two reveal whether revenue is concentrated (whales staying / leaving) or spread (broad customer trends).

What about quarterly or annual cohorts?

Monthly is the default for SaaS and e-commerce. Quarterly cohorts work for low-frequency businesses (annual subscriptions, capital equipment). For cohorts smaller than 30 customers, statistical significance is poor — group up.

How far back should I go?

Five years if you have it. Beyond that the early cohorts are usually too small or too different (the company itself looked nothing like today). For very young companies, six months of data is enough to start spotting patterns.

Can I do this without Power BI?

Yes — Excel pivot tables work fine. The SQL queries are the same; pivot in Excel instead of Power BI. For quick exploration, even Google Sheets with the pivot table feature handles it.

How do I handle freemium / trial customers?

Decide whether trials count as cohort members. If they do, retention drops dramatically (most trials don't convert). If they don't, define cohort by first paid purchase. Most SaaS uses paid-only cohorts.

What if customers come back after a long absence?

Count them in their original cohort. If they were "M0 Jan 2024", a purchase in M14 still counts as M14 retention for the Jan 2024 cohort. Don't reassign.

How do I deal with seasonality?

Seasonal businesses have predictable cyclic dips. Compare same-month-of-year (e.g., Q4 cohorts to Q4 cohorts) instead of consecutive months. Or use seasonality-adjusted retention curves.

What's a healthy retention curve?

SaaS: 90%+ M1, 85%+ M3, 75%+ M12 for stable products. E-commerce: 30-50% M1 (most one-off buyers don't return), 15-25% M12. B2B services: 70-85% M12 for engaged accounts.

Should I include refunded purchases?

No — they shouldn't count as retained behavior. Filter state IN ('sale','done') AND exclude orders fully refunded.

How do I attribute retention to specific actions?

Compare cohorts before and after a major change (new feature launch, pricing tier change, onboarding redesign). Differences in retention curves attribute to the change.

Can I forecast LTV from cohort data?

Yes — sum the expected revenue across all forward periods, weighted by retention probability. Apply a discount rate (10-15%) to future revenue. The result is a predicted LTV.

What's the difference between gross and net retention?

Gross retention: percent of customers who stay (or revenue from them). Net retention adds expansion revenue (upsells, cross-sells) — can exceed 100%. Net dollar retention (NDR) is the SaaS gold-standard.

For complex retention analytics including AI-driven churn prediction and customer-success playbooks, ECOSIRE Power BI services ship turnkey dashboards. Or read how to track customer lifetime value for the dollar-impact view.