Daily Retention Metrics for Cohort Analysis

Modeling
1

Overview

Daily retention metrics are crucial for understanding user engagement and product stickiness. These are often used in Saas products, gaming and eLearning. This guide demonstrates how to build a comprehensive retention analysis system in Omni by connecting your daily active users table with your user dimension table. Key Metrics to Track

  1. D0: Same-day engagement (multiple sessions on signup day)
  2. D1: Next-day return (critical early engagement indicator)
  3. D7: Weekly retention (product-market fit signal)
  4. D30: Monthly retention (long-term habit formation)

Core Tables Required

  • Daily Active Users Table (daily_active_users): Contains user activity by date
  • User Dimension Table (dim_users): Contains user attributes and first seen dates

Key Relationship

The relationship between these tables is typically established through user_id as the primary key, enabling cohort analysis and retention calculations.

Building the Retention Framework

1. Retention Day Dimension

First, create a dimension to calculate days since first activity:

retention_day:
  sql: DATE_DIFF(${daily_active_users.event_date}, ${dim_users.user_first_seen_date}, DAY)
  description: "Days Since First Activity""

2. Days Since User Signup

Create a helper dimension for eligibility calculations. I’m using curren_date() (assuming Snowflake but swap this out for your database’s corresponding date function).

days_since_user_signup:
  sql: DATE_DIFF(CURRENT_DATE(), ${dim_users.user_first_seen_date}, DAY)
  description: "Days since first activity (from today)"

Core Retention Metrics Implementation

D1 Retention (Day 1)

D1 Retained Users:

d1_retained_users:
  sql: ${daily_active_users.user_id}
  label: D1 Retained Users
  format: ID
  aggregate_type: count_distinct
  filters: 
    retention_day:
      is: 1

D1 Eligible Users:

d1_eligible_users:
  sql: ${daily_active_users.user_id}
  label: D1 Eligible Users
  format: ID
  aggregate_type: count_distinct
  filters: 
    days_since_user_signup:
      greater_than: 0

D1 Retention Rate:

d1_retention_rate:
  sql: 1.0 * ${d1_retained_users}/ NULLIF(${d1_eligible_users},0)
  label: D1 Retention Rate
  description: "Percentage of users who returned on day 1"

Best Practices

1. Data Quality Considerations

  • Always use NULLIF(${eligible_users},0) to prevent division by zero errors
  • Ensure your user_first_seen_date is properly populated in dim_users
  • Use current_date()::date for consistent date calculations

2. Activity Definition Considerations

If you have a specific definition for what constitutes “active” usage (e.g., must perform certain actions, spend minimum time, etc.), consider adding a daily_active_flag to your daily users table and including that in calculations.