How to calculate retention rates in Real-Time Analytics: step by step

João Barros 08 de October de 2026 3 min read

Let's build a simple pipeline to compute retention rates in Real-Time Analytics: measure how many users who performed an action on the reference day return on subsequent days. This calculation is essential to assess loyalty, monitor A/B tests and measure the impact of product changes in real time. The basic idea is to form cohorts by the first day of action and then measure, for each relative day (day_index), how many users from that cohort returned. In real scenarios, it is common to observe day 1 retentions in the range of 20–60% depending on the product, and 7-day retentions of 5–30%.

Prerequisites

Streaming event source (for example, Event Hubs, Kafka or other) with events that contain user_id and event_timestamp. Ideally each event also has event_id for deduplication.
Real-Time Analytics platform that accepts streaming queries (for example, Azure Data Explorer / Kusto or another compatible engine). For large volumes consider supports for stateful processing.
Tool to visualize results (Power BI, dashboards or query explorer) that supports aggregated tables and heatmaps for cohorts.

Step 1: Normalize events and create day stamp

It is important to convert timestamps to a consistent day stamp (UTC or a chosen timezone) to compare days. Normalization prevents events near midnight from being placed on different days due to time zones. In the KQL example we transform the raw timestamp into datetime and extract event_day with startofday(). In environments with millions of events/day, perform this transformation as early as possible (at ingestion) to reduce later processing cost.

// Exemplo KQL para normalizar eventos
Events
| where event_type == "action"
| extend event_time = todatetime(event_timestamp)
| extend event_day = startofday(event_time)
| project user_id, event_day

Step 2: Identify the first event day per user (cohort)

To compute retention we need the cohort: the first day when the user performed the action. We group by user_id and take the minimum event_day. In controlled samples, if we have 1,000 users with first action on 2026-10-01, that cohort_day will have cohort_users = 1000. In production, when dealing with millions of users, it is common to use approximations (for example approx_dcount) to reduce cost, accepting an error of 0.5–1%.

// Calcular a coorte (primeiro dia por user)
let FirstDay =
    Events
    | where event_type == "action"
    | extend event_time = todatetime(event_timestamp)
    | extend event_day = startofday(event_time)
    | summarize cohort_day = min(event_day) by user_id;

FirstDay

Step 3: Associate events to the cohort and calculate day difference

We join events with the cohort for each user and compute the relative day (day_index) since the cohort — for example, 0 is the day of the first action, 1 is the next day, etc. This allows aggregating by day_index and seeing the evolution over time. In the example we limit the window to 30 days, but it can be extended to 90 days or 180 days as needed. Be mindful of cost: storing state for many cohorts and long windows increases storage and latency.

// Eventos com day_index relativo à coorte
let EventsWithCohort =
    Events
    | where event_type == "action"
    | extend event_time = todatetime(event_timestamp)
    | extend event_day = startofday(event_time)
    | project user_id, event_day
    | join kind=inner (
        FirstDay
    ) on user_id
    | extend day_index = datetime_diff('day', event_day, cohort_day) * -1 // ou: toint((event_day - cohort_day) / 1d)
    | where day_index >= 0 and day_index <= 30 // limitar janela, ex: 30 dias
    | summarize users_active = dcount(user_id) by cohort_day, day_index;

EventsWithCohort

Step 4: Compute cohort size and daily retention rate

Now we compute the total number of users in the cohort (day_index == 0) and then the percentage that returned at each day_index. A simple approach is to get CohortSize and perform a join; another is to pivot to build a cohort x day_index matrix, useful for heatmaps. Always check that retention_pct at day_index 0 is 100% (or near, if you used aprox_count_distinct).

// Tamanho da coorte (dia 0)
let CohortSize =
    EventsWithCohort
    | where day_index == 0
    | project cohort_day, cohort_users = users_active;

// Retenção percentual por coorte e day_index
EventsWithCohort
| join kind=inner (CohortSize) on cohort_day
| extend retention_pct = todouble(users_active) / todouble(cohort_users) * 100.0
| order by cohort_day, day_index

Step 5: Optimize for Real-Time (incremental windows and state)

For Real-Time Analytics, avoid recomputing everything for each new message. Use incremental windows (tumbling or sliding) and maintain a state with aggregates by (cohort_day, day_index). In solutions like Azure Stream Analytics or Kusto jobs with update policies, process in micro-batches (e.g.: 5s–1min) and update aggregated tables. If you have 10k active cohorts and a 30-day window, you will have up to 300k state combinations—design memory and TTL accordingly.

// Pseudocódigo para atualização incremental (conceptual)
// 1) Ingerir novo lote de eventos
// 2) Calcular event_day e juntar com tabela FirstDay incremental
// 3) Atualizar agregação agreg_table (cohort_day, day_index) com novos user_ids únicos
// 4) Recalcular retention_pct apenas para cohorts afetadas

Practical tips: use deduplication by (user_id, event_id) with TTL to avoid inflated counts; when volume is very high, consider approx_dcount for users_active (acceptable error around 0.5%). For very low latency, maintain a state store (for example, Cosmos DB, KV store) that stores the first day per user_id and small cohort aggregates that are incremented in streaming.

Verify the result

Validate that each cohort_day has a cohort_users (day_index = 0) and that retention_pct is 100% at day_index 0. Test with controlled data: for example, create 1,000 users with events on day 0; simulate that 400 return on day 1 and 180 on day 7. You should obtain retention_pct day1 = 40.0% and day7 = 18.0%. Also check against duplicates: if a user generates 3 events on day 1, they should only be counted once (unique user).

Conclusion

This process enables calculating retention rates in Real-Time Analytics, showing cohorts and retention by day_index for product monitoring. Practical next steps: extend the window to 90 days if you need long-term visibility, segment by properties (country, version, channel) and create visualizations in Power BI (heatmap + trend lines). Remember: deduplication and the choice between exact and approximate counting are key decisions to balance cost, accuracy and latency. Would you like to try segmenting by acquisition channel in a controlled example of 1,000 users to see the impact on retention rates?