Schema Theory

← Back to KPIs & Dashboards

Retention Curve

Description

The Retention Curve shows what percentage of a user cohort continues to be active after signup, measured at regular intervals (e.g., Day 1, Day 7, Day 30). It helps evaluate long-term engagement, product stickiness, and user loyalty.

Recommended category: Customer (Engagement & Retention).

Visual Example

Retention Curve Visual

DAX Example

This example computes Day N retention (% of a signup cohort active N days later). Assume UserActivity with [UserId] and [ActivityDate], and Signups with [UserId] and [SignupDate].

Retention % (Day N) =
VAR N = SELECTEDVALUE('Retention Days'[Day]) -- slicer table with values 1,7,30,...
VAR Cohort = VALUES(Signups[UserId])
VAR SignupsInCohort =
    CALCULATETABLE(
        Signups,
        Cohort
    )
VAR ActiveAtN =
    CALCULATETABLE(
        DISTINCT(UserActivity[UserId]),
        FILTER(
            UserActivity,
            UserActivity[ActivityDate] =
                MAX(Signups[SignupDate]) + N
        )
    )
RETURN
DIVIDE(
    COUNTROWS( INTERSECT(Cohort, ActiveAtN) ),
    COUNTROWS(Cohort)
)

Notes: This pattern works well with a "Retention Days" disconnected table so you can slice by Day 1, 7, 30. You can also build a full curve visual by plotting N on the X-axis and Retention % on the Y-axis.

SQL Example

This SQL calculates retention for cohorts by signup month and retention day.

WITH cohorts AS (
  SELECT
    u.UserId,
    DATE_TRUNC('month', s.SignupDate)::date AS cohort_month,
    s.SignupDate
  FROM Signups s
  JOIN Users u ON u.UserId = s.UserId
),
activity_lag AS (
  SELECT
    c.cohort_month,
    c.UserId,
    DATE_PART('day', a.ActivityDate - c.SignupDate) AS days_since_signup
  FROM cohorts c
  JOIN UserActivity a
    ON a.UserId = c.UserId
   AND a.ActivityDate >= c.SignupDate
),
retention AS (
  SELECT
    cohort_month,
    days_since_signup,
    COUNT(DISTINCT UserId) AS active_users
  FROM activity_lag
  GROUP BY 1,2
)
SELECT
  r.cohort_month,
  r.days_since_signup,
  r.active_users,
  COUNT(DISTINCT c.UserId) AS cohort_size,
  r.active_users::decimal / COUNT(DISTINCT c.UserId) AS retention_rate
FROM retention r
JOIN cohorts c ON r.cohort_month = c.cohort_month
GROUP BY 1,2,r.active_users
ORDER BY 1,2;

Usage Notes

Downloads

Download PBIX Download Sample Data