StayAi Models

select
    order_id
  , order_name
  , customer_id
  , subscription_id
  , created_at :: timestamp as created_at
  , updated_at :: timestamp as updated_at
  , fulfillment_status
  , currency
  , total_price
  , cart_discount_amount
  , current_total_tax
  , total_shipping_price
  , address
  , tags
  , line_items
  , split_part(order_id, 'Order/', 2) :: varchar as shopify_order_id_clean
from 
	{{sources.stayai.orders}}

select
    id
  , subscription_id
  , customer_id
  , email_address
  , created_at :: timestamp as created_at
  , updated_at :: timestamp as updated_at
  , last_charge_date :: timestamp as last_charge_at
  , next_billing_date :: timestamp as next_billing_at
  , paused_until :: timestamp as paused_until
  , dunning_started_at :: timestamp as dunning_started_at
  , dunning_exited_at :: timestamp as dunning_exited_at
  , churned_at :: timestamp as churned_at
  , cancelled_at :: timestamp as cancelled_at
  , cancellation_reason
  , price
  , delivery_price
  , currency
  , status
  , order_interval_frequency
  , order_interval_unit
  , line_items
from {{models.stay_ai_subscriptions_base}}

select
    s.id
  , s.subscription_id
  , s.customer_id
  , li.value->>'sku' as sku
  , li.value->>'lineId' as line_id
  , (li.value->>'quantity')::int as quantity
  , (li.value->>'isOneTime')::boolean as is_one_time
  , (li.value->>'unitPrice')::numeric as unit_price
  , li.value->>'productTitle' as product_title
  , li.value->>'variantTitle' as variant_title
  , (li.value->>'subtotalPrice')::numeric as subtotal_price
  , li.value->>'subscriptionId' as shopify_subscription_id
  , li.value->>'shopifyProductId' as shopify_product_id
  , li.value->>'shopifyVariantId' as shopify_variant_id
from {{models.stay_ai_subscriptions}} s
	cross join lateral jsonb_array_elements(s.line_items::jsonb) as li(value)

with ranked as (
  select
      o.order_id
    , o.shopify_order_id_clean
    , o.customer_id
    , o.subscription_id
    , o.created_at
    , o.updated_at
    -- latest-updated StayAI record wins per Shopify order (established business rule)
    , row_number() over (partition by o.shopify_order_id_clean order by o.updated_at desc) as shopify_order_index
  from {{models.stay_ai_orders_base}} o
)

select
    order_id
  , shopify_order_id_clean
  , customer_id
  , subscription_id
  , created_at
  , updated_at
from ranked
-- keep only the winning record per Shopify order
where shopify_order_index = 1

with first_subscription as (
  -- each customer's first subscription, for tenure/reactivation cohorts
  select
      customer_id
    , min(created_at) as first_subscription_created_at
    , min(cancelled_at) as first_cancelled_at
  from {{models.stay_ai_subscriptions_base}}
  group by customer_id
)

