Back to Blog
Guides

Measure Cohort Retention Without Complex SQL Queries

SQL-free cohort retention analytics

How to calculate cohort retention rate, read a cohort analysis table and retention curve, and measure retention on your warehouse with SQL or with Mitzu.

Published October 24, 2025 · Updated October 27, 2026
5 min read
Measure Cohort Retention Without Complex SQL Queries

TL;DR

How to calculate cohort retention rate, read a cohort analysis table and retention curve, and measure retention on your warehouse with SQL or with Mitzu. Cohort retention rate is the share of a cohort, a group of users who started in the same period, that is still active a given number of days, weeks or months later.

Cohort retention rate is the share of a cohort, a group of users who started in the same period, that is still active a given number of days, weeks or months later. Formula: retention rate in week N = users from the cohort active in week N ÷ users in the cohort × 100. If 1,000 users signed up in the first week of March and 320 of them were active in week 4, that cohort's week-4 retention rate is 32%. Cohort analysis puts these rates side by side for every signup week, so you can see whether newer cohorts retain better than older ones.

Understanding Cohort Retention: Key to Marketing Success

As a Marketing manager, I know that retention is crucial for success when analyzing campaigns. However, to leverage the data and understand our customers over a period of time, we have to stay informed about our user behavior. Imagine you are developing a Notion-like app where you measure users who sign up for your product and engage with specific features. This user engagement generates events that are ingested into a data warehouse. In this blog post, I'll demonstrate how to analyze cohort retention for your company by comparing three possible options.

What Is Cohort Retention?

But first, let me explain what cohort retention analysis is. Cohort analysis enables us to examine groups of users who share similar traits or experiences over a specific timeframe. In this case, we created a cohort of users who signed up for the platform, and by tracking their activity, we can measure their retention and churn rate. To calculate the cohort retention rate, it is NOT enough to divide the number of users who signed up and the number of users who engaged with the features in the application.

This would result in incorrect retention rates.

Instead, you must keep track of the same users who signed up and were active in the upcoming weeks after signing up. Your product may have active users that are not from the monitored cohorts. These users we should exclude from the calculation.

Tools for Measuring Cohort Retention Rates

Now that we know how to calculate it from the data, the question is: where is the data, and how can we use it to display the retention rate? I’d like to present three cohort analysis tools for you to consider, and you can decide which one would be the best fit for your company:

SQL Queries: Flexibility with Complexity

While SQL written by data analysts offers flexibility and allows for custom queries directly in your data warehouse, it often comes with notable drawbacks.First, the process can be slow and requires skilled data analysts who know how to write complex SQL queries - something that's not easy to find.

Even with the right talent, maintaining these queries can feel like a never-ending chore, often leading to frustration. When it comes to creating visualizations, it’s a whole different headache. Whenever you want to use something or explore your data from a new angle, you’re back to writing new queries and hoping you didn’t make any mistakes along the way. It can be a real hassle!

Third-Party Tools: Quick Insights with Data Management Challenges

Using Third-party tools like Amplitude and Mixpanel have their perks, but they also come with some challenges. They’re fast and user-friendly, with built-in visualizations that make retention analysis really nice. However, you must copy all your data to their platform because they do not use a native data warehouse connection. These tools can get pretty expensive as your needs grow.

Keeping your data in sync is often more work than you’d expect, and you might find yourself stuck with their way of doing things, limiting your flexibility for your own analysis. Third-party tools offer powerful features but may require more time to manage data than actually analyze it. Overall, these tools are useful for quick insights, but they can become less ideal as your data becomes more complex.

Mitzu: Warehouse-Native Analytics for Efficient Retention Analysis

Mitzu is an analytics platform on your data warehouse for BI, product analytics and experiment analysis, where the data team governs the metrics and everyone uses them through dashboards, an AI agent, Slack and their own AI assistants. It queries the warehouse in place, so no data is copied, and pricing does not scale with event volume. I will explain the warehouse-native cohort retention calculation in the following paragraphs.

Step-by-Step Guide to Cohort Retention Analysis with Mitzu

Defining Your Cohort Group for Targeted Analysis

To start our cohort retention analysis, we must define how we'll group the users. For this example, we’re focusing on users who signed up and used the platform between November 1, 2022, and December 31, 2022.

This timeframe is crucial because it allows us to measure weekly engagement changes. We want to see how many of these new users return in the following weeks -week 2, week 3, week 4, and so on.

By measuring this, we can determine whether we’re keeping users engaged after their initial sign-up or losing them along the way. This insight will help us identify trends and areas for improvement to enhance user retention.

Tracking Customer Cohorts Over Time

Once you've defined your cohorts, it's time to track user activity and measure retention. You want to see how many users stay active in the weeks after they first engage with your platform. I'll look at retention rates from week 0 (when they started) all the way to week 8.

This is where a warehouse-native tool helps. You don't write the SQL or copy data out of the warehouse: Mitzu's deterministic query engine generates the SQL on top of your event tables, whether they are raw or modeled in dbt. Select the cohort event, the return event and the timeframe, and the engine handles the cohort bucketing, so every retention chart is computed the same way. The query below shows the pattern on BigQuery.

