version: 2
models:
- name: dim_subscriptions
description: >
Subscription dimension with one row per subscription. Use it for current subscription status,
product, cadence, price, MRR, and currency reconciliation.
columns:
- name: sm_store_id
description: SourceMedium's unique store identifier.
- name: platform
description: Subscription platform. Observed values are `ReCharge` and `Medusa`.
- name: subscription_id
description: Platform identifier for the subscription.
- name: subscription_line_id
description: Alias of `subscription_id`. Kept for compatibility with line-oriented subscription models.
- name: subscriber_id
description: Identifier for the subscriber who owns the subscription.
- name: customer_id
description: Alias of `subscriber_id`.
- name: address_id
description: Platform address identifier associated with the subscription, when available.
- name: reporting_timezone
description: Workspace timezone used for subscription lifecycle dates.
- name: subscription_status
description: >
Current subscription status from the subscription platform, stored in uppercase. Observed
values are `ACTIVE`, `CANCELLED`, `EXPIRED`, and `PAUSED`. ReCharge uses `EXPIRED` for
involuntary lapse; Medusa uses `PAUSED`.
- name: cancellation_reason
description: Cancellation reason supplied by the subscription platform, when available.
- name: cancellation_comments
description: Free-text cancellation comments supplied by the subscription platform, when available.
- name: is_active
description: Whether the subscription is currently active (`subscription_status = ACTIVE`).
- 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_dunning
description: >
Whether this active subscription currently belongs to a subscriber in dunning.
- 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: dunning_entered_date
description: >
Date this active subscription entered dunning, based on the subscriber's latest failed
charge. Used by the daily report's dunning time series.
- name: is_return
description: >
Whether this subscription is a returning subscription at subscriber and product grain.
- name: subscriber_status
description: >
Current status of the owning subscriber. Published values are `active`, `inactive`, and
`dunning`.
- name: subscription_quantity
description: Quantity included in the subscription.
- 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: charge_interval_frequency
description: >
Number of `order_interval_unit` values between charges. For prepaid subscriptions this can
be longer than the delivery interval and is what SourceMedium uses to calculate MRR.
- name: subscription_interval
description: Combined delivery cadence label, such as `30-day`.
- name: product_id
description: Platform identifier for the subscribed product.
- name: variant_id
description: Platform identifier for the subscribed product variant.
- name: product_title
description: Product title supplied by the subscription platform.
- name: variant_title
description: Variant title supplied by the subscription platform.
- name: sku
description: Stock keeping unit for the subscribed product variant.
- name: is_prepaid
description: Whether the subscription is billed once for multiple upcoming deliveries.
- name: is_bundle_component
description: >
True when a ReCharge line is a bundle component. Bundle purchases currently appear as
multiple subscriptions, so counts and MRR can be inflated until SourceMedium can collapse
those components.
- name: has_queued_charges
description: Whether the subscription currently has queued charges. Not available for Medusa.
- name: max_retries_reached
description: >
Whether the subscription has reached the platform's payment-retry limit. Not available for
Medusa.
- name: subscription_created_at
description: UTC timestamp when the subscription was created.
- name: subscription_created_at_local_datetime
description: Subscription created timestamp converted to the workspace reporting timezone.
- name: subscription_created_date
description: Subscription start date in the workspace reporting timezone.
- name: subscription_cancelled_at
description: UTC timestamp when the platform recorded a cancellation, when present.
- 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. Expired and passively
cancelled subscriptions therefore have a date even when the customer did not cancel.
Active subscriptions are null.
- name: subscription_updated_at
description: UTC timestamp when the subscription was last updated.
- name: subscription_next_charge_scheduled_date
description: Next scheduled charge date supplied by the subscription platform, when available.
- name: subscription_price
description: Subscription price in `subscription_currency_code`.
- 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: subscription_original_currency_code
description: Currency supplied by the subscription source.
- name: subscription_converted_currency_code
description: Workspace reporting currency selected for the subscription.
- name: subscription_currency_conversion_currency_code
description: >
Currency that should describe subscription price and MRR after conversion is applied.
- name: subscription_currency_conversion_effective_date
description: Date used to select the exchange rate for this subscription.
- name: subscription_currency_conversion_rate_applied
description: Exchange rate used to convert subscription price and MRR.
- name: subscription_currency_conversion_status
description: >
Whether the subscription was converted, already used the reporting currency, or could not
be converted.
- name: subscription_currency_mismatch_flag
description: True when the source currency and conversion mapping do not agree.
- name: subscription_currency_canonicalization_status
description: >
Whether standard subscription price and MRR were rewritten to reporting-currency values.
- name: subscription_original_price
description: Subscription price before currency conversion.
- name: subscription_converted_price
description: Subscription price converted to the workspace reporting currency.
- name: subscription_original_mrr
description: Monthly recurring revenue before currency conversion.
- name: subscription_converted_mrr
description: Monthly recurring revenue converted to the workspace reporting currency.
- name: is_subscription_currency_canonicalized
description: True when standard subscription price and MRR use reporting-currency values.
- name: smcuuid
description: Hashed store identifier.
Subscription price and MRR use the exchange rate from the subscription start date. See Reporting Currency for reconciliation guidance.
Medusa subscription MRR describes recurring subscription value. Medusa does not provide subscription charge history, so this field is not collected charge revenue or realized subscriber LTV.
A ReCharge bundle currently lands as multiple component subscriptions. Use
is_bundle_component to identify those rows; counts and MRR can be inflated until SourceMedium can collapse the components.