Retention Time Series

Query

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 AS (
  SELECT DISTINCT
    d.DEVICE_ID,
    d.STRIPE_SUBSCRIPTION_ID,
    CONVERT_TIMEZONE('UTC','America/Los_Angeles', d.CREATED_AT)::DATE AS device_created_date
  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 %}
),

periods AS (
  SELECT
    CASE
      WHEN {{ time_grain }} = 'Week' THEN DATE_TRUNC('week', DATEADD(day, seq4(), '2024-10-01'::date))
      WHEN {{ time_grain }} = 'Month' THEN DATE_TRUNC('month', DATEADD(day, seq4(), '2024-10-01'::date))
      WHEN {{ time_grain }} = 'Quarter' THEN DATE_TRUNC('quarter', DATEADD(day, seq4(), '2024-10-01'::date))
    END AS period_start,
    CASE
      WHEN {{ time_grain }} = 'Week' THEN DATEADD('week', 1, DATE_TRUNC('week', DATEADD(day, seq4(), '2024-10-01'::date)))
      WHEN {{ time_grain }} = 'Month' THEN DATEADD('month', 1, DATE_TRUNC('month', DATEADD(day, seq4(), '2024-10-01'::date)))
      WHEN {{ time_grain }} = 'Quarter' THEN DATEADD('quarter', 1, DATE_TRUNC('quarter', DATEADD(day, seq4(), '2024-10-01'::date)))
    END AS period_end
  FROM TABLE(GENERATOR(ROWCOUNT => 600))
  WHERE DATEADD(day, seq4(), '2024-10-01'::date) <= CURRENT_DATE()
  GROUP BY 1, 2
  HAVING period_start < 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
),

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
),

-- ── Churn: subs active during period that ended during it ───

churn AS (
  SELECT
    p.period_start,
    COUNT(*) AS active_subs,
    COUNT(CASE
      WHEN s.ended_date >= p.period_start AND s.ended_date < p.period_end
      THEN 1 END) AS subs_lost
  FROM periods p
  JOIN subs s
    ON s.created_date < p.period_end
   AND (s.ended_date IS NULL OR s.ended_date >= p.period_start)
  JOIN eligible_devices ed
    ON s.ID = ed.STRIPE_SUBSCRIPTION_ID
   AND ed.device_created_date < p.period_end
  GROUP BY 1
),

-- ── Inactivity: on network but below usage threshold ────────

device_period_usage AS (
  SELECT
    a.THIS_PARTICIPANT_DEVICE_ID AS device_id,
    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,
    SUM(a.SUCCESSFUL_CALLS_PARTICIPATED_IN) AS calls,
    SUM(a.TALK_TIME_SECONDS) AS talk_time_seconds
  FROM ANALYTICS_DB.ANALYTICS.PARTICIPANT_DAILY_ACTIVITY_FULL_HISTORY a
  WHERE a.THIS_PARTICIPANT_DEVICE_ID != 3635
  GROUP BY 1, 2
),

inactivity AS (
  SELECT
    p.period_start,
    COUNT(DISTINCT ed.DEVICE_ID) AS active_network_devices,
    COUNT(DISTINCT CASE
      WHEN COALESCE(dpu.calls, 0) < {{ retention_call_threshold }}
        OR COALESCE(dpu.talk_time_seconds, 0) / 60.0 < {{ retention_talk_minutes_threshold }}
      THEN ed.DEVICE_ID
    END) AS inactive_devices
  FROM periods p
  JOIN subs s
    ON s.created_date < p.period_end
   AND (s.ended_date IS NULL OR s.ended_date >= p.period_start)
  JOIN eligible_devices ed
    ON s.ID = ed.STRIPE_SUBSCRIPTION_ID
   AND ed.device_created_date < p.period_end
  LEFT JOIN device_period_usage dpu
    ON ed.DEVICE_ID = dpu.device_id
   AND p.period_start = dpu.activity_period
  GROUP BY 1
)

