A different way of thinking about retention analysis

TL;DR: If you want to see how your product stickiness is changing over time, you can easily visualize this with a simple SQL query on your events data. You can also find a walkthrough video on our YouTube channel.

Rethinking retention metrics: A better approach for product managers

If you’re a product manager or work in the software space, you’ve probably encountered a cohort retention chart at some point. These charts are the go-to visualization for many product teams tracking user retention and pretty much standard to any SaaS product analytics solutions nowadays (Posthog, Amplitude, Mixpanel…)

However, I believe that in many cases, cohort retention charts are not the best way to think about retention. Especially if you're in the B2B SaaS space, there are alternative ways of visualizing retention that might better capture what you're trying to achieve.

The problem with traditional cohort retention charts

Cohort retention charts typically look something like this:

This type of chart is great if you’re focused on user activation and want to understand how long it takes before a user is considered “converted.” For instance, let’s say you’re working on improving your onboarding flow. You want to see how many users come back after Day 1, Week 1, etc. In this case, a cohort retention chart can help you determine where there might be friction or opportunities to increase the conversion rate from one stage of onboarding to the next.

However, there are many cases where a cohort retention chart falls short—particularly when you want to understand ongoing engagement and not just activation.

Shifting focus to ongoing user engagement

Consider a rolling retention window analysis

In my experience, having worked predominantly in the B2B SaaS space, I am more interested in how user engagement changes over time for existing customers. Often, my goal is to drive existing users back to the product on a more regular basis. I may be introducing a new feature or improving a workflow to enhance ongoing engagement. If this is the objective, the cohort retention chart just doesn’t give me the insight I need.

Instead, I prefer a different type of visualization that allows me to see how often users are coming back, relative to what I consider healthy engagement for that type of user. A chart for this purpose might look something like this:

This chart format is more helpful when I’m trying to understand whether an engaged user is interacting with the product at the frequency that we expect. For example, if an engaged user persona should be interacting with the product every three days or every three weeks, I can visualize whether that engagement is happening on schedule.

This kind of chart also forces me to think about what “regular engagement” actually looks like, especially if that engagement isn’t uniform across all personas. In one of my previous projects, healthy engagement for one user persona was daily use, while for another, it was every other week. A cohort retention chart just can’t provide this level of insight.

Building Custom Retention Visualizations with SQL

While many product analytics tools don't offer this kind of visualization out of the box, you can build it quite easily with SQL—assuming your event data is directly queryable. Some SaaS product analytics platforms provide a query interface that lets you run this type of analysis as well.

Below is an example of how you can use SQL to analyze user engagement over time. This type of query helps you understand when each user is interacting with the product and allows you to build a more flexible visualization.

Here's a sample code snippet:

with weekly_events as (
    select
        user_id,
        DATE_TRUNC('week', event_date) as event_week
    from events_df
    group by
        user_id,
        event_week
),

past_events as (
    select
        we1.user_id,
        we1.event_week,
        COUNT(distinct we2.event_week) as past_event_count
    from weekly_events as we1
    inner join weekly_events as we2
        on
            we1.user_id = we2.user_id
            and we2.event_week between we1.event_week
            - INTERVAL '3 weeks' and we1.event_week
            - INTERVAL '1 week'
    group by
        we1.user_id,
        we1.event_week
)

select
    we.event_week,
    COUNT(distinct we.user_id) as total_users,
    COUNT(distinct pe.user_id) as users_with_past_events,
    (COUNT(distinct pe.user_id) * 100.0 / COUNT(distinct we.user_id))
        as percent_with_past_events
from weekly_events as we
left join past_events as pe
    on
        we.user_id = pe.user_id
        and we.event_week = pe.event_week
group by we.event_week
order by we.event_week;

In this query, the “past_events” is the key since it builds a list of user IDs for each week who have a matching event in a select time window.

This approach lets you create charts that visualize engagement frequency, providing valuable insight into how often users are returning to your product—beyond just whether they came back a day after signing up.

Understanding your North Star metric

It's worth mentioning that tracking engagement isn’t always the right answer. Driving users back to your product just for the sake of engagement can be a misguided strategy and may not contribute to real value for your customers or your business. It’s essential to understand what healthy engagement looks like for your product and ensure that your product changes are aligned with meaningful outcomes.

For many products, engagement might not be the primary north star metric. Instead, you might care more about specific feature adoption, task completion, or some other metric that better represents user success. Blindly pushing for increased engagement can lead to dark patterns and an overall poor user experience—so always consider whether this is the right goal for your product.

Making the most of your retention analysis

If you're using a product analytics platform that provides a SQL interface, try exploring your event data to create more tailored visualizations like the one above. Moving beyond basic cohort retention charts allows you to think critically about how your users engage with your product, leading to more actionable insights and ultimately better decision-making.

At Fabi.ai, we're big proponents of using the right data to answer the right questions—and that sometimes means building custom analyses that go beyond what typical analytics dashboards offer. With tools like SQL, Python NS I integrated into Fabi.ai, we make it easier for analysts and product managers to explore data and build meaningful insights using SQL and Python, without having to leave their primary workflow.

If you're interested in taking your retention analysis to the next level, try Fabi.ai for free and see how our advanced integration capabilities can help you gain deeper insights into user behavior and complement your existing SaaS product analytics stack.