subscriptions
The subscriptions schema captures subscription count snapshots. It's a source for active, added, and canceled subscription movement by interval.
Anchor to Use casesUse cases
- Subscription base: Group
active_subscriptionsby month to show the subscription base over time. - Movement: Compare
subscriptions_addedandsubscriptions_canceledfor the same snapshot interval to see whether the subscription base is growing or churning.
ShopifyQL
Examples
Description
Compare monthly `active_subscriptions` for this year with a year-over-year percent-change column. This example uses [`COMPARE TO previous_year`](/docs/api/shopifyql/latest/syntax/compare-to) to benchmark each month against the same month last year.
ShopifyQL
FROM subscriptions SHOW active_subscriptions TIMESERIES month WITH PERCENT_CHANGE DURING this_year COMPARE TO previous_year ORDER BY month ASC VISUALIZE active_subscriptions TYPE lineDescription
Compare [`subscriptions_added`](#subscriptionsmetric-propertydetail-subscriptionsadded) and `subscriptions_canceled` by quarter for the last eight quarters. This example uses a line chart to show subscription additions and cancellations over time.
ShopifyQL
FROM subscriptions SHOW subscriptions_added, subscriptions_canceled TIMESERIES quarter WITH TOTALS SINCE startOfQuarter(-7q) UNTIL today ORDER BY quarter ASC VISUALIZE subscriptions_added, subscriptions_canceled TYPE lineDescription
Break down `active_subscriptions` and `subscriptions_canceled` by `quarter` across this year. This example uses [`GROUP BY`](/docs/api/shopifyql/latest/syntax/group-by) to compare the active base with cancellations each quarter.
ShopifyQL
FROM subscriptions SHOW active_subscriptions, subscriptions_canceled GROUP BY quarter WITH TOTALS DURING this_year ORDER BY quarter ASC VISUALIZE active_subscriptions TYPE lineDescription
Break down `active_subscriptions` and `subscriptions_added` by `week` across last quarter. This example uses [`GROUP BY`](/docs/api/shopifyql/latest/syntax/group-by) to compare new additions with the active base each week.
ShopifyQL
FROM subscriptions SHOW active_subscriptions, subscriptions_added GROUP BY week WITH TOTALS DURING last_quarter ORDER BY week ASC VISUALIZE active_subscriptions TYPE line
Anchor to MetricsMetrics
Counts and calculations that let you track key business indicators. Metrics show up as the columns when queried.
Metrics you can use when querying FROM subscriptions.
- Anchor to active_subscriptionsactive_
subscriptionsactive_ subscriptions INTEGERINTEGER The active subscription count from the latest measurement in each period.
- Anchor to subscriptions_addedsubscriptions_
addedsubscriptions_ added INTEGERINTEGER Number of subscriptions your customers added in the given period
- Anchor to subscriptions_canceledsubscriptions_
canceledsubscriptions_ canceled INTEGERINTEGER Number of subscriptions your customers canceled in the given period
INTEGER
A whole number without decimal places, used for counts, quantities, and other discrete numeric values.
Anchor to DimensionsDimensions
Attributes of your data that let you look more granularly at aspects of the data. Group and filter by dimensions to shape the rows your query returns.
Dimensions you can use when querying FROM subscriptions.
- Anchor to daydaydayDAY_TIMESTAMPDAY_TIMESTAMP
The day your subscription summary applies to.
- Anchor to monthmonthmonthMONTH_TIMESTAMPMONTH_TIMESTAMP
The month your subscription summary applies to.
- Anchor to quarterquarterquarterQUARTER_TIMESTAMPQUARTER_TIMESTAMP
A three-month calendar quarter, Q1 through Q4.
- Anchor to shop_idshop_
idshop_ id IDENTITYIDENTITY - Anchor to weekweekweekWEEK_TIMESTAMPWEEK_TIMESTAMP
The week your subscription summary applies to.
- Anchor to yearyearyearYEAR_TIMESTAMPYEAR_TIMESTAMP
The year your subscription summary applies to.
- Anchor to shop_nameshop_
nameshop_ name STRINGSTRING Name of your store
DAY_TIMESTAMP
A date value truncated to day precision.
MONTH_TIMESTAMP
A date value representing the start of a month.
QUARTER_TIMESTAMP
A date value representing the start of a fiscal quarter.
IDENTITY
A unique identifier for a Shopify resource such as a customer, product, or order.
WEEK_TIMESTAMP
A date value representing the start of a week.
YEAR_TIMESTAMP
A date value representing the start of a year.
STRING
A sequence of characters representing text data.
fulfillments: Fulfillment events when you want to track recurring shipments tied to subscription orders.sales: Sale-level data when you want to attribute revenue back to subscription orders.customers: Customer-level data for lifetime value of subscribers and one-off buyers.