Retention Grid

Query

-- ── Subscription type classification ────────────────────────

WITH subs AS (
  SELECT
    s.ID,
    CONVERT_TIMEZONE('UTC','America/Los_Angeles', TO_TIMESTAMP(s.CREATED))::DATE AS created_date,
    CASE WHEN s.ENDED_AT IS NOT NULL
      THEN CONVERT_TIMEZONE('UTC','America/Los_Angeles', TO_TIMESTAMP(s.ENDED_AT::NUMBER))::DATE
    END AS ended_date,
    CASE
      WHEN s.PLAN:amount::NUMBER = 0 THEN 'Free'
      WHEN s.PLAN:amount::NUMBER > 0 AND s.PLAN:interval::VARCHAR = 'month' THEN 'Paid Monthly'
      WHEN s.PLAN:amount::NUMBER > 0 AND s.PLAN:interval::VARCHAR = 'year' THEN 'Paid Annual'
    END AS sub_type
  FROM RAW_DB.STRIPE.SUBSCRIPTIONS s
    WHERE s.LIVEMODE = true
    AND s.STATUS != 'incomplete_expired'
    AND CASE
          WHEN s.PLAN:amount::NUMBER = 0 THEN 'Free'
          WHEN s.PLAN:amount::NUMBER > 0 AND s.PLAN:interval::VARCHAR = 'month' THEN 'Paid Monthly'
          WHEN s.PLAN:amount::NUMBER > 0 AND s.PLAN:interval::VARCHAR = 'year' THEN 'Paid Annual'
        END IS NOT NULL

),

-- ── Eligible devices (filtered by sub type) ─────────────────

eligible_devices AS (
  SELECT DISTINCT
    d.DEVICE_ID,
    d.STRIPE_SUBSCRIPTION_ID,
    CONVERT_TIMEZONE('UTC','America/Los_Angeles', d.CREATED_AT)::DATE AS device_created_date,
    s.sub_type
  FROM RAW_DB.TINCAN.LEGACY_DEVICES d
  JOIN subs s ON d.STRIPE_SUBSCRIPTION_ID = s.ID
WHERE d.STRIPE_SUBSCRIPTION_ID IS NOT NULL
  {% if sub_type_filter is not none and sub_type_filter | length > 0 %}
    AND s.sub_type IN ({{ sub_type_filter | array }})
  {% endif %}


),

-- ── Usage-based retention CTEs ──────────────────────────────

base_activity AS (
    SELECT
        a.THIS_PARTICIPANT_DEVICE_ID,
        a.CALL_DAY,
        CASE
            WHEN {{ time_grain }} = 'Week' THEN DATE_TRUNC('week', a.CALL_DAY)
            WHEN {{ time_grain }} = 'Month' THEN DATE_TRUNC('month', a.CALL_DAY)
            WHEN {{ time_grain }} = 'Quarter' THEN DATE_TRUNC('quarter', a.CALL_DAY)
        END AS activity_period,
        a.SUCCESSFUL_CALLS_PARTICIPATED_IN,
        a.talk_time_seconds
    FROM ANALYTICS_DB.ANALYTICS.PARTICIPANT_DAILY_ACTIVITY_FULL_HISTORY a
    INNER JOIN eligible_devices ed
        ON a.THIS_PARTICIPANT_DEVICE_ID = ed.DEVICE_ID
    WHERE a.THIS_PARTICIPANT_DEVICE_ID != 3635
),

current_period AS (
    SELECT
        CASE
            WHEN {{ time_grain }} = 'Week' THEN DATE_TRUNC('week', CURRENT_DATE)
            WHEN {{ time_grain }} = 'Month' THEN DATE_TRUNC('month', CURRENT_DATE)
            WHEN {{ time_grain }} = 'Quarter' THEN DATE_TRUNC('quarter', CURRENT_DATE)
        END AS current_period
),

device_periodly AS (
    SELECT
        THIS_PARTICIPANT_DEVICE_ID,
        activity_period,
        SUM(SUCCESSFUL_CALLS_PARTICIPATED_IN) AS calls,
        SUM(talk_time_seconds) AS talk_time_seconds
    FROM base_activity
    GROUP BY 1, 2
),

device_cohort AS (
    SELECT
        THIS_PARTICIPANT_DEVICE_ID,
        MIN(activity_period) AS cohort_period
    FROM device_periodly
    WHERE calls >= 1
    GROUP BY 1
),

