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 (
  SELECT STRIPE_SUBSCRIPTION_ID, MIN(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
  GROUP BY all
),

activations AS (
select convert_timezone('UTC','America/Los_Angeles',TO_TIMESTAMP(created_at))::date created_on,
count(*) activations
from raw_db.tincan.legacy_devices
group by all
),

new_contacts AS (
select convert_timezone('UTC','America/Los_Angeles',created_at)::date contact_created,
count(*) as new_contacts_created
from raw_db.tincan.legacy_contacts
where 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.LEGACY_CONTACTS

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

---

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.