Returning Learner Rate

Also known as: Repeat Learner Rate, Returning Student Rate, Repeat Active Learner Percentage, Returning User Learning Rate, Existing Learner Share

RetentionmetricEdTech & E-Learning

Measures the percentage of distinct active learners in a reporting period who completed at least one qualifying learning activity before that period began. It quantifies the share of current learning engagement generated by previously active learners rather than first-time learners.

Business Use Case

The Product department reviews the monthly rate by acquisition cohort, course category, subscription plan, and platform. If the rate declines while new learner volume remains stable, product leaders can investigate content relevance, learning-path continuity, reminder effectiveness, or platform friction and prioritize lifecycle messaging, course recommendations, or curriculum improvements for previously active learners.

Business Criticality

High

Journey / Navigation Area

Engagement

Owning Function

Product

Scope

User

Formula

100 * COUNT(DISTINCT CASE WHEN qualifying_activity_timestamp >= period_start AND qualifying_activity_timestamp < period_end AND first_qualifying_activity_timestamp < period_start THEN learner_id END) / NULLIF(COUNT(DISTINCT CASE WHEN qualifying_activity_timestamp >= period_start AND qualifying_activity_timestamp < period_end THEN learner_id END), 0)

Calculation Notes

1️⃣ Calculate for each half-open reporting window [period_start, period_end). 2️⃣ Count a learner in the denominator if they have at least one qualifying activity during the window. 3️⃣ Count a learner in the numerator if they have a qualifying activity timestamp before period_start. 4️⃣ Use the first-ever reliable qualifying activity timestamp; exclude non-qualifying activities. 5️⃣ Exclude anonymous, bot, internal, test accounts, and canceled/sandbox enrollment activities. 6️⃣ Return null instead of zero when the denominator is zero. 7️⃣ Recalculate historical periods for late events or identity merges affecting timestamps or populations.

Technical Details

Measure Typepercentage

Aggregation WindowTime based - Daily/Weekly/Monthly

Events

Platform	Event
Google Analytics 4	select_content
Adobe	select_content

Data Mappings

W3 Data Layer

{
  "digitalData": {
    "event": [
      {
        "eventInfo": {
          "eventId": "evt_01JQZ8S4X6W2K9M3R7T5V1N0CP",
          "eventDate": "2025-04-15",
          "eventName": "select_content",
          "eventTimestamp": "2025-04-15T14:30:00Z"
        },
        "attributes": {
          "channel": "web",
          "country": "US",
          "courseId": "course_2044",
          "language": "en-US",
          "platform": "lms_web",
          "learnerId": "learner_7f3a91c2",
          "programId": "program_300",
          "sessionId": "session_1744727400",
          "courseName": "Data Privacy Fundamentals",
          "eventSource": "digital_analytics",
          "deliveryMode": "online",
          "enrollmentId": "enrollment_88319",
          "pageLocation": "https://learning.example.com/courses/course_2044",
          "pageReferrer": "https://learning.example.com/dashboard",
          "sourceSystem": "learning_management_system",
          "userPseudoId": "pseudo_4b8d20e6",
          "learnerStatus": "active",
          "sessionNumber": 12,
          "courseCategory": "compliance",
          "organizationId": "org_1001",
          "consentAnalytics": true,
          "enrollmentStatus": "active",
          "returningLearner": true,
          "currentActivityId": "activity_2044",
          "dataQualityStatus": "validated",
          "identityNamespace": "LMS_LEARNER_ID",
          "reportingTimeZone": "UTC",
          "reportingPeriodEnd": "2025-04-30T23:59:59Z",
          "currentActivityName": "Data Privacy Fundamentals",
          "currentActivityType": "course",
          "reportingPeriodStart": "2025-04-01T00:00:00Z",
          "currentActivityStatus": "selected",
          "identityAuthenticated": true,
          "firstActivityTimestamp": "2024-11-08T10:15:00Z",
          "activeInReportingPeriod": true,
          "currentActivityQualifies": true,
          "daysSincePriorCompletion": 90,
          "identityResolutionStatus": "resolved",
          "priorQualifyingActivityId": "activity_1028",
          "priorCompletionBeforePeriod": true,
          "priorQualifyingActivityType": "course",
          "priorQualifyingCompletionDate": "2025-01-15",
          "priorQualifyingActivityCompleted": true,
          "currentActivityCompletionTimestamp": null,
          "priorQualifyingCompletionTimestamp": "2025-01-15T16:42:18Z"
        }
      }
    ]
  }
}

