obt_subscriptions

version: 2

models:
  - name: obt_subscriptions
    description: >
      Subscription analytics table with one row per subscription, enriched with product and
      subscriber attributes.
    columns:
      - name: sm_store_id
        description: SourceMedium's unique store identifier.

      - name: subscription_id
        description: Platform identifier for the subscription.

      - name: subscriber_id
        description: Identifier for the subscriber who owns the subscription.

      - name: platform
        description: Subscription platform. Observed values are `ReCharge` and `Medusa`.

      - name: reporting_timezone
        description: Workspace timezone used for subscription tenure and lifecycle dates.

      - name: subscription_status
        description: >
          Current subscription status from the subscription platform, stored in uppercase. Observed
          values are `ACTIVE`, `CANCELLED`, `EXPIRED`, and `PAUSED`.

      - name: is_active
        description: Whether the subscription is currently active.

      - name: is_dunning
        description: >
          Whether this active subscription currently belongs to a subscriber in dunning.

      - name: cancel_type
        description: >
          How a non-active subscription left the active base. Observed values are `Active` for a
          voluntary cancel and `Passive` for expiry, max retries, and other non-active statuses.
          Null for active subscriptions.

      - name: is_return
        description: >
          Whether this subscription is a returning subscription at subscriber and product grain.

      - name: cancellation_reason
        description: Cancellation reason supplied by the subscription platform, when available.

      - name: subscription_price
        description: Subscription price in `subscription_currency_code`.

      - name: subscription_quantity
        description: Quantity included in the subscription.

      - name: subscription_currency_code
        description: >
          Currency of the standard subscription price and MRR fields. Uses the workspace reporting
          currency when reporting-currency conversion is enabled.

      - name: subscription_mrr
        description: Monthly recurring revenue in `subscription_currency_code`.

      - name: interval_days
        description: Delivery cadence expressed in days.

      - name: order_interval_unit
        description: >
          Unit for the subscription delivery cadence. Observed values are `day`, `week`, and
          `month`.

      - name: order_interval_frequency
        description: Number of `order_interval_unit` values between deliveries.

      - name: is_prepaid
        description: Whether the subscription is billed once for multiple upcoming deliveries.

      - name: subscription_created_at
        description: UTC timestamp when the subscription was created.

      - name: subscription_created_date
        description: Subscription start date in the workspace reporting timezone.

      - name: subscription_cancelled_date
        description: >
          Outflow date used for non-active subscriptions. Prefer the platform cancel timestamp,
          then the last settled charge date, then last updated, then created. Active subscriptions
          are null.

      - name: dunning_at
        description: >
          Date the subscription is currently in dunning, in the workspace reporting timezone. Null
          when the subscription is not currently in dunning.

      - name: subscription_cohort_month
        description: Month in which the subscription started.

      - name: subscription_tenure_days
        description: >
          Days from `subscription_created_date` to `subscription_cancelled_date`, or to the current
          date in the workspace reporting timezone for active subscriptions.

      - name: product_id
        description: Platform identifier for the subscribed product.

      - name: variant_id
        description: Platform identifier for the subscribed product variant.

      - name: sku
        description: Stock keeping unit for the subscribed product variant.

      - name: product_title
        description: >
          Product title. Prefers the Shopify product master title when the variant joins, otherwise
          the title from the subscription platform.

      - name: product_type
        description: Product type from the Shopify product master, when the variant joins.

      - name: product_vendor
        description: Product vendor from the Shopify product master, when the variant joins.

      - name: sm_product_key
        description: SourceMedium product key from the Shopify product master, when the variant joins.

      - name: sm_product_variant_key
        description: >
          SourceMedium product variant key from the Shopify product master, when the variant joins.

      - name: subscriber_status
        description: >
          Current status of the owning subscriber. Published values are `active`, `inactive`, and
          `dunning`.

      - name: number_active_subscriptions
        description: Number of currently active subscriptions owned by this subscriber.

      - name: subscriber_cohort_month
        description: Month of the subscriber's first subscription.

      - name: subscriber_tenure_days
        description: Days since the subscriber's first subscription.

      - name: subscriber_lifetime_revenue
        description: Collected subscription charge revenue available from the subscription source.

      - name: first_charge_processed_at
        description: UTC timestamp of the subscriber's first processed charge, when available.

      - name: last_charge_processed_at
        description: UTC timestamp of the subscriber's most recent processed charge, when available.

      - name: smcuuid
        description: Hashed store identifier.