Network Size

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.TRIAL_END IS NOT NULL THEN convert_timezone('UTC','America/Los_Angeles',TO_TIMESTAMP(s.TRIAL_END::NUMBER))::DATE END AS trial_end_date,
    s.PLAN:amount::NUMBER AS plan_amount
  FROM RAW_DB.STRIPE.SUBSCRIPTIONS s
  WHERE s.LIVEMODE = true
    AND s.STATUS != 'incomplete_expired'
),

devices AS (
  -- Historical devices from the retained (frozen) legacy_devices keep their true created dates;
  -- go-forward, add devices created after the freeze from `device` (deduped via the routing_id
  -- bridge). device.created_at alone is a migration timestamp, so legacy stays the historical source.
  SELECT STRIPE_SUBSCRIPTION_ID, MIN(device_created_date) AS device_created_date
  FROM (
    SELECT STRIPE_SUBSCRIPTION_ID,
           convert_timezone('UTC','America/Los_Angeles', CREATED_AT)::DATE AS device_created_date
    FROM RAW_DB.TINCAN.LEGACY_DEVICES
    WHERE STRIPE_SUBSCRIPTION_ID IS NOT NULL
    UNION ALL
    SELECT sub.STRIPE_SUBSCRIPTION_ID,
           convert_timezone('America/Los_Angeles', d.CREATED_AT)::DATE AS device_created_date
    FROM RAW_DB.TINCAN.DEVICE d
    JOIN RAW_DB.TINCAN.SUBSCRIPTION sub ON sub.ID = d.SUBSCRIPTION_ID
    WHERE sub.STRIPE_SUBSCRIPTION_ID IS NOT NULL
      AND NOT EXISTS (
        SELECT 1 FROM RAW_DB.TINCAN.LEGACY_DEVICES ld
        WHERE ld.DEVICE_ID::varchar = d.ROUTING_ID
      )
  )
  GROUP BY all
),

activations AS (
-- Historical activations from the retained (frozen) legacy_devices; go-forward, add new devices
-- created after the freeze from `device` (deduped via routing_id). Preserves the historical trend.
select created_on, count(*) activations
from (
  select convert_timezone('UTC','America/Los_Angeles', TO_TIMESTAMP(created_at))::date created_on
  from raw_db.tincan.legacy_devices
  union all
  select convert_timezone('America/Los_Angeles', d.created_at)::date created_on
  from raw_db.tincan.device d
  where not exists (
    select 1 from raw_db.tincan.legacy_devices ld
    where ld.device_id::varchar = d.routing_id
  )
)
group by all
),

new_contacts AS (
-- V2 migration: replaces the V1 contacts table, which was DROPPED 2026-08-06 and no longer exists.
-- In V2, contact approval status lives on the phone number (contact_number.request_status), so join
-- contact -> contact_number and treat a contact as approved if it has >=1 approved number.
-- count(distinct c.id) keeps contact grain (a contact can have multiple numbers). contact.created_at
-- is TIMESTAMP_TZ, so 2-arg convert_timezone (the V1 column was NTZ, which used the 3-arg form).
-- Historic months matched V1 closely, but new_contacts_created steps UP from ~May 2026: V1 held a
-- large block of approved contacts with NULL created_at and under-captured post-cutover, both of
-- which this more-complete source correctly includes. See network_size.md.
select convert_timezone('America/Los_Angeles', c.created_at)::date contact_created,
count(distinct c.id) as new_contacts_created
from raw_db.tincan.contact c
join raw_db.tincan.contact_number cn on cn.contact_id = c.id
where cn.request_status = 'approved'
group by all order by 1

)



SELECT
  id.dt,
  COUNT(*) AS active_subs_with_device,
  COUNT(CASE 
    WHEN s.plan_amount > 0 
      AND (s.trial_end_date IS NULL OR s.trial_end_date <= id.dt)
    THEN 1 END) AS paid_subscriptions,
  COUNT(CASE 
    WHEN s.plan_amount > 0 
      AND s.trial_end_date IS NOT NULL 
      AND s.trial_end_date > id.dt
    THEN 1 END) AS trial_subscriptions,
  COUNT(CASE WHEN s.plan_amount = 0 THEN 1 END) AS free_subscriptions,
  a.activations,
  nc.new_contacts_created