SELECT
  c.period_start,
  CASE
        WHEN {{ time_grain }} = 'Week' THEN TO_CHAR(c.period_start, 'YYYY-MM-DD')
        WHEN {{ time_grain }} = 'Month' THEN TO_CHAR(c.period_start, 'YYYY-MM')
        WHEN {{ time_grain }} = 'Quarter' THEN CAST(EXTRACT(YEAR FROM c.period_start) AS VARCHAR) || '-Q' || CAST(EXTRACT(QUARTER FROM c.period_start) AS VARCHAR)
    END AS period_label,
  c.active_subs,
  c.subs_lost,
  i.active_network_devices,
  i.inactive_devices
FROM churn c
JOIN inactivity i ON c.period_start = i.period_start
CROSS JOIN chart_window cw
WHERE c.period_start >= cw.start_date
  AND c.period_start <= cw.end_date
ORDER BY c.period_start

Overview

Produces a period-level time series tracking two distinct retention failure modes side by side:

• Churn: Subscriptions that were active during a period but ended before the period closed.
• Inactivity: Devices that were on the network but failed to meet the configured call and talk time thresholds during the period.

Unlike retention_grid.sql, which tracks retention across cohort lifetimes, this query tracks retention health as a simple time series — useful for spotting trends in churn rate or engagement drop-off over time.

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

---

Parameters

Parameter	Type	Description
{{ time_grain }}	'Week', 'Month', 'Quarter'	Aggregation period for all metrics
{{ 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 calls a device must have in a period to be considered active
{{ retention_talk_minutes_threshold }}	Number	Minimum total talk minutes a device must have in a period to be considered active
{{ custom_date_start }}	Date or NULL	Start of the output window. Defaults to 2025-07-01 if not set
{{ custom_date_end }}	Date or NULL	End of the output window. Defaults to CURRENT_DATE if not set

---

Output Columns

Column	Description
period_start	Start date of the period
period_label	Human-readable period label (e.g. "2025-01", "2025-Q1")
active_subs	Subscriptions with an activated device that were active at any point during the period
subs_lost	Subscriptions that ended during the period
active_network_devices	Distinct devices on the network during the period
inactive_devices	Devices on the network that fell below the call or talk time threshold during the period

---

CTEs

subs

Source: RAW_DB.STRIPE.SUBSCRIPTIONS

Same classification logic as retention_grid.sql — filters to live-mode, non-expired subscriptions and assigns each to 'Free', 'Paid Monthly', or 'Paid Annual'.

eligible_devices

Source: RAW_DB.TINCAN.LEGACY_DEVICES

Joins devices to subscriptions and applies the optional {{ sub_type_filter }}. Identical in structure to retention_grid.sql but does not carry sub_type forward since it isn't needed in the output.

periods

Generates a spine of completed periods from 2024-10-01 through the start of the current in-progress period (exclusive). Uses TABLE(GENERATOR(ROWCOUNT => 600)) with DATE_TRUNC and GROUP BY to produce distinct period start and end dates at the selected grain. Note this spine starts earlier (2024-10-01) than the default chart window (2025-07-01) — the chart_window filter trims the output downstream.

chart_window

Defines the output date range. Defaults to 2025-07-01 as the start date if {{ custom_date_start }} is not provided, matching the default in retention_grid.sql.

churn

For each period, counts subscriptions with an activated device that were active at any point during the period (active_subs) and those whose ended_date fell within the period (subs_lost).

device_period_usage

Aggregates daily call activity from PARTICIPANT_DAILY_ACTIVITY_FULL_HISTORY to the device × period grain. Device 3635 is excluded.

inactivity

For each period, counts all network devices (active_network_devices) and those that failed to meet either the call or talk time threshold (inactive_devices). A device is inactive if calls < {{ retention_call_threshold }} OR talk_minutes < {{ retention_talk_minutes_threshold }} — meeting only one threshold is not sufficient.

---

Notes

• Churn and inactivity measure different things. subs_lost counts subscriptions that formally ended; inactive_devices counts devices still on the network but not engaging. A churned subscription will also appear as inactive in the same period, so these columns overlap — they should not be summed.
• Device 3635 is excluded from device_period_usage, consistent with retention_grid.sql.
• sub_type_filter uses Jinja array syntax ({% if ... %} / {{ sub_type_filter | array }}), consistent with retention_grid.sql.
• The periods spine starts at 2024-10-01 rather than 2025-01-01 like other report spines. This is earlier than the default chart window but does not reflect when subscription or device data became reliable enough for retention analysis.