GA4 Data Layer

{
  "event": "select_content",
  "item_id": "activity_2044",
  "content_type": "learning_activity",
  "learner_context": {
    "channel": "web",
    "country": "US",
    "event_id": "evt_01JQZ8S4X6W2K9M3R7T5V1N0CP",
    "language": "en-US",
    "platform": "lms_web",
    "course_id": "course_2044",
    "event_date": "2025-04-15",
    "event_name": "select_content",
    "learner_id": "learner_7f3a91c2",
    "program_id": "program_300",
    "session_id": "session_1744727400",
    "course_name": "Data Privacy Fundamentals",
    "event_source": "digital_analytics",
    "delivery_mode": "online",
    "enrollment_id": "enrollment_88319",
    "page_location": "https://learning.example.com/courses/course_2044",
    "page_referrer": "https://learning.example.com/dashboard",
    "source_system": "learning_management_system",
    "learner_status": "active",
    "session_number": 12,
    "user_pseudo_id": "pseudo_4b8d20e6",
    "course_category": "compliance",
    "event_timestamp": "2025-04-15T14:30:00Z",
    "organization_id": "org_1001",
    "consent_analytics": true,
    "enrollment_status": "active",
    "returning_learner": true,
    "identity_namespace": "LMS_LEARNER_ID",
    "current_activity_id": "activity_2044",
    "data_quality_status": "validated",
    "reporting_time_zone": "UTC",
    "reporting_period_end": "2025-04-30T23:59:59Z",
    "current_activity_name": "Data Privacy Fundamentals",
    "current_activity_type": "course",
    "identity_authenticated": true,
    "reporting_period_start": "2025-04-01T00:00:00Z",
    "current_activity_status": "selected",
    "first_activity_timestamp": "2024-11-08T10:15:00Z",
    "active_in_reporting_period": true,
    "current_activity_qualifies": true,
    "identity_resolution_status": "resolved",
    "days_since_prior_completion": 90,
    "prior_qualifying_activity_id": "activity_1028",
    "prior_completion_before_period": true,
    "prior_qualifying_activity_type": "course",
    "prior_qualifying_completion_date": "2025-01-15",
    "prior_qualifying_activity_completed": true,
    "current_activity_completion_timestamp": null,
    "prior_qualifying_completion_timestamp": "2025-01-15T16:42:18Z"
  }
}

Adobe Client Data Layer

{
  "data": {
    "content": {
      "contentId": "activity_2044",
      "contentType": "learning_activity"
    },
    "learnerContext": {
      "channel": "web",
      "country": "US",
      "eventId": "evt_01JQZ8S4X6W2K9M3R7T5V1N0CP",
      "courseId": "course_2044",
      "language": "en-US",
      "platform": "lms_web",
      "eventDate": "2025-04-15",
      "eventName": "select_content",
      "learnerId": "learner_7f3a91c2",
      "programId": "program_300",
      "sessionId": "session_1744727400",
      "courseName": "Data Privacy Fundamentals",
      "eventSource": "digital_analytics",
      "deliveryMode": "online",
      "enrollmentId": "enrollment_88319",
      "pageLocation": "https://learning.example.com/courses/course_2044",
      "pageReferrer": "https://learning.example.com/dashboard",
      "sourceSystem": "learning_management_system",
      "userPseudoId": "pseudo_4b8d20e6",
      "learnerStatus": "active",
      "sessionNumber": 12,
      "courseCategory": "compliance",
      "eventTimestamp": "2025-04-15T14:30:00Z",
      "organizationId": "org_1001",
      "consentAnalytics": true,
      "enrollmentStatus": "active",
      "returningLearner": true,
      "currentActivityId": "activity_2044",
      "dataQualityStatus": "validated",
      "identityNamespace": "LMS_LEARNER_ID",
      "reportingTimeZone": "UTC",
      "reportingPeriodEnd": "2025-04-30T23:59:59Z",
      "currentActivityName": "Data Privacy Fundamentals",
      "currentActivityType": "course",
      "reportingPeriodStart": "2025-04-01T00:00:00Z",
      "currentActivityStatus": "selected",
      "identityAuthenticated": true,
      "firstActivityTimestamp": "2024-11-08T10:15:00Z",
      "activeInReportingPeriod": true,
      "currentActivityQualifies": true,
      "daysSincePriorCompletion": 90,
      "identityResolutionStatus": "resolved",
      "priorQualifyingActivityId": "activity_1028",
      "priorCompletionBeforePeriod": true,
      "priorQualifyingActivityType": "course",
      "priorQualifyingCompletionDate": "2025-01-15",
      "priorQualifyingActivityCompleted": true,
      "currentActivityCompletionTimestamp": null,
      "priorQualifyingCompletionTimestamp": "2025-01-15T16:42:18Z"
    }
  },
  "event": "select_content"
}

