Subscription Flags

Subscription reporting depends on consistent classification at the order level and, in many cases, the order-line level. SourceMedium provides several fields that work together.

Order-level fields

Common subscription-related fields in your_project.sm_transformed_v2.obt_orders and your_project.sm_transformed_v2.dim_orders:

sm_order_type: order classification (subscription vs one-time)
is_subscription_order: boolean convenience field
subscription_order_sequence: first vs recurring subscription classification
subscription_order_index: sequential index of subscription orders per customer when available
is_first_subscription_order: convenience flag derived from subscription_order_sequence
is_recurring_subscription_order: convenience flag derived from subscription_order_sequence

Line-level fields

When you need product-level precision, use your_project.sm_transformed_v2.obt_order_lines:

order_line_type: line-level subscription classification
is_order_line_subscription: boolean convenience field for subscription lines
subscription_order_index: sequential index for the subscription when available from line-level subscription data
subscription_order_sequence: first vs recurring subscription classification at the line level
subscription_id: subscription identifier when a reliable one is available

These fields are most reliable when you have a direct subscription-platform integration, Shopify line-item metadata, or Amazon Seller Central order-item promotion/program signals that clearly identify subscription vs one-time behavior.

For direct subscription-platform integrations, SourceMedium can use exact line-item matching first and fallback matching when platform data is incomplete. That improves line-level classification without changing the recommended query patterns in these docs. Amazon Seller Central Subscribe & Save classification, and what it does not include, is on Order type.

When to use orders vs order lines

Use obt_orders when your business question is about:

customer retention
subscription-order counts
order-level LTV or cohort analysis

Use obt_order_lines when your business question is about:

mixed carts with subscription and one-time items
product-level subscription performance
free gifts, bundles, or add-ons within a cart
line-specific debugging when an order contains conflicting signals

Shopify nuance

Shopify classification prefers a connected subscription platform and explicit line-level metadata over order tags. Precedence and the tag patterns that still count are on Order type.

Line-level sequence alignment

At the line level, SourceMedium now keeps subscription_order_sequence aligned with is_order_line_subscription:

subscription lines should not carry the non-subscription sequence value
non-subscription lines should carry the non-subscription sequence value
when a line is clearly subscription but no trustworthy first-vs-recurring index is available, SourceMedium can default the line to the recurring subscription sequence instead of the non-subscription sequence

Use line-level fields to answer line-level questions, and order-level fields to answer order-level questions.

Recommended query patterns

Order-level subscription trend

SELECT
  DATE_TRUNC(DATE(order_processed_at_local_datetime), MONTH) AS month_start,
  subscription_order_sequence,
  COUNT(*) AS order_count,
  SUM(order_net_revenue) AS net_revenue
FROM `your_project.sm_transformed_v2.obt_orders`
WHERE is_order_sm_valid = TRUE
  AND is_subscription_order = TRUE
  AND order_processed_at_local_datetime >= DATETIME_SUB(CURRENT_DATETIME(), INTERVAL 365 DAY)
GROUP BY 1, 2
ORDER BY month_start DESC, subscription_order_sequence;

Line-level subscription mix

SELECT
  order_line_type,
  subscription_order_sequence,
  COUNT(*) AS line_count,
  SUM(order_line_net_revenue) AS net_revenue
FROM `your_project.sm_transformed_v2.obt_order_lines`
WHERE is_order_sm_valid = TRUE
  AND order_processed_at_local_datetime >= DATETIME_SUB(CURRENT_DATETIME(), INTERVAL 180 DAY)
GROUP BY 1, 2
ORDER BY net_revenue DESC;

Mixed-cart debugging

SELECT
  order_id,
  sm_order_key,
  order_line_id,
  order_line_type,
  is_order_line_subscription,
  subscription_order_sequence,
  subscription_id,
  order_line_net_revenue
FROM `your_project.sm_transformed_v2.obt_order_lines`
WHERE is_order_sm_valid = TRUE
  AND order_id = 'replace_with_order_id';

How the model works

How numbers are defined

Order segmentation

Customer record

Attribution

Order-level fields

Line-level fields

When to use orders vs order lines

Shopify nuance

Line-level sequence alignment

Recommended query patterns

Order-level subscription trend

Line-level subscription mix

Mixed-cart debugging

​Order-level fields

​Line-level fields

​When to use orders vs order lines

​Shopify nuance

​Line-level sequence alignment

​Recommended query patterns

​Order-level subscription trend

​Line-level subscription mix

​Mixed-cart debugging

​Related resources

Order-level fields

Line-level fields

When to use orders vs order lines

Shopify nuance

Line-level sequence alignment

Recommended query patterns

Order-level subscription trend

Line-level subscription mix

Mixed-cart debugging

Related resources