Cohort Analysis in 2026: Beyond Redash, Beyond Mixpanel

The cohort heatmap was the killer chart of 2014. It made retention legible to a non-analyst, it surfaced the kind of trend that doesn’t show up in averages, and it became the default visualization for “is the product getting better.” A decade later, every analytics tool in the stack ships a cohort module. The decade also taught us, repeatedly, what the cohort module gets wrong.

This is a working guide to cohort analysis in 2026: what it actually does, how to build it in modern SQL (PostgreSQL, Snowflake, BigQuery), how to ship it as a dbt model that survives audit, and when the tool-built version is misleading you. The article assumes you’ve already read the retention rate piece — cohort analysis sits on top of retention; the failure modes there propagate here.

What a cohort is, precisely

A cohort is a group of users that share a starting event. The starting event is almost always “first session” or “registration,” but it can be anything that anchors a user’s lifecycle: first paid order, first subscription, first activation of a specific feature.

Cohorting is useful because it controls for tenure. Averages across all users mix new and old, and any shift in acquisition mix bleeds into your metric. Cohorts pin tenure to zero for every user in the group, then measure forward.

The dimensions you cohort on are where the real analytical decisions happen. The default — cohorting by signup week — answers “did the product change for new users.” That’s a useful question but not always the most useful one. Stronger cohort dimensions, in rough order of how often they surface a finding worth acting on:

• Acquisition channel — paid search vs organic vs referral cohorts often have retention curves with 2-3x spread
• Activation event reached — users who completed the second-session milestone retain 4-5x better than those who didn’t
• Plan tier or initial spend — paid cohorts behave nothing like free cohorts; mixing them produces averages that describe neither
• Geography — for products with international expansion, country cohorts diverge fast
• Device / platform — iOS vs Android vs web cohorts diverge especially in onboarding completion
• Cohort week / month — the default; useful as a baseline and for tracking longitudinal product changes

Pick the dimension that answers the actual question. “Did retention improve” without a dimension is rarely the right question. “Did retention improve for paid-search users after the new onboarding shipped” is.

The classic heatmap, and what to actually look for

The cohort heatmap shows cohorts as rows (one row per week or month of signups), days or weeks since signup as columns, and retention as the cell color. Bright cells in the upper-right would mean recent cohorts retain better than older ones; dark cells would mean the opposite.

What experienced analysts look at, in order:

1. The first column (day 0 / week 0). Should be 100% by definition. If it isn’t, your cohort definition and your return-event definition disagree. Common cause: cohort defined on registration, return defined on first session, and users register on day 0 but don’t have a session yet.
2. The diagonal frontier. Recent cohorts only have data up to a certain day — that’s the diagonal edge. Don’t compare day-30 retention for a cohort that just turned 30 days old against day-30 retention for a cohort that’s 90 days old; the former has noisier underlying data and the latter has had more time for late-returners.
3. Vertical drift. Reading down a single column (e.g. “day-7 retention”) shows whether cohort-over-cohort retention is improving, stable, or declining at that tenure. This is usually the most actionable read.
4. Horizontal shape. Reading across a single row shows the retention curve for one cohort. The shape matters more than any single number — sharp dropoff in week 1 vs gradual decay vs a “rolling-back” pattern (rare, indicates re-engagement) — each implies a different intervention.
5. Mature cohorts only. Reading from the right side of the heatmap (older cohorts, more tenure) gives a clean signal at higher day_offsets. Recent cohorts on the left look brighter just because they’re young.

The SQL behind the heatmap

The query produces a long table — one row per (cohort, day_offset, retention) — which the heatmap renders. The shape is the same one we used for retention generally; the difference is the cohort dimension is on a weekly or monthly grain.

PostgreSQL, weekly cohorts, daily granularity for the first 30 days:

WITH cohort AS (
  SELECT
    date_trunc('week', registered_at)::date AS cohort_week,
    count(*) AS cohort_size
  FROM users
  WHERE registered_at >= current_date - interval '180 days'
    AND registered_at <  current_date - interval '30 days'
  GROUP BY 1
),
returns AS (
  SELECT
    date_trunc('week', u.registered_at)::date AS cohort_week,
    (date(s.session_at) - date(u.registered_at)) AS day_offset,
    count(DISTINCT u.id) AS returning_users
  FROM users u
  JOIN sessions s ON s.user_id = u.id
  WHERE u.registered_at >= current_date - interval '180 days'
    AND u.registered_at <  current_date - interval '30 days'
    AND (date(s.session_at) - date(u.registered_at)) BETWEEN 0 AND 30
  GROUP BY 1, 2
)
SELECT
  c.cohort_week,
  c.cohort_size,
  r.day_offset,
  r.returning_users,
  round(r.returning_users::numeric / c.cohort_size, 4) AS retention