FROM included_dates id
INNER JOIN subs s
  ON s.created_date <= id.dt
  AND (s.ended_date IS NULL OR s.ended_date > id.dt)
INNER JOIN devices d
  ON s.ID = d.STRIPE_SUBSCRIPTION_ID
  AND d.device_created_date <= id.dt
left outer join activations a
    on id.dt = a.created_on
left outer join new_contacts nc
    on id.dt = nc.contact_created
GROUP BY ALL
ORDER BY 1

Overview

Produces a daily time-series of subscription and activation activity. Each row represents a calendar date and shows the count of active subscriptions (broken down by plan type), device activations, and new approved contacts created on that day.

All timestamps are converted from UTC to America/Los_Angeles before date truncation.

Depends on: Date Spine (included_dates)

---

Output Columns

Column	Description
dt	Calendar date (from the date spine)
active_subs_with_device	Count of live subscriptions that have at least one associated device activated on or before dt
paid_subscriptions	Paid plan subs (plan_amount > 0) whose trial has ended or never existed
trial_subscriptions	Paid plan subs (plan_amount > 0) still within an active trial on dt
free_subscriptions	Subscriptions on a free plan (plan_amount = 0)
activations	Number of devices activated on dt (all devices, not filtered to active subs)
new_contacts_created	Number of approved contacts created on dt

---

CTEs

subs

Source: RAW_DB.STRIPE.SUBSCRIPTIONS

Filters to live-mode subscriptions, excluding incomplete_expired status. Extracts: - created_date — subscription creation date (UTC → PT) - ended_date — subscription end date, if set (UTC → PT) - trial_end_date — trial end date, if set (UTC → PT) - plan_amount — plan price in cents from the PLAN JSON field

devices

Source: RAW_DB.TINCAN.LEGACY_DEVICES

For each Stripe subscription ID, finds the earliest device activation date. Used to ensure a subscription is only counted as "active with device" once a device has been physically activated.

activations

Source: RAW_DB.TINCAN.LEGACY_DEVICES

Daily count of all device activations across the fleet, regardless of subscription status.

new_contacts

Source: RAW_DB.TINCAN.CONTACT joined to RAW_DB.TINCAN.CONTACT_NUMBER

Daily count of newly approved contacts. Reflects network growth — each approved contact represents a connection added to a device owner's calling list.

Migrated from a V1 contacts table (since dropped) to the V2 platform tables. In V2 the approval status lives on the phone number, so CONTACT is joined to CONTACT_NUMBER and a contact is counted as approved when it has at least one number with request_status = 'approved'. COUNT(DISTINCT contact.id) keeps the metric at contact grain (a contact may have several numbers). See the discontinuity note below.

---

Join Logic

• Date spine (included_dates) drives the date series, covering all days from 2025-01-01 through yesterday.
• Subscriptions are joined as active on a given date if created_date <= dt and (ended_date IS NULL OR ended_date > dt).
• Devices are joined to confirm activation on or before dt.
• activations and new_contacts are left-joined on exact date match; days with no activity will be NULL.

---

Notes

• plan_amount is denominated in cents (Stripe standard). A value of 0 indicates a free plan; any positive value indicates a paid plan.
• Trial logic: a subscription is considered in trial if trial_end_date is set and falls after dt; otherwise it is counted as paid (assuming plan_amount > 0).
• The date spine excludes today (dt < CURRENT_DATE), so this query will never reflect same-day activity.
• new_contacts_created discontinuity (~May 2026): the source was migrated from the V1 contacts table to the V2 CONTACT / CONTACT_NUMBER tables. Monthly counts matched the V1 source closely through April 2026, then step up, because the V2 source is more complete: the V1 table held a large block of approved contacts with a NULL created_at (silently dropped from the daily series) and under-captured contacts after the V1→V2 platform cutover. The step-up is a data-quality correction, not a real surge in contact creation. The V1 contacts table was dropped on 2026-08-06, so this discontinuity can no longer be re-measured against it — treat this note as the record.