XDM Mapping

{
  "_id": "evt_01JQZ8S4X6W2K9M3R7T5V1N0CP",
  "web": {
    "webInteraction": {
      "URL": "https://learning.example.com/courses/course_2044",
      "name": "select_content",
      "type": "other",
      "linkClicks": {
        "value": 1
      }
    },
    "webPageDetails": {
      "URL": "https://learning.example.com/courses/course_2044",
      "referrer": "https://learning.example.com/dashboard"
    }
  },
  "_learning": {
    "learnerContext": {
      "channel": "web",
      "country": "US",
      "eventId": "evt_01JQZ8S4X6W2K9M3R7T5V1N0CP",
      "courseId": "course_2044",
      "language": "en-US",
      "platform": "lms_web",
      "eventDate": "2025-04-15",
      "eventName": "select_content",
      "learnerId": "learner_7f3a91c2",
      "programId": "program_300",
      "sessionId": "session_1744727400",
      "courseName": "Data Privacy Fundamentals",
      "eventSource": "digital_analytics",
      "deliveryMode": "online",
      "enrollmentId": "enrollment_88319",
      "pageLocation": "https://learning.example.com/courses/course_2044",
      "pageReferrer": "https://learning.example.com/dashboard",
      "sourceSystem": "learning_management_system",
      "userPseudoId": "pseudo_4b8d20e6",
      "learnerStatus": "active",
      "sessionNumber": 12,
      "courseCategory": "compliance",
      "eventTimestamp": "2025-04-15T14:30:00Z",
      "organizationId": "org_1001",
      "consentAnalytics": true,
      "enrollmentStatus": "active",
      "returningLearner": true,
      "currentActivityId": "activity_2044",
      "dataQualityStatus": "validated",
      "identityNamespace": "LMS_LEARNER_ID",
      "reportingTimeZone": "UTC",
      "reportingPeriodEnd": "2025-04-30T23:59:59Z",
      "currentActivityName": "Data Privacy Fundamentals",
      "currentActivityType": "course",
      "reportingPeriodStart": "2025-04-01T00:00:00Z",
      "currentActivityStatus": "selected",
      "identityAuthenticated": true,
      "firstActivityTimestamp": "2024-11-08T10:15:00Z",
      "activeInReportingPeriod": true,
      "currentActivityQualifies": true,
      "daysSincePriorCompletion": 90,
      "identityResolutionStatus": "resolved",
      "priorQualifyingActivityId": "activity_1028",
      "priorCompletionBeforePeriod": true,
      "priorQualifyingActivityType": "course",
      "priorQualifyingCompletionDate": "2025-01-15",
      "priorQualifyingActivityCompleted": true,
      "currentActivityCompletionTimestamp": null,
      "priorQualifyingCompletionTimestamp": "2025-01-15T16:42:18Z"
    }
  },
  "eventType": "web.webinteraction.linkClicks",
  "timestamp": "2025-04-15T14:30:00Z",
  "environment": {
    "type": "browser",
    "language": "en-US"
  },
  "identityMap": {
    "ECID": [
      {
        "id": "pseudo_4b8d20e6",
        "primary": false,
        "authenticatedState": "ambiguous"
      }
    ],
    "LMS_LEARNER_ID": [
      {
        "id": "learner_7f3a91c2",
        "primary": true,
        "authenticatedState": "authenticated"
      }
    ]
  },
  "placeContext": {
    "geo": {
      "countryCode": "US"
    },
    "localTime": "2025-04-15T14:30:00",
    "localTimezoneOffset": 0
  }
}