FROM cohort c
JOIN returns r USING (cohort_week)
ORDER BY 1, 3;

Two things this query does that the typical tool-built version doesn’t:

1. Pins both ends of the cohort window. Cohorts are between 30 and 180 days old, so day-30 retention is computable for every cohort in the result. Mixing immature cohorts (just signed up) with mature ones drops the average artificially for recent cohorts.
2. Truncates to weeks consistently. date_trunc('week', ...) makes the cohort grain explicit. Some BI tools group dates “by week” without specifying which day starts the week, which creates 7-day shifts depending on the user’s locale settings.

For Snowflake, replace date_trunc('week', x)::date with date_trunc('WEEK', x)::DATE and the rest is the same. BigQuery uses DATE_TRUNC(x, WEEK). ClickHouse uses toMonday(x) if you want Monday-anchored weeks; or toStartOfWeek(x, 1) for Sunday-anchored. Pick one and document the choice.

The dbt cohort macro

Once cohort analysis is a recurring need, the SQL belongs in dbt. The pattern is to have a single cohort model parameterized by anchor event, return event, cohort grain, and observation window. The query body is roughly the same as above, but the four parameters are macro variables.

A working shape:

-- models/marts/cohort_retention.sql
{% set anchor_event   = var('cohort_anchor_event',   'registration') %}
{% set return_event   = var('cohort_return_event',   'session') %}
{% set cohort_grain   = var('cohort_grain',          'week') %}
{% set max_day_offset = var('cohort_max_day_offset', 30) %}

WITH anchors AS (
  SELECT user_id, event_at AS anchor_at
  FROM {{ ref('events') }}
  WHERE event_name = '{{ anchor_event }}'
),
returns AS (
  SELECT user_id, event_at AS return_at
  FROM {{ ref('events') }}
  WHERE event_name = '{{ return_event }}'
),
joined AS (
  SELECT
    date_trunc('{{ cohort_grain }}', a.anchor_at)::date AS cohort,
    (date(r.return_at) - date(a.anchor_at))            AS day_offset,
    a.user_id
  FROM anchors a
  LEFT JOIN returns r
    ON r.user_id = a.user_id
   AND r.return_at >= a.anchor_at
   AND (date(r.return_at) - date(a.anchor_at)) <= {{ max_day_offset }}
),
cohort_size AS (
  SELECT cohort, count(DISTINCT user_id) AS cohort_size
  FROM joined
  GROUP BY 1
),
retained AS (
  SELECT cohort, day_offset, count(DISTINCT user_id) AS retained_users
  FROM joined
  WHERE day_offset >= 0
  GROUP BY 1, 2
)
SELECT
  c.cohort,
  c.cohort_size,
  r.day_offset,
  r.retained_users,
  r.retained_users::numeric / c.cohort_size AS retention
FROM cohort_size c
JOIN retained r USING (cohort)
ORDER BY 1, 3

Two upgrades to that base, depending on the project:

• Add a dimension parameter for cohort-stratified output: cohort by (cohort_week, channel), expose the result with the dimension as a column, let the BI layer pivot.
• Move events into staging so the model isn’t pulling from the raw event table directly. Each downstream cohort model is one config away from a different anchor or return event.

Once this is in dbt, the question “what is our day-7 retention by channel for users who registered in March” is a 30-second query against the mart, not a 2-hour project.

Rolling cohorts and other variants

The default cohort heatmap shows classic retention. Two variants come up often enough to be worth implementing alongside:

Rolling cohorts — instead of cohorting by signup week, you cohort by “users active in week W.” Same user can appear in multiple cohorts. The chart shows whether a user active in any given week tends to be active again N weeks later. This is the natural shape for “is engagement sticky” questions on already-active users (e.g. for a B2B SaaS measuring whether power users stay power users).

Reverse cohorts — anchor on a future event (first paid order, first feature usage, churn date) and look backward. “Of users who churned in March, what did their engagement look like in the 30 days before churn?” Reverse cohorting is the right tool for churn diagnostics, and most BI cohort modules don’t support it natively — you’ll need to write it.

