obt_subscriptions

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

One Big Table at subscription grain: denormalized dim_subscriptions + subscriber context (status, cohort, tenure, realized LTV) + Shopify product master + customer master. The flat BI surface for entity slicing by product/tenure/cohort/status.

Columns

Column	Type	Description
`sm_product_key`	STRING	SourceMedium product key from the Shopify product master, when the variant joins.
`sm_product_variant_key`	STRING	SourceMedium product variant key from the Shopify product master, when the variant joins.
`sm_store_id`	STRING	SourceMedium’s unique store identifier.
`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_reason`	STRING	Cancellation reason supplied by the subscription platform, when available.
`dunning_at`	DATE	Date the subscription is currently in dunning, in the workspace reporting timezone. Null when the subscription is not currently in dunning.
`first_charge_processed_at`	TIMESTAMP	UTC timestamp of the subscriber’s first processed charge, when available.
`interval_days`	FLOAT64	Delivery cadence expressed in days.
`is_active`	BOOL	Whether the subscription is currently active.
`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.
`last_charge_processed_at`	TIMESTAMP	UTC timestamp of the subscriber’s most recent processed charge, when available.
`number_active_subscriptions`	INT64	Number of currently active subscriptions owned by this subscriber.
`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. Prefers the Shopify product master title when the variant joins, otherwise the title from the subscription platform.
`product_type`	STRING	Product type from the Shopify product master, when the variant joins.
`product_vendor`	STRING	Product vendor from the Shopify product master, when the variant joins.
`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_cohort_month`	DATE	Month of the subscriber’s first subscription.
`subscriber_id`	STRING	Identifier for the subscriber who owns the subscription.
`subscriber_lifetime_revenue`	NUMERIC	Collected subscription charge revenue available from the subscription source.
`subscriber_status`	STRING	Current status of the owning subscriber. Published values are `active`, `inactive`, and `dunning`.
`subscriber_tenure_days`	INT64	Days since the subscriber’s first subscription.
`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. Active subscriptions are null.
`subscription_cohort_month`	DATE	Month in which the subscription started.
`subscription_created_at`	TIMESTAMP	UTC timestamp when the subscription was created.
`subscription_created_date`	DATE	Subscription start date in the workspace reporting timezone.
`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_id`	STRING	Platform identifier for the subscription.
`subscription_mrr`	FLOAT64	Monthly recurring revenue in `subscription_currency_code`.
`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`.
`subscription_tenure_days`	INT64	Days from the subscription’s start date to today, or to its cancellation date for subscriptions that have ended, in your reporting timezone.
`variant_id`	STRING	Platform identifier for the subscribed product variant.

subscription_price and subscription_mrr use the exchange rate from the subscription start date when reporting-currency conversion is enabled. Conversion does not apply to subscriber_lifetime_revenue, which depends on charge data supplied by the subscription platform. Use dim_subscriptions when you need the original value, converted value, and exchange rate.

Medusa does not provide subscription charge history. subscriber_lifetime_revenue therefore cannot represent realized Medusa subscription LTV.

A ReCharge bundle currently lands as multiple component subscriptions. Counts and MRR can be inflated for bundle-heavy stores. Identify those rows with dim_subscriptions.is_bundle_component.

Get into BigQuery

Activate

SQL Query Library

Common analyses (SQL)

Tables

Schema Standards

Columns

​Columns

Columns