SQL Queries

Google Analytics 4 (BigQuery)

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
WITH reporting_period AS (
  SELECT
    TIMESTAMP('2025-04-01 00:00:00+00') AS period_start,
    TIMESTAMP('2025-05-01 00:00:00+00') AS period_end_exclusive,
    'UTC' AS reporting_time_zone
),
parameterized_events AS (
  SELECT
    e.event_name,
    TIMESTAMP_MICROS(e.event_timestamp) AS event_timestamp,
    PARSE_DATE('%Y%m%d', e.event_date) AS event_date,
    MAX(IF(ep.key = 'event_id', ep.value.string_value, NULL)) AS event_id,
    MAX(IF(ep.key = 'reporting_period_start', ep.value.string_value, NULL)) AS reporting_period_start,
    MAX(IF(ep.key = 'reporting_period_end', ep.value.string_value, NULL)) AS reporting_period_end,
    MAX(IF(ep.key = 'reporting_time_zone', ep.value.string_value, NULL)) AS reporting_time_zone,
    MAX(IF(ep.key = 'learner_id', ep.value.string_value, NULL)) AS learner_id,
    COALESCE(
      MAX(IF(ep.key = 'user_pseudo_id', ep.value.string_value, NULL)),
      e.user_pseudo_id
    ) AS user_pseudo_id,
    MAX(IF(ep.key = 'identity_namespace', ep.value.string_value, NULL)) AS identity_namespace,
    MAX(IF(ep.key = 'identity_authenticated', COALESCE(SAFE_CAST(ep.value.int_value AS BOOL), SAFE_CAST(ep.value.string_value AS BOOL)), NULL)) AS identity_authenticated,
    MAX(IF(ep.key = 'identity_resolution_status', ep.value.string_value, NULL)) AS identity_resolution_status,
    MAX(IF(ep.key = 'learner_status', ep.value.string_value, NULL)) AS learner_status,
    MAX(IF(ep.key = 'returning_learner', COALESCE(SAFE_CAST(ep.value.int_value AS BOOL), SAFE_CAST(ep.value.string_value AS BOOL)), NULL)) AS supplied_returning_learner,
    MAX(IF(ep.key = 'active_in_reporting_period', COALESCE(SAFE_CAST(ep.value.int_value AS BOOL), SAFE_CAST(ep.value.string_value AS BOOL)), NULL)) AS active_in_reporting_period,
    MAX(IF(ep.key = 'prior_qualifying_activity_completed', COALESCE(SAFE_CAST(ep.value.int_value AS BOOL), SAFE_CAST(ep.value.string_value AS BOOL)), NULL)) AS prior_qualifying_activity_completed,
    MAX(IF(ep.key = 'prior_qualifying_activity_id', ep.value.string_value, NULL)) AS prior_qualifying_activity_id,
    MAX(IF(ep.key = 'prior_qualifying_activity_type', ep.value.string_value, NULL)) AS prior_qualifying_activity_type,
    MAX(IF(ep.key = 'prior_qualifying_completion_timestamp', ep.value.string_value, NULL)) AS prior_qualifying_completion_timestamp,
    MAX(IF(ep.key = 'prior_qualifying_completion_date', ep.value.string_value, NULL)) AS prior_qualifying_completion_date,
    MAX(IF(ep.key = 'prior_completion_before_period', COALESCE(SAFE_CAST(ep.value.int_value AS BOOL), SAFE_CAST(ep.value.string_value AS BOOL)), NULL)) AS prior_completion_before_period,
    MAX(IF(ep.key = 'first_activity_timestamp', ep.value.string_value, NULL)) AS first_activity_timestamp,
    MAX(IF(ep.key = 'days_since_prior_completion', COALESCE(ep.value.int_value, SAFE_CAST(ep.value.string_value AS INT64)), NULL)) AS days_since_prior_completion,
    MAX(IF(ep.key = 'current_activity_id', ep.value.string_value, NULL)) AS current_activity_id,
    MAX(IF(ep.key = 'current_activity_type', ep.value.string_value, NULL)) AS current_activity_type,
    MAX(IF(ep.key = 'current_activity_name', ep.value.string_value, NULL)) AS current_activity_name,
    MAX(IF(ep.key = 'current_activity_status', ep.value.string_value, NULL)) AS current_activity_status,
    MAX(IF(ep.key = 'current_activity_qualifies', COALESCE(SAFE_CAST(ep.value.int_value AS BOOL), SAFE_CAST(ep.value.string_value AS BOOL)), NULL)) AS current_activity_qualifies,
    MAX(IF(ep.key = 'current_activity_completion_timestamp', ep.value.string_value, NULL)) AS current_activity_completion_timestamp,
    MAX(IF(ep.key = 'session_id', COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING)), NULL)) AS session_id,
    MAX(IF(ep.key = 'session_number', COALESCE(ep.value.int_value, SAFE_CAST(ep.value.string_value AS INT64)), NULL)) AS session_number,
    MAX(IF(ep.key = 'enrollment_id', ep.value.string_value, NULL)) AS enrollment_id,
    MAX(IF(ep.key = 'enrollment_status', ep.value.string_value, NULL)) AS enrollment_status,
    MAX(IF(ep.key = 'course_id', ep.value.string_value, NULL)) AS course_id,
    MAX(IF(ep.key = 'course_name', ep.value.string_value, NULL)) AS course_name,
    MAX(IF(ep.key = 'course_category', ep.value.string_value, NULL)) AS course_category,
    MAX(IF(ep.key = 'program_id', ep.value.string_value, NULL)) AS program_id,
    MAX(IF(ep.key = 'delivery_mode', ep.value.string_value, NULL)) AS delivery_mode,
    MAX(IF(ep.key = 'platform', ep.value.string_value, NULL)) AS platform,
    MAX(IF(ep.key = 'source_system', ep.value.string_value, NULL)) AS source_system,
    MAX(IF(ep.key = 'event_source', ep.value.string_value, NULL)) AS event_source,
    MAX(IF(ep.key = 'channel', ep.value.string_value, NULL)) AS channel,
    MAX(IF(ep.key = 'page_location', ep.value.string_value, NULL)) AS page_location,
    MAX(IF(ep.key = 'page_referrer', ep.value.string_value, NULL)) AS page_referrer,
    MAX(IF(ep.key = 'language', ep.value.string_value, NULL)) AS language,
    MAX(IF(ep.key = 'country', ep.value.string_value, NULL)) AS country,
    MAX(IF(ep.key = 'organization_id', ep.value.string_value, NULL)) AS organization_id,
    MAX(IF(ep.key = 'consent_analytics', COALESCE(SAFE_CAST(ep.value.int_value AS BOOL), SAFE_CAST(ep.value.string_value AS BOOL)), NULL)) AS consent_analytics,
    MAX(IF(ep.key = 'data_quality_status', ep.value.string_value, NULL)) AS data_quality_status
  FROM `your-project.analytics_123456789.events_*` AS e
  CROSS JOIN reporting_period AS rp
  LEFT JOIN UNNEST(e.event_params) AS ep
  WHERE REGEXP_CONTAINS(_TABLE_SUFFIX, r'^\d{8}$')
    AND PARSE_DATE('%Y%m%d', _TABLE_SUFFIX) < DATE(rp.period_end_exclusive)
  GROUP BY
    e.event_name,
    e.event_timestamp,
    e.event_date,
    e.user_pseudo_id
),
deduplicated_events AS (
  SELECT * EXCEPT(row_number)
  FROM (
    SELECT
      pe.*,
      ROW_NUMBER() OVER (
        PARTITION BY COALESCE(event_id, CONCAT(user_pseudo_id, '|', CAST(event_timestamp AS STRING), '|', event_name))
        ORDER BY event_timestamp
      ) AS row_number
    FROM parameterized_events AS pe
  )
  WHERE row_number = 1
),
accepted_events AS (
  SELECT e.*
  FROM deduplicated_events AS e
  WHERE e.learner_id IS NOT NULL
    AND e.identity_namespace = 'LMS_LEARNER_ID'
    AND e.identity_authenticated IS TRUE
    AND e.identity_resolution_status = 'resolved'
    AND e.consent_analytics IS TRUE
    AND e.data_quality_status = 'validated'
),
active_learners AS (
  SELECT DISTINCT
    rp.period_start,
    rp.period_end_exclusive,
    rp.reporting_time_zone,
    e.organization_id,
    e.learner_id
  FROM accepted_events AS e
  CROSS JOIN reporting_period AS rp
  WHERE e.event_timestamp >= rp.period_start
    AND e.event_timestamp < rp.period_end_exclusive
    AND e.learner_status = 'active'
    AND e.active_in_reporting_period IS TRUE
    AND e.current_activity_qualifies IS TRUE
),
qualifying_completions AS (
  SELECT DISTINCT
    e.learner_id
  FROM accepted_events AS e
  CROSS JOIN reporting_period AS rp
  WHERE e.current_activity_qualifies IS TRUE
    AND LOWER(e.current_activity_status) = 'completed'
    AND COALESCE(
          SAFE_CAST(e.current_activity_completion_timestamp AS TIMESTAMP),
          e.event_timestamp
        ) < rp.period_start

  UNION DISTINCT

  SELECT DISTINCT
    e.learner_id
  FROM accepted_events AS e
  CROSS JOIN reporting_period AS rp
  WHERE e.prior_qualifying_activity_completed IS TRUE
    AND SAFE_CAST(e.prior_qualifying_completion_timestamp AS TIMESTAMP) < rp.period_start
),
learner_population AS (
  SELECT
    a.period_start,
    a.period_end_exclusive,
    a.reporting_time_zone,
    a.organization_id,
    a.learner_id,
    q.learner_id IS NOT NULL AS is_returning_learner
  FROM active_learners AS a
  LEFT JOIN qualifying_completions AS q
    ON a.learner_id = q.learner_id
)
SELECT
  period_start AS reporting_period_start,
  TIMESTAMP_SUB(period_end_exclusive, INTERVAL 1 MICROSECOND) AS reporting_period_end,
  reporting_time_zone,
  organization_id,
  COUNT(*) AS total_active_learners,
  COUNTIF(is_returning_learner) AS returning_learners,
  SAFE_DIVIDE(COUNTIF(is_returning_learner), COUNT(*)) AS returning_learner_rate,
  100 * SAFE_DIVIDE(COUNTIF(is_returning_learner), COUNT(*)) AS returning_learner_rate_percent