Event-anchored cohorts — anchor on a non-time event like “first reached aha-moment.” Combines naturally with funnel analysis: cohort users by which step of the activation funnel they last completed, then measure forward retention from that point. A cohort heatmap stratified by “highest funnel step reached” is one of the highest-information charts you can put in front of a product team.

Tooling in 2026: what each tool gets right and wrong

Redash — the original cohort visualization. Still works, still flexible, still ugly. Best for warehouse-backed cohort analysis where the SQL is the source of truth. Worst for non-analysts; the chart configuration is unforgiving and the heatmap rendering doesn’t scale past about 30 cohort rows.

Metabase — cohort visualization works for simple cases. Doesn’t support custom anchor events directly; you have to pre-shape the data in SQL. Sufficient for “weekly cohorts, daily retention” but limited beyond.

Mixpanel / Amplitude — best-in-class UI for cohort building if your data is in their event format. The catch is in the defaults: cohort definitions and return-event definitions are decisions the tool makes for you, and “default” turns out to mean different things in different parts of the product. We’ve seen the same Amplitude project produce three different cohort retention curves for the same logical question depending on which dropdown the analyst clicked. The fix is governance, not the tool.

Hex / Mode / Deepnote — the modern notebook category is where cohort analysis lives best for analyst-driven projects. You get SQL plus Python in one document, version control, parameterized notebooks for stakeholder self-serve. Cohort analyses that need a chart that doesn’t exist as a pre-built component (rolling cohorts, reverse cohorts, multi-dimensional stratification) belong here.

dbt + a BI tool — the production pattern. dbt model produces the long-form cohort table; BI tool (Looker, Tableau, Metabase, Sigma, anything with a heatmap visualization) renders it. The cohort definition lives in version-controlled SQL, so any change is visible in a PR and not hidden in a BI tool’s metadata.

The product-led-growth playbook of 2020 was “ship cohort dashboards in your event-analytics tool.” The 2026 version is closer to “ship them on a warehouse-backed semantic layer.” The reason is exactly the governance problem: BI-tool defaults drift, dbt models don’t.

When cohort analysis misleads

Cohort analysis is so visually compelling that it lulls teams into trusting it past its useful range. The patterns to watch for:

Cohort sizes too small. A cohort heatmap with weekly cohorts of 50 users each has standard errors on each cell larger than the variation between cells. The eye reads patterns that aren’t statistically there. Below 500-1000 users per cohort, switch to monthly grain or pool cohorts.

Survivorship bias by selection. If you filter the cohort by an event that only happens after registration (e.g. “users who made a purchase”), you’ve conditioned on the outcome and the cohort doesn’t represent your acquisition funnel anymore. The retention curve will look great because you’ve selected the survivors. Acceptable for “what does a high-value cohort look like” questions; misleading for “is our product retaining better” questions.

Looking at the wrong column for the wrong question. Day-7 retention answers a question about onboarding; day-30 answers a question about habit formation; day-90 answers a question about long-term value. Picking the column that looks best in the deck and calling it “our retention” is endemic. Pick the column that matches the decision being made.

Treating brighter as “better” without normalizing for cohort size. A small cohort with three power users can look brighter than a larger cohort with normal mix. Reading the heatmap colors without checking the cohort_size column is the cohort-analysis version of looking at a chart with no y-axis.

A checklist for a cohort analysis that holds up

• Anchor event and return event are named in plain English, in the same place as the chart
• Cohort grain (week / month) is documented, including which day starts the week
• Cohort window has both lower and upper bounds; all cohorts in the chart are mature for the longest day_offset displayed
• Cohort size is shown alongside retention (or available on hover)
• At least one cohort dimension other than time is offered (channel / plan / geography)
• SQL lives in dbt or equivalent, not in a BI tool query
• Heatmap color scale has a defined min and max (not auto-scaled per render — colors should be comparable across time)
• When the chart is on a board deck, the underlying SQL link is one click away

Further reading

Cohort analysis composes naturally with retention metrics (what each cell measures) and LTV models (cohort-stratified LTV is the right unit for marketing decisions). Both are covered in detail elsewhere on this blog:

• Retention Rate: The Metric Everyone Calculates Wrong — anchor events, return events, three flavors, four misuses
• Modeling Customer LTV in SQL: From Naive to Defensible — turning a cohort retention curve into a forecast