select
    s.subscription_id
  , s.customer_id
  , s.email_address         
  , s.created_at
  , s.churned_at
  , s.cancelled_at
  , s.cancellation_reason
  , s.status
  , s.price                  
  , s.currency               
  , s.order_interval_frequency
  , s.order_interval_unit
  -- normalize billing cadence to days so intervals are comparable across units
  , case
      when upper(s.order_interval_unit) = 'DAY' then s.order_interval_frequency
      when upper(s.order_interval_unit) = 'WEEK' then s.order_interval_frequency * 7
      when upper(s.order_interval_unit) = 'MONTH' then s.order_interval_frequency * 30
      when upper(s.order_interval_unit) = 'YEAR' then s.order_interval_frequency * 365
      else null
    end as interval_in_days

  -- COHORT: nth subscription for this customer (1 = first)
  , row_number() over (partition by s.customer_id order by s.created_at asc) as customer_subscription_index

  -- COHORT: this subscription started after the customer had already cancelled one
  , case
      when fs.first_cancelled_at is not null and fs.first_cancelled_at < s.created_at then true
      else false
    end as is_reactivated

  -- COHORT: months since the customer's FIRST subscription (acquisition cohort offset)
  , (extract(year from s.created_at) * 12 + extract(month from s.created_at))
    - (extract(year from fs.first_subscription_created_at) * 12 + extract(month from fs.first_subscription_created_at))
    as months_since_first_subscription

  -- COHORT: tenure to churn, bucketed. NULL while still active.
  -- TZ-converted because this is an intentional week/month rollup (see skill: rollups use business TZ).
  , case
      when s.churned_at is null then null
      else floor(
        extract(day from (
          (s.churned_at at time zone 'UTC' at time zone 'America/New_York')
          - (s.created_at at time zone 'UTC' at time zone 'America/New_York')
        )) / 7
      )
    end as weeks_to_churn

  , case
      when s.churned_at is null then null
      else (extract(year from (s.churned_at at time zone 'UTC' at time zone 'America/New_York')) * 12
            + extract(month from (s.churned_at at time zone 'UTC' at time zone 'America/New_York')))
         - (extract(year from (s.created_at at time zone 'UTC' at time zone 'America/New_York')) * 12
            + extract(month from (s.created_at at time zone 'UTC' at time zone 'America/New_York')))
    end as months_to_churn

  -- COHORT: map raw cancellation reasons to analysis categories.
  -- VALUES ARE CUSTOMER-SPECIFIC — replace the reason strings per customer.
  , case
      when s.cancellation_reason in ('Example reason A', 'Example reason B') then 'Price/Budget'
      when s.cancellation_reason in ('Example reason C') then 'Product Fit'
      when s.cancellation_reason is null then null
      else 'Other'
    end as cancellation_reason_category

from {{models.stay_ai_subscriptions_base}} s
left join first_subscription fs on fs.customer_id = s.customer_id

select
    customer_id
  , count(*) as subscription_count
  , min(created_at) as first_subscription_created_at
  , max(created_at) as latest_subscription_created_at
  , min(cancelled_at) as first_cancelled_at
  , count(*) filter (where upper(status) = 'ACTIVE') as active_subscription_count
  , count(*) filter (where cancelled_at is not null) as cancelled_subscription_count
  , bool_or(cancelled_at is not null) as has_ever_cancelled
from {{models.stay_ai_subscriptions_base}}
group by customer_id

select
    id
  , subscription_id
  , customer_id
  , sku
  , product_title
  , variant_title
  , quantity
  , unit_price
  , subtotal_price
  , is_one_time
  -- EXAMPLE: parse a product dimension encoded in the SKU.
  -- Tailor per customer (e.g. flavor, weight, size, pack count).
  , case
      when lower(sku) like '%example_a%' then 'Dimension A'
      when lower(sku) like '%example_b%' then 'Dimension B'
      else null
    end as product_dimension
  -- EXAMPLE: extract a numeric attribute (e.g. pack/case size) from the SKU.
  , nullif(regexp_replace(sku, '\D', '', 'g'), '') as numeric_attribute_from_sku
from {{models.stay_ai_subscriptions_base}}

select
    sf.subscription_id -- pk
  , sf.customer_id
  , sf.email_address
  , sf.created_at
  , sf.churned_at
  , sf.cancelled_at
  , sf.status
  , sf.price
  , sf.currency
  , sf.interval_in_days
  -- cohort facts (defined upstream in stay_ai_subscription_facts)
  , sf.customer_subscription_index
  , sf.is_reactivated
  , sf.months_since_first_subscription
  , sf.weeks_to_churn
  , sf.months_to_churn
  , sf.cancellation_reason
  , sf.cancellation_reason_category
  -- subscriber-grain context (defined upstream in stay_ai_subscriber_facts)
  , subf.subscription_count
  , subf.has_ever_cancelled
from {{models.stay_ai_subscription_facts}} sf
left join {{models.stay_ai_subscriber_facts}} subf on subf.customer_id = sf.customer_id

select
    lf.id as subscription_line_id
  , lf.subscription_id
  , lf.customer_id
  , lf.sku
  , lf.product_title
  , lf.quantity
  , lf.unit_price
  , lf.subtotal_price
  , lf.is_one_time
  , lf.product_dimension
  , lf.numeric_attribute_from_sku
from {{models.stay_ai_subscription_line_facts}} lf