FROM learner_population
GROUP BY
  reporting_period_start,
  reporting_period_end,
  reporting_time_zone,
  organization_id
ORDER BY organization_id;

Adobe Experience Platform (AEP Query Service)

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
WITH reporting_period AS (
  SELECT
    CAST('2025-04-01 00:00:00+00' AS TIMESTAMP) AS period_start,
    CAST('2025-05-01 00:00:00+00' AS TIMESTAMP) AS period_end_exclusive,
    'UTC' AS reporting_time_zone
),
raw_events AS (
  SELECT
    _id AS event_id,
    eventType AS event_name,
    timestamp AS event_timestamp,
    CAST(timestamp AS DATE) AS event_date,
    CAST(_learning.learnerContext.reportingPeriodStart AS TIMESTAMP) AS reporting_period_start,
    CAST(_learning.learnerContext.reportingPeriodEnd AS TIMESTAMP) AS reporting_period_end,
    _learning.learnerContext.reportingTimeZone AS reporting_time_zone,
    _learning.learnerContext.learnerId AS learner_id,
    _learning.learnerContext.userPseudoId AS user_pseudo_id,
    _learning.learnerContext.identityNamespace AS identity_namespace,
    _learning.learnerContext.identityAuthenticated AS identity_authenticated,
    _learning.learnerContext.identityResolutionStatus AS identity_resolution_status,
    _learning.learnerContext.learnerStatus AS learner_status,
    _learning.learnerContext.returningLearner AS supplied_returning_learner,
    _learning.learnerContext.activeInReportingPeriod AS active_in_reporting_period,
    _learning.learnerContext.priorQualifyingActivityCompleted AS prior_qualifying_activity_completed,
    _learning.learnerContext.priorQualifyingActivityId AS prior_qualifying_activity_id,
    _learning.learnerContext.priorQualifyingActivityType AS prior_qualifying_activity_type,
    CAST(_learning.learnerContext.priorQualifyingCompletionTimestamp AS TIMESTAMP) AS prior_qualifying_completion_timestamp,
    CAST(_learning.learnerContext.priorQualifyingCompletionDate AS DATE) AS prior_qualifying_completion_date,
    _learning.learnerContext.priorCompletionBeforePeriod AS prior_completion_before_period,
    CAST(_learning.learnerContext.firstActivityTimestamp AS TIMESTAMP) AS first_activity_timestamp,
    _learning.learnerContext.daysSincePriorCompletion AS days_since_prior_completion,
    _learning.learnerContext.currentActivityId AS current_activity_id,
    _learning.learnerContext.currentActivityType AS current_activity_type,
    _learning.learnerContext.currentActivityName AS current_activity_name,
    _learning.learnerContext.currentActivityStatus AS current_activity_status,
    _learning.learnerContext.currentActivityQualifies AS current_activity_qualifies,
    CAST(_learning.learnerContext.currentActivityCompletionTimestamp AS TIMESTAMP) AS current_activity_completion_timestamp,
    _learning.learnerContext.sessionId AS session_id,
    _learning.learnerContext.sessionNumber AS session_number,
    _learning.learnerContext.enrollmentId AS enrollment_id,
    _learning.learnerContext.enrollmentStatus AS enrollment_status,
    _learning.learnerContext.courseId AS course_id,
    _learning.learnerContext.courseName AS course_name,
    _learning.learnerContext.courseCategory AS course_category,
    _learning.learnerContext.programId AS program_id,
    _learning.learnerContext.deliveryMode AS delivery_mode,
    _learning.learnerContext.platform AS platform,
    _learning.learnerContext.sourceSystem AS source_system,
    _learning.learnerContext.eventSource AS event_source,
    _learning.learnerContext.channel AS channel,
    _learning.learnerContext.pageLocation AS page_location,
    _learning.learnerContext.pageReferrer AS page_referrer,
    _learning.learnerContext.language AS language,
    _learning.learnerContext.country AS country,
    _learning.learnerContext.organizationId AS organization_id,
    _learning.learnerContext.consentAnalytics AS consent_analytics,
    _learning.learnerContext.dataQualityStatus AS data_quality_status
  FROM aep_learning_analytics_dataset
  CROSS JOIN reporting_period rp
  WHERE timestamp < rp.period_end_exclusive
),
deduplicated_events AS (
  SELECT *
  FROM (
    SELECT
      r.*,
      ROW_NUMBER() OVER (
        PARTITION BY COALESCE(event_id, CONCAT(COALESCE(user_pseudo_id, ''), '|', CAST(event_timestamp AS VARCHAR), '|', event_name))
        ORDER BY event_timestamp
      ) AS row_number
    FROM raw_events r
  ) d
  WHERE row_number = 1
),
accepted_events AS (
  SELECT e.*
  FROM deduplicated_events e
  WHERE e.learner_id IS NOT NULL
    AND e.identity_namespace = 'LMS_LEARNER_ID'
    AND e.identity_authenticated = TRUE
    AND e.identity_resolution_status = 'resolved'
    AND e.consent_analytics = TRUE
    AND e.data_quality_status = 'validated'
),
active_learners AS (
  SELECT DISTINCT
    rp.period_start,
    rp.period_end_exclusive,
    rp.reporting_time_zone,
    e.organization_id,
    e.learner_id
  FROM accepted_events e
  CROSS JOIN reporting_period rp
  WHERE e.event_timestamp >= rp.period_start
    AND e.event_timestamp < rp.period_end_exclusive
    AND e.learner_status = 'active'
    AND e.active_in_reporting_period = TRUE
    AND e.current_activity_qualifies = TRUE
),
qualifying_completions AS (
  SELECT DISTINCT
    e.learner_id
  FROM accepted_events e
  CROSS JOIN reporting_period rp
  WHERE e.current_activity_qualifies = TRUE
    AND LOWER(e.current_activity_status) = 'completed'
    AND COALESCE(e.current_activity_completion_timestamp, e.event_timestamp) < rp.period_start

  UNION

  SELECT DISTINCT
    e.learner_id
  FROM accepted_events e
  CROSS JOIN reporting_period rp
  WHERE e.prior_qualifying_activity_completed = TRUE
    AND e.prior_qualifying_completion_timestamp < rp.period_start
),
learner_population AS (
  SELECT
    a.period_start,
    a.period_end_exclusive,
    a.reporting_time_zone,
    a.organization_id,
    a.learner_id,
    CASE WHEN q.learner_id IS NOT NULL THEN 1 ELSE 0 END AS is_returning_learner
  FROM active_learners a
  LEFT JOIN qualifying_completions q
    ON a.learner_id = q.learner_id
)
SELECT
  period_start AS reporting_period_start,
  period_end_exclusive - INTERVAL '1' MICROSECOND AS reporting_period_end,
  reporting_time_zone,
  organization_id,
  COUNT(*) AS total_active_learners,
  SUM(is_returning_learner) AS returning_learners,
  CAST(SUM(is_returning_learner) AS DOUBLE PRECISION) / NULLIF(COUNT(*), 0) AS returning_learner_rate,
  100.0 * CAST(SUM(is_returning_learner) AS DOUBLE PRECISION) / NULLIF(COUNT(*), 0) AS returning_learner_rate_percent