WITH cohort AS (
  SELECT
    user_id,
    DATE_TRUNC(MIN(event_time), WEEK(MONDAY)) AS cohort_week
  FROM `sample_saas.events`
  WHERE event_name = 'account_created'
  GROUP BY 1
),
activity AS (
  SELECT
    e.user_id,
    DATE_TRUNC(e.event_time, WEEK(MONDAY)) AS activity_week
  FROM `sample_saas.events` e
  WHERE e.event_name = 'dashboard_viewed'
),
retention AS (
  SELECT
    c.cohort_week,
    DATE_DIFF(a.activity_week, c.cohort_week, WEEK) AS week_number,
    COUNT(DISTINCT c.user_id) AS retained_users
  FROM cohort c
  LEFT JOIN activity a ON a.user_id = c.user_id AND a.activity_week >= c.cohort_week
  GROUP BY 1, 2
)
SELECT
  cohort_week,
  week_number,
  retained_users,
  FIRST_VALUE(retained_users) OVER (PARTITION BY cohort_week ORDER BY week_number) AS cohort_size,
  ROUND(
    100.0 * retained_users
    / FIRST_VALUE(retained_users) OVER (PARTITION BY cohort_week ORDER BY week_number),
    2
  ) AS retention_rate_pct
FROM retention
WHERE week_number BETWEEN 0 AND 12
ORDER BY cohort_week, week_number

Interpreting Your Retention Curve

A retention curve usually has three phases: early drop-off, stabilization, and a long-term retained core. Day 1 to Day 7 often captures onboarding friction and whether users hit their first value moment. After that, the curve should flatten as habitual users remain active.

The flattening point is your retention floor, and it matters more than a single vanity checkpoint. As a rule of thumb, many B2C products consider 20-30% Day-30 retention strong, while B2B SaaS often targets 40-60% by Month 3 for successful cohorts. The exact benchmark depends on category and usage frequency.

Segmenting Retention Cohorts

Aggregate retention hides why users stay or churn. Segment by acquisition channel, onboarding completion, plan type, user role, or geography to identify where your product experience performs differently. This is often where actionable insights appear.

SELECT
  CASE
    WHEN acquisition_channel IN ('google_ads', 'bing_ads') THEN 'paid_search'
    WHEN acquisition_channel = 'organic' THEN 'organic'
    ELSE 'other'
  END AS channel_segment,
  cohort_week,
  week_number,
  AVG(retention_rate_pct) AS avg_retention
FROM analytics.retention_cohorts
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3

Using Retention Data to Improve the Product

Retention analysis should feed an iteration loop, not just reporting. If you see a Week 2 drop-off, inspect the final actions churned users completed before leaving, then form a hypothesis about missing value. Run an A/B test on onboarding or activation prompts, and measure whether that specific cohort segment improves in subsequent weeks.

Measuring Retention in Mitzu

In Mitzu, a retention chart takes three choices: a cohort event (for example, Account Created), a return event (for example, Dashboard Viewed) and the time window. Mitzu then generates the warehouse SQL and returns the cohort table and curve. Because the data team defines those events once in Mitzu's semantic layer, the same retention numbers appear on the dashboard, in the agent's answers and next to the revenue KPIs on a KPI dashboard.

Conclusion

Retention is one of the clearest indicators of product-market fit. Measuring it at cohort level and segmenting by user characteristics turns a top-line metric into a decision system for product and growth teams. With warehouse-native tooling like Mitzu, teams can run this analysis directly on governed data without the cost and complexity of duplicative analytics stacks.

Start free on your warehouse, or book a demo to see cohort retention on your own event data.

FAQ

What's the difference between retention rate and churn rate?

Retention rate measures the percentage of users who return in a defined period after cohort entry. Churn rate measures the percentage who do not return in that same period. They are complementary and typically move in opposite directions.

What is cohort analysis?

Cohort analysis groups users by a shared starting point, usually the week or month they signed up, and tracks each group's behavior over the same relative time, such as week 1, week 2 and week 3 after signup. Comparing cohorts row by row shows whether a product change, a pricing change or a new acquisition channel improved retention, which a single blended retention number hides.

How do you calculate retention rate?

For a user cohort, divide the users from the cohort who are active in period N by the cohort's starting size and multiply by 100. For customer retention over a period, the common formula is ((customers at the end − new customers acquired during the period) ÷ customers at the start) × 100. For example, starting with 500 customers, adding 80 and ending with 520 gives ((520 − 80) ÷ 500) × 100 = 88%.

How many users should a cohort have for statistically useful retention?

There is no universal threshold, but very small cohorts create noisy curves and unstable decisions. Many teams use minimum cohort sizes (for example, 100+ users) before comparing segments. Confidence intervals or Bayesian methods can help when volumes are lower.

Should I use calendar-week cohorts or rolling 7-day cohorts?

Calendar-week cohorts are easier to communicate in business reporting and planning cadences. Rolling cohorts are better for experimentation and near-real-time optimization. Most teams use calendar cohorts for executive review and rolling windows for operational diagnostics.

How do I calculate retention in SQL for BigQuery event tables?

Build a cohort table from each user's first qualifying event, then join subsequent activity and compute week offsets with DATE_DIFF. Group by cohort period and offset, then divide retained users by cohort size for each bucket. The SQL example in this post follows that exact pattern.

Key Takeaways

  • How to calculate cohort retention rate, read a cohort analysis table and retention curve, and measure retention on your warehouse with SQL or with Mitzu.

About the Author

Ambrus Pethes

Growth

LinkedIn: https://www.linkedin.com/in/ambrus-pethes-19512b199/

Growth at Mitzu. Expert in data engineering and product analytics.

Share this article

Subscribe to our newsletter

Get the latest insights on product analytics.

Ready to transform your analytics?

See how Mitzu can help you gain deeper insights from your product data.

How to get started with Mitzu

Start analyzing your product data in three simple steps

Connect your data warehouse

Securely connect Mitzu to your existing data warehouse in minutes.

Define your events

Map your product events and user properties with our intuitive interface.

Start analyzing

Create funnels, retention charts, and user journeys without writing SQL.