dim_subscriptions

Pro warehouses only. Subscription tables are not delivered on Foundation warehouses. See Foundation and Pro.

BI subscription dimension.

Grain: (sm_store_id, subscription_id).

Columns

Column	Type	Description
`sm_store_id`	STRING	SourceMedium’s unique store identifier.
`address_id`	STRING	Platform address identifier associated with the subscription, when available.
`cancel_type`	STRING	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.
`cancellation_comments`	STRING	Free-text cancellation comments supplied by the subscription platform, when available.
`cancellation_reason`	STRING	Cancellation reason supplied by the subscription platform, when available.
`charge_interval_frequency`	INT64	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.
`customer_id`	STRING	Alias of `subscriber_id`.
`dunning_at`	DATE	Date the subscription is currently in dunning, in the workspace reporting timezone. Null when the subscription is not currently in dunning.
`dunning_entered_date`	DATE	Date this active subscription entered dunning, based on the subscriber’s latest failed charge. Used by the daily report’s dunning time series.
`has_queued_charges`	BOOL	Whether the subscription currently has queued charges. Not available for Medusa.
`interval_days`	FLOAT64	Delivery cadence expressed in days.
`is_active`	BOOL	Whether the subscription is currently active (`subscription_status = ACTIVE`).
`is_bundle_component`	BOOL	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.
`is_dunning`	BOOL	Whether this active subscription currently belongs to a subscriber in dunning.
`is_prepaid`	BOOL	Whether the subscription is billed once for multiple upcoming deliveries.
`is_return`	BOOL	Whether this subscription is a returning subscription at subscriber and product grain.
`is_subscription_currency_canonicalized`	BOOL	True when standard subscription price and MRR use reporting-currency values.
`max_retries_reached`	BOOL	Whether the subscription has reached the platform’s payment-retry limit. Not available for Medusa.
`order_interval_frequency`	INT64	Number of `order_interval_unit` values between deliveries.
`order_interval_unit`	STRING	Unit for the subscription delivery cadence. Observed values are `day`, `week`, and `month`.
`platform`	STRING	Subscription platform. Observed values are `ReCharge` and `Medusa`.
`product_id`	STRING	Platform identifier for the subscribed product.
`product_title`	STRING	Product title supplied by the subscription platform.
`reporting_timezone`	STRING	Your store’s reporting timezone, used for the date columns on this row.
`sku`	STRING	Stock keeping unit for the subscribed product variant.
`smcuuid`	STRING	Hashed store identifier.
`subscriber_id`	STRING	Identifier for the subscriber who owns the subscription.
`subscriber_status`	STRING	Current status of the owning subscriber. Published values are `active`, `inactive`, and `dunning`.
`subscription_cancelled_at`	TIMESTAMP	UTC timestamp when the platform recorded a cancellation, when present.
`subscription_cancelled_date`	DATE	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.
`subscription_converted_currency_code`	STRING	Workspace reporting currency selected for the subscription.
`subscription_converted_mrr`	FLOAT64	Monthly recurring revenue converted to the workspace reporting currency.
`subscription_converted_price`	NUMERIC	Subscription price converted to the workspace reporting currency.
`subscription_created_at`	TIMESTAMP	UTC timestamp when the subscription was created.
`subscription_created_at_local_datetime`	DATETIME	Subscription created timestamp converted to the workspace reporting timezone.
`subscription_created_date`	DATE	Subscription start date in the workspace reporting timezone.
`subscription_currency_canonicalization_status`	STRING	Whether standard subscription price and MRR were rewritten to reporting-currency values.
`subscription_currency_code`	STRING	Currency of the standard subscription price and MRR fields. Uses the workspace reporting currency when reporting-currency conversion is enabled.
`subscription_currency_conversion_currency_code`	STRING	Currency that should describe subscription price and MRR after conversion is applied.
`subscription_currency_conversion_effective_date`	DATE	Date used to select the exchange rate for this subscription.
`subscription_currency_conversion_mapping_from_currency`	STRING
`subscription_currency_conversion_mapping_id`	STRING
`subscription_currency_conversion_mapping_source`	STRING
`subscription_currency_conversion_mapping_to_currency`	STRING
`subscription_currency_conversion_rate_applied`	FLOAT64	Exchange rate used to convert subscription price and MRR.
`subscription_currency_conversion_resolution_method`	STRING
`subscription_currency_conversion_settings_version`	STRING
`subscription_currency_conversion_status`	STRING	Whether the subscription was converted, already used the reporting currency, or could not be converted.
`subscription_currency_mismatch_flag`	BOOL	True when the source currency and conversion mapping do not agree.
`subscription_id`	STRING	Platform identifier for the subscription.
`subscription_interval`	STRING	Combined delivery cadence label, such as `30-day`.
`subscription_line_id`	STRING	Alias of `subscription_id`. Kept for compatibility with line-oriented subscription models.
`subscription_mrr`	FLOAT64	Monthly recurring revenue in `subscription_currency_code`.
`subscription_next_charge_scheduled_date`	DATE	Next scheduled charge date supplied by the subscription platform, when available.
`subscription_original_currency_code`	STRING	Currency supplied by the subscription source.
`subscription_original_mrr`	FLOAT64	Monthly recurring revenue before currency conversion.
`subscription_original_price`	NUMERIC	Subscription price before currency conversion.
`subscription_price`	NUMERIC	Subscription price in `subscription_currency_code`.
`subscription_quantity`	INT64	Quantity included in the subscription.
`subscription_status`	STRING	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`.
`subscription_updated_at`	TIMESTAMP	UTC timestamp when the subscription was last updated.
`variant_id`	STRING	Platform identifier for the subscribed product variant.
`variant_title`	STRING	Variant title supplied by the subscription platform.

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.

Get into BigQuery

Activate

SQL Query Library

Common analyses (SQL)

Tables

Schema Standards

Columns

​Columns

Columns