FROM learner_population
GROUP BY
  period_start,
  period_end_exclusive,
  reporting_time_zone,
  organization_id
ORDER BY organization_id;

Source Data

Learning Management System event logs, Digital Analytics, enrollment records, and identity resolution service

Dependencies

[ "learner_id", "qualifying_learning_activity_event", "qualifying_activity_timestamp", "first_qualifying_activity_timestamp", "reporting_period_start", "reporting_period_end", "learner_identity_map", "account_type", "event_validity_flag" ]

Report Attributes

Reporting Period Course Course Category Subject Area Learning Program Subscription Plan Learner Acquisition Channel Learner Registration Cohort Geography Institution Organization Platform Device Type Learning Format Instructor Days Since Previous Learning Activity

Recommended Reporting Views

Views

Learner Engagement Executive DashboardLearner Retention and Reactivation DashboardProduct Health DashboardCourse Portfolio Performance DashboardSubscription Learning Dashboard

Segment Eligibility

true

Governance

Data Sensitivityinternal

Derived KPIs

Returning Learner Rate Trend Returning Learner Rate Variance to Target Returning-to-New Learner Ratio Returning Learner Contribution to Course Completions

Governance

Created bydevyendarm

Created on9/2/2026

Last modified bydevyendarm

StatusPUBLISHED

GitHub

View File on GitHubcatalog/metrics/returning-learner-rate.yml

Returning Learner Rate

Business Use Case

Business Criticality

Journey / Navigation Area

Owning Function

Scope

Formula

Calculation Notes

Technical Details

Events

Data Mappings

SQL Queries

Source Data

Dependencies

Report Attributes

Recommended Reporting Views

Segment Eligibility

Governance

Related Metrics

Derived KPIs

Governance

GitHub

Community Discussion