Skip to content

Cohort Retention Methodology

Use this guide for monthly engagement and retention analyses built from ANALYTICS_DB.ANALYTICS.PARTICIPANT_DAILY_ACTIVITY_FULL_HISTORY.

Canonical cohort event

For call-engagement cohorts built from this table, assign each device to the month of its first successful call participation observed in the dataset.

DATE_TRUNC('month', MIN(CALL_DAY))

Apply that cohorting rule after filtering to records that contribute to successful call participation, so the cohort means first observed successful call month.

This cohort is appropriate for investor-style call engagement reporting. For questions about device activation, signup, or subscription start, build the cohort from the source table for that milestone and label the chart with that milestone name.

Retention definition

Define a device as retained in a given month when it has at least one successful call participated in during that month.

Make that rule explicit in the monthly aggregation:

SUM(SUCCESSFUL_CALLS_PARTICIPATED_IN) > 0

This guide therefore measures retention as share of the original cohort that completed at least one successful call in the month.

Denominator rules

Use the denominator that matches the story the metric is telling.

Metrics about the whole original cohort

Use cohort_size when the metric should include inactive members in the denominator.

Examples: - retention_pct = active_devices / cohort_size - talk_hours_per_cohort_member = total_talk_hours / cohort_size - calls_per_cohort_member = total_successful_calls / cohort_size

Metrics about currently retained devices

Use active_devices when the metric is specifically about intensity among devices that were retained in that month.

Examples: - talk_hours_per_active_device = total_talk_hours / active_devices - calls_per_active_device = total_successful_calls / active_devices

Name the metric with the denominator in the title so the chart is self-explanatory.

Relationship to the Daily Calls guide

daily_calls.md uses a broader activity concept for general activity reporting. For cohort retention heatmaps, use the successful-call rule above so retention means connected call participation rather than any attempted activity.

Prefer labels that make the cohort event and denominator explicit: - First successful call cohort - Retention (% of original cohort with at least one successful call) - Talk hours per original cohort member - Calls per active retained device

Reference monthly pattern

WITH device_cohort AS (
  SELECT
    THIS_PARTICIPANT_DEVICE_ID,
    DATE_TRUNC('month', MIN(CALL_DAY)) AS cohort_month
  FROM ANALYTICS_DB.ANALYTICS.PARTICIPANT_DAILY_ACTIVITY_FULL_HISTORY
  WHERE SUCCESSFUL_CALLS_PARTICIPATED_IN > 0
  GROUP BY 1
),
monthly_usage AS (
  SELECT
    THIS_PARTICIPANT_DEVICE_ID,
    DATE_TRUNC('month', CALL_DAY) AS activity_month,
    SUM(SUCCESSFUL_CALLS_PARTICIPATED_IN) AS successful_calls,
    SUM(TALK_TIME_SECONDS) / 3600.0 AS talk_hours
  FROM ANALYTICS_DB.ANALYTICS.PARTICIPANT_DAILY_ACTIVITY_FULL_HISTORY
  GROUP BY 1, 2
)
SELECT
  c.cohort_month,
  u.activity_month,
  DATEDIFF('month', c.cohort_month, u.activity_month) AS months_since_activation,
  COUNT(DISTINCT CASE WHEN u.successful_calls > 0 THEN u.THIS_PARTICIPANT_DEVICE_ID END) AS active_devices,
  SUM(u.successful_calls) AS total_successful_calls,
  SUM(u.talk_hours) AS total_talk_hours
FROM monthly_usage u
JOIN device_cohort c
  ON u.THIS_PARTICIPANT_DEVICE_ID = c.THIS_PARTICIPANT_DEVICE_ID
GROUP BY 1, 2, 3
ORDER BY 1, 3;