cohort_sizes AS (
    SELECT
        cohort_period,
        COUNT(DISTINCT THIS_PARTICIPANT_DEVICE_ID) AS cohort_size
    FROM device_cohort
    GROUP BY 1
),

period_spine AS (
    SELECT DISTINCT
        ba.activity_period
    FROM base_activity AS ba
    CROSS JOIN current_period AS cp
    WHERE ba.activity_period < cp.current_period
),

cohort_period_cross AS (
    SELECT
        dc.THIS_PARTICIPANT_DEVICE_ID,
        dc.cohort_period,
        ps.activity_period
    FROM device_cohort AS dc
    CROSS JOIN period_spine AS ps
    WHERE ps.activity_period >= dc.cohort_period
),

full_grid AS (
    SELECT
        cpc.THIS_PARTICIPANT_DEVICE_ID,
        cpc.cohort_period,
        cpc.activity_period,
        COALESCE(dp.calls, 0) AS calls,
        COALESCE(dp.talk_time_seconds, 0) AS talk_time_seconds
    FROM cohort_period_cross AS cpc
    LEFT JOIN device_periodly AS dp
        ON cpc.THIS_PARTICIPANT_DEVICE_ID = dp.THIS_PARTICIPANT_DEVICE_ID
       AND cpc.activity_period = dp.activity_period
),

full_grid_enriched AS (
    SELECT
        fg.*,
        CASE
            WHEN {{ time_grain }} = 'Week' THEN DATEDIFF('week', fg.cohort_period, fg.activity_period)
            WHEN {{ time_grain }} = 'Month' THEN DATEDIFF('month', fg.cohort_period, fg.activity_period)
            WHEN {{ time_grain }} = 'Quarter' THEN DATEDIFF('quarter', fg.cohort_period, fg.activity_period)
        END AS periods_since_activation,
        CASE
            WHEN {{ time_grain }} = 'Week' THEN TO_CHAR(fg.cohort_period, 'YYYY-MM-DD')
            WHEN {{ time_grain }} = 'Month' THEN TO_CHAR(fg.cohort_period, 'YYYY-MM')
            WHEN {{ time_grain }} = 'Quarter' THEN CAST(EXTRACT(YEAR FROM fg.cohort_period) AS VARCHAR) || '-Q' || CAST(EXTRACT(QUARTER FROM fg.cohort_period) AS VARCHAR)
        END AS cohort_label
    FROM full_grid AS fg
),

chart_window AS (
  SELECT
    CASE
      WHEN {{ custom_date_start }} IS NOT NULL
        THEN CAST({{ custom_date_start }} AS DATE)
        ELSE '7/1/2025'::DATE
    END AS start_date,
    CASE
      WHEN {{ custom_date_end }} IS NOT NULL
        THEN CAST({{ custom_date_end }} AS DATE)
      ELSE CURRENT_DATE
    END AS end_date
),

-- ── Network retention CTEs ──────────────────────────────────

network_device_subs AS (
  SELECT
    ed.STRIPE_SUBSCRIPTION_ID,
    ed.device_created_date,
    s.created_date AS sub_created_date,
    s.ended_date   AS sub_ended_date,
    CASE
      WHEN {{ time_grain }} = 'Week' THEN DATE_TRUNC('week', s.created_date)
      WHEN {{ time_grain }} = 'Month' THEN DATE_TRUNC('month', s.created_date)
      WHEN {{ time_grain }} = 'Quarter' THEN DATE_TRUNC('quarter', s.created_date)
    END AS network_cohort_period
  FROM eligible_devices ed
  JOIN subs s ON ed.STRIPE_SUBSCRIPTION_ID = s.ID
),

network_cohort_sizes AS (
  SELECT network_cohort_period, COUNT(*) AS network_cohort_size
  FROM network_device_subs
  GROUP BY 1
),

network_retention AS (
  SELECT
    nds.network_cohort_period,
    ps.activity_period,
    COUNT(*) AS network_retained_devices
  FROM network_device_subs nds
  JOIN period_spine ps
    ON nds.sub_created_date < CASE
         WHEN {{ time_grain }} = 'Week' THEN DATEADD('week', 1, ps.activity_period)
         WHEN {{ time_grain }} = 'Month' THEN DATEADD('month', 1, ps.activity_period)
         WHEN {{ time_grain }} = 'Quarter' THEN DATEADD('quarter', 1, ps.activity_period)
       END
   AND (nds.sub_ended_date IS NULL OR nds.sub_ended_date > ps.activity_period)
  WHERE ps.activity_period >= nds.network_cohort_period
  GROUP BY 1, 2
)

