ACTIVITY_STATUS()

All functions > BUSINESS > ACTIVITY_STATUS()

Returns the activity status between two time periods for the given activity bits

Signatures

Returns: Activity status: 'new', 'retained', 'resurrected', 'churned', or 'stale'

ACTIVITY_STATUS(activity_bits: BIGINT, length_days_current: BIGINT, [length_days_previous: BIGINT]) → VARCHAR

Notes

• Classifies users into cohorts based on activity patterns
• 'new': active in current period, not in previous or before
• 'retained': active in both current and previous periods
• 'resurrected': active in current, not in previous, but was before
• 'churned': not active in current, but was in previous
• 'stale': not active in current or previous periods
• Essential for cohort analysis and retention metrics
• Commonly used with 7, 28, or 90-day periods

Examples

SELECT
    f1 := ACTIVITY_STATUS(3::BIGINT, 7), -- Seven-day window with bits 0–1 set reads as new
    f2 := ACTIVITY_STATUS(1031::BIGINT, 7, 14) -- Different current vs previous window lengths
;