Growth accounting functions

Tracking whether users are active, churning, or coming back is fundamental to growth analytics. FeatureQL encodes up to 60 days of activity history into a single BIGINT using bitwise representation — making retention queries fast and storage minimal.

How activity bits work

Each bit in a BIGINT represents one day. Bit 0 (rightmost) is the reference date (typically today), bit 1 is yesterday, bit 2 is two days ago, and so on up to bit 59. A 1 means the user was active that day; a 0 means they weren't.

For example, the binary pattern 10000000111 means the user was active today, yesterday, two days ago, and ten days ago.

TO_ACTIVITY_BITS() converts an array of dates into this representation, and FROM_ACTIVITY_BITS() converts it back. For debugging, BINARY_REPRESENTATION() returns a human-readable binary string.

Querying activity

Three functions let you ask questions about the bit pattern:

• IS_ACTIVE(bits, days_back, offset) — was the user active in a window? IS_ACTIVE(bits, 7) checks the last 7 days. IS_ACTIVE(bits, 7, 7) checks days 8-14 (the 7 days before the last 7).
• COUNT_ACTIVE(bits, days_back, offset) — how many days was the user active in that window?
• RECENCY(bits) — how many days since the user was last active? Returns 0 if active today.

Classifying user lifecycle stages

ACTIVITY_STATUS() compares activity across two time periods and classifies each user:

ACTIVITY_STATUS(bits, current_period_days, previous_period_days)

Complete example

This example shows all the activity bit functions working together. Five users with different activity patterns are classified across 7-day and 7/14-day windows. The UPDATE_ACTIVITY_BIT() call at the end demonstrates how to shift history forward by one day and record new activity:

WITH
    activity := INPUT(ARRAY(DATE)),
    date_ref := INPUT(DATE),
    (activity, date_ref) := BIND_VALUES(
        ARRAY[
            ROW(ARRAY[DATE '2025-01-19', DATE '2025-01-20', DATE '2025-01-18', DATE '2025-01-10'], DATE '2025-01-20'),
            ROW(ARRAY[DATE '2025-01-07'], DATE '2025-01-20'),
            ROW(ARRAY[DATE '2025-01-06'], DATE '2025-01-20'),
            ROW(ARRAY[DATE '2025-01-01'], DATE '2025-01-20'),
            ROW(ARRAY[DATE '2024-12-01'], DATE '2025-01-20')
        ]
    )
SELECT
    activity,
    TO_ACTIVITY_BITS(activity, date_ref) as activity_bits,
    BINARY_REPRESENTATION(activity_bits) as representation,
    COUNT_ACTIVE(activity_bits, 7) as count_active_7,
    IS_ACTIVE(activity_bits, 7) as is_active_7,
    IS_ACTIVE(activity_bits, 7, 7) as is_active_7_7,
    RECENCY(activity_bits) as recency_days,
    ACTIVITY_STATUS(activity_bits, 7) as status_7,
    ACTIVITY_STATUS(activity_bits, 7, 14) as status_7_14,
    UPDATE_ACTIVITY_BIT(activity_bits, TRUE) as activity_bits_new,
    FROM_ACTIVITY_BITS(activity_bits_new, DATE '2025-01-21') as activity_new
;

Notice how user lifecycle classification depends on the window size. The user active only on January 7th is "churned" under a 7-day window (active 8-14 days ago, not in the last 7) but also "churned" under a 7/14-day window. The user active on January 19-20 is "retained" in both — they were active in the current and previous periods.

Common patterns

Weekly and daily active users:

IS_ACTIVE(activity_bits, 7)    -- WAU: active in last 7 days
IS_ACTIVE(activity_bits, 1)    -- DAU: active today

Retention cohorts — what fraction of users active 8-14 days ago are still active in the last 7 days:

COUNT_IF(IS_ACTIVE(bits, 7, 7) AND IS_ACTIVE(bits, 7))
    / COUNT_IF(IS_ACTIVE(bits, 7, 7))

Engagement intensity — classify by how frequently a user is active:

CASE
    WHEN COUNT_ACTIVE(bits, 7) >= 6 THEN 'power_user'
    WHEN COUNT_ACTIVE(bits, 7) >= 3 THEN 'regular'
    WHEN COUNT_ACTIVE(bits, 7) >= 1 THEN 'casual'
    ELSE 'inactive'
END

Churn risk scoring — higher score means higher risk:

CASE
    WHEN RECENCY(bits) = 0 THEN 0   -- Active today
    WHEN RECENCY(bits) <= 3 THEN 1   -- Recently active
    WHEN RECENCY(bits) <= 7 THEN 2   -- Week old
    WHEN RECENCY(bits) <= 14 THEN 3  -- Two weeks
    ELSE 4                            -- High risk
END

Updating activity bits

UPDATE_ACTIVITY_BIT() shifts the entire bit pattern left by one position and sets bit 0 based on whether the user was active today. This is designed for daily batch updates:

NEW_BITS := UPDATE_ACTIVITY_BIT(OLD_BITS, USER_HAD_ACTIVITY_TODAY)

Design tradeoffs

Activity bits trade flexibility for performance. The 60-day limit (BIGINT constraint) and day-level granularity won't work for every use case — but for the common growth accounting patterns (DAU/WAU/MAU, retention cohorts, lifecycle classification), they're dramatically faster than scanning date arrays. A single bitwise operation replaces what would otherwise be an array scan with date comparisons.