-- ── Final SELECT ────────────────────────────────────────────

SELECT
    cs.cohort_size,
    fge.cohort_period,
    fge.cohort_label || ' (n=' || TO_VARCHAR(cs.cohort_size, '999,999,999') || ')' AS cohort,
    fge.cohort_label || ' (n=' || TO_VARCHAR(ncs.network_cohort_size, '999,999,999') || ')' AS network_cohort,
    fge.periods_since_activation,
    fge.activity_period,
    --Call volume & time stats for All users (default measure, includes zeros)
    MEDIAN(fge.calls) AS median_calls_all,
    AVG(fge.calls) AS avg_calls_all,
    MEDIAN(fge.talk_time_seconds) / 60.0 AS median_talk_minutes_all,
    AVG(fge.talk_time_seconds) / 60.0 AS avg_talk_minutes_all,

    --Call volume & time stats for RETAINED users only
    MEDIAN(CASE
        WHEN fge.calls >= {{ retention_call_threshold }}
         AND (fge.talk_time_seconds / 60.0) >= {{ retention_talk_minutes_threshold }}
        THEN fge.calls END) AS median_calls_retained,
    AVG(CASE
        WHEN fge.calls >= {{ retention_call_threshold }}
         AND (fge.talk_time_seconds / 60.0) >= {{ retention_talk_minutes_threshold }}
        THEN fge.calls END) AS avg_calls_retained,
    MEDIAN(CASE
        WHEN fge.calls >= {{ retention_call_threshold }}
         AND (fge.talk_time_seconds / 60.0) >= {{ retention_talk_minutes_threshold }}
        THEN fge.talk_time_seconds / 60.0 END) AS median_talk_minutes_retained,
    AVG(CASE
        WHEN fge.calls >= {{ retention_call_threshold }}
         AND (fge.talk_time_seconds / 60.0) >= {{ retention_talk_minutes_threshold }}
        THEN fge.talk_time_seconds / 60.0 END) AS avg_talk_minutes_retained,

    AVG(
        CASE
            WHEN fge.calls >= {{ retention_call_threshold }}
             AND (fge.talk_time_seconds / 60.0) >= {{ retention_talk_minutes_threshold }}
            THEN 1.0 ELSE 0.0
        END
    ) AS pct_retained,

    -- Network retention columns
    ncs.network_cohort_size,
    nr.network_retained_devices

FROM full_grid_enriched AS fge
INNER JOIN cohort_sizes AS cs
    ON fge.cohort_period = cs.cohort_period
CROSS JOIN chart_window cw
LEFT JOIN network_retention nr
    ON fge.cohort_period = nr.network_cohort_period
   AND fge.activity_period = nr.activity_period
LEFT JOIN network_cohort_sizes ncs
    ON fge.cohort_period = ncs.network_cohort_period

WHERE
    fge.cohort_period >= cw.start_date
    AND fge.activity_period <= cw.end_date

GROUP BY ALL
ORDER BY
    fge.cohort_period,
    fge.periods_since_activation

Overview

The primary query for the user retention report. Produces a cohort × period grid showing how engagement and retention evolve over time for devices on the active network. Each row represents a cohort-period combination and includes retention rates, call volume statistics, and parallel network (subscription) retention metrics.

Two distinct retention concepts are tracked in parallel:

• Usage retention: Whether a device, which has made at least one call in the past, met the configured call and talk time thresholds in a given period.
• Network retention: Whether the underlying subscription was still active in a given period, regardless of call activity.

Depends on: ANALYTICS_DB.ANALYTICS.PARTICIPANT_DAILY_ACTIVITY_FULL_HISTORY, RAW_DB.STRIPE.SUBSCRIPTIONS, RAW_DB.TINCAN.LEGACY_DEVICES

---

Parameters

Parameter	Type	Description
{{ time_grain }}	'Week', 'Month', 'Quarter'	Aggregation period for cohort and activity bucketing
{{ sub_type_filter }}	Array or none	Optional filter to restrict analysis to specific subscription types ('Free', 'Paid Monthly', 'Paid Annual'). If empty or none, all types are included
{{ retention_call_threshold }}	Integer	Minimum number of calls a device must have in a period to be counted as a retained USER
{{ retention_talk_minutes_threshold }}	Number	Minimum total talk minutes a device must have in a period to be counted as a retained USER
{{ custom_date_start }}	Date or NULL	Start of the cohort window. Defaults to 2025-07-01 if not set
{{ custom_date_end }}	Date or NULL	End of the activity window. Defaults to CURRENT_DATE if not set

---

Output Columns

Column	Description
cohort_size	Number of devices in the usage cohort (users whose first call was in the cohort period)
cohort_period	Start date of the cohort period
cohort	Display label for the cohort, including cohort size (e.g. "2025-01 (n=42)")
network_cohort	Display label for the network cohort, including network cohort size
periods_since_activation	Number of periods elapsed since the device's first active period
activity_period	The period being measured
median_calls_all	Median calls per device across all devices in the cohort (including zeros)
avg_calls_all	Average calls per device across all devices (including zeros)
median_talk_minutes_all	Median talk minutes per device across all devices (including zeros)
avg_talk_minutes_all	Average talk minutes per device across all devices (including zeros)
median_calls_retained	Median calls among retained devices only
avg_calls_retained	Average calls among retained devices only
median_talk_minutes_retained	Median talk minutes among retained devices only
avg_talk_minutes_retained	Average talk minutes among retained devices only
pct_retained	Fraction of cohort devices meeting both the call and talk minute thresholds in this period
network_cohort_size	Number of subscriptions in the network cohort for this period
network_retained_devices	Number of subscriptions still active in the given activity period

---

CTEs

subs

Source: RAW_DB.STRIPE.SUBSCRIPTIONS

Filters to live-mode, non-expired subscriptions and classifies each into 'Free', 'Paid Monthly', or 'Paid Annual' based on PLAN:amount and PLAN:interval. Subscriptions that don't match any classification are excluded.

eligible_devices

Source: RAW_DB.TINCAN.LEGACY_DEVICES

Joins devices to their subscription and applies the optional {{ sub_type_filter }} to restrict the analysis population. Uses Jinja conditional syntax ({% if ... %}), so the filter is only applied when values are provided.

base_activity

Source: ANALYTICS_DB.ANALYTICS.PARTICIPANT_DAILY_ACTIVITY_FULL_HISTORY

Pulls daily call activity for eligible devices and assigns each day to the appropriate period based on {{ time_grain }}. Device 3635 is explicitly excluded (test/internal device).

current_period

Computes the start of the current in-progress period. Used to exclude the current period from the retention grid, since it is incomplete.

device_periodly

Aggregates base_activity to the device × period grain, summing calls and talk time.

device_cohort

Assigns each device to its cohort period — the first period in which it had at least one call.

cohort_sizes

Counts the number of devices in each cohort period.

period_spine

Produces the distinct set of completed activity periods from base_activity, excluding the current in-progress period.

cohort_period_cross

Produces all valid cohort × activity period combinations — every cohort crossed with every period at or after its cohort start. This is the scaffold for the retention grid.

full_grid

Left-joins actual device activity onto cohort_period_cross. Devices with no activity in a given period receive 0 for calls and talk time.

full_grid_enriched

Adds periods_since_activation (periods elapsed since cohort start) and cohort_label (human-readable cohort identifier) to each row.

chart_window

Defines the cohort and activity date range for the final filter. Defaults to 2025-07-01 as the start date if {{ custom_date_start }} is not provided.

network_device_subs

Joins eligible_devices back to subs to capture subscription start and end dates alongside each device's cohort period for network retention analysis.

network_cohort_sizes

Counts subscriptions per network cohort period.

network_retention

For each network cohort × activity period combination, counts subscriptions that were still active during the activity period (i.e. created before the period ended and not yet ended at the start of the period).

---

Notes

• Two retention definitions run in parallel. Usage retention (pct_retained) requires a device to meet both {{ retention_call_threshold }} and {{ retention_talk_minutes_threshold }} in a period. Network retention (network_retained_devices / network_cohort_size) only requires the subscription to still be active — no call activity needed.
• "All" vs. "retained" stats. The _all aggregate columns include every device in the cohort for that period, including those with zero activity. The _retained columns include only devices that met both thresholds. This means _all medians and averages will be pulled down by inactive devices.
• Default chart start date is 2025-07-01, not 2025-01-01 like other reports. This likely reflects when reliable retention data begins.
• Device 3635 is excluded from base_activity as a known test or internal device.
• sub_type_filter uses Jinja array syntax ({{ sub_type_filter | array }}), which is distinct from the standard {{ parameter }} Hex syntax used elsewhere. This enables